Microsoft SQL Server 2008 R2 Unleashed- P167:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1604 CHAPTER 42 What s New for Transact-SQL in SQL Server 2008 Now if you run another update but only specify a value for Weight in the XML string the Color column is set to NULL Update Product_sparse set ProductInfo Weight .10 Weight where productID 5 SELECT productID productName Color Weight SEllEndDate FROM Product_sparse where productID 5 go productID productName Color Weight SEllEndDate 5 ValveStem NULL NULL However if you reference the sparse columns explicitly in an UPDATE statement the other values remain unchanged Update Product_sparse set Color silver where ProductID 5 SELECT productID productName Color Weight SEllEndDate FROM Product_sparse where productID 5 go productID productName Color Weight SEllEndDate 5 ValveStem silver NULL Column sets are most useful when you have many sparse columns in a table for example hundreds and operating on them individually is cumbersome. Your client applications may more easily and efficiently generate the appropriate XML string to populate the column set rather than your having to build an UPDATE statement dynamically to determine which of the sparse columns need to be included in the SET clause. Applications might actually see some performance improvement when they select insert or update data by using column sets on tables that have lots of columns. Sparse Columns Good or Bad There is some disagreement in the SQL Server community whether or not sparse columns are appropriate. A number of professionals are of the opinion that any table design that requires sparse columns is a bad design that does not follow good relational design guidelines. Sparse columns by their nature are heavily denormalized. On the other hand many times you have to live in the real world and make the best of a bad database design Spatial Data Types 1605 that you ve inherited. Sparse columns can help solve performance and storage issues in databases that may have been poorly designed. Although sparse columns can solve certain kinds of .
