SQL Server Temprary Table

Temporary table are tables created to hold data temporarily for a particular session. When a create table script is executed for a temporary table, physical table is created on tempdb database. You can see the tables as follows

“Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables”

temporary table can be used as a physical table, but it exist till the session is active who is created the table. Multiple user can create temporary table with same name and data. As each table is session based, table does not conflict  for different user.When session is expire or connection is broken, temporary table vanish automatically. Here is the syntax of creating temporary table.

Syntax

CREATE TABLE #table_name
(
  column1 datatype NULL/NOT NULL,
  column2 datatype NULL/NOT NULL,
  column3 datatype NULL/NOT NULL,
  ...
);


Data manipulation operation like insert ,update, delete can be done with temporary tables. Below is the example of creation and data manipulation using temporary table.

Create Table

create table #myTable

(

    ID int,

    Name Varchar(50),

    Roll Varchar(50)

)

Create Table and Insert data

create table #myTable

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

GO

INSERT INTO #myTable(ID,Name,Roll)

SELECT 1,'Student1',123

UNION

SELECT 2,'Student2',124

UNION

SELECT 3,'Student1',125

Output

(3 row(s) affected)

Create Table and Update data 

create table #myTable

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

GO

INSERT INTO #myTable(ID,Name,Roll)

SELECT 1,'Student1',123

UNION

SELECT 2,'Student2',124

UNION

SELECT 3,'Student1',125

GO

UPDATE #myTable

SET Name='New Srudent'

WHERE ID=1

SELECT * FROM #myTable

Output

ID    Name         Roll

1     New Srudent  123

2     Student2     124

3     Student1     125

Create Table and Delete data

create table #myTable

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

GO

INSERT INTO #myTable(ID,Name,Roll)

SELECT 1,'Student1',123

UNION

SELECT 2,'Student2',124

UNION

SELECT 3,'Student1',125

GO

DELETE FROM #myTable

WHERE ID=1

GO

SELECT * FROM #myTable

Output

ID    Name         Roll

2     Student2     124

3     Student1     125

A temporary table can we join with other physical or temporary table or table variables as regular tables. Below is the joining example of temporary table with other tables.

create table #myTable

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

create table student

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

SELECT a.Name,b.Name

FROM student a

INNER JOIN #myTable b

ON a.ID=b.ID

Constrain can be define in  Temporary table. Primary key, foreign key ,identity ,default value , checking can be defined. Below example of temporary people with identity and primary key and Default value.

create table #myTable

(

     ID int identity primary key ,

     Name Varchar(50),

     Roll Varchar(50),

     createtime datetime DEFAULT CURRENT_TIMESTAMP

)

Temporary table can be used in stored procedure. Here is a example of temporary table is used in stored procedure.

CREATE PROCEDURE sp_my_procedure

            @LastName nvarchar(50),

            @FirstName nvarchar(50)

AS

create table #myTable

(

         ID int identity primary key ,

         Name Varchar(50),

         Roll Varchar(50),

         createtime datetime DEFAULT CURRENT_TIMESTAMP

)

create table student

(

        ID int,

        Name Varchar(50),

        Roll Varchar(50)

)

SELECT a.Name,b.Name

FROM student a

INNER JOIN #myTable b

ON a.ID=b.ID

GO

Temporary table cannot be used in function. Temporary table is used to hold large data ,Temporary table can hold large data, with fast response.

CREATE FUNCTION dbo.fn_GetName()

RETURNS int

AS

BEGIN

 

DECLARE @ret int;

 

create table #myTable

(

      ID int identity primary key ,

      Name Varchar(50),

      Roll Varchar(50),

      createtime datetime DEFAULT CURRENT_TIMESTAMP

)

RETURN @ret;

END;

Msg 2772, Level 16, State 1, Procedure fn_GetName, Line 8

Cannot access temporary tables from within a function.

SQL Server Management Studio

SQL Server Management Studios integrated platform for SQL server development, management ,configuration and administration.  SQL Server Management Studio is visual tools  for SQL development.SQL Server  Complex infrastructure and architecture are simplified in a graphical  representation. But before using SQL Server Management Studio you have to do login. Login maybe Windows authentication or SQL server authentication.


After successful login ,the following can be done  to SQL Server Management Studio.

1)Configuration
2)Backup and Restore of databases
3)Create or Delete of data bases
4)Query execution
5)SQL server email configuration
6)Job Scheduling
7)Database synchronization
8)Create, delete, update of SQL server functions
9)Create,delete ,update stored procedure
10)Create,delete, update triggers
11)Create, delete, update tables
12)Create, delete, update views

SQL Server Management Studio is a integrated  several component is associated with the platforms

object Explorer : object Explorer is used to view manage configure administration of objects with the help of a graphical views, context menu etc. It is the most used component of a SQL Server Management Studio. You can connect to the server and choose corresponding databases all create database and expand  tree structure of the object Explorer. You can see all database ,table stored procedure, functions , Trigger, views, job and scheduling task  here.

Query and Text editor:  this editor is used for writing query script and execution  and debugging of the query. You can also see execution plan of a query or a stored procedure in this component. You can write query and press F5 to run query or click on Run button, will notice the result will be shown in the below, if any error occur the component will notify you by error message.

Solution Explorer : you can build project on manage project by writing script in the solution Explorer. Implement source control  and work with multiple files.

SQL server management studio has a lot of functionality, you can connect to disconnect particular server and corresponding database by login to that server.

You can get output of a query in text format or Grid format or you can export it to the file.