SQL Pass Summit... SQL Teaser Transactions

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

Without running it, what will printed when the following two blocks of code are run?

BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 COMMIT
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)


GO

-----------------------

BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 ROLLBACK
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)

Now run it.... Were you right?

What about the code below, what will happen if you run these two code blocks? Does it matter that the transactions are created in separate batches?

BEGIN TRAN
GO 7

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 COMMIT
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)


GO

-----------------------
BEGIN TRAN
GO 7

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 ROLLBACK
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)

SQL Teaser uniqueidentifier

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

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

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

Yet Another Date Teaser

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

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)

Thanksgiving SQL Teaser COUNT

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 Customer (CustomerID INT PRIMARY KEY)
INSERT Customer VALUES (1)
INSERT Customer VALUES (2)
INSERT Customer VALUES (3)
INSERT Customer VALUES (4)

SELECT COUNT(*) Customer

SQL Teaser: Printing Without Using PRINT

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: Guess the output

What do you think will be the output?


DECLARE @d datetime
SET @d = '20071010'

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

After that run this to see how to get first and last days for years, quarters, months and weeks(be careful of ISO weeks!)

DECLARE @d datetime
SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d), 0) as FirstDayOfYear,
DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) as LastDayOfYear,
DATEADD(qq, DATEDIFF(qq, 0, @d), 0) as FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) as LastDayOfQuarter,
DATEADD(mm, DATEDIFF(mm, 0, @d), 0) as FirstDayOfMonth,
DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) as LastDayOfMonth,
DATEADD(wk, DATEDIFF(wk, 0, @d), 0) as FirstDayOfWeek,
DATEADD(wk, DATEDIFF(wk, 0, @d)+1, -1) as LastDayOfWeek

SQL Teaser: Guess the output

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)

SQL Teaser PASS Special: Table Size

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, this will give us a total of 7000 bytes
Finally we add col3 which has 1000 bytes, this will give us a total of 8000 bytes

First run these two statements
--Total size = 6000
CREATE TABLE TestSize (Col1 char(5000),col2 char(1000))
GO

--total size = 7000
ALTER TABLE TestSize
ALTER COLUMN col2 char(2000)
GO

Now what do you think will happen when you run this?

--total size should be 8000 bytes (5000 + 2000 + 1000)
ALTER TABLE TestSize
ADD Col3 char(1000)
GO

Now for bonus points. What book have I been reading.

Hint: the author is at PASS

Summer SQL Teaser #13 Numeric

Hi and welcome to another fascinating SQL summer teaser. Summer it is except inPrinceton where it was 50 degrees this week.
There was no teaser last week because of a death in the family, I had to go to a wake and a funeral last week. That is why the teaser will be posted on a Thursday this week ;-)



look at these values

$55.69
1.4e35
2d4
3.7
412

How many numeric values do you see? What do you think SQL Server's ISNUMERIC function will return for those values?

Let's find out, run the following code

CREATE TABLE #Temp (Data varchar(18))

INSERT INTO #Temp VALUES('$55.69')
INSERT INTO #Temp VALUES('1.4e35')
INSERT INTO #Temp VALUES('2d4')
INSERT INTO #Temp VALUES('3.7')
INSERT INTO #Temp VALUES('412')
INSERT INTO #Temp VALUES(CHAR(9)) --tab

Now without running this try to guess which values will be 1 and which 0. I added a bonus ISNUMERIC(ColumnName + 'e0') function. What do you think that will do? Remember first try to guess and then run the code. Any surprises?

SELECT Data,
ISNUMERIC(Data) AS [IsNumeric],
ISNUMERIC(Data + 'e0') AS IsReallyNumeric
FROM #Temp

Summer SQL Teaser #12 Missing Dates

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 of guessing/knowing.
First create this table of numbers


SET NOCOUNT ON
CREATE TABLE numbers(num int primary key)
DECLARE @l int
SELECT @l =0
WHILE @l <= 1000 BEGIN
INSERT numbers VALUES(@l)
SET @l = @l + 1
END
GO

Below is a table, return all the dates for July 2007 where Joe does not have a LoginDate entry. So in this case the output is 28 rows

DECLARE @LOGIN TABLE (id int IDENTITY(1,1), Name varchar(20), LoginDate datetime )
INSERT @LOGIN VALUES ('Joe', '20070701')
INSERT @LOGIN VALUES ('Joe', '20070710')
INSERT @LOGIN VALUES ('Joe', '20070720')
INSERT @LOGIN VALUES (Fred, '20070702')
INSERT @LOGIN VALUES (Fred, '20070707')
INSERT @LOGIN VALUES (Fred, '20070711')


Expected output

Joe 2007-07-02 00:00:00.000
Joe 2007-07-03 00:00:00.000
Joe 2007-07-04 00:00:00.000
Joe 2007-07-05 00:00:00.000
Joe 2007-07-06 00:00:00.000
Joe 2007-07-07 00:00:00.000
Joe 2007-07-08 00:00:00.000
Joe 2007-07-09 00:00:00.000
Joe 2007-07-11 00:00:00.000
Joe 2007-07-12 00:00:00.000
Joe 2007-07-13 00:00:00.000
Joe 2007-07-14 00:00:00.000
Joe 2007-07-15 00:00:00.000
Joe 2007-07-16 00:00:00.000
Joe 2007-07-17 00:00:00.000
Joe 2007-07-18 00:00:00.000
Joe 2007-07-19 00:00:00.000
Joe 2007-07-21 00:00:00.000
Joe 2007-07-22 00:00:00.000
Joe 2007-07-23 00:00:00.000
Joe 2007-07-24 00:00:00.000
Joe 2007-07-25 00:00:00.000
Joe 2007-07-26 00:00:00.000
Joe 2007-07-27 00:00:00.000
Joe 2007-07-28 00:00:00.000
Joe 2007-07-29 00:00:00.000
Joe 2007-07-30 00:00:00.000
Joe 2007-07-31 00:00:00.000

Your task is to write code that will return that resultset.
1) you have to use the number table I provided
2) you cannot use a calendar table

Shortest code wins, I got mine down to 207 characters (and no, it is not all in 1 line)

I will post my code tomorrow

Summer SQL Teaser #11 NULLIF

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 varchar
SET @v = ' '

SELECT NULLIF(@v,' ')

That returned NULL because @v and ' ' are the same


Now run this first

CREATE TABLE #j (n varchar(15))


DECLARE @a int
SET @a = 1
WHILE @a <= 1000 BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END


Then without running try to guess if the following query will return any rows

SELECT * FROM #j WHERE n = ' '

Summer SQL Teaser #10 ROLLBACK

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 occurs
Without running this try to guess what the counts of the three tables will be after the rollback


CREATE TABLE Test (id int)
CREATE TABLE #Test (id int)
DECLARE @Test table (id int)



BEGIN TRAN
INSERT INTO @Test VALUES(1)
INSERT INTO Test VALUES(1)
INSERT INTO #Test VALUES(1)
ROLLBACK TRAN


SELECT '@test',COUNT(*) FROM @Test
SELECT ' test',COUNT(*) FROM Test
SELECT '#test',COUNT(*) FROM #Test


DROP TABLE Test,#Test

Summer SQL Teaser #9 @@TRANCOUNT

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 ON

PRINT '#1 == ' + LTRIM(STR(@@TRANCOUNT))

BEGIN TRANSACTION

PRINT '#2 == ' + LTRIM(STR(@@TRANCOUNT))

ROLLBACK

Summer SQL Teaser #8 Comments And Go

Without running the following two blocks of code what do you think will the output be?


/* code 1

SELECT GETDATE()

GO */

SELECT GETDATE()



/* code 2

SELECT GETDATE()

GO

*/

SELECT GETDATE()

Summer SQL Teaser Datetime Yet Again

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 the result?

SELECT DATEADD(ms,1,CONVERT(datetime, '2007-01-01 00:00:00.001'))

SELECT DATEADD(ms,2,CONVERT(datetime, '2007-01-01 00:00:00.001'))

Summer SQL Teaser: Datetime

First create this table

CREATE TABLE #DateMess (SomeDate datetime)
INSERT #DateMess VALUES('20070710')
INSERT #DateMess VALUES('20070711')
INSERT #DateMess VALUES('20070712')
INSERT #DateMess VALUES('20070713')


This should be easy for most people, but not everyone knows this.
Without running the query do you know how many rows you will get back from the query

SELECT *
FROM #DateMess
WHERE SomeDate <= '2007-07-12 23:59:59.999'


I created this teaser because of a response that Celko made here:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/345a73f93cf6a684/

Summer SQL Teaser Non Existing Database

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 doesn''t exist'


USE WasabiDb
GO
IF @@Error <> 0
PRINT 'db doesn''t exist'

If you would run this in one shot (hit F5) how many of the error messages below will you see

Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WasabiDb'. No entry found with that name. Make sure that the name is entered correctly.


And how many 'db doesn't exist' messages will you see