SQL Tables,Temporary Tables,Table Variables

SQL-Tables
SQL-Tables

SQL Tables,Temporary Tables,Table Variables

Welcome to CodeSpread!

SQL provides a variety of Tables. Lets summarize each one of them.

Permanent Tables:

Why do we require it?

It is required for data persistence across the database. Once tables are created and filled with the data, this data is available for all the stored procedures and any DML statements can be run on them.

Example: Create a Student table having id,name as attributes.

Create table Student (Id int, Name varchar(50))

Here Table Student will have two columns, Id and Name. Id will accept only integers and Name will accept  maximum 50 characters.

Any Table created will have row and columns. Please refer the image below for visual representation.

Student

Id | Name

Temporary Tables:

This tables have a limited time duration and they are required to be dropped once they have served their purpose of existence. They are used to store data while defining a stored procedure and data is required to be persist beyond the procedure.

We have two type of temporary tables

  1. Local Temporary Tables. (#table_name)
  2. Global Temporary Tables.(##table_name)

Their name only describe their existence period and their usefulness. The symbols mentioned above is how they are described individually.

  • Local Temporary Tables: 

These tables are created within a procedure and stored in the temp_db provided by SQL server. They can be identified with a session specific identifier. It can be used when data is coming from another stored procedure. It also reduces the amount of locking required and also involves less logging. Still, there are few limitations such as character limit is 116 and transactions can create unnecessary locks in temp_db.

Syntax:

Create table #Student (Id int, Name varchar(50))

  • Global Temporary Tables:

These tables are same as local temporary table but the difference lies in the lifetime as it is available for all the sessions. This can be useful when the same set of data is required by one or more users. But the issue will come when the user, who should not be given access to this data, will have access to it as it is a global table and available for all users.

Syntax:

Create table ##Student (Id int, Name varchar(50))

See the difference of two #.

Table Variables:

Same structure as a normal table but only difference is the shortest life time among all the varieties. This table is created and stored in memory and its lifetime is decided by the stored procedure who have created it. Once stored procedure/DML statement exits, this table gets auto cleaned and memory gets free. Apart from that, log activity is truncated immediately. An important note, If we have a requirement to use a table structure in user defined function then we have only one option as Table variable and no other variety can be used.

Syntax:

Declare @Student Table (Id int, Name varchar(50))

I hope this article covered all the variation of a table. If you can contribute to improve this article, please write a comment below.Thanks.

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

2 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

More from CodeSpread:

  • How to create database with SQL express bundled with Visual Studio?How to create database with SQL express bundled with Visual Studio?Yes, Visual studio comes bundled with a copy of SQL express edition which gets installed during visual studio installation. These steps are required to create database with SQL express bundled w...
  • SQL : Clustered and NonClustered IndexSQL : Clustered and NonClustered IndexWe read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL. We have clustered and non-clustered index in SQL, let’s see...
  • Nullable .Net typesNullable .Net typesWe declare .net types in our project daily but still we do not recollect default values for each .net type so we end up doing null check for each data type. Its a basic mistake of a developer but ....
  • C#: Useful JSON in .NetC#: Useful JSON in .NetWhy JSON? All web applications revolve around large chunk of data and availability of that data to our application is of utmost importance. To do that, we have options like fetch data from databas...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • 19 Dec: Must Read Codes [1-5]19 Dec: Must Read Codes [1-5]We are sharing few must read codes/concepts which are required now and then. Each of these codereads doesn't require a full length descriptive article so combining them into groups of five. CodeRe...
  • Asp.Net: More about CookiesAsp.Net: More about CookiesWe have seen, how our cookies looks in our last article Cookies Part 1. In this article, we will talk about their properties,limitations and technical part. Properties and Limitations Cook...
  • Useful ‘ref’ and ‘out’ parametersUseful ‘ref’ and ‘out’ parameters It took me some time to realize the real potential of ref and out keywords, till the time I experience a condition which could only be simplified by implementing ref and out keyword. Lets st...