Please enable JavaScript.
Coggle requires JavaScript to display documents.
(Index)Data stored in a Database - Coggle Diagram
(Index)Data stored in a Database
Sql server uses data page to store data.
each data page is 8Kb
8 data pages we stored under a logical container known as “extend”.
How Database Engine Retrieves
Full page scan
SQL Server will search for the required information in each and every data page to collect the information.
Index Scan
SQL Server without searching into each and every data page for retrieving the information
Index
Whenever an index is created on a column or columns of a table internally an index table gets created maintaining the information of a column on which the index is created as well as the address (pointer to the row corresponding to a column).
Indexes are created on tables and views
Types of Indexes
Clustered Index
A clustered index, the arrangement of the data in the index table will be the same as the arrangement of the data of the actual table.
When a table has a clustered index then the table is called a clustered table.
A table can have only one clustered index in it which will be created when the primary key constraint used in a table.
A clustered index determines the physical order of data in a table
Non-Clustered Index
A non-clustered index, the arrangement of data in the index table will be different from the arrangement of the actual table.
The data is stored in one place, the index is another place. The index will have pointers to the storage location of the data. We can have more than one non-Clustered Index
we can create a maximum of 249 non clustered indexes
the data is stored in an ascending or descending order of the index key which does not in any way influence the storage of data in the table
A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
Unique Index
If the index is created by using the “UNIQUE” option then that column on which the index is created will not allow duplicate values. Unique index is used to enforce uniqueness of key values in the index
Differences between UNIQUE Constraints and the UNIQUE Index
when we add a unique constraint, a unique index gets created behind the scenes
ALTER TABLE tblEmployees ADD CONSTRAINT UQ_tblEmplyees_City UNIQUE (city)
It will create a UNIQUE non clustered index on the City column.
ALTER TABLE tblEmployees ADD CONSTRAINT UQ_tblEmplyees_City UNIQUE CLUSTERED (city)
It will create a clustered index on the City column.