indexing

01.

Clustered Index

    1. It is stored as a B-tree ( B+ tree to be specific)

    2. It defines how the data is stored.

    3. The data is stored in the leaf nodes of the tree while the intermedia nodes help in navigation (tranversing) data through the tree.

CREATE CLUSTERED INDEX 'NAMEOFINDEX' ON 'TABLENAME'

('COLUMNNAME');

CREATE CLUSTERED INDEX 'NAMEOFINDEX' ON 'TABLENAME'

('COLUMNNAME-01' ASC , 'COLUMNNAME-02);

02.

Non Clustered Index

    1. It has a same B-tree structure as clustered index.

    2. It does not determine the ordering of the actual data pages.

    3. Each index entry contains

      1. the value

      2. row indicator which points to the actual row or clustered index reference which points to the row.

03.

Primary Key

    1. It creates a unique key on the column. If the unique key is not specified then a unique key is generated internally using a 4 byte data.

    2. It creates a clustered key by default.

      1. A clustered key is not created if specified as NONCLUSTERED KEY or

      2. When a clustered key already exists.

ALTER TABLE 'TABLENAME' ADD CONSTRAINT 'KEY-NAME'

PRIMARY KEY ('COLUMN-NAME' ASC)

ALTER TABLE 'TABLENAME' ADD CONSTRAINT 'KEY-NAME'

PRIMARY KEY NONCLUSTERED ('COLUMN-NAME' ASC)

Other Important Definitions in SQL server

Page : 8K data set.

Extend : Collection of 8 pages. Pages can be of the same table or multiple extends.

Heap : Collection of Extends where the pages are arranged in a specific manner.

Fill Factor : Space reserved for expansion of the row.

Following helps to get the name of the indexes applied on a table

SELECT

OBJECT_NAME(object_id)

ISNULL(name, OBJECT_NAME(object_id) IndexName,

Index_Id,

type_desc,

From sys.indexes

WHERE OBJECT_NAME(object_id) = 'Indexing'

Following helps to get the actual B-tree structure of the index used for a table

SELECT

OBJECT_NAME(object_id) Name,

index_type_desc AS INDEX_TYPE

alloc_unit_type_desc AS DATA_TYPE

index_id AS INDEX_ID

index_depth AS DEPTH

index_level AS IND_LEVEL

record_count AS RECORD_COUNT

page_count AS PAGE_COUNT

fragment_count AS FRAGMENT_COUNT

FROM

sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('Indexing')