Database Interview: What is fillfactor? What is the use of it ? What happens when we ignore it? When you should use low fill factor?
When you create a clustered index, the data in the table is stored in
the data pages of the database according to the order of the values in
the indexed columns. When new rows of data are inserted into the table
or the values in the indexed columns are changed, Microsoft® SQL
Server™ 2000 may have to reorganize the storage of the data in the
table to make room for the new row and maintain the ordered storage of
the data. This also applies to nonclustered indexes. When data is
added or changed, SQL Server may have to reorganize the storage of the
data in the nonclustered index pages. When a new row is added to a
full index page, SQL Server moves approximately half the rows to a new
page to make room for the new row. This reorganization is known as a
page split. Page splitting can impair performance and fragment the
storage of the data in a table.
When creating an index, you can specify a fill factor to leave extra
Explain about Clustered and non clustered index? How to choose between a Clustered Index and a Non-Clustered Index?
There are clustered and nonclustered indexes. A clustered index is a
special type of index that reorders the way records in the table are
physically stored. Therefore table can have only one clustered index.
The leaf nodes of a clustered index contain the data pages.
A nonclustered 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. The leaf nodes of a nonclustered index does not consist
of the data pages. Instead, the leaf nodes contain index rows.
Consider using a clustered index for:
o Columns that contain a large number of distinct values.
o Queries that return a range of values using operators such as
BETWEEN, >, >=, <, and <=. o Columns that are accessed sequentially. o Queries that return large result sets. Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences: o The data rows are not sorted and stored in order based on their non-clustered keys. o The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value. o Per table only 249 non clustered indexes. 30. Disadvantage of index? Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. 31. Given a scenario that I have a 10 Clustered Index in a Table to all their 10 Columns. What are the advantages and disadvantages? A: Only 1 clustered index is possible.
