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

Question: How do you check ANSI compatibility of SQL Server queries? SET FIPS_FLAGGER can flag constructs against the historical FIPS 127-2 standard. It doesn’t certify that a query will behave identically in another database product.

This question surprised me during a Comprehensive Database Performance Health Check. I had used this feature when moving work between relational database systems, but hadn’t heard it as an interview question.

The checker offers ENTRY, INTERMEDIATE and FULL levels. Its FIPS 127-2 foundation comes from SQL-92. That is a historical language standard, not the latest SQL standard or a check of today’s product-specific behavior.

Try the original demonstration

The sample uses AdventureWorks and its DatabaseLog table. Select the corresponding database installed on your server; here it is AdventureWorks2025.

USE AdventureWorks2025;
GO
SET FIPS_FLAGGER 'FULL';
GO
SELECT TOP 5 *
FROM [dbo].[DatabaseLog];
GO
SET FIPS_FLAGGER OFF;
GO

Read the Messages tab for standards warnings. TOP and bracket-delimited identifiers are SQL Server extensions, so this is deliberately not a portable example. The query still runs: the setting reports warnings, rather than converting the statement into portable SQL.

Use separate batches as shown. FIPS_FLAGGER acts at parse time, so placing OFF later in the same batch can change which text is checked. Restore OFF before continuing ordinary work.

A clean warning list is only the start of a portability review. Test the query on the intended target product with the same data, including nulls and boundary values. Compare the values and data types it returns. SQL Server compatibility levels don’t turn T-SQL into another product’s dialect.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.