Difference in Sql Server

Basic difference between Temp Tables and Table Variables

There are two types of temporary tables

A.) Local declared with one # Sign (#)

B.) Global declared with two # Sign (##)

Both of these tables created by simple CREATE TABLE (BOL) syntax except that # is used for Local Temporary Tables declaration and ## is used for Global Temporary Tables.

The Major difference between these two are Local Temporary tables (#) are visible in current connection and Global Temporary Table (##) are visible to all sessions or to the entire instance. You should always check for existence of global temporary table before creating it.

Let see with an example..

Open SSMS connect it and now create a Local Temporary table like this.

-- Creating Local Temporary Table

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))

This will create a temporary table named LOCALTEST now open other SSMS connect it and again retry to create another table with same name but using the new connection and check if it created or not so here it is ….

-- Create Temporary Table with same name

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))

-- Try to find out table with 'LOCALTEST' name in TempDb

USE TEMPDB

GO

SELECT Table_Catalog, Table_Name FROM information_schema.tables

WHERE table_name like '%LOCALTEST%'

GO

So here is the result set for the above query

So its very clear from the result set that there are two Local Temporary Tables with the same name.

In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number, you can see that in this image.

But when you try the same thing with Global Temporary Tables then it will result into an error.

So here is some points about Temporary tables

1. Can be create by simple Create Table Syntax use # for Local Temporary Table and ## for Global Temporary table.

2. Local temporary tables will be dropped at the end of current session but if it is created inside a store procedure, it will be dropped when store procedure is finished.

3. Its name is limited to 116 characters.

4. Foreign Key constraints can’t be applied to primary tables.

Now we know a little about Table Variables :

Table Variables (@) introduced first in SQL Server 2000 and onwards as name suggest its a variable of type table. Its definition’s include column definition, names, data type and constraint(Foreign Key Constraint are not allowed). They are more flexible and and always stay in memory well this is not true that table Variables stay always in memory this is a MYTH as table variables may be stored as in same way in TempDb as temporary tables we will look at some example for this. They are created with the Declare @variable syntax. There are some properties for Table Variables

1. They have well defined limited scope and are not part of persistent database.

2. Transactional rollbacks doesn’t affect on them.

3. They have very limited scope like current batch of statements so they can be deleted automatically after the completion of statements.

4. You don’t use them in nested procedures.

5. You not able to truncate a table variable.

So these are the some of basics for Table Variables and Temporary Tables but our Aim is to be find out which one is best in terms of performance, Advantages & Disadvantages for each and some considerations so we try to understand things why one is better then another and why not so

A.) Table Variables have a limited scope and are not part of persistent database, transaction rollback do not affect them.

Let’s understand what does it mean by transaction rollback do not affect them just take a simple example for this..

-- Create Tempoarary Table

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))

-- Declare Table Variable

DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(50))

-- Insert Values into both

Insert into #LOCALTEST SELECT 1 ,'Ashish'

Insert into @LOCALTEST SELECT 1 ,'Ashish'

-- Begin Transaction (Update both Temp table & Table Var)

BEGIN TRAN

UPDATE #LOCALTEST SET C2='ASHISH GILHOTRA'

UPDATE @LOCALTEST SET C2='ASHISH GILHOTRA'

-- Rollback Tran

ROLLBACK TRAN

-- Check data

SELECT * FROM #LOCALTEST

SELECT * FROM @LOCALTEST

and here is the result set

From this it’s very clear that Table Variables doesn’t take part in rollback transactions.So it may be or may not be useful for developers depends on the developers.

B.) There is less locking and logging on table variables then temporary tables so what does it means so we take an another example to understand this ..

USE tempdb

GO

-- Drop table #LOCALTEST

-- Drop table DUMMYDATA

-- Create a dummy table

CREATE TABLE DUMMYDATA (A int, B varchar(100))

-- Populate Data in it

Declare @i int

set @i=1

while @i<1001

BEGIN

INSERT into DUMMYDATA SELECT @i,REPLICATE('A',100)

SET @i=@i+1

END

--select * from DUMMYDATA

--Using #LOCALTEST

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))

GO

BEGIN TRAN

INSERT INTO #LOCALTEST(C1,C2)

SELECT A,B FROM DUMMYDATA

-- Using @LOCALTEST

DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))

BEGIN TRAN

INSERT INTO @LOCALTEST(C1,C2)

SELECT A,B FROM DUMMYDATA

-- Now check for locks

select request_session_id, resource_type, db_name(resource_database_id),(case resource_type

WHEN 'OBJECT' then object_name(resource_associated_entity_id)

WHEN 'DATABASE' then ' '

ELSE (select object_name(object_id)

from sys.partitions

where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description,

request_mode,

request_status

from sys.dm_tran_locks

--Rollback tran

So if you going to run this query batch you will see lock acquire by “#LOCALTEST” not by @LOCALTEST so yes that true that Table Variables takes less locking then temporary tables.

for logging purpose just populate temp Table and Table Variable with same data as shown above and now use fn_dblog(Un Documented) to see what happen like this..

So here is a way to find logging

First of all see logging for Table Variables

-- Table Variable

DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))

INSERT INTO @LOCALTEST(C1,C2)

SELECT A,B FROM DUMMYDATA

-- update all the rows

update @LOCALTEST set C2 = replicate ('AB', 50)

-- Look at the top 10 log records.

select top 10 operation, AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%LOCALTEST%'

order by [Log Record Length] Desc

For this i don’t get any records of update log in this and try same for Temporary Tables you will get update logs in this..

-- Temp Table

--drop table #localtest

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))

GO

INSERT INTO #LOCALTEST(C1,C2)

SELECT A,B FROM DUMMYDATA

-- update all the rows

update #LOCALTEST set C2 = replicate ('ab', 50)

-- Look at the log records. Here you get log records for update

select operation,AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%LOCALTEST%'

order by [Log Record Length] Desc

(1)Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.

(2)Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.

(3)You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.

(4)Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.

(5) In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.

What is difference between DELETE & TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE can not be Rolled back using logs.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table.

DELETE

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE Can be Rolled back using logs.

DELETE is DML Command.

DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.

What is the difference between clustered and a non-clustered index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

What is the difference between a local and a global variable?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

 What is the difference between a CHAR and a VARCHAR datatype?

CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).

You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.

What is difference between OSQL and Query Analyzer

Both are same for functioning but there is a little difference OSQL is command line tool which execute query and display the result same a Query Analyzer do but Query Analyzer is graphical.OSQL have not ability like Query Analyzer to analyze queries and show statistics on speed of execution .And other useful thing about OSQL is that its helps in scheduling which is done in Query Analyzer with the help of JOB.