Types Of Index And How To create Index On Sql Server

INDEXES

Indexes in SQL server is similar to index in text book.. Indexes are used to improve the performance of queries.

INDEXES ARE GENERALLY CREATED FOR FOLLOWING COLUMNS

Primary key column

Foreign key column:  frequently used in join conditions.

Column which are frequently used in where clause

Columns, which are used to retrieve the data in sorting order.

INDEXED CANNOT BE CREATED FOR FOLLOWING COLUMNS:

The columns which are not used frequently used in where clause.

Columns containing the duplicate and null values

Columns containing images, binary information, and text information.

TYPES OF INDEXES:

·         CLUSTERED INDEX

·         NON-CLUSTERED INDEX

CLUSTERED INDEX: only one clustered index is allowed per table. The order of values in a table order of values in index is also same. When cluster index is created on table data is arranged in ascending order cluster index will occupy 5% of the table.

Syntax:

CREATE [UNIQUE] CLUSTERED INDEX INDEXNAME ON TABLENAME (COLUMN)

E.g.:

CREATE CLUSTERED INDEX CI ON EMP (EMPNO)

Note: if we want to maintain unique values in clustered/non clustered indexed column then specify UNIQUE keyword along with CLUSTERED INDEX/NONCLUSTERD INDEX

NONCLUSTERED INDEX: It is the default index created by the server the physical order of the data in the table is different from the order of the values in index.

Max no. Of non-clustered indexed allowed for table is 249

Syntax:

CREATE [UNIQUE] NONCLUSTERED INDEX INDEXNAME

 ON TABLENAME ( COLUMN1,…)

E.g.:

CREATE NONCLUSTERED INDEX NCI ON EMP (ENAME, SAL)

Ex:

 CREATE UNIQUE NONCLUSTERED  INDEX UI ON DEPT (DNAME)

COMPOSITE INDEX: If a Unique NonClustered index is created on more than one column then that concept  is called composite index.

CREATE UNIQUE NONCLUSTERED  INDEX  COI ON DEPT (DEPTNO, DNAME)

DEPTNO       DNAME

10              SALES

20              HR

30              IR

10              HR (Accepted)

20              SALES (Accepted)

30              IR (Repeated, Not accepted)

SP_HELPINDEX: This stored procedure is used to display the list of indexes, which have been placed on different columns of a specific table.

E.g.: SP_HELPINDEX   EMP

Syntax to drop the index:

DROP INDEX TABLENAME.INDEX.NAME

E.g.:

DROP INDEX DEPT.UI

6.      User defined function are introduced.

7.      OLAP (online analytical process) services available in SQL server 7.0 are now called as SQL server 2005 analysis services

8.      Repository components available in SQL server now called Meta data services.

Requirements To Install SQL Server 2005 in your work station

• Operating System: Windows Xp, Windows 2000/NT/Professional,Vista
• Primary Memory:  512 MB Ram or above
• Secondary memory: 1GB or above
• Processor: 500 Mhz or above
• Internet explorer: 5.0 or above
• Windows Installer 3.1
• .Net Framework 2.o

 SP_HELPTEXT  ‘TRIGGER_NAME’

                        SP_HELPTEXT  ‘T1’

DDL TRIGGERS (2005 Triggers):

These triggers are fired when user performs DDL operations in the database and these triggers belong to database. It  means we can define triggers on the current database.

Syntax:

CREATE TRIGGER TRIGGERNAME ON DATABASE

FOR/ AFTER {DROP,/ALTER/ CREATE}

 AS

BEGIN

 SQL STATEMENT.

END

E.g.:

USE PUBS

CREATE TRIGGER DROP_TRG ON DATABASE FOR DROP-TABLE

AS

BEGIN

PRINT ‘TABLE DROPPED’

ROLLBACK

END

E.g.:

USE PUBS

CREATE TRIGGER DROP_TRG ON DATABASE  INSTEAD OF DROP-TABLE

AS

BEGIN

PRINT ‘U CANNOT DROP THE TABLE’

ROLLBACK

END