SQL Server 2008 Column Sets

We're back on the 2008 topics here at the W, talking about column sets.  Column sets extend sparse columns allowing you another mechanism to query your desperate data.

A column set is implemented as an XML column on your table.  For this example I will use the same table in my earlier sparse column post.  Here is the syntax:

IF OBJECT_ID('dbo.ProductSparse', 'U') IS NOT NULL DROP TABLE dbo.ProductSparse; CREATE TABLE dbo.ProductSparse ( ProductID INT IDENTITY(1,1) ,[Description] VARCHAR(100) NOT NULL ,Col02 INT SPARSE NULL ,Col03 INT SPARSE NULL ,Col04 INT SPARSE NULL ,Col05 INT SPARSE NULL ,ProductSparseSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS );

Now we will insert some data.  The script below will create 4000 rows in our table spread across the various sparse columns.

--Insert some data INSERT dbo.ProductSparse ([Description], Col02) VALUES ('aaa', 1); INSERT dbo.ProductSparse ([Description], Col03) VALUES ('bbb', 2); INSERT dbo.ProductSparse ([Description], Col04) VALUES ('ccc', 3); INSERT dbo.ProductSparse ([Description]) VALUES ('ddd'); GO 1000

If we query the table with "SELECT *" we will get back the column set only, all our SPARSE columns will be obscured.  Here's a snippet of our table:

SELECT * FROM dbo.ProductSparse;

Another nice touch of the column set is that we can use our normal means of dealing with XML data for this column.  We can insert or update data as easily with the column set XML as we can the actual column(s).  One caveat -- if you do use the XML for updating sparse columns any column not referenced in the update will be set to NULL.

While this feature was implemented mainly to support future SharePoint functionality there are many business cases in the OLTP world that can benefit.  Product catalogs, census style information or anything else where desperate data exists can be more successfully modeled with sparse columns.


Posted by: whitneyw
Posted on: 5/28/2008 at 11:22 PM
Tags:
Categories: Blog
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

When checkboxes attack

I ran across a crazy behavior in the SQL Server 2008 CTP 6 tools around changing a table via the "Design" option in Object Browser.  I typically script all changes to objects as I am not a fan of GUI development in general (and this may have cemented the opinion forever).  Maybe I was just in a hurry today, but I connected to a database to add a column to an existing table and was presented with the following ugly message box:

I knew this wasn't permissions related as I had been happily altering schema in this instance for weeks (even a couple stored procedures that day).  So what's a geek to do when his path is blocked?  Dig into the options!  So away I went down the Tools--> Options--> Designers path.  And there it was, staring me in the face...

A rather odd little checkbox with the text "Prevent saving changes that require table re-creation".  I was positive I hadn't seen this before, so positive I fired up SQL 2005 SSMS to compare, and found that it truly was new functionality.  For the life of me I'm not sure why this was added as a default behavior.  Especially one that might override the security privileges of a user.

The only documentation I found online for this is from the MSDN SQL Server Developer site.  There was one Connect item with a Microsoft comment referencing the fact that it is on by default, I'm assuming the Change Data Capture feature is the answer.  I've got some emails out to see what the true intention was here.

So if you see the nasty pop up above while working in your CTP you will now know that you've been attacked by a checkbox.  ;)

 

UPDATE:  I got an email back from Buck Woody of the SQL team right after posting last night with the purpose for this checkbox, you can check it out here.  Thanks Buck!


Posted by: whitneyw
Posted on: 5/28/2008 at 11:20 PM
Tags:
Categories: Blog
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

SQL 2005 DMV Slides and Code Samples

You can find the slides and code samples from my talk last night at the Atlanta.MDF on my SkyDrive.  Thanks to all that came out last night.  We had some great questions and a lot of good conversation afterwards.


Posted by: whitneyw
Posted on: 5/14/2008 at 11:23 PM
Tags:
Categories: Blog
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

Atlanta.mdf meeting 5/12

I will be speaking at the Atlanta.mdf user group this coming Monday, on the topic of Dynamic Management Views in SQL Server 2005.  If you are in the perimeter area stop by, the meeting starts at 6:30PM with food and drinks provided.


Posted by: whitneyw
Posted on: 5/9/2008 at 11:24 PM
Tags:
Categories: Blog
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

GGMUG Inaugural Meeting

The first meeting of the Gwinnett Georgia Microsoft User Group will be held tomorrow night at Gwinnett Technical College.  I am always happy to see new user groups pop up, especially ones that are close to home.  ;)

Magenic will be sponsoring the food tomorrow night.  If you will be in the area swing by and check it out.


Posted by: whitneyw
Posted on: 5/8/2008 at 11:25 PM
Tags:
Categories: Blog
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

Backward compatibility for IntelliSense please

I had an interesting email thread on Friday with Eric Kang, a Program Manager in the SQL Server team, about the IntelliSense feature in SQL 2008.  Eric has been gracious enough to let me post the contents of this thread here.

The conversation related to what I felt was misinformation in various places about whether or not IntelliSense would be backward compatible.  Currently, CTP6 will support IntelliSense against a SQL 2005 or SQL 2000 instance.  This was exciting to me as I tend to work in multiple revs of the product.  As it turns out, the RTM version of SQL 2008 WILL NOT support this behavior.  Here is Eric's statement to why that is the case:

CTP5 and CTP6 IntellliSense did not properly check the server version but allowed itself working on SQL Server 2005 (or any downlevel version).

Unfortunately, it was a known defect since core modules were designed explicitly for SQL Server 2008 and later versions. #1 goal of IntelliSense is to help users authoring Transact-SQL script more efficiently so that increase the productivity. However, IntelliSense could cause negative impact on 2005 or any unsupported versions by incorrectly suggesting syntax error or suggest things that won't actually execute. This case, users will spend more time to figure out the truth.

Although IntelliSense provides an application level feature, the underlying implementation is similar to rebuilding the server side lexer, parser binder in managed code base. The scale of project is more likely catching up the full scope of what SQL Server has implemented for many years. Adding support for downlevel version would multiply this scope of work.

Our team envisioned to invent a client side framework and platform that understand syntactic and semantic context of Transact-SQL script that enable many *intelligent* applications and tools. In SQL Server 2008, IntelliSense and Upgrade Advisor are good examples. By doing so, not only Microsoft product but also Microsoft partners and vendor could take advantage of it in the future and provide such intelligent applications to customers overall.

As a part of the roadmap, the business decision was first to enable core Transact-SQL language constructs such as query and DML statement for SQL Server 2008. We still have a long way to go to accomplish our vision but we are getting there and team has multiple project charters to enable more language coverage in versions to come.

Hope this helps understand the reason why we had to restrict the version support to SQL Server 2008 only.

While I appreciate Eric's description and understand his reasoning, I'm still disappointed.  I had been using the 2008 tools against two client's SQL 2005 instances with great results.  Giving this up will be painful as I have really enjoyed the experience of IntelliSense in SSMS.

If you agree with me on this point please go to Connect and vote for my suggestion that backward compatibility be included in the feature.  Hopefully this could find it's way into a service pack or other release.

I want to thank Eric again for letting me post part of our conversation.  Eric's job of implementing IntelliSense in SQL Server is no small task.  I am happy to see this feature make the product, it already has a better feel than many of the other add-ins on the market.


Posted by: whitneyw
Posted on: 5/4/2008 at 11:27 PM
Tags: ,
Categories: Blog
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed