Unique Index

      If we create a Unique index on a table. Index will not allow duplicate values.

Example:

CREATE TABLE SAMPLE12

(

ID INT ,

NAME VARCHAR(10),

TYPE_DESC VARCHAR(50)

)

Syntax:

CREATE UNIQUE INDEX <Index Name> ON <Table Name> (<Column Name>)

Creating index on single column:

CREATE UNIQUE INDEX IDX_SAMPLE12_UQ ON SAMPLE12 (ID)

Result:

Drop Index:

DROP INDEX SAMPLE12.IDX_SAMPLE12_UQ

Result:

Creating index on multiple column:

CREATE UNIQUE INDEX IDX_SAMPLE12_UQ ON SAMPLE12 (ID,NAME)

Result:

Drop Index:

DROP INDEX SAMPLE12.IDX_SAMPLE12_UQ

Result:

Nonclustered Index

Non Clustered will not sort the physical order of the table. Each index row in the nonclustered index contains the nonclustered key value and a row locator. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

Example:

CREATE TABLE SAMPLE11

(

ID INT ,

NAME VARCHAR(10),

TYPE_DESC VARCHAR(50)

)

Creating index on single column:

CREATE NONCLUSTERED INDEX IDX_SAMPLE11_NONCLU ON SAMPLE11 (ID)

Result:

Drop Index:

DROP INDEX SAMPLE11.IDX_SAMPLE11_NONCLU

Result:

Creating index on multiple column:

CREATE NONCLUSTERED INDEX IDX_SAMPLE11_NONCLU ON SAMPLE11 (ID,NAME)

Result:

Drop Index:

DROP INDEX SAMPLE11.IDX_SAMPLE11_NONCLU

Result:

Clustred Index

A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

Example:

      CREATE TABLE SAMPLE10

      (

            ID INT ,

            NAME VARCHAR(10),

            TYPE_DESC VARCHAR(50)

      )

Syntax:

CREATE <Index type> INDEX <Index name> ON <Table name> (Column1,Column2,...)

Creating index on single column:

CREATE CLUSTERED INDEX IDX_SAMPLE10_CLU ON SAMPLE10 (ID)

Result:

Drop Index:

DROP INDEX SAMPLE10.IDX_SAMPLE10_CLU

Result:

Creating index on multiple column:

CREATE CLUSTERED INDEX IDX_SAMPLE10_CLU ON SAMPLE10 (ID,NAME)

            Result:

Drop Index:

DROP INDEX SAMPLE10.IDX_SAMPLE10_CLU

Result:

Encrypting Views

      Using 'SP_HELPTEXT' (SP_HELPTEXT <View name>)we can see the view code. If we create a view with "WITH ENCRYPTION" then we can't see the view code.

Example:

Sample Employee Table creation with data

Sample Department table creation with data

View Creation Script:

CREATE VIEW EMPLOYEE4

WITH ENCRYPTION

AS

SELECT A.ID

      ,A.FirstName

      ,A.LastName

      ,A.Gender

      ,A.Dept_ID

      ,B.Name

      ,B.Location

FROM EMPLOYEE A

JOIN DEPT B 

ON A.Dept_ID = B.DeptID

Checking view source code: 
     If we try to view the view code it will through the message.

SP_HELPTEXT EMPLOYEE4

WITH CHECK OPTION

Creating a View without "WITH CHECK OPTION":

        We created a view with filter condition. If it is a Simple view then we can perform DML operations.

Example:

Employee table script

View creation script:

CREATE VIEW VW_EMPLOYEE2

AS

SELECT

ID

,FirstName

,LastName

,Gender

,Designation

,ManagerID

,Dept_ID

FROM EMPLOYEE

WHERE Dept_ID = 10

View output:

The view will display department 10 data only, because view is created with where condition.

 

    

Inserting data into 'VW_EMPLOYEE2':

I am going to insert two records one is with department id 10 other one is 20.

Insert stmt:

INSERT INTO VW_EMPLOYEE2

(ID,FirstName,LastName,Gender,Designation,ManagerID,Dept_ID)

VALUES (112,'A','Z','M',NULL,NULL,10),

(113,'B','Y','F',NULL,NULL,20)

View output:

We can see only 10th department data using view, because view is having where condition. We can see the newly inserted records in the table.

 

Table output:

Creating a View with "WITH CHECK OPTION":

        We created a view with filter condition and 'WITH CHECK OPTION'. If it is a Simple view then we can  insert the data. The record must satisfied the filter condition, other wise it will throw error.

Example:

View creation script:

CREATE VIEW VW_EMPLOYEE3

AS

SELECT

                ID

                ,FirstName

                ,LastName

                ,Gender

                ,Designation

                ,ManagerID

                ,Dept_ID

FROM EMPLOYEE

WHERE Dept_ID = 10 

WITH CHECK OPTION

Insert stmt:

INSERT INTO VW_EMPLOYEE3

(ID,FirstName,LastName,Gender,Designation,ManagerID,Dept_ID)

VALUES (113,'B','Y','F',NULL,NULL,20) 

Error msg:

                                                Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

SCHEMABINDING a VIEW

Without Schemabinding:

          If we create a view without schemabinding, then we can drop the tables which are used in the view. If we drop the table(s) then view will not work.

Example:
     Employee table script

     View creation script:

CREATE VIEW VW_EMPLOYEE

 AS

 SELECT

   ID

   ,FirstName

   ,LastName

   ,Gender

   ,Designation

   ,ManagerID

   ,Dept_ID

       FROM EMPLOYEE

     We can drop the table which is used in the view.

DROP TABLE EMPLOYEE

     If we try to select the view then it will through error.

With Schemabinding:

          If we create a view without schemabinding, then we cannot drop the tables which are used in the view. If we try to drop the table(s) then it will throw error.

Example:
     Employee table script

     View creation script:

CREATE VIEW VW_EMPLOYEE1

WITH SCHEMABINDING

AS

 SELECT

   ID

   ,FirstName

   ,LastName

   ,Gender

   ,Designation

   ,ManagerID

   ,Dept_ID

       FROM DBO.EMPLOYEE

     We canot drop the table which is used in the view.
       DROP TABLE EMPLOYEE