Differences | SQL with Manoj

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING – SQL Server

December 10, 2010 4 comments

You have to create a Stored-Procedure and you need a template. You expand the Object Explorer go to Database -> Programmability -> “Stored Procedure” -> (right click) -> select “New Stored Procedure”.
A new editor opens up with lot of commented & flawless code. There you see some predefined statements.

Sometimes while working with Stored Procedures and other SQL Scripts a those few statements makes you confusing and clueless. Most of the time people remove them or just ignore them, but one should know why are they placed there. The 2 mains SET statements that are placed by default are described below with examples:

–// SET ANSI_NULLS (http://msdn.microsoft.com/en-us/library/ms188048.aspx)
Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

Syntax: SET ANSI_NULLS { ON | OFF }

create table #tempTable (sn int, ename varchar(50))

insert into #tempTable
select 1, 'Manoj'
UNION ALL
select 2, 'Pankaj'
UNION ALL
select 3, NULL
UNION ALL
select 4, 'Lokesh'
UNION ALL
select 5, 'Gopal'

SET ANSI_NULLS ON
select * from #tempTable where ename is NULL -- (1 row(s) affected)

sn	ename
3	NULL

select * from #tempTable where ename = NULL -- (0 row(s) affected)
select * from #tempTable where ename <> NULL -- (0 row(s) affected)

SET ANSI_NULLS OFF
select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (1 row(s) affected)

sn	ename
3	NULL

select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (4 row(s) affected)

sn	ename
1	Manoj
2	Pankaj
4	Lokesh
5	Gopal

drop table #tempTable

–// SET QUOTED_IDENTIFIER (http://msdn.microsoft.com/en-us/library/ms174393.aspx)
Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks.
When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

Syntax: SET QUOTED_IDENTIFIER { ON | OFF }

-- Example 1
SET QUOTED_IDENTIFIER ON
create table "#tempTable" (sn int, ename varchar(50)) -- Command(s) completed successfully.

SET QUOTED_IDENTIFIER OFF
create table "#tempTable" (sn int, ename varchar(50)) -- Incorrect syntax near '#tempTable'.

drop table #tempTable

-- Example 2
SET QUOTED_IDENTIFIER ON
select "'My Name is Manoj'" as Col1, "Let's play" as Col2 -- Invalid column name ''My Name is Manoj''. Invalid column name 'Let's play'.

SET QUOTED_IDENTIFIER OFF
select "'My Name is Manoj'" as Col1, "Let's play" as Col2 -- (1 row(s) affected)

Col1			Col2
'My Name is Manoj'	Let's play

-- Example 3
SET QUOTED_IDENTIFIER ON
select '''My Name is Manoj''' as Col1, 'Let''s play' as Col2 -- (1 row(s) affected)

Col1			Col2
'My Name is Manoj'	Let's play

SET QUOTED_IDENTIFIER OFF
select '''My Name is Manoj''' as Col1, 'Let''s play' as Col2 -- (1 row(s) affected)

Col1			Col2
'My Name is Manoj'	Let's play

–// SET ANSI_PADDING (http://msdn.microsoft.com/en-us/library/ms187403.aspx)
Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

Syntax: SET ANSI_PADDING { ON | OFF }

-- Example 1
SET ANSI_PADDING ON
create table #tempTable1 (charcol char(20), varcharcol varchar(20))

insert into #tempTable1
select 'Manoj', 'Manoj'
UNION ALL
select 'Pandey', 'Pandey'

select '['+charcol+']' as charcol, '['+varcharcol+']' as varcharcol from #tempTable1

charcol			varcharcol
[Manoj               ]	[Manoj]
[Pandey              ]	[Pandey]

-- Example 2
SET ANSI_PADDING OFF
create table #tempTable2 (charcol char(20), varcharcol varchar(20))

insert into #tempTable2
select 'Manoj', 'Manoj'
UNION ALL
select 'Pandey', 'Pandey'
select '['+charcol+']' as charcol, '['+varcharcol+']' as varcharcol from #tempTable2

charcol		varcharcol
[Manoj]		[Manoj]
[Pandey]	[Pandey]

drop table #tempTable1
drop table #tempTable2

Other SET statements MSDN BOL: http://msdn.microsoft.com/en-us/library/ms190356.aspx

Categories: Differences, SQL DB Engine Tags: ANSI_NULLS, ANSI_PADDING, QUOTED_IDENTIFIER

Physical Join vs Logical Join in SQL Server

October 6, 2010 5 comments

Most of us know about JOINS in SQL Server and their types. But do we really know how they are interpreted in SQL Server internally. Today I found lot of informative, interesting and important sources regarding Logical and Physical joins in SQL Server (links below).

–> Classifying JOINS mainly into 2 types:

1. Logical Joins: These joins are simple joins that we apply in our SQL queries, like INNER JOIN, RIGHT/LEFT OUTER JOIN, CROSS JOIN, OUTER APPLY, etc.

2. Physical Joins: These are the joins that users don’t use/write in their SQL queries. Instead these are implemented inside SQL Server engine as operators or algorithms to implement the Logical Joins. Their types are Nested Loop, Merge and Hash.

For a particular SQL query when you try to view an Estimated Execution Plan or execute a query by selecting Actual Execution Plan, you can clearly see these Physical Joins under the Execution Plan tab in SSMS.

Reference for Logical Joins from one of my old posts: https://sqlwithmanoj.wordpress.com/2009/03/12/sql-server-joins-and-types

–> Logical Joins:

– Inner/Outer/Cross: https://blogs.msdn.microsoft.com/craigfr/2006/07/19/introduction-to-joins/

–> Physical Joins:

– Nested Loop Joins: https://blogs.msdn.microsoft.com/craigfr/2006/07/26/nested-loops-join/

– Merge Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/03/merge-join/

– Hash Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/10/hash-join/

Categories: Differences, JOINS, SQL DB Engine Tags: Hash join, Logical Join, Merge join, Nested Loop, Physical Join

SQL basics – Temporary Tables vs Table Variables

May 15, 2010 9 comments

I see lot of people debating on usage of Temporary Tables & Table Variables. And everyone cites their own definition and examples, and most of them conflicts with each other. So I thought to put the differences & points that are valid and tested:

–> Temporary Tables:

1. Syntax: CREATE TABLE #T (..)

2. A Temporary Table or Temp-Table is created on disk in the tempDB system database. The name of this Temp-Table is suffixed with a session-specific ID so that it can be differentiated with other similar named tables created in other sessions. The name is limited to 116 chars.

3. The Scope of this Temp-Table is limited to its session, like a Stored Procedure, or a set of nested Stored Procedures.

4. The Temp-Table gets Dropped automatically when the session ends or the Stored Procedure execution ends or goes out of scope.

5. One of the main benefits of using a #temp table, as opposed to a permanent table, is the reduction in the amount of locking required (since the current user is the only user accessing the table), and also there is much less logging involved.

6. Global Temporary Tables (##) operate much like Local Temporary Tables; they are also created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all the sessions, until the creating session goes out of scope.

7. One can create desired Indexes on Temporary Tables (like permanent tables) and these make use of Statistics, thus resulting in better query plan compared to Table variables.

–> Table Variables:

1. Syntax: DECLARE @T TABLE (…)

2. A Table Variable is also created on disk in the tempDB system database. But the name of this Table Variable is generated completely by the SQL engine and it also differs with other similar named tables created in same or other sessions.

3. The Scope of Table Variables is limited to its BATCH only like other variables. Contrary to the temporary tables, they are not visible in nested stored procedures and in EXEC(@SQLstring) statements.

4. The Table Variable gets Dropped automatically when the BATCH ends (after the GO batch separator) or the Stored Procedure or Function execution ends or goes out of scope.

5. A Table Variable is created in memory, this is a myth. They are also treated as Temp-Tables and created in tempdb, but they performs slightly better than Temp-Tables because there is even less locking and logging in a Table Variable.

6. Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a UDF (User Defined Function). You can create a Table Variable within a UDF, and modify the data using one of the DML statements, this is not possible with Temp-Tables.

7. A Table Variable will always have a cardinality of 1, thus statistics are not tracked for them and may result in bad query plan.

–> Limitations with Table variables:

8. Table Variables do not participate in TRANSACTIONS and locking.

9. You cannot use a Table Variable in either of the following situations:
a. INSERT @table EXEC spSomeProcedure (Starting in SQL Server 2005, this limitation was removed and table variables can now be used as the destination for INSERT EXEC commands.)
b. SELECT * INTO @table FROM someTable

10. You cannot Truncate a Table Variable.

11. Table Variables cannot be Altered after they have been declared.

12. You cannot explicitly add an index to a Table Variable, however you can create an inline index through a PRIMARY KEY CONSTRAINT, and multiple indexes via UNIQUE CONSTRAINTs.

13. You cannot create a named Constraint on Table Variables. You cannot use a user-defined function (UDF) in a CHECK CONSTRAINT, computed column or DEFAULT CONSTRAINT.

14. You cannot use a user-defined type (UDT) in a column definition.

15. Unlike a #temp table, you cannot DROP a Table Variable when it is no longer necessary, you just need to let it go out of scope.

16. You can’t build the Table Variable inside Dynamic SQL. This is because the rest of the script knows nothing about the temporary objects created within the dynamic SQL. Like other local variables, table variables declared inside of a dynamic SQL block (EXEC or sp_executeSQL) cannot be referenced from outside, and vice-versa. So you would have to write the whole set of statements to create and operate on the table variable, and perform it with a single call to EXEC or sp_executeSQL.

17. Table variables are not visible to the calling procedure in the case of nested Stored Procs. It is possible with temp tables.

18. You cannot insert explicit values into an IDENTITY column of a Table variable (the Table Variables does not support the SET IDENTITY_INSERT ON).

–> Now the question is when to use either of them?

– Temporary Tables: When you are dealing with large volume of data sets use Temp-Tables, as you can create Indexes on them and they use Statistics for accurate cardinality estimations, thus providing a better query plan.

– Table Variables: When you are dealing with smaller data sets, use Table Variables, as they would not acquire locks and are Transaction free, and may not be affected by the absence of Indexes and Stats.

–> Check the full demo here:

Categories: Differences, tempdb Tags: Global Temp, Table Variables, Temporary Tables

DB Basics – Difference between Primary Key & Unique Key

November 3, 2009 2 comments

Primary Key & Unique Key are nothing but Constraints that can be set at column level of a table, to maintain Uniqueness in the table and thus not allowing duplicate entries.

–> Primary Key (PK) Constraint:

1. A PRIMARY KEY uniquely identifies every row in a database table.

2. The PK constraint on a Table’s COLUMN enforces:
– – UNIQUE values and
– – NOT NULL values

3. Every table can have only one PK defined on a particular column (or more than 1 columns).

4. You can create PK on a Table with:
– – CREATE TABLE statement (in two ways)
– – or later with ALTER TABLE … ADD CONSTRAINT statement.

5. You can create a PK with an IDENTITY column.

6. PK can be Composite Key, containing more than one column.

PRIMARY Key and INDEXes:

7. In SQL Server on creating a Primary Key on a table, a Clustered Index is created with PK column as the Clustering Key.

8. You can also create a Primary Key with a Non-Clustered Index, check this blog post.

Check the video on Primary Keys:

–> Unique Key (PK) Constraint:

1. A UNIQUE KEY just like PK uniquely identifies every row in a database table.

2. The UK constraint on a Table’s COLUMN enforces:
– – UNIQUE values (no duplicate values)
– – Allow single NULL value (But PK do not allow NULL values)

3. A table can have one or more than one UK defined on many columns (or more than 1 columns). But only 1 PK.

4. You can create UK on a Table with:
– – CREATE TABLE statement (in two ways)
– – or later with ALTER TABLE … ADD CONSTRAINT statement.

6. UK can be Composite Key, containing more than one column.

7. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

Unique Key and INDEXes:

8. In SQL Server on creating a Unique Key on a table, a Non-Clustered Index is created with the column(s) as the Index Key.

Check the video on Unique Keys:

To know about more on Constraints and their types check this blog post.

Categories: DB Concepts, Differences Tags: Constraints, PK Constraint, PRIMARY KEY, UK Constraint, Unique Key

SQL Basics – Difference between TRUNCATE, DELETE and DROP?

February 22, 2009 23 comments

DELETE and TRUNCATE are two SQL commands used to remove records from a particular table. But they differ in how they execute and operate.

–> DELETE: (MSDN)

1. Removes Some or All rows from a table.

2. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.

3. Causes all DELETE triggers on the table to fire.

4. It removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE.

5. Every deleted row in locked, thus it requires more number of locks and database resources.

6. According to MS BOL, if a table is a Heap or no Clustered index is defined than the row-pages emptied are not de-allocated instantly and remain allocated in the heap. Thus, no other object can reuse this associated space. Thus to de-allocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.

7. This is a DML command as it is just used to manipulate/modify the table data. It does not change any property of a table.

–> TRUNCATE: (MSDN)

1. Removes All rows from a table.

2. Does not require a WHERE clause, so you can not filter rows while Truncating.

3. With SQL Server 2016 you can Truncate a Table Partition, for more details check [here].

4. IDENTITY columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.

5. No Triggers are fired on this operation because it does not operate on individual rows.

6. It de-allocates Data Pages instead of Rows and records Data Pages instead of Rows in Transaction logs, thus is faster than DELETE.

7. While de-allocating Pages it locks Pages and not Rows, thus it requires less number of locks and few resources.

8. TRUNCATE is not possible when a table:
a. is reference by a Foreign Key or tables used in replication or with Indexed views.
b. participates in an Indexed/Materialized View.
c. published by using Transactional/Merge replication.

9. This is a DDL command as it resets IDENTITY columns, de-allocates Data Pages and empty them for use of other objects in the database.

Note: It is a misconception among some people that TRUNCATE cannot be roll-backed. But in reality both DELETE and TRUNCATE operations can be COMMITTED AND ROLL-BACKED if provided inside a Transaction. The only method to Rollback a committed transaction after DELETE/TRUNCATE is to restore the last backup and run transactions logs till the time when DELETE/TRUNCATE is about to happen.

–> DROP: (MSDN)

1. The DROP TABLE command removes one or more table(s) from the database.

2. All related Data, Indexes, Triggers, Constraints, and Permission specifications for the Table are dropped by this operation.

3. Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.

4. Cannot drop a table that is referenced by any Foreign Key constraint.

5. According to MS BOL, Large tables and indexes that use more than 128 extents are dropped in two separate phases: Logical and Physical. In the Logical phase, the existing allocation units used by the table are marked for de-allocation and locked until the transaction commits. In the physical phase, the IAM pages marked for de-allocation are physically dropped in batches.