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. BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH -- All of these must be used inside CATCH. SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH;