advanced code snippet search

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.

Expand | Embed | Plain Text 
  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.