How many types of indexes in sql server 2005,2008:

Why do we  use  indexes:

 To get the high performence index are used in sql server database.Indexes can use to get the information quickly,Like we used indexes in books to get information quickly.Indexes are created on columns in tables or view.The index provides a fast way to look up data in table and database.Index creation in table helps us to query optimization and faster access of data.

Types of Indexes:

There are different  types of indexes you can create in sql server database.The indexes are given below:

1:Clustered

2:Nonclustered

3:Unique

4:Index with included columns

5:Indexed views

 

1:Clustered Index:

A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key.We can have only 1 clustered index in a table.Clustered index is applied on primary key.In case of clustered index , indexed values are sorted in either ascending or descending order.

2: Nonclustered Index:

A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator.We can have max 249 Nonclustered index in a table.

3:Unique Index:

A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.

Both clustered and nonclustered indexes can be unique.

4:Index with included columns:

A nonclustered index that is extended to include nonkey columns in addition to the key columns.

5:Indexed views:

A index in view is called Indexed view.We can add index on view.The view and result set is permanently stored in a unique clustered index in the same way a table with a clustered index is stored. Nonclustered indexes on the view can be added after the clustered index is created.

 

How to create trigger in sql server 2005,2008:

A trigger is  a database object which is associated with table.A trigger is automatically executed whenever a insert, update and delete operation is performed in table.Triggers are also know as 'special  store procedure' but   they are  associated with table's    only .

Syntax of trigger creation in sql server 2005,2008 is given below:

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
END

Exapmle: 

 Approach 1:Trigger for  insert

Create  a new table in which you have to apply a trigger:

CREATE TABLE Class_Master (Student_ID int IDENTITY, SName varchar(100),Age int,Class int)

Create trigger for this table:

CREATE TRIGGER tr_Class_Master_INSERT
ON Class_Master
FOR INSERT
AS
declare @class varchar(50)
select @class=Class from inserted

IF (@class>=9 and @class<=10) 
 BEGIN
   insert into TblHighSchool
    select * from inserted
 END
Else IF(@class>10 and @class<=12)
    Insert into TblSeniorHighSecondry
    select * from inserted
else
    Insert into TblJunior
    select * from inserted

In the above table we are inserting the student information in a table  Class_Master and on new data insert we are checking class in a trigger and then as per student class we are placing them in other tables.

Approach 2:Trigger for Delete

In below example we are deleting the records from child tables when the data is deleted from Class_master table

CREATE TRIGGER tr_Class_Master_Delete
ON Class_Master
FOR Delete
AS
declare @class varchar(50),@Student_ID int
select @class=Class,@Student_ID=Student_ID from deleted

 IF (@class>=9 and @class<=10) 
 BEGIN
    delete from TblHighSchool where Student_ID=@Student_ID
   
 END
 Else IF(@class>10 and @class<=12)
    delete from TblSeniorHighSecondry where Student_ID=@Student_ID  

 else
    delete from TblJunior where Student_ID=@Student_ID 

Approach 3:Trigger for Update:

how to   use update trigger in sql server 2005,2008:
Sql server update trigger: 

In below example we are updating the age of child table if age is updated in master table 'Class_master'.

CREATE TRIGGER tr_Class_Master_Update
ON Class_Master
AFTER Update
AS
declare @age int ,@Student_ID int,@class varchar(50)
select @age=age,@Student_ID=Student_ID,@class=class from inserted

 IF (@class>=9 and @class<=10) 
 BEGIN
     update  TblHighSchool set age=@age where Student_ID=@Student_ID
   
 END
 Else IF(@class>10 and @class<=12)
    update TblSeniorHighSecondry set age=@age where Student_ID=@Student_ID   

 else
    update   TblJunior set age=@age where Student_ID=@Student_ID


So above examples shows that how to use trigger for insert ,update and delete query.These automatically fire whenever a insert ,update or delete statement is performed in table.
   

What is the difference between sp(stored procedure) and function?

functions and sp(stored procedures) are different from each other in many ways(return type,table variable etc.) .Difference between sp and functions is given below.

Difference between sp and functions:

1:Return Value:

The first difference between sp(stored procedure ) and function  is return value .stored procedures may or may not return a value but functions always returns values.

2:Return type Table variable  :

Stored procedure never have a table variable return type in sql server 2005 but present in sql server 2008 .Function can have a table  return type .In case of inline table valued functions it always returns a table variable.

3:Inline Query:

We can call a function from  within a stored procedure .

We cannot call sp(stored procedure) from within a function. Only functions and extended stored procedures can be executed from within a function.

extended stored procedures:

Extended stored procedures are stored procedures that call functions from DLL files.but they often causes memory leak.

Create Function in sql server  2005,2008:

In sql server 2005 you can create two types of functions:

1. Inline Table valued function:
1. If you want return  value as a table then use this function.
2. scalar valued function:
1. If you want return type as any data type then use this function.

1:Create Inline Table valued function:

the syntax of inline table values function is given below:

 CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(  
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)

Examples:

Approach 1:

This is a simple example of function returns  table

CREATE FUNCTION Fun_MyFirstInline
(   
    @Student_Id int
)
RETURNS TABLE
AS
RETURN
     select Student_name,Student_class from dbo.student where Student_Id =@Student_Id

Approach 2:

In this exapmle we are cheking student info if not 'Student Does not Exists' message will pass in table and we have created a table  variable  @studentInfo .

CREATE  FUNCTION Fun_MyFirstInline
(   
    @Student_Id int
)
RETURNS @studentInfo TABLE (name varchar(50),class varchar(50))
AS
begin
 
        --Insert student info into table variable @studentInfo

        insert into @studentInfo
        select   Student_name,Student_class from dbo.student where Student_Id =@Student_Id
        
--Check if any records are present is table variable @studentInfo
        declare @count int
        select @count=0
        select @count=isnull(count(*),0) from @studentInfo

 --If Records are not present in table insert a error message into the table variable
        if @count=0
        insert into @studentInfo
        select   '','Student Does not Exists' 

RETURN
end   

2:Create scalar valued function:

creating scalar valued function is more easy than inline table valued function.

syntax for scalar valued function is given below: 

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END

Examples:

Approach 1:

This is a simple example of scalar valued function creation  

CREATE FUNCTION [DBO].[GETORDERLINEPROPERTY]
(
    @STUDENT_ID INT
)       
RETURNS  NVARCHAR(MAX)
AS       
BEGIN 
   
    DECLARE @MARKS NVARCHAR(MAX)
    SET @MARKS =(SELECT MARKS FROM DBO.STUDENT_REPORT WHERE STUDENT_ID =@STUDENT_ID)
    RETURN @MARKS

END 
 

How to Create New Database in sql server 2005,2008:

To create new database in sql server 2005,2008 open sql server management studio and follow the steps given below:

Step1:

Right click on database and select New database:

Step2:

A new database creation window will open.In this window enter the database name you want to create  against database name  section.Once database name is done click on add button .You have successfully created your first database .

In next section we will see how to create user for new database and security related settings in new database.