DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: General
       *****************************************************
       #Post#: 40--------------------------------------------------
       Indexing Large Columns to avoid table scan with calculated colum
       n and CHECKSUM
       By: srinivasma_exceldbp Date: April 7, 2014, 12:08 am
       ---------------------------------------------------------
       /***************************************************************
       **************
       Indexing Large Columns with calculated column and CHECKSUM
       SQL server does not allow index sizes larger than 900 bytes
       If there are columns having > 900 bytes we need to create
       computed column with checksum function and create index on the
       column
       In the queries we need to use column and checksumcolumn to avoid
       table scan
       Following demo will provide an example on this
       ****************************************************************
       ************/
       use tempdb
       go
       set nocount on
       go
       if exists(
       select *
       from sys.tables t
       join sys.schemas s on
       t.schema_id
       where t.name
       and s.name
       )
       drop table dbo.LargeKey
       go
       create table dbo.LargeKey
       (
       Id
       LargeField
       constraint PK_Data
       primary key clustered(ID)
       )
       go
       ;with CTE(ID)
       as
       (
       select 1
       union all
       select ID + 1 from CTE where ID < 50000
       )
       insert into dbo.LargeKey(LargeField)
       select REPLICATE(convert(nvarchar(36),NEWID()),15)
       from CTE
       option (maxrecursion 0)
       go
       /* Enable execution plan */
       set statistics io on
       go
       declare
       
       select @Param
       from dbo.LargeKey
       where Id
       /*
       Following query results in Table Scan
       since index can not be created on LargeField which is
       nvarchar(540)
       which is equivalent to 1080 bytes ( > 900 bytes)
       Check Actual Execution Plan
       */
       select * from dbo.LargeKey
       where LargeField = @Param
       go
       /*
       Index  can NOT be created - key size > 900 bytes
       Following will result in error
       */
       create unique nonclustered index IDX_LargeKey_LargeField
       on dbo.LargeKey(LargeField)
       go
       --==============================================================
       =====
       /*
       Solution
       Add a calculated persistent column with checksum
       and add index on that column
       */
       /* Add calculated persistent column */
       alter table dbo.LargeKey
       add
       PERSISTED
       go
       /* Rebuild the clustered index */
       alter index PK_Data on dbo.LargeKey REBUILD
       go
       /* Create the index on the column */
       create index IDX_LargeKey_LargeFieldCheckSum
       on dbo.LargeKey(LargeFieldCheckSum)
       go
       /* select modified with checksum column */
       declare
       
       select @Param
       from dbo.LargeKey
       where Id
       /* Index Seek . Check Actual Execution Plan */
       select *
       from dbo.LargeKey
       where LargeField
       and LargeFieldCheckSum  = CHECKSUM(@Param)
       go
       --==============================================================
       =====
       *****************************************************