bSwartz's SQL Server Blog

SQL 2005's new PIVOT functionality

2005-12-27T04:46:00Z

New with SQL Server 2005 are some really cool T-SQL enhancements.

One that I was really looking forward to sinking my teeth into was the PIVOT/UNPIVOT enhancements to the Select statement. That was until I started to use it.

After a little frustration I figured out the PIVOT's idiosyncrasies and was pretty disappointed in some basic functionality that is missing.

First, let’s talk about “how” to use it in the most basic fashion.

Using your select statement, you create the query as you typically would, then you add the following to it.

SQL 2000
SELECT GroupByColumn1,GroupByColumn2,
Max(
CASE
when PivotColumn = ‘abc’ then PivotColumn else null
when PivotColumn = ‘def’ then PivotColumn else null
END
)
FROM myTable
GROUP BY groupByColumn1,GroupByColumn2

SQL 2005
SELECT * FROM myTable
PIVOT ((Aggregate function) FOR Column Name IN (List of Values))
AS AliasName

Seems pretty basic right?? (Not really)

The Aggregate function replaces your normal aggregate wrapper for the case statement, and the List of values are the values you would use within the case statement. The problem (and the missing functionality) is in the List of Values.

You would think given that the values MUST be convertible to NVarchar that a string is what is being called for. And strings are typically quoted like this ‘abcdefg’. Well, not so with this option.

Something like this will not work.
PIVOT (MAX(value) FOR myColumn IN ('11/18/2005','11/19/2005','11/20/2005') )
AS myAlias

The SQL Parser wants to see a “raw” or bracketed values from the result set. So you MUST do something like this.
PIVOT (MAX(value) FOR myColumn IN ([11/18/2005],[11/19/2005],[11/20/2005]) )
AS myAlias
Or
PIVOT (MAX(value) FOR myColumn IN (abc,def,hij) )
AS myAlias

Here’s where the missing functionality comes in. >>> A sub query will not work in the place of the column list. The following will not work.
PIVOT (MAX(value) FOR myColumn IN (SELECT myPivotcolumn FROM MyTable) )
AS myAlias

As I was working with this, I thought.. You have got to be kidding. Is this really a limitation? I tried every which way I could to make this work.. If anyone can prove me wrong, PLEASE do so…

So, to get around this, I do something like this. For all the folks that are going to throw stones about the dynamic SQL, I don’t like doing dynamic SQL either… But I don’t see a way around it in this case.

DECLARE @Dates varchar(2000)
SET @dates = ''
--// Get a list of the pivot columns that are important to you.

SELECT @Dates = @Dates + '[' + convert(varchar(12),CaptureDate,101) + '],'
FROM (SELECT Distinct CaptureDate FROM DurationSummary) Duration

--// Remove the trailing comma

SET @Dates = LEFT(@Dates, LEN(@Dates) - 1)
--// Now execute the Select with the PIVOT and dynamically add the list
--// of dates for the columns
EXEC( 'SELECT * FROM DurationSummary PIVOT (MAX(Quantity) FOR CaptureDate IN (' + @Dates + ')) AS X' )

Merry Christmas / Happy Holidays & a VERY Happy New Year!!!

Bill

Thinking about migrating to SQL Server 2005?

2005-07-29T15:52:00Z

Shameless plug Migrating to SQL Server 2005

SQL Server 2005’s release is just a few months away.

If your not excited about it, you’re either don’t care about SQL Server technology, you’re still on SQL Server 6.5, or your in one of those jobs where you’re working 20 hour days and don’t see how you’ll ever find time to learn about the new server(I empathize with you).

Software has gotten boring lately. Back in the days when Windows 95 first came out, there were soooo many new software packages being delivered that were really cool, or really made a difference. I think SQL Server 2005 is in the “cool“ software category..

Here’s some things that I’m personally interested in.

A new Enterprise Manager with Intellisense.. Yippeeee… (SQL Server Workbench).
A new way of inserting business logic into the database (Procs / Managed code with DotNet languages) (DBA’s beware)
A completely new DTS implementation (Lets hope it’s more developer friendly than the old one)
New Views for system objects, new ways to partition objects.
SQL Stuff – Recursive queries (Finally!!!), Pivot functionality and Synonyms
New DataTypes varchar(max) and XML (as a datatype)
New Analysis Services with native XML/A.. (will anyone ever really adopt this as a standard?)

So if you already are thinking about moving towards SQL Server 2005 and want some migration advice, or just want to hear from some folks that have been working with this platform for some time, consider signing up for the Migrating to SQL Server 2005 Road Show. This is put on by SQL Server Magazine, so you know it’s from someone that’s focused on the platform you love!

Creating DotNet COM objects for DTS

2005-07-13T15:45:00Z

SQL Servers DTS (Data Transformation Services) can be a huge pain in the butt, but it also has some great tools for data migration / loading. So if you’re moving lots of data between databases / servers, chances are you are probably using it.

I spend a lot of my time in the DTS world, and have gotten used to its idiosyncrasies and short-comings. I was recently educated on a method that will make me more productive and provide greater functionality for my DTS packages.

Create and call your own COM component in the ActiveX Script Task. (Thanks to Jay Meekin at Magenic Inc (my new Employer)

But… I’m not just creating any COM object, I’m using C# to do this. Mingling the old with the new so to speak.

This might not be new to some of you more advanced developers, since I've never coded a COM object, it was a little out of my comfort zone, but really wasn't difficult at all. Database folks that spend their time only writing SQL for their DTS packages should definitely spend time to explore this option.

So why is this a big deal?? DTS has no way to re-use subroutines or methods in it’s ActiveX tasks. You have 3 choices in DTS if you want to reuse code.

Copy and paste between a bunch of DTS ActiveX scripts. (not really re-use)
Write only SQL related code and call stored procedures
(my new favorite) Write your own com object and call it in your ActiveX Task.

To help you get started, here’s a quick cheat sheet.

Create your Class / Methods in a dotNet language (you must have a constructor that has no parameters)
Put the DLL in %SystemRoot%\System32 (On all the boxes that will execute the DTS Package)
Run REGASM to register the DLL (you might have to search for this if it’s not in your path)
Add CreateObject(“YourNameSpace.YourClassName”) to your ActiveX Script
Have Fun!!

Bill

Why you should treat the database as a sacred item.

2005-05-02T16:50:00Z

I get asked to teach junior developers about SQL Server on a frequent basis. I had an interesting question posed to me yesterday that I thought was very astute of the developer. He asked “What is so different about a database server versus any other platform”

This is actually a pet peeve of mine. Some developers don’t treat it any differently and I think database servers should be seen as a sacred item. I re-wrote one query written by a VB programmer many years ago that was taking 7 hours to complete.. My new query ran in 30 seconds..

Even though the “server” is multi threaded, when it comes down to row level access, no matter how you look at it, that row can’t be shared when performing updates.

For this reason alone, you should ALWAYS be concerned about optimizing that task you are currently coding. Most developers can code a Select or Insert. But, do you stop and take the time to ask yourself if it really is the most efficient way to perform the task? I know I know, it doesn’t always matter, but if you develop the discipline to at least think about it, you’ll be a much better database developer.

Quoting some much smarter people than myself, 80% of performance issues are related to the code and NOT the infrastructure.

My recommendations:

Check out http://www.sql-server-performance.com

If you are using transactions in your stored procedures, try to make sure that only Inserts, Updates and Deletes and error handling are between the BEGIN TRANSACTION and COMMIT TRANSACTION. This is not always possible, but at least ask yourself if it is possible. When you issue that BEGIN TRANSACTION, the Lock Manager puts up that big red stop sign and no-one else gets through until you are done. So if you have one Insert, 10 reads and then a second insert, you‘ve dramatically extended that Lock.

If you are writing a complex select statement, be sure to use the Estimated Execution Plan in Query Analyzer. You don’t have to understand all the actions that are performed to get a benefit from the Execution Plan. Visually take a look at the size of the arrows and the estimated row counts. If you have 10 steps and the final row count is 2 rows, but you carry 7 million rows through each step until the last one, you can probably write the query differently to make it perform better.

Performance is what you make of it!!

Large columns (text,nText,image) and use of "Text in Row"

2005-04-14T00:47:00Z

Mike Campbell was sharing his thoughts on using the “Text in Row” option of sp_tableOption system procedure on the SqlAdvice list, so I thought I would expand on it a little.

If you choose to use text, nText or image columns because the data just “might” grow to a size greater than 8060 bytes. Lookup sp_tableOption and read up on the “text in row” option.

This is a way to have your cake and eat it too when it comes to huge amounts of variability with these columns.

Think of these columns as a place to store your extra large mobile RV. In my city there is a law that says you cannot store a vehicle larger than say 22 feet on your property. So you have to store the RV at your local self storage unit or some other place out in the country.

So, in order to use this RV, your buddy, aunt or wife has to drive you to the storage place, pick up your RV and both of you drive it back to the house to load it up for the trip. BUT.... If your RV is less than 22 feet long, the city is ok with you parking it in your driveway.

If you don’t use the “text in columns” option, the database defaults to the same extra overhead as the RV trip. The database will store a pointer in the data row and the “data” are stored in a “collection of pages” Which means that if your text is only 2k bytes you have the added overhead of looking up the pointer and traveling to the “other pages” and retrieving the data. IE, a trip to the Self Storage Yard..

So, if you estimate that most of your data will be smaller, let's say 2048 bytes, and you want to keep the potential for a 2GB possibility, use the “text in column” to keep it most of your data in the same data row / page.

But, be careful. This performance gain is only available IF you have space left in the data row. So if all your other columns add up to 7k and your nText column is 2048, you get to go to the storage yard anyway <grin>

Welcome!!!

2005-04-14T00:19:00Z

I've not been a huge fan of blogging because I think it's a little weird that people share their lives over the internet with a lot of strangers.. Kind of voyeuristic if you ask me..

However, when asked to participate in the SqlAdvice Blog It occurred to me that it’s not such a bad forum to share technical idea’s and thoughts.. I've always enjoyed teaching and this can clearly be such a medium for sharing knowledge... And maybe it will lead to a book deal or even a movie some day… I can hear the phones ringing now!!

Just shows you CAN teach an old dog new tricks...

So here I am.