An interesting thing happened to me yesterday. A coworker came up to me and asked, “Did you write this SQL statement?” After looking at it I realized it was a statement I had written about a year ago. In this statement, I had come up with a clever way of creating what is essentially a SQL identity column. The first thought that came to my mind was “What an idiot! I could have just added an identity column to the table!” But what my coworker noticed, that I did not, is that this statement has powerful implications.
alter table temp_your_table add Reference_Number int declare @intCounter int set @intCounter = 1000 update temp_your_table SET @intCounter = Reference_Number = @intCounter + 1
The neat thing about this statement is that it can be used, instead of a loop, to mathematically manipulate a column. For example you could add a column of Fibonacci numbers! I’m not sure why a person would need a column of Fibonacci numbers, but it’s empowering to know that you could. 😉 There are probably several other neat applications that could be done with this approach.
Truth be told, things like this happen when a college grad with limited SQL knowledge, but an abundance of C++ education, begins learning SQL.
** UPDATE **
** 5/16/2006 **
Well I found a great use for the SQL statement above. For a recent project I needed to create a region specific drop down list of languages. If a user is visiting our Spanish site their drop down list needs to include the Spanish spellings of our language options in alphabetical order, BUT with EspaÃ±ol on top of the list. Likewise, if the user is visiting our Portuguese site they need to see the list of languages with the Portuguese spelling, but with Portuguese on top.
To do this I sorted my list of languages in alphabetical order into a temp table with an “OrderNumber” column. Then I updated the “OrderNumber” column with an incrementing integer starting with 1. Next I updated my temp table, setting the “OrderNumber” of the default language to 0. Finally, I return the temp table ordered on my “OrderNumber” column. The whole procedure looks like the following. (I highlighted the “Haunted SQL Statement” from above in red.):
CREATE procedure GetLocalLanguageNameList @LanguageCode varchar(8) as set nocount on -- Find the ID of the Language the Page is Requesting declare @LocalLanguageId as int set @LocalLanguageId = (select LanguageId from Language where LanguageCode = @LanguageCode) -- Get a list of alternative languages in the native tongue of the user -- Add OrderNumber so we can use it to put the original Page Language ontop -- Sore the alternative languages in alphabetical order select l.LanguageCode, lln.LanguageName, 0 as OrderNumber into #tempLangs from dbo.LocalLanguageName lln join dbo.Language l on lln.LanguageId = l.LanguageId where lln.LocalLanguageId = @LocalLanguageId order by lln.LanguageName -- Number our languages in alphabetical order declare @intCounter int set @intCounter = 1 update #tempLangs SET @intCounter = OrderNumber = @intCounter + 1 -- Update the placement of the current pages language to the front of our list update #tempLangs set OrderNumber = 0 where LanguageCode = @LanguageCode -- Display the list in our final order select LanguageCode, LanguageName from #tempLangs order by OrderNumber
2 thoughts on “A Haunting SQL Statement”
The haunting Sql statement article is AWESOME. All this time working in Sql, and I had no idea you could daisy chain equality assignments like that. Areyou sure that’s documented? LOL.The language list though… I think you’re taking 3 steps to do what could be done in 1. I know this is a small dataset so it’s not obvious that even aseveralfold improvement in performance makes a practical difference, but… if this procis being called from a website that’s getting hit by a gazillion users for example, it could become relevant. Also, you’re doing an update statement at one point, which is a logged operation, which will also impact speed performance, and if you’ve got a gazillion usershitting your website, your log will start to fill up.The first thing you could do, is replace your temp table usage with a table variable, and do an insert into that, rather than a select into the temp table. Because table variables arescoped, SQL nows whatever table you’re constructing will disappear after the code that’s using it is done running. So, inserts, updates, and deletes into, to, and from table variables are not logged.But even simpler and faster, you could do this:select l.LanguageCode,lln.LanguageNamefrom dbo.LocalLanguageName llnjoin dbo.Language l on lln.LanguageId = l.LanguageIdwhere lln.LocalLanguageId = @LocalLanguageIdorder by case when l.LanguageCode = @LanguageCode then 0 else 1 end ,lln.LanguageNameBut man, I gotta go back and give you kudos for the haunting Sql thing a second time. I can already think of a dozen places in my own code where that will comein very handy.
Oh yes, that’s quite an elegant solution to my language select list problem. Thanks for the input!I’m glad you enjoyed the haunted statement. As far as daisy chaining equality assignments in SQL being documented… sure it is! You can read about it on http://www.dustyreagan.com! 😉 heheThanks again for the feedback Zackaroo. It’s greatly appreciated.