When I read Bob Pusateri invite to this month’s T-SQL Tuesday, it brought me back to my college days where I actually studied Thermal Dynamics and what the Coefficent of Thermal Expansion was. I will admit that I didn’t have a clue (too many years gone by) and had to look it up.
So for you fellow nerds out there: Coefficient of Thermal Dynamics is the degree of expansion divided by the change in temperature in some sort of material. Wikipedia has a pretty good explanation on it. I did have a brief (real brief) thought about trying to determine what the coefficient of the hard drive might be while running a SQL CTE, but then I realized that I wouldn’t even know where to start with that. Plus, that would probably make this post a very long read, so I ditched that idea. Ah well, moving on!
An colleague of mine, Jamie, was working on a small project dealing with logging from the entity framework (I think). In a nutshell, there are 4 tables that log various types of data. For example, say that these tables are:
Jamie was looking for a clean way to select information from each respective table given certain criteria, such as a machine name and which logs he want to look at. The stored procedure would accept a parameter(s) and which logs they were looking for based on the numberic value that is assigned to said table. In other words, if they wanted the InformationLog and TraceLog the call would look like:
EXEC dbo.usp_FindMyLogs @machineName='RedOctober', @Logs='1,3'
Make sense? Both parameters would be a VARCHAR() data type and the @Logs parameter would be comma delimited.
Initially we started down the path of using various temporary tables to load all of the data, filter it out, and then aggregate everything together using various ‘IF’ statements. While this is certainly a viable solution, we both knew that there was probably a much cleaner solution to be found. So, we started to play around with the data and looking into using a CTE to help clean things up.
Here’s the CTE solution that we came up with:
DECLARE @myTable TABLE (id INT, [name] VARCHAR(20))
INSERT INTO @myTable (id, [Name])
SELECT 1,'Information' UNION
SELECT 2,'Exception' UNION
SELECT 3,'Warning' UNION
-- used just for this example.
DECLARE @LogId VARCHAR(25) = '1,2,4'
DECLARE @machineName VARCHAR(10)
;WITH myCTE (ID, [Message])
(SELECT 'Information',[Message] FROM dbo.InformationLog WHERE machineName = @machineName
SELECT 'Exception',[Message] FROM dbo.ExceptionLog WHERE machineName = @machineName
SELECT 'Trace',[Message] FROM dbo.TraceLog WHERE machineName = @machineName
SELECT 'Warning',[Message] FROM dbo.WarningLog WHERE machineName = @machineName
SELECT cte.ID, cte.[Message] FROM myCTE cte
INNER JOIN @myTable mt ON cte.ID = mt.name
INNER JOIN dbo.ufn_split(@logId,',') ufn ON mt.id = ufn.Value_Tx
Now, you’ll notice that we create the table variable every time this is called, thus I’ve recommended that given the data doesn’t change, it would make sense to make a small physical table so that we don’t have the over head of this creation every time.
Also, there is a pre-built split function that we use internally to split out delimited values. The Value_TX column from the function would contain the table names, effectively changing them from literal names to their associated numeric values. This INNER JOIN allows Jamie to only select data from the tables which he specifies, even though the CTE contains data from ALL of the tables. Yes, we know that having this extra data in the CTE is overhead, but it’s fairly minimal and shouldn’t cause that much extra effort. I smell a follow up blog post coming in looking at the execution plan for this solution.
All in all, I think that this CTE is a clean way to narrow down the results that we have to play with without using a bunch of logical ‘IF’ statements. I know that this isn’t anything special, but it was a solution in which we were able to utilize the awesome power of CTE’s and provide a nice clean solution that fitted the need.
Be gentle. It’s my first T-SQL Tuesday post. 😉
© 2011, John Morehouse. All rights reserved.