1

Table structure:

ID textvalue longtextvalue

textvalue has an index and a length of 900
longtextvalue has no index and a length of MAX

Goal is to put together both columns with a length of MAX and an index. So that longtextvalue can be removed and only textvalue remains but can store longtextvalues with an index. It works with PostgreSQL, Oracle with CLOB but with Microsoft SQL Server it doesn't.

Queries run against would be = queries

Fine would be a filtered index that works up to 900 characters and for everything above there's no index. But using that filtered index for the size would require the LEN() function which doesn't work. And a workaround with a computed column doesn't work as well:

textvalue_size column = LEN(textvalue).

To have the size as help. And then filtered index either on textvalue or textvalue_size doesn't work since, SQL Server doesn't support filtered index on computed column.

Therefore is there a way combining those two columns without using a full-text index which would require an extra catalog?

SQL Server Version: 2014 and above

0
2

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.

0
4

Goal is to put together both columns with a length of MAX and an index

That is ambiguous. If you mean that the table structure changes such that there are only two colums (ID, textvalue+longtextvalue) - then the index could only be keyed on ID and include textvalue+longtextvalue.

If the table structure isn't changing then same concept but index could be keyed solely on textvalue (or composite with ID) and include longtextvalue.

LOB data can't be used as part of index key. They can, however, be included. For example:

CREATE NONCLUSTERED INDEX [MyIdx] ON [MyTable] (textvalue) INCLUDE (longtextvalue)

The benefit of doing this would vary depending on your workload.

Up to 32 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes for a clustered index, or 1,700 for a nonclustered index. The limits are 16 columns and 900 bytes for versions before SQL Database and SQL Server 2016 (13.x).

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index.

...

All data types are allowed [in INCLUDEd columns] except text, ntext, and image.

Product documentation for CREATE INDEX:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.