Unclosed quotation mark after the character string

Just a quick note, I had copied a query out and missed the final bracket in my query and thought it amusing this is the error message the SQL parser kicks back.  Given a query like this

SELECT * FROM dbo.[TABLE1

the parser will kick back the following error message

Msg 105, Level 15, State 1, Line 2

Unclosed quotation mark after the character string 'TABLE1

'.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'TABLE1

'.

SQL Saturday 53 Birds of a Feather luncheon

One of the things that I really enjoyed at Summit 2009 was the Birds of a Feather luncheon.  BoF was an optional activity but in the luncheon area they had set aside particular tables with table tents on them describing a topic and had a volunteer there to get the ball rolling, provide insight and what not.  It was easily one of my favorite activities there as it allowed me to meet folks and have easy material to get the conversation rolling.  Todd McDermid chaired one on SSIS scripts and custom objects and it was great because I had gotten to connect a face to one of the people that always seemed to answer msdn forums before I could put my $.02 in.   With that said and based on Andy Warren's suggestion to up the social/networking aspect of SQL Saturdays, plus the on-going twitter chat, I want to see if we can recreate that magic at my event, SQL Saturday 53.  Not only will it be an opportunity for free SQL Server training in the Kansas City area, it'll be an avenue for you to connect with other like-minded professionals and to grow your professional network. Below is a representation of what I started to come up with and then gave up and cribbed from the topics available at SQL Pass 2009.  If someone is interested in chairing a table on one of these topics or something else they'd want to anchor a discussion table on, please give me a shout out and we'll see what we can do. Table topics

SSIS

Query tuning

PD

Storage

Azure

SSRS

SSAS

security

HA

CLR

SSIS scripts and custom objects

Extended Events

Networking

Self-marketing/brand management

Independent consulting

DMO (DMF, DMV)

Agile development in BI

Data Mining

Data Warehouse concepts

DBA best practices

DBA dashboards

Don't be afraid of XML

Failover Clustering

Full text search and file stream

Getting started with TSQL

Query plan reuse

BI

Indexing

Multi-server management

CMS Central Management Server

Performance analysis/tuning

PBM Policy Based management

Powershell in SQL Server

Relational database design

Social Networking:  DBA to butterfly

SQL Azure

Storage best practices

Table Partitioning

Thinking in MDX

Troubleshooting SSIS

Virtualization

VSDB edition

PD

SQL Server 2005/2008 what's new, part 7

SQL Saturday 53, takes place in 41 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

TableSample

Tablesample is a hint to SQL Server to just grab some rows. Which rows? If you're asking "which rows", tablesample isn't for you. No, tablesample is a hint to SQL Server that you're in a lascivious mood and you just want some data now. It's clean data, no dirty reads but you might get some really odd, inconsistent results if you try to do things like apply filtering. Best use I've found so far is quasi-psuedo-random sampling of data. It's not random enough for true random so don't treat it as such but if you just want a drink of the data in the table, it's probably "good enough," assuming you remember the syntax.

The TABLESAMPLE clause is a different sort of animal. Conceptually, one may think it's nothing more than an ugly synonym for TOP or LIMIT, if you're using a different RDBMS vendor. However, from a query processing perspective, TOP can't run until after the query has been run through the wringer which for large tables might negate any performance savings. At least based on my chapter 1 understanding of logical query processing. It's an odd duck because it either includes a page of information or it doesn't. The query plan will always indicate it's a table scan but it will only read from disk if the selected pages, something something. At this point, read the BOL article above because we are unfortunately way beyond my SQL Server knowledge zone.

Calls to tablesample can either be deterministic or non-deterministic based on whether the REPEATABLE option is used. Activity in the database that affects data pages may also affect the deterministic-ness of your REPEATABLE option. These activities include "inserting, updating, deleting, index rebuilding, index defragmenting, restoring a database, and attaching a database" Consult your DBA if you experience eye watering, mouth watering, flower watering or if your queries last for more than 4 hours.

Syntax

TABLESAMPLE is a clause in your FROM statement. From BOL, the syntax is TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ])[REPEATABLE (repeat_seed)]. As tablesample requires real tables, none of the jiggered up examples I have used thus far will work. Rather than forcing you to have adventureworks installed, I figured querying against master.dbo.spt_values seemed a safe table to assume will exist on your machine. What I wasn't ready for, was seeing the caveat about pages of data and maybe you won't get anything back. The other surprise was just how variable "10 rows" can be in result sets. In the handful of times I reran, I saw sets ranging from empty, to 47 to 149. Changing it from rows to percent didn't really dial back the variability like I'd have thought based on this statement in BOL "When a number of rows is specified, instead of a percentage based on the total number of rows in the table, that number is converted into a percentage of the rows and, therefore, pages that should be returned. The TABLESAMPLE operation is then performed with that computed percentage." I still saw some pretty big swings from 0 to 61 to 358 rows.

SELECT COUNT(1) rc FROM master.dbo.spt_values T

rc
2508

SELECT * FROM master.dbo.spt_values T tablesample (10 rows)

Invocation 1

name	number	type	low	high	status

Invocation 2

name	number	type	low	high	status
ROUTE	21586	EOB	NULL	NULL	0
STATISTICS	21587	EOB	NULL	NULL	0
ASYMMETRIC KEY USER	21825	EOB	NULL	NULL	0
CERTIFICATE USER	21827	EOB	NULL	NULL	0
GROUP USER	21831	EOB	NULL	NULL	0
SQL USER	21843	EOB	NULL	NULL	0
WINDOWS USER	21847	EOB	NULL	NULL	0
SERVICE	22099	EOB	NULL	NULL	0
INDEX	22601	EOB	NULL	NULL	0
LOGIN	22604	EOB	NULL	NULL	0
XML SCHEMA COLLECTION	22611	EOB	NULL	NULL	0
TYPE	22868	EOB	NULL	NULL	0
SERVER AUDIT	8257	EOD	NULL	NULL	0
CHECK CONSTRAINT	8259	EOD	NULL	NULL	0
DEFAULT	8260	EOD	NULL	NULL	0
FOREIGN KEY CONSTRAINT	8262	EOD	NULL	NULL	0
STORED PROCEDURE	8272	EOD	NULL	NULL	0
RULE	8274	EOD	NULL	NULL	0
TABLE SYSTEM	8275	EOD	NULL	NULL	0
TRIGGER SERVER	8276	EOD	NULL	NULL	0
TABLE	8277	EOD	NULL	NULL	0
VIEW	8278	EOD	NULL	NULL	0
STORED PROCEDURE EXTENDED	8280	EOD	NULL	NULL	0
DATABASE AUDIT SPECIFICATION	16708	EOD	NULL	NULL	0
SERVER AUDIT SPECIFICATION	16723	EOD	NULL	NULL	0
TRIGGER ASSEMBLY	16724	EOD	NULL	NULL	0
DATABASE	16964	EOD	NULL	NULL	0
OBJECT	16975	EOD	NULL	NULL	0
FULLTEXT CATALOG	17222	EOD	NULL	NULL	0
STORED PROCEDURE ASSEMBLY	17232	EOD	NULL	NULL	0
SCHEMA	17235	EOD	NULL	NULL	0
CREDENTIAL	17475	EOD	NULL	NULL	0
EVENT NOTIFICATION SERVER	17491	EOD	NULL	NULL	0
EVENT SESSION	17747	EOD	NULL	NULL	0
AGGREGATE	17985	EOD	NULL	NULL	0
FUNCTION TABLE-VALUED INLINE SQL	17993	EOD	NULL	NULL	0
PARTITION FUNCTION	18000	EOD	NULL	NULL	0
STORED PROCEDURE REPLICATION FILTER	18002	EOD	NULL	NULL	0
FUNCTION TABLE-VALUED SQL	18004	EOD	NULL	NULL	0
RESOURCE GOVERNOR	18258	EOD	NULL	NULL	0
SERVER ROLE	18259	EOD	NULL	NULL	0
WINDOWS GROUP	18263	EOD	NULL	NULL	0
ASYMMETRIC KEY	19265	EOD	NULL	NULL	0
DATABASE ENCRYPTION KEY	19268	EOD	NULL	NULL	0
MASTER KEY	19277	EOD	NULL	NULL	0
PRIMARY KEY	19280	EOD	NULL	NULL	0
SYMMETRIC KEY	19283	EOD	NULL	NULL	0

What to notice

The rows or percentage supplied to TABLESAMPLE has some fairly wild variability based on table width and how it physically got laid onto disk. If you need to provide an upper bound to that variability, use tablesample with TOP. Don't trust REPEATABLE unless you are working against a read-only database.

Availability

SQL Server 2005+

SQL Server 2005/2008 what's new, part 6

SQL Saturday 53, takes place in 42 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

NTILE()

The NTILE function is the fourth of four windowing functions introduced in SQL Server 2005. NTILE takes a different approach to paritioning data. ROW_NUMBER, RANK and DENSE_RANK will generate variable sized buckets of data based on the partition key(s). NTILE attempts to split the data into equal, fixed size buckets. BOL has a comprehensive page comparing the ranking functions if you want a quick visual reference on their effects.

Syntax

The syntax for NTILE differs slightly from the other window functions. It's NTILE(@BUCKET_COUNT) OVER ([PARTITION BY _] ORDER BY _) , where @BUCKET_COUNT is a positive integer or bigint value. Using a fast number generator calendar, I added a call to NTILE() based on event date to introduce a rank column.

-- Example of using NTILE to split 30 numbers
-- into 10 and 4 equal sized buckets
SELECT
    GN.number
,   NTILE(10) OVER (ORDER BY GN.number) AS thirds
,   NTILE(4) OVER (ORDER BY GN.number) AS quartile
FROM 
    dbo.GenerateNumbers(30) GN

number	thirds	quartile
1	1	1
2	1	1
3	1	1
4	2	1
5	2	1
6	2	1
7	3	1
8	3	1
9	3	2
10	4	2
11	4	2
12	4	2
13	5	2
14	5	2
15	5	2
16	6	2
17	6	3
18	6	3
19	7	3
20	7	3
21	7	3
22	8	3
23	8	3
24	8	4
25	9	4
26	9	4
27	9	4
28	10	4
29	10	4
30	10	4

What to notice

The, backwardsly named, thirds column was evenly partitioned with the NTILE function. As expected, ten buckets were created and each hold 3 sets of numbers (3 * 10 = 30). Quartile column as expected was 4 buckets of 8, or is it 4 buckets of 7? 4 * 8 = 32; 4 * 7 = 28. Neither is the correct answer. In cases where the rows can't be broken out into even buckets, NTILE will fill from the top down so there is at most a 1 count difference between the first and last bucket.

Availability

SQL Server 2005+

SQL Server 2005/2008 what's new, part 5

SQL Saturday 53, takes place in 43 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

DENSE_RANK()

The DENSE_RANK function is the third of four windowing functions introduced in SQL Server 2005. DENSE_RANK is nearly identical to RANK except that in the case of a tie or duplicate values, DENSE_RANK will leave no gaps in numbering.

Syntax

The syntax for DENSE_RANK is identical to RANK(). It's DENSE_RANK() OVER (ORDER BY _) , parenthesis required. Using the upcoming SQL Saturday calendar, I added a call to DENSE_RANK() based on event date to introduce a rank column.

-- This query demonstrates the use of the DENSE_RANK function
-- Notice the ranks for rows with same key (event_date) are 
-- ranked equally and the subsequent rank number leaves
-- no gaps in the numbers.
;
WITH SQL_SATURDAY (event_date, event_name, event_location) AS
(
SELECT CAST('Aug 21, 2010' as datetime), 'SQLSaturday #51', 'Nashville 2010'
UNION ALL SELECT 'Sep 18, 2010', 'SQLSaturday #46', 'Raleigh 2010'
UNION ALL SELECT 'Sep 18, 2010', 'SQLSaturday #50', 'East Iowa 2010'
UNION ALL SELECT 'Sep 18, 2010', 'SQLSaturday #55', 'San Diego 2010'
UNION ALL SELECT 'Sep 25, 2010', 'SQLSaturday #52', 'Colorado 2010'
UNION ALL SELECT 'Oct 2, 2010', 'SQLSaturday #53', 'Kansas City 2010'
UNION ALL SELECT 'Oct 2, 2010', 'SQLSaturday #48', 'Columbia 2010'
UNION ALL SELECT 'Oct 16, 2010', 'SQLSaturday #49', 'Orlando 2010'
UNION ALL SELECT 'Oct 23, 2010', 'SQLSaturday #54', 'Salt Lake City 2010'
UNION ALL SELECT 'Oct 23, 2010', 'SQLSaturday #56', 'Dallas (BI Edition) 2010'
UNION ALL SELECT 'Oct 29, 2010', 'SQLSaturday #58', 'Minnesota 2010'
UNION ALL SELECT 'Nov 20, 2010', 'SQLSaturday #59', 'New York City 2010'
UNION ALL SELECT 'Jan 22, 2011', 'SQLSaturday #45', 'Louisville 2011'
UNION ALL SELECT 'Jan 29, 2011', 'SQLSaturday #57', 'Houston 2011'
UNION ALL SELECT 'Feb 5, 2011', 'SQLSaturday #47', 'Phoenix 2011'
)
SELECT
    SS.* 
,   DENSE_RANK() OVER (ORDER BY SS.event_date ASC) AS zee_dense_rank
FROM 
    SQL_SATURDAY SS

event_date	event_name	event_location	zee_dense_rank
2010-08-21	SQLSaturday #51	Nashville 2010	1
2010-09-18	SQLSaturday #50	East Iowa 2010	2
2010-09-18	SQLSaturday #55	San Diego 2010	2
2010-09-18	SQLSaturday #46	Raleigh 2010	2
2010-09-25	SQLSaturday #52	Colorado 2010	3
2010-10-02	SQLSaturday #48	Columbia 2010	4
2010-10-02	SQLSaturday #53	Kansas City 2010	4
2010-10-16	SQLSaturday #49	Orlando 2010	5
2010-10-23	SQLSaturday #56	Dallas (BI Edition) 2010	6
2010-10-23	SQLSaturday #54	Salt Lake City 2010	6
2010-10-29	SQLSaturday #58	Minnesota 2010	7
2010-11-20	SQLSaturday #59	New York City 2010	8
2011-01-22	SQLSaturday #45	Louisville 2011	9
2011-01-29	SQLSaturday #57	Houston 2011	10
2011-02-05	SQLSaturday #47	Phoenix 2011	11

What to notice

I created a new column called "zee_dense_rank" with the invocation of the DENSE_RANK function. Because I ordered it by dates, and we have 3 events all on 2010-09-18, the rank starts at 1 for Nashville represent! East Iowa, San Diego and Raleigh battled it out for supremacy on 2010-09-18 and it was a three way tie at 2 which resulted in Colorado being ranked 3rd versus 5th if RANK had been used.

Availability

SQL Server 2005+

SQL Server 2005/2008 what's new, part 4

SQL Saturday 53, takes place in 44 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

RANK()

The RANK function is the second of four windowing functions introduced in SQL Server 2005. RANK is nearly identical to ROW_NUMBER except that in the case of a tie or duplicate values, ROW_NUMBER will select one as coming first. RANK is a New Age, granola loving function that says "you're all winners in my book" and gives all the matches the same value. RANK keeps track of how many ties for Nth place occur and adjusts the next number accordingly.

Syntax

The syntax for RANK is identical to ROW_NUMBER(). It's RANK() OVER (ORDER BY _) , parenthesis required. Using the upcoming SQL Saturday calendar, I added a call to RANK() based on event date to introduce a rank column.

-- This query demonstrates the use of the RANK function
-- Notice the ranks for rows with same key (event_date) are 
-- ranked equally and the subsequent rank number behaves
-- like an identity insert that was rolled back, i.e. gaps
-- in the meaningless identifiers that someone (QA) will question
;
WITH SQL_SATURDAY (event_date, event_name, event_location) AS
(
SELECT CAST('Aug 21, 2010' as datetime), 'SQLSaturday #51', 'Nashville 2010'
UNION ALL SELECT 'Sep 18, 2010', 'SQLSaturday #46', 'Raleigh 2010'
UNION ALL SELECT 'Sep 18, 2010', 'SQLSaturday #50', 'East Iowa 2010'
UNION ALL SELECT 'Sep 18, 2010', 'SQLSaturday #55', 'San Diego 2010'
UNION ALL SELECT 'Sep 25, 2010', 'SQLSaturday #52', 'Colorado 2010'
UNION ALL SELECT 'Oct 2, 2010', 'SQLSaturday #53', 'Kansas City 2010'
UNION ALL SELECT 'Oct 2, 2010', 'SQLSaturday #48', 'Columbia 2010'
UNION ALL SELECT 'Oct 16, 2010', 'SQLSaturday #49', 'Orlando 2010'
UNION ALL SELECT 'Oct 23, 2010', 'SQLSaturday #54', 'Salt Lake City 2010'
UNION ALL SELECT 'Oct 23, 2010', 'SQLSaturday #56', 'Dallas (BI Edition) 2010'
UNION ALL SELECT 'Oct 29, 2010', 'SQLSaturday #58', 'Minnesota 2010'
UNION ALL SELECT 'Nov 20, 2010', 'SQLSaturday #59', 'New York City 2010'
UNION ALL SELECT 'Jan 22, 2011', 'SQLSaturday #45', 'Louisville 2011'
UNION ALL SELECT 'Jan 29, 2011', 'SQLSaturday #57', 'Houston 2011'
UNION ALL SELECT 'Feb 5, 2011', 'SQLSaturday #47', 'Phoenix 2011'
)
SELECT
    SS.* 
,   RANK() OVER (ORDER BY SS.event_date ASC) AS zee_rank
FROM 
    SQL_SATURDAY SS

event_date	event_name	event_location	zee_rank
2010-08-21	SQLSaturday #51	Nashville 2010	1
2010-09-18	SQLSaturday #50	East Iowa 2010	2
2010-09-18	SQLSaturday #55	San Diego 2010	2
2010-09-18	SQLSaturday #46	Raleigh 2010	2
2010-09-25	SQLSaturday #52	Colorado 2010	5
2010-10-02	SQLSaturday #48	Columbia 2010	6
2010-10-02	SQLSaturday #53	Kansas City 2010	6
2010-10-16	SQLSaturday #49	Orlando 2010	8
2010-10-23	SQLSaturday #56	Dallas (BI Edition) 2010	9
2010-10-23	SQLSaturday #54	Salt Lake City 2010	9
2010-10-29	SQLSaturday #58	Minnesota 2010	11
2010-11-20	SQLSaturday #59	New York City 2010	12
2011-01-22	SQLSaturday #45	Louisville 2011	13
2011-01-29	SQLSaturday #57	Houston 2011	14
2011-02-05	SQLSaturday #47	Phoenix 2011	15

What to notice

I created a new column called "zee_rank" with the invocation of the RANK function. Because I ordered it by dates, and we have 3 events all on 2010-09-18, the rank starts at 1 for Nashville represent! East Iowa, San Diego and Raleigh battled it out for supremacy on 2010-09-18 and it was a three way tie at 2 which resulted in Colorado being ranked 5th.

Availability

SQL Server 2005+

SQL Server 2005/2008 what's new, part 3

SQL Saturday 53, takes place in 45 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

ROW_NUMBER()

The ROW_NUMBER function is one of four windowing functions introduced in SQL Server 2005. ROW_NUMBER() is a monotomically increasing function for each partition within a query. That's a fancy way of saying starting at 1, add 1 for every row you encounter. The partition is simply the signal to start counting over.

Syntax

The syntax simple, it's ROW_NUMBER() OVER (ORDER BY _) , parenthesis required. Using the query from the CTE introduction, I added a call to row_number() to introduce a sequential row number column.

-- This query demonstrates the ROW_NUMBER function 
-- along with partitioning
;
WITH BORDER_STATES (state_name, abbreviation) AS
(
    SELECT 'IOWA', 'IA'
    UNION ALL SELECT 'NEBRASKA', 'NE'
    UNION ALL SELECT 'OKLAHOMA', 'OK'
    UNION ALL SELECT 'KANSAS', 'KS'
    UNION ALL SELECT 'ILLINOIS', 'IL'
    UNION ALL SELECT 'KENTUCKY', 'KY'
    UNION ALL SELECT 'TENNESSEE', 'TN'
)
, BEST_STATE AS
(
    SELECT 'MISSOURI' AS state_name, 'MO' AS state_abbreviation
)
, JOINED AS
(
    SELECT M.*, 1 AS state_rank FROM BEST_STATE M
    UNION
    SELECT BS.*, 2 AS state_rank FROM BORDER_STATES BS
)
SELECT J.*, ROW_NUMBER() OVER (PARTITION BY J.state_rank ORDER BY J.state_rank) AS zee_row_number
FROM JOINED J

state_name	state_abbreviation	state_rank	zee_row_number
MISSOURI	MO	1	1
IOWA	IA	2	1
NEBRASKA	NE	2	2
OKLAHOMA	OK	2	3
KANSAS	KS	2	4
ILLINOIS	IL	2	5
KENTUCKY	KY	2	6
TENNESSEE	TN	2	7

What to notice

I created a new column called "zee_row_number" with the invocation of the ROW_NUMBER function. I partitioned my results on the state_rank which lead to my data being segmented into two sets, state_rank 1 and 2. Missouri being the only element in its set is assigned zee_row_number of 1. Within the second set, Iowa was selected as the first element of that set. Your mileage may vary. After that, the other 6 rows had their zee_row_number incremented by 1.

Availability

SQL Server 2005+

SQL Server 2005/2008 what's new part 2

SQL Saturday 53, takes place in 46 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

Recursive Common Table Expressions

Recursive CTEs are like a normal Common Table Expressions, just with a little something extra to them. By default, you can have 100 levels of recursion before it chokes. Vast improvement over the 32 levels of default recursion allowed with procedure, function, trigger or nested views.

Syntax

The syntax is quite simple, see books online for the BNF. The following contrived queries count down from 100 and 101 to 0, endpoints inclusive.

-- This query demonstrates recursion by counting down from 100 to 0
;
WITH BASE AS
(
    -- Anchor query
    SELECT 100 AS anchor
    
    -- recursive query
    -- notice that I can reference BASE
    UNION ALL
    SELECT B.anchor -1 FROM BASE B WHERE B.anchor > 0
    
)
SELECT B.* FROM BASE B

-- This query demonstrates recursion by counting down from 101 to 0
-- The MAXRECURSION hint allows us to override the default for good or ill
;
WITH BASE AS
(
    -- Anchor query
    SELECT 101 AS anchor
    
    -- recursive query
    -- notice that I can reference BASE
    UNION ALL
    SELECT B.anchor -1 FROM BASE B WHERE B.anchor > 0
    
)
SELECT B.* 
FROM BASE B 
OPTION (MAXRECURSION 101)

What to notice

Recursive queries can be split into their non-recursive or anchor portion and the recursive portion. The anchor query which can be as complex as need be but it must is evaluated before the recursive portion begins. The recursive will execute until it either overflows the stack or returns an empty set a.k.a. meets the terminal condition. The default maximum recursion level for a CTE is 100 frames. The absolute maximum level of CTE recursion is 32,767 . If you're hitting this limit, congratulations. Now, rethink your query.

Availability

SQL Server 2005+

SQL Server 2005/2008 what's new, part 1

SQL Saturday 53, takes place in 47 days. I will be presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

Common Table Expressions

I like to come out swinging and Common Table Expressions, CTE, introduced with SQL Server 2005 are worth the price of admission! At their simplest, they're nothing more than syntactic sugar but don't dismiss them as such. There is very little you can do with CTEs that you couldn't do with temporary tables, derived tables, table variables and what have you. The biggest difference developing queries using CTEs versus the Tumbling data anti-pattern is that with a Common Table Expression the compiler can have a chance to optimize your query despite being separated into logic sub tables. Think of them as single use tables with no explicit cleanup required.

Syntax

The syntax is quite simple, see books online for the BNF. The following contrived query builds up data to define the states that border Missouri.

;
WITH BORDER_STATES (state_name, abbreviation) AS
(
    SELECT 'IOWA', 'IA'
    UNION ALL SELECT 'NEBRASKA', 'NE'
    UNION ALL SELECT 'OKLAHOMA', 'OK'
    UNION ALL SELECT 'KANSAS', 'KS'
    UNION ALL SELECT 'ILLINOIS', 'IL'
    UNION ALL SELECT 'KENTUCKY', 'KY'
    UNION ALL SELECT 'TENNESSEE', 'TN'
)
, BEST_STATE AS
(
    SELECT 'MISSOURI' AS state_name, 'MO' AS state_abbreviation
)
, JOINED AS
(
    SELECT M.*, 1 AS state_rank FROM BEST_STATE M
    UNION
    SELECT BS.*, 2 AS state_rank FROM BORDER_STATES BS
)
SELECT * FROM JOINED

What to notice

As a helpful suggestion, preface all your CTEs with a semi-colon on the preceding line. If they are the only statement in a batch, then it doesn't matter. Otherwise, you'll encounter "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon." BORDER_STATES is an in memory table containing the 8 states that directly border Missouri. Notice that I explicitly specify the column names when I defined the table. BEST_STATE is also an in memory table but here I chose to define the column names within the query. JOINED shows I can reference the tables immediately after defining them. Finally, I reference JOINED from outside the CTE by grabbing all of the data.

What's better, it depends. Besides that hoary chestnut, I find myself defining columns if I am building up some static data values like I do in BORDER_STATES. Otherwise, I typically just alias my columns as I query them like I do with BEST_STATE.

Finally, you should notice that the use of SELECT * leads to puppies being slaughtered. I read it on a blog so it must be true.

Availability

SQL Server 2005+