Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

    Tools

    Tag cloud

    azure book business intelligence ctp database dates denali dmv functions gotcha high availability how to howto indexing mirroring performance sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 sql server 2012 sql server denali sql server integration services ssis t-sql tip trick

    Authors

    Main Categories

    Search

    XML Feeds

    What is RSS?

    Google Ads

    « SQL University – Performance WeekMeme Monday: Eleven Words or Less »
    11
    comments

    Use a database how it was intended to be used

    by SQLDenis on Apr 04, 2011 in categories Data Modelling & Design, Database Programming, Microsoft SQL Server
    Instapaper

    Take a look at this piece of junk code, what pops up in your head when you look at this?

    tsql
    Line number Off | Hide | Select all
    1. CREATE FUNCTION [dbo].[age](@set varchar(10))
    2. RETURNS TABLE
    3. AS
    4. BEGIN
    5.     IF  (@set = 'tall')
    6.          SELECT * from player where height > 180
    7.     ELSE IF (@set = 'average')
    8.          SELECT * from player where height >= 155 and height <=175
    9.     ELSE IF (@set = 'low')
    10.          SELECT * from player where height < 155
    11. END

    If you are thinking silly you, you can't have an IF statement like that in a function, then you have disappointed me. What you really should be saying is the following: why are you hardcoding this, create a heights table and then grab all the heights that are valid for the range

    Yes, I grabbed this example from the following question: TSQL - If..Else statement inside Table-Valued Functions - cant go through…. As you can see several people answered with how to use a CASE statement instead. That is all nice and dandy but the information should be in a table instead

    Single Source of Truth

    The thing that is wrong with that piece of code posted is not the fact that the IF statement doesn't work but the fact that a table was not used to store the info. If you are using a table then you can populate a drop down in a form, use the table in the function, in other procedures, views etc etc. If you need to make a change, you do it in one place and one place only.

    This is a very important thing to understand, if you have the same information from 10 different sources all over the place hen when you need to make a change you are bound to make mistakes. Can you imagine if you need to store tax information, having this hardcoded like that? That is insanity.

    It is not difficult to create such a table.
    Here is a sample table

    tsql
    Line number Off | Hide | Select all
    1. CREATE TABLE Heights ( 
    2.     HeightId int primary key not null,
    3.     HeightDescription varchar(20) not null,
    4.     StartRange smallint not null,
    5.     EndRange smallint not null)
    6. GO

    Now let's insert some data

    tsql
    Line number Off | Hide | Select all
    1. INSERT Heights values(1,'Small',0,154)
    2. INSERT Heights values(2,'Average',155,175)
    3. INSERT Heights values(3,'Tall',176,300)

    Now if I want to know if 181 centimeters is considered tall or not, I can run this

    tsql
    Line number Off | Hide | Select all
    1. SELECT HeightDescription
    2. FROM Heights
    3. WHERE 181 between StartRange and EndRange

    Let's continue by adding another table, this table will have some people and their height.

    tsql
    Line number Off | Hide | Select all
    1. CREATE TABLE Players(Player varchar(200),Height smallint)
    2. GO

    The following people are the shortest and tallest people on record

    tsql
    Line number Off | Hide | Select all
    1. INSERT Players values('Robert Wadlow',272)
    2. INSERT Players values('John Rogan',267)
    3. INSERT Players values('June Rey Balawing',56)
    4. INSERT Players values('Gul Mohammed',57)
    5. INSERT Players values('Pauline Musters',58)

    Now when I want to list all the tall people, my query looks like this

    tsql
    Line number Off | Hide | Select all
    1. SELECT p.*
    2. FROM Players p
    3. JOIN Heights h on p.Height between h.StartRange and h.EndRange
    4. where h.HeightDescription = 'Tall'

    When I want to list all the tall people, my query looks like this

    tsql
    Line number Off | Hide | Select all
    1. SELECT p.*
    2. FROM Players p
    3. JOIN Heights h on p.Height between h.StartRange and h.EndRange
    4. where h.HeightDescription = 'Small'

    If tomorrow I decide that tall people have to be taller than 180 centimeters, I only have to change this in 1 place. This is much cleaner and also easily maintainable.

    P.S.
    Yes, I know, instead of having a player column I should have at least first and last name columns (preferably in a person table) but in the day and age of internet induced ADD I didn't feel it would add anything to the point I was trying to make in this post......so forgive me




    *** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

    About the Author

    User bio imageDenis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
    Social SitingsTwitterFacebookLinkedInHomePageFlickrLTD RSS Feed
    979 views
    Read More Posts by SQLDenis
    Instapaper

    11 comments

    Comment from: Erik [Member] Email
    Erik What goes through my mind when I see code like that is "AUUUGH this code makes me want to barf!"
    04/04/11 @ 22:07
    Comment from: LNBruno [Member] Email
    LNBruno What goes through my mind when I see code like that is "Yeah, I have to support that type of insanity."

    But, that's what happens sometimes when your "developers" graduate to data table design via the Excel--> Access--> RDBMS path.
    04/05/11 @ 06:34
    Comment from: Jamie Thomson [Visitor] Email
    Jamie Thomson Good post Denis. I would be tempted to solve this slightly different however because, IMO, you have the same information stored twice. [Heights].[EndRange] is implied by [Heights].[StartRange] of the proceding [HeightId] and hence I would be tempted to solve this like so:

    CREATE TABLE Heights (
    HeightId INT PRIMARY KEY not null,
    HeightDescription VARCHAR(20) not null,
    StartRange SMALLINT not null)

    INSERT Heights VALUES(1,'Small',0)
    INSERT Heights VALUES(2,'Average',155)
    INSERT Heights VALUES(3,'Tall',176)

    create view vHeights as
    select h.HeightId,h.HeightDescription,h.StartRange,COALESCE(MIN(h_2.StartRange)-1,300)
    from Heights h
    left outer join Heights h_2
    on h.StartRange < h_2.StartRange
    group by
    h.HeightId,h.HeightDescription,h.StartRange


    The advantage of this approach is that you have preserved data integrity. The disadvantage is of course that its going to take longer to query it. Gotta love trade-offs.

    JT
    04/05/11 @ 08:46
    Comment from: SQLDenis [Member] Email
    SQLDenis Jamie, that would work...anything is fine with me as long it is not hard coded
    04/05/11 @ 09:36
    Comment from: chopstik [Member]
    chopstik This is the result when you have developers who don't want to deal with the hassle of databases and figure they can handle all of their problems via whatever front-end code they want to write. Just because you can doesn't mean you should. *sigh*
    04/05/11 @ 10:49
    Comment from: Erik [Member] Email
    Erik Jamie,

    I agree with the table design. To my surprise, your view performed better than this query. Here it is as at least one more tool for your toolbox:




    SELECT P.* , H.*
    FROM
    Players P
    CROSS APPLY (
    SELECT TOP 1 HeightDescription
    FROM Heights H
    WHERE P.Height >= H.StartRange
    ORDER BY H.StartRange DESC
    ) H
    WHERE
    H.HeightDescription = 'Tall'

    I think the worse performance is not a flaw with this method but a function of the small table sizes. In a situation where the Heights table is very large, I think the cost of doing the view join/aggregate will start to exceed the sort that makes my query worse right now.
    04/05/11 @ 16:37
    Comment from: SQLDenis [Member] Email
    SQLDenis Emtucifor,

    Feel free to make it a blog post,test all kind of scenarios with a table with 10000 rows, 100000 rows and 1 million rows to see if you get a noticeable difference
    04/05/11 @ 16:54
    Comment from: Jamie Thomson [Visitor] Email
    Jamie Thomson Emtucifor,
    Indeed, like Denis I'd like to see see stats on that around bigger volumes. Its certainly an interesting way of solving the problem - I'd like to know if its worth addding that to my "utility belt".

    Jamie
    04/05/11 @ 23:45
    Comment from: Erik [Member] Email
    Erik Heights is not a good example for 10,000+ rows because there wouldn't be that many classifications. Even if it was a sales volume pricing structure that was very complicated, I couldn't possibly imagine more than about 10 price points for different volumes.

    However, a different scenario where the lookup is a range could make sense: perhaps the column could be a date instead of numeric, like looking up historical product prices on certain dates where the lookup data only has the date the product changed price rather than a row for every calendar date.
    04/06/11 @ 10:25
    Comment from: Tom [Visitor]
    Tom I think there is a far, FAR worse problem with this code.
    "CREATE FUNCTION [dbo].[age](@SET VARCHAR(10))"

    Age? Why is this called age?
    You want a maintenance nightmare, start giving anything to do with physical measurements and dimensions a name that implies time or financial measurements.

    I don't care how good your DB design is, if you have a height function with a name like Age or Income or NumberOfCarsOwned, you have bigger problems than hardcoding Magic Numbers.

    Sure, it may be a typo, but it's a little disturbing that noone mentioned it either here OR at stackoverflow.
    04/14/11 @ 12:19
    Comment from: Naomi Nosonovsky [Member]
    Naomi Nosonovsky Good point, Tom.
    04/14/11 @ 18:34

    Leave a comment


    Your email address will not be revealed on this site.

    Your URL will be displayed.
    (Line breaks become <br />)
    (Name, email & website)
    (Allow users to contact you through a message form (your email will not be revealed.)

    Powered by B2Evolution
    Valid XHTML | Valid CSS