0

I have the following:

        CREATE TABLE Topic (
            [TopicId] INT IDENTITY (1, 1) NOT NULL,
            [TopicSeq] INT NOT NULL,
            [SubjectId] INT NOT NULL,
            [Name] NVARCHAR (50) Not NULL,
            [Version] rowversion,
            [ModifiedDate] [datetime] NOT NULL,
            CONSTRAINT [PK_Topic] PRIMARY KEY CLUSTERED ([TopicId] ASC)
        )";

I am thinking if I need to find all the Topics for a subject that this will take a long time. If I add an index for SubjectID then will this improve every query automatically and what is the best kind of index for me to add?

  • I wouldn't bother adding indexes until you actually have a performance problem. But yes, an index on SubjectID would speed up certain queries automatically. – Blorgbeard Mar 27 '13 at 03:32
  • But if I was to want to add an index then are there different kinds that I could add? Also as I am using EF then will the EF queries use the index? –  Mar 27 '13 at 03:33
  • It depends on your queries, we cannot help you without them. – Igor Borisenko Mar 27 '13 at 03:33
  • SQL Server will use indexes when it can and it thinks they will help, for all queries, including queries from EF. – Blorgbeard Mar 27 '13 at 03:35
  • You should not have issues until you break it out into a view. Then you may find performance problems. – Ross Bush Mar 27 '13 at 03:38
  • @Blorgbeard: adding an index on foreign key columns is an accepted and well-documented best practice. That's one step I'd definitely always do. But I agree - I would typically not add any *further* indices until I know how the app behaves – marc_s Mar 27 '13 at 05:56

1 Answers1

0

An index on SubjectID would be fairly cheap. You don't want a UNIQUE index, so it's either CLUSTERED or UNCLUSTERED. I'd go for CLUSTERED because the index is not very wide (32 bits) even though there are probably a lot of non-unique values.

Derek Knight
  • 225
  • 2
  • 9
  • Thank you for your advice - What's the difference between a clustered and an unclustered? How would I code the SQL DDL? –  Mar 27 '13 at 03:43
  • @Melina http://stackoverflow.com/questions/5070529/difference-between-clustered-and-nonclustered-index – Gibron Mar 27 '13 at 03:53
  • Oops my bad. You can only have one clustered index per table. Since it defines the sorting order for the data on disk, and you have a clustered index for your topic IDs. So your index would have to be unclustered. It speeds up your searches though because the index will be in order of SubjectId and therefore finding any TopicID via the subjectId will be quick. CREATE NONCLUSTERED INDEX Topic_SubjectId_IX On Topic (SubjectId) That should do it – Derek Knight Mar 27 '13 at 03:59