Last Days to Register…
Hands-On Busines Intelligence workshop with Brian Knight
Taught by the master, this seriously hands-on workshop will take you from start to finish with practical creation of a data warehouse solution. You’ll learn the real-world things you need to know, and you’ll be learning them from the master of BI, Brian Knight. The workshop will be held in the SSWUG.ORG offices, with team and individual hands-on learning.
Due to the hands-on nature of this workshop,
SEATING IS EXTREMELY LIMITED.
Find out more here, and register ASAP to save your seat or seats for your entire team.
Amazon AWS 7/1/2012 Outage – And What We Learned
The recent outage with Amazon AWS was a … learning experience. Check out what we’ve learned, and perhaps there are some items that are food for thought in your own installation.
[Watch here] – special session on important lessons learned.
SELECT * – Another Good Reason Not To
Most of you probably know that it is preferred to explicitly name the columns in an SQL Select query. There are a lot of reasons for this statement.
One of the key reasons I think is compelling enough is that using SELECT * can cause your code to break.
If the consumer of a query expects a specific set of columns in a specific order, and the underlying table or view schema is modified to change this output, then using SELECT * can change the “Interface” or contract of your output and your code breaks.
However, if you use SELECT Col1, Col2, Coln… then even if the underlying schema is modified, then your code continues to work as designed. Only if one of the specified columns is removed will the query break. Even then, it is something your code would have to address anyway.
Today I came across another reason not to use SELECT * as programming syntax. It turns out that if you have a view based on a table, and when addressing the table you use SELECT *, SQL Server will not return new columns added to the base table until the plan for the view is updated.
If you check out the MSDN posting for sp_refreshview at http://msdn.microsoft.com/en-us/library/ms187821.aspx you can get a detailed explanation. According to Microsoft, “Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.”
What this means is that until you refresh the SQL Cached plan for using a view it will not contain additional columns. It may blow up if you remove columns. This problem will not occur if you specify all the necessary -columns in your select statement. If you specify explicit columns that are removed it will not work…but you will know why. If you wish new columns to be contained in the view, you will have to add them, and not worry about if they appear or not. They won’t appear unless you alter the view. Either way, there is no question as to what will appear when using a view depending on if the plan is cached or not.
So, there you go, Another reason not to use SELECT *.
Are you tired of hearing "don’t use SELECT *?" Do you have a compelling reason to exclude this syntax? Share your thoughts by writing to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting problems with views in SQL Server 2008 R2
In this article, Alexander Chigrik explains some problems that you can have when you work with views in SQL Server 2008 R2.
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)