indexing
01.
Clustered Index
It is stored as a B-tree ( B+ tree to be specific)
It defines how the data is stored.
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
It has a same B-tree structure as clustered index.
It does not determine the ordering of the actual data pages.
Each index entry contains
the value
row indicator which points to the actual row or clustered index reference which points to the row.
03.
Primary Key
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.
It creates a clustered key by default.
A clustered key is not created if specified as NONCLUSTERED KEY or
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')