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))

 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
  SET @BinNumber = SUBSTRING(
    '0123456789', (@SettingNumber % 2) + 1, 1) + @BinNumber
  SET @SettingNumber = @SettingNumber / 2
    @BinNumber,LEN(@BinNumber)[email protected]+1,1) as Integer)

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


  • Clustering Non-Numeric Data Using C#

    Clustering non-numeric -- or categorial -- data is surprisingly difficult, but it's explained here by resident data scientist Dr. James McCaffrey of Microsoft Research, who provides all the code you need for a complete system using an algorithm based on a metric called category utility (CU), a measure how much information you gain by clustering.

  • So What's Up with Microsoft's (and Everyone Else's) Love of Rust?

    Microsoft already stewards several popular programming languages -- C#, TypeScript, F# -- so what's up with its love of Rust, along with the rest of the world?

  • C# Steps Up Programming Language Popularity Ladder

    Microsoft's C# programming language climbed a year-over-year notch on the TIOBE Index, which measures popularity among developers.

  • VS Code Java Tool Updates Debugging, Refactoring

    The monthly update to the tooling that boosts Java development in the open source, cross-platform Visual Studio Code editor highlights debugging, refactoring and more.

  • Microsoft Plugs Away at Blazor for Mobile in Preview 3

    Microsoft is furthering its work to target mobile app development with Blazor, the ASP.NET Core offering that originally was developed to allow for C#-based web development instead of JavaScript through the use of WebAssembly for the client side.

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.

Upcoming Events