I'm in the situation where I am encouraged to tune an existing database and in the process either introduce or remove indexes as seem best fit.
I was looking at a certain table and noticed that the Database Tuning Adviser had been previously used, due to the fact that a lot of the indexes began with
I had a glace at the Clustered Index on said table
CREATE CLUSTERED INDEX [_dta_index_TABLENAME_c_5_1970822083__K6_K8] ON [dbo].[TABLE_NAME] ([register] ASC, [deleted] ASC) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] GO
So the index isn't unique and probably has a very special distribution in the histogram. Both columns
deleted are defined as
NULL in the table DDL:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TABLE__NAME]( [delete] [smallint] NULL, [timestamp] [int] NULL, [root_id] [int] NULL, [object_id] [int] NULL, [object_type] [int] NULL, [register] [int] NULL, [reg_type] [int] NULL, [deleted] [int] NULL ) ON [PRIMARY] GO
...which got me thinking.
How can I determine the least required number of columns to create an
UNIQUE index with the following premises:
- Seeing as
deletewill contain either a
0I omitted this column from my train of thoughts
- I don't know the data
- I do know the number of records is 58'934'964 (and growing by 20 every second)
I do know the distinct counts of each column:
delete = 1 timestamp = 29'030'385 root_id = 1'744'414 object_id = 58'934'823 object_type = 33 register = 9'229'854 reg_type = 8 deleted = 723'952
What I tried
I had a look at the
sys.dm_db_missing_index_details DMV to determine if SQL Server knows when to create an UNIQUE index.
select sddmid.index_handle, db_name(sddmid.database_id), so.name, sddmid.object_id, sddmid.equality_columns, sddmid.inequality_columns, sddmid.included_columns, sddmid.statement from sys.dm_db_missing_index_details sddmid join sys.objects as so on so.object_id = sddmid.object_id where database_id = db_id('DATABASE') and so.name = 'TABLE_NAME'
But no details in that view.
Would I really have to perform a statement over all columns like this:
select -- distinct single column count(distinct delete), count(distinct timestamp), count(distinct root_id),... -- distinct double column count(distinct delete,timestamp), count(distinct delete,root_id), ... --- distinct triple column count(distinct delete,timestamp,root_id), count(distinct delete,timestamp,object_id), ... --- distinct quadruple column count(distinct delete,timestamp,root_id,object_id), ... ... from TABLE_NAME
until I have found a combination that is distinct/unique?
I guess there is a better solution...