Q.What are the different types of Indexes available in SQL Server?
Ans: The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
Q.What is the difference between Clustered and Non-Clustered Index?
Ans: In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
Q.What is Heap Table ?
Ans: Heaps (Tables without Clustered Indexes), A heap is a table without a clustered index. One or more non-clustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order.
Q.What is Fill Factor?
Ans: Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.
Q.What is the default fill factor value?
Ans: By default the fill factor value is set to 0.
Q.Where do you find the default Index fill factor and how to change it?
Ans: The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes.
The other option of viewing and changing this value is using