Primary Indexed?
-
CREATE TABLE [PreferenceType] ( [PreferenceTypeId] [BIGINT] IDENTITY (1,1) NOT NULL, [LongName] [NVARCHAR](500), [ShortName] [NVARCHAR](250) ); CREATE TABLE [Table2] ( [Table2Id] [BIGINT] IDENTITY (1,1) NOT NULL, [PreferenceTypeId] [INT] ) Given the Preference Lookup table above, if I do an Inner Join to Table2 on the PreferenceTypeId field, I should index like this: CREATE INDEX [PreferenceTypeId_Idx] ON [Table2]([PreferenceTypeId]); <--- good? But I shouldn't index like this: CREATE INDEX [PreferenceTypeId2_Idx] ON [PreferenceType]([PreferenceTypeId]); <--- bad? because Primary Key fields should already be indexed, correct?
-
CREATE TABLE [PreferenceType] ( [PreferenceTypeId] [BIGINT] IDENTITY (1,1) NOT NULL, [LongName] [NVARCHAR](500), [ShortName] [NVARCHAR](250) ); CREATE TABLE [Table2] ( [Table2Id] [BIGINT] IDENTITY (1,1) NOT NULL, [PreferenceTypeId] [INT] ) Given the Preference Lookup table above, if I do an Inner Join to Table2 on the PreferenceTypeId field, I should index like this: CREATE INDEX [PreferenceTypeId_Idx] ON [Table2]([PreferenceTypeId]); <--- good? But I shouldn't index like this: CREATE INDEX [PreferenceTypeId2_Idx] ON [PreferenceType]([PreferenceTypeId]); <--- bad? because Primary Key fields should already be indexed, correct?
You should make PreferenceType.PreferenceTypeId a PrimaryKey (Yes it will be automatically indexed) and you should make Table2.PreferenceTypeId a ForeignKey constraint referencing PreferenceType.PreferenceTypeId (this will also be automatically indexed). [Edit] Foreign keys are NOT automatically indexed.
modified on Wednesday, March 17, 2010 5:06 PM
-
You should make PreferenceType.PreferenceTypeId a PrimaryKey (Yes it will be automatically indexed) and you should make Table2.PreferenceTypeId a ForeignKey constraint referencing PreferenceType.PreferenceTypeId (this will also be automatically indexed). [Edit] Foreign keys are NOT automatically indexed.
modified on Wednesday, March 17, 2010 5:06 PM
KEWL, i learned something new, that foreign keys automatically get indexed for me.
-
KEWL, i learned something new, that foreign keys automatically get indexed for me.
I believe that's incorrect. Creating an index on a foreign key is usually recommended, but not done automatically[^].
"When did ignorance become a point of view" - Dilbert
-
I believe that's incorrect. Creating an index on a foreign key is usually recommended, but not done automatically[^].
"When did ignorance become a point of view" - Dilbert
Thank you for the correction and the links.
-
Thank you for the correction and the links.
You're welcome.
"When did ignorance become a point of view" - Dilbert