MS SQL compare string CASE INSENSITIVE / SENSITIVE

-- case insensitive
SELECT UserId, email
FROM aspnet_membership
WHERE email = 'biilg@microsoft.com' COLLATE SQL_Latin1_General_CP1_CI_AS

-- case sensitive
SELECT UserId, email
FROM aspnet_membership
WHERE email = 'billg@microsoft.com' COLLATE SQL_Latin1_General_CP1_CS_AS

Source:
http://aspadvice.com/blogs/ssmith/archive/2007/09/30/Case-Sensitive-or-Insensitive-SQL-Query.aspx