DevDisasters

The T-SQL Trick

When fewer SQL Server columns does not mean better performance.

• By Mark Bowytz
• 10/01/2012

"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!"