CODEWITHSUNDEEP
sqlsql-server-2008indexing
Matt
Matt

Reputation: 26971

Indexing a large table to find distinct values in each columns

The Table 'TestResult' is very large (20M+ rows), but not every Test, Student or ResultType is present in it.

I want to quickly get unique TestId, StudentId and ResultTypeId. My current strategy is to have an index on each of the preceding columns in the following manner.

CREATE NONCLUSTERED INDEX [NCI_TestResult_ResultTypeId] ON [dbo].[TestResult]
(
    [ResultTypeId] 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

I have one such index per column.

Is this the correct way to do this or is there a better way to do this with only 1 index for all three columns ?

I am using SqlServer 2016

Schema

Upvotes: 0

Views: 79

Answers (1)

shabnamz
shabnamz

Reputation: 48

As ESG has said, creating those three indices is the right way, but if you have batch insert/delete/update on this table with large size of data, you may have performance problems.

Upvotes: 1

Related Questions