Search This Blog

Saturday, April 28, 2012

T-SQL: Useful Tips

SCOPE_IDENTITY() To retrieve identity value from the last inserted record, use SCOPE_IDENTITY() instead of @@IDENTITY.
EXISTS To gain better performance, use EXISTS instead of IN.
BONUS: SQL Server: JOIN vs IN vs EXISTS - the logical difference
SET NOCOUNT ON Use it in a stored proc. It reduces network traffic by eliminating sending "done" messages to client.
@@ROWCOUNT It returns number of rows affected by the last statement.
RETURN It is usually used for returning status/error code. RETURN can be used to exit from a stored proc. Any statements that follow RETURN are not executed.
@@ERROR It returns the error number for the last statement. Zero (0) means no errors.
ERROR_NUMBER() The result of ERROR_NUMBER() is the same as @@ERROR.
 -- Generate a divide-by-zero error.
 SELECT 1/0;
 -- All of these must be used inside CATCH.
  ERROR_NUMBER() AS ErrorNumber,
  ERROR_SEVERITY() AS ErrorSeverity,
  ERROR_STATE() AS ErrorState,
  ERROR_PROCEDURE() AS ErrorProcedure,
  ERROR_LINE() AS ErrorLine,
  ERROR_MESSAGE() AS ErrorMessage;

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.