SQL Tips

Various SQL-oriented tips discovered during work, written down to remember them.

BCP

Export data to a file:

• bcp <dbname>..<tablename> out <filename>.dat /S<servername> /T /E /t# /c
• bcp "<select query, with <dbname>..<tablename>>" queryout <filename>.dat /S<servername> /T /E /t# /c

Import data from a file:

• sqlcmd -Q"DELETE FROM <tablename>" -S<servername> -d<dbname> -E
• bcp <dbname>..<tablename> in <filename>.dat /S<servername> /T /E /t# /c

Comma Separated List

Generate a comma-separated list of values in a table:

DECLARE @list NVARCHAR(MAX);
SELECT @list = COALESCE(@list + ',', '') + <column> FROM <table>;
PRINT @list;

Prevent Table Lock Escalation

Prevent the database from taking an exclusive table lock:

BEGIN TRANSACTION;
SELECT * FROM <table> WITH (XLOCK, HOLDLOCK) WHERE 1 = 2

Run this in a separate thread without doing a commit. It will prevent the database from escalating key/page locks to table locks.

Links

• Lock Types for User Data
• SQL Server 2005 Waiting and Blocking Issues