DevDisasters
The T-SQL Trick
When fewer SQL Server columns does not mean better performance.
"I can't wait to show you this new feature I cooked up!" Bob exclaimed as Nathan entered his living room.
Bob was Nathan's brother-in-law, and the owner of a small, niche software firm. Nathan, who worked as a database admin, usually didn't offer much in the way of input when Bob had something to show off at family functions. But today was special. Bob had something database-related to bounce off of him. Plus, his brother-in-law had just cracked open a case of tasty imported beer to mark the occasion.
Just Two Will Do!
"Check this out," Bob said. "The user access settings table is just two columns. Two! What do you think? Pretty clever, huh? That's a big difference!"
"Yeah, Bob! Nice one!" Nathan replied, smiling and nodding. He held back a grimace as he recalled the old user access table. Dozens of Boolean columns that were named Flag1, Flag2, ActiveFlag, Flag44, FlagIsBob and so on, each successive column named more mysteriously than the last.
"Yeah, the old table was pretty messy," said Bob. "That's gone now, though -- I replaced it with something nice and simple. One column named UserName and another for the SettingNum of the user."
Nathan chose his next words carefully -- the wrong remark could delay his exit.
"How'd you do it?"
Bob's round face lit up. "I thought you'd never ask!"
Bob's Secret Sauce
He fumbled a bit and pulled up the source of a T-SQL function on his laptop, the likes of which Nathan had never seen:
ALTER FUNCTION [dbo].[FetchUserSetting] (@UserName Varchar(200),
@UserSetting Varchar(200))
RETURNS Integer
as
BEGIN
DECLARE @BinNumber Varchar(200)
DECLARE @SettingNumber Integer, @SettingIndex Integer
SET @BinNumber = ''
SELECT @SettingIndex =
CASE WHEN @UserSetting = 'ShowSplashScreen' THEN 1
WHEN @UserSetting = 'IsManager' THEN 2
WHEN @UserSetting = 'IsAdmin' THEN 3
WHEN @UserSetting = 'IsActiveUser' THEN 4
WHEN @UserSetting = 'IsBob' THEN 5
-- and so on...
ELSE 0 END,
@SettingNumber = uservalue
FROM users
WHERE username = @UserName
WHILE @SettingNumber < > 0
BEGIN
SET @BinNumber = SUBSTRING(
'0123456789', (@SettingNumber % 2) + 1, 1) + @BinNumber
SET @SettingNumber = @SettingNumber / 2
END
RETURN CAST (SUBSTRING(
@BinNumber,LEN(@BinNumber)-@SettingIndex+1,1) as Integer)
END
At this point, Nathan feared it was the beer talking when he asked, "But... why, Bob? Why do it this way?"
"Why?" Bob replied incredulously. "Well to save on bits, of course! Fewer columns mean smaller tables and better performance!"
Code of Silence
For an instant, Nathan considered pointing out that SQL Server was probably smart enough to arrange things so that those dozens of poorly named columns didn't take up all that much space. In fact, storing such a large number to represent all those different settings was going to take up more space in the long run. There was also the matter of hitting a maximum number at some point... but Nathan restrained himself.
"Hah! Yeah, good stuff. Pretty unique solution you got going on there."
Bob laughed back. "Oh, you like it, then... great! Come here and see what I have brewing for the application config files. I call it BobXML!"
About the Author
Mark Bowytz is a contributor to the popular Web site The Daily WTF. He has more than a decade of IT experience and is currently a systems analyst for PPG Industries.