Denis Gobo : Teaser

SQL Teaser Count That Trancount

Denis Gobo — Thu, 26 Feb 2009 21:15:00 GMT

Today's teaser is very simple

First create this table

CREATE 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 code

DECLARE @trancount int

SELECT @trancount = @@TranCount

INSERT #Tran (TranCountPassedIn) VALUES (@trancount)

We assigned @@TRANCOUNT to the @trancount variable and we passed that in, so TranCountDefault has the default value and TranCountPassedIn has the value we passed in

Now when you do a select from the table will the 2 columns have the same value?

SELECT * FROM #Tran

SQL Teaser: Missing FROM

Denis Gobo — Fri, 09 Jan 2009 18:30:00 GMT

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

CREATE TABLE TestQuery (TestID int)

SELECT * FROM information_schema.columns

WHERE column_name =( SELECT column_name

WHERE column_name = 'TestID')

SQL Teaser: Where Clause Gone Wild

Denis Gobo — Fri, 25 Jul 2008 18:24:00 GMT

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 NULL

AND r.FacilityID IN ( NULL ) )

OR ( NULL IS NULL ) )

AND --Filter on Inactive

ISNULL(r.inactive , 0) = 0

ORDER BY

ISNULL(g.LAST , '') + ISNULL(g.FIRST , '')

If you think I made that up, then you are in for a surprise. This is actually part of a query, I found it here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3662895&SiteID=1

This NULL IS NULL and NULL IS NOT NULL stuff is just killing me. I wonder why this person did not get an answer yet.....Enjoy your weekend :-)

SQL Teaser uniqueidentifier

Denis Gobo — Tue, 27 May 2008 18:37:00 GMT

Create this table

CREATE TABLE #bla (SomeVal uniqueidentifier)
INSERT #bla VALUES('D903D52D-DBFA-4904-9D95-F265152A391F')

What do you think this will return?

SELECT * FROM #bla
WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F12345678910'
UNION ALL
SELECT * FROM #bla
WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F1'

Surprised?

What about this?

SELECT * FROM #bla
WHERE SomeVal = CONVERT(uniqueidentifier,'D903D52D-DBFA-4904-9D95-F265152A391F12345678910')

SQL Teaser: @@ROWCOUNT

Denis Gobo — Thu, 08 May 2008 14:50:00 GMT

Without running this what do you think will be printed?

SET ROWCOUNT 0
DECLARE @ int
SET @ =6
IF @@ROWCOUNT = 1
PRINT 'yes'
ELSE
PRINT 'no'
PRINT @@rowcount

SQL Teaser: Some Things Are Not What They Seem

Denis Gobo — Fri, 25 Apr 2008 13:46:00 GMT

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

SQL Puzzle: Hours Stored As Integers

Denis Gobo — Mon, 14 Apr 2008 15:34:00 GMT

You have a table where hours are stores as integers and you need to display it in weeks, days and hours
If you have the following table

CREATE TABLE #Hours (hours int)
INSERT INTO #Hours
SELECT 5 UNION ALL
SELECT 55 UNION ALL
SELECT 125 UNION ALL
SELECT 1225 UNION ALL
SELECT 555 UNION ALL
SELECT 721 UNION ALL
SELECT 719

The expected output is this

Hours	weeks	days	hours
5	0	0	5
55	0	2	7
125	0	5	5
1225	7	2	1
555	3	2	3
721	4	2	1
719	4	1	23

Big deal right? So here is the deal, can you generate this output with code that is as short as possible. You do not need to alias the columns.

The code that I have is 50 characters.

George's Division Teaser

Denis Gobo — Thu, 17 Jan 2008 20:45:00 GMT

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 sqrt(1)=0

Select 'True' As Query2

Else

Select 'False' As Query2

The first one runs, the second one does not. It looks like the engine doesn't even look at the 1/0 because the 1/1 = 0 is false anyway. The second query is not using a constant but a function and the optimizer actually has to run the statement and it bombs out. So what do you think, am I right?

Now change the first query to this

If 1/0 = 10 or 1/1 = 0

Select 'True' As Query1

Else

Select 'False' As Query1

And? Does this also happen in other languages like this?

There is some more....:-) Switch the if statement around.

If 1/0 = 10 And sqrt(1)=0

Select 'True'

Else

Select 'False'

-- Same query with the order changed.

If sqrt(1)=0 And 1/0 = 10

Select 'True'

Else

Select 'False'

Now let's make the AND an OR

If sqrt(1)=1 or 1/0 = 10

Select 'True'

Else

Select 'False'

If 1/0 = 10 or sqrt(1)=1

Select 'True'

Else

Select 'False'

Very interesting indeed.

Yet Another Date Teaser

Denis Gobo — Thu, 10 Jan 2008 13:59:00 GMT

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 2008
SELECT

ISDATE('1/1/1'),

ISDATE('01/01/01'),

ISDATE('001/01/01'),

ISDATE('0001/01/01')

Now just for fun run these 4 on SQL Server 2008, one of them will fail so run them one by one

SELECT CONVERT(datetime2,'1/1/1')

SELECT CONVERT(datetime2,'01/01/01')

SELECT CONVERT(datetime2,'001/01/01')

SELECT CONVERT(datetime2,'0001/01/01')

Compare the isdate output to the select statement, see the inconsistency?

SQL Teaser: LEN vs DATALENGTH

Denis Gobo — Wed, 12 Dec 2007 13:56:00 GMT

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)

SQL Teaser: Printing Without Using PRINT

Denis Gobo — Fri, 09 Nov 2007 17:59:00 GMT

Print the @SQL variable without using PRINT

DECLARE @SQL varchar(49)

SELECT @SQL = 'Print This Now ' + CONVERT(VARCHAR(30), GETDATE())

--Your Code Here

SQL Teaser: Select

Denis Gobo — Wed, 24 Oct 2007 18:24:00 GMT

Without running this code try to guess what the values of @var1 and @var2 will be

CREATE TABLE #TeasMeNot(id int)

DECLARE @var1 int,@var2 int

SELECT @var1=0,@var2 = 0

SELECT @var1 =id FROM #TeasMeNot

SELECT @var2 = (SELECT id FROM #TeasMeNot)

--What is the value of @var1 and @var2?

SELECT @var1,@var2

DROP TABLE #TeasMeNot

Dot Doesn't Make Sense?

Denis Gobo — Thu, 18 Oct 2007 17:11:00 GMT

What will happen when you run this?

Create Table #Dots(Data VarChar(50))

Insert Into...#Dots Values('Huh?') --3 dots

Insert Into..#Dots Values('Huh, say what?') --2 dots

Select * From .#Dots -- 1 dot

Drop Table #Dots --look no dots!

SQL Teaser: Guess the output

Denis Gobo — Wed, 10 Oct 2007 14:46:00 GMT

What do you think will be the output?

DECLARE @d datetime
SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)

SQL Teaser: Guess the output

Denis Gobo — Mon, 01 Oct 2007 18:55:00 GMT

Here is a small teaser, can you guess the output?

SELECT d.c-d.b/d.a
FROM(SELECT 1 c,2 b,5 a)d(a,b,c)