Multi row subquery

       In Multi row subquery, inner query returns 'One or More' row. Based on the inner query result outer query will return output.

Example:

           Employee table

           Department table

SELECT * FROM EMPLOYEE

WHERE Dept_ID IN

(

      SELECT

      DEPTID

      FROM DEPT

)

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: