Tuesday, December 22, 2009

Sql Server- Difference between Stored Procedure and Function


In Sql Server, both functions and stored procedures can be defined as the logically grouping of Sql statements formed to complete a task, but still there are many differences between Stored procedure and Functions.

These differences can be summarized as follows:-

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 can not 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) Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function.

4) A stored procedure can return multiple parameters but a function can return only one value.

5) We can use join in the outcome from the functions but we can't use joins in the outcome from stored procedures.

6) Transaction management is not possible in functions but it is possible in Stored procedures.

7)Print function can not be called within the function but it can be called within the stored procedure.

Thursday, December 17, 2009

Difference between Delete and Truncate Command

Although the Delete and Truncate Commands logically does the same work of deleting the rows from the table but still there are many differences in their working. These differences are given below:-

1) Delete command maintained the logs files of each deleted row but Truncate command do not maintain the logs files for each deleted row but maintains the record for deallocation of the datapages in the log files.The deallocation of the datafiles means that the data rows still exists in the data pages but the extends have marked as empty for reuse.

2) Truncate command is much faster than delete command.

3) You can use Where clause in case of Delete command to delete a particular row but in case of Truncate command you have to delete the data from all the row since Where clause is not work with Truncate command.

4) Triggers is fired in case of Delete command only and they are not fired when Truncate command is used.

5) Truncate command resets the Identity property to its initial value whereas Delete command do not resets the Identity property of the column.

6) Delete is a DML command and Truncate is a DDL command.

Sunday, December 6, 2009

Sql Server - How to write a Stored procedure in Sql server

Stored Procedure:- Stored Procedure In Sql server can be defined as the set of logically group of sql statement which are grouped to perform a specific task. There are many benefits of  using a stored procedure. The main benefit of  using a stored procedure is that it increases the performance of the database.The other benefits of using the Stored Procedure is given below:-

Benefits of using the Stored Procedure:-

1) One of the main benefit of using the Stored procedure is that it  reduces the amount of information sent to the database server. It can become more important benefit when the bandwidth of the network is less. Since if we send the sql query (statement)which is executing in a loop to the server through network and the network get disconnected then the execution of the sql statement don't returns the expected results, if the sql query is not  used between Transaction statement and rollback statement is not used.

2) Compilation step is required only once when the stored procedure is created. Then after it  does not required recompilation before executing unless it is modified and re utilizes the same execution plan whereas the sql statements needs to be compiled every time whenever it is sent for execution even if we send the same sql statement every time.

3) It helps in re usability of the sql code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same sql statement every time. It helps in reduces the development time.

4) Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure. 

5) Sometime it is useful to use the database for storing the business logic in the form of stored procedure since it make it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.
How to write a Stored Procedure in Sql server:-
 
Suppose there is a table called tbl_Students whose structure is given below:-

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

Support we insert the following data into the above table:-

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Vivek', 'Johari', 'vivek@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Pankaj', 'Kumar', 'pankaj@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Amit', 'Singh', 'amit@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Manish', 'Kumar', 'manish@abc.comm')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Abhishek', 'Singh', 'abhishek@abc.com')

Now, while writing a Stored Procedure,  the first step will be to write the Create Procedure statement as the first statement,

Create Procedure Procedure-name 
(
Input parameters ,
Output Parameters (If required)
)

As
Begin

     Sql statement used in the stored procedure
End

Now,  suppose we need to create a Stored Procedure which will returns a student name whose studentid is given as the input parameter to the stored procedure. Then the Stored Procedure will be:-

/*  Getstudentname is the name of the stored procedure*/
Create  PROCEDURE Getstudentname
(
@studentid INT                   --Input parameter ,  Studentid of the student
)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

We can also collect the student name in the output parameter of the Stored Procedure.For Example:-

 /* 
GetstudentnameInOutputVariable is the name of the stored procedure which uses output variable @Studentname to collect the student name returns by the stored procedure
*/

Create  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                                          --Input parameter ,  Studentid of the student
@studentname VARCHAR(200)  OUT        -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

Note:-/* */ is used to write comments in one or multiple lines
           --     is used to write comment in a single line

How to Alter a Stored Procedure in a Sql server:-

In Sql Server, a stored procedure can be modified with the help of the Alter keyword.Now if, we want to get student email address through the same procedure GetstudentnameInOutputVariable . So we need to modified it by adding one more output parameter " @StudentEmail " which is shown below:-

/* 
Stored Procedure GetstudentnameInOutputVariable is modified to collect the email address of the student with the help of the Alert Keyword
*/
Alter  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                   --Input parameter ,  Studentid of the student
@studentname VARCHAR (200) OUT,    -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT     -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname, @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END

Note:- It is not necessary that a stored procedure will have to written. It can be the case when a stored procedure doesn't written any thing. For Example, a stored procedure can be used to Insert, delete or update a sql statement. For Example the below stored procedure is used tp insert value into the table tbl_students.

/*
This Stored procedure is used to Insert value into the table tbl_students.
*/
Create Procedure InsertStudentrecord
(
 @StudentFirstName Varchar(200),
 @StudentLastName  Varchar(200),
 @StudentEmail     Varchar(50)
)
As
 Begin
   Insert into tbl_Students (Firstname, lastname, Email)
   Values(@StudentFirstName, @StudentLastName,@StudentEmail)
 End
Execution of the Stored Procedure in Sql server:-

   Execution of the Stored Procedure which don't have a Output Parameter:-
   A stored procedure is used in the Sql server with the help of the "Execute" or "Exec" Keyword. For Example , If we want to execute the stored procedure "Getstudentname", then we will use the following statement:-

Execute Getstudentname 1
Exec Getstudentname 1
Execution of the Stored Procedure using the Output Parameter:-
     If we want to execute the Stored procedure "GetstudentnameInOutputVariable " , then we first need to declare the variable to collect the output values. For example

Declare @Studentname as nvarchar(200)   -- Declaring the variable to collect the Studentname
Declare @Studentemail as nvarchar(50)     -- Declaring the variable to collect the Studentemail
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail      -- "Select" Statement is used to show the output from Procedure
Summary:-
In the end we can say that a Stored procedure not only enhancing the possibility of reusing the code and execution plan,  but it also increases the performance of the database by reducing the traffic of the network by reducing the amount of information send over the network.
DMCA.com

Tuesday, November 17, 2009

Sql Server - Union and Union All

Union and Union All, both are used to select data from one or more than one tables but still they have some differences. For Example, Union is used to select distinct data from the tables but Union All allows duplicate rows to be selected from the tables.


Suppose we have a table called tbl_Manager and the structure of this table is given below:-


CREATE TABLE tbl_Manager(Managerid int identity(1,1) not null,Managername nvarchar(255),Departmentname nvarchar(255))


Now Suppose we insert the data into the table with the help of the query given below:-


Insert into tbl_Manager(Managername,Departmentname)
select 'Vivek Johari', 'Technology' union all
select 'Atul','Testing' union all
select 'Vivek Johari', 'Mobile development' union all
select 'Atul','HR' union all
select 'Virender Singh', 'Web development' union all
select 'Virender Singh', 'R&D development' union all
select 'Jagdish','Quality Assurance' union all
select 'Jagdish','Mobile development' union all
select 'Atul','Technology'




Now Suppose, we want to select the “Managername” from the table who work in the department “Technology” and “Mobile development”.


First, we try to get the result with the help of Union.In this case the query will be given below:-


select Managername from tbl_Manager where departmentname='Technology'
union
select Managername from tbl_Manager where departmentname='Mobile development'


Result :-







Now if we use the Union All instead of union to selet the managername from the table who work in the department “Technology” and “Mobile development”, we get the following result:-

select Managername from tbl_Manager where departmentname='Technology'
union all
select Managername from tbl_Manager where departmentname='Mobile development'



Result:-








By analyzing the two results, one can easily find out that the Union all allowed dublication of the data and Union do not allows the duplicate data to appear.


The second difference between Union and Union All can be given on the basic of their uses. Both Union and Union All  can be used to insert multiple rows in the table in a single query. You can see the use of the Union All in the insert statement, which we have used to insert the data into the table tbl_Manager.But in case where we required to insert multiple rows containing duplicate values , we can’t use Union for this purpose. Also the use of Union will increase the cost of execution plan for the insert query as compared to Union all..


Also since Union does not allowed the duplication of the data, therefore it has to first select the whole data and then use the Distinct function to eliminate the duplicate data.It will increase the cost of the execution plan for the query since it have to use the two functions whereas Union All allow the duplication of the data so it only needs the select statement to show the data. So it will cost little as compare to the Union.


Summary
Both Union and Union All are used to select the data from the tables but we should use Union All unless it is required to fetch only the distinct data since the use of Union will increase the cost of the execution of the query.







Thursday, November 12, 2009

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 its initial seed value.

Tuesday, October 13, 2009

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


Fig1



Fig2





DMCA.com

Monday, October 12, 2009

Different ways to create a table and insert data into the table

Tables can be defined as the structure which contains the data in the database. There can be many ways to create table and to insert data into the table. Some of the ways to create a table is given below:-

1)Creation of a table with the help of a create statement

For example, suppose we want to create a table called tbl_student then the syntax of creating this table is given below:-

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

Let us suppose we want to insert data into the table tbl_students, then we can use the insert statements to insert data into the table.

The insert statement used for inserting the data is given below:-

Insert into tbl_students(Firstname,lastname,email)
select 'Vivek','Johari','Vivek@abc.com' union all
select 'Pankaj','Kumar','Pankaj@abc.com' union all
Select 'Amit','Singh','amit@abc.com'

Here we can insert multiple rows into the table with the help of a single query mentioned above.

Another way to insert the values into the table tbl_students is :-

insert into tbl_students(Firstname,lastname,email)
Values('Manish','Kumar','manish@abc.com')

Insert into tbl_students(Firstname,lastname,email)
Values('Abhishek','Singh','abhishek@abc.com')

Insert into tbl_students(Firstname,lastname,email)
Values ('Uma','Sharma','Uma@abc.com')

Here, whenever we need to insert a row into the table ,we have to use a seperate insert statement.

The result of the above Sql queries can be verified with the help of the following query:-

SELECT * FROM tbl_students

Result:-









Note:- We don't need to insert data into the column Studentid since it is defined as the Identity column and whenever a record is inserted into the table, sql server automatically insert value into this column.

2)Creation of table with the help of another table.

2.1) Suppose we want to create a table tbl_studentinfo which contains a subset of the columns (studentid, Firstname, Lastname) of the table tbl_student then we can use the following query.

Select studentid, Firstname, Lastname into tbl_studentinfo from tbl_students

This query will create a table tbl_studentinfo having columns studentid, Firstname, Lastname and it contains data of these columns from the table tbl_students. This can be verify with the help of the given below query.

SELECT * FROM tbl_studentinfo

Result:-









2.2)Suppose we want to create a table which is exactly the copy of a given table then we can also use the following Sql query

SELECT * INTO tbl_studentscopy FROM tbl_students

The table tbl_studentscopy created by the above Sql query will contains exactly the same data as tbl_students. This can be verified with the help of the query given below:-

SELECT * FROM tbl_studentscopy

Result:-










There is another interesting way to insert the values into a table with the help of another table. For example, suppose we have a table named as tbl_Studentsdemo whose structure is given below:-

CREATE TABLE tbl_Studentsdemo( [Studentid] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](200) ,
[Lastname] [nvarchar](200) ,
[Email] [nvarchar](100)
)
Now if we want to insert values into the table from the table tbl_students, then we can use the following sql query to insert the data into the table.

Insert into tbl_Studentsdemo(Firstname,lastname,email)
SELECT Firstname,lastname,email FROM tbl_students

The above query will insert all the data from the table tbl_students into the table tbl_Studentsdemo. Again we can verified it with the help of the given query:-

SELECT * FROM tbl_Studentsdemo

Result:-

DMCA.com

Monday, October 5, 2009

Difference between Primary key and Unique key



Both Primary key and Unique key enforce uniqueness of the column on which they are defined but still there are some differences between them. The differences are defined below:-

1)There can be only one Primary key possible in a table but there can be many unique keys possible in a table.

Suppose there is a table named tbl_primarykey whose structure is given below:-

CREATE TABLE tbl_primarykey (studentid INT NOT NULL PRIMARY KEY, Studentname nvarchar(255), email nvarchar(50), PhoneNo nvarchar(10))

Here studentid is defined as the primary key. Now since primary key is already defined on the column studentid, no other primary key can be defined on the table tbl_primarykey

Again, suppose there is another table named tbl_UniqueIndex whose structure is given below:-

CREATE TABLE tbl_UniqueIndex(studentid INT NOT NULL UNIQUE , Studentname nvarchar(255), email nvarchar(50), PhoneNo nvarchar(10))

Here Unique constraint is defined on the column studentid. We can also add another unique constraint on another column studentname of the table tbl_UniqueIndex.

ALTER TABLE tbl_UniqueIndex ADD CONSTRAINT Const_Unique UNIQUE(studentname)

The above query shows that there can be multiple unique constraints can be defined on a single table but on a single table only one primary key can be defined.

2)Primary key do not allow NULL values but a Unique key allow one NULL value.If we try to insert NULL value into the primary key, we get an error message. 

For example, if we try to insert NULL value into the column studentid of the table tbl_primarykey, we get the following error:-

INSERT INTO tbl_primarykey (studentid,Studentname,email)
VALUES(NULL,'Vivek' ,'vivek@mail.com')

Error:-

 


On the other hand if we want to insert NULL values in the column on which Unique constraint is defined we can do it.

For example, we can insert the null value in the column studentname of the table tbl_UniqueIndex.

INSERT INTO tbl_UniqueIndex (studentid,Studentname,email)
VALUES(1,NULL,'abc@mail.com')

Note:- We can only insert one NULL value inside the column on which a Unique Constraint is defined.

For example, if we try to insert more than one NULL value in the column Studentname of the table tbl_UniqueIndex, we get the following error:-

INSERT INTO tbl_UniqueIndex (studentid,Studentname,email)
VALUES(2,NULL,'virender@mail.com')

Error:-




3)When a Primary key is created, a clustered index is made by default but if an Unique key is created, a non-clustered index is created by default.

If we check all the indexes made on the table tbl_primarykey and tbl_UniqueIndex, then we can found out that a clustered index is created by default when a Primary key is created and a Non_Clustered Index is created by default when a Unique Constraint is created on the table.

To check the indexes made on the table tbl_primarykey, we can use the following query:-

sp_helpindex tbl_primarykey




To check the indexes made on the table tbl_UniqueIndex, we can use the following query:-

sp_helpindex tbl_UniqueIndex





You can also get more information about Primary Key and Unique Constraint from the link given below:-



DMCA.com

Tuesday, September 29, 2009

Difference between Subquery, Nested Subquery and Correlated Subquery

-->

Query: - Query can be defined as a way to inquire the data from the database. It is used to extract the data from one table or multiple tables depending upon the user needs.

Suppose we have a two tables Student and courses whose structure is given below:-

create table Student (Studentid int identity(1,1), Firstname nvarchar(200), Lastname nvarchar(200),Email nvarchar(100))

create table Course (Courseid int identity(1,1), Coursename nvarchar(250), CourseAdmin int)

Now suppose we insert the following data into these tables:-

For table Student

insert into Student values ('Atul','Bajaj', 'atul@abc.com' )

insert into Student values ('Vivek','Johari', 'vivek@abc.com' )

insert into Student values ('Ankur','Johari', 'ankur@abc.com' )

insert into Student values ('Tarveen', 'Kaur', 'Tarveen@abc.com')

For table Course

Insert into Course values('Oracle',2)

Insert into Course values('Automation',4)

Insert into Course values('Java',2)

Insert into Course values('QTP',4)

Now the query to see all the data from the table student and course is given below:-

Select * from student









Select * from Course








Subquery:-If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.

For example, if we want to find the name of the course Admin of the course “Oracle”, then the following subquery will be used:-

select Firstname+' '+Lastname from student where studentid in (select courseadminid from course where coursename ='Oracle')

Result:-





In this example, the sql statement select courseadminid from course where coursename ='Oracle'is a subquery.

Nested Subquery:-If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.

Let us suppose we have another table called “StudentCourse” which contains the information, which student is connected to which Course. The structure of the table is:-

create table StudentCourse( StudentCourseid int identity(1,1), Studentid int, Courseid int)

The Query to insert data into the table “Studentcourse” is

Insert into StudentCourse values(1,3)
Insert into StudentCourse values(2,1)
Insert into StudentCourse values(3,2)
Insert into StudentCourse values(4,4)

Note: - We don’t need to insert data for the column StudentCourseid since it is an identity column.

Now, if we want to get the list of all the student which belong to the course “Oracle”, then the query will be,

select Firstname, lastname from student where studentid in (select studentid from studentcourse where courseid in (select courseid from course where coursename='Oracle'))

Result:-





In this example we use the nested subquery since the subquery “select courseid from course where coursename='Oracle'” is itself contained in the another subquery(Parent Subquery) “select studentid from studentcourse where courseid in (select courseid from course where coursename='Oracle')”.

Correlated Subquery:-If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.

Suppose we want to get the details of the Courses (including the name of their course admin) from the Course table, we can use the following query:-

select Coursename ,Courseadminid,(select Firstname+' '+Lastname  from student where studentid=Course.courseadminid)as CourseAdminName from course

Result:-








Here in this example the “select Firstname+' '+Lastname  from student where studentid=Course.courseadminid” is called the correlated subquery since the outcome of this subquery is depends on the column courseadminid of the parent query. This means that the correlated subquery will be executed for each row selected by the parent query.

It is not necessary that the column on which the correlated query is depended is included in the selected columns list of the parent query. For example the   below query will also works even the column courseadminid on which the correlated query is depends , is not included in the selected columns list of the parent query.

select Coursename ,(select Firstname+' '+Lastname  from student where studentid=Course.courseadminid)as CourseAdminName from course

Results:-



DMCA.com

Wednesday, August 19, 2009

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.





DMCA.com