SQL

SQL syntax

LIKE: _ for single character (as opposed to %)

Datetimes:

SELECT * FROM Orders WHERE OrderDate < '20080101' --other strings work but beware locale
SELECT CONVERT(DateTime, '20080101', 112) -- 103 is ddmmyyyy, 101 is mmddyyyy
SELECT DATEADD(DD, 7, GETDATE()) --7 days from today

Parameterized WHERE columns:

@Filter         nvarchar(256),
@FilterOn       nvarchar(256)
SELECT * FROM  USERS
WHERE (
 CASE 
   WHEN @FilterOn='UserID' THEN USERS.UserID 
   WHEN @FilterOn='UserRole' THEN USERS.Role 
   WHEN @FilterOn='Name' THEN USERS.Name 
   ELSE USERS.UserID
 END
  LIKE @Filter)

SQLServer2005 paging use ROW_NUMBER() OVER(ORDER BY col) as RowNum
Note you have to repeat the select list in the outer sql too.

SELECT ...
FROM
   (SELECT ... 
        , ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum
    FROM Employees e)
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

Multi-table selects can deadlock against row-level update locks.
- In Sql2000 and Oracle, use dirty reads (or see below for 2005's snapshot)
```
SELECT * FROM USERS WITH (NOLOCK)
```
  using (TransactionScope tscope =
  new TransactionScope(TransactionScopeOption.Required,
  new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted } ))
- In Sql2005+, avoid deadlocks with snapshot isolation (which must be enabled first)
```
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
```
  using (var tscope =
  new TransactionScope(TransactionScopeOption.Required,
  new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Snapshot }))

Management Notes

DON'T PREFIX SPROCS WITH "SP_". That looks them up in master database first. A common error.
Command Line stopping: NET STOP MSSQLSERVER /Y (the Y is to stop SQLServerAgent too).
I have to do this to free up memory for VPCs.
Backup/restore across machines- use sp_change_users_login 'Update-One' 'user1', 'user1' to fix the database user to the server user

To get Identity after an insert:

INSERT INTO [Categories] (CategoryName) VALUES ('Random');
--use @@IDENTITY 
SET @iid = @@IDENTITY;
--SCOPE_IDENTITY() is safer as triggers may be firing
SET @sid = SCOPE_IDENTITY();

BCP: to bulk export a table:

--export
bcp database.dbo.MYTABLE out myfile.bcp -N -T

--import
sqlcmd -ddatabase -Q"delete from dbo.MYTABLE"
bcp database.dbo.MYTABLE in myfile.bcp -N -T

--with mixed mode authentication, append 
-Sserver_name\instance_name -Umylogin -Pmypassword

Rebuild all indexes (2005 version, with 2000 version commented out)

DECLARE @TableName nvarchar(255)
DECLARE @sql nvarchar(255)

DECLARE TableCursor CURSOR FOR
SELECT  table_schema + '.' + table_name FROM information_schema.tables
WHERE table_type = 'base table' Order by table_name 

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--DBCC DBREINDEX(@TableName,' ',90)
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD' 
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Shrinking the log file

static void

SQL

Links

SQL syntax

Management Notes