Comparing Rows in a Table

As all SQL professionals will know sqlservercentral.com is an amazing resource. In an entry titled Linking to the Previous Row the author, David McKinney, describes a simple way to compare rows within a table using the two functions introduced in SQL Server 2005: Common Table Expressions (CTE) and Rownumber().

To be honest, until I read this article, I didn't see much of a use for CTEs other than recursive queries (for more on that see: Recursive Queries in SQL Server 2005). But this example provides a tidy method for comparing, for instance, changes over time.

And if you don't already know, the Rownumber() function allows you to include, as the name suggests, a column of row numbers in your result set based on a sort order that you determine. In addition, the PARTITION BY allows you to group the results.

I will definitely be employing this

Parse a Comma Separated List

Here is a simple user-defined function to parse a comma separated (or any other character separated) list and return a table of the values in the list. The input variable must be a text data type (varchar). It returns the results as varchar so depending on your needs you may need to convert to int or datetime, etc.

A Quick Cheat to Create a Comma Separated List

The select statement below is an easy way of creating a comma separated list within a larger select query without the need for a cursor, user defined function or even a paramenter. It exploits the 'for xml' statement. By not defining any xml elements the column you select gets concatenated into a single line.

In my scenario I have keywords that can be members of multiple adgroups. Since my revenue figures are aggregated by keyword and not broken down by adgroup I need to display all adgroups in the same row as the keyword or I will get duplicate revenue figures.
I owe a lot to the colleague who showed this to me. It's saved me tremendous amounts of time and trouble over the past few years. I hope you find this as helpful as I did.

Find SSRS Subscription Jobs

A helpful query found on Patrick LeBlanc's blog:


The result set returns a list of SQL Server Reporting Services Reports matched to the jobs used to run the report subscriptions. A very helpful query in that SSRS report manager names the regular subscription and data driven subscription jobs based on a uniqueidentifier. This makes it difficult to identify which jobs run which subscriptions when viewing jobs through SQL Server Management Studio.

Problems Moving tempdb Files

I had a situation where I had miscalculated the disk I/O load of the TempDB data and log files and therefore needed to alter the RAID configuration and move the tempdb files to the new configuration.
I had originally placed the tempdb data files on a RAID 10 (E:) partition with one mirrored physical disk and the tempdb log file on a separate but identical RAID 10 (G:) partition. The disk queue length for the data files was far too large. The log file was not used nearly as much. I decided (since resources were tight) to merge the E and G partitions into one RAID 10 partition with two mirrored disks.
The steps that were needed to accomplish this were as follows:
- Move tempdb data and log files to spare partition
- Merge E: and G: partitions
- Move tempdb data and log files to newly merged partition

For those that don't have experience with moving database files the procedure is pretty straight forward:
Run the following SQL with the new destination for each data file
Then restart SQL Server service.
My problem ocurred once I wanted to move the the tempdb files to the newly created RAID 10 partition. I kept receiving an error message stating the tempdb log file was unavailable. I had neglected to set the proper permissions on the new folders of the new partition. However, configuring this did not resolve the problem.

I couldn't find anything referring to the problem I had and all the documentation I read stated that restarting the SQL Server service was sufficient.

In the end I restarted the machine and the problem was resolved. Though this option was not specified in any documentation. I can only assume that the database did not "recognize" the partition and/or security changes until after an OS reboot.