SELECT * FROM [Musings]

Database matters.

October 2006

Here is a good discussion of some of the unexpected results you can get using TOP vs. MAX, as well as SET vs. SELECT.

I have noticed some of the issues discussed, but had never actually tried to track down exactly why the result is not what I expected. This entry also links to Tony Rogerson's entry on the same topic.

Posted by Douglas Reilly at 09:57 AM  Permalink |

I do not have a machine to spare that I run beta stuff on just now, and so I have not played with the Visual Studio 2005 Database Professional Edition.

Fortunately, Kent Tegels has a nice entry with a list of improvements in the latest CTP (Community Technical Preview) here. I am glad they are making progress, and will likely install the released version as soon as it is ready.

Posted by Douglas Reilly at 11:49 AM  Permalink |

I am in the process of reading a book on database refactoring - Refactoring Databases - Evolutionary Database Design

Before I get too deep into this book, I wanted to talk about how I currently manage refactoring in the database work that I do. This is a nuts and bolts implementation explanation rather than a generic, more globally applicable method.

First, I generally work in SQL Server, and virtually always use stored procedures when accessing a SQL Server database. I initially did so because a boss I worked for insisted upon it, however in the longer term, I discovered that by using stored procedures, I could mask changes in the database. That is, I could change internal implementations, yet keep the public interface the same so that any applications using the stored procedure could continue to work. For instance, imagine I have the following stored procedure (greatly simplified):

CREATE PROCEDURE spInsertCustomer
@CustomerName nvarchar(120),
@CustomerAddress nvarchar(120),
@CustomerPhone nvarchar(120),
@CustomerEmail nvarchar(120)

Now, imagine that I wanted to add a field to the Customer table indicating if the customer location is handicapped accessible. I have a number of options. First, I could allow the HandicappeeAccessible column to accept nulls or provide a default value. One other option, which would allow applications using the database to insert customers with the new column if they like, would be to change the procedure so that its declaration is as follows:

CREATE PROCEDURE spInsertCustomer
@CustomerName nvarchar(120),
@CustomerAddress nvarchar(120),
@CustomerPhone nvarchar(120),
@CustomerEmail nvarchar(120),
@HandicappedAccessible bit = 0

Since @HandicappedAccessible has a default value, existing programs can continue to call the stored procedure with the original 4 parameters, while new or modified code could add the fifth parameter.

There are other circumstances where you might need to change the interface to a stored procedure. For instance, you might need to retrieve more than a single recordset in a SELECT procedure, or select columns that are named differently. In this case, my solution is to actually create a second stored procedure, with the original name of the stored procedures, with "Ex" or perhaps even "2" appended to the stored procedure name. Thus, older conde continues to work, while newer code can take advantage of the new features added to the "Ex" or "2" version. Sometimes, you may even need to change the internal workings of the original stored procedure so that it's interface is the same as it was before whatever underlying change to the database took place.

So, now that you know how I currently do database "refactoring," it will be interesting to revisit this post after reading a more formal book covering refactoring databases.

Posted by Douglas Reilly at 09:44 AM  Permalink |

Per Jonathan Cogley's blog, a link and info on www.mssqltips.com.

There are lots of tips, eith one being added every few days. Example tips: detail the difference between DELETE and TRUNCATE and desceibe how and why you would use multiple instances.

Posted by Douglas Reilly at 05:23 AM  Permalink |

Craig Gemmil blogs about a SQL problem that I have seen in a number of ASP.NET applications, especially those written by folks new to ASP.NET

Years ago, SQL Connections were very expensive to create and tear down, and so on a fat client application, you would often find a way to open a connection and share it among all the code in the application. Making this work was the fact that while a connection could actually only do one thing at a time, classic ADO would open additional underlying connections as needed.

ASP.NET (and Web apps in general) are different. Because Web applications are, under it all, stateless, data access in the ADO.NET Web world is designed to be mostly disconnected. A user requests a page, the page load event opens and closes any connections it needs (opening ass late as possible and closing as early as possible). Connection objects should be declared in the narroest scope possible, and should always be closed before the scope is exited. Opening connections is not as expensive as it used to be, since ADO.NET, by default, uses connection pooling. Connection pooling keeps underlying physical connections to the database open even after the high level connection object has closed. Then the underlying connection can be used in a subsequent connection open request.

The problem in question was caused by a developer declaring a connection as static. Thus the second user would end up resetting the connection, killing off whatever the first simultaneous user was trying to do. Again, connection objects should not be declared at application scope or made static, since this will allow multiple requests to try and simultaneously use the same connection. A very bad thing...

Posted by Douglas Reilly at 10:28 PM  Permalink |

SQL Refactor from Red Gate is now available, in beta.

Refactoring, especially when enabled by visual tools, is a big deal these days. Visual Studio has it for C# code, and now the SQL Refactor tool from Red Gate will bring it to SQL Server.

Posted by Douglas Reilly at 01:30 PM  Permalink |