COALESCE - Returns the first non-NULL expression in its argument list. If all arguments are null, an exception is thrown.

DECLARE @BookId INT
...
SELECT Title FROM Book WHERE BookId = COALESCE(@BookId, BookId)

Format money using CONVERT (2 decimal places, commas for thousands):

DECLARE @Price MONEY
SET @Price=1206.2
SELECT CONVERT(VARCHAR, CAST(@Price AS MONEY), 1) -- output: 1,206.20

@@ROWCOUNT

-- Example #1
UPDATE Flags SET IsRead=1 WHERE DateIsRead IS NOT NULL
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' item(s) set as "Read"'
 
-- Example #2
SELECT @CustomerType=CustomerType FROM Customer WHERE LOWER(CustomerName)='bruce'
IF @@ROWCOUNT=0 SELECT @CustomerType=DefaultCustomerType FROM DefaultSettings

Different types of identity:

• @@IDENTITY - Returns the last generated identity value on the current connection for any table.
• SCOPE_IDENTITY() - Returns the last generated identity value on the current connection and the current scope.
• IDENT_CURRENT('table_name') - Returns the last generated identity value for the table passed as a parameter regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

-- Example #1:
DECLARE @ID INT
INSERT INTO Customer VALUES('Customer #1')
SET @ID = SCOPE_IDENTITY() -- get the automatically generated ID of the new customer record

Overriding the identity value:

SET IDENTITY_INSERT MyTable ON

NEWID() - Generates a GUID:

SELECT NEWID() -- result: C616F086-FC91-46B7-BBCF-2BCCE159E6A7