Increased limits for columns / indexes / statistics

I assume this went up too?

Columns per SELECT statement 4,096

Columns per INSERT statement 1,024

How else would you insert?

The only use I see is to store some monthly sales data transposed back to 2000BC  :-)

Imagine you need only half the columns of that table? Good luck with that SELECT statement

The intention of sparse columns is not for typical OLTP databases, but for applications that add thousands of user-defined (or occasional) columns - SharePoint docs, document management, etc. A sparse column should be empty for most rows - without having to store a null.

Internally a spase column is a lot like a entity-value pairs pattern, but handled by the storage engine instead of procs and functions. So it's not really a new feature for our apps, but instead moving the capability to deeper within the engine.

Get ready for SELECT * and XML manipulation...

If you listen to

SQL Down Under show 35 - Roger Doherty - SQL Server 2008 for Database Developers

you will find out that it is all SharePoints fault  :-)

Its all about Sparse columns and they are a great feature of Katmai Imagine you have a products table,

I think alot of changes in MS Db's is done to keep with other db's feature sets, not

whether its useful or not.  This above comment will probably keep me off

their evangelical hr lists.

I woud have preferred larger row space.

I hate the R.

But 2k8 could be part of the effort to build db features into OS's, the table columns would probably need to match the file 'aspects' options in vista.  

If you look at the variables there is quite the number of options, from what was the favourite colour of the author, to what the price of tea was when the file was saved.

E

Hi Aarton,

/*

This room comes at a cost, though.  For tables with sparse columns, the maximum row length drops from 8,060 bytes to 8,018 bytes.

*/

I guess its not true. The total data space occupied by sparse columns should be less than 8018. However, table's row can be greater than 8018 but less than 8060 as usual. The script provided below will show the same.

drop table [sparse_col]

GO

CREATE TABLE [dbo].[sparse_col](

[dt] [datetime] NOT NULL,

[value] [int] NULL,

data char(520) null,

sparse_data char(7500) sparse  null,

)

GO

insert into [sparse_col] Select GETDATE(),0,'char','sparse data'

GO

select * from sys.dm_db_index_physical_stats(7,null,null,null,'Detailed')

where OBJECT_NAME(object_id) like '%sparse%'

Go

Regards,

Raj