Segment Elimination when VARCHAR(MAX) is included – Columnstore index

[Dave]

Hi All

Does anyone know if it’s possible to get a query to run in batch mode if you include a VARCHAR(max) in the select clause from the table with the columnstore index?


create table CSTest (Attrib1 int,Attrib2 int, Attrib3 varchar(max))

create columnstore index csind on CSTest(Attrib1,Attrib2)

SELECT Attrib1,Attrib2,Attrib3
FROM dbo.CSTest
WHERE Attrib1 IN (3,4,6,7)
AND Attrib2 IN (33,55,77,88)

I have tried everything I can think of and can’t seem to get it to work.


with(index=csind)
OPTION(querytraceon 8649)
OPTION(hash Join)

[Dave]

Post by Dave
Hi All
Does anyone know if it’s possible to get a query to run in batch mode if you include a VARCHAR(max) in the select clause from the table with the columnstore index?
create table CSTest (Attrib1 int,Attrib2 int, Attrib3 varchar(max))
create columnstore index csind on CSTest(Attrib1,Attrib2)
SELECT Attrib1,Attrib2,Attrib3
FROM dbo.CSTest
WHERE Attrib1 IN (3,4,6,7)
AND Attrib2 IN (33,55,77,88)
I have tried everything I can think of and can’t seem to get it to work.
with(index=csind)
OPTION(querytraceon 8649)
OPTION(hash Join)

script to generate data


--DROP INDEX CSTest.csind

SELECT 1 X INTO #T FROM SYS.OBJECTS

INSERT INTO CSTest WITH(TABLOCK)
select checksum(newid()),checksum(newid()),newid()
from #T a,#T b,#T c

create columnstore index csind on CSTest(Attrib1,Attrib2)

[Erland Sommarskog]

Does anyone know if it?s possible to get a query to run in batch mode if
you include a VARCHAR(max) in the select clause from the table with the
columnstore index?

varchar(MAX) as such does not really matter; it doesn't get any better
with varchar(8000). Except that in the latter case you can add the column to
the CS index.

I looked at
http://social.technet.microsoft.com/wiki/contents/articles/3540.sql-server-
columnstore-index-faq-en-us.aspx#Batch_mode_processing
and it says:

What query execution plan operators are supported in batch mode in
Denali?

Filter
Project
Scan
Local hash (partial) aggregation
Hash inner join
(Batch) hash table build


And in this case the plan will always been a Loop Join with a RID lookup;
don't think you can get a hash join for a key lookup. So that would be the
answer.