SQL Server Denali Column Store indexes

[SQLDenis]

They showed at PASS yesterday a scan against a 800 Billion table and it took only 19 seconds

You can download a PDF describing the columnar storage here: http://download.microsoft.com/download/ ... r%2011.pdf

[ramireddyindia]

Denis,

In that they mentioned its for datawarehouses only... Can we use for our normal OLTP applications also???

[SQLDenis]

Denis,

In that they mentioned its for datawarehouses only... Can we use for our normal OLTP applications also???

it is read only so no OLTP..remember storing data in a columnar table is more expensive than a row table, getting it out is much faster

[ramireddyindia]

Thanks Denis,

We are having an database, in which daily morning we will be importing 200000 records into single table. Can i use column store here if i use Denali? Do i need to setup any extra effort to use this column store like creating any fact tables etc etc???

[Thirster42]

They showed at PASS yesterday a scan against a 800 Billion table and it took only 19 seconds

You can download a PDF describing the columnar storage here: http://download.microsoft.com/download/ ... r%2011.pdf

i about pooed myself when i read that...

[SQLDenis]

Thanks Denis,

We are having an database, in which daily morning we will be importing 200000 records into single table. Can i use column store here if i use Denali? Do i need to setup any extra effort to use this column store like creating any fact tables etc etc???

The column storage is not available in CTP1, it will be part of CTP2 probably

[ramireddyindia]

ok...Thanks Denis...

Another question Denis

How you are playing with CTP from last 2 weeks, if it is announced only on monday?? Being a MVP, you will get additional privileges?

[Thirster42]

ok...Thanks Denis...

Another question Denis

How you are playing with CTP from last 2 weeks, if it is announced only on monday?? Being a MVP, you will get additional privileges?

Denis is privy to a lot of info that he can't tell us. He gets all the shiny things early.

[SQLDenis]

Being a MVP, you will get additional privileges?

Yes

[SQLDenis]

Think of it this way.... you have 10 years of data in monthly partitions..all you have to do is upload the latest data and then just switch in a partition


See page 7 of the pdf

[Thirster42]

Denis,

In that they mentioned its for datawarehouses only... Can we use for our normal OLTP applications also???

it is read only so no OLTP..remember storing data in a columnar table is more expensive than a row table, getting it out is much faster

If it is read-only, that implies that the indexes would need to be dropped and then rebuilt after a load of data? So in effect, this is not unlike processing a measure group after a data load in SSAS. And I would assume the equivalent to SSAS partitions would be to simply partition the SQL tables? If this is the case, it sounds like it could save some work if someone did not want to go down the SSAS route. However, the UDM and MDX are valuable enough in their own right to stick with SSAS for many.

To move data into a columnstore table you can switch in a partition, or disable the columnstore index, update the table, and rebuild the index.