Say I have the following table: CREATE TABLE [dbo].[widgets] ( [widget_id] [smallint] IDENTITY(1, 1) NOT NULL , [widget_name] [varchar](100) NOT NULL , [widget_code] [varchar](20) NULL , [widget_start] [datetime] NOT NULL , [widget_end] [datetime] NOT NULL , [widget_type] [smallint] NOT NULL , [widget_parent] [smallint] NOT NULL , [widget_location] [smallint] NOT NULL , [create_date] [datetime] NOT NULL CONSTRAINT [DF_widgets_create_date] DEFAULT ( GETDATE() ) , [change_date] [datetime] NOT NULL , [created_by] [varchar](30) NOT NULL CONSTRAINT [DF_widgets_created_by] DEFAULT ( USER_NAME() ) , [updated_by] [varchar](30) NOT NULL , CONSTRAINT [PK_widgets] PRIMARY KEY CLUSTERED ( [widget_id] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[widgets] WITH CHECK ADD CONSTRAINT [CK_widget_start_lt_widget_end] CHECK (([widget_start]<[widget_end])) GO ALTER TABLE [dbo].[widgets] CHECK CONSTRAINT [CK_widget_start_lt_widget_end] GO ...and I decide that I want a covering index something like the following: CREATE UNIQUE NONCLUSTERED INDEX [ix_u_nc_widget_type] ON [dbo].[widgets] ( [widget_type] ASC, [widget_start] ASC, [widget_end] ASC, [widget_id] ASC ) INCLUDE ( [widget_name], [widget_location] ) 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 , FILLFACTOR = 90 ) ON [PRIMARY] GO As you can see I've made the index unique by having the last key column as the primary key (widget_id). Under what circumstances would it better to create the index as a NON unique NCI (i.e. by leaving the widget_id off)? Are there any advantages to creating the index as a unique index by having the pk col as the final index key column?
Having an index be unique does have advantages. The optimizer will know that it can guarantee a single row returned for any given key within the index and will adjust the operations it chooses accordingly. So, where possible, I'd probably have a bias towards unique indexes. However, I wouldn't artificially adjust the keys in order to arrive at a unique index. If you have a covering index, unique or not, you've really got yourself into a good situation. It's going to assist performance extremely well. If, on the other hand, it's not covering and it's not unique, well, since you're going to be doing lookups to the cluster or heap, it might not be a very useful index in that case. But, even there, I'd test to be sure. It really does depend on the queries that the index is satisfying. Largely, unique indexes will work best in point lookup situations. They're not as useful in range situations (although, again, test for the given query, test, test, test, test, like the Spam Song). Covering indexes work well in point situations and range situations. Unique and covering, even better. Non-covering, non-unique, non-clustered indexes... test. They may or may not be useful, depending, and are probably most useful in short range situations where the lookups won't be too painful.