I earlier wrote an article about SQL SERVER – Fundamentals of Columnstore Index and it got very well accepted in community. However, one of the suggestion I keep on receiving for that article is that many of the reader wanted to see columnstore index in the action but they were not able to do that. Some of the readers did not install SQL Server 2012 or some did not have good machine to recreate the big table involved in the demo.
For the same reason, I have created small video for that.
[youtube=http://youtu.be/C-Ay6UxMfMo]
I have written two more article on columstore index. Please read them as followup to the video:
SQL SERVER – How to Ignore Columnstore Index Usage in Query
SQL SERVER – Updating Data in A Columnstore Index
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
It was a good demo on benefits of ColumnStore index. I have a few queries
a. Could you also explain when one should go for such an index?
b. is it only when I have aggregate functions used on my tables (in my stored procedures or queries)? or
c. does it provide benefit even if I don’tr have aggregate functions used?
d. Is the order of columns that make up the Index keys important in ColumnStore index also?
Thanks
Vijayaraghavan @ Chennai
Sir Please Guide me that I want to do certification in sql server so to do study and preparation of Exams
I want know about sql server certification
Greetings Sir,..
I faced these in interview,but still have confusions, pls let me clear..
* Will Group function ignore null values?
* Declaring a column (for which we gonna create index)as not null or using indexed column in queries often,which will improve the performance of an index?
*its commonly said that,views and synonyms will get dropped after dropping relevant table..but it becomes invalid and not dropped..only constrains,indexes will get dropped..Am i right??
Hi sir,
can you please guide me the process of index works,in the below is one scenario
as
CREATE TABLE DEPT(deptid NUMBER(4) PRIMARY KEY, deptname CHAR(10));
CREATE INDEX dept_deptname_idx ON DEPT(deptname);
/* The DEPT table contains 10,000 rows and the .deptname column contains 10 unique values that are evenly distributed. */
Based on the scenario above, which query needs to use the dept_deptname_idx index?
SELECT deptid FROM DEPT WHERE deptid 1234;
SELECT deptid FROM DEPT WHERE deptname = ‘SALES’;
SELECT DISTINCT deptid FROM DEPT;
SELECT deptid FROM DEPT WHERE deptname ‘SALES’;
SELECT deptid FROM DEPT WHERE deptid < 1234;
please answer it and give me the how and why is pocessing
regard
Hi sir,
Here is one more scenario as
You have a Table called Employees with following Columns
EmployeeID INT
EmployeeName VARCHAR(256)
Salary MONEY
DeptID SMALLINT
The table has following Indexes created on it
1. Clustered Index on EmployeeID
2. Non Clustered Index on EmployeeName
3. Non Clustered Index on DeptID
The Employees tables has over 10 million rows.
You want to get the count of Employees, for which you write the following query
SELECT COUNT(*) FROM Employees
What will be the likely query plan for above query? Which index will SQL SERVER use in above query? Please give an explanation for your answer.
please help me in detailed manner
regard
Hi,
That was really informative messages. Thank you very much sharing.
I’ve created NonClustered Index (IDX_x) and COLUMNSTORE INDEX (idx_csi).
Once I run SELECT query with NonClustered Index, the query takes 30 secs to execute and COLUMNSTORE INDEX takes around 2 seconds to execute.
But, if I try to run the NonClustered Index once again, surprisingly it takes only 2-3 seconds to run the query. So my question is once COLUMNSTORE INDEX is created and ran, is there any mechanism which stores the searched data in memory and will be used later if required. Let me know how it works.
Thanks,
Santosh
Hi Sir,
I need your help, here is my query, it is slow first time
SELECT COUNT(1) AS Results FROM ImportShipmentNew2013
WHERE CONTAINS(ProductDescription, ‘neem’) –AND (@HSCode=” OR HSCode = @HSCode )
GROUP BY ForeignCountry
(69 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ImportShipmentNew2013’. Scan count 0, logical reads 721107, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Please suggest to index create to improve performance