That is just example for just learned post .
/* make sure you do not have actually real table with that name! */
IF OBJECT_ID('checksumsTable', 'U') IS NOT NULL
DROP TABLE checksumsTable
CREATE TABLE checksumsTable(checksumsTableId int primary key IDENTITY(1, 1), someDescriptions varchar(MAX))
/* add table used as our own index */
ALTER TABLE checksumsTable ADD checkSumValue AS CHECKSUM(someDescriptions)
/* and index that index */
CREATE NONCLUSTERED INDEX ix_checksumsTable_checkSumValue ON checksumsTable(checkSumValue)
/* you could, always, create index on string column, but it works only for data < 900 bytes, as it is max length of key */
-- CREATE NONCLUSTERED INDEX ix_checksumsTable_someDescriptions ON checksumsTable(someDescriptions)
/* fill our newly created table with some random datas */
INSERT INTO checksumsTable(someDescriptions)
SELECT
(
/* I assume that it is not perfect method, but it actually works. And it is fast.
Table 'Numbers' is common Tally Table */
SELECT CHAR(CAST(NEWID() AS BINARY(1))*N.n % 25 + 65) AS [text()]
FROM Numbers WHERE n < 8000
FOR XML PATH ('')
) [xx]
FROM Numbers N WHERE n < 1001
And that most valuable part is:
When we compare it directly:
SELECT #checksumsTableId FROM #checksumsTable WHERE 'AAFPPFFUFPFPUFPAFAUPFKKAFAKUFAAUK(...cut!)' = someDescriptions
vs
using CHECKSUMS:
SELECT #checksumsTableId FROM #checksumsTable WHERE CHECKSUM('AAFPPFFUFPFPUFPAFAUPFKKAFAKUFAAUK(...cut!)') = checkSumValue
As you can see, using checksum improves looking for data.
Without it, I had in my case 1005 logical reads, while with CHECKSUM, it was 2 logical reads.
However - you can say - we could use classic index. Yes, but not for so long strings.
If, even, you've created index for no longer than VARCHAR(900), then, that index would be much bigger and heavier, I think.
And, anyway, its just an example.