Jerry Dixon

For most of this year, I've been giving presentations and teaching classes on various aspects of Visual Studio 2005 and SQL Server 2005. In my consulting role, I've been constantly presented with problems that, while definitely solvable with current tools, simply screamed for the new products. Alas, like most of you, I haven't been in a position to use these tools in a production environment. Now the wait is over. By the time you read this, production versions of both products should be available to the general public. Now all we need is to help each other learn how to make the best use of the new and improved features that these products provide. That's where .NET Developer's Journal comes in. The good folks at the .NETDJ have agreed to publish a series of articles in a new SQL Server 2005 column. Each month I will explore a different feature of the product, conce... (more)

Create XML Easily with FOR XML PATH

Do you love XML? Have you been using XML with SQL Server? Many people have, starting way back when with SQL Server 7.0. Back then, there was no support for XML in the database, so we had to write external programs to convert the relational data into an XML format. This was time-consuming and often inefficient. When SQL Server 2000 came out, with its integrated support for XML, there were a lot of high expectations. Unfortunately, XML still couldn't be easily stored in the database, although it could be created and consumed. XML could be stored in the database as a large string of... (more)

A Point of Order: Organize Your Data with SQL Ranking Functions

Sequential numbers. Sooner or later, you'll find a process that requires you to generate a series of sequential numbers. As a matter of fact, most database engines provide at least one method of producing such numbers. Microsoft Access, for example, has AutoNumber columns, while SQL Server has IDENTITY columns. However, these are features of tables. There are times when you need some sequential numbers, but creating a table is inconvenient or inefficient. So how does one proceed? How does one obtain a list of sequential numbers without creating tables? SQL Server 2000 has always... (more)

DDL Triggers

A number of years ago, I worked for a company that was developing internal applications with SQL Server 2000. We didn't have any real database administrators. I was given that responsibility, but I was really a developer, one of many in fact. The problem with this arrangement was that all the developers had high-level rights to SQL Server. Each one could make whatever changes seemed appropriate to him at the time, and they could do this without consulting me. This often resulted in data incompatibilities that caused a bit of downtime, but lots of irritation. One day, however, my ... (more)

SQL Server 2005 Service Broker

In today's complex and demanding environments, it is quite common for users to be able to submit requests faster than those requests can be processed. In some situations, this is because the system has an enormous number of users. In other situations, it is because the requests take a long time to process. In both cases, the system needs to be designed so that it can accept the requests immediately, and process those requests later on. Such a system is said to be asynchronous. Asynchronous systems are typically built around queues. Queues function just like those long lines at t... (more)