Index in sql server

Index in sql server

• 1.
  Index is adatabase object, which can be created on one or more columns (16 Max column combinations). When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify. So it depends on how much data retrieval can be performed on table versus how much of DML (Insert, Delete and Update) operations. In short:- Index is basically use for fast data retrieval from database. Example: For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers. Type of Index: In SQL Server database there are mainly two types of indexes, 1. Clustered index and 2. Non Clustered index 1. Clustered Index In simple way- Primary key is Clustered index. 2. Non Clustered Index Unique key is Non-Clustered index.
• 2.
  Syntax to createIndex The basic syntax of CREATE INDEX is as follows: CREATE INDEX index_name ON table_name; The DROP INDEX Command: An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved. The basic syntax is as follows: DROP INDEX index_name; Difference between Clustered Index and Non Clustered Index in SQL Server 1.One of the main difference between clustered and non clustered index in SQL Server is that, one table can only have one clustered Index but It can have many non clustered index, approximately 250 because Basically primary key is Clustered index and Unique key is Non- Clustered index and one table have only one primary. 2. Basically primary key is Clustered index and Unique key is Non-Clustered index. 3. A clustered index determines the order in which the rows of the table will be stored on disk .A non-clustered index has no effect on which the order of the rows will be stored. 4. Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index.
• 3.
  5. Non-Clustered Indexrequired more space as compared to clustered Index because in non- clustered index one separate table is maintained. Here I mostly focus on what is index and types of index in SQL server .Hope u like my articles.