Dec 132011
 

T-SQL Tuesday LogoThis month’s T-SQL Tuesday is hosted by Allen White (blog | @SQLRunr) and is an invitation to show off a nifty T-SQL trick you use to make your job easier. Here’s one of my favorites as of late:

Some of the source systems we copy from shard their data across multiple similar tables. For example, instead of having 1 table named dbo.Sales, they might have 30 of them named dbo.Sales_001 to dbo.Sales_030. If there were a rhyme or reason to this sharding it might be seen as an advantage, but unfortunately neither myself nor others on the team have ever found a method to this madness, and the vendor will not divulge the way they do this. As if that’s not bad enough, additional tables can pop up out of nowhere, and not all these tables are guaranteed to contain the same columns. Table dbo.Sales_031 may pop up tomorrow and have only a subset of the columns from the previous 30 tables, or may contain new columns not present in any of the others.

To keep this all straight, I have a process that compares the columns in all applicable tables and generates a view that combines their contents using UNION ALL. I’ve actually blogged about an aspect of this before, but today I have a function containing a nice little trick that’s proven very helpful for me. Since the tables I’m generating a view of aren’t guaranteed to have the same columns in them, a simple “SELECT *” from each of them won’t work because unioning tables together requires identical datatypes in the same order. Instead I have to generate a select statement that explicitly lists all columns for every table. T-SQL can easily accomplish this with cursors and loops, but then I found FOR XML PATH, which made life a lot simpler.

FOR XML PATH has many features which are outside the scope of this post, but the one behavior we’re going to exploit today is that you can pass a row tag name that’s blank. When you do this, instead of sandwiching each row between tags, it simply concatenates all the row values together into one string. If you add commas, you’ve got a great way to list out all the columns of a table which you can then form into a SELECT statement. Without any further ado, here’s the demo code:

-- create our demo table with a few columns
CREATE TABLE dbo.MyTable (
   ID INT NOT NULL,
   Value VARCHAR(20),
   Notes NVARCHAR(MAX)
);

-- select the table's columns from system tables
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MyTable'
ORDER BY c.column_id;

-- now let's format it as XML
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MyTable'
ORDER BY c.column_id
FOR XML PATH;

-- now pass a blank string argument to PATH
-- note how the <row> tags are now removed but column tags remain
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MyTable'
ORDER BY c.column_id
FOR XML PATH('');

-- casting it to NVARCHAR removes the tags for each column
DECLARE @s NVARCHAR(MAX);

-- now let's add a blank string argument to PATH
SET @s = (
      SELECT ',' + QUOTENAME(c.name) -- comma to separate names
      FROM sys.columns c
      INNER JOIN sys.tables t ON c.object_id = t.object_id
      WHERE t.name = 'MyTable'
      ORDER BY c.column_id
      FOR XML PATH('') -- XML that really isn't
   );

-- remove the leading comma
SELECT SUBSTRING(@s, 2, LEN(@s));

Wrap the final output with a SELECT and a table name and you’re good to go. There’s tons more uses for this than creating lists of columns – basically anytime you want to turn column values into a delimited list you can make it happen using this method. Happy coding!

Dec 092011
 

SQL Saturday 119 LogoThe third time’s a charm! SQL Saturday #119 (Chicago 2012) has been posted on the SQL Saturday website! It will once again be held at the DeVry University campus in Addison, IL and will be on May 19, 2012.

SQL Saturday in Chicago has a special place in my heart for a few reasons. The first SQL Saturday I ever attended was SQL Saturday #31 back in 2009, the very first one held in Chicago. I didn’t have this blog back then, but had an excellent time. Last year was SQL Saturday #67, which I did blog about. I was also very fortunate to be part of a great group of people who help put it together, and am back for more this time around. The rest of our team consists of:

– Ted Krueger (blog | @onpnt)
– Aaron Lowe (blog | @vendoran)
– Bill Lescher (@blescher)
– Rich Rousseau (@zigzag219)
– Wendy Pastrick (blog | @wendy_dance)

Register Today!

Registration is open, so sign up today! We had a great turn out last year and we’re hoping to fill up again! The event itself is free, but there is a $10.00 charge for lunch (it won’t be pizza!)

Why You Need To Be There

  • It’s free knowledge! Yes there is a charge for lunch, but that’s optional. The training itself costs nothing.
  • Meet other awesome DBAs! Most companies have very few DBAs, sometimes only one. This can lead to feelings of isolation and that the whole world of developers is against you. You’re not alone though – there’s tons of us out there! Community events are by far the best way to meet people in this field – where else can you hang out all day with a bunch of people as passionate about databases as you are?
  • Did I mention it’s free?

Call for Speakers

Have you spoken at work, a user group, or a SQL Saturday before? Have you never tried but are looking to take the plunge? The call for speakers is open until April 19, 2012, so please submit an abstract (or two or three) if you’re at all interested!

I can’t wait to see you there in May!

Dec 052011
 

Well, we’re back at the top of the month again, and this time around for Meme Monday we’re to talk about what we’d love to see under the tree from Microsoft. This got my creative juices flowing, and I came up three wishes:

First and foremost, if I had one wish that I could wish this holiday season, it would be that all the children of the world would join hands and sing together in the spirit of harmony and peace.

If I had two wishes that I could wish for this holiday season, it would be for all of the children of the world to join hands and sing, and for SQL Server to natively support object-level restores.

Christmas PresentsWhen working with a large database, native backups take a good deal of time to create and restore. For this reason, VLDBs are likely to be backed up using non-native methods such as SAN snapshots, but let’s forget about those and stick with what Microsoft includes in the box. If a single table needs to be restored from a backup, the only native way to accomplish this at the moment is to restore the entire database somewhere else and then copy the desired table back to the production server. This takes a great amount of disk space (the entire size of the database) as well as the time necessary to restore all that data. If available, filegroup backups can make it possible to restore only the specific filegroup(s) necessary for said table(s), but even that would be restoring more data than is necessary, since filegroups typically contain more than one table.

The best solution is to be able to restore an individual table or other object directly from a backup. Since only the necessary data would be read out of the backup this would keep the required time and disk space to a minimum. This feature is already offered by several third party vendors, but having it be built-in would be a huge help as you wouldn’t have to sit down with your boss and make the case for purchasing yet another tool.

If I had three wishes that I could wish for this holiday season, it would be again for the children, for object-level restores, and for the ability to run integrity checks on a backup.

The time it takes for DBCC CHECKDB to complete increases along with the size of the database being checked, and as databases get larger the general recommendation for integrity checks shifts towards running them on another server instead of the production machine. Once again limiting ourselves to options provided natively by Microsoft, we would have to restore a full backup of our very large database on another machine and run DBCC CHECKDB on it. As before, this will be expensive both in terms of time and disk space.

A few years ago Paul Randal mentioned in one of this blog posts that during his time at Microsoft he developed and patented a way to run DBCC CHECKDB on a backup without restoring it. I can’t imagine why Microsoft would sit on this technology, as I know it would be a huge help to me and I’m sure many others could benefit from it as well. Hopefully sometime it can be included as a feature.