Articles

• Delivering on SQL Server: A frank, honest and open discussion on what it takes to deliver a successful solution on SQL Server.
• SQL Server 2000 Partitioned Views: Partitioned views are a feature in SQL Server 2000 that allows an additional level of abstraction to be introduced in between the orthogonal view of the data and its storage in an underlying table. In this article we explore what a partitioned view is, how it is created and accessed and, most importantly, what advantages it brings to the table.
• SQL Server 2000 Hash Indexes: Hash indexes bring together a number of SQL Server 2000 features to deliver a technique to allow the indexing of wide columns.
• When a String Is Not a String: This entry was inspired by a seemingly innocent bit of code that caused a performance problem in a very unexpected way. It is the kind of code that you'd find in many samples. Unfortunately, it has a potential flaw that is only exposed when large data volumes are present.
• Covariant Generic List: The introduction of generics in .NET 2.0 has opened up a whole new approach to class design in C#. The first, and most obvious, benefit generics provide is strongly typed containers. We no longer have to keep casting, boxing / unboxing, to / from object for ArrayList items anymore. However, with the power of generics come a few limitations. This article explores some subtle restrictions in covariance with generics.
• Partitioned Views on a Budget: Partitioned views are a great feature of SQL Server. By injecting an additional layer of abstraction between your logical view and physical implementation they give you fine grained control over data partitioning. However, the cover charge required for entry to this exclusive club is pretty high. The checklist required to implement a partitioned view can often interfere with your design to such a degree that they become more of a hindrance than a benefit. However, by decomposing the features SQL Server uses to implement the complete partitioned views specification we can get most of the benefits without meeting all of the criteria.
• Indispensable SQL CLR functions: Since the first CTP of SQL Server 2005 I have been building up a library of SQL CLR utilities. Some were just experiments to find the limits of the technology. Others have had tremendous longevity and are part of my daily TSQL repertoire.

Discover SQL Server 2005

Articles examining enhancements to database engine fundamentals that can bring the most benefit to new and existing database systems.

• The INTERSECT and EXCEPT Operators
• The NTILE Ranking Function
• The ROWNUMBER Ranking Function
• The RANK and DENSE_RANK Ranking Functions
• The OVER Clause Part I
• The OVER Clause Part II
• TRY...CATCH
• Snapshot Isolation Part I
• Snapshot Isolation Part II
• Dynamic Management Views (DMVs) Part I
• Dynamic Management Views (DMVs) Part II
• The OUTPUT Clause
• VARCHAR(MAX)
• Introducing Common Table Expressions (CTEs)
• The APPLY Operator
• The PIVOT Operator
• The UNPIVOT Operator
• The TABLESAMPLE Clause

Unless otherwise specified, all of the samples make use of the AdventureWorks sample database that is included in SQL Server 2005.