The clustered index on a table can be defined as: the sort order for how the data for the table is actually stored. Being that the clustered index is the actual data itself, you cannot have two clustered indexes. You can however have many non clustered indexes. These non clustered indexes are a subset of the columns for the table that point back to the clustered index itself. If a clustered index is not defined, then the table is considered to be a heap. Or just a scattered array of rows with no particular sort order. The only time a heap is considered useful, is when insert speed is a factor. When new rows are inserted into a heap, they do not need to get stored in any particular order and thus do not need to be squeezed into any particular “page” of an index. Whereas, the clustered index insert needs to be placed (or squeezed) into a specific location in order to maintain the underlying sort order. This process of being squeezed occurs on what is called a page of the index. Multiple pages comprise the entire index, and there is also a setting defining how “full” pages should be kept. This fullness percentage is called “fill factor” and a lower percentage accommodates inserts into the proper sort order without having to push rows to the next page in order to fit the incoming rows. So if insert speed is not a factor, or inserts are not happening often […]
Continue reading ...
To add an index in SQL Server use the CREATE INDEX statements. When adding indexes remember there can only be one clustered index per table. The main options when creating an index are clutered or nonclustered or unique vs non unique. Using SQL 2005+ you can also specify columns to include at the leaf level of the index. Create a single nonclustered index [cc lang=”sql”] CREATE UNIQUE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber) — specify table and column name [/cc] Create a multi-column (composite) nonclustered index [cc lang=”sql”] CREATE UNIQUE NONCLUSTERED INDEX IX_NC_PresidentNumber_PresidentName — specify index name ON dbo.Presidents (PresidentNumber,PresidentName) — specify table and column names [/cc] Create a multi-column (composite) clustered index [cc lang=”sql”] CREATE UNIQUE CLUSTERED INDEX IX_C_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber,PresidentName) — specify table and column names [/cc] Create a non clustered index with included columns [cc lang=”sql”] CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber) — specify table and column name INCLUDE (President,YearsInOffice,RatingPoints) — specify included columns [/cc] Create index with fill factor [cc lang=”sql”] CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber) — specify table and column name WITH (FILLFACTOR = 80) — specify the fill factor [/cc] SQL Server 2008 options SQL Server 2008 provides additional index options. Most notably it provides the ability to filter an index. This can help decrease the size of the index for very large or partitioned table to only include the records that are normally […]
Continue reading ...
This article gives a real world explanation (and code examples) of indexing along with the tools necessary to be able to choose the right indexing strategy for your tables.
Continue reading ...
This script will automatically determine whether a rebuild or a reorganize should be used according to the fragmentation of the index. It will then execute the appropriate command. Note that performing index rebuilds online during production hours will cause contention. [cc lang=”sql”] SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(258); DECLARE @objectname nvarchar(258); DECLARE @indexname nvarchar(258); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command varchar(8000); — ensure the temporary table does not exist IF EXISTS (SELECT name FROM sys.objects WHERE name = ‘work’) DROP TABLE work; — conditionally select from the function, converting object and index IDs — to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO work FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’) WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; — Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM work; — Open the cursor. OPEN partitions; — Loop through the partitions. FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; WHILE @@FETCH_STATUS = 0 BEGIN; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; — 30 is the decision point at which to switch — between […]
Continue reading ...
Code for reindexing all tables in a database using dbreindex
Continue reading ...
