SQL
Links
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 }))
- In Sql2000 and Oracle, use dirty reads (or see below for 2005's 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