@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT: how each one returns the last identity value, and why a trigger can make @@IDENTITY give the wrong one.
SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer
Clean Cache commands affect different parts of SQL Server memory. I separate buffer-pool testing from execution-plan testing.
SQL SERVER – Fix: Error Msg 128 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Msg 128, The name is not permitted in this context, shows up when one column is used as the DEFAULT of another. Use only constants, expressions or variables.
SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity
DBCC CHECKIDENT with RESEED lets you reset table identity to any value. Reseed to 0 so the next insert gets 1, and never go below values already in the table.
SQL SERVER – Union vs. Union All – Which is better for performance?
UNION removes duplicate rows like SELECT DISTINCT, while UNION ALL keeps them all. My early notes on Union vs. Union All, with a link to the rewritten post.
SQL SERVER – Script to Determine Which Version of SQL Server 2000-2005 is Running
A quick way to see which version of SQL Server 2000 or 2005 is running: SERVERPROPERTY returns the product version, the product level and the edition.
SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation
What do SET QUOTED_IDENTIFIER and SET ANSI_NULLS do around every stored procedure you create? A plain explanation of both settings, ON and OFF.