Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Wednesday, 22 February 2017

Temp Table And Table Variable In SQL Server

 No comments   


Introduction

In this article i will explain about what is Temp table and Table variable ,why do we need to use these with our procedures and comparison between both temp table and table variable.


What TempTable Is

Temp or Temporary table is just like your normal Sql table but it has few restrictions that's why we did not use this as regular table. If we are working with large database and with many tables then we have relationship between tables to maintain the whole data. But by using temp table there is no concept of foreign key constraint means you can not use any foreign key constraint with it. 

As its name suggest it is temporary. Temporary tables automatically dropped when procedure finishes execution. Means doesn't matter how many tables created by you in a single procedure. They all are dropped after completion of execution.

Syntax
CREATE TABLE #YourTable
    Field1 DataType
    Field2 DataType
    ..................
GO


Sql server defines # ( Has or pond) Sign for Temp table declaration.

Example
CREATE TABLE #MyTempTb
    Id INT
    Name VARCHAR(50)
    Phone INT



Insert Values With in temp table

INSERT INTO #MyTempTb values ( 1, 'JP', 7838000001)
INSERT INTO #MyTempTb values ( 2, 'GN', 9456000001)


Select data from temp table
SELECT ID , Phone  FROM  #MyTempTb

All the operational syntax are same just like our main table but only difference is that we use # sign for temp table.


Table variable

Table variable also same as temp table. We need to define our table variable means a table type variable of variable instead of creating a table like temporary table.
It is good practice to use Table variable in our procedures . but if we have more than 150 n above rows than my opinion is use the temporary table , It is easy to handle data b indexing using temporary table. 

Defining a Table variable
DECLARE @tblVar table( ID int, Name VARCAR(50))


Insert and Selection is same as Temporary tables.
INSERT INTO @tblVar values ( 1, 'JP', 7838000001)
INSERT INTO @tblVar values ( 2, 'GN', 9456000001)

DELETE 
DELETE from @tbvar


Global Temporary Table

Global temporary table are visible to all SQL server connections. Means I you create any global temp table than it will be visible to all user's. It is rarly used in SQL server. It has 2 has '#' sign for declaration.

CREATE TABLE # #globalTb
    Field1 DataType
    Field2 DataType
    ..................
GO



Intresting Points
Both temp table and variable are stored in tempdb.
You can not truncate a table variable because it is not a physical table.
You can not perform any DDL operation with table variable like Alter table, Create Index and Truncate.
You can not use foreign key constraint with temp table


  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to TwitterShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

comma seperated string in sql Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS row_number() and dense_rank() salary table structure. Scope_Identity() Use of indexes while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write