XML FeedsA customer's DBA team created a checklist for the development teams with some best practices for writing proper T-SQL and asked me to write some contributions for their tips document library. So if I do the research and write the documents I might as well post them here. The content may not be all sparkling and new but since there is a demand from customers, there are still people out there having trouble finding the correct information.
So let's get started. The first document is why you should avoid SELECT * in your queries:
For performance reasons
To be honest this part of the post is based on Ted's post: SELECT * vs SELECT 1 with EXISTS.
To check the performance impact I use a tool called SQLQueryStress to execute my queries and see how much time elapsed.
To get started I need a table with a large number of columns, data is not necessary for this test so I use this script to generate a table with 127 columns:
- DECLARE @colnumber int = 1
- DECLARE @command VARCHAR(4000) =''
- WHILE @colnumber <= 125
- BEGIN
- SET @command = @command + ' col' + CAST(@colnumber AS varchar(3)) + ' int, '
- SET @colnumber += 1
- END
- SET @command = 'CREATE TABLE StarPerform (PerfId int, ' + @command + ' Lastcolumn int)'
- EXEC (@command)
Now I query the table using the SQLQueryStress tool and I choose a high Number of Iterations to get a meaningful average:
In the Client Seconds/Iteration (Avg) you see that the performance difference for an individual query is negligible but a similar query often executed on a busy server can result in 15 extra seconds on 5000 Iterations.
Broken code
Using SELECT * in Views is also a bad practice because changes to the underlying table will return unexpected results or fail completely.
First I create a table and insert some data:
- --Create the table
- CREATE TABLE StarBreak
- (
- ID int IDENTITY (1,1),
- Name varchar (10),
- DateFirstPost date,
- DateLastPost date
- );
- GO
- --Insert some values
- INSERT INTO StarBreak (Name, DateFirstPost, DateLastPost)
- VALUES ('Denis','20080207','20130101'),('Ted','20081107','20121231'),('Koen','20121123','20121227'),('Jes','20101210','20121221');
- GO
Now I create and query a View to return all the columns:
- CREATE VIEW GetStarFromStarBreak
- AS
- SELECT * from Starbreak;
- GO
And I get this result back:
Now let’s drop the table and recreate it but switch the position of the two datecolumns:
- --Drop the table
- DROP TABLE StarBreak;
- GO
- --Create the table
- CREATE TABLE StarBreak
- (
- ID int IDENTITY (1,1),
- Name varchar (10),
- DateLastPost date,
- DateFirstPost date
- );
- GO
- --Insert some values
- INSERT INTO StarBreak (Name, DateFirstPost, DateLastPost)
- VALUES ('Denis','20080207','20130101'),('Ted','20081107','20121231'),('Koen','20121123','20121227'),('Jes','20101210','20121221');
- GO
When I query the table again I get the correct result:
But when I query my view again I get the following result:
You see that the column headers are in the same order as in the initial table but the data reflects the column order of the second table.
Dropping the table, and adding a column in the middle will also result in the above behavior:
- --Drop the table
- DROP TABLE StarBreak;
- GO
- --Create the table
- CREATE TABLE StarBreak
- (
- ID int IDENTITY (1,1),
- Name varchar (10),
- Gender char(1),
- DateLastPost date,
- DateFirstPost date
- );
- GO
- --Insert some values
- INSERT INTO StarBreak (Name, DateFirstPost, DateLastPost)
- VALUES ('Denis','20080207','20130101'),('Ted','20081107','20121231'),('Koen','20121123','20121227'),('Jes','20101210','20121221');
- GO
What happens when I recreate the table but only 3 instead of 4 columns? Let’s try:
- --Drop the table
- DROP TABLE StarBreak;
- GO
- --Create the table
- CREATE TABLE StarBreak
- (
- ID int IDENTITY (1,1),
- Name varchar (10),
- DateLastPost date,
- );
- GO
- --Insert some values
- INSERT INTO StarBreak (Name, DateLastPost)
- VALUES ('Denis','20130101'),('Ted','20121231'),('Koen','20121227'),('Jes','20121221');
- GO
Now query the view again but use this query to make sure you only select the 3 existing columns:
- SELECT ID, Name, DateLastPost FROM GetStarFromStarBreak
And the result is:
You see the code breaks because the view still expects 4 columns although you specified you only query 3 columns from the view.
Conclusion
We have seen several reasons to stay away from the SELECT * statement but we do understand the pain in typing in 127 column names. So here is a last tip:
When you drag the Columns folder of your table in Object Explorer to the Query Pane, SQL Server Management Studio will automatically list the column names separated with commas for you.
Axel started his IT career more than a decade ago. After a few months he was the accidental DBA for the SQL Server 6.5 running the Service Desk application. It didn't took long or Axel started to focus on SQL Server and after another year he became the full-time DBA in a large Belgian datacenter managing the SQL Servers for the hosted customers.It's more than five years now since he switched to consulting and teaching SQL Server.
Axel is a Microsoft Certified DBA and Trainer.
Great post. I was not aware of the issues with view definitions actually returning incorrect data. Only that new columns would not appear in the view without refreshing the view definition.
Nice post! I had a company I worked for lose a lot of money because of the view issue returning incorrect calculations. Here is another post on it and preventing it if you do find yourself in a select * and view situation (maintain the meta data type deal)
Thanks for the comments and tips
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.