Creating Indexes to improve performance

[peatmoss]

I am trying to understand the use of Indexes to improve query performance (a broad task, I know). To pose my question, consider 2 simple tables: [Alarms] which has an AlarmID as its PK and [Events] which has AlarmID as a FK and EventID which represents an ordered sequence of Events (1,2,3,...) for each AlarmID. (The TSQL code for the schema is included at the bottom).

In the [Events] table, I have defined the PK as (AlarmID,EventID). If I do a lot of queries like
SELECT * FROM Alarms,Events WHERE (Alarms.AlarmID = Events.AlarmID) AND (AlarmID = 12)
would I benefit from creating a separate Index on [Events].AlarmID? As a usage issue, Events are rarely looked at on their own. Nearly 100% of the time, they are always viewed in the context of an Alarm object.

As a related question, does the order of the way I specify the PK for [Events] matter? For a clustered index, I think I would want to keep all the Events associated with an AlarmID near each other. If the answer to this question is YES, which should be specified first: AlarmID or EventID?

Any other suggestions/mods are most welcome.

Thnx,
Pete

CREATE TABLE [dbo].[Alarms] (
[AlarmID] [int] NOT NULL ,
[MoreStuff] [varchar] (50) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Events] (
[AlarmID] [int] NOT NULL ,
[EventID] [int] NOT NULL ,
[MoreStuff] [varchar] (50) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Alarms] WITH NOCHECK ADD
CONSTRAINT [PK_Alarms] PRIMARY KEY CLUSTERED
(
[AlarmID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Events] WITH NOCHECK ADD
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED
(
[AlarmID],
[EventID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Events] ADD
CONSTRAINT [FK_Events_Alarms] FOREIGN KEY
(
[AlarmID]
) REFERENCES [dbo].[Alarms] (
[AlarmID]
) ON DELETE CASCADE NOT FOR REPLICATION
GO

 

[tlbroadbent]

SQL Server performance depends on proper indexing. For small tables, indexing may not be necessary but as tables grow indexes are essential to good performance.

If the Events table contains more than a few records, you will benefit from an index on Events.AlarmID. However, there is no need to add another Index to Events because the primary key is an index.

A clustered index may or may not be the best choice for the index. I believe it will prove to be the best for performance but I've been surprised a few times. You need to test. And retest as the tables grow.

There are a number of articles at Microsoft's website and others where you can read about indexing. A few of those articles are listed at the following link.

http://members.home.net/tlbroadbent/sqlarticles.htm

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.