How to check the ANSI Compatibility of SQL Server Queries? – Interview Question of the Week #221

Question: How to check the ANSI Compatibility of SQL Server queries?

Answer: I was totally blown away by this question. I had received this question in my recent Comprehensive Database Performance Health Check, this was indeed extremely unique question and I have yet not heard it before. Fortunately, I know the answer to this question as I have to use this feature to move a database from one relational database to another relational database.

It is very important that one writes the SQL code that is portable between different relational database management systems. The Federal Information Processing Standard (FIPS) is usually applicable for the systems which are purchased by the US Government. The latest standard for the same is FIPS 127-2, which is based on the ANSI SQL-92 standard.

Fortunately, T-SQL has a very interesting command FIPS_FLAGGER. There are 3 levels of compliance we can set for FIPS_FLAGGER. They are ENTRY, INTERMEDIATE and FULL.

Let us see a working example of this command to understand how it impacts the compliance of the SQL.

SET FIPS_FLAGGER 'FULL'
GO
SELECT TOP 5 *
FROM [dbo].[DatabaseLog]
GO

When you run the statement above in the message area we get the following message. This shows what are the keywords which are not compliant with the ANSI standard.

FIPS Warning: Line 1 has the non-ANSI statement ‘SET’.
FIPS Warning: Line 1 has the non-ANSI clause ‘TOP’.

Well, if your statement is 100% compliant to ANSI SQL-92 standard you will not see any warnings. Now you can see how this particular setting works.

You can turn off the FIPS_FLAGGER by running the following statement.

SET FIPS_FLAGGER OFF
GO

Reference: Pinal Dave (https://blog.sqlauthority.com)