Computed Columns can be stored in the data page in one of two ways. Either by creating them as PERSISTED
or by including them in the clustered index definition.
If they are included in the clustered index definition then even if the columns are not marked as PERSISTED
then the values will still be stored in each row. These index key values will additionally be stored in the upper level pages.
If the computed column is imprecise (e.g. float
) or not verifiable as deterministic (e.g. CLR functions) then it is a requirement for the column to be marked as PERSISTED
in order to be made part of an index key.
So to give an example
CREATE TABLE T
(
A INT,
C1 AS REPLICATE(CHAR(A),100) PERSISTED,
C2 AS REPLICATE(CHAR(A),200),
C3 AS CAST(A AS FLOAT) PERSISTED,
C4 AS CAST(A + 1 AS FLOAT)
)
CREATE UNIQUE CLUSTERED INDEX IX ON T(C2,C3)
C1
will be stored in just the data page rows as it is marked as
PERSISTED
but not indexed.
C2
will be stored in both the rows on the data page and the index higher levels as it is an index key column.
C3
will be stored as for C2
. As it is imprecise
it is a requirement to mark it as PERSISTED
however.
C4
won't be stored anywhere as it is neither marked as PERSISTED
nor indexed.
Similarly all computed columns referenced in non clustered index definitions as key columns need to be stored at all levels of the index as they are part of the index key. There is the same requirement regarding precise/deterministic results.
Fails
CREATE NONCLUSTERED INDEX IX2 ON T(A,C4)
With the error.
Cannot create index or statistics 'IX2' on table 'T' because the
computed column 'C4' is imprecise and not persisted. Consider removing
column from index or statistics key or marking computed column
persisted.
To include it as part of the non clustered index key it must also be stored in the clustered index data pages. However
Succeeds.
CREATE NONCLUSTERED INDEX IX2 ON T(A) INCLUDE (C4)
Computed columns that are only INCLUDE
d columns are persisted to the NCI leaf page and do not have the requirement that they also be persisted in the data page.
In there interest of showing a generalized example until we see your particular DDL and query, take the below as a basic example:
create table SomeTable
(
id int identity(1, 1) not null
primary key clustered,
AnotherInt int null,
SomeData nvarchar(1024) null
)
go
insert into SomeTable(AnotherInt, SomeData)
values(null, null)
go 1000
update SomeTable
set
AnotherInt = id * 3 + 152,
SomeData = 'My ID value is ' + cast(id as nvarchar(16))
So we have a test table, SomeTable, with a clustered index. Execute the below query:
select SomeData
from SomeTable
where AnotherInt < 200
This causes a Clustered Index Scan, like you are seeing:
So analyzing the query, we have the column AnotherInt that is in the WHERE
clause and being searched on. We are also retrieving the SomeData column, so to prevent a key lookup in the clustered index (or the optimizer may even just use a Clustered Index Scan again), we'll have SomeData as an INCLUDE
column:
create nonclustered index IX_SomeData_AnotherInt_SomeData
on SomeTable(AnotherInt)
include (SomeData)
go
Now, executing the same query above:
select SomeData
from SomeTable
where AnotherInt < 200
SQL Server will utilize that non-clustered index to return the data. It is a covering index, because it won't need to do a lookup on the clustered index for the remaining data:
Through creating a prudent NCI, we have now eliminated the CI Scan in lieu of an Index Seek.
Best Answer
There are many many many more factors at play.
I suggets you follow the procedure described in Capturing wait stats for a single operation to capture the wait stats of the scans you observed as blocking. Se if indeed they block on locks held by the table scan operation. IF the scenario is truly as you described (read scan vs. other read operations) then there is no reason for blocking so something else will be at play. You can also give sp_whoisactive a shot.