SQL Server Code,Tips and Tricks, Performance Tuning

I am sitting in Kalen Delaney;s session on Locking, Blocking, Versions: Concurrency for Maximum Performance and had a great idea for a SQL S...

Create this table CREATE TABLE #bla (SomeVal uniqueidentifier) INSERT #bla VALUES ( 'D903D52D-DBFA-4904-9D95-F265152A391F' ) what ...

Without running this what do you think will be printed? SET ROWCOUNT 0 DECLARE @ int SET @ = 6 IF @@ROWCOUNT = 1 PRINT 'yes' E...

This one is a little sneaky, don’t send me hate mail for it. What does this return? SELECT ISNUMERIC ( '+' ), ISNUMERIC ( '–...

It has been a while since my last teaser but here we go What do you think the following returns? SELECT CONVERT ( datetime , '1/1/1...

This should trip up some people..... Without running this code what do you think will LEN and DATALENGTH return? DECLARE @i int SELECT @i ...

Here is a small simple Thanksgiving teaser. What do you think will the result be of the select count query? USE tempdb GO CREATE TABLE Cus...

Print the @SQL variable without using PRINT DECLARE @SQL varchar (49) SELECT @SQL = 'Print This Now ' + CONVERT ( VARCHAR (30), ...

What do you think will be the output? DECLARE @d datetime SET @d = '20071010' SELECT DATEADD (yy, DATEDIFF (yy, 0, @d)+1, -1) Aft...

Here is a small teaser, can you guess the output? SELECT d.c-d.b/d.a FROM ( SELECT 1,2,5)d(a,b,c)

What will be the outcome of this script? First we create a table with a total of 6000 bytes Next we increase col2 from 1000 to 2000 bytes, t...

Hi and welcome to another fascinating SQL summer teaser. Summer it is except inPrinceton where it was 50 degrees this week. There was no tea...

Stole this tease from my friend Mark . The teaser for this week is not really a teaser, this time you will have to write some code instead o...

Here is a fun teaser. NULLIF will return a null value if the two specified expressions are equivalent. So to give an example DECLARE @v var...

This one is not so much a teaser but it will show you what you can do in case you want to insert data in a logging table after a rollback oc...

Here is another quick teaser. What will be the values of the print statements? Try to guess it before running this code SET ANSI_DEFAULTS ...

Without running the following two blocks of code what do you think will the output be? /* code 1 SELECT GETDATE() GO */ SELECT GETDATE () /...

Okay one more quick teaser You have this date '2007-01-01 00:00:00.001' When adding 1 or 2 milliseconds to that date what will be th...

First create this table CREATE TABLE #DateMess (SomeDate datetime ) INSERT #DateMess VALUES ( '20070710' ) INSERT #DateMess VALUE...

Here is a simple teaser, BTW I assume you don't have a database named WasabiDb or do you? USE WasabiDb IF @@Error 0 PRINT 'db d...