SET ANSI_NULLS ON / OFF – ?

Notes:-

1.They are used in objects like stored procedures and functions.

2.This is a setting for ANSI NULL comparisions.

3.If the option is ON , any statement that compares a identifier with a null returns a value 0.

4.IF the option is OFF , any statement that compares a identifier with a null returns a NULL means nothing.

5.You can set the option like SET ANSI_NULLS ON GO at the begining of the stored procedure.

Below example will explain the things in detail

–Create a table
CREATE TABLE TESTANSINNULL
(
id int identity(1,1) primary key,
[name] varchar(100) NULL
)

–Insert some values in the table
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( ‘Pawan’ )

SELECT * from TESTANSINNULL — Check the table values

OUTPUT
_________
id name
———– ———-
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 Pawan

(6 row(s) affected)

1.
FIRST MAKE THE OPTION OFF
SET ANSI_NULLS OFF
SELECT * FROM TESTANSINNULL where name = null

OUTPUT — This has return all the rows in which the name is null
________

id name
———– ——–
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL

(5 row(s) affected)

2.
FIRST MAKE THE OPTION ON
SET ANSI_NULLS ON
SELECT * FROM TESTANSINNULL where name = null

OUTPUT — This has return 0 rows.
________

id name
———– ——

(0 row(s) affected)

USE OF IS NULL

If you use IS NULL then the queries will return all the rows in which name is null.
In this case ON or OFF doesnt matter . Try out the below examples.

3.
SET ANSI_NULLS ON
SELECT * FROM TESTANSINNULL where name IS NULL

OUTPUT — This has return all the rows in which the name is null
________

id name
———– ——–
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL

(5 row(s) affected)

4.
SET ANSI_NULLS OFF
SELECT * FROM TESTANSINNULL where name IS NULL

OUTPUT — This has return all the rows in which the name is null
________

id name
———– ——–
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL

(5 row(s) affected)

PLEASE NOTE :

1.ANSI_NULLS affect the result of equal to (=)
2.ANSI_NULLS does not affect the result of ISNULL in case of NULL comparisions.

Hence always use ISNULL for checking NULL values.

Pawan Kumar
Pawankkmr@hotmail.com

INFORMATION SCHEMA VIEW HELP IN SQL SERVER 2005

INFORMATION_SCHEMA views are SQL-92 ANSI standards which conforms views with database metadata.

We can use SELECT statements to get their contents.

select * from Information_Schema.Check_Constraints

select * from Information_Schema.Column_Domain_Usage

select * from Information_Schema.Column_Privileges

select * from Information_Schema.Columns

select * from Information_Schema.Constraint_Column_Usage

select * from Information_Schema.Constraint_Table_Usage

select * from Information_Schema.Domain_Constraints

select * from Information_Schema.Domains

select * from Information_Schema.Key_Column_Usage

select * from Information_Schema.Parameters

select * from Information_Schema.Referential_Constraints

select * from Information_Schema.Routine_Columns

select * from Information_Schema.Routines

select * from Information_Schema.Schemata

select * from Information_Schema.Table_Constraints

select * from Information_Schema.Table_Privileges

select * from Information_Schema.Tables

select * from Information_Schema.Views

select * from Information_Schema.View_Column_Usage

select * from Information_Schema.View_Table_Usage

I got this information from an excellent blogger.Please spread the knowledge.

Pawan Kumar
Pawankkmr@hotmail.com

SYSTEM TABLES HELP IN SQL SERVER 2005

1.
SELECT * FROM sys.objects  
Info : Contains a row for each user-defined, schema-scoped object that is created within a database.
sys.objects does not show DDL triggers, because they are not schema-scoped.

2.
select * from sysobjects
SELECT * FROM sys.sysobjects      
Info: Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure.    
 
3.
SELECT * FROM sys.all_columns
Info : Shows the union of all columns belonging to user-defined objects and system objects. 

4.
SELECT * FROM sys.all_objects  
Info : Shows the UNION of all schema-scoped user-defined objects and system objects. 
 
5.
SELECT * FROM sys.all_parameters
Info : Shows the union of all parameters that belong to user-defined or system objects.

6.
SELECT * FROM sys.all_sql_modules
Info : Returns the union of sys.sql_modules and sys.system_sql_modules.

7.
SELECT * FROM sys.all_views
Info : Shows the UNION of all user-defined and system views.

8.Select * from sys.triggers
Info : All triggers, both DML and DDL, are found in sys.triggers. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers. 

9.
Select * from sys.types
Info : Contains a row for each system and user-defined type.

10.select * from sys.xml_schema_collections
Info : Returns a row per XML schema collection. An XML schema collection is a named set of XSD definitions.
The XML schema collection itself is contained in a relational schema, and it is identified by a schema-scoped Transact-SQL name.
The following tuples are unique: xml_collection_id, and schema_id and name.

Please go through MSDN for detailed explainations.

Pawan Kumar
pawankkmr@hotmail.com

LIST OF THINGS IN SQL SERVER 2005 — Very interesting.

FIND PRIMARY KEYS OF ALL THE TABLES IN A DATABASE.
____________________________________________________

SELECT SCHEMA_NAME(schema_id) as ‘Schema Name’,OBJECT_NAME(parent_object_id) AS ‘Table Name’,OBJECT_NAME(object_id) AS ‘Primary Key Name’, create_date , modify_date
FROM   sys.objects
WHERE  type_desc IN (‘PRIMARY_KEY_CONSTRAINT’) and type = ‘PK’

FIND LIST OF FORIEGN KEYS IN A DATABASE
____________________________________________________

SELECT SCHEMA_NAME(schema_id) as ‘Schema Name’,OBJECT_NAME(object_id) AS ‘Table Name’, create_date , modify_date
FROM   sys.objects
WHERE  type_desc IN (‘FOREIGN_KEY_CONSTRAINT’) and type = ‘F’

FIND LIST OF STORED PROCEDURES IN A DATABASE
____________________________________________________

SELECT SCHEMA_NAME(schema_id) as ‘Schema Name’,OBJECT_NAME(object_id) AS ‘Stored Procedure Name’ , create_date , modify_date
FROM   sys.objects
WHERE  type_desc IN (‘SQL_STORED_PROCEDURE’) and type = ‘P’

FIND LIST OF USER TABLES IN A DATABASE
____________________________________________________

SELECT SCHEMA_NAME(schema_id) as ‘Schema Name’,OBJECT_NAME(object_id) AS ‘Table Name’, create_date , modify_date
FROM   sys.objects
WHERE  type_desc IN (‘USER_TABLE’) and type = ‘U’

CHECK out the below table for details.

SELECT * from sys.objects

Pawan Kumar
Pawankkmr@hotmail.com

OBJECT ID OF AN OBJECT IN SQL SERVER 2005

We can use a function called Object_Id to find out the object id of the object.

Syntax is OBJECT_ID (‘ObjectName’)

The returns the database object identification number of a schema-scoped object.

Examples are given below.

1.SELECT OBJECT_ID ( ‘NewEmployee’ ) as ‘Object Identification Number’

OUTPUT
_________

Object Identification Number

749961748

2.It will return if the object is not there in the database.

SELECT OBJECT_ID ( ‘Employee’ ) as ‘Object Identification Number’

OUTPUT
_________

Object Identification Number

NULL — Returned NULL because the object new employee doesnot exists in the database.

3.One more way is there which is given below.

Select id as ‘Object Identification Number’ , [name] as ‘Object Name’ from sysobjects where name = ‘NewEmployee’

OUTPUT
_________

Object Identification Number Object Name
—————————- ————-
749961748 NewEmployee

Please let me know if you have any other way.

Pawan Kumar
Pawankkmr@hotmail.com