About Lesson
/*
Index:
types of indexes in SQL Server
1. Clustered:
1.1 Determines the physical order of data in a table.
1.2 Primary key, constraint create clustered indexes automatically.
2. Nonclustered
2.1 Non Cluster Index store in DBMS Software as an array of pointers – it keeps the locations (hold the addresses) of a record value in a database
2.2 nonclustered index is stored separately from the actual data.
2.3 Syntax: Create NonClustered Index IndexName on tblemp(column)
3. Unique
4. Filtered
5. XML
6. Full Text
7. Spatial
8. Columnstore
9. Index with included columns
10. Index on computed columns
To check the index information call the stored procedure- syntax: sp_helpindex tblname
To drop the index- syntax: Drop index tblName.IndexName
To create the index – syntax: Create Clustered Index IndexName_AccordingPatern on tblName(col order, col order, …)
example: Create Clustered Index IX_tblEmployee_Gender_Salary on tblemp(job asc, sal desc)
*/
sp_helpindex tblemp;