T-SQL Tidy : Home
T -SQL Tidy takes your messy unformatted T-SQL statements and formats it into nice
Enter your SQL in the box below or click Paste.
Thursday, July 11, 2013 1:00 PM
Question: When is a columnstore index not a columnstore index?
Answer: When its a rowstore index. or When its not been rebuilt. Explanation: The answer is on MSDN – “The updateable columnstore first inserts new data into an OPEN rowgroup, which is in rowstore format” –> Yes, rowstore, not column store. MSDN says re-organising the index will put it in columnstore format, but in CTP1 this doesn’t work. You need to rebuild the index, not a problem because we are using developer edition (or enterprise edition). I’ve blogged here what happens when you insert into a column store index here
Monday, July 08, 2013 5:55 PM
Column Store Index Life Cycle
In my series on the Column Store Index, I thought I would blog on what happens when you insert/ delete into a column store index. 1) Lets insert some rows into a table with a column store index. 2) Rebuild the Index. The state of index changes to Compressed from Open and size_in_bytes is populated. 3) Insert some more rows. We now have two rows, one ‘OPEN’ and second ‘COMPRESSED’ 4) Delete some rows. The deleted rows appear to have come from the ‘COMPRESSED’ row. 5) Rebuild the index again. 6) When I updated 1 row in the COMPRESSED table, the engine deleted the row and added a new row in the OPEN / delta table. The page on MSDN about the sys.column_store_row_groups system table http://msdn.microsoft.com/en-us/library/dn223749(v=sql.120).aspx , explains why the 2nd table / delta table or delta row set is used , how and when data is moved from open of the threee states; ‘OPEN’,’COMPRESSED' and ‘CLOSED’ – I’ve not seen ‘CLOSED’ yet.
Monday, July 08, 2013 5:26 PM
Clustered Column store index recap
Wednesday, June 26, 2013 2:24 PM
Very interesting things about updateable column store indexes
Wednesday, June 26, 2013 2:06 PM
More interesting things about the Clustered Column store index..
Wednesday, June 26, 2013 12:50 PM
Interesting thing about updateable Clustered Column Store Index
Monday, June 10, 2013 12:03 PM
Going to SQL Relay?
Usage Stats: (Tidies)
Last 7 days:
Last 30 Days: