SET ANSI_NULLS ON mean?

2011-05-30  来源:本站原创  分类:Database  人气:77 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

These are the SQL-92 set the statement, so SQL Server 2000/2005 to comply with SQL-92 rules.
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and text must be delimited by single quotes. When SET QUOTED_IDENTIFIER is OFF, identifiers can not be quoted, and must comply with all Transact-SQL rules for identifiers.
SQL-92 standard air values ​​in the equal (=) or not equal (<>) comparison value is FALSE. When SET ANSI_NULLS is ON, even if column_name contains null values, use the WHERE column_name = NULL in the SELECT statement returns zero rows. Even if column_name contains non-null value, using WHERE column_name <> NULL in the SELECT statement will return zero rows.
When SET ANSI_NULLS is OFF, the Equals (=) and not equal (<>) comparison operators do not comply with the SQL-92 standard. Using WHERE column_name = NULL in SELECT statement returns column_name row contains null values. Using WHERE column_name <> NULL in the SELECT statement returns the column contains non-null values. In addition, using WHERE column_name <> XYZ_value the SELECT statement returns all not XYZ_value not as NULL.

相关文章
  • Sql Server's SET ANSI_NULLS 2010-07-27

    When SET ANSI_NULLS is ON, even if column_name contains null values, use the WHERE column_name = NULL in the SELECT statement returns zero rows. Even if column_name contains non-null value, use the WHERE column_name <> NULL in the SELECT statement w

  • SET ANSI_NULLS ON mean? 2011-05-30

    SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO These are the SQL-92 set the statement, so SQL Server 2000/2005 to comply with SQL-92 rules. When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and text must be d

  • SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON and mean 2011-07-01

    http://blog.sina.com.cn/s/blog_5e7917a50100bzq6.html SET QUOTED_IDENTIFIER ON GO What does it mean? Grammar SET QUOTED_IDENTIFIER {ON | OFF} Note When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and text must

  • (Transfer) the reasons for slow database query 2010-02-04

    ● query slow for many reasons, common following categories: 1, no index or index is not used (this is the most common problems slow query is programming defects) 2, I / O throughput is small, the formation of a bottleneck effect. 3, does not create a

  • ORACLE and SQL syntax differences summarized 2010-03-04

    Comparison of data types Type name Oracle SQLServer Compare Character data types CHAR CHAR are fixed-length character data, but the maximum degree of oracle which 2kb, SQLServer which the maximum length of 8kb Variable-length character data type VARC

  • Sample stored procedure 2010-03-11

    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[SP_TMS_AUTO_BILLING] ( @V_TMS_ORDER_ID VARCHAR (32), -- Order master ID @V_RETUREN_VALUE VARCHAR (20) output -- State returns ) AS /*************************************************

  • Decomposition of the string-sql function to execute with parameters 2010-03-12

    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go - ============================================= - Author: <MIN> - Create date: <2007-12-12> - Description: <decomposition string> - ============================================= --- Implement

  • Characters using xml as a parameter (the stored procedure) 2010-03-19

    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[SP_RPT_AP_BILLING_SUMMARY] @TMS_FREIGHT_IDS varchar(8000) ---FREIGHT_ID WITH EXECUTE AS CALLER AS BEGIN declare @idoc int exec sp_xml_preparedocument @idoc output,@TMS_FREIGHT_IDS S

  • recursive query sql server 2005 2010-03-31

    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go /** ** **author: zhyd **date:20100331 ** ** Gets the current user and the Organization of all sub-organizations of that organization ( Include your own ) **/ ALTER proc [dbo].[SP_GET_OWNER_OFFICE]( @CREAT

  • ASP.NET, so images in binary form stored in a database 2010-03-30

    This morning, acquaintances and asked my question, I used to save all files directly in the database name, has not tried to store an entire image into a database, Internet search a bit, but it is some test code is as follows : Table set up to save th

  • 10 hex and 36 hex conversion function 2010-04-06

    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- AUTHOR: <ZHYD> -- CREATE DATE: <2010-03-12> -- DESCRIPTION: < Decimal-binary conversions into 36 > -- =====================================

  • A SQL Server2000 and 2005 can be used for paging method 2010-04-07

    (1) Stored Procedure (SQL2000 and 2005 are available under): USE [Northwind] GO /****** Object : StoredProcedure [dbo].[p_GetOrders] Script date : 03/29/2010 23:00:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[p_G

  • Flex and Java integration to connect the database instance 2010-04-15

    Flex and Java integration to connect the database instance of the effect of photo: Development Tools: eclipse + flex Database: SQL SERVER 2005 Firstly, the database: Database: companyDB Data Table: company USE [companyDB] GO /****** Object: Table [db

  • not in null and null operator 2010-04-23

    Not in \ in the column contains null values does not rule out the null (because null! = Null) Oracle not in (if subquery returns a null value or set of values, it will not record query Solution: You can limit the query sub-null return value is not nu

  • Universal paging stored procedure SQL Server2005 2010-04-29

    SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO - ============================================= - Author: hao.w - Create date: 2008.1.15 - Description: SqlServer2005 general paging method - ============================================= create PROCED

  • Garbage good useful book two stored procedures 2010-05-12

    Source: SQL Dynamic Crosstab http://blog.csdn.net/cjh47349038/archive/2008/11/12/3285082.aspx 5.2 Cross Table Decision Analysis Program (book) http://ajava.org/readbook/java/javafabd/9240.html Using stored procedures to achieve cross-tabular data, qu

  • Stored Procedure operation 2010-06-07

    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go - Goods shipped - Return: -1 data overflow ALTER PROCEDURE [dbo]. [Ap_PopPro] @ Pro_id int, - Product ID @ Wh_id int, - Warehouse No. @ Num int - the number of library AS BEGIN declare @ Pos1 int, - start

  • MS SQL Server Query Optimization 2010-06-09

    MS SQL Server Query Optimization Study: xmllover 2007-11-29 Query slow for many reasons, as several common 1, no index or index is not used (this is the most common problems slow query is programming defects) 2, I / O throughput is small, the formati

  • Paging stored procedure 2010-06-09

    In SQL Server2005 added a new function: row_number (), returns the result set partition expert serial number. So I will now use this function to be a common paging method, and before that we used three methods often compared to its advantages: first,

  • SqlServer stored procedure using a cursor example 2010-06-17

    USE [Test] GO /****** Object: StoredProcedure [dbo].[testKenter] Script Date: 06/17/2010 16:08:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: kenter -- Create date: 2010-06-17 -