T-SQL or Standard SQL

  • icester

    SSC Eights!

    Points: 927

    Hello SSC,

    I am currently reading through "Querying Microsoft SQL Server 2012" (for the 70-461 Exam).  The book suggests to use Standard SQL when possible unless T-SQL offers functionality that Standard SQL does not.  The reason for this is portability of the code and portability of knowledge.  Talking with another DBA it sounds like the industry standard is the other way around, to use T-SQL (or PL/SQL for Oracle) with no concern of what the standard SQL is, but rather whatever is easier to interpret and/or use.  Two examples I have are COALESCE vs ISNULL and CURRENT_TIMESTAMP vs GETDATE.

    COALSCE vs ISNULL
    COALSCE(city, '') and ISNULL(city,'') produce the same result.  COALSCE, however, has more functionality than ISNULL.  The only difference between these two is how they work with NULLs in SELECT INTO statements.  The book suggests to use COALSCE since it is the SQL Standard instead of the T-SQL ISNULL.  The argument for using ISNULL is only because it's easier to read.

    GETDATE vs CURRENT_TIMESTAMP
    SELECT GETDATE() and SELECT CURRENT_TIMESTAMP yield the same result.  The difference being GETDATE is T-SQL and CURRENT_TIMESTAMP is standard SQL.  With this, too, I hear the common practice is to use GETDATE when using T-SQL rather than using CURRENT_TIMESTAMP, even though CURRENT_TIMESTAMP is portable.

    I'm sure the answer comes down to personal choice and business needs, but is there a "best practice", as the book suggests?  Is it common practice to use whatever is more convenient, the T-SQL vs Standard SQL, or to keep the code portable by using Standard SQL where applicable?

    Thanks!
    Dan

  • Lynn Pettis

    SSC Guru

    Points: 442116

    It really comes down to what is needed.  For instance, I tend to use ISNULL more than COALSCE, it really comes down to what I need to accomplish. As for GETDATE() and CURRENT_TIMESTAMP, I just use GETDATE().

    Truly portable code is a fantasy.  Unless you are supporting multiple dialects of SQL databases, I tend to stick with T-SQL.  Just how often do companies really change the RDBMS they are using.  Write your code so that you make the most of the database engine being used.  I would do this even if supporting multiple RDBMS's even if it meant having different code bases for each for the same application.

  • Jeff Moden

    SSC Guru

    Points: 993882

    icester - Wednesday, March 7, 2018 10:20 AM

    Hello SSC,

    I am currently reading through "Querying Microsoft SQL Server 2012" (for the 70-461 Exam).  The book suggests to use Standard SQL when possible unless T-SQL offers functionality that Standard SQL does not.  The reason for this is portability of the code and portability of knowledge.  Talking with another DBA it sounds like the industry standard is the other way around, to use T-SQL (or PL/SQL for Oracle) with no concern of what the standard SQL is, but rather whatever is easier to interpret and/or use.  Two examples I have are COALESCE vs ISNULL and CURRENT_TIMESTAMP vs GETDATE.

    COALSCE vs ISNULL
    COALSCE(city, '') and ISNULL(city,'') produce the same result.  COALSCE, however, has more functionality than ISNULL.  The only difference between these two is how they work with NULLs in SELECT INTO statements.  The book suggests to use COALSCE since it is the SQL Standard instead of the T-SQL ISNULL.  The argument for using ISNULL is only because it's easier to read.

    GETDATE vs CURRENT_TIMESTAMP
    SELECT GETDATE() and SELECT CURRENT_TIMESTAMP yield the same result.  The difference being GETDATE is T-SQL and CURRENT_TIMESTAMP is standard SQL.  With this, too, I hear the common practice is to use GETDATE when using T-SQL rather than using CURRENT_TIMESTAMP, even though CURRENT_TIMESTAMP is portable.

    I'm sure the answer comes down to personal choice and business needs, but is there a "best practice", as the book suggests?  Is it common practice to use whatever is more convenient, the T-SQL vs Standard SQL, or to keep the code portable by using Standard SQL where applicable?

    Thanks!
    Dan

    I agree with the DBA you spoke with.  First, true portability is a myth, period.  Second, not using the incredible features of whatever RDBMS is available for the sake of portability is stupid, IMNSHO.  It's like saying that you can't use the trig functions on your scientific calculator because the next person that needs to do the job only knows how to use a 4 function calculator.  Life is difficult enough without such crazy and ineffective limitations.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • icester

    SSC Eights!

    Points: 927

    Thanks Lynn & Jeff for your input.  I too will stick with using T-SQL over Standard SQL.  I do find it interesting that this book suggests using Standard SQL over T-SQL when possible, being it's a Microsoft book.  

    Thanks again!

    Dan

  • Jeff Moden

    SSC Guru

    Points: 993882

    I'll also add... long live the DATETIME datatype.  The DATETIME2, DATE, and TIME datatypes are crippled in comparison. "Change is inevitable... change for the better is not".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Alan Burstein

    SSC Guru

    Points: 61019

    icester - Wednesday, March 7, 2018 1:07 PM

    Thanks Lynn & Jeff for your input.  I too will stick with using T-SQL over Standard SQL.  I do find it interesting that this book suggests using Standard SQL over T-SQL when possible, being it's a Microsoft book.  

    Thanks again!

    Dan

    I too find using T-SQL easier in most cases. You're more likely to come across a unicorn than you are to find portable SQL code. 

    It's worth noting, however, one of the authors, Itzik Ben-Gan seems to advocate for learning the standard SQL along with the differences between T-SQL and ANSI SQL. I think it's more of a Ben-Gan position than a Microsoft one; you will see this in his other books too. Understanding the ANSI standard has made it easier for me port my code over to other languages as needed. 

    I work on SQL Server but work in an environment which is mostly PostgreSQL and Redshift (also PostgreSQL). I sometimes use more PostgreSQL friendly syntax for the benefit of the developers I work with on the PostgresSQL side of the house. E.g. COALESCE instead of ISNULL, OFFSET/FETCH instead of TOP. People familiar, for example, with DelimitedSplit8K, will easily understand my PostgreSQL splitter logic:

    WITH -- SomeString (BELOW) IS A pseudo variable I'm using for testing
    SomeString AS (SELECT '21355,11555,xxx,6688,09tt'::text AS string), -- note the conversion
    E1 AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as poo),
    iTally AS
    (
    SELECT 0 AS N UNION ALL
    SELECT ROW_NUMBER() OVER (ORDER BY NULL)::int
    FROM E1 as A
    CROSS JOIN E1 as B
    CROSS JOIN E1 as C
    )
    SELECT
    N AS itemNumber,
    N+1 AS itemIndex,
    COALESCE(NULLIF(strpos(SUBSTRING(string from N+1 for length(string)), ','), 0), length(string)),
    SUBSTRING
    (
      string
      from N+1
      for COALESCE(NULLIF(strpos(SUBSTRING(string from N+1 for length(string)), ','), 0), length(string))
    )
    FROM iTally
    CROSS JOIN SomeString
    WHERE N < length(string)
    AND (N=0 OR SUBSTRING(string from N for 1) = ',');

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code.  -- Itzik Ben-Gan 2001

Viewing 6 posts - 1 through 6 (of 6 total)

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