Technology with Vivek Johari

Replication in SQL Server

2012-12-31T10:48:03-08:00

Replication is a way of distribution of database and their objects from one master database to one or more recipient databases which can be on the same server or on the remote server.It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, we can distribute data to different locations and to remote users over local and wide area networks.It is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users so that they can make changes to their local copy of the database.

Replication Components

SQL Server replication consists of three components: The Publisher, the Distributor and the Subscriber. These components act on articles that are defined within publications and subscriptions.

Article

For each SQL Server object that should be replicated, a replication article needs to be defined. Each article corresponds to a single SQL Server object, or a subset of an object. The objects that are replicated most often are tables, views and stored procedures. The properties of an article determine whether the article contains the entire object, or if a filtered subset of the objects makes up the replicated article.

Publication

A group of articles that logically belong together can be combined into a publication. The publication has options defined that apply to all the articles in that publication. The main option defined by a publication is the type of replication that is to be used.

Publisher

The SQL Server instance that makes a publication available for replication is called the publisher.

The publisher monitors all articles for changes, and makes information about those changes available to the distributor.

Distributor

The distributor is the SQL Server instance keeps track of all subscribers and all published changes and makes sure that each subscriber gets notified of each change. Most of the changes are tracked in a distribution database. The distributor can be a separate SQL Server instance, but often the distribution service runs on the same machine as the publisher.

Subscriber

The subscriber is the SQL Server instance that receives all the published information through subscriptions.

Subscription

A subscription is the counterpart of the publication. A subscription defines which server (subscriber) is to receive the updates published in a publication. Each subscription creates a link between one publication and one subscriber. There are two types of subscriptions: push subscriptions and pull subscriptions. In a push subscription, the distributor directly updates the data in the subscriber database. In a pull subscription, the subscriber asks the distributor regularly if any new changes are available, and then updates the data itself.

Types of replication:-

Snapshot Replication
Transactional Replication
Merge Replication

Snapshot Replication:-

Snapshot replication is also known as static replication. Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time. Every time snapshot replication is run, everything is recopied from scratch, so it has high bandwidth and storage requirements. Therefore this replication is advisable only when the size of the database is small.

Transactional Replication:-

Transactional replication works on a transaction basis. In Transactional replication only the committed changes are logged into the subscribers. Transactional replication allows for close to real time synchronization and leaves only a small footprint on the publisher. It is the best way of replication to work one way only.

Merge Replication:-

Merge replication was designed to allow the changes to the data to be made on the publisher as well as the subscriber side. In Merge Transaction, a subscriber might not be connected during the day. That subscriber would synchronize after reconnecting in the evening. If a row gets updated in two different places at the same time, a conflict occurs. Merge replication have many built in options to resolve those conflicts.

Log Shipping in SQL Server

2012-12-31T10:47:42-08:00

Log Shipping:- Log Shipping is a process of keeping the latest copy of the database of the primary database server on the secondary database server with the help of transaction logs backup. It is done on a regular basis to minimize the loss of the database in case of database fail over. It is a continuous process in which the backup of the transaction logs has been taken on the regular basics and then they are transported to the secondary server. In the secondary server, the transactions backups are restored to make the database on the secondary database server as the exact replica of the primary database or near to the current primary database.

This process can have three components:-

Primary database Server:- This server contains the primary database .The backup of the transaction logs of the Primary database is taken on this server and send to the Secondary database server.

Secondary database Server:- This server contains the secondary database . There can be one or more than one secondary database server. Here backup of the primary database server transactions log are applied to the secondary database server databases individuality so that they have database which is close to the Primary server database.

Monitor Server:- This optional server, monitor the log shipping process by recording the history and the status of the the backups of the transaction logs. It also monitor the restore operations on the secondary server. It can also raise alarm if the process is not completed as per scheduled.

Rebuild And Reorganization of Indexes

2012-12-31T10:46:01-08:00

Rebuild and Reorganization of Indexes:- SQL Server has the ability of maintaining the indexes whenever we makes changes (update, Insert, Delete) in the tables. Over a period of time, the may causes the fragmentation on the table in which the logical ordering based on the key value pairs does not match with the physical ordering inside the data files. This causes the degradation of the performance of the SQL Query.To solve this problem of fragmentation, we use rebuilding or reorganization of the indexes.

In case of Rebuilding, it drop the particular index and again recreate it.It removes fragmentation, reclaims the disk space by compacting the pages based on the specified or existing fill factor setting, and again reorders the index rows in those contiguous pages. We can rebuild all the indexes of the table within a single transaction by specifying the ALL with it.

ALTER INDEX [Indexname] ON [tablename] REBUILD

ALTER INDEX ALL ON [tablename] REBUILD

In case of Reorganization, it defragments the leaf level nodes of indexes by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. It uses minimal system resources and also compact the index pages

ALTER INDEX [Indexname] ON [tablename] REORGANIZE

ALTER INDEX ALL ON [tablename] REORGANIZE

Rebuild should be used when the fragmentation index is greater than 30% and reorganization option should be used when the fragmentation index is between 5% and 30%. Rebuilding of an Index can be done online or offline. But to achieve the availability of the index, rebuilding should be done online. Reorganization can be done online.

Related Article
Fragmentation

Fragmentation in SQL Server

2012-12-31T10:47:07-08:00

Fragmentation:- Fragmentation can be defined as condition where data is stored in a non continuous manner. In can be defined into two types

1. Internal Fragmentation
2. External Fragmentation

Internal Fragmentation:- In this fragmentation, there exists a space between the different records within a page. This is caused due to the Insert, delete or Update process and due to this Index takes more space than it needs to and it result in more read operation during scanning. It can also be caused due to the low value of fill factor of the page which determine how much % of the page should be used for storing the records.

External Fragmentation:- In this fragmentation, the extents of the table is not physically stored continuously on the disk which causes the jump from one extent to another extent which takes longer time.

Both the fragmentation can be resolved by Rebuilding or Reorganization of the indexes of the tables.

For Rebuilding or Reorganization of the indexes , please refers the below link:

http://vivekjohari.blogspot.in/2013/01/rebuild-and-reorganization-of-indexes.html

Welcome 2013

2012-12-31T07:19:20-08:00

Happy New Year 2013

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

Happy Diwali

2012-11-13T01:28:35-08:00

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

Boyce - Codd Normal Form (BCNF)

2012-08-19T05:10:23-07:00

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

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

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

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

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

Isolation levels in SQL Server

2012-08-19T04:46:16-07:00

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

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

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

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

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

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

Read Uncommitted
Read Committed
Repeatable Read
Serializable
Snapshot

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

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

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

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

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

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

SET TRANSACTION ISOLATION LEVEL

Merge Command In Sql Server

2012-04-17T11:15:03-07:00

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.

Adding , Deleting and Updating a Column in a table

2012-04-15T01:33:14-07:00

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

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

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

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

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

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

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

Select * from Employee

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

Alter table Employee Alter column Firstname nvarchar(100)

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

Alter table Employee Add Regdate datetime null

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

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

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

Select * from Employee

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

Alter Table Employee Drop Column Regdate

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

Select * from Employee

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

Derived Table In Sql Server

2012-04-15T00:07:28-07:00

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.

Pivot and Unpivot table in SQL SERVER

2012-04-14T02:34:36-07:00

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

The syntax for the Pivot is given below:-

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

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

Syntax for creating the database:-

Create database DB_Pivot

Query for creating table:-

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

Query for inserting the data into the table:-

Insert into tbl_student (studentname,grade,marks)

Select 'Vivek Johari','I',30

Union All

Select 'Vivek Johari','II',20

Union All

Select 'Vivek Johari','III',35

Union All

Select 'Vivek Johari','IV',40

Union All

Select 'Vivek Johari','V',45

Union All

Select 'Avinash Dubey','I',30

Union All

Select 'Avinash Dubey', 'II', 2

Union All

Select 'Avinash Dubey', 'III' ,35

Union All

Select 'Chandra Singh', 'I', 30

Union All

Select 'Chandra Singh', 'II', 20

Union All

Select 'Chandra Singh', 'III', 35

Union All

Select ' Pankaj Kumar', 'I', 33

Union All
Select ' Pankaj Kumar', 'II', 29

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

Select * from tbl_student

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

Studentname I II III IV V

Vivek Johari 30 20 35 40 45

Chandra Singh 30 20 35
Avinash Dubey 30 20 35

Pankaj Kumar 33 29

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

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

Or we can use the given below query also:-

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

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

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

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

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

Insert into tbl_stdmarksdata (studentname,I,II,III,IV,V)

Select 'Vivek Johari',30,20,35, 40, 45

Union All

Select 'Chandra Singh',30,20,35,44, 80

Union All

Select 'Avinash Dubey',30,25,35,20, 39

Union All

Select 'Pankaj Kumar',33,29,30, 60, 50

After insert, the data in the table :-

select * from tbl_stdmarksdata

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

select studentname,Marks,Grade

from tbl_stdmarksdata

unpivot

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

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

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

SQL Server- Output clause

2012-01-22T09:51:45-08:00

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

CTE command In Sql Server

2012-01-17T10:44:59-08:00

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

SQL Server-Table Variable

2012-01-10T02:45:09-08:00

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.

SQL Server - Local temporary table

2012-01-08T10:39:48-08:00

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

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

SQL Server - Global temporary tables

2012-01-08T10:38:46-08:00

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:-

We can't use global temporary table inside a SQL Function.
We can't create triggers on the temporary tables.
We can create View on the temporary tables.

How to remane a Table in Sql Server

2012-01-03T09:43:07-08:00

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

2012-01-03T09:43:25-08:00

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

SQL Triggers - An Introduction

2012-01-28T20:51:13-08:00

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:-

After Triggers
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.

Direct recursion
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.

SQL Server Functions- An Introduction

2012-01-28T20:51:37-08:00

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:-

System Defined Function
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:-