Columnstore Indexes and sp_spaceused

SQL Server 2014 supports clustered columnstore index.
Storage structure and updating/deleting data is quite different compare to normal tables.
It has concepts of deltastore which we will see in future articles.
Here I want to notice you behavior of clustered columnstore index while reporting table size.
Lets see an example for it

CREATE TABLE temp1

(id INT)

CREATE CLUSTERED COLUMNSTORE INDEX  idx1 ON temp1

DECLARE @i INT

SET @I = 1

WHILE @i <= 2000000

BEGIN

       INSERT INTO temp1 VALUES (@i)

       SET @i = @i+1

END

sp_spaceused temp1

Here we have inserted more 2 Million rows but it reports wrong number of rows and size.
So how to get correct data.
Even UPDATE STATISTICS and DBCC UPDATEUSAGE will not work. 


Accurate method to get space information  is alter table rebuild method.

ALTER TABLE temp1 REBUILD

Now we will run sp_spaceused again.

sp_spaceused temp1

This is very important and every developer has to keep in mind while handling large tables with clustered columnstore indexes