Some days back i was asked about Temp Tables and Table Variables the basic difference why one is better than another one and all, and i am not able to provide a satisfactory reason\answer at that time so this is the time when i studied about a lots about it and i go through several topics, it takes one whole day and night to me to understand some aspects of this and here i am writing a detailed post about Temp Tables and Table Variables which may help some of peoples like me to understand in a better way.
So going through very basic details let talk about Temporary Tables first. So 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..
- Can be create by simple Create Table Syntax use # for Local Temporary Table and ## for Global Temporary table.
- 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.
- Its name is limited to 116 characters.
- 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
- They have well defined limited scope and are not part of persistent database.
- Transactional rollbacks doesn’t affect on them.
- They have very limited scope like current batch of statements so they can be deleted automatically after the completion of statements.
- You don’t use them in nested procedures.
- 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
And at last about the performance and IO usage for temporary table and table variables i use that example so the values vary for user to user so you can try the same on your systems and see what results you have with this…i am still working on this and as soon as possible i will be back with some results valid and good results to this till then you can try this at your end…
USE AdventureWorks
-----------------------------------------------------------------------
DECLARE @TEMP TABLE
(ADDRESSID INT ,ADDRESSLINE1 VARCHAR(100)
,ADDRESSLINE2 VARCHAR(100),CITY VARCHAR(30),StateProvinceID INT)
INSERT INTO @TEMP (ADDRESSID,ADDRESSLINE1,ADDRESSLINE2,CITY,StateProvinceID)
SELECT TOP 100 ADDRESSID,ADDRESSLINE1
,ADDRESSLINE2,CITY,StateProvinceID FROM PERSON.ADDRESS
--SELECT * FROM @TEMP
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT a.*
FROM @TEMP A INNER JOIN PERSON.STATEPROVINCE B
ON A.StateProvinceID=B.StateProvinceID
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
---------------------------------------------------------------------------------------------
CREATE TABLE #TEMP(ADDRESSID INT ,ADDRESSLINE1 VARCHAR(100)
,ADDRESSLINE2 VARCHAR(100),CITY VARCHAR(30),StateProvinceID INT)
INSERT INTO #TEMP (ADDRESSID,ADDRESSLINE1,ADDRESSLINE2,CITY,StateProvinceID)
SELECT TOP 100 ADDRESSID,ADDRESSLINE1
,ADDRESSLINE2,CITY,StateProvinceID FROM PERSON.ADDRESS
--SELECT * FROM @TEMP
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT a.*
FROM #TEMP A INNER JOIN PERSON.STATEPROVINCE B
ON A.StateProvinceID=B.StateProvinceID
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
DROP TABLE #TEMP
----------------------------------------------------------------
There are some more things like You can’t Alter Table Variable definition no DDL is applied on Table Variables and no statistics is maintained on table variable and statistics are used by query optimizer so there is a performance issue with this.
So after that experiments i think that this is totally depends on the developer that what he wants to use and what are the requirements. i will made a follow up post on this topic again as i may miss many of things on this vast topic so i really appreciate your suggestions and reviews on this if you found anything which i miss or wrong in this then drop a comment here we will try to make it clear as soon as possible and soon as i got more information about this i will write a follow up post soon.
Thanks
This time its all about a script a backup script.Today early morning i asked to get BackUp details for some of DB’s we have, usually they ask me about the last BackUp taken so its easy for me to tell him. But this time he needs all the history of BackUp’s for some reason.So i know that iformation is stored somewhere is System databases so started goggling for it and i found it on mssqltips.com and the script is written by Thomas LaRock(aka SQLRockStar) and i modified this script according to my use by taking his prior permission i modified his script and add the details about the restoration of BackUp’s and location of BackUp’s basically i added information about the Restorations of BackUp’s, so here is my modified script about BackUp status which returns some very useful information about BackUp’s and restore.
-- Script Returns BackUp Status and corresponding Restore Status for all Databases BackUps
DECLARE @DBNAME VARCHAR(100)
SET @DBNAME=NULL -- Default NULL(All Databses)
select 'BackUp Name'=BS.name,
'User Name'=BS.user_name,
'Start Date'=BS.backup_start_date,
'Finish Date'=BS.backup_finish_date,
'Backup Type'=Case when BS.type='D' then 'FULL Backup'
when BS.type='L' then 'Transaction Log Backup'
when BS.type='I' then 'Differential Backup' end
,'Backup Size MB'=floor(((BS.backup_size/1024)/1024))
,'DbName'=BS.database_name
,'Server Name'=BS.server_name
,MF.physical_device_name
,'IS Ever Restored'=case when BS.backup_set_id in
(select backup_set_id from msdb.dbo.restorehistory)
then 'Yes' else 'No' end
,'Destination Db'
=isnull(RH.destination_database_name,'Yet Not Restored From This BackUpSet')
,'Restore Path'
=isnull(min(RF.destination_phys_name),'Yet Not Restored From This BackUpSet')
,'restore Type'=isnull(CASE WHEN RH.restore_type = 'D' THEN 'Database'
WHEN RH.restore_type = 'F' THEN 'File'
WHEN RH.restore_type = 'G' THEN 'Filegroup'
WHEN RH.restore_type = 'I' THEN 'Differential'
WHEN RH.restore_type = 'L' THEN 'Log'
WHEN RH.restore_type = 'V' THEN 'Verifyonly'
WHEN RH.restore_type = 'R' THEN 'Revert'
ELSE RH.restore_type
END ,'Yet Not')
,Rh.restore_date,'Restore By'=isnull(RH.user_name,'No One')
,'Time Taken'
=cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/3600 as varchar(10))
+' Hours, ' +
cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/60 as varchar(10))
+ ' Minutes, ' +
cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)%60 as varchar(10))
+' Seconds'
from msdb..backupset BS
JOIN msdb..backupmediafamily MF
on BS.media_set_id=MF.media_set_id
left outer join msdb..restorehistory RH
on BS.backup_set_id =RH.backup_set_id
left outer join msdb..restorefile RF
on RF.restore_history_id=Rh.restore_history_id
where BS.database_name = isnull(@DBNAME,BS.database_name)
group by BS.name,BS.user_name,BS.backup_start_date,BS.backup_finish_date,
BS.TYPE,BS.backup_size,BS.database_name,BS.server_name
,MF.physical_device_name,BS.backup_set_id,RH.destination_database_name
,RH.restore_type,Rh.restore_date,RH.user_name
Here this scripts take a Database name as parameter and is you don’t supply that parameter that by default it shoes information about all databases
Here what this script returns :
- BS_Name = Name of BackUp
- BS_UserName = Name of User from which BackUp had taken.
- backup_start_date = Start Date Time of BackUp
- backup_end_date = End Date Time of BackUp
- BackUp Type = Type of BackUp
- BackUp Size = Size of BackUp File
- Dbname = Name of database
- ServerName = Name of Server
- Physical_Device_Name = name of Drive or Device where backup stored
- Is Ever Restored = is BackUp Ever Restore(Yes/No)
- Destination Db = Name of Database Restored By BackUp
- Restore Path = Path from database restored
- Restore type = Type of restoration
- restore_date = Date when Database Restored
- restore by = User by which database in restored
- Time Taken = Total time taken to take Database BackUp
So in this way this script return some useful information regarding BackUp’s and Restoration of backup’s its really make sense if you schedule this script to send through mail to get updates about your BackUp’s and Restore information so its really a good idea if we send this information in mail so in next blog post i will write how to send this information using
e-mail to direct in your inbox through Database nail in SQL Server.
SO if you have any suggestions regarding this script and do let me now or if you found any other method then do post here any kind of suggestions are welcomed
Thanks….
Hi All …
You all must here about T-SQL Challenges, these challenges are ideal to learn Set Based Queries using TSql. The most beneficial part of these challenges is that, through these challenges , you get a chance to see better solution to solve a given problem, T-SQL Challenges is mainly focused on writing Set Based Queries using T SQL. You can find previous challenges, winner of all challenges, and the best possible for all solutions by T SQL Challenges team as well as all the solutions provided by the winners with explanation of their solution’s here (ie TSQL Challenge #8)on TSQL Challenges Blog at beyondrelational.com ; and TSQL challenge team also provided a winning certificate for TSQL Challenges winners.Here is my certificate for my winning entry of TSQL Challenge #1.
The most wonderful effort put in to these challenges by TSQL Team is that they provide a free training webcast for those peoples who interesting in challenges , but not able to solve it.The training webcast helps us to write complex set based queries in simple manner and enhance our T SQL Set Based Quering Skills. So if you able to solve the challenge then send your entries else join the training webcast and learn how to write/solve challenges.
So hope to see you all having fun solving these challenges.
Thanks