Select Statements in SQL 2005

Select Statements in SQL 2005

• Maxamor

  Is there an article out there that explains what syntax is allowed with T-SQL in 2005

  Thanks,

  Jeremy

  
  
  

• bpsmith

  Jeremy:

  For one thing, select * leaves "land mines" in stored procedures. What happens is that the meaning of select * is determined at stored procedure compile time and not at stored procedure run time. The problem occurs when a column is added or removed. Since the meaning of SELECT was determined BEFORE the table change occurred there is a disconnect. If columns were added, the SELECT * of the stored procedure doesn't "know" anything about the new columns and does not include them in the results of the compiled SELECT * statement. If columns were deleted from the table the SELECT * statement in the stored procedure now references columns that do not exist.

  If someone recompiles the stored procedure without making changes, the stored procedure that was not working correctly might now "mysteriously" begin to work correctly (maybe).

  There are other scenarios too, but this should give you the idea of at least one set of problems.

  
  

• Jeffrey Kent

  Thanks for everyones information on this.

  Thanks,

  Jeremy

  
  
  

• Paula M

  The backward compatibility topics in Books Online documents all the changes. Take a look at the link below:

  http://msdn2.microsoft.com/en-us/library/ms143532.aspx

  You can also use the Upgrade Advisor to prepare for the upgrade. This will help you identify problems in your code. See link below:

  http://msdn2.microsoft.com/en-us/library/ms144256.aspx

  
  
  

• pravinarote

  I agree with Waldrop.
  
  But, I was wondering if you are refering to "SELECT *" or SELECT using *= and =* for outer joins. The syntax using *= and =* is no longer supported and needs to be removed from all queries.
  
  
  
• Select Statements in SQL 2005