July 8, 2011 at 1:32 am
Hi all,
I need advice regarding the creation of index for a table. I do not know which type of index I have to choose.
I have a table with about 10 million of records.
this is a table that references the prices of securities for banks. the key is: the security number, stock exchange and date . it is researching by security number, stockexchange and often on MAX (date).
This table is updated every day. at this time, the index is not clustered.
Is it better to have a clustered index (security number ASC, stockexchange ASC, date DESC)?
thanks in advance for your help
July 8, 2011 at 1:35 am
Based on your description, my vote goes to a clustered index.
Just to be sure, when you say "updated", you mean existing rows are updated or new rows are inserted?
-- Gianluca Sartori
July 8, 2011 at 1:43 am
Thanks for your answer.
sorry, READ : new rows are inserted every day instead of updated.
July 8, 2011 at 1:56 am
That's a little wide for a clustered index....
What's the table definition (create table statement) and what are some of the queries you are trying to optimise.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2011 at 2:06 am
freewarefw (7/8/2011)
Thanks for your answer.sorry, READ : new rows are inserted every day instead of updated.
That changes things a lot. I withdraw my vote and give it to a NC.
-- Gianluca Sartori
July 8, 2011 at 2:35 am
- script of the CREATE TABLE :
.[secprice](
[ssp_number] [int] NOT NULL,
[ssp_sexcode] [char](3) NOT NULL,
[ssp_date] [datetime] NOT NULL,
[ssp_date_c] [datetime] NOT NULL,
[ssp_date_m] [datetime] NOT NULL,
[ssp_user] [char](8) NOT NULL,
[ssp_status] [char](1) NOT NULL,
[ssp_sexsec] [int] NOT NULL,
[ssp_price] [decimal](20, 8) NOT NULL,
[ssp_ccy] [char](3) NOT NULL,
[ssp_nominal] [decimal](14, 6) NOT NULL,
[ssp_high] [decimal](20, 8) NOT NULL,
[ssp_low] [decimal](20, 8) NOT NULL,
[ssp_price_ex_interest] [decimal](20, 8) NOT NULL,
CONSTRAINT [ssppkey] PRIMARY KEY NONCLUSTERED
(
[ssp_number] ASC,
[ssp_sexcode] ASC,
[ssp_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[secprice] WITH NOCHECK ADD CONSTRAINT [sspfkey_ccy] FOREIGN KEY([ssp_ccy])
REFERENCES [dbo].[currency] ([ccy_code])
GO
ALTER TABLE [dbo].[secprice] CHECK CONSTRAINT [sspfkey_ccy]
GO
ALTER TABLE [dbo].[secprice] WITH NOCHECK ADD CONSTRAINT [sspfkey_sdt] FOREIGN KEY([ssp_number])
REFERENCES [dbo].[securities] ([sdt_number])
GO
ALTER TABLE [dbo].[secprice] CHECK CONSTRAINT [sspfkey_sdt]
GO
ALTER TABLE [dbo].[secprice] WITH NOCHECK ADD CONSTRAINT [sspfkey_sex] FOREIGN KEY([ssp_sexcode])
REFERENCES [dbo].[stockexchange] ([sex_code])
GO
ALTER TABLE [dbo].[secprice] CHECK CONSTRAINT [sspfkey_sex]
"]
- part of a script where table is used.
July 8, 2011 at 2:40 am
I guess the key here would be are you inserting rows with older dates into the date column, if you can garuntee that all dates inserted will be sequential in nature then no harm using a clustered index, if your inserting data with historical data ranges then Non clustered is the way to go.
Personally I would stick with a non clustered covering index.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply