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+

SSIS Lookup task with CTE SQL Query and Enable Memory Restriction checked causes Error

Isn't that a mouthful? Common Table Expressions are valid for use against a SQL Server database version 90 and above (2005 & 2008). SSIS works also works for version 90 and above. CTEs are perfectly valid data sources in SSIS. They can be perfectly valid sources in the Lookup component as well, as long as you are using Full Caching. Once you attempt to set the caching to Partial or None, then your package will fail to validate.

The reason for the failure has to do with some of the internal voodoo the Lookup component does to accomplish this. It's readily apparent if you click into the advanced tab and check the box for Custom Query. SSIS takes the most brain dead approach to ensuring it can build out the query in Partial/No cache by wrapping your query with "select * FROM (YOUR QUERY HERE) [RefTable] where [refTable].[critera] = ?" Given the title of this post, the astute reader can see the problem. From BOL: "When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon."

2005 Error message

Validation error. MyDataFlowName: MyLookupTask [84]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

2008 Error message

Error at Data Flow Task [Lookup [52]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
Error at Data Flow Task [Lookup [52]]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties.
Error at Data Flow Task [SSIS.Pipeline]: "component "Lookup" (52)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)

So, what to do? Until they fix it, my money is on "Closed, won't fix" as a resolution, the query will have to be rewritten in a non-CTE fashion. Heaven help you if it was a recursive CTE

References:
http://msdn.microsoft.com/en-us/library/ms175972.aspx
https://connect.microsoft.com/SQLServer/feedback/details/531823/ssis-lookup-task-with-cte-sql-query-and-enable-memory-restriction-checked-causes-error

Query to build SSIS qualified path

I had been hemming and hawing on how to best deal with the recursive nature of paths in SQL Server. This query is going to be what I need to build out the path correctly and being a good geek, I share.

TODO: add pictures so people have an idea of what it would look like

;
WITH FOLDER_STRUCTURE AS
(
   SELECT
       F.folderid
   ,   cast('MSDB/' as nvarchar(max)) As folder_path
   FROM
       msdb.dbo.sysdtspackagefolders90 F
   WHERE
       folderid = '00000000-0000-0000-0000-000000000000'
   UNION ALL
   SELECT
       F.folderid
   ,   R.folder_path + F.foldername + '/' As folder_path
   FROM
       FOLDER_STRUCTURE R
       INNER JOIN
           msdb.dbo.sysdtspackagefolders90 F
           ON F.parentfolderid = R.folderid
)
, PACKAGES AS
(
   SELECT
       P.name AS package_name
   ,   P.folderid
   ,   P.id as package_id
   ,   P.createdate as package_createdate
   ,   P.vermajor
   ,   P.verminor
   ,   P.verbuild
   FROM
       msdb.dbo.sysdtspackages90 P
)
SELECT
   FS.folder_path + P.package_name
FROM
   PACKAGES P
   INNER JOIN
       FOLDER_STRUCTURE FS
       ON FS.folderid = p.folderid

Recursive CTE

I ran into a situation today where we needed some dummy data loaded into a brand new table to make a rough prototype.  While I could have done lots of typing to get in there, I thought this was a decent opportunity to use a recursive common table expression (CTE).

CREATE TABLE 
    DBO.COMPANY_DEPARTMENT
(
    company_id int
,   company_name varchar(255)
,   department_id int
,   department_name varchar(255)
,   PRIMARY KEY CLUSTERED (company_id, department_id)
)
GO

;
-- load up some dummy data
WITH COMPANY AS
(
   SELECT
       0 AS company_id
   ,   'Sample company' AS company_name
)
, DEPARTMENT AS
(
   SELECT
       20 AS department_id
   ,   'Department ' + char(65 + 20) AS department_name
   UNION ALL
   SELECT
       department_id -1
   ,   'Department ' + char(65 + department_id -1) AS department_name
   FROM
       DEPARTMENT
   WHERE
       department_id > 0
)
INSERT INTO
   COMPANY_DEPARTMENT
SELECT
   *
FROM
   COMPANY
   CROSS APPLY
       DEPARTMENT

The above query will kick out data like
0,Sample Company, 0, Department A
0,Sample Company, 1, Department B

from 0 to 20