SQL Server - Creating Index

Index is a database object created on a table to arrange records in such a way that the data can be searched quickly and efficiently. The table with index is best suitable when there is frequent search against the table. If the table is updated or new records are inserted frequently, the existence of Index will slow down this process. So it is not advisable to use Index on those kind of tables. There are different types Indices that can be created in SQL Server. Some of the options and types of Indices are explained below.

UNIQUE

Creates a unique index on a table or view. In case of UNIQUE index no two records are allowed to have same value on the fields the index is created upon. A clustered index on a view must be unique. UNIQUE Index cannot be created, if the table contains duplicate value even if the IGNORE_DUP_KEY property is set to ON. Only columns with NOT NULL constraint is allowed to be part of Unique Index columns. Multiple NULL values are considered as duplicate.

CLUSTERED

Creates an index which orders the key values physically as it is ordered logically. Only one clustered index is allowed for a table or view. Creating a unique clustered index on a view physically materializes the view. When we create unique Clustered Index on a view, it should be created before any other index is created on the view. Clustered Index should be created before creating any Non-Clustered indices. The word CLUSTERED is used to create the Clustered index, absence of the word will create a Non-Clustered Index. A view with a unique clustered index is called an indexed view.

NONCLUSTERED

Creates an index that specifies the logical ordering of a table. With a non-clustered index,  the records are ordered logically, not physically using index tables. The index keys are ordered as in the logical order specified and physical order is independent of logical order. A table can have maximum 999 Non-Clustered Indexes. Indexes can be created implicitly with PRIMARY KEY or UNIQUE or explicitly with CREATE INDEX.

In simple words, Clustered Indexes orders the records physically. Non-Clustered Indexes orders records logically. Below is the syntax for creating the indexes.

The difference between above 3 are: Unique index decides whether the key values are unique are not. Unique index allows only one NULL value in the key column whereas the Clustered allows multiple NULL values in key columns. Clustered and Non-clustered decides whether to organize records physically or logically.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX Index_Name 

    ON Table_Name ( column_1 [ ASC | DESC ] [ ,...n ] ) 

    [ WHERE <Filter_Condition> ]

Example 1:

CREATE UNIQUE INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

Example 1 creates a Unique Index on the table Emp_Tab.

Example 2:

CREATE CLUSTERED INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

Example 2 creates a Clustered Index on the table Emp_Tab and physically order the records on the basis of emp_id.

Example 3:

CREATE INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

or

CREATE NONCLUSTERED INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

Example 3 creates a Non-Clustered Index on the table Emp_Tab and order the records on the basis of emp_id logically.

Thanks for reading this post !

Read More

MS SQL Server - How to get the last change date of table data and schema?

Hi Everyone,

Do you need to know when was the last change (Insert, Update or Delete) happened in the SQL Server table data and the table meta data (schema change)?

If you are searching for answers for the above questions, here I am with the answers.

Script to find the last schema change date of a table, you will need to supply table name in the where clause. Run this query in the target database.

SELECT

NAME AS TABLENAME ,

MODIFY_DATE AS LAST_SCHEMA_CHANGE

FROM    SYS.OBJECTS

WHERE   NAME = ‘<TABLE>’

Script to find when the table data was last changed, here you will need to specify the database name and the table number. You may require admin privilege to run this query.

SELECT  

OBJECT_NAME(OBJECT_ID) AS TABLENAME ,

LAST_USER_UPDATE ,

USER_UPDATES ,

INDEX_ID

FROM    SYS.DM_DB_INDEX_USAGE_STATS

WHERE   DATABASE_ID = DB_ID(‘<DATABASENAME>’)

AND OBJECT_ID = OBJECT_ID(‘<TABLENAME>’)

Hope this helped you, Thanks for reading this article !

Read More

SQL Server - How to delete duplicate rows from an SQL Server Table?

Hi Everyone,

Here is the example, which illustrates how to delete the duplicate records in SQL Server?

--Create a new table EMP

CREATE TABLE EMP (EMPID INT, NAME VARCHAR(100), SALARY INT)

GO

--Insert records to the table

INSERT INTO EMP (EMPID, NAME,SALARY)

SELECT 1, 'JOHN',2000

UNION ALL

SELECT 1, 'JOHN',2000

UNION ALL

SELECT 1, 'JOHN',2000

UNION ALL

SELECT 2, 'JOHN',2000

UNION ALL

SELECT 3, 'JOHN',2000

UNION ALL

SELECT 4, 'DAVID',3000

UNION ALL

SELECT 5, 'CHARLIE',4000

UNION ALL

SELECT 6, 'HARRY',2000

GO

In this example, I want to eliminate repeated values in the NAME and SALARY columns.

--Selecting the Data

SELECT * FROM EMP

GO

--Here is the solution

WITH EMP_ROW_NUM AS

(

  SELECT

    ROW_NUM = ROW_NUMBER() OVER( PARTITION BY NAME, SALARY

    ORDER BY NAME, SALARY ), *

  FROM EMP

)

DELETE FROM EMP_ROW_NUM WHERE ROW_NUM>1

You must specify the column names that you expect duplicate values in the PARTITION BY clause. In this example, NAME and SALARY.

--Dropping the table

DROP TABLE EMP

GO

Please share this, if this helps you. Thanks for reading my Blog !

Read More

Microsoft SQL Server - Query to find the record counts of tables

Hi Everyone,

Use the below query to find the record count of tables.

SELECT

SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMA_NAME,

T.NAME AS TABLE_NAME,

SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME AS FULL_TABLE_NAME,

I.ROWS

FROM

SYS.TABLES AS T INNER JOIN SYS.SYSINDEXES AS I

ON T.OBJECT_ID = I.ID AND I.INDID < 2

WHERE

SCHEMA_NAME(T.SCHEMA_ID)='DBO'

AND T.NAME IN ('Table1','Table2','Table3','TableN')

Thanks for reading my Blog !

Read More

Microsoft SQL Server - Query to fetch Table Meta Data

Use the below query to fetch table metadata in Microsoft SQL Server.

SELECT

TABLE_SCHEMA,

TABLE_NAME,

ORDINAL_POSITION,

COLUMN_NAME,

CASE DATA_TYPE

WHEN 'DATETIME' THEN UPPER(DATA_TYPE)

ELSE

UPPER(DATA_TYPE)+'('+LTRIM(RTRIM(DATA_LENGTH))+')'

END AS DATA_TYPE,

NULLABLE

FROM

(

SELECT

COLS.TABLE_SCHEMA,

COLS.TABLE_NAME,

COLS.COLUMN_NAME,

COLS.ORDINAL_POSITION,

COLS.DATA_TYPE,

CASE COLS.DATA_TYPE

  WHEN 'CHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'NCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'VARCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'NVARCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'SMALLINT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'INT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'BIGINT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'DECIMAL' THEN RTRIM(CONVERT(CHAR,COLS.NUMERIC_PRECISION)) + ',' +RTRIM(CONVERT(CHAR,COLS.NUMERIC_SCALE))

ELSE

NULL

END AS DATA_LENGTH,

CASE COLS.IS_NULLABLE

WHEN 'NO' THEN 'NOT NULL'

WHEN 'YES' THEN 'NULL'

END AS NULLABLE

FROM

INFORMATION_SCHEMA.COLUMNS COLS

WHERE COLS.TABLE_SCHEMA = 'DBO'

AND COLS.TABLE_NAME = 'EMPLOYEE'

)A

ORDER BY  TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME

Can also use IN Clause for COLS.TABLE_NAME to include multiple tables.

 Thanks for reading my Blog !

Read More

Microsoft SQL Server - Reset the Identity Column Sequence Number.

Here is how we reset the Identity column to start from 1 run the below query.

Syntax:

DBCC CHECKIDENT('<Table_Name>', RESEED, 0)

Example:

DBCC CHECKIDENT('Customer', RESEED, 0)

The above statement will reset the Identity column of the Customer Table to 0, so that when a new record is inserted the identity column value will be 1.

Thanks for reading my Blog !

Read More