3 Sessions submitted to SQL Saturday in Salt Lake City

April 22, 2012 by Leave a Comment

I am planning on visiting Salt Lake City for SQL Saturday in October.

Filed under SQL Saturday Tagged with

Using a CTE in a Function to Split Up a Query String

April 9, 2012 by 3 Comments

The question came up as to how do I parse a query string using TSQL.  So here you go, using a common table expression, and basing this on a similar function to what I put together yesterday for split.

CREATE FUNCTION dbo.SplitQueryString (@s varchar(8000))
RETURNS table
AS
RETURN (
 WITH splitter_cte AS (
 SELECT CHARINDEX('&', @s) as pos, 0 as lastPos
 UNION ALL
 SELECT CHARINDEX('&', @s, pos + 1), pos
 FROM splitter_cte
 WHERE pos > 0
 ),
 pair_cte AS (
 SELECT chunk,
 CHARINDEX('=', chunk) as pos
 FROM (
 SELECT SUBSTRING(@s, lastPos + 1,
 case when pos = 0 then 80000
 else pos - lastPos -1 end) as chunk
 FROM splitter_cte) as t1
 )
 SELECT substring(chunk, 0, pos) as keyName,
 substring(chunk, pos+1, 8000) as keyValue
 FROM pair_cte
)
GO

declare @queryString varchar(2048)
set @queryString = 'foo=bar&temp=baz&key=value';
SELECT *
 FROM dbo.SplitQueryString(@queryString)
OPTION(MAXRECURSION 0);

which produces this output.

A quick and easy way to parse a query string  in TSQL using a CTE.

Filed under CTE Tagged with , , , , ,

Using a CTE to Split a String Into Rows

April 8, 2012 by 5 Comments

One of the common questions that comes up in my CTE class/presentation asks can you use the CTE inside of a function or stored procedure. The answer is yes, but today I came across another question, how can I split a string (varchar) into rows. I did some searching but didn’t find quite what I was looking for. I did fine one CTE example, but I didn’t like the way it worked, so I decided to create my own Split function, using a recursive CTE to implement the split function.

Keep in mind that the OPTION(MAXRECURSION 0) does not go inside of the table valued function, be instead it goes in the query that calls the function.

GO
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH splitter_cte AS (
      SELECT CHARINDEX(@sep, @s) as pos, 0 as lastPos
      UNION ALL
      SELECT CHARINDEX(@sep, @s, pos + 1), pos
      FROM splitter_cte
      WHERE pos > 0
    )
    SELECT SUBSTRING(@s, lastPos + 1,
                     case when pos = 0 then 80000
                     else pos - lastPos -1 end) as chunk
    FROM splitter_cte
  )
GO

SELECT *
  FROM dbo.Split(' ', 'the quick brown dog jumped over the lazy fox')
OPTION(MAXRECURSION 0);

which produced the following output.

chunk
---------------------
the
quick
brown
dog
jumped
over
the
lazy
fox
(9 row(s) affected)

There are programming tasks that can be accomplished easily with a Recursive Common Table Expression.

Filed under CTE Tagged with , , ,

Finished my Draft Copy of my Chapter for SQL Tribal Knowledge book

April 6, 2012 by Leave a Comment

I finished my draft copy of my chapter titled “Minimal Downtime and Minimal Risk SQL Server Upgrades” for the SQL Tribal Knowledge book. I am sure there will be some editing and cleanup to do, but for now I am calling the draft copy complete.

What a great experience getting this chapter together. I hope that the other writers had as much fun with it as I did. Lots of creative ideas when into this chapter. I am looking forward to reading all the other chapters.

Now looking for the next new project to work on.

Filed under TribalSQL

TSQL 2012 – OFFSET and FETCH

April 4, 2012 by Leave a Comment

Its about time, MySQL has had a feature LIMIT which is similar to the  usage of OFFSET and FETCH for years.  Now in SQL Server 2012 Microsoft introduces OFFSET and FETCH.

The OFFSET and FETCH functionality is used to achieve data paging server side in TSQL.  Thing of a page like your typical search results where you are shown the first 20 results, then you click to page 2 where you see the next 20 search results.  Over the years of coding in different languages I have seen a lot of very creative solutions for data paging, most of which have been extremely difficult to follow and most of which have been difficult to maintain.  Most of these are done client side in ASP, .net, PHP or some other web scripting language where if you want rows 21 to 40 you select all the rows, then skip over the first 20, then process rows 21 to 40 and eventually give up on the result set loop at row 40.   It works, but it s not pretty.

Prior to SQL Server 2012, my favorite way to do server side paging was using a Common Table Expression (CTE), which I have presented at a couple of SQL Saturdays now.

With SQL Server 2012 the OFFSET and FETCH options are introduced into the SELECT statement.  The OFFSET statement tells you where to start and the FETCH statement tells how many rows you want to grab.

For this sample, I will be using the same Revenue table from a previous TSQL 2012 example.

First, just look at the Revenue table to see whats there

SELECT * 
FROM REVENUE
ORDER BY Year, DepartmentID ASC;

Now to grab the second 10 rows with a CTE, the old way of doing it prior to FETCH and OFFSET.

WITH RevenuePagingCTE AS
(
 SELECT *,
 ROW_NUMBER() OVER (ORDER BY Year, DepartmentID ASC) as RowNum
 FROM REVENUE
)
SELECT DepartmentID, Revenue, Year
 FROM RevenuePagingCTE
 WHERE RowNum > 10
 AND RowNum <= 20;

And now for the new OFFSET and FETCH option which will produce the same output as the CTE, but is much easier to put together.


SELECT DepartmentID, Revenue, Year
 FROM REVENUE
 ORDER BY Year, DepartmentID ASC
 OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY;

So how do these compare performance wise. Is the CTE faster, or is the FETCH/OFFSET option faster? Lets take a look at the execution plan. As you can see in this example although the CTE has more steps that it goes through, the total cost between the two are equal at 50% each.

So overall, FETCH and OFFSET doesn’t give you anything that you couldn’t do before, it just makes it much easier, and faster.

Filed under CTE, SQL 2012, TSQL 2012 Tagged with , , , , , , ,

My Top 10 TSQL Enhancements in SQL Server 2012

April 4, 2012 by Leave a Comment

So far after playing around with SQL Server 2012 there are many new features to SQL.  Overall I think my favorites are in the Analytic Functions category.  Here is my breakdown of the top 10 transact SQL enhancements to SQL Server 2012.

10.  New SEQUENCE object

9.  CHOOSE function

8.  Analytics - Analytic Functions – PERCENT_RANK

7.  Analytics PERCENTILE_DISC and PERCENTILE_CONT

6. IIF function in TSQL.

5. TSQL Analytic Functions LEAD and LAG

4.  OVER clause with ROWS PRECEDING and FOLLOWING in TSQL 2012

3.  OFFSET / FETCH in a SELECT statement

2.  LAST_VALUE and FIRST_VALUE

1.  Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST()

Whats your favorite?

Filed under SQL 2012, TSQL 2012 Tagged with , , , , , , ,

← Older posts