# Consider creating index on column(s) frequently used in the WHERE, ORDER BY, and GROUP BY clauses.
These column(s) are best candidates for index creating. You should analyze your queries very attentively to avoid creating not useful indexes.
*****
# Keep your indexes as narrow as possible.
Because each index take up disk space try to minimize the index key's size to avoid using superfluous disk space. This reduces the number of reads required to read the index and boost overall index performance.
*****
# Drop indexes that are not used.
Because each index take up disk space and slow the adding, deleting, and updating of rows, you should drop indexes that are not used. You can use Index Wizard to identify indexes that are not used in your queries.
*****
# Try to create indexes on columns that have integer values rather than character values.
Because the integer values usually have less size then the characters values size (the size of the int data type is 4 bytes, the size of the bigint data type is 8 bytes), you can reduce the number of index pages which are used to store the index keys. This reduces the number of reads required to read the index and boost overall index performance.
*****
# Limit the number of indexes, if your application updates data very frequently.
Because each index take up disk space and slow the adding, deleting, and updating of rows, you should create new indexes only after analyze the uses of the data, the types and frequencies of queries performed, and how your queries will use the new indexes. In many cases, the speed advantages of creating the new indexes outweigh the disadvantages of additional space used and slowly rows modification. However, avoid using redundant indexes, create them only when it is necessary. For read-only table, the number of indexes can be increased.
*****
# Check that index you tried to create does not already exist.
Keep in mind that when you create primary key constraint or unique key constraints SQL Server automatically creates index on the column(s) participate in these constraints. If you specify another index name, you can create the indexes on the same column(s) again and again.
*****
# Create clustered index instead of nonclustered to increase performance of the queries that return a range of values and for the queries that contain the GROUP BY or ORDER BY clauses and return the sort results.
Because every table can have only one clustered index, you should choose the column(s) for this index very carefully. Try to analyze all your queries, choose most frequently used queries and include into the clustered index only those column(s), which provide the most performance benefits from the clustered index creation.
*****
# Create nonclustered indexes to increase performance of the queries that return few rows and where the index has good selectivity.
In comparison with a clustered index, which can be only one for each table, each table can have as many as 249 nonclustered indexes. However, you should consider nonclustered index creation as carefully as the clustered index, because each index take up disk space and drag on data modification.
*****
# Create clustered index on column(s) that is not updated very frequently.
Because the leaf node of a nonclustered index contains a clustered index key if the table has clustered index, then every time that a column used for a clustered index is modified, all of the nonclustered indexes must also be modified.
*****
# Create clustered index based on a single column that is as narrow as possibly.
Because nonclustered indexes contain a clustered index key within their leaf nodes and nonclustered indexes use the clustered index to locate data rows, creating clustered index based on a single column that is as narrow as possibly will reduce not only the size of the clustered index, but all nonclustered indexes on the table also.
*****
# Avoid creating a clustered index based on an incrementing key.
For example, if a table has surrogate integer primary key declared as IDENTITY and the clustered index was created on this column, then every time data is inserted into this table, the rows will be added to the end of the table. When many rows will be added a "hot spot" can occur. A "hot spot" occurs when many queries try to read or write data in the same area at the same time. A "hot spot" results in I/O bottleneck.
Note. By default, SQL Server creates clustered index for the primary key constraint. So, in this case, you should explicitly specify NONCLUSTERED keyword to indicate that a nonclustered index is created for the primary key constraint.
*****
# Create a clustered index for each table.
If you create a table without clustered index, the data rows will not be stored in any particular order. This structure is called a heap. Every time data is inserted into this table, the row will be added to the end of the table. When many rows will be added a "hot spot" can occur. To avoid "hot spot" and improve concurrency, you should create a clustered index for each table.
*****
# Don't create index on column(s) which values has low selectivity.
For example, don't create an index for columns with many duplicate values, such as "Sex" column (which has only "Male" and "Female" values), because in this case the disadvantages of additional space used and slowly rows modification outweigh the speed advantages of creating a new index.
*****
# If you create a composite (multi-column) index, try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
The order of the columns in a composite (multi-column) index is very important. This can increase the chance the index will be used.
*****
# If you create a composite (multi-column) index, try to order the columns in the key so that the WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index.
The order of the columns in a composite (multi-column) index is very important. The index will be used to evaluate a query only if the leftmost index key's column are specified in the WHERE clause of the query. For example, if you create composite index such as "Name, Age", then the query with the WHERE clause such as "WHERE Name = 'Alex'" will use the index, but the query with the WHERE clause such as "WHERE Age = 28" will not use the index.
*****
# If you need to join several tables very frequently, consider creating index on the joined columns.
This can significantly improve performance of the queries against the joined tables.
*****
# Consider creating a surrogate integer primary key (identity, for example).
Every table must have a primary key (a unique identifier for a row within a database table). A surrogate primary key is a field that has a unique value but has no actual meaning to the record itself, so users should never see or change a surrogate primary key. Some developers use surrogate primary keys, others use data fields themselves as the primary key. If a primary key consists of many data fields and has a big size, consider creating a surrogate integer primary key. This can improve performance of your queries.
*****
# Consider creating the indexes on all the columns, which referenced in most frequently used queries in the WHERE clause which contains the OR operator.
If the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed, then the table or clustered index scan will be made. In this case, creating the indexes on all such columns can significantly improve your queries performance.
*****
# If your application will perform the same query over and over on the same table, consider creating a covering index including columns from this query.
A covering index is an index, which includes all of the columns referenced in the query. So the creating covering index can improve performance because all the data for the query is contained within the index itself and only the index pages, not the data pages, will be used to retrieve the data. Covering indexes can bring a lot of performance to a query, because it can save a huge amount of I/O operations.
*****
# Use the DBCC DBREINDEX statement to rebuild all the indexes on all the tables in your database periodically (for example, one time per week at Sunday) to reduce fragmentation.
Because fragmented data can cause SQL Server to perform unnecessary data reads and the queries performance against the heavy fragmented table can be very bad, you should periodically rebuild all indexes to reduce fragmentation. Try to schedule the DBCC DBREINDEX statement during CPU idle time and slow production periods.
*****
# Use the DBCC INDEXDEFRAG statement to defragment clustered and secondary indexes of the specified table or view.
The DBCC INDEXDEFRAG statement is a new SQL Server 2000 command, which was not supported in the previous versions. Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG does not hold locks long term and thus will not block running queries or updates. So, try to use the DBCC INDEXDEFRAG command instead of DBCC DBREINDEX, whenever possible.
*****
# Consider using the SORT_IN_TEMPDB option when you create an index and when tempdb is on a different set of disks than the user database.
The SORT_IN_TEMPDB option is a new SQL Server 2000 feature, which was not supported in the previous versions. When you create an index with the SORT_IN_TEMPDB option, SQL Server uses the tempdb database, instead of the current database, to sort data during the index creation. Using this option can reduce the time it takes to create an index, but increases the amount of disk space used to create an index.
*****
# Use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes.
This trace will show which tables are being scanned by queries instead of using an index.
SELECT <Column Name>, NumberOfTimes=COUNT(<Column Name>) FROM TableName
GROUP BY <Column Name>
ORDER BY <Column Name> DESC
we get sample result as
UsrID NumberOfTimes
------ -------------
28472 10
28467 4
27552 1
24426 12
The most frequently used wildcard is the percent sign (%).
Within a search string, % means match any number of occurrences of any character.
Before we proceed it further lets create a table Dev_Products having two columns.
Create Table Dev_Products
(
Prod_ID Int,
Prod_Name Varchar(255)
)
Inserting some values.
Insert Into Dev_Products (Prod_ID,Prod_Name)Values(1,'DevProd1')
Go
Insert Into Dev_Products(Prod_ID,Prod_Name)Values(2,'DevProd2')
Go
Insert Into Dev_Products(Prod_ID,Prod_Name)Values(3,'Plaza101')
Go
Insert Into Dev_Products(Prod_ID,Prod_Name)Values(4,'Coupon Park')
GO
Insert Into Dev_Products(Prod_ID,Prod_Name)Values(5,'Imate KJam')
For example, to find all products that start with the word ‘dev’, you can issue the following SELECT statement
SELECT
Prod_ID,
Prod_Name
FROM
Dev_Products
WHERE
Prod_name LIKE 'Dev%'
Output
Prod_ID Prod_Name
----------------------
1 DevProd1
2 DevProd2
(2 row(s) affected)
The % tells Sql Server to accept any characters values after the word “dev”.
Wildcard can be used first or middle or any part of the search pattern.
A rare case of wildcard is in the middle.
SELECT
Prod_ID,
Prod_Name
FROM
Dev_Products
WHERE
Prod_name LIKE 'D%1'
Prod_ID Prod_Name
-------------------------------------------------------
1 DevProd1
Mean search all the products which start from D and end with d.
The Underscore (_) Wildcard
Another useful wildcard is underscore, it works same way like % but it will match only a single charater.
For Example
SELECT
Prod_ID,
Prod_Name
FROM
Dev_Products
WHERE
Prod_name LIKE '_oupon%'
Output
Prod_ID Prod_Name
--------------------------
4 Coupon Park
(1 row(s) affected)
The Brackets ([ ]) Wildcard
The set of characters specified between brackets wildcard which will match any one characters in the specified position (the location of the wildcard).
SELECT
Prod_ID,
Prod_Name
FROM
Dev_Products
WHERE
Prod_name LIKE '[p1]%'
Output
Prod_ID Prod_Name
-------------------------------
3 Plaza101
Brackets [] wildcard will match the charaters begins with P and end with 1.
The Brackets (^) Wildcard
Any single character not within the specified range ([^a-f]) or set ([^abcdef])
SELECT
Prod_ID,
Prod_Name
FROM
Dev_Products
WHERE
Prod_name LIKE '[^Dev]%'
Output
Prod_ID Prod_Name
----------- ------------------------
3 Plaza101
4 Coupon Park
5 Imate KJam
(3 row(s) affected)
Any charaters started with “Dev” will be excluded.