Monday, December 31, 2012

Welcome 2013


Happy New Year 2013




May this new year bring lots of Happiness in all of your life. Let  welcome 2013 together with lots of Hopes and happiness.



Tuesday, November 13, 2012

Happy Diwali



Wish you all a very happy and blessed festive season. May this festival of lights brings more brightness in your lives in every way. Happy Diwali :)
Play safe...!

Sunday, August 19, 2012

Boyce - Codd Normal Form (BCNF)

Boyce - Codd Normal Form (BCNF)  :- A normal form is said to be a Boyce - Codd Normal Form if it is in 3NF and there is not a possibility of a key attribute is determined by a Non Key attribute.

For example , suppose there are 5 columns says A,B,C,D,E and  combination of the columns  A and B  constitute the Primary key for this table.

Suppose, there is no transitive dependencies are existed in this table, but column C can determine the value of column B. In other words, the value of Column B is determined by the Column C then this table can not be in the Boyce - Codd Normal Form. To make this table in BCNF we needs to divide this table into two tables "table 1" and "table2" . The table1 will contains the following columns A, B, D,E where primary key is the combination of the column A and B and  table2 will contains the two columns B , C where C will be the primary key.

Table 1  (Primary key :-A,B)                     Table2 (Primary Key :- C)
A,B,D,E                                                             B,C

 Thus every table satisfying the 3NF condition cannot be said to be in BCNF normal form but a table in BCNF will always be in 3NF form.
DMCA.com

Isolation levels in SQL Server



Isolation levels :- Isolation level is required to isolate a resource and protect it from other transactions. This is achieved with the help of locks but what locks are needed and how they can be established is decided on the isolation level set on the database level. If low level of Isolation is set, it allows multiple users to access the resources concurrently but it may result in many  concurrency related problems like phantom reads, dirty reads etc. If higher levels of Isolation is set then it eliminate the concurrency related problem but it results in less number of concurrent access and it may result in data blocking

Dirty reads:- This situation happens when a transaction tries to read a data by some other concurrent transaction which is not committed yet. There is a  risk, that this other transaction may never be committed,  leaving the original transaction with wrong data.

Lost updates:- It happen when more than one transaction tries to update the same data. In Lost update, a successfully updated value of a data written in the database is overrides accidentally by the some another transaction updating the same data concurrently.

Repeatable reads:- Repeatable reads condition occur when a transaction tries to read a data multiple times and and between the two reads, another transaction modified that data. Therefore when the original transaction tries to read that data second time, it find different value for that data. In other words, the original transaction reads two different values for the same data. 

Phantom reads:-This condition happen when a transaction needs to execute a same query twice(or multiple times) and it gets different set of rows from what it get earlier due to the execution of the query first time. This happens if some another transaction add or delete rows between the two executions of the query and these added/deleted rows are the part of the record set returns by the execution of the query.

There are the  five Isolation levels (from lower level to higher level) defined in the SQL Server.

  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable
  • Snapshot


Read Uncommitted:- In this Isolation level, a transaction can read the data which is modified by some other transactions but still not committed. This Isolation level do not issue shared locked to prevent data modification which is read by some other transactions. Also it is not issue exclusive locks to prevents transactions from reading data which is modified from other transaction by not committed yet. It may results in problem like dirty read, lost updates, phantom reads etc. It is the least restrictive Isolation level.

Read Committed:- It is the default Isolation set by the SQL Server for any Database. This Isolation level prevents the transaction from reading data which are modified by some other transactions but still are not committed yet. Thus it eliminates the problem of Dirty read. But it do not eliminates the occurrence of Phantom reads and Repeatable reads.

Repeatable Read:- This isolation level is higher than the previous two mention isolation level and it does not allows any transaction to read a data that is being modified by some other transaction but not committed yet. Also it doesn't allow any transaction to modify a data if it is being read by some other transaction until the transaction reading that data complete its operation.  This way it eliminates the dirty read and Repeatable reads problem but it doesn't eliminates the Phantom reads.

Serializable:- This Isolation level do not allow any transaction to read the data unless the other transactions completed their data modification operation. Also it doesn't allow other transactions to modify the data until the current transaction completed its read operation. This isolation level allows a transaction to acquire a read lock (if only read operation) or write lock (for insert,delete,update) for the entire range of records that the transaction is going to affect. For example, if the query is "Select * from employees" then the transaction will acquire the read lock for whole table and no other transaction is allowed to add new rows or delete the rows until the current transaction releases its lock. Similarly, if the query is "Select * from Employee where country='India", then the current transaction will acquire the read lock for all the records of the table where country is India, and no other transaction is allowed to add or delete new rows until the current transaction releases its read lock. Thus, if we executes the same query twice , then each time it will get the same set of rows of a data  and therefore it eliminates the Phantom read problem.


Snapshot:-In this isolation level, a transaction recognise only data which is committed before the start of the transaction. Any modification of the data after the transaction is begin,  is not visible to any statements of the currently executing transaction. It is like a snapshot of data, given to each transaction. It is done with the help of row version where a separate version of each modified row is maintain in the temp db database dedicated to the transactions. This  isolation level eliminates dirty reads, lost updates, repeatable reads and Phantom reads problem.

Isolation level can be set by using the following command:-

SET TRANSACTION ISOLATION LEVEL


DMCA.com

Tuesday, April 17, 2012

Merge Command In Sql Server



Merge Command:- Merge command is a new feature introduced in the SQL Server 2008. It can perform Update, Insert and delete operations at a single statement which means all the data is processed and read only once instead of three times(In case of Insertion, deletion and update statements.) It has a target table and Source table. These two tables are join based on a conditions and depending upon whether the Condition is matched or not, Update, Insertion and Deletion Operations are performed.


The Syntax of the Merge command is given below:-


MERGE  [AS TARGET]
USING  [AS SOURCE]
ON 
[WHEN MATCHED 
THEN  ]
[WHEN NOT MATCHED [BY TARGET]
THEN  ]
[WHEN NOT MATCHED BY SOURCE
THEN  ];


For example, suppose we have two tables in which Import_Employee is a source table and Employee is a target table.


The query for creating the Source table Import_Employee and inserting its data is  given below:-


Create table Import_Employee (Empno int, firstname nvarchar(100), lastname nvarchar(100))

Insert into Import_Employee (Empno, firstname, lastname)

select 1,'Vivek', 'Johari'

Union All

select 2,'Ravi', 'Kumar'

Union All

select 3,'Uma', 'Sharma'

Union All

select 4,'Neha', 'Sharma'


Select * from  Import_Employee






The query for creating the Target table Employee and inserting its data is given below:-


create table Employee(Empno int, fname nvarchar(100), lname nvarchar(100),enddate datetime)


Insert into Employee (Empno, fname, lname)
select 3,'Jagdish', 'Johari'
Union All
select 4,'Neha', 'Sharma'
Union All
select 5,'Sunny', 'Rana'


Select  *  from Employee






Suppose we have the following requirements:-
1) If a empno is exists in both the table then the corresponding values of the column Fname and Lname for the empno in the table Employee is updated by the corresponding values of the column  firstname and  lastname of the Empno from the source table Import_Employee  .
2) If there is no corresponding empno in the Target table Employee for a empno of the table Import_Employee, then the data from the Source table is inserted into the target table for that empno.
3) If there exists a records for a empno in the Target table whose corresponding records are not in the Source table, these these record should be deleted from the target table.


Therefore, the condition on which the target table and Source table is joined will be the Empno.


The merged statement fulfilling all the above three requirements are given below:-


MERGE employee AS TARGET

USING Import_Employee AS SOURCE

ON TARGET.empno=SOURCE.empno

WHEN MATCHED

THEN update set TARGET.fname=SOURCE.firstname,

TARGET.lname = SOURCE.lastname

WHEN NOT MATCHED BY TARGET THEN

insert(empno,fname,lname)

values(SOURCE.empno,SOURCE.firstname,SOURCE.lastname)

WHEN NOT MATCHED BY SOURCE THEN

Delete;

Select * from 
Employee 















In the above example, the target table "Employee" and the source table "Import_Employee " has the same empno 3, so the fname and the lname of the table employee is updated with the corresponding  firstname and lastname column value from the table Import_Employee  for empno 3.


Also the table employee does not have the record for the empno 1 and 2. Therefore the corresponding record for the empno 1 and 2 will be inserted from Import_Employee (source) table to the Employee(Target) table.


Again, the target table employee contains the records for the empno 5 whereas the source table Import_Employee  do not contains any records for empno 5 , therefore the record for empno 5 is deleted from the target table by this Merge statement.


So we can see from the above example that with the help of Merge statement, we can perform the update, delete and insert commands within a single statement.

DMCA.com

Sunday, April 15, 2012

Adding , Deleting and Updating a Column in a table

Many times we need to alter the table definition by adding , deleting or updating a column in the table. In this article, I am trying to explain the following :-

1. How to add a column
2. How to update a column
3. How to drop a column

Suppose we have a table say Employee whose structure is given below:-

CREATE TABLE Employee (Emplyeeid int identity(1,1),
Empnumber nvarchar(10),
Firstname nvarchar(50),
Lastname nvarchar(150),
Age int,
Phoneno nvarchar(15),
Address nvarchar(200),
Empdate datetime)

Insert query for Inserting data in the table is given below:-


Insert Into employee Values
('mani123','Manish', 'Arora',27,'245121','Delhi',getdate())

We can see the data in table employee through the following SQL Statement:-


Select * from Employee

Now suppose we want to update the column Firstname of the table Employee by increasing its character limits from 50 to 100.  The SQL statement used for this column updation will be:-


Alter table Employee Alter column Firstname nvarchar(100)


Again suppose we want to add a column Regdate into the table Employee then the Sql statement for adding the column into the table Employee will be given below:-


Alter table Employee Add Regdate datetime null


The above SQL statement will add a nullable column Regdate to the table which have the datatype as datetime.


The insert command after the addition of the new column regdatum will be given below:-

Insert Into employee Values
('VJO','Vivek', 'Johari',28,'244555121','Delhi',getdate(),'01/01/2012')

Select * from Employee



Suppose if we want to delete a column from the table then we have to write the following SQL Statement


Alter Table Employee Drop Column Regdate


This command will drop the column Regdate from the table Employee which we can verify with the help of the select command.

Select * from Employee



from the above picture, it can be easily see that the column Regdate is not a column of the table Employee anymore.

Derived Table In Sql Server

Derived tables:-  Derived tables are the tables which are created on the fly with the help of the Select statement. It is different from the temporary table in the way that  in case of temporary table, first we have to create a temporary table,  insert the data into the table, select the data from the temporary table and then we have to drop the temporary table. But in case of derived table, SQL Server itself create and populate the table in the memory and we can directly use it. Also we don,t need to drop it. But it can only be referenced  by the outer Select query who created it. Also since it is reside in the memory itself, it is faster then Temporary tables which are created in the temp database.


Suppose, there is a table say tbl_studentcoursereg to store the id of the students registered for various courses, registration month and date. Table structure is given below:-



create table tbl_studentcoursereg (id int identity(1,1),  studentid int, coursename nvarchar(100), Regmonth nvarchar(50), regdate datetime)


The insert command to populate this table with the data is given below:-



insert into tbl_studentcoursereg
values (1, 'C#', 'JAN','01/01/2012')


insert into tbl_studentcoursereg
values (2, 'SQL', 'JAN','01/02/2012')


insert into tbl_studentcoursereg
values (3, 'C++', 'JAN','01/03/2012')


insert into tbl_studentcoursereg
values (4, 'C#', 'FEB','02/02/2012')


insert into tbl_studentcoursereg
values (5, 'C#', 'MAR','03/03/2012')


insert into tbl_studentcoursereg
values (6, 'JAVA', 'APR','04/04/2012')


insert into tbl_studentcoursereg
values (7, 'JAVA', 'APR','04/04/2012')


insert into tbl_studentcoursereg
values (8, 'ORACLE', 'APR','04/04/2012')


insert into tbl_studentcoursereg
values (9, 'C#', 'APR','04/23/2012')


insert into tbl_studentcoursereg
values (10, 'C#', 'MAY','05/05/2012')


Now if we want to know in which month number of students registered for a particular months is greater than 2, the we can use the following query which uses the derived table.



select regmonth, totalstud, totalcourse from 
(select  regmonth ,count(studentid) as totalstud ,
count(coursename)as totalcourse from tbl_studentcoursereg group by regmonth )as tbl1  where tbl1.totalstud>2



In the above query, the following is the derived table:-



(select  regmonth ,count(studentid) as totalstud ,
count(coursename)as totalcourse from tbl_studentcoursereg group by regmonth ) as tbl1

In case of derived table, we have to give the alias name to the derived table as tbl1 is the alias for the above mention derived table.







DMCA.com

Saturday, April 14, 2012

Pivot and Unpivot table in SQL SERVER


Pivot Table:- Pivot tables are used to summarize and display the data, specially in case of report data by means of aggregating the values. Pivot table can be used if we want to display the unique values of the column of a table as the columns of another table. It turns the unique values of a specified column into another table columns.


The syntax for the Pivot is given below:-



SELECT non-pivoted column,
    firstpivotedcolumn AS column name,
    secondpivotedcolumn AS column name,
    lastpivotedcolumn AS column name
FROM
    (SELECT query that produces the data>)
    AS aliasforsourcequery
PIVOT
(
    aggregation function(column being aggregated)
FOR
column that contains the values that will become column headers
    IN ( firstpivotedcolumn,secondpivotedcolumn,
    last pivoted column)
) AS aliasforthepivottable (optional ORDER BY clause) 



For example, suppose we have a table called tbl_student which contains the columns studentname, grade and marks. The query for creating this table and inserting data is given below:-


Syntax for creating the database:-


Create database DB_Pivot


Query for creating table:-

Create table tbl_student (studentname nvarchar(200), grade nvarchar(10), marks int)


Query for inserting the data into the table:-

Insert into tbl_student (studentname,grade,marks)
Select 'Vivek Johari','I',30
Union All
Select 'Vivek Johari','II',20
Union All
Select 'Vivek Johari','III',35
Union All
Select 'Vivek Johari','IV',40
Union All
Select 'Vivek Johari','V',45
Union All
Select 'Avinash Dubey','I',30
Union All
Select 'Avinash Dubey', 'II', 2
Union All
Select 'Avinash Dubey', 'III' ,35
Union All
Select 'Chandra Singh', 'I', 30
Union All
Select 'Chandra Singh', 'II', 20
Union All
Select 'Chandra Singh', 'III', 35
Union All
Select  ' Pankaj Kumar', 'I', 33
Union All
Select ' Pankaj Kumar', 'II', 29


Now if we want to see the data in the table tbl_student, it will looks like shown below:-


Select  *  from tbl_student




Suppose we want to display the data as shown below:- 

Studentname       I            II               III             IV                  V
Vivek Johari          30        20           35          40              45
Chandra Singh      30        20           35
Avinash Dubey      30        20           35                       
Pankaj Kumar       33         29 


Then we can either use the Select......... Case statement or the Pivot command.
In this article I am going to show the use of the Pivot operator to display data as shown above:-


Select studentname, [I], [II], [III], [IV] , [V]
 from
( Select grade, studentname, marks from tbl_student) as sourcetable
Pivot (  avg(marks) for grade in ([I],[II],[III],[IV],[V])) as pivotable order by V desc,IV desc,III desc,II desc,I desc




Or we can use the given below query also:-



Select studentname, [I], [II], [III], [IV] , [V] from tbl_student
Pivot  (  avg(marks) for grade in ([I],[II],[III],[IV],[V])) as pivotable order by V desc,IV desc,III desc,II desc,I desc








Both the query will gives the same result. In the first query we use the Derived table as the Source table and in the 2nd query we use the table tbl_student as the source table.


Unpivot table:- Unpivot table is reverse of Pivot table as it rotate the columns of a table into the value of a column. For example, suppose we have a table say tbl_stdmarksdata whose structure us given below:-



Create table tbl_stdmarksdata  (studentname nvarchar(100), I int, II int, III int, IV int, V int)

Query for inserting data in this table is given below:-

Insert into tbl_stdmarksdata (studentname,I,II,III,IV,V)
Select 'Vivek Johari',30,20,35, 40, 45
Union All
Select 'Chandra Singh',30,20,35,44, 80
Union All
Select 'Avinash Dubey',30,25,35,20, 39
Union All
Select 'Pankaj Kumar',33,29,30, 60, 50

After insert,  the data in the table :-

select * from tbl_stdmarksdata 



The Query for the Unpivot table will be as follow:-

select studentname,Marks,Grade

from tbl_stdmarksdata
unpivot
(Marks for Grade in (I,II,III,IV,V) ) as tblunpvt







Please  note:- Also as per the MSDN,
When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher.


Futher reading about the Pivot operator can be done at the following Pivot Operator

DMCA.com

Sunday, January 22, 2012

SQL Server- Output clause

Output Clause:- The SQL SERVER 2005 gives us an Output Clause which gives us the information about each row affected by the Insert, Update , Delete and Merge statement. It is more useful than @@scope_Identity and @@Identity column since these global variables gives us the information about the last inserted identity column value, but the Output clause gives us the last affected identity in case of Inserted, deleted, updated and merge statement. Output clause has the access to the Magic tables (Inserted and deleted) and we can capture the records of affected rows into the temporary tables too for latter use.

Create table Tbl_output (id int identity(1,1), fname nvarchar(100), lname nvarchar(100))

Example of Output clause in case of Insert command


Declare @outputtemp Table  (id int , fname nvarchar(100), lname nvarchar(100))

Insert into tbl_output(fname,lname)
Output inserted.id, inserted.fname, inserted.lname into @outputtemp
values('Vivek', 'Johari')

Select * from @outputtemp



Example of Output clause in case of Update command

Update tbl_output set fname='Vibodh' 
Output inserted. fname , deleted.fname
where id=1

Example of Output clause in case of Delete command

Delete from tbl_output 
Output deleted.* 
where id=1

Read more about Output Clause

Wednesday, January 18, 2012

CTE command In Sql Server


Common table expression (CTE):- Common table expression or CTE can be described as the temporary, named record set return by the execution of the query (insert, delete, update, select or update view statement). It is not stored as an object in the database and it last only till the execution of the SQL Query.
It also can reference itself. It can be referenced multiple times. The SQL Scripts of creation of all the tables and their insert data statement is given below:-


Query for creation of the database used in this article and then using it.


Create database CTE_Learning


Query to used the database created above


Use CTE_Learning


SQL Query for creation of the table Employee


Create table Employee (Employeeid int identity(1,1), EmpFname nvarchar(150), EmpLname nvarchar(150), Managerid int, regdate datetime)


SQL Query for creation of the table Project


Create table Project (Projectid int identity(1,1), Projectname nvarchar(150))


SQL Query for creation of the table Empproj


Create table Empproj (Empprojid int identity(1,1), Employeeid int, Projectid int)


Query for inserting the data in the table Employee


Insert into Employee(EmpFname, EmpLname, Managerid, regdate)
    Select 'Samir', 'Sethi', 0, getdate()
    union all
    Select 'Vivek', 'Johari', 1, getdate()
    union all
    Select 'Avinash', 'Dubey', 2, getdate()
    union all
    Select 'Sunny', 'Rana', 3, getdate()
    union all
    Select 'Himanshu','Aggarwal',3,getdate()


Query for inserting the data in the table Project 


Insert into Project (Projectname)
            Select 'Project1'
            Union all
            Select 'Project2'
            union all
            Select  'Project3'
Query for inserting the data in the table Empproj 


Insert into Empproj (Employeeid, Projectid)
               Select 1,1
              Union  all
               select 1, 2
              Union  all
               select 1,3
              Union  all
               select 2,1
              Union  all
               Select 2,2
              Union  all
                Select 3,1
              Union all
                Select 4,2
 The Common table expression has the following syntax:


With Expressionname (Column list)
AS
(
   CTE query definition
)


Here, Expressionname is the name of the CTE, Column list is the name of the unique column names which are return as the record set through the execution of the CTE query and CTE query definition is the SQL query which we are going to use in the CTE.


Suppose, we want to get the recordset which contains the details of the employees who are attacted to more than 1 projects then we can use the CTE as given below:-


With CTE_Projinfo (employeeid, total_projects) 
As
(
  select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1
)


select e.employeeid , e.empfname + ' ' + e.empLname, e.regdate from employee e inner join CTE_Projinfo
on e.employeeid = CTE_Projinfo.employeeid




Here CTE_Projinfo is the CTE expression name
        employeeid, total_projects are the names of the columns return by the execution of the CTE query


select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1


In the above example, we have used the CTE "CTE_Projinfo " to get the details of the employees by using the inner join with the table employee.


Also we can includes multiple CTEs within a WITH Clause
For example,



With
    CTE_Projinfo (employeeid, total_projects) 
As
(
  select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1
),


CTE_Empproject (employeeid, projectname)
AS
(
  Select CTE_Projinfo.employeeid,Projectname from CTE_Projinfo  inner join  Empproj
on CTE_Projinfo.Employeeid=Empproj.employeeid inner join Project 
on Empproj.projectid=Project.projectid
)


select e.employeeid , e.empfname + ' ' + e.empLname, e.regdate,CTE_Empproject.projectname from employee e inner join CTE_Empproject 
on e.employeeid =  CTE_Empproject.employeeid 

Here we have includes two CTEs expressions  CTE_Projinfo and CTE_Empproject within a same With caluse. In this example, we have seperated the two CTEs with the help of ",".
  
Since a CTE can reference itself also, we can use the CTE to get the hierarchy of an organization. 


For example, we can use the following CTE to get the  hierarchical data of the employees of an organisation.
      
With CTE_managerlevel (Employeeid,employeename,managerid,joblevel) as
(
  select e.employeeid , e.empfname + ' ' + e.empLname, e.Managerid,0 from employee e where e.employeeid=1
  UNION ALL
 select m.employeeid , m.empfname + ' ' + m.empLname, m.Managerid,CTE_managerlevel.joblevel+1 from employee m inner join CTE_managerlevel
 on m.Managerid=CTE_managerlevel.employeeid
)


select Employeeid,employeename,managerid,joblevel from CTE_managerlevel



DMCA.com

Tuesday, January 10, 2012

SQL Server-Table Variable

Table variable:- SQL Server provides an variable known as table variable which is used to store data in a similar way as we store data in physical tables but with some limitations. Like other SQL variable, it is also declare with the help of the Declare keyword with @ prefix. The Syntax of declaring a table variable is given below:-
Declare @tablename table(col1 datatype, col2 datatype, col3 datatype........coln datatype)


Suppose we want to declare a table variable named @tbl3, the we will use the following syntax:



Declare @employee table (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

Also to insert data into the table variable , we can use the insert command similar to physical, and temporary tables
Insert into  @employee  ( empFname   empEname  , empdate )
  Values ( 'Vivek', ' Johari', getdate())


Scope of the table variable is limited to the block of code in which it is defined or within the stored procedure in which it is defined. Unlike temporary tables (local or global) table variable it is not accessible in the procedures executed within the procedure in which it is defined. Like other variables, table variable is also created on the memory. Also since table variable is itself a variable, we doesn't need to force it deletion through the use of Drop statement.
Example of using a table variable inside a stored procedure.


Create  procedure test_tablvariable
as
begin
  Declare @employee table (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
  Insert into  @employee  ( empFname  ,  empEname  , empdate )
  Values ( 'Vivek', ' Johari', getdate())
  select * from @employee
end 


Unlike local and global temporary tables, we can't apply transaction on the table variable.


Alter procedure test_tablvariable
as
begin
  begin tran
  Declare @employee table (id int identity(1,1), empFname nvarchar(100),  empEname nvarchar(100), empdate datetime)
  Insert into  @employee  ( empFname  ,  empEname  , empdate )
  Values ( 'Vivek', ' Johari', getdate())
   Rollback
  select * from @employee
end


 The execution of the above store procedure will give the following result set in spite of the Rollback statement written above the select command.




Also unlike temporary tables and physical tables, we can't add constraints on it. For example, if we try to add primary key on the table @employee, it will throw an error.


Alter Procedure test_tablvariable
As
Begin
  Declare @employee table (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
  alter table @employee add constraint pk_temp primary key (id)
  Insert into  @employee  ( empFname  ,  empEname  , empdate )
  Values ( 'Vivek', ' Johari', getdate())
  select * from @employee
End


The execution of the above procedure will give the following error.








Similarly we also can't define indexes on the table variable.


Table variable is good when we need to store less number of  rows. But if the size of the data or number of rows keeps on  increasing  then storing the data into the table variable will not going to be a good idea. Since we can't use temporary tables inside the SQL Functions, table variable can be very useful to store temporary data and return the data in the table format. 
DMCA.com

Saturday, January 7, 2012

SQL Server - Local temporary table


Local temporary table:- Local temporary table is created in the tempdb and it is visible to the current user's session only. It remains exists till the current user session is connected. Once the user connection is disconnected it gets destroyed. Since the local temporary table is created in the tempdb, whenever we use temporary tables there is a interaction between the two database (tempdb and the database in which block of code is written) which may slow down the performance. We can use the temporary tables in the joins as well like physical tables. We can also use the temporary table with the While loop to replace the cursor. Temporary table is created with the prefix #. The syntax for the creation of local temporary table is given below:-


Create table #[table name] (col1 datatype, col2 datatype........coln datatype)


For example, below is the SQL command to create a temporary table #employee 


CREATE table #employee (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)


We can also defines constraints on the local temporary table. For example, the syntax to add primary key on the table #employee is given bellow:-


ALTER  Table #employee add constraint pk_LTT primary key (id)


We can also create indexes on the local temporary table also. For example


create index indx_LTT on #employee (empFname)


Insert command for temporary table is similar to insert command in the physical table. For example , if we want to insert data in the table # employee  we can use the following the SQL Statements:


Insert into  #employee ( empFname  ,  empEname  , empdate )
  Values ( 'Vivek',' Johari',getdate())


we can verify the creation of the local temporary table in the tempdb with the help of system view 'sys.objects'.


SELECT * FROM sys.objects where type='U'


Local temporary tables are itself dropped when the current user session is closed but it is better to drop it manually at the end of the block of the code in which it is defined or at the end of the stored procedure in which it is defined.
Create Procedure test_LTT
As
Begin
   CREATE table #employee (id int identity(1,1), empFname nvarchar(100),   empEname nvarchar(100), empdate datetime)
   Exec insert_LTT
   select * from #employee
   Drop table #employee
End


Create procedure insert_LTT
 as
begin
  Insert into  #employee ( empFname  ,  empEname  , empdate )
  Values ( 'Vibodh','Johari',getdate())
end


Transactions are also applicable in local temporary tables.


Alter Procedure test_LTT
As
Begin
Begin Transaction
   CREATE table #employee (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
  EXEC Insert_LTT
Rollback
Select * from #employee
Drop table #employee
End


Since transactions are applicable in the Local temporary tables, the rollback command in the above procedure will rollback the entire transactions including the creation of the table #employee, execution of the above procedure will return the error that table #employee does not exists.


Limitations
  1. We can't use local temporary table inside a SQL Function. 
  2. We can't create trigger on a temporary tables.
  3. We can't create View on the temporary tables.

DMCA.com

SQL Server - Global temporary tables


Global temporary table:- Global temporary table is created in the tempdb  and it is visible to all other sessions as well. Global temporary tables are only destroyed when the current user disconnected and all the sessions referring to it closed. It means no user sessions refers it. Since the global temporary table is created in the tempdb, whenever we use temporary tables there is a interaction between the two database (tempdb and the database in which block of code is written) which may slow down the performance. Global temporary table can be used in Joins. Global table is created with the help of the ##.

The syntax for creating the global temporary table is given below:

create table ##[table name] (col1 datatype, col2 datatype, ......coln datatype)

For example, the syntax to create the global temporary table  ##employeedetails is given below:


CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)


We can also create indexes on the Global temporary tables. For example,

Create index indx_GTT on ##employeedetails (empFname)

We can also define constraints on the Global temporary tables. For Example

Alter table ##employeedetails add constraint pk_GTT primary key(id)

Similar to physical table and local temporary table, the values in the global temporary table can also be inserted with the help of the Insert command. For example, to insert the value in the global temporary table ## employeedetails  , we can use the following SQL Statement:

Insert into ## employeedetails ( empFname   empEname  , empdate )
  Values ( 'Vivek', ' Johari', getdate())

We can access the global table within the stored procedure in which it is defined and also in the stored procedure which is called inside this stored procedure.

Create procedure test_GTT
as
begin
   CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
exec insert_GTT

select * from ##employeedetails
drop table ##employeedetails
end

Create procedure insert_GTT
 as
begin
Insert into ##employeedetails ( empFname  ,  empEname  , empdate )
Values ( 'Vivek', ' Johari', getdate())
end

In the above example, we create a global temporary table ##employeedetails
in the stored procedure test_GTT. This procedure call another procedure insert_GTT. The procedure contains the Insert command for the table ##employeedetails. When we execute the procedure test_GTT, it will give the following result.

Exec test_GTT


Global temporary tables are itself dropped when the current user session is closed and no other user is referring to it, but it is better to drop it manually at the end of the block of the code in which it is defined or at the end of the stored procedure in which it is defined.

The global temporary tables can viewed with the help of the system view 'sys.objects'.

SELECT * FROM sys.objects where type='U'

Transactions are also applicable on the Global temporary tables. 

Alter  procedure test_GTT
as
begin
begin transaction

   CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

exec insert_GTT

rollback

select * from ##employeedetails

drop table ##employeedetails

end
If we execute the above procedure the it will give the following output.


Limitations:-
  1. We can't use global temporary table inside a SQL Function.
  2. We can't create triggers on the temporary tables. 
  3. We can't create View on the temporary tables.

DMCA.com