I have a bunch of processes that run at then end of the day. Some of these processes are configured dynamic since table names, server names, database names and a whole bunch of other stuff might change.
So you might have a (over simplified here) table like this
Typeid TypeName TypeValue 1 ActiveServerName SQLDenisDB1 1 DatabaseName MyDB 1 LogTableName LogFileTable
And there might be a dozen more configurations for a process
In general people will do 3 selects if there are 3 values, let’s take a look at what I mean. First create this table
T-SQL | |
1 2 3 4 5 6 7 | CREATE TABLE SomeConfigurations(Typeid INT NOT NULL, TypeName VARCHAR(100), TypeValue VARCHAR(100)) INSERT SomeConfigurations VALUES (1,'ActiveServerName','SQLDenisDB1') INSERT SomeConfigurations VALUES (1,'DatabaseName','MyDB') INSERT SomeConfigurations VALUES (1,'LogTableName','LogFileTable') |
CREATE TABLE SomeConfigurations(Typeid INT NOT NULL, TypeName VARCHAR(100), TypeValue VARCHAR(100)) INSERT SomeConfigurations VALUES (1,'ActiveServerName','SQLDenisDB1') INSERT SomeConfigurations VALUES (1,'DatabaseName','MyDB') INSERT SomeConfigurations VALUES (1,'LogTableName','LogFileTable')
One select per value
This is what I usually see, one query for each value.
T-SQL | |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DECLARE @ActiveServerName VARCHAR(100), @DatabaseName VARCHAR(100), @LogTableName VARCHAR(100) SELECT @ActiveServerName = TypeValue FROM SomeConfigurations WHERE Typeid =1 AND TypeName = 'ActiveServerName' SELECT @DatabaseName = TypeValue FROM SomeConfigurations WHERE Typeid =1 AND TypeName = 'DatabaseName' SELECT @LogTableName = TypeValue FROM SomeConfigurations WHERE Typeid =1 AND TypeName = 'LogTableName' SELECT @ActiveServerName,@DatabaseName,@LogTableName |
DECLARE @ActiveServerName VARCHAR(100), @DatabaseName VARCHAR(100), @LogTableName VARCHAR(100) SELECT @ActiveServerName = TypeValue FROM SomeConfigurations WHERE Typeid =1 AND TypeName = 'ActiveServerName' SELECT @DatabaseName = TypeValue FROM SomeConfigurations WHERE Typeid =1 AND TypeName = 'DatabaseName' SELECT @LogTableName = TypeValue FROM SomeConfigurations WHERE Typeid =1 AND TypeName = 'LogTableName' SELECT @ActiveServerName,@DatabaseName,@LogTableName
Okay so that is not really something I want to maintain. I guess if you get paid by lines of code written it makes you look good 🙂
Next up.. a different approach…
Enter the pivot.
Pivot was introduced in SQL Server 2005 and here is how you can change those three selects into one select
T-SQL | |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE @ActiveServerName VARCHAR(100), @DatabaseName VARCHAR(100), @LogTableName VARCHAR(100) SELECT @ActiveServerName = ActiveServerName, @DatabaseName = DatabaseName, @LogTableName = LogTableName FROM (SELECT TypeName,TypeValue FROM SomeConfigurations WHERE Typeid =1) AS pivTemp PIVOT ( MAX(TypeValue) FOR TypeName IN (ActiveServerName,DatabaseName,LogTableName) ) AS pivTable SELECT @ActiveServerName,@DatabaseName,@LogTableName |
DECLARE @ActiveServerName VARCHAR(100), @DatabaseName VARCHAR(100), @LogTableName VARCHAR(100) SELECT @ActiveServerName = ActiveServerName, @DatabaseName = DatabaseName, @LogTableName = LogTableName FROM (SELECT TypeName,TypeValue FROM SomeConfigurations WHERE Typeid =1) AS pivTemp PIVOT ( MAX(TypeValue) FOR TypeName IN (ActiveServerName,DatabaseName,LogTableName) ) AS pivTable SELECT @ActiveServerName,@DatabaseName,@LogTableName
I prefer that over those three selects any time. Not only is it less code but it is also faster because it touches the tables once. If you want to learn more about PIVOT and UNPIVOT, then visit the following books on line link: http://msdn.microsoft.com/en-us/library/ms177410.aspx
If you are running software from the last century: case to the rescue
Even for you people who are still on SQL Server 2000 there is a way to do this in one select, take a look at the query below
T-SQL | |
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE @ActiveServerName VARCHAR(100), @DatabaseName VARCHAR(100), @LogTableName VARCHAR(100) SELECT @ActiveServerName = MAX(CASE TypeName WHEN 'ActiveServerName' THEN TypeValue ELSE '' END), @DatabaseName = MAX(CASE TypeName WHEN 'DatabaseName' THEN TypeValue ELSE '' END), @LogTableName = MAX(CASE TypeName WHEN 'LogTableName' THEN TypeValue ELSE '' END) FROM SomeConfigurations WHERE Typeid =1 SELECT @ActiveServerName,@DatabaseName,@LogTableName |
DECLARE @ActiveServerName VARCHAR(100), @DatabaseName VARCHAR(100), @LogTableName VARCHAR(100) SELECT @ActiveServerName = MAX(CASE TypeName WHEN 'ActiveServerName' THEN TypeValue ELSE '' END), @DatabaseName = MAX(CASE TypeName WHEN 'DatabaseName' THEN TypeValue ELSE '' END), @LogTableName = MAX(CASE TypeName WHEN 'LogTableName' THEN TypeValue ELSE '' END) FROM SomeConfigurations WHERE Typeid =1 SELECT @ActiveServerName,@DatabaseName,@LogTableName
And the same applies here as to pivot, this query will hit the table only one time.
So in the end, which do you prefer?
A) 3 selects (because you get paid by lines of code)
B) Pivot (because nobody else understand pivot syntax, so this is job security)
C) Case statement (because you are still running NT 4.0 with SQL Server 2000 SP4)
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
4 Comments
Denis,
Great post in showing these T-SQL alternatives.
We all have seen our share of option #1 – easy to understand and explain, but not the least number of code lines to run or maintain.
The pivot option (#2) is interesting (clever – I wouldn’t have thought of that solution), but not necessarily my favorite – it may be harder to explain how it works to others (Pivot operator is not as commonly used as other T-SQL operators) and harder to recall how it works a year later when you have to maintain the code – depending on how often you use and stay current on this approach in different apps. I may change my mind on this one over time, as it grows on me.
I actually like the case option (#3) best, as it is easy (for me) to understand now (and I suspect in a year), fewer lines of code to maintain than option #1, usable on more versions of SQL Server (old and new), easy to expand to more parameters (only one extra line per new parameter in the same where clause set), and should be easy to explain how it works to others.
From my lack of experience with the Pivot operator, I would have thought that the FOR TypeName IN … clause in your Pivot example:
FOR TypeName IN (ActiveServerName, DatabaseName, LogTableName)
needed to have each of the three IN clause values in quotes (as would be used in a SELECT … WHERE … IN clause):
FOR TypeName IN (‘ActiveServerName’, ‘DatabaseName’, ‘LogTableName’)
But in trying your example, I find that not only does your example work just fine as shown, but it will NOT work if you use quotes! Learn something new every day!
All in all, a good comparison and exposure to different ways of solving the same task. Thanks again!
Scott R.
Scott,
Thanks for your comment. The reason that PIVOT doesn’t need quotes (I think) is that those now become column names and are not values anymore.
The real reason I( added the CASE query is that PIVOT is a little intimidating at first, I still have to look up the syntax every single time I use it.
Reminds me of user defined functions, I had to lookup the syntax for years…of course I would just copy an existing one and modify it…made life easier…BTW I do the same foe pivot…copy an existing one..change columns and table name and done
What we less advanced user of SQL prefer is to keep settings in a single row table with lots of columns, so that we don’t have to break the keyboard typing just to query some config values :-))
wqw,
That is also an option but becomes a pain in the neck to maintain because you will need to add a column every time you have to add some other configuration value