Temporary tables

Temporary tables, are very similar to the permanent tables. Permanent tables get created in the database you specify, and remain in the database permanently, until you delete (drop) them. On the other hand, temporary tables get created in the TempDB and are automatically deleted, when they are no longer used.

 Types of Temporary tables
 - Local Temporary tables 
  -Global Temporary tables. 



A local temporary table is automatically dropped, when the connection that has created the it, is closed. If the user wants to explicitly drop the temporary table, he can do so using
DROP TABLE #PersonDetails

 If the temporary table, is created inside the stored procedure, it get's dropped automatically upon the completion of stored procedure execution. The stored procedure below, creates #PersonDetails temporary table, populates it and then finally returns the data and destroys the temporary table immediately after the completion of the stored procedure execution.

 How to Create a Global Temporary Table:
To create a Global Temporary Table, prefix the name of the table with 2 pound (##) symbols. EmployeeDetails Table is the global temporary table, as we have prefixed it with 2 ## symbols.
Create Table ##EmployeeDetails(Id int, Name nvarchar(20))

Global temporary tables are visible to all the connections of the sql server, and are only destroyed when the last connection referencing the table is closed.

Comments

Post a Comment

Popular posts from this blog

Prime Number Program in C#

Prime number is a number that is greater than 1 and divided by 1 or itself. In other words, prime numbers can't be divided by other numbers than itself or 1. For example 2, 3, 5, 7, 11, 13, 17, 19, 23.... are the prime numbers. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 {     class Program     {         static void Main(string[] args)         {             int number, i, m = 0, flag = 0;             Console.WriteLine("PLEASE ENTER THE NUMBER TO CHECK PRIME NUMBER");             number = int.Parse(Console.ReadLine());             m = number / 2;             for(i=2;i<=m;i++)             {           ...
Read more

Fibonacci Series in C#

In the case of Fibonacci series, the next number is the sum of the previous two numbers for example 0, 1, 1, 2, 3, 5, 8, 13, 21 etc. The first two numbers of Fibonacci series are 0 and 1. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 {     class Program     {         static void Main(string[] args)         {             int val1 = 0, val2 = 1, val3, i, number;             Console.WriteLine("Please Enter any number");             number = int.Parse(Console.ReadLine());             Console.WriteLine(val1 + " " + val2);             for (i=2;i<=number;i++)             {                 val3 =...
Read more

view in sql server

Image
A view is nothing more than a  saved SQL query . A view can also be considered as a virtual table .   A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. SQL CREATE VIEW Syntax CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE coHEndition SQL Script to create tblEmployee table: CREATE TABLE  tblEmployee (  Id  int Primary Key ,  Name  nvarchar (30),  Salary  int ,  Gender  nvarchar (10),  DepartmentId  int ) When this query is executed , the database engine actually retrieves the data from the underlying base tables,  tblEmployees and tblDepartments . The View itself, doesnot store any data by default. When to Use a Vi...
Read more