Tag: set statement

SQL SERVER – Check for FIPS 127-2 standards – SET FIPS_FLAGGER – @SQLSERVER


SET FIPS_FLAGGER : This set statement is used to set up the current environment to check for compliance with the FIPS 127-2 standard. There are 3 levels of compliance we can set for FIPS_FLAGGER. They are ENTRY, INTERMEDIATE and FULL. This set statement is set at query parse time. So, SQL Server will return the warning message if the Transact SQL statement conflicts the FIPS standards while parsing.

FIPS (Federal Information Processing Standards), the complete list of 127-2 standards are given in FIPS 127-2.

Syntax

SET FIPS_FLAGGER ‘ENTRY‘  => The standards checking for ISO entry-level compliance. The entry-level SQL is a minor enhancement over the minimum requirements of FIPS PUB 127-1
or
SET FIPS_FLAGGER ‘INTERMEDIATE‘  => The standards checking for ISO intermediate-level compliance. The intermediate level SQL is a major enhancement over Entry level SQL.
or
SET FIPS_FLAGGER ‘FULL‘  => The standards checking for ISO full compliance. The full level SQL is a major enhancement over Intermediate level.
or
SET FIPS_FLAGGER OFF  => It turns off the standard checking for ISO compliance
 

Example

1. Executing the following statement returns a warning message for FIPS compliance check.

SET FIPS_FLAGGER ‘FULL’
GO

Output

pic1

SQL SERVER – SET CONCAT_NULL_YIELDS_NULL – SET Statement @SQLServer


SET CONCAT_NULL_YIELDS_NULL : This set statement is used to configure the current environment or session to return NULL value while The NULL is concatenated with other values in an expression. This set setting will be ON by default in the future version of SQL Server and it cannot be set to OFF. So, we have to handle this set statement carefully in the current and future application design.

The best practice is to keep the SET CONCAT_NULL_YIELDS_NULL to ON throughout the data processing.

Usage

SET CONCAT_NULL_YIELDS_NULL ON

or

SET CONCAT_NULL_YIELDS_NULL OFF

Example

1. A simple select statement that concatenates a string with NULL value.

When CONCAT_NULL_YIELDS_NULL is OFF, it returns the string value.pic1When CONCAT_NULL_YIELDS_NULL is ON, it returns NULL value.

pic2I hope that you have understood the use of this SET statement. Happy coding.

SQL SERVER – SET STATISTICS PROFILE – TSQL – SET Statement


SET STATISTICS PROFILE : This set statements used to get the query output (result set) and query plan execution information with row count value. There are other set statements such as SHOWPLAN_TEXT, SHOWPLAN_XML and SHOWPLAN_ALL are also used to get the query execution plan but it will not return the result set as part of the output. This is very useful when we need both result set and execution plan information for performance tuning.

Usage

SET STATISTICS PROFILE ON

or

SET STATISTICS PROFILE OFF

Example

Executing a query when SET STATISTICS PROFILE is ON.

pic1Graphical execution plan look like this

pic2

SQL SERVER – SET STATISTICS TIME – SET Statement


SET STATISTICS TIME : This set statement is used to get the total time required to parse, compile and execute a SQL statement. The time is represented as milliseconds.

We may not get an accurate result (process time) if we enabled the lightweight pooling option.

The cpu column in the sysprocesses table is only updated when a query executes with SET STATISTICS TIME ON.

Example

pic1when Statistics Time is ON,

pic2SET STATISTICS TIME Usage

SET STATISTICS TIME ON

or

SET STATISTICS TIME OFF

Example

pic3This set statement is another important statement in the SQL query performance tuning.

SQL SERVER – SET STATISTICS IO – DROPCLEANBUFFERS- SET Statement


SET STATISTICS IO : This set statement gives information about the disk IO activities of the T-SQL statements executed in the current session.

It displays disk information when it is set to ON and it hides the disk information when it is set to OFF.

Usage

SET STATISTICS IO ON

or

SET STATISTICS IO OFF

Disk activity information

pic1Sample output when STATISTICS IO is ON

pic2

DBCC DROPCLEANBUFFERS

It cleans all buffers from the buffer pool and it helps user to see the actual query performance without using cache data. It should be the first step before we start query performance tuning.

Note: We should not execute this DBCC command in the production server.Because, it will remove all the cached data from the buffer and make the query execution slower for the all new queries.

Example

When STATISTICS IO is ON and running a SQL query in the query windows

DBCC DROPCLEANBUFFERS
GO

set statistics io on
go

select * from [Person].[PersonPhone]

set statistics io off
go

pic4Statistics io data is very important while doing query performance tuning. I hope you have learned about STATISTICS IO set statement from this blog post. Please let me know if you have any questions.