XML FeedsI 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
- 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.
- 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
- 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
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Denis,
Scott,
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 :-))
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.