Indexes

Hi - Can anyone tell me the best approach to indexing a database I am using
for reporting?
I have a data table & a time table(below), the time table has a timeID field
with an incrementing integer ID & a clustered Index on this field.
Each time record represents a unique reporting time stamp with all time
details such as day of week, monthname, day of month etc... as other fields.
The data table joins to the time table with the timeID as a foreign key in
the data table.
I am wondering whether I should drop the TimeID field on the time table &
instead use the "localtime" field as the clustered index instead since all
localtime values will be unique (& remove the timeID foreign key from the
data table & replace it with a localtime field).
Would this be more efficient than an integer key as I am likely to query
data by time ranges' table definitions below.
Thanks for any advice on this!!
Mike Knee
CREATE TABLE [dbo].[Data] (
[DataID] [int] IDENTITY (1, 1) NOT NULL ,
[HeaderID] [int] NOT NULL ,
[Value] [float] NOT NULL ,
[TimeID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DimTime] (
[TimeID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeLocal] [smalldatetime] NOT NULL ,
[Year] [smallint] NULL ,
[MonthName] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[MonthNumber] [tinyint] NULL ,
[DayName] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[Quarter] [tinyint] NULL ,
[DayOfWeek] [tinyint] NULL ,
[DayOfMonth] [tinyint] NULL ,
[DayOfYear] [smallint] NULL ,
[WeekOfYear] [tinyint] NULL ,
[Hour] [tinyint] NULL ,
[IsHoliday] [tinyint] NULL ,
[IsWeekday] [tinyint] NULL ,
[IsWorkingDay] [tinyint] NULL ,
[Date] [smalldatetime] NULL
) ON [PRIMARY]
GOOn Thu, 2 Nov 2006 12:56:15 -0000, Michael Knee wrote:

>Hi - Can anyone tell me the best approach to indexing a database I am using
>for reporting?
(snip)
>I am wondering whether I should drop the TimeID field on the time table &
>instead use the "localtime" field as the clustered index instead since all
>localtime values will be unique (& remove the timeID foreign key from the
>data table & replace it with a localtime field).
>Would this be more efficient than an integer key as I am likely to query
>data by time ranges' table definitions below.
Hi Michael,
Based on the infoprmation given: yes.
But the best way to find out is to perform tests. On your hardware, with
your queries, and against your data.
Hugo Kornelis, SQL Server MVP