Today's teaser is very simple First create this tableCREATE TABLE #Tran (TranCountDefault int DEFAULT (@@TranCount),TranCountPassedIn int)  As you can see that table has two columns, one column has a default of @@TRANCOUNT. Now run this piece of codeDECLARE @trancount intSELECT @trancount = @@TranCountINSERT #Tran (TranCountPassedIn) VALUES ...

Take a look at this code, will the select return anything or not? Look at the subquery and notice that there is no FROM there.   USE tempdb GO CREATE TABLE TestQuery (TestID int) GO   SELECT * FROM information_schema.columns WHERE column_name =( SELECT column_name WHERE column_name = 'TestID')

Try to guess what this WHERE clause is supposed to do.   WHERE r.ApptId IS NULL AND r.DATE >= ISNULL(NULL , '1/1/1900') AND r.DATE < DATEADD(d , 1 , ISNULL(NULL , '1/1/3000')) AND --Filter on resource ( ( NULL IS NOT NULL AND r.DoctorResourceID IN ( NULL ) ) OR ( NULL IS NULL ) ) AND --Filter on facility ( ( NULL IS NOT ...

Create this tableCREATE TABLE #bla (SomeVal uniqueidentifier)INSERT #bla VALUES('D903D52D-DBFA-4904-9D95-F265152A391F')What do you think this will return? SELECT * FROM #blaWHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F12345678910'UNION ALLSELECT * FROM #blaWHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F1'Surprised? What about ...

Without running this what do you think will be printed? SET ROWCOUNT 0DECLARE @ intSET @ =6IF @@ROWCOUNT = 1    PRINT 'yes'ELSE    PRINT 'no'PRINT @@rowcount

This one is a little sneaky, don’t send me hate mail for it. What does this return?   SELECT ISNUMERIC('+'),ISNUMERIC('–') Copy and paste it into QA/SSMS to verify  :-0    

You have a table where hours are stores as integers and you need to display it in weeks, days and hoursIf you have the following table CREATE TABLE #Hours (hours int)INSERT INTO #HoursSELECT 5 UNION ALLSELECT 55 UNION ALLSELECT 125 UNION ALLSELECT 1225 UNION ALLSELECT 555 UNION ALLSELECT 721 UNION ALLSELECT 719   The expected output is ...

This teaser was posted by my friend George on Tek Tips and I am posting it here after I asked for his permission. the main reason I am posting it here is because I want to get some opinions from the experts. Take a look at these two queries If 1/0 = 10 And 1/1 = 0 Select 'True' As Query1 Else Select 'False' As Query1   If 1/0 = 10 And ...

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') -CONVERT(datetime,1) + CONVERT(datetime,0)   How about this on SQL Server 2008 SELECT CONVERT(datetime2,'1/1/1'),CONVERT(datetime2,'01/01/01'),CONVERT(datetime2,'0001/01/01') Now run this on SQL Server ...

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