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.

comments powered by Disqus

Reader Comments:

Thu, Dec 13, 2012 Jeff_Yao Canada

typo in my last comment "...is worth the sacrifice..." should be read as "...is NOT worth the sacrifice..."

Thu, Dec 13, 2012 Jeff_Yao Canada

Totally agree. To condense the columns for the sake of space and the so called performance improvement (no proof) is worth the sacrifice of easily-understood data model. In late 1980s and early 1990s, when memory is small and expensive, we tried all these kind of tricks to squeeze the code and make the code hardly maintainable, and it indeed caused not only pain but later more expensive refactoring efforts to ensure easy maintenance and better productivity (i.e. less trained people can quickly pick up the work)

Thu, Oct 11, 2012

Been there, saw similar odd stuff. Many years ago probably would scream when faced with a task to clean up somebody else mess. In these days I believe it is one of the reasons why I stay gainfully employed. The scariest thought - what if Bob would be promoted and you have to report to him?

Add Your Comments Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.