INDEXS IN SQL SERVER
clustered index
---------------
datapages store into leaf level nodes
only one clustered index for table (data arrange physically only one way)
good performance
not allow duplicates
If we create primary key on table on any key column then automatically the clustered index create by default.
Note: Maximum number of columns can be created per table: 1024
no clustered index
--------------------
data pages store into address of pointers
249-----2005
999-----2008
allow duplicates ,one null value
forgien key creates automatically non cluster index created
Clustered
A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.
Nonclustered
A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.
Unique
A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.
Uniqueness can be a property of both clustered and nonclustered indexes.
Columnstore An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing.
Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.
Index with included columns
A nonclustered index that is extended to include nonkey columns in addition to the key columns.
Index on computed columns An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.
Filtered
An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
Spatial
A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometrydata type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.
XML
A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.
Full-text
A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.
Q.1 what is difference between Clustered or Non Clustered Index.
Ans. Cluster Index physically stores data, or arranges data in one order • Data is Stored in physically ordered by clustered key so Only one clustered index
in a one table • On leaf level page have actual data • Non clustered can create 249 index on a table
Q.2 what is Covering Index
Ans. A covering index is one that contains all the columns referenced in the query for a single table
Q3. What is Missing Index
Ans When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes
Q4. How to find Missing Index Ans.
sys.dm_db_missing_index_details
Q5. What is difference between Primary Key and Unique Key Ans. When we Create Clustered Index primary key auto created. You can not post null values in Primary key but in unique key.
Q6. How you find Duplicate Index Ans. sp_helpindex table_name
Q7. T-sql of Disable / Enable Index and how do you find ?
Ans. ALTER INDEX [Index_name) ON [dbo].[Table_name] DISABLE ---- FOR Disabling ALTER INDEX (INDEX_NAME) ON (DBO.TABLE_NAME) REBUILD ---- for Enabling SELECT is_disabled,type_desc from sys.indexes where name = 'index_name'
Q8. What is Fill Facto
Ans. Fill factor is a values how data page will be fill. Means if fill factor values is 70 then Data page will be free space is 30
Q9. What is the Heap
Ans. A heap is an unordered table. This means that rows will be inserted into the heap anywhere where there is space.
A heap consists entirely of data pages - as there is no b-tree, there are no index pages
Q10. How we do Rebuild / Reorgnized Index (fragment of Index)
Ans. you can use sys.dm_db_physical_index_State for Fragmentation of Index and check avg_fragmentation_in_percent column if values >5 to >=30 then Reogrnized or <30 then Rebuild.
Q11. What is difference between Online and OFFLINE Rebuild Index
Ans. Online Mode the new Index is build side by side means table have not lock. after creation Index table have lock for replacing old Index for few time In Offline Mode the table have locked for any read or write and then new index build one by one. while holding a lock on the table no read or write operation is permitted on the table after rebuild or replace new index you have to permitted for read and write.
Q12. what is candidate key Ans.
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than `one candidate key, one of them will become the primary key, and the rest are called alternate keys