Wednesday, August 20, 2008

At last September's BarCamp Brighton I saw the CSS Specificity Snap cards and this gave me an idea. So, armed with a concept of showing the different ways of producing the same output using the SQL Server 2005 flavour of SQL I started generating SQL statements. Alex was kind enough to make them pretty, and last week I ran a Madgex ILP session to play.

There are 26 cards in my pack, each card has a SQL statement, and a letter. The letter is used for the crib sheet to allow me to easily spot the matches.



I prepared by producing two piles of 13 cards, with a match in both piles. I then put one pile in the middle of the table face down, and dealt out the rest of the cards to the 5 people who were playing. I placed the 3 spare cards on the table face up so that we could all see them and proceeded to turn them over one by one. When a card was turned over everyone looked at the SQL on the overturned card, and checked their cards and the spare cards to see if there was a match. If a match wasn't spotted then I explained what the SQL was doing, and at only one point did I have to say which letter the matching card would have. During this I had quite a few comments about functions people didn't recognise - especially COALESCE and NULLIF. I also explained some of the performance, and functional differences between the SQL statements when they were seen - so for instance I explained the differences between DELETE and TRUNCATE but I still I have these as a match because they can provide the same result and provided an interesting talking point.

The second game we played was a memory game where I placed all of the cards on the table face down and everyone turned over 2 cards at a time. If the SQL on the cards resulted in the same output, then it was a match and they took the cards away. If they didn't then the cards got turned back over and the next person had a go.

All in all this session took about 30 minutes, and resulted in quite a lot of noise and laughter (so much so that someone came and closed the door of the room we were in).

The following table contains the Letter Code, SQL statement, and the matching Letter code. They are ordered so that the matches are grouped together.

Code	SQL	Match
V	SELECT CAST(GETDATE() AS VARCHAR(11))	G
G	SELECT CONVERT (VARCHAR(11), GETDATE())	V
Z	SELECT name FROM sysobjects WHERE xtype = 'P'	Q
Q	SELECT name FROM sys.procedures	Z
D	SELECT sysobjects.name, syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype = 'u'	A
A	SELECT sysobjects.name, syscolumns.name FROM sysobjects, syscolumns WHERE sysobjects.id = syscolumns.id AND sysobjects.xtype = 'u'	D
U	SELECT getdate()	O
O	EXEC('SELECT getdate()')	U
J	DELETE FROM Test	T
T	TRUNCATE TABLE Test	J
Y	SELECT ISNULL(NULL,1)	W
W	SELECT COALESCE(NULL,1)	Y
P	SELECT [name], xtype FROM sysobjects ORDER BY xtype	M
M	SELECT [name], xtype FROM sysobjects ORDER BY 2	P
H	SELECT NULLIF(1,1)	R
R	SELECT CASE 1 WHEN 1 THEN NULL ELSE 1 END	H
I	SELECT [name], xtype FROM sysobjects ORDER BY 1	N
N	SELECT [name], xtype FROM sysobjects ORDER BY [name]	I
X	SELECT CAST(GETDATE() AS VARCHAR(20))	F
F	SELECT CONVERT (VARCHAR(20), GETDATE())	X
S	SELECT name FROM syscolumns WHERE id = ( SELECT id FROM sysobjects WHERE xtype = 'u' AND name = 'Jobs' )	K
K	WITH objects (id) AS ( SELECT id FROM sysobjects WHERE xtype = 'u' AND name = 'Jobs' ) SELECT name FROM syscolumns INNER JOIN objects ON syscolumns.id = objects.id	S
L	INSERT INTO Job (JobID, PrimaryJobTypeID) VALUES (1,1)	B
B	INSERT INTO Job (JobID, PrimaryJobTypeID) SELECT 1,1	L
C	DECLARE @sMessage AS VARCHAR(20) SET @sMessage = 'Hello'	E
E	DECLARE @sMessage AS VARCHAR(20) SELECT @sMessage = 'Hello'	C

Labels: ilp, SQL, SQLServer2005

// posted by Jane @ 8:17 PM   

Comments:

Post a Comment