Posted By

derekholmes on 02/05/10


Tagged

index key keys Foreign


Versions (?)

Foreign Keys


 / Published in: C#
 

When you add a Foreign Key Relationship in MS SQL you should always add a index as well for performance. This seems like it should be almost done automatically but its not.

  1. ALTER TABLE [dbo].[sarg_PM_StatusHistory] WITH NOCHECK ADD CONSTRAINT [FK_sarg_PM_StatusHistory_sarg_PM_LOC] FOREIGN KEY([locID])
  2. REFERENCES [dbo].[sarg_PM_LOC] ([locID])
  3. ON UPDATE CASCADE
  4. ON DELETE CASCADE
  5. GO
  6.  
  7. ALTER TABLE [dbo].[sarg_PM_StatusHistory] CHECK CONSTRAINT [FK_sarg_PM_StatusHistory_sarg_PM_LOC]
  8. GO
  9.  
  10. /****** Object: Index [IX_sarg_PM_StatusHistory] Script Date: 02/05/2010 09:29:47 ******/
  11. CREATE NONCLUSTERED INDEX [IX_sarg_PM_StatusHistory] ON [dbo].[sarg_PM_StatusHistory]
  12. (
  13. [locID] ASC,
  14. [status] ASC
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  16. GO

Report this snippet  

You need to login to post a comment.