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

Featured

  • Build Your First AI Applications with Local AI

    "AI right now feels like a vast space which can be hard to jump into," says Craig Loewen, a senior product manager at Microsoft who is helping devs unsure about making that first daunting leap.

  • On Blazor Component Reusability - From Day 0

    "We want to try to design from Day One, even Day Zero, with reusability in mind," says Blazor expert Allen Conway in imparting his expertise to an audience of hundreds in an online tech event on Tuesday.

  • Decision Tree Regression from Scratch Using C#

    Dr. James McCaffrey from Microsoft Research presents a complete end-to-end demonstration of decision tree regression using the C# language. Unlike most implementations, this one does not use recursion or pointers, which makes the code easy to understand and modify.

  • Visual Studio's AI Future: Copilot .NET Upgrades and More

    At this week's Microsoft Ignite conference, the Visual Studio team showed off a future AI-powered IDE that will leverage GitHub Copilot for legacy app .NET upgrades, along with several more cutting-edge features.

  • PowerShell Gets AI-ified in 'AI Shell' Preview

    Eschewing the term "Copilot," Microsoft introduced a new AI-powered tool for PowerShell called "AI Shell," available in preview.

Subscribe on YouTube