Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to try the column store index in SQL Server and im trying to get my queries to run in batch mode, i rewrote my query this way( it had an outer join before) but the execution plan still show that is running in row mode. Can anyone advice me in how to rewrite the query? i really thought this would work but it didnt.

WITH T (dim_facil_dkey, countDiv) AS(
SELECT f.dim_facil_dkey, COUNT(d.division)countDiv
FROM dbo.fact_orderstatus f
JOIN dbo.dim_divisions d on f.dim_divisions_dkey= d.dim_divisions_dkey
GROUP BY f.Dim_facil_dKey

SELECT DISTINCT f.dim_facil_dkey,
ISNULL (T.countDiv, 0) as countDiv
FROM dbo.fact_orderstatus f
LEFT OUTER JOIN T on f.dim_facil_dkey = T.dim_facil_dkey
ORDER BY ISNULL(T.countDiv, 0) desc

Thanks in advance!


migration rejected from Jul 30 '13 at 5:54

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Paul White, StanleyJohns, Mat, Mark Storey-Smith, RolandoMySQLDBA Jul 30 '13 at 5:54

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

Well, for one, do you need the DISTINCT? Also can you explain which of these tables has a columnstore index? – Aaron Bertrand May 14 '13 at 12:58
How big is the table -- no of records ? It might be the case as Row execution mode was selected because the table is not large enough to require the batch execution mode. – Kin May 14 '13 at 21:08
You need to validate MAXDOP as well. It may not kick in if MAXDOP = 1. – Chris J May 15 '13 at 15:52
This question is essentially answerable without an execution plan and/or table and index definitions. – Paul White Jul 30 '13 at 4:00