Star Struck

Comments: No Comments
Published on: August 17, 2010

Have you ever run into an error and been puzzled as to why that error occurred?

Recently I have been working on a project to backfill a development and QA environment.  These environments are essential in any database environment to help ensure the production database will continue to work after changes have been made.  These environments existed once upon a time but had been sorely neglected and process was not followed.

For this process, I am using some software to help generate diff scripts between the environments.  Once the script has been generated I will execute that script in the appropriate environment (DEV or QA).  I am doing this process for all tables, procs, views, and functions.  This is also done iteratively database by database.

All-Stars

While performing this routine, occasionally an error might be encountered due to an order of processing that may need to be performed.  In cases such as that, I would typically alter my process to account for this error.  Usually that is not an issue if the tables are first created, then the views, and then the procs and functions.  Other times, the create/alter script must be evaluated to determine what is causing the failure.

One of these failures that caused me a lot of angst was producing the following error in  a few stored procedures.

That error message is fair enough to evaluate.  Based on the error one should expect that the stored procedure contains one of those keywords.  I picked a stored procedure out of the mix that was generating this error and checked the code for it.  Looking through the entire proc, there was no such instance of those keywords.  This was temporarily puzzling.  I continued to investigate and found that there was a view being used in that stored procedure.  I decided to double-check the view and see if it might be the cause.  Inside the view I see something like the following:

[codesyntax lang=”tsql”]

[/codesyntax]

At first sight, I see a few things I don’t like but didn’t immediately pick up on the table in the third select.  I saw the table name and moved on thinking it must be ok.  Then I decided to sanity check more precisely what was there and saw that it was pointing to the same table name in a different database.  Low and behold in the other database the table did not have the same number of columns.  These tables are all pretty wide at 100+ columns each.  The designer thought that every single column from each of these tables was needed and decided to take a shortcut.  Had the columns been explicitly named, I would have gotten a more useful error message, in this case, such as the following:

That little change in message could save time when troubleshooting a problem such as what I have described.  Granted, it does not tell me which database or table but at least I know which column is missing.  This is one little reason why I like to explicitly name the columns in my Select statements.

Another reason why I like to use the column names instead of a ” * ” is that I have seen queries improve dramatically in performance speed when compared.  In this little case, even though there are 100+ columns from each table to list out and all of the data would be returned from each of these, I see a 700 ms improvement in return time on just the “top 1000” records.  When I bump that up to the full result set required by the view, I tire of waiting for the results from either query.  Both are terrificly slow (takes longer than 15 minutes to return the full result set).

But But But…
So why do people use Select * in their code?  It is a lot easier to type than having to type all of the column names.  Because it is fewer keystrokes it would also save time when writing code.  Many times, we may also use it when trying to get a glimpse of the system really quick.  It is also oftentimes used when doing a demo.

Should it be used?  There are cases for using that kind of code such as in an “if exists” block.  There are cases where the performance impact is minimal or just as good as listing the columns so it is fine.  I use it when I am trying to do a quick and dirty statement while troubleshooting.  I don’t like to put that into code to be consumed by applications or reused.  I like to know exactly what columns are being returned by my queries and I don’t feel it necessary to return more data than necessary (and consequently consume more resources).

What are some cases you have found a select * to be better than explicitly naming the columns?

page 1 of 1








Calendar
February 2017
M T W T F S S
« Jan    
 12345
6789101112
13141516171819
20212223242526
2728  
Content
SQLHelp

SQLHelp

  • @Ko_Ver: @NickyvV no, no special tricks in the package :) #sqlhelp
  • @NickyvV: @Ko_Ver I've seen that last week that the Job (step) succeeded, but it actually failed with an error #sqlhelp
  • @NickyvV: @Ko_Ver Is FailParentOnFailure set to false on a task/container maybe? #sqlhelp
  • @Ko_Ver: When I start an Agent job, the "job running" pop-up says there's an error. But SSIS catalog and Agent history says jobs succeed. #sqlhelp
  • @DbaMayor: #sqlhelp I'm seeing LCK_M_U and LCK_M_RS_U waits on sec readonly replica with only reporting queries...anyone faced similar issue?..thnks
  • @TrustMeImADBA: Prob not a good question for #sqlhelp but what is the chances time-service changing the time 23 sec forward made my sql cluster failover?
  • @zippy1981: Seem to be that I need to install SSMS vNext Verifying now #sqlhelp
  • @ChrisAdkin8: @YaHoznaDBA you should also compare avg writelog waits to avg tran log write stall,the two are not necessarily foced to be the same #sqlhelp
  • @toddkleinhans: #sqlhelp Will try using lock priority. Totally forgot about that. Thanks!
  • @DBArgenis: @toddkleinhans Compatibility mode is for T-SQL constructs and Query Optimization, nothing else. #sqlhelp

Welcome , today is Tuesday, February 21, 2017