The (nocount/ansi_nulls) option in SQL Server

1 Nocount Options

When SET NOCOUNT is on, no count is returned.
When SET NOCOUNT is OFF, the count is returned.

eg

if object_id(N'table_test'N'U') is  not NULLDrop Tabletable_testGoCREATE TABLETable_test (IDINT, nameVARCHAR( -))GoINSERT  intoTable_testVALUES(1,'xiaoming'),(2,NULL)GoSETNOCOUNT onSELECT *  fromtable_testGO

SET NOCOUNT Offselect * from Table_testgo

2 ANSI_NULLS Options

When SET Ansi_nulls is on,
SELECT statements that use WHERE column_name = NULL still return 0 rows, even if the column_name contains null values.

When SET Ansi_nulls is OFF,

Use the WHERE COLUMN_NAME = null SELECT statement to return rows that contain null values in column_name.

eg

SETAnsi_nulls on SELECT TOP  - *  fromTable_testWHEREName= NULL GoSETAnsi_nullsoff SELECT TOP  - *  fromTable_testWHEREName= NULL Go

The (nocount/ansi_nulls) option in SQL Server