HOW TO INDEX SQL STATEMENT?
If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
If you are using MSSQL 2000 and you need to create a SQL Server index for performance enhancement, below are few basic things you need to consider:
- Create an Index object.
- Set the Name property.
- Set the IndexedColumns property to the column or columns participating in the index.
- Set the Type property of the Index object to control the attributes of the index created (optional). If not set, a nonclustered index allowing duplicate values is created. For more information about SQL Server index types and limitations on indexes applied to tables.
- Set optional properties, such as FileGroup.
- Get the Table object that references the SQL Server table you want from the Tables collection of a connected Database object.
- Use the BeginAlter method of the Table object to mark the start of changes to the SQL Server table.
- Add the Index object to the Indexes collection of the selected Table object.
- Use the DoAlter method of the Table object to mark the end of changes and create the index on the SQL Server.
Take note that to creates an index on a given table or view.
Only the table or view owner can create indexes on that table. The owner of a table or view can create an index at any time, whether or not there is data in the table. Indexes can be created on tables or views in another database by specifying a qualified database name.
What is the difference between clustered and non-clustered index?
CLUSTERED :
Creates an object where the physical order of rows is the same as the indexed order of the rows, and the bottom (leaf) level of the clustered index contains the actual data rows. A table or view is allowed one clustered index at a time.
A view with a clustered index is called an indexed view. A unique clustered index must be created on a view before any other indexes can be defined on the same view.
Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created.
NONCLUSTERED
Creates an object that specifies the logical ordering of a table. With a nonclustered index, the physical order of the rows is independent of their indexed order. The leaf level of a nonclustered index contains index rows. Each index row contains the nonclustered key value and one or more row locators that point to the row that contains the value. If the table does not have a clustered index, the row locator is the row’s disk address. If the table does have a clustered index, the row locator is the clustered index key for the row.
Each table can have as many as 249 nonclustered indexes (regardless of how they are created: implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX). Each index can provide access to the data in a different sort order.
For indexed views, nonclustered indexes can be created only on a view with a clustered index already defined. Thus, the row locator of a nonclustered index on an indexed view is always the clustered key of the row.
Example index query
This example creates a simple index on the au_id column of the authors table.
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = ‘au_id_ind’)
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
GO
This example creates a unique clustered index on the employeeID column of the emp_pay table that enforces uniqueness. This index physically orders the data on disk because the CLUSTERED clause is specified.
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘emp_pay’)
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = ‘employeeID_ind’)
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
GO
If you have a performance issue on sql transaction, adn you don’t know how to look for it below are the steps that you can use:
1. run sql profiler on the database server that is having performance issue (choose the tuning template provided)
2. look for transaction that takes duration more than the required time.
3. copy the query and paste to sql query analyzer.
4. at the sql query analyzer, press ‘ctrl + L’ (display estimated execution time).
5. look for the total cost of the execution from the diagram normal fast transaction would cost less that 0.XX value.
6. if not, you can create the index. based on the above example. take note you have to change the database name and the column (column take from query example where name = ‘ahb’, column should be ‘name’)
