And the EAV winner is …. sparse columns

Many of you have already heard the "hardware store" story. What's the best way to model products in a hardware store, where new items arrive at the hardware store each day. Each item has a "short list" of similar properties (e.g. UPC, price) and a long list of dissimilar properties (e.g. paint has color, type, […]

ORDPATH, ORDPATH, everywhere

ORDPATH is a hierarchical labeling scheme used in the internal implementation of the XML data type in SQL Server 2005. It's meant to provide optimized representation of hierarchies, simplify insertion of nodes at arbitrary locations in a tree, and also provide document order. It's described in the academic whitepaper "ORDPATHs: Insert-Friendly XML Node Labels". In […]

SQL Server system data types implemented in .NET

SQL Server 2008 will contain, if my count is correct, 7 new data types. Note: none of these are in the current CTP. DATE – ANSI-compliant date data type TIME – ANSI-compliant time data type with variable precision DATETIMEOFFSET – timezone aware/preserved datetime DATETIME2 – like DATETIME, but with variable precision and large date range GEOMETRY […]

A supported Service Broker external activator?

Had to write about another thing that "caught my ear" at TechEd during a chalktalk by Rick Negrin about Service Broker usage patterns. Service Broker supports "internal activation", that is, associate a stored procedure that gets invoked when a queue has messages to process as well as "external activiation". When using external activation, an event […]

Table valued parameters in SQL Server 2008

The June CTP of SQL Server 2008 contains support for table-valued parameters. Here's a usage scenario for these that has been around for a while. Imagine you are running an online store and deal with (among other data) orders and order detail lines. You'd like to have a stored procedure that can add an entire […]

Grouping sets and TABLIX – made for each other?

SQL Server 2008 will include GROUPING SETS; a (very) short explanation is that these allow the equivalent of multiple GROUP BY clauses in a single SQL statement. The result is a UNION ALL of the resultsets. SQL Server 2008 also contains/allows standard syntax for ROLLUP and CUBE, which have been in SQL Server for a while. […]

SQL Server 2008 new features – the list

There was a fairly well-known Powerpoint slide that attempted to summarize the new SQL Server 2005 features in bullet points of a single slide. By the release, there were so many new features, the feature list had to be rendered in a 5-point font to fit. At TechEd 2007, Microsoft presented the new features of SQL […]