What index should be used if all queries only need a perfect match (no partial text searches)

Question

I want to improve the performance of the query var dbObj = dbSet.Where(x => x.Name == name).FirstOrDefault(); done on the table pasted below. I was thinking about adding an index on [Name]. What type of index should I use if I don't need the ability to query for substrings?

CREATE TABLE [dbo].[TitleTypes] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (MAX) NOT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime),
    CONSTRAINT [PK_TitleTypes] PRIMARY KEY CLUSTERED ([Id] ASC)

)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[TitleTypesHistory]));

I'm looking into Full Text Search which requires the creation of a catalog. If I understand my research correctly this would greatly benefit someone who needs to search substrings within the text which is not the case here. Instead I was hoping for something like an index of a strings binary to make it quicker to determine if my search string is contained within the column.

Answer 1

For wide character columns your best bet is to calculate a checksum and add an index.

Then calculate the checksum of what you are searching for and add that to your query, it looks like this:

-- add a persisted computed column, we will calculate a checksum over the body column which is of type nvarchar(max) alter table dbo.posts add chksum_body as checksum(body) persisted

-- add an nonclustered index to the table for the new column create nonclustered index ncix_posts_chksum_body on dbo.posts (chksum_body)

-- show query io and cpu usage set statistics io, time on

-- we need 2 variables, one for the string we are searching for and one for its checksum declare @str nvarchar(max) declare @chksumstr int

-- initialise the variable with the search string select @str = N'Can anyone help me to index wide columns in SQL Server please' -- calculate a checksum for the search string and store in a variable select @chksumstr = checksum(@str)

-- compare search the old way (no index) select top 100 * from [dbo].[Posts] where body = N'Can anyone help me to index wide columns in SQL Server please'

-- to search using the checksum, note we recheck the value of the body to eliminate any false matches due to checksum collisions -- but as we have an index seek we just are doing a residual filter on the few matching rows select top 100 * from [dbo].[Posts] where body = @str and chksum_body = @chksumstr SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

Id AcceptedAnswerId AnswerCount Body 72 0 2 Can anyone help me to index wide columns in SQL Server please (1 row affected)

Table 'Posts'. Scan count 5, logical reads 4239877, physical reads 2150, page server reads 0, read-ahead reads 4194084, page server read-ahead reads 0, lob logical reads 677199, lob physical reads 407176, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times: CPU time = 34829 ms, elapsed time = 36995 ms.

Id AcceptedAnswerId AnswerCount Body 72 0 2 Can anyone help me to index wide columns in SQL Server please (1 row affected)

Table 'Posts'. Scan count 1, logical reads 7, physical reads 4, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

Answer 2

I would suggest to evaluate the MAX LEN of the [name] field , to see the real usage of it.

You could eventually add a new field with the first 8000 chars of the [name] field and index that field.

But you have definitely to reshape the way you store that data in a searchable way.