//
you're reading...
SQL Server

Are the new 2012 T-SQL Features ANSI Compliant?

I have often wondered which of the new feature of SQL Server 2012 T-SQL are ANSI compliant. Through a Google search I ran across the FIPS_FLAGGER setting. (FIPS stands for Federal Information Processing Standard)

The FIPS_FLAGGER setting evaluates a batch of statements at parse time and reports any non-ANSI compliant statements. The messages will appear before the statements actually run and does not  prevent the statements from running.

FIPS_FLAGGER accepts a parameter “LEVEL” which can be ENTRY, INTERMEDIATE, FULL or OFF. The information in BOL doesn’t really explain which level you should use, but with a bit of experimentation I think it should be ENTRY.

Using AdventureWorks 2012, I ran the following code:

SET FIPS_FLAGGER ‘ENTRY’;
SELECT DATEFROMPARTS(2012,8,11);
SELECT IIF(1=2,’One equals two’,’One does not equal two’);
SELECT CHOOSE(1,’A’,’B’,’C’);
SELECT CONCAT(‘abc’,null,’def’);
SELECT TRY_PARSE(‘abc’ AS DATETIME);
SELECT TRY_CONVERT(VARCHAR,GETDATE(),101);
SELECT FORMAT(GETDATE(),’yyyy/mm/dd’);
SELECT ProductID, Name
FROM AdventureWorks2012.Production.Product
ORDER BY name
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

SELECT CustomerID, SalesOrderID, OrderDate, TotalDue,
     SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM AdventureWorks2012.Sales.SalesOrderHeader
ORDER BY CustomerID, SalesOrderID;

BEGIN TRY
     SELECT 1/0
END TRY
BEGIN CATCH
     THROW
END CATCH;

The messages returned look like this:

FIPS Warning: Line 1 has the non-ANSI statement ‘SET’.
FIPS Warning: Line 8 has the non-ANSI function ‘try_convert’.
FIPS Warning: The length of identifier ‘AdventureWorks2012’ exceeds 18.
FIPS Warning: The length of identifier ‘AdventureWorks2012’ exceeds 18.
FIPS Warning: Line 27 has the non-ANSI statement ‘THROW’.

If I change the level to INTERMEDIATE, I get the same results.  If I change to FULL, the warning about TRY_CONVERT is gone.  Notice that the SET command is not compliant? interesting.

AS it turns out the majority of the new T-SQL code is ANSI compliant. As for the non compliant CONVERT or TRY_CONVERT can now be eliminated now that we have the ANSI compliant FORMAT function.

Using FIPS_FLAGGER sometimes reports noise from background processes, when I ran the code a second time the extraneous messages were gone.

Many shops insist that the database code must be ANSI compliant and may have good reasons. If you have the choice, use all the features that are available to you to take advantage of all SQL Server has to offer.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: