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

Location:India Dwarka, New Delhi, Delhi, India

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

Location:India Dwarka, New Delhi, Delhi, India

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

Monday, January 2, 2012

How to rename a Table in Sql Server

To rename a column in a SQL Table following command can be reused:
SP_RENAME  'old table name', 'New table name'

For example, if we want to rename the table from employee to employeemaster, following command can be used.

SP_RENAME  'employee', 'employeemaster'

This command will rename the table from employee to employeemaster.

How to change the database collation

To change the collation of an database following commands can be used:




ALTER DATABASE [database name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE  [database name]  COLLATE Newcollation

ALTER DATABASE  [database name]  SET MULTI_USER


For example , suppose if the name of your database is learningdb and you want to change the collation to the collation SQL_Latin1_General_CP1257_CI_AS, then following commands can be used:-

ALTER DATABASE  learningdb  SET SINGLE_USER WITH ROLLBACKIMMEDIATE
ALTER DATABASE  learningdb  COLLATE SQL_Latin1_General_CP1257_CI_AS
ALTER DATABASE  learningdb  SET MULTI_USER