Good article on understanding indexes
List of index related articles- sys.sysindexes is sql 2k legacy - Partitioned tables and indexes are not fully supported.
Clustered index leaf level node contains actual data pages.
. When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition.
When creating a clustered index you can specify if the column should be indexed asc or desc. Doesnt matter if using just one column but if > 1 then it can make a diff.
Non clustered can be on a a clustered index table (ordered) or on a HEAP (non ordered table).
Non clustered index on a heap will result in the leaf level of the index being the RID (pointer to data) - which is comprised of extent, page and row offset.
Non clustered index on a clustered table will result in leaf level of index being the cluster key of the existing clustered index.
2005 has the INCLUDE keyword which will include extra columns in the leaf level of the non clustered index (clustered index already has the entire row at leaf level), which may be good for covering indexes as a trip from index leaf to data will *not* have to be made for a query with a covering index. To cover a QUERY make sure all columns to be *returned* are included columns and all columns in the *where* clause are index key columns.
2005 can have 1 clustered and up to 249 non-clustered indexes.
Index defragmenting will only reorganize data within pages (extents are 64k - made up of 8 8k pages)
More heavy duty index perf problems may beed index rebuilding
See index fragmentation using :sys.dm_db_index_physical_stats (dbcc showcontig in 2k)
PK is created with a clustered index by default - this is not ideal sometimes as the fillfactor cannot be specified.
CREATE UNIQUE CLUSTERED INDEX is the same as a PK and gives access to specify all options on the index - lovely :-)
Difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.
FILLFACTOR determines how *full* the index pages are on creation - OLAP style stable tables need a high figure. 100
In my test a 53 sec query came down to 12 secs when the PK was replaced by an explicit unique clustered index with a fillfactor of 100. To obtain a very high fill factor the index must be applied after the data has been inserted into the table - which is fine with OLAP.
ALTER INDEX can be run with REBUILD option which is hardcore and make the index unavailable during its use
The other option is to run it with REORGANIZE which will not do quite such a good job but will leave it online.
DBREINDEX is the old skool way of reindexing -
2005 - Database Tuning Advisor tool
FRAGMENTATION
Caused mostly by deletes leaving pages with few rows on them. This is a bit of a blessing for OLTP systems as it means there is always going to be a fair amount of random free space so the probability of page splits when inserts are made is minimal. This also means that read performance is very poor which OLAP systems hate.
Summary - frag can be good for OLTP but always bad for OLAP.
2k - DBCC SHOWCONTIG tablename ALL_INDEXES
A clustered index and a table cannot be on different filegroups
EXECUTION PLANS
There are a few basic operations which SQL will perform when looking for the data that you need. Here they are listed in the order of worst to best.
* Table Scan
* Clustered Index Scan
* Index Scan
* Clustered Index Seek
* Index Seek
The basic rule to follow is Scans are bad, Seeks are good.
When SQL Server does a scan it loads the object which it wants to read from disk into memory, then reads through that object from top to bottom looking for the records that it needs.
When SQL Server does a seek it knows where in the index that the data is going to be, so it loads up the index from disk, goes directly to the part of the index that it needs and reads to where the data that it needs ends. This is obsouly a must more efficient operation than a scan, as SQL already knows where the data is that it is looking for.
When SQL Server is looking for your data probably one of the largest things which will make SQL Server switch from a seek to a scan is when some of the columns are you looking for are not included in the index you want it to use. Most often this will have SQL Server fall back to doing a clustered index scan, since the Clustered index contains all the columns in the table. This is one of the biggest reasons (in my opinion at least) that we now have the ability to INCUDE columns in an index, without adding those columns to the indexed columns of the index. By including the additional columns in the index we increase the size of the index, but we allow SQL Server to read the index, without having togo back to the clustered index, or to the table it self to get these values.
AND VERSUS OR¶
When the query uses the AND operator between predicates, there are a lot of combinations of indexes that you can create in order to satisfy the query. The optimal index would be a composite index on all the columns participating in the WHERE clause with all the rest of the columns in the query as included columns. But you might want to use a near-optimal index in order to reduce the overhead of your indexes.
The case of the OR operator is different. The only way to satisfy the query is to have a separate covering index for each column participating in the WHERE clause. If even a single index is missing or is not efficient enough (for example, because it doesn’t cover all the relevant columns), there is no point in using the other indexes, and you'll get a table scan (or a clustered index scan) instead.
But when you have an efficient index for each of the filtered columns, you'll get a very efficient execution plan that uses each index separately and then concatenates and aggregates the results in order to perform a union of the rows.