March 7, 2018 at 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
March 7, 2018 at 10:44 am
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.
March 7, 2018 at 11:22 am
icester - Wednesday, March 7, 2018 10:20 AMHello 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
Change is inevitable... Change for the better is not.
March 7, 2018 at 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
March 7, 2018 at 2:00 pm
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
Change is inevitable... Change for the better is not.
March 7, 2018 at 2:06 pm
icester - Wednesday, March 7, 2018 1:07 PMThanks 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) = ',');
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply