A few things to consider:
I have inherited a system which has very few tables with indexes. One table has 18K rows, expected to grow to 25K by year end, row size is 140 bytes.
It's good to have a primary key and clustered index on every table; this is generally considered a best practice. That said, 25K is not a lot of records. If you have a lot to get done I personally would not worry to much about this.
CREATE TABLE [dbo].[CEOData](
[StatisticsDate] [datetime] NULL,
[Seq] [int] NULL,
[Format] [int] NULL,
[Stats] [varchar](50) NULL,
[Actual] [numeric](18, 2) NULL,
[Budget] [numeric](18, 2) NULL,
[Variance] [numeric](18, 2) NULL,
[PriorYear] [numeric](18, 2) NULL
)
Not to get off the topic but, if you have control over the table design you would probably benefit from setting these columns to NOT NULL unless they MUST be NULLable.
This table is never updated, 40 rows inserted daily, queried daily by StatisticsDate.
StatisticsDate+ Stats would be unique.... if I were to create an unique clustered index on StatisticsDate+Stats, it would be unique, static and ever-increasing. But it would not be narrow.
You would have to change these columns to not be NULLable. That's not an ideal clustering key but based on this design and the number of rows that you are dealing with I think that would be fine.
The SP which inserts to this is slow, mostly because of the number of tables and views the data is pulled from.
Adding indexes will make that only make those inserts slower. Just an FYI.
The report that is generated from this table is quite slow. I feel certain that having an index would help with that. This table is not accessed by end-users.
An index may not help. If you only have 18K records in there and the report is slow it's probably not due to lack of indexes on a table. What kinds of reporting sofware are you using? How are the reports accessing the table (e.g. via stored procedure, ad-hoc SQL query)?
The part that I am hung up about is the 'narrow' aspect of a good clustered index. Should the fact that StatisticsDate+Stats would be up to 60 bytes prevent me from using it as a clustered index?
Thanks for your suggestions.
In this case that might be your best option and not a terrible one considering the small number of rows you are dealing with. For a better suggestion can you post some examples of queries that are ran against this table?
-- Itzik Ben-Gan 2001