Old Dog, New Tricks

  • Steve Jones - SSC Editor (2/2/2012)


    davidandrews13 (2/2/2012)


    isn't that just down to formatting though? the fact that the article has coloured the ColumnNames in green, which wouldn't be the case normally, just highlights it even more.

    Two issues there. First, width. As you get more complex, it's not necessarily fitting easily on a screen. second, scanning for a column, based on a result, I think it's easier to see.

    select

    TotalSales = sum( itemquantity * unitprice) - salestax

    , Profit = sum( itemquantity * unit price) - salestax - sum( itemcost * itemquantity)

    - shippingcost - othercost

    , itemquantity

    , customername = (firstname + ' ' + substring( middlename, 1, 1) + ' ' + lastname + ' ' + suffix

    from ...

    its definitely a personal preference thing. i guess the way the article shows it, means anyone will write the code in the same easily readable manner without worrying about trying to format it with TABs etc.

    the way i write it, if the column is a long column, i'll put it on to 2,3 or more lines just to make it more readable anyway.

    select

    DATEADD(day,15,getdate())AS myTime

    ,DATEADD(day,13,getdate())AS myExtraTime

    ,'this is a' + ' long long long long' +

    ' string'AS myString

  • I agree with davidandrews13 that it is a preference thing, however, there is plenty of empirical evidence that states that the smaller element of code should appear before the larger for easier understanding e.g. you should reverse the if statement around if the else block is smaller than the if block. Could this be an example of it?

    Oh, you're about to ask for a reference to the evidence? I think you will find some reported in Steve McConnell's Code Complete. I hope my memory has held up...what was that about old dogs???

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Gary Varga (2/2/2012)


    Rod at work (2/2/2012)


    Semicolons will at some point be required in T-SQL? Really? I've seen it used in some cases, but am completely uncertain as to when they are to be used. I write C# code, and before that C and some C++. It's easy to know when to use semicolons in those languanges. So, when do you use semicolons in T-SQL?

    My understanding is that they are currently an optional delimiter but can be used at the end of each statement just like the C family of languages where they are mandatory.

    OK, just to clarify, let me provide a code snippet from a SP I wrote several years ago:

    IF (@VoucherServiceCode > 500) AND (@VoucherServiceCode < 700)

    BEGIN

    /* Here's where we check the daily services. */

    IF ((SELECT Count(*) FROM VoucherServices WHERE

    VoucherNumber = @VoucherNumber AND

    VoucherServiceCode = @VoucherServiceCode AND

    DateOfService = @DateOfService) > 0)

    BEGIN

    RAISERROR('A Daily Service already Exists for this Treatment Type for this date.',11,1)

    RETURN

    END

    END

    ELSE

    BEGIN

    IF @VoucherServiceCode = 700

    BEGIN

    SELECT @CountServ = (SELECT Count(*) FROM VoucherServices WHERE

    VoucherNumber = @VoucherNumber AND

    VoucherServiceCode = @VoucherServiceCode AND

    DateOfService = @DateOfService)

    IF (@CountServ > 1)

    BEGIN

    RAISERROR('Methadone Services have been invoiced for this Month. No changes allowed...',11,1)

    RETURN

    END

    END

    END

    If I have to use semicolons at the end of statements, then I can see making a SELECT statement from above looking like this:

    SELECT Count(*) FROM VoucherServices WHERE

    VoucherNumber = @VoucherNumber AND

    VoucherServiceCode = @VoucherServiceCode AND

    DateOfService = @DateOfService;

    that's easy. And I'm sure that if it's in a conditional, like my lines above, then I wouldn't put in a semicolon. But what about a closing END keyword? Do you put semicolons there? e.g.:

    IF (@CountServ > 1)

    BEGIN

    RAISERROR('Methadone Services have been invoiced for this Month. No changes allowed...',11,1)

    RETURN

    END;

    END;

    I've seen semicolons applied to the end of END closing statements in some languages, will they be required in some future version of T-SQL?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • cengland0 (2/2/2012)


    IceDread (2/2/2012)


    About aging, it's been proven recently that people over 45 solves equations a bit more slowly than younger ones.

    Really? I'd like to see that documentation. I'm over 45 and believe I solve problems and equations much faster than my younger peers. That's because I have more experience doing it. Maybe once they are my age, they can do it faster too.

    I tried to find the reference, I believe I read it in http://www.svd.se or http://www.dn.se not that long ago but I could not.

    I will get back to you if I do find it.

    Because I could not let it go, I did some digging. This is what I found http://www.fas.se/upload/dokument/ALI%20pdf-skrifter/isbn9170453950.pdf

    While one professor writes "software gets better with age but the hardware degrades" there are several articles reviewed in that book and there is no conclusive evidence for that claim, or the claim that I made.

  • IceDread (2/2/2012)


    Because I could not let it go, I did some digging. This is what I found http://www.fas.se/upload/dokument/ALI%20pdf-skrifter/isbn9170453950.pdf

    While one professor writes "software gets better with age but the hardware degrades" there are several articles reviewed in that book and there is no conclusive evidence for that claim, or the claim that I made.

    Tried to read it but it's so complicated to understand. It's almost like it's in some foreign language or something. 🙂

  • I've seen semicolons applied to the end of END closing statements in some languages, will they be required in some future version of T-SQL?

    I've been trying to get into the habit of using semicolons for a while now, since they're already required in some T-SQL statements such as WITH. I'm sure that list will grow in 2012 and versions beyond.

    It helps to think of them as statement delimiters. Your last snippet of code works, but also could be:

    IF (@CountServ > 1)

    BEGIN;

    RAISERROR('Methadone Services have been invoiced for this Month. No changes allowed...',11,1);

    RETURN;

    END;

    END;

    To be honest, for me it has been trial-and-error so far. I've seen a lot of Incorrect syntax near ';' messages!

  • Here's a sample with correct semicolons:

    IF 1 = 1

    BEGIN ;

    PRINT 'Yep' ;

    END ;

    ELSE

    BEGIN;

    PRINT 'Nope' ;

    END ;

    Note that the IF statement doesn't end with one, nor does ELSE. That's because it's actuall "IF 1=1 BEGIN;" as a single statement, and "ELSE BEGIN;" as a single statement. Moving "BEGIN" to the next line is prettier and more readable, but it's still part of one T-SQL statement.

    I've been using semicolons at the end of my SQL statements for years. It's for my own logical breakdown of statements, not for any necessity (except in the few cases where T-SQL requires it, like CTEs in most cases).

    On the other hand, I don't use BEGIN/END to define stored procedures (CREATE PROC dbo.MyProc AS BEGIN code here END). So I'm insonsistent on that point.

    To me, it's like ending sentences with punctuation Just because the reader (compiler) can figure out what I mean doesn't mean I should avoid explicit clarity It just reads better to me that way If you don't like punctuation for clarity that is by all means your choice on the matter There are of course other ways to delimit statements

    (Sorry, couldn't resist that last paragraph.)

    On the point of learning new things, like the editorial points out: Yeah, I'm constantly evolving my standards on various things. I'm a firm believer in the idea that "just because that's how we've been doing it, doesn't mean that's the way we should continue doing it". After all, the ability to rapidly learn new things, and learn from other people's experiences, is a large part of what makes us this planet's apex predator, instead of being saber-tooth tiger food.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thank you, Randy Rabin and GSquared, for giving me code examples using semicolons, especially with respect to keywords like BEGIN and END. I'll start using semicolons in more of my T-SQL code, so I can get used to it.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • I find gsquared example confusing. Usually ; is end of sqlstatement. With all those ; where is the beginning and end?

  • On the particular point of columnAlias = Somestuff, I dislike it particularly for larger queries.

    They look like where clauses, or joins, to me when I'm scanning large scripts. An AS statement is easier for me to pick up during a high speed scroll looking for the 8th query.

    The AS helps me mentally delineate them as columns. Now, if it was some other symbol then ones we used for equality, I'd be all for it. Pipe comes to mind.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • I find funny when someone complain about by *= versus explict outer join and stills write code in upper case and complains about lower case.

    *=

    OUTER JOIN

    outer join

    😛

    But I digress.

    I'm a old dog. Starting with computers as a hobby at 10, as a begginer at 20, mature professional at 30 and today over 40 I'm still learning.

    (And for sure is easyer to learn a new tool a new technology than learn to do thing in a different way).

    I belive it's the best in this field. All is changing, every day, and you must change as well.

    It's challenging and a live without challenges is like a meal without spice.

    :hehe:

  • jcb (2/3/2012)


    I find funny when someone complain about by *= versus explict outer join and stills write code in upper case and complains about lower case.<Snip />

    Casing of SQL is something I may have missed. I was taught that SQL keywords should always be in uppercase. Have I missed a change?

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • MS SQL Server is mostly case insensitive.

    reserved words are insensitive.

    objects names and strings are insensite (if the collation used is insensitive)

    and only a few things are sensitive like the today's QotD.

  • Until recently, I was using AS. I switched to using =, exactly because of the readability. Of course, I am also rather..... particular when it comes to formatting anyway.

    For me, when writing INSERT statements, I alias every column in the SELECT statement with the column name it corresponds to.

    INSERT INTO [schema].

    (

    col1

    , col2

    )

    SELECT

    col1 = t.colA

    , col2 = t.colB

    FROM

    [schema].[table2] AS t

    ;

    I am also the sort of person who has to align all the variable names and data types, and put them all together under one DECLARE statement at the beginning of the procedure, and has to align the CASE & END, WHEN & ELSE, and each of the THEN statements.

    I know, too picky probably, but it makes things much easier for me to scan through.

  • Uppercase was just convention as opposed to a compiler requirement.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

Viewing 15 posts - 31 through 45 (of 92 total)

You must be logged in to reply to this topic. Login to reply