Dev 101


Query detail -
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

Cursor template / Cursor over tables
While loop template / While loop and table variable / Loop a date range

CTEs - Examples  |  MaxRecursion  |  SequencesFinding Sequence Gaps

Error Handling - TRY / CATCH

Fast Row Counts 

Instant Messages in Management Studio - RAISERROR ('my message', 10, 1) WITH NOWAIT

While loop for Incremental (nibble) DELETE / UPDATE

CASE - Basics  |  Conditional WHERE, LIKE, ORDER BY using CASE

Data Types - SSIS 2008 / SQL 2008 / .NET

Dates - date from datetime -
v1 - DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
v2 - SELECT CAST(DATEDIFF(D, 0, GETDATE()) AS DATETIME)
v3 - SELECT DATEADD(dd, DATEDIFF(dd, '1900-01-01', GETDATE()),'1900-01-01') -- prefered version, doesn't incur implicit conversions

date formatting - SELECT CONVERT(VARCHAR(30),GETDATE(),21)
see date formats here

custom date functions 

Numbers (decimal places) - SELECT CONVERT(DECIMAL(10,2),mynumber)

Searching Code

Schemas - changing schema of an object - ALTER SCHEMA newschema TRANSFER oldschema.tablename




101s -
Compression
Date Formatting
Email
Indexes
Network Debugging
Schemas
Service Broker
Storage
Temporary Tables



My SqlServerCentral Script Submissions