Concepts and Solutions

 Execute Stored Procedure with Output Parameter?

select * from empdet

 

Creating Stored procedures with Output parameter:

create proc getempmgr

@empid int,

@mgrid int output

as

begin

select @mgrid=mgr

from empdet

where empno=@empid

end

Executing Stored Procedures with output parameter:

declare @result int

exec getempmgr 7566,@mgrid=@result OUTPUT

SELECT @RESULT

O/P:

 

Sql server Constraints - Primary Key, Foreign Key, Unique Key, Not Null, Check Constraints

A constraint is a property that is assigned to a column or a group of columns to prevent incorrect or corrupt data from entering into the tables. These constraints ensure the accuracy and reliability of the data into the tables.

Let’s suppose we have two tables Employee and Department whose description is given below:-

CREATE TABLE [dbo].[Employee](
[Empid] [int] IDENTITY(1,1) NOT NULL Primary key ,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLastName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] NULL,
[Departmentid] [INT]
)

CREATE TABLE [dbo].[Department](
[Departmenttid] [int] IDENTITY(1,1) NOT NULL primary key ,
[DepartmentName] [nvarchar](255) NOT NULL
)


There are following types of constraints in the SQL Server:-

Primary Key
Foreign Key
Unique Key
Not Null
Check Constraints

Primary Key: - Primary key is used to uniquely identify a row in a table. A table can have only one primary key. Primary keys don’t allow null values. The data help across the primary key column must be unique. It can be defined at the column level or at the table level.
Primary Key can be defined while creating a table with Create Table command or it can be added with the Alter table command.

Syntax for Primary Key

(ColumnName) (DataType) ((Size)) Primary Key

Primary key defined at the column level
Example
Suppose we want to create a table DepartmentManager which contains the information of the manager for the departments.

create table DepartmentManager(Departmentmanagerid int identity(1,1) primary key,empid int, Departmenttid int)

This table contains the Empid (id of the employee from the table Employee), Departmenttid (from the Department table)and Departmentmanagerid which is the identity column and primary column too.

Primary key defined at the table level

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int, primary key(Departmentmanagerid))

Primary key (known as composite primary key) can be a combination of two or more than two columns. If a primary key is a combination of two or more than two columns then it can only be defined at the table level only. For Example if we want that the primary key should be the combination of two columns empid and Departmenttid of the table DepartmentManager,then the required query will be

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int, primary key(empid, Departmenttid))

Adding Primary Key constraint using Alter table command

The query for Adding a primary key with the Alter Table command is as follows:-
Syntax

Alter Table tablename Add constraint constrainname Primary Key (Columnname)

Suppose there is no primary key defined for the table employeemaster and we want to add a primary key constraints on the column empid of the table employeemaster with alter table command then the required query should be:-

Drop table DepartmentManager

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int)


Alter Table employeemaster add constraint pk_EmpPrimaryKey primary key(empid)

Dropping a primary constraint from a table
The command for dropping a primary key is given below:-

Alter Table tablename Drop constraint name

For Example if we want to drop the constraint pk_EmpPrimaryKey from the table employeemaster,the required query will be

alter table DepartmentManager drop constraint pk_EmpPrimaryKey


Foreign Key: - Foreign key is used to prevent destroying the link between two tables. In foreign key, the table (Child table) in which the foreign key is defined points to the primary column of another table (Master table). A foreign key can points to the primary column of the same table. In this Foreign key relationship is said to be defined within the same table. Due to foreign key relationship, a value from the primary column of the master table can not be deleted until its all references from the child tables are deleted. Also a new value in the column in which primary key is defined can not be inserted until the value is already existed in the primary column of the master table.

Syntax for defining the Foreign key is:-

(Column Name)(data type) ((Size)) References (Table Name) [((Column Name))]

For example:-Suppose we want to create a table DepartmentManager whose column empid references to the empid column of the Employee table.Then the query will be

create table DepartmentManager(Departmentmanagerid int identity(1,1) primary key, empid int references Employee(empid), Departmenttid int )

Foreign Key Constraints can also be added with the alter table command. For example, if we want that the departmentid column of the table DepartmentManager references to the Departmentid column of the table Department,then the query will be:-

Alter Table DepartmentManager Add Constraint Fk_Departmenttid Foreign Key(Departmenttid)references Department (Departmenttid)

The above defined foreign key constraint can also be dropped from the table by using the following query:-

Alter Table DepartmentManager Drop Constraint Fk_Departmenttid

The foreign key columns of a table can also references to columns of the same table.
For Example, if Managerid is the foreign key column of the table Employee which references to the empid column of the same table then the required query will be:-


CREATE TABLE [dbo].[Employee](
[Empid] [int] IDENTITY(1,1) NOT NULL Primary key ,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLastName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] references employee (empid) NULL,
[Departmentid] [INT]
)



Unique Key: - Unique key constraint enforces that the column can contains only unique values on which unique constraints is defined. This column can contain multiple null values but all not null values must be unique. Unique Constraint can be defined either at the column level or at the table level.

Syntax for defining the unique constraint at the column level is:

(ColumnName) (Datatype) ((Size)) UNIQUE

Syntax for defining the unique constraint at the column level is:

Create table tablename ((col1)(datatype) ((size)), (Col2)(Datatype) ((Size)), Unique ((col1), (Col2)))

For example, if want to make the empid column of the table DepartmentManager as unique,then the required query is:

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int Unique, Departmenttid int)

Also if we want to make both the columns empid and Departmenttid Unique then the required query is

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int, Unique(empid, Departmenttid))

Like primary key and the foreign key, Unique Key can be added through Alter Table Command. The syntax is:-

Alter table tablename add constraint constraintname Unique (Columnname)

Suppose we have created a table DepartmentManager without any unique Key and
We want to add a unique key constraint to the table with alter table command then the required query is:-

Drop table DepartmentManager

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int)

alter table employeesalarymaster add constraint Uni_empid unique (empid)


Dropping a Unique constraint from a table
The command for dropping a Unique key is given below:-

Alter Table tablename Drop constraint name

For Example if we want to drop the constraint Uni_empid from the table employeemaster,the required query will be

alter table DepartmentManager drop constraint Uni_empid


Not Null: - Not Null Constraint enforces that the column in which not null constraint is defined can not contains null values.

Not Null constraints can only be defined at the column level only. It ensures that the column of the table on which it is defined can not be left blank.

The syntax for defining the Not Null Constraint is:-

(Column Name)(Data Type ((size)))NOT Null

For example, if want to make the empid column of the table DepartmentManager is Not Null,then the required query is:

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int Not Null, Departmenttid int)
Check Constraints: - Check constraints are the user defined business rules which can be applied to the database table column. For example a check constraint on the column “Salary” of the table Employee salary can be defined which state that no employee can have salary less than 5000.

Check Constraint can be defined as the column level or the table level.

The Syntax for defining the check constraints at the column level is

(Column Name)(Data type) ((Size)) Check ((Logical Expression))

For Example: - Suppose we want to create a table Employeesalarymaster which have a column name Empsalary which contains the salary of an employee.Now If we want that only those rows inserted into the table whose Empsalary Column value is greater than 5000 then we can use the check constraint in the following way.

CREATE TABLE Employeesalarymaster(empsalaryid int identity(1,1),Empid int, Empsalary numeric(10,2) check (empsalary)5000) not null, SalaryDt datetime)

The Syntax for defining the check constraints at the column level is

Check ((Logical Expression))

For Example: - The same table Employeesalarymaster can be created by defining the check constraint at the table level whose syntax is given below:-

CREATE TABLE employeesalarymaster(empsalaryid int identity(1,1),Empid int, Empsalary numeric(10,2) not null,SalaryDt datetime,check (empsalary)5000))

Note: - The Check constraints must be a Boolean expression that can be evaluated using the values in the rows being inserted or updated.

How to find duplicate values in a table of no of time it duplicate or repeated:

Sometimes duplicate values in tables can create a major problem when we do not make a primary key or a unique key on a table. In these cases we need to find out the duplicate records and need to delete them. We can use the Having Clause to find out that duplicate records.we show this with the help of an example.

Suppose we have a table named EmployeeDeptInfo which have the column Employeid and Departmentid. The query for creating this table is given below:-

Create table EmployeeDeptInfo ( Employeeid int, Departmentid int)

Employeeid contains the Id of the employee and Departmentid contains the Id of the department to which he/she is belonged. This table do not have any primary key defined on it and also it doesnt have any unique key constraint defined on any column.

Suppose the table contains the following data

Employeeid              Departmentid

1                                  1

2                                  2

3                                  2

4                                  3

3                                  2

2                                  2

5                                  4

2                                  2
In this table, entries for the employee having employeeid 2 & 3 are get repeated.In this example the data in the table is not much for the example point of view but in real time scenario it can be billions of rows and duplication of rows can be a very big concern. Therefore it is necessary to find out the duplicate rows in the table. We can use the Having Clause to find out the duplicate rows:-

Query for finding out the duplicate rows in the table:-

Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1 and count (Departmentid) >1)

This query will give us the following results:-

Employeeid         Departmentid

2                              2

3                              2

We can also find out that how many times a record is repeated in the table. For example, The following query gives us the result of how many times a given record is repeated

Select Employeeid, Departmentid, count (Employeeid) as NoOfRepeat from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1 and count (Departmentid) >1)

This query will give us the following results

Employeeid           Departmentid        NoOfRepeat
    2                                 2                           3

    3                                 2                           2

Here NoOfRepeat shows the number of times the records are duplicated in the table

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.

How to make database offline or online

Database is made offline to move its physical files. There can be many ways to make a database offline. But there are three main methods which are used frequently to make the database offline. These methods are given below:-

1)With the help of Alter database Command:-

We can make the database offline or online with the help of the Alter database command. The Alter Database command to make the database offline is :-

    ALTER DATABASE database name SET Offline

If we want to make the database online we can use the following Alter Database command:-

  ALTER DATABASE database name SET Online

2)With the help of the Db_options:-

 We can also use the db_options command to make a database offline or online.To make a database offline we can use the following command:-

sp_dboption databasename ,'offline',true

To make the database online we can use the following command:-

sp_dboption databasename ,'offline',false

3)With the help of Sql server management studio:-

We can also use the Sql server management studio to make a database offline as shown in the given figure.
To make database offline we have to follow steps show in fig 1 and to bring back the database online we needs to follows the step shown in fig 2:-

Renaming a column in Sql Server

 We often need to change the name of a column of a table to a new name. We  can do this with the help of the Exec Sp_rename command.


 The Syntax of the Sp_rename is given below:-

 Exec sp_rename 'TableName.[OldColumnName]', '[NewColumnName]', 'Column'


 For example, suppose we have a table called Employee who has the following structure:-



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


Now suppose we insert the following data into the table Employee



Insert into Employee values('VIV123', 'Vivek', 'Johari', 27, '9211134', 'Delhi', getdate())


If we execute the Select command into the table Employee, we get the following result:-

Suppose now  we want to change the column name Firstname to Fname the we use the given query:-


Exec Sp_rename 'Employee.Firstname', 'Fname', 'column'



If we again execute the Select command into the table Employee, we get the following result:-

Here the column name "Firstname" changed to the column "Fnamn".

Fig1

Fig2

Hi I have a scenario like source table have below
Source table
empid salary
100 5000
101 4000
102 2000
and
Destionation table
empid basic hra da
100 2000 2000 1000
101 1000 500 500
102 1000 500 500
So source table empid and destination empid is have realation.
I want to check whether Source table salary column is equal to destionation table of basic+hra+da or not?
My output look like below:
Empid Result
100 Equal
101 Not Equal
102 Equal
Solution:

DECLARE @Source TABLE ( empid INT, salary INT )

INSERT  @Source

    ( [empid], [salary] )

select 100, 5000

union

select 101, 4000

union

select 102, 2000

DECLARE @Destionation TABLE

(

  empid INT ,

  BASIC INT ,

  hra INT ,

  da INT

)

INSERT into  @Destionation

    ( [empid], [BASIC], [hra], [da] )

select   100, 2000, 2000, 1000

union

select   101, 1000, 500, 500

union

select   102, 1000, 500, 500

SELECT  [d].[empid] ,

    [d].[BASIC] ,

    [d].[hra] ,

    [d].[da] ,

    CASE WHEN salary = [d].[BASIC] + hra + [d].[da] THEN 'Equal'

         ELSE 'Not Equal'

    END AS Comparison,s.salary

FROM    @source AS s

    INNER JOIN @Destionation AS d

    ON s.empid = d.empid

IDENTITY Property in SQL Server

IDENTITY Property

Sometimes we need a column whose values can uniquely identifying the rows in the table. To achieve this purpose, this column should contains the unique values and it can’t contains the NULL or empty values. We can use this column in the “Where” clause of the Select statement to retrieve the data from the table quickly.

But as a human being we sometime forget to insert the data into this column or sometimes we insert the duplicate data into this column. So it will be very helpful if the SQL Server itself insert the value in this column whenever we try to insert any row into the table. SQL Server does this with the help of the identity column. We can set the initial value for this column and the value which the previous value is incremented to get the new value. We sometimes use this like the primary key of the table.


For example, suppose we want to create a table named student whose structure is given below




           CREATE TABLE Student (Studentid int IDENTITY (1, 1) NOT NULL,Firstname nvarchar (200) NULL,Lastname nvarchar (200),Email nvarchar (100) NULL )


Here column Studentid is defined as the identity column. In the Identity column, the value of the first argument defined the initial value for this column and the value of the second argument defined the value used to add in the last value of this column for getting the next value.


Now if we want to insert the row in the table Student, we do not need to specify the value for the column Studentid. For Example,



              insert into Student (Firstname,Lastname,Email)
              Values('Vivek', 'Johari', ‘vivekjohari@abc.com')



Here we do not specify the value for the studentid column in the Insert Statement.


Although the Identity column is very useful in maintain the data in the table, sometimes we need to set this constraint off like when we importing the data into the database.


To set the Identity column property off, the following command is used.



          Set Identity_insert Tablename Off



For Example if we want to keep the identity column (studentid) property off for the Student table, we need to use the following Sql Query:-



         set identity_insert Student off



To set the Identity column property On, the following command is used.



         Set Identity_insert Tablename On


For Example,


         set identity_insert Student on


We can also reset the values of the identity column to some different value with the help of the following command:-


          dbcc checkident (Tablename, reseed, 10)



For example, to reset the value of the studentid column of the table Student we need to use the following Sql Command

         dbcc checkident (Student, reseed, 10)




Summary:-
Identity column in the Sql server is a very useful property and it can be used to retrieve the data very quickly specially in the table where no primary key is defined.


Note:-
1) Only one Identity column is possible for a table.
2) In case of truncate command on a table the identity column value is reset to 0.

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.

Cursor in sql server

Sometimes our application required a database object which allows manipulation of data from a set of rows on row by row basic which means single row at a time. Cursor is the database object which solves this problem. With the use of cursor, we can fetch a sets of rows based on some conditions and manipulate the data of a single row at a time.


Section of the cursor:-


   The Cursor is consists of the following sections:-

1. Declaration of Cursor:- This section is used to declare the cursor object
2. Sql Statement for fetching the records:- This section is used to define the SQL query used for fetching the record set
3. Open Cursor:- This section is used to Open the Cursor. Also this statement allocates the memory to the cursor to hold the record set return by the execution of the Sql Statement.
4. Fetch statement:- This section is used to fetch a single row from the record set get by the execution of the Sql Query and assigning the values to the respective variable.
5. @@Fetch_status:- This is the System variable and it is used to know that whether the Fetch statement is returning rows or not. This system variable returns the value of 0 if the Fetch statement is successful, -1 if the Fetch statement is failed and -2 if the row fetched is missing. While loop is used compare the value of the @@Fetch_status with the 0.
6. Begin......End:- This section is used to write the Sql code used inside the Cursor to manipulates the data of the rows fetch by the Fetch Statement
7. Close Cursor:- This statement is used to close the cursor.
8. Deallocate Cursor:- This section is used to deallocated the memory used by the cursor.

Syntax for defining the Cursor:-


  Declare @Cursor_name cursor For
    Sql query                                                        // Select statement to Fetch rows
 Open @Cursor_name
  Fetch next from @Cursor_name  into {@Variable1,@variable2 etc)
  While (@@fetch_status=0)
   Begin


      // Sql Block  sql statements to process the data of the row return by the cursor.


 Fetch next from @Cursor_name  into {@Variable1,@variable2 etc)
 End
 Close @Cursor_name
 Deallocate @Cursor_name


Example:-
Suppose we have two tables named customer and customerTransaction whose structure is given below:-

Create Table customer(customerid int identity(1,1) primary key,Custnumber nvarchar(100),custFname nvarchar(100), CustEnamn nvarchar(100),email nvarchar(100),Amount int, regdate datetime)

Create Table customerTransaction(Transactionid int identity(1,1) primary key,custid int, Transactionamt int,mode nvarchar, trandate datetime)

Customer table contains the customer information and the table customerTransaction contains the information about the transactions (credit or debit) done by the customer. Whenever any transaction occur, it entry should also be inserted into the table customerTransaction.

Suppose we have an requirement that all the customer amount should be increased by 20% and this transaction entry should be made into the table customerTransaction, then we can use the cursor for this purpose. Cursor is used mostly within the stored procedures or the SQL function. In our example we used the cursor inside the stored procedure named Updatecustamount whose Sql Script is given below:-

Create Procedure Updatecustamount
  As
Begin
 Declare @custid as Int
 Declare @amount as Int
 Declare @addamt as Int
 Declare cur_cust Cursor For
   Select customerid,amount
   From customer

 OPEN Cur_cust

  FETCH Next From cur_cust Into @custid,@amount
 While(@@fetch_status=0)
  Begin
    Set @addamt=(20*@amount)/100
    Update customer set amount=amount+@addamt where customerid=@custid
 
    Insert into  customerTransaction(custid,Transactionamt,mode,trandate )
                   Values(@custid,@addamt,'c',Getdate())
   Fetch Next From cur_cust Into @custid,@amount
  End
CLOSE cur_cust
DEALLOCATE cur_cust
End


Summary:-
We can say that cursor is a very useful database object if we needs to manipulates the rows on a row by row basic. But there are two camps, one which oppose the use of cursor since it sometimes slow down the performance by occupying a lot of memory to hold the rows return by the query and the other camp which advocates the use of cursor since it gives the readability and simplicity to our code. We can replace the cursor with the help of the While loop or sometimes with the help of the Select case statements.One can also used the cursor if the need is to execute the Sql statements in a serialized manner.Also we can use the cursor for doing many Administrative tasks.

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.

SQL Server Functions- An Introduction

SQL FUNCTION: -
   Function in a database can be defined as the code segment consisting of a logical group of SQL statements which takes some input arguments (if required), run in a sequential order and returns the output either in the form of a single value or in the form of a table.Through this article, I am trying to give the overview of the System defined functions,User defined functions, their advantages, their disadvantages and their differences with the Stored procedures. 


Types of Functions: -


In SQL, Functions can be categorized into two categories:-

1. System Defined Function
2. User Defined Function (UDF)

In this article, I am going to use the following tables for the examples:-

CREATE TABLE [dbo]. [Employee](

[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,

[EmpNumber] [nvarchar](50) NOT NULL,

[EmpFirstName] [nvarchar](150) NOT NULL,

[EmpLAStName] [nvarchar](150) NULL,

[EmpEmail] [nvarchar](150) NULL,

[Managerid] [int] NULL

)

CREATE TABLE [dbo].[Department](

[Departmentid] [int] IDENTITY (1, 1) NOT NULL primary key,

[DepartmentName] [nvarchar](255) NOT NULL

)

CREATE TABLE [dbo].[EmpdepartmentInfo]

(

 Empdepartmentid int identity(1,1) primary key,

 Empid int not null,

 departmentid int not null

)

SQL scripts for entering the data into the table Employee:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)

Values('A001','Samir','Singh','samir@abc.com',2)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)

Values('A002','Amit','Kumar','amit@abc.com',1)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)

Values('A003','Neha','Sharma','neha@abc.com',1)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)

Values('A004','Vivek','Kumar','vivek@abc.com',1)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)

Values('A005',' AvinASh', 'Dubey','avinASh@abc.com',2)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)

Values('A006',' Sanjay','Kumar',' sanjay@abc.com',5)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)

Values('A007','Rajiv','Kumar','rajiv@abc.com',5)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)

Values('A008','Manish','Kumar','manish@abc.com',6)

SQL scripts for entering the data into the table Department:-

Insert Into Department(DepartmentName)

Values('Testing')

Insert Into Department(DepartmentName)

Values('Admin')

Insert Into Department(DepartmentName)

Values('HR')

Insert Into Department(DepartmentName)

Values('Technology')

SQL scripts for entering the data into the table EmpdepartmentInfo:-

Insert Into [EmpdepartmentInfo](empid, departmentid)

Values(1,1)

Insert Into [EmpdepartmentInfo](empid, departmentid)

Values(2,2)

Insert Into [EmpdepartmentInfo](empid, departmentid)

Values(3,3)

Insert Into [EmpdepartmentInfo](empid, departmentid)

Values(4,4)

Insert Into [EmpdepartmentInfo](empid, departmentid)

 Values(4,5)

Insert Into [EmpdepartmentInfo](empid, departmentid)

Values(5,1)

Insert Into [EmpdepartmentInfo](empid, departmentid)

Values(6,2)

Insert Into [EmpdepartmentInfo](empid, departmentid)

Values(7,3)

Insert Into [EmpdepartmentInfo](empid, departmentid)

Values(8,4)

System defined function can again be further divided into further subsections which are given below:-


1. Aggregate function.
 Example: - Avg (), Min (), Max ()


2. Configuration function.
 Example:-@@servername (), @@version()


3. Cursor function.
Example: -@@Fetch_status


4. Date and Time function.
Example: - Getdate (), Month (), Day (), Year ()


5. Mathematical function.
 Example: - Floor (), Abs ()


6. Metadata function.
 Example: - Db_Name (), File_Name ()


7. Other functions.
 Example: - cast (), convert ()


8. Rowset function.
Example: - Openxml (), Openrowset ()


9. Security function.
Example:-user(), User_Id (), User_Name ()


10. String function.
Example: - Char (), Left (), Len ()


11. System Statistical function.
 Example:-@@connections


12. Text and Image function
Example: - Textvalid ()


Types of UDF:-


Similarly, UDF can be divided Into 3 categories:-

1. Scalar UDF
2. Inline Table UDF
3. Multi statements UDF

Scalar UDF:-
The UDFs which only returns only single values comes into this category.


Syntax for creating Scalar UDFs:-


CREATE FUNCTION (FUNCTION name)
(
 (Input Variable name) (data type)
)
Returns (returning variable data type)
AS
 BEGIN
 (FUNCTION body)
 Return (returning variable name)
 End


Example


CREATE FUNCTION fn_getempname
(
 @empid Int
)
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @empname AS Nvarchar(100)


SELECT @empname=empfirstname + ' ' + emplAStname
FROM  employee
WHERE empid=@empid


RETURN @empname
END


Syntax for executing the Scalar UDF


SELECT dbo. (FUNCTION name) (input variable name)


For example, to execute the above UDF we use the following syntax:-


SELECT DBO.[fn_getempname](1) /* Here 1 is the empid*/


or


SELECT DBO.[fn_getempname](1) AS EmployeeName/*To get the output under the column EmployeeName */


If we want to store the value of this FUNCTION in an variable the we have to use the following syntax:-


DECLARE @name AS nvarchar(100)
SELECT @name =dbo.[fn_getempname](1)
SELECT @name


Here we first needs to define a variable (@name) which will be going to store the value return by the function and then used this variable in the SELECT statement to store the value.


Result
EmployeeName
Samir Singh


Explanation


This function will return the name of the employee whose empid we passed in the function as the Input parameter.


Inline Table UDF:-


The UDF which contains a single inline SQL statement and returns the data in the form of table is called Inline Table UDF.


Syntax for creating Inline Table UDF:-


CREATE FUNCTION (FUNCTION name)
(
 (Input Variable name) (data type)
)
Returns Table
AS
Return (SELECT statement)


Example:-

Create Function fn_getempinfo
(
 @empid Int
)
Returns Table
As
 Return Select empid,empnumber,empfirstname,emplastname,empemail 
           From employee 
           Where empid=@empid


Syntax for executing the Inline Table UDFs


Select (columns names) from dbo. (Function name) (Input Parameter)



SELECT empid,empnumber,empfirstname,emplastname,empemail
FROM dbo.[fn_getempinfo](1)


Result


Empid  Empnumber  Empfirstname Emplastname   Empemail


1         A001               Samir              Singh               samir@abc.com


Explanation


This FUNCTION will return the columns empid, empnumber, empfirstname, emplAStname, empemail of the employee AS a table variable whose employeeid is given AS the input parameter to the FUNCTION.


Multi statements UDF: -


The UDFs which contain multiple SQL statements to returns the data in the form of table is called Multi Statements UDFs.


Syntax for creating Multi Statements UDFs: -


Create Function
(
 (Input Variable name) (data type)
)
Returns (table variable) Table (table columns)
As
 Begin
 (Function body)
 Return
 End

Example


CREATE FUNCTION fn_GetEmpdepartmentinfo
(
 @empid Int
)
 Returns @Empproject Table
 (
 Employeename Nvarchar(100),Empemail Nvarchar(50),Departmentname Nvarchar(100)
 )
AS
BEGIN


 Insert Into @Empproject(Employeename,Empemail,Departmentname)
 SELECT empfirstname + ' '+ emplAStname ,empemail,departmentname 
  FROM employee Inner Join EmpdepartmentInfo On employee.empid=EmpdepartmentInfo.empid 
  Inner join Department On EmpdepartmentInfo.departmentid=Department.departmentid
 WHERE employee.empid=@empid


RETURN
END


Syntax for executing the Multi Statements UDF


Select (columns names) from dbo. (Function name) (Input Parameter)


SELECT Employeename,Empemail,Departmentname 
FROM dbo.[fn_GetEmpdepartmentinfo](1)


Result


Employeename     Empemail              Departmentname 
Samir Singh          samir@abc.com        Accounts


Explanation


This function will returns the Employeename,Empemail,Departmentname of the employee whose empid we pass as the input parameter to the function.

Difference between UDFs and Stored Procedures:-

1. A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.
2. Stored procedure in SQL Server cannot we executed within the DML statement. It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement.
3. A function can be called from within the Stored Procedure but a stored procedure cannot be called from within a function.
4. We can use result set return by the function as a table in Join statements but we can't use ResultSet return from stored procedure as table in Join statements.
5. Transaction management is not possible in function but it is possible in Stored procedures.
6. Print function cannot be called within the function but it can be called within the stored procedure.

Advantages of UDF:-

1. SQL Functions can be used in a DML statement. It means we can use execute the FUNCTION within the SELECT statement.


2. We can use the recursive FUNCTION to get the hierarchical information.


For example, if we want to get the all the employee which are directly or indirectly have the manager whose empid is given AS the input parameter(@managerid), then we can use the following FUNCTION which calls itself.


CREATE FUNCTION fn_recuursivesample
(
 @managerid int,
 @mode int
)
Returns @temporder table(employeeid int , managerid int)
AS


BEGIN


 DECLARE @count AS int
 DECLARE @empid AS int
 DECLARE @next_empid AS int
 DECLARE @next_orderid AS int


 IF @mode=0
 BEGIN
    INSERT Into @temporder
    SELECT @managerid,(SELECT managerid FROM employee WHERE empid=@managerid)
END


SELECT @count=count(empid)FROM employee WHERE managerid=@managerid
 IF @count=1
 BEGIN
  SELECT @empid=empid FROM employee WHERE managerid=@managerid
  INSERT Into @temporder Values(@empid,@managerid)
  INSERT Into @temporder SELECT * FROM dbo.fn_recuursivesample(@empid,1)
 END
ELSE IF @count>1
  BEGIN
   SELECT @empid=min(empid)FROM employee WHERE managerid=@managerid
   WHILE @empid>0
        BEGIN
           Insert Into @temporder Values(@empid,@managerid)


           Insert Into @temporder 
           SELECT * FROM dbo.fn_recuursivesample(@empid,1)


           SELECT @next_empid=isnull(min(empid),0)
           FROM employee
           WHERE empid >@empid and managerid=@managerid
        SET @empid=@next_empid
   END
END
RETURN
END


Syntax to execute the above function


SELECT employeeid, managerid FROM dbo. fn_recuursivesample(1,0)


Result:-


Employeeid        Managerid
     1                            0
     2                            1
     5                            2
     6                            5
     8                            6
     7                            5
     3                            1
     4                            4
3. We can use the Function in the Join queries.


4. We can used UDFs as the parametrized view(a view which take input parameters).


5. UDFs also reduce the compilation cost of SQL codes by caching the plans and reusing them for repeated executions which mean it does not need to be re-parsed and recompiled with each use and it result in better execution time.


6. We can use the WHERE clause to filter the rows as per our needs FROM the result set return by the Function. For example, in the above function fn_recuursivesample, if we want to get the empid of only those employee which are directly under the employee whose empid is given AS the input parameter, the we can use the "WHERE" clause to filter the dataset return by the function.


SELECT employeeid, managerid FROM dbo. fn_recuursivesample(1,0) WHERE managerid=1


It will give the following result:-


Employeeid   Managerid
2                       1
3                       1
4                       1


7. UDFs can be used to divide the complex code into shorter and simple blocks of code which helps in maintenance of the code.


Disadvantages of the UDF

1. We cannot use temporary tables inside the UDF.
2. We cannot use Print command inside the UDF.
3. We can't use the Insert, update and delete command inside the function. We can only use these commands on the table variable defined inside the function.
4. We cannot use the try catch statement inside the function for the debugging purpose which makes it difficult to debug.
5. We can't use transaction inside the function.
6. We can't call the stored procedure from inside the function.
7. We can't return multiple record sets from a function as we can do in case of Stored Procedure.
8. We can't use Getdate() function  within a UDF.

Conclusions
    Functions are the feature which is given by the SQL Server to make our complex code shorter and less complex. We can either use the Functions which are already provided by the SQL Server known as the System defined Function such as Avg (), Min (), Max () or we can write our own UDF. If we want to get only a scalar value as result, we can create Scalar UDFs or if we want to get a ResultSet, then we can create Inline Table UDFs or Multi statements UDFs. We can also used Functions in the SELECT statement and in join statements. But while creating the UDFs we should also keeps in our mind that no DML(Insert, Update, Delete) statement can be performed inside the UDF. Also we can't use temporary table inside the Function. Also we can't use transaction management inside the UDF.

SQL Triggers - An Introduction

Introduction

Triggers can be defined as the database objects which perform some action for automatic execution whenever users try to do execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are bound to specific tables. As per MSDN, triggers can be defined as the special kind of stored procedures. Before describing the types of triggers, we should first understand the Magic tables which are referenced in triggers and used for reuse.


Magic Tables

    There are two tables Inserted and deleted in the SQL Server, which are popularly known as the Magic tables. These are not the physical tables but the SQL Server internal tables usually used with the triggers to retrieve the inserted, deleted or updated rows. These tables contain the information about inserted rows, deleted rows and the updated rows. This information can be summarized as follows:

Action	Inserted	Deleted
Insert	Table contains all the inserted rows	Table contains no row
Delete	Table contains no rows	Table contains all the deleted rows
Update	Table contains rows after update	Table contains all the rows before update

Difference between Stored Procedure and Trigger


1) We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.


2) We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table. 


3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.


4) Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.


5) Stored procedures can return values but a trigger cannot return a value.


6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.


7) We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger.


8) We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.


DML Triggers
       

Types of trigger
   In SQL Server, there are two types of triggers which are given below:-

1.    After Triggers
2.    Instead of Triggers

In this article, we will use three tables named customer, customerTransaction and Custmail whose structure is given below:-


Create table customer (customerid int identity (1, 1) primary key,Custnumber nvarchar(100), custFname nvarchar(100), CustEnamn nvarchar(100), email nvarchar(100), Amount int, regdate datetime)


Create table customerTransaction(Transactionid int identity(1,1)primary key,custid int,Transactionamt int, mode nvarchar, trandate datetime)


Create table Custmail (Custmailid int identity (1, 1) primary key, custid int, Amt int, Mailreason nvarchar(1000))


 After Triggers:-
   "After Triggers" are executed after the data modification action (Insert, Delete, Update) is executed on the respective tables. A table can have multiple triggers defined on it.


Syntax of the After trigger
Create Trigger trigger_name
On Table name
For Insert/Delete/update
As
 Begin


    //SQL Statements
End


Example of After Trigger for Insert


Suppose we have a requirement that whenever a new customer is added then automatically its corresponding value must be inserted into the table Custmail so that an email can be send to the customer and an authorized person in the Bank. To solve this problem we can create a After Insert trigger on the table customer whose syntax is given below:-


Create Trigger trig_custadd on Customer
For Insert
As
Begin
  Declare @Custnumber as nvarchar(100)
  Declare @amount as int
  Declare @custid as int


  Select @Custnumber=Custnumber, @amount=Amount
  From inserted


  Select @custid=customerid
  From customer
  Where Custnumber =@Custnumber


  Insert Into Custmail (custid,Amt,Mailreason)
        Values (@custid,@amount,'New Customer')
End


This trigger will be fired, whenever a new Customer is added to the bank and the corresponding entry is inserted into the table Custmail. The mail functionality will use the entries from the table custmail to send the mail to the Customer.


Example of After Trigger for Delete


Suppose, there is an another requirement that whenever a customer is deleted from the system, a mail is send to the customer containing the notification about deletion.To sends the mail, we need to insert an entry of the customer in the table custmail, whenever a customer is deleted from the master table customer. To achieve this we will use the after trigger for deletion. In the example given below, we will use the magic table Deleted.

Create trigger trig_custdelete

on customer

for delete

as

begin

 Declare @Custnumber as nvarchar(100)

 Declare @custid as int

 select @Custnumber=Custnumber from deleted

 select @custid=customerid from customer where Custnumber =@Custnumber

 delete from customerTransaction where custid=@custid

   insert into Custmail

   values(@custid,0,'Customer delete')

end

Example of After Trigger for Update

Suppose, we have also a requirement that whenever a client credit his account or updated his name (first name as well as last name), a mail should be send to the customer containing this information. In this case, we can use the After trigger for update. In this example,we are going to use the Magic table Inserted.

create trigger trig_Custupdate

on customer

for update

as

begin

  declare @Custnumber as nvarchar(100)

  declare @amount as int

  Declare @custid as int

  if update(amount)

    begin

        select @Custnumber=Custnumber, @amount=Amount from inserted

        select @custid=customerid from customer where Custnumber =@Custnumber

        insert into Custmail

        values(@custid,@amount,'Customer Amount Update')

    end

  if update(custFname)or update(CustEnamn)

    begin

   insert into Custmail

   values(@custid,0,'Customer Name Update')

    end

end

In the above example, we used the Update function on the columns amount, custfname and custEname which initiates the update trigger on modification of these columns.


Instead of Triggers
    Instead of trigger is used when we want to perform another action instead of the action which causes the trigger to fire. Instead of trigger can be defined in case of Insert, Delete and Update. For example, suppose we have a condition that in a single transaction a user could not be able to debit more than $15000. We can use the Instead of trigger, to implement this constraint. If the user try to debit more than $15000 from his account at a time then error is raised with the message "Cannot Withdraw more than 15000 at a time". In this example we use the magic table Inserted.


Create trigger trigg_insteadofdelete
on customerTransaction
instead of insert
as
begin
      declare @Custnumber as nvarchar(100)
      declare @amount as int
      Declare @custid as int
      Declare @mode as nvarchar(10)
      select @custid=custid , @amount=Transactionamt,@mode=mode from
      inserted
      if @mode='c'
         begin
           update customer set amount=amount+@amount where 
           customerid=@custid
           insert into Custmail 
           values(@custid,@amount,'Customer Amount Update')
         end
      if @mode='d'
          begin
             if @amount<=15000
         begin
    update customer set amount=amount-@amount where 
                  customerid=@custid
    insert into Custmail 
    values(@custid,@amount,'Customer Amount Update')
         end
             else
  begin
    Raiserror ('Cannot Withdraw more than 15000 at a time',16,1)
    rollback;
        end
          end
end


DDL Triggers



 DDL Triggers has the similar behavior as the DML triggers to have except that they are fired in response to a DDL type event like Alter command, Drop command and Create commands. In other words, it will fire in response to the events which try to change the schema of the database. Therefore, these triggers are not created for a particular table, but they are applicable to all the tables on the database. Also DDL triggers can be fired only after the commands which make them fire is executed. They can be used for the following purposes:


1) To prevent any changes to the database Schema
2) If we want to store the records of all the events, which change the database schema.


For example, suppose we want to create a table command_log which will store all the user commands for creating tables (Create table) and commands which alter the tables. Also we don't want any table to be dropped. Therefore if any drop table command is fired, a DDL trigger will rollback the command with a message that "You can't drop a table".


The script for the table  command_log will be given below:



CREATE TABLE Command_log(id INT identity(1,1), Commandtext NVARCHAR(1000), Commandpurpose nvarchar(50))

DDL Trigger for Create_table

For storing the create table command in the table command_log , we first need to create a trigger which will be fired in response to the execution of the Create table command. 



CREATE TRIGGER DDL_Createtable
ON database 
FOR CREATE_Table
AS 
  Begin
     PRINT 'Table has been successfully created.'
     insert into command_log ()
     Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText ) [1] ','nvarchar(1000)')


  End


This trigger will be fired whenever any command for the table creation is fired and will insert the command into the table command_log and also print the message that "Table has been successfully created".


Note:  Eventdata() is a functions which returns information about the server or database events.It returns value of XML type. Read more about Eventdata()

DDL Trigger for Alter_Table


Suppose if we want to store the alter table commands also in the table command_log, we need to make a trigger for Alter_table command.


Create Trigger DDL_Altertable
On Database
for Alter_table
 as
   begin
declare @coomand as nvarchar(max)
       print 'Table has been altered successfully'
       insert into command_log(commandtext)
       Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')
       
  end


This trigger will be fired whenever any alter table command is fired on the database and will print the message "Table has been altered successfully."


DDL Trigger for Drop_Table


To stop the user from dropping any table in the database, we need to create a trigger for drop table command.


Create TRIGGER DDL_DropTable
ON database 
FOR Drop_table
AS 
 Begin
     PRINT 'Table cannot be dropped.'
     INSERT into command_log(commandtext)
     Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')
     Rollback;
end


This trigger will not allow any table to be dropped and also print the message the "Table cannot be dropped."


Nested Triggers

Nested Trigger: - In Sql Server, triggers are said to be nested when the action of one trigger initiates another trigger that may be on the same table or on the different table. 
For example, suppose there is a trigger t1 defined on the table tbl1 and there is another trigger t2 defined on the table tbl2, if the action of the trigger t1 initiates the trigger t2 then both the triggers are said to be nested. In SQL Server, triggers can be nested up to 32 levels. If the action of nested triggers results in an infinite loop, then after the 32 level, the trigger terminates.
 Since the triggers are executed within a transaction, therefore failure at any level of within nested triggers can cancel the entire transaction, and it result in total rollback.


We can also stop the execution of nested triggers through the following SQL Command:


sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO


Recursive triggers
  In SQL Server, we can have the recursive triggers where the action of a trigger can initiate itself again. In SQL Server, we have two types of recursion.

1.   Direct recursion
2.   Indirect recursion

  In Direct recursion, action of a trigger initiates the trigger itself again which results in trigger calling itself recursively.
  In Indirect recursion, action on a trigger initiates another trigger and the execution of that trigger again calls the original trigger, and this happen recursively. Both the triggers can be on the same table or created on the different tables.


Please note: Recursive trigger can only be possible when the recursive trigger option is set.


Recursive trigger option can be set using the following SQL Command:


ALTER DATABASE databasename 
SET RECURSIVE_TRIGGERS ON | OFF


How to find the Triggers in a database


1)  Finding all the triggers defined on whole the database
Suppose we want to get the list of all the triggers and their respective tables name then we can use the following SQL Statement.


 select o1.name, o2.name from sys.objects o1 inner join sys.objects o2 on  o1.parent_object_id=o2.object_id and o1.type_desc='sql_trigger'

2) Finding all the triggers defined on a particular table


 For example if we want to find out all the triggers created on the table Customer then we can use the following SQL Statement:-


sp_helptrigger Tablename
example:-
sp_helptrigger 'Customer'

3)  Finding the definition of a trigger


    Suppose if we want to find out the definition of the trigger, we can use the following SQL Statement:-


   sp_helptext triggername
For example:-
  sp_helptext 'trig_custadd'


Result

How to Disable a trigger

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }

ON { object_name | DATABASE | ALL SERVER } [ ; ]

Disabling a DML trigger on a table

DISABLE TRIGGER 'trig_custadd' ON Customer;

Disabling a DDL trigger

 DISABLE TRIGGER 'DDL_Createtable' ON DATABASE;

 Disabling all triggers that were defined with the same scope

 DISABLE Trigger ALL ON ALL SERVER;

How to enable a trigger

 Enabling a DML trigger on a table

ENABLE Trigger 'trig_custadd'  ON Customer;

Enabling a DDL trigger

ENABLE TRIGGER 'DDL_Createtable' ON DATABASE;

Enabling all triggers that were defined with the same scope

ENABLE Trigger ALL ON ALL SERVER;

How to drop a trigger.

Dropping a DML trigger

DROP TRIGGER trig_custadd  ;

Dropping a DDL trigger

DROP TRIGGER DDL_Createtable ON DATABASE;

Real life example

Some weeks ago one of my friends gets a task which needs to be completed on a very old written code. The task includes that a mail should be sent to the user in the following cases:

1. The user is added to the system.

2. Whenever any information regarding the user is updated or deleted or added.

3. A user is deleted.

The challenges in this task include:-

1. The code is very old and unstructured. Therefore, it has many inline queries written on the various .aspx pages. 

2. Queries for the insert, delete and update is also written in many stored procedures.

So the code doesn't have a common library function or a standard stored procedure which is used throughout the application which can be used to insert, update and delete a user, which is not a good practice. But it happen sometimes with the old code. The required queries are written on many .aspx pages and stored procedures.

Possible solutions:


  To complete this task, we need to insert an entry into the table tblmail with proper flags indicating the insert, delete and update. A scheduled application built in .net application will read the rows from the table tblmail and send the mails. 


Two approaches to insert the rows:

1. Find all the places in the .aspx files and the stored procedures where the queries for the insert, delete and update and after these queries, add the insert query for the table tblmail.

2. Instead of finding these queries in all the .axps files and stored procedures, create after (insert, update and delete) trigger on the user master table will insert the date in the table tblmail after the execution of the insert, update and delete statement.

We used the second approach because of the following 4 reasons:

1) It is very difficult to search so many .aspx files and stored procedures to find the required queries.

2) It has the risk that a new developer may not know about this requirement of sending mail and forget to add the code for inserting the values in the table tblmail.

3) If we need to change anything in the requirement, it has to be changed in all these files and stored procedures.

4) With the second approach, we only need to create triggers on the table and the developer, and it will also minimize the risk mention in the three 3 points mention above. 

Advantages of SQL Triggers
 1) It helps in maintaining the integrity constraints in the database tables, especially when the primary key and foreign key constrain are not defined.


 2) It sometimes also helps in keeping the SQL codes short and simple as I show in the real-life example.


3) It helps in maintaining the track of all the changes (update, deletion and insertion) occurs in the tables through inserting the changes values in the audits tables.


4) Sometimes if the code is not well managed, then it can help in maintaining the database constraints defined on the tables on which the trigger is defined. For example, suppose if have a situation that there is an online learning system in which a user can register in the multiple course.


 Suppose the organization wants to define a constraint is defined that a user cannot be deleted until he/she passed all the course in which he is registered or the user has to first himself from all the incomplete or failed courses.


 Since the code is not well managed and the code to delete the user is defined as the inline query in many .net pages and multiple stored procedures (this is not a good thing, but it happens), one has to write the code for enforcing this constraint in to all these .net files and stored procedures, which take so much time and also if the new developer does not this constraint and forgets to include the constrain enforcing code which corrupt the database. In this case, we can defines an instead of trigger on the table which checks every time a user is deleted and if the condition of the above constraint is not met, display the error message instead of  deleting user.


Disadvantages of Triggers

1) Hard to maintain since this may be a possibility that the new developer doesn't able to know about the trigger defined in the database and wonder how data is inserted, deleted or updated automatically.

2) They are hard to debug since they are difficult to view as compared to stored procedures, views, functions, etc.

3) Excessive or over use of triggers can slow down the performance of the application since if we defined the triggers in many tables then they kept automatically executing every time data is inserted, deleted or updated in the tables (based on the trigger's definition) and it makes the processing very slow.

4) If complex code is written in the triggers, then it will slow down the performance of the applications.

5) The cost of creation of triggers can be more on the tables on which frequency of DML (insert, delete and update)  operation like bulk insert is high.

Conclusion
  Trigger is bad or good depends upon its use and its proper documentation. It can be very useful when it is used to maintain the integrity constraints in the database tables in the absence of primary key and foreign key, or it is very useful for the auditing purpose in tracking all the changes. But, if it is used extensively, it can reduce the performance. Also to maintain it and making debugging simple, proper documentation of the Triggers is necessary, which records the trigger name, table name on which it is created, its definition and its purpose.

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.

We can also get the output without using derived tables as below:

select regmonth,count(studentid)[Noofstdreg],count(coursename)[Noofcoursereg]

from @tbl_studentcoursereg

group by regmonth

having count(studentid) >2 and count(coursename)>2

Derived Tables in SQL

The power of SQL Server never fails to amaze me; it literally seems that you can do just about anything in SQL. All you need is a little creativity and knowledge of the syntax, and you can put the power of SQL behind your web application. One of the neatest things I've yet done with SQL Server is using derived tables.
If you've used a VIEW before, you've used a more formal, more correct form of a derived table. For example, we could do the following:

        CREATE VIEW vwEmployeesFromNewYork AS

               SELECT * FROM Employee

               WHERE State = "NY"

        GO

Then if we wanted to see all of the Employees from New York with the last name Smith, ordered alphabetically, we could write:

        SELECT LastName, FirstName

        FROM vwEmployeesFromNewYork

        WHERE LastName = "Smith"

        ORDER BY FirstName

However, using derived tables, we could eliminate the view entirely. (Of course the view could be eliminated by simply adding an "AND State = "NY"" to the above WHERE clause, but what's important here is the concept, not the example!) Here is the same resultset as above but with the use of a derived table in place of a veiw:

        SELECT LastName, FirstName

        FROM

               (SELECT * FROM Employee

                WHERE State = "NY") AS EmployeeDerivedTable

        WHERE LastName = "Smith"

        ORDER BY FirstName

SQL - Derived Tables

Introduction
With SQL Server you have the ability to create derived tables on the fly and then use these derived tables within your query. In very basic terms, a derived table is a virtual table that's calculated on the fly from a select statement.
In concept this is similar to creating a temporary table and then using the temporary table in your query, but the approach is much simpler, because it can all be done in one step. This can be tremendously useful in certain situations.

Boost Performance

The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O, the faster the performance.
Here are the steps when you use a temporary table:

1. Lock tempdb database
2. CREATE the temporary table (write activity)
3. SELECT data & INSERT data (read & write activity)
4. SELECT data from temporary table and permanent table(s) (read activity)
5. DROP TABLE (write activity)
6. Release the locks

Compare this with the number of steps it takes for a derived table:

1. CREATE locks, unless isolation level of "read uncommitted" is used
2. SELECT data (read activity)
3. Release the locks

As is rather obvious from this example, using derived tables instead of temporary tables reduces disk I/O and can boost performance.

An example

Below, I'm going to use the Northwind database that included with MS-SQL 2000. Let's say you've been asked to generate a report that shows off the total number of orders each customer placed in 1996. "Not a problem." you think to yourself. "This is just an easy aggregated join query." So, you sit down and generate your query and come up with:

SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders

 FROM Customers C LEFT OUTER JOIN Orders O ON 

 C.CustomerID = O.CustomerID

 WHERE YEAR(O.OrderDate) = 1996

 GROUP BY C.CustomerID, C.CompanyName

Looks good, But there's something missing. Customers that didn't place an order in 1996 aren't showing up. You could leave off customers that didn't place an order, but in many cases the customers that didn't buy place orders are the ones that are of the most interest.
If you think you can include those customers with a "is null" check, you'd be wrong. Run the following code and see if you notice what is wrong:

SELECT C.CustomerID, C.CompanyName,

 COUNT(O.OrderID) AS TotalOrders 

 FROM Customers C LEFT OUTER JOIN Orders O ON 

 C.CustomerID = O.CustomerID

 WHERE (YEAR(O.OrderDate) = 1996 OR O.OrderDate IS NULL)

 GROUP BY C.CustomerID, C.CompanyName

At first glance this may look right, but if you examine the results carefully you'll see that the customers with the zero count have never placed an order. If a customer has placed an order, but just not in the year 1996 they won't show up. This is because the "is null" check finds customers that have never placed an order -- it still doesn't do anything to add customers who've placed an order, but just not in 1996.
This is where a derived table can come in handy. Instead of using the "Orders" table, we'll reduce the Orders table to a snapshot of itself -- including only data from the year 1996.

SELECT C.CustomerID, C.CompanyName,

 COUNT(dOrders.OrderID) AS TotalOrders

 FROM Customers C LEFT OUTER JOIN 

 /* start our derived table */

 (SELECT * FROM Orders WHERE YEAR(Orders.OrderDate) = 1996) AS dOrders

 /* end our derived table */

  ON 

  C.CustomerID = dOrders.CustomerID

  GROUP BY C.CustomerID, C.CompanyName

Run the above query and examine the results. You should now see a row returned for each Customer and the total number or orders placed in the year 1996 -- including the customers that didn't place an order.

The Reason

This works is because the LEFT JOIN will include all matches or null values. If the LEFT JOIN has matches (like in our first query,) but non that match the criteria those rows are excluded. In our derived table, since we're only running against the orders from the year 1996 we're ensuring that we return results for each customer.
"I think this is the best and easy example to understand the concept of derived tables."
Now if you asked when and why to use derived tables then I'll say that it all depends on the complexity of your query; you have to decide when to use derived tables, but I think derived tables are faster than Temporary tables. Read the following points for the when to use derived tables.

• If you face a challenge of whether to create a temporary table or a view to produce the desired query take a look at using a derived table instead
• Experiment with using derived tables, views and temporary tables to see what yields better performance results. There are several articles on the internet that have shown that using a derived table is faster then temporary tables, but try it for yourself.

Updating a derived table

Now the question of updating a derived table. I don't think that it is a big issue, but still it's a question asked about derived tables. So the answer is as follows,
- MS SQL 2000 - Not possible
- MS SQL 2005 - Possible
So, updating a derived table is not possible in SQL Server 2000. If you try to do so, then SQL Server will give you the following error.

Update T SET Id=0 FROM (SELECT * FROM tt1) AS T

Error:

Server: Msg 4421, Level 16, State 1, Line 1

Derived table 'T' is not updatable because a column of the derived table is derived or constant.

I am not 100% sure that it is not possible to update a derived table in SQL Server 2000, but from what I have studied and searched on Internet, I believe that it is not possible.
However, in the case of SQL Server 2005, it is possible. I have executed the same query above in SQL Server 2005, and it is working perfectly and all rows are updated.

Conclusion

The power of SQL Server amaze me all the time, to see the power of SQL Server all you need is a little creativity and knowledge of the syntax. Some times derived tables can be more useful, likewise, you need to create a view for a single query and then you want to use it within another query and after that the view should be dropped because no use of the view after that action, then here derived tables can help you and benefits you by saving to create you a catalog entry in the form of view in the server.
For repeated queries, a SQL derived table used multiple times performs well with cached definition. It will not down your performance. A SQL derived table differs from a temporary table in that a SQL derived table exists only for the duration of the query, while a temporary table exists until the server is brought down and also it uses lot of disk space than a derived table in the temdb database. So it's better to make use of Derived tables wherever possible by eliminating the one time use views and by eliminating the temporary tables. What I think is it will improve the performance of your query.

Fixing the error: The multi-part identifier ... could not be bound in Join statements

One of the most common errors that you might face in join statements is this error:

Msg 4104, Level 16, State 1, Line ...

The multi-part identifier ...... could not be bound.

The main reason for this error is that the source table cannot be found, for example if you have statement such as Table1.OrderDate, and then if you get error above, this means that Table1 cannot be found in the query. Sometimes you can see that source table exists in the query, but T-SQL cannot understand it, especially when you write join statements.

Consider join statement below:

select * from sales.SalesOrderHeader SH

left outer join sales.SalesOrderDetail SD

left outer join Production.Product P

on SD.ProductID=p.ProductID and SH.SalesOrderID=SD.SalesOrderID

With statement above you will get the error : The multi-part identifier "SH.SalesOrderID" Could not be bound.

the reason is that SalesOrderHeader (SH) table is hidden from the third part of the join. and if you want to apply joining condition it will throw the error.

Resolution is:

put join condition of each join statement exactly after it. query below is showing fixed version of that statement:

select * from sales.SalesOrderHeader SH

left outer join sales.SalesOrderDetail SD

on SH.SalesOrderID=SD.SalesOrderID

left outer join Production.Product P

on SD.ProductID=p.ProductID

This statement used SH.SalesOrderID = SD.SalesOrderID right after the first join where the SH table is totally visible

Replace Duplicate Spaces with Single Space in T-SQL

T-SQL code (working in SQL Server) to replace duplicate spaces (or triple or more) with a single space is :

declare @text varchar(max)

set @text=' St    Charles'

while charindex('  ',@text)>0

set @text=replace(@text,'  ',' ')

select @text

and the result is :

St Charles