...Refer Source... for complete detail.
Source:
14 SQL Server Indexing Questions You Were Too Shy To Ask
Why would you use a covering index instead of a composite index?
...Refer Source...
A composite index is simply one in which you include more that
onekey column.
Multiple key columns can
be useful for uniquely identifying a row, as can be the case when a
unique cluster is defined on a primary key, or you’re trying to
optimize an often-used query that references multiple columns.
In
general, however, the more key columns an index contains, the less
efficient that index, which means composite indexes should be used
judiciously.
This is not an issue for clustered indexes because all data is already three. (That’s why it’s so important to give plenty of thought to how you create your clustered indexes.)
But a nonclustered index includes only the key column values in the leaf nodes. For all other data, the optimizer must take additional steps to retrieve that data from elsewhere, which could represent significant overhead for your most common queries.
That’s where the covering index comes in. When defining your nonclustered index, you can include columns in addition to the key columns.
....
SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
You can create a composite nonclustered index on both columns, but the OrderDate column only adds overhead to the index and serves no purpose as a key column.
A better solution is to create a covering index with OrderID as the key column and OrderDate as the included column:
CREATE NONCLUSTERED INDEX ix_orderid
ON dbo.Sales(OrderID)
INCLUDE (OrderDate);
This way, you avoid the disadvantages of indexing a column unnecessarily, while still benefiting your query. The included column is not part of the key, but the data is still stored in the leaf nodes. This can improve performance without incurring more overhead. Plus, there are fewer restrictions on columns used as included columns, compared to those used as key columns.
************************************************************
1.Why can’t a table have two clustered indexes?
CREATE UNIQUE CLUSTERED INDEX ix_oriderid_lineid
ON dbo.Sales(OrderID, LineID);
When you run the statement, all rows in the table are physically
sorted, first by the OrderID column and then by the LineID column
...
For this reason, you cannot create two clustered indexes.
There can be only one table and that table can be sorted in only
one order.
2.Given the many benefits of clustered tables, why even bother with heaps?
Clustered tables are great, and most of your queries will probably
perform best of your tables are configured with clustered indexes.
But in some cases you might want to leave the table in its natural
state, that is, as a heap, and create only nonclustered indexes
to support your queries.
A heap, as you’ll recall, stores data in an unspecified order.
Normally, the database engine adds the data in the order the
rows are inserted into the table,....
If the query engine must find data without the benefit of a
nonclustered index, it does a full table scan to locate the target
rows.
On a very small table, this is usually not a big deal, but as a
heap grows in size, performance is likely to quickly degrade.
A nonclustered index can help, of course, by using a pointer that
directs the query engine to the file, page, and row where the data
is stored—normally a far better alternative to a table scan.
Even so, it’s still hard to beat the benefits of a clustered index
when weighing query performance.
Yet heaps can help improve performance in certain situations.
Consider the table that has a lot of insert activity, but few
updates and deletes, if any.
On a heap, you won’t see
the type of page splits and fragmentation you would with a clustered
index (depending on the key columns) because rows are simply added to
the end of the heap. Too much page splitting can have a significant
effect on performance, and not in a good way. In general, heaps make
insert operations relatively painless, and you don’t have to
contend with the storage or maintenance overhead you find with
clustered indexes.
What all this means is that you should consider using a heap only
when you’re working with ultra-light tables or your DML operations
are limited to inserts and your queries are fairly basic
(and you’re still using nonclustered indexes). Otherwise, stick
with a well-designed clustered index, that is, one defined on a
simple ascending key, such as the ubiquitous IDENTITY column.
3.How do I override the default fill factor when creating an
index?
....
The fill factor refers to the amount of space an index uses on a
leaf node before flowing over to a new page.
...
By default, the fill factor on a SQL Server instance is set to 0,
which is the same as setting the fill factor to 100.
....
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'fill factor', 90; GO RECONFIGURE; GO
Once you’ve reset the fill factor, you must restart the SQL Server
service.
USE AdventureWorks2012; GO CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id('Person.Person') AND name='ix_people_lastname';
However, suppose we drop the index and re-create it, only now we provide a specific fill factor: CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id('Person.Person') AND name='ix_people_lastname';
....
For example, someone could have mucked around with the server and
done something dumb like set the fill factor to 20. In the
meantime, you continue to create indexes assuming that the default
is still 0. Unfortunately, you have no way of knowing the fill
factor when you create the index unless you specifically retrieve
that value afterwards, like we did in our examples. Otherwise,
you have to wait until the performance starts steadily degrading
and you realize something is wrong.
....
Another fill factor issue you should be aware of has to do with
rebuilding indexes. As with creating an index, you can specify a
fill factor when you rebuild it. However, unlike index creation,
the rebuild does not use the server default, despite how it might
appear. Rather, if you don’t specify a fill factor, SQL Server
uses the index’s fill factor as it existed before the rebuild.
For example, the following ALTER INDEX statement rebuilds the
index we just created: ALTER INDEX ix_people_lastname ON Person.Person REBUILD; GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id('Person.Person') AND name='ix_people_lastname';
4.
Can you create a clustered index on a column with duplicate values?
Yes and no. Yes, you can create a clustered index on key columns
that contain duplicate values. No, the key columns cannot remain
in a non-unique state. Let me explain. If you create a non-unique
clustered index on a column, the database engine adds a four-byte
integer (a uniquifier) to duplicate values to ensure their
uniqueness and, subsequently, to provide a way to identify each
row in the clustered table.
For example, you might decide to create a clustered index on the
LastName column of a table that contains customer data.
...
the database engine will modify the duplicates so that the values
look something like this: Adams, Franklin, Hancock, Hancock1234,
Washington, Smith, Smith4567, and Smith5678.
...
On the surface, this might seem an okay approach, but the integer
increases the size of the key values, which could start becoming
an issue if you have a lot of duplicate values and those values
are being referenced by foreign keys and nonclustered indexes.
For this reason, you should try to create unique clustered indexes
whenever possible. If not possible, at least go for columns that
have a high percentage of unique values. 5. How is a table stored if a clustered index has not ben defined on
the table?
...
In most cases, you’ll want to create a clustered index on a table
to take advantage of the sorting capabilities and query benefits
they can deliver.
...
However, if you choose not to create a clustered index, you can
still create nonclustered indexes on the heap. In such cases,
each row in the index includes a pointer that identifies the
row being referenced in the heap. The pointer includes
the data file ID, page number,
and row number for the targeted data.
6.
What is the relationship between unique
and primary key constraints and a table’s
indexes?
Primary key and unique constraints ensure that the values in the
key columns are unique. You can define only one
primary key on a table and it cannot
contain null values. You can create
multiple unique constraints on a table
and each one can contain a single null
value.
When you create a primary key constraint,
the database engine also creates a
unique clustered index, if a clustered
index doesn’t already exist.
...Refer Source...
When you create a unique constraint, the database engine creates
a unique nonclustered index.
.....
For all practical purposes, a unique constraint and unique index
are one in the same.
7. Why are SQL Server clustered and
nonclustered indexes considered B-tree
indexes?
...Refer Source...
8. How can an index improve performance if the query engine has to
negotiate through all those index nodes?
...Refer Source...
Just like a book’s index, a SQL Server index lets you perform
targeted queries instead of scanning all of a table’s data.
...Refer Source...
9. Does a clustered index have to be created on the primary key
column?
True, a clustered index and primary key constraint is usually a
match made in heaven, so well suited in fact that when you define
a primary key, a clustered index is automatically created, if one
doesn’t already exist.
...Refer Source...
In some cases, a surrogate primary key can be an even better
choice because, in addition to being unique, the values are
small and added sequentially, making the nonclustered indexes
that reference those values more efficient as well.
...Refer Source...
In the end, however, your clustered index should take into
account a number of factors, such as how many nonclustered indexes
will be pointing to the clustered index, how often the clustered
key values will change and how large those key columns are.
...Refer Source...
A clustered index should be based on relatively stable columns
that grow in an orderly fashion, as opposed to growing randomly.
The index should also support the queries most commonly accessing
the table’s data so they can take full advantage of the data being
sorted and available in the leaf nodes. If the primary key fits
this scenario, then use it. Otherwise, use a different set of
columns. 10. If you index a view is it still a view?
...Refer Source...
You can improve a
view’s performance by creating clustered and nonclustered indexes
on that view, just like you create indexes on a table, the main
caveat being that you must create a unique clustered index before you
can create a nonclustered one.
When creating an indexed view (also referred to as a materialized view),
...Refer Source...
Before you can create an index on a view, it must meet a number of
restrictions. For example, the view can reference only base
tables, not other views, and those tables must be within the same
database.
11.Filtered Index:
...Refer Source...
...
since SQL Server 2008, you’ve been able to create filtered indexes
that limit the rows included in the index.
...
CREATE NONCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber IS NOT NULL; ...Refer Source...
************************************************************
No comments:
Post a Comment