Single row subquery

       In Single row subquery, inner query returns 'Zero or One' row. Based on the inner query result outer query will return output. The operators that can be used with single-row subqueires are =, >, >=, <, <=, and <>.

Example:

           Employee table

           Department table

SELECT * FROM EMPLOYEE

WHERE Dept_ID =

(

      SELECT

      DEPTID

      FROM DEPT

      WHERE DeptID = 10

)

Rename Column

    Using SP_RENAME we can rename columns.

Rename Column:
Syntax:
SP_RENAME '<Database name>.<Schema name>.<Table Name>.<Column Name>','<New Name>'

Example:
Table Creation:

CREATE TABLE SAMPLE14

(

ID INT ,

NAME VARCHAR(10),

TYPE_DESC VARCHAR(50)

)

Renaming Table:

SP_RENAME 'KIRAN.DBO.SAMPLE14.ID','EMP_ID'

Rename table

    Using SP_RENAME we can rename SQL Server data base objects (Tables, Views, Functions and Procedures). Using SP_RENAME we can rename columns also.

Rename table:
Syntax:
SP_RENAME '<Database name>.<Schema name>.<Table Name>','<New Name>'

Example:
Table Creation:

CREATE TABLE SAMPLE13

(

ID INT ,

NAME VARCHAR(10),

TYPE_DESC VARCHAR(50)

)

Renaming Table:

SP_RENAME 'KIRAN.DBO.SAMPLE13','TEST_TBL'

Indexes

     Using Indexes we can improve query performance. SQL Server has three major types of indexes.

• CLUSTERED
• NONCLUSTERED
• UNIQUE

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: