SET ANSI_NULLS ON/OFF & SET QUOTED_IDENTIFIER ON/OFF in SQL Server ?

Hi,

SET ANSI_NULLS ON / OFF
GO

SET QUOTED_IDENTIFIER ON / OFF
GO

These we can notify at creating new Stored Procedure in Sql Server.

ANSI Stands for American National Standards Institute .

When to use ON / OFF.

For Example i had a table named "Person.Address"

My Table data looks like when i used a simple select query.

Query 1: 

SELECT     AddressID, AddressLine1, AddressLine2, City
FROM   Person.Address

Result :

Now i am using SET ANSI_NULLS ON :

Query 2:

SET ANSI_NULLS ON

SELECT  AddressID, AddressLine1, AddressLine2, City

FROM   Person.Address

WHERE AddressLine2 < > NULL

AND AddressLine2 = NULL

Result :

Note :

When  SET ANSI_NULLS ON, Equals (=), not equals (< >) comparison against a null value in WHERE condition in SELECT Statement it returns zero rows even if the column contains NULL values or any data. 

Because it will not follow the SQL-92 standard.

Now i am using SET ANSI_NULLS OFF :

Query 3:

SET ANSI_NULLS OFF

SELECT AddressID, AddressLine1, AddressLine2, City

FROM  Person.Address

WHERE AddressLine2 < > NULL

Result :

Query 4:

SET ANSI_NULLS OFF

SELECT AddressID, AddressLine1, AddressLine2, City

FROM  Person.Address

WHERE AddressLine2 = NULL

Result :

Note :

When  SET ANSI_NULLS OFF, Equals (=), not equals (< >) comparison against a null value in WHERE condition in SELECT Statement it returns if the column contains any null values when (=), and it returns if the column contains any data when (< >).

Because it follows the SQL-92 standard.

Same way we can use these ANSI_NULLS {ON/OFF} in creating Stored Procedures.

===============================================================

SET QUOTED_IDENTIFIER ON / OFF

In SET QUOTED_IDENTIFIER ON / OFF

When it is ON, Any character set in double quotes (" ") treated as T-SQL Identifier like (SP Name, Table Name and Column Name etc .....). Here i am giving column name in double quotes.

For Example : Same Table

Query 5:

SET QUOTED_IDENTIFIER ON

SELECT AddressID, AddressLine1, "AddressLine2", City

FROM  Person.Address

WHERE AddressLine2 < > NULL

Result :

When it is OFF, it will be treated as string, and it gives that string value in quotes as a result with no column name, but in case if table it will throw the error like Incorrect syntax near Table Name.

Query 6: For Column Name in double quotes

SET QUOTED_IDENTIFIER OFF

SELECT AddressID, AddressLine1, "AddressLine2", City

FROM  Person.Address

WHERE AddressLine2 < > NULL

Result :

Query 7 : For Table Name in double quotes

SET QUOTED_IDENTIFIER OFF

SELECT AddressID, AddressLine1, AddressLine2, City

FROM  "Person.Address"

WHERE AddressLine2 < > NULL

Result :

I hope you will enjoy this information

Thanks & Regards 

RAJ

Special permissions on drive for copy and paste options

Hi,

Today i came across one query like this 

In my PC D-Drive, access permissions sharing like copy and paste 

Users in the same network, can copy files from my D-Drive, folders / subfolders,
but they can't paste in my D-Drive directly.

But they can paste files in selected folders / subfolders in my D-Drive.

Simply,

1. Copy : Full permissions from D-Drive, folders and subfolders too.
2. Paste : Limited permissions users can paste files in selected folders and subfolders only not in the D-Drive directly.

So please follow these steps :

1. Go to your D-Drive, right click ==> properties
2. Go to security tab, select Users, click on Edit, please check only these 
  a. Read & Execute
  b. List Folder Contents
  c. Read
3. Click on Apply and ok.

Images :

Fig 1

Fig 2

Fig 3

This will given you only copy / read permission to everyone in the network.

Now we have to give paste / write permission for all or selected folders in D-Drive.

4. Open your D-Drive
5. Select your Folder, right click on folder, go to properties
6. Go to Sharing tab, and click on share
7. One pop-up will come with the name File Sharing, Click on the dropdown select Everyone and add it
8. Change permission Level Read to Read/Write, Click on Share
9. This may take few minutes for processing
10. Now you can paste / write files in this selected folder in D-Drive.

Images :

Fig 4

Fig 5

Fig 6

Fig 7

Like this you can follow the same steps for giving the write / paste permissions to all folders.

Thanks & Regards
RAJKUMAR