MS SQL Server in Particular, Databases in General

The minty freshness of my database knowledge is suffering under the weight of my grand (and successful) five-year plan to switch from a COM world of Microsoft Access and ASP to the new world of .NET and ASP.NET. Tangentially, these are the highlights of this remarkable transformation:

• Leverage knowledge of MS SQL (used with MS Access) to move to t-SQL (used with MS SQL Server). This move took place almost ten years ago.

• Pay respect to the ANSI SQL Standard (1992) and demonstrate this respect by buying and reading/studying The Practical SQL Handbook. This book, by the way, is written by three women, Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky.

• Leverage knowledge of VBA (used with MS Access) to move into VB6 and ASP (with VBScript). This move which also close to ten years old was my first major move away from the billion-dollar ‘nursery’ of the Microsoft Office into “real” programming.

• Leverage knowledge of DAO and a COM-based generic data access layer to build a similar generic layer for ASP in VBScript. This was my first attempt at building a framework (although this word was unknown to me at the time). This attempt was very successful. It has literally made me and my broken family several hundred thousand dollars (spread out over upwards of five years). Code written with this framework is still in use today.

• Leverage almost nothing to move into the OOP world of .NET. My deep respect for Java theory was my best helper to get me into .NET. Yes, Visual Basic 6 had some object-oriented concepts but it really was not enough (for me).To try to summarize my journey into .NET from my little ASP cash cow to the space-time location called now would take too much space for this Blog post. But what we can see is that my SQL Server knowledge has not moved (very much) from 1992. In fact, according a Wikipedia.org article there have been three SQL standards since my salad days in the 1990s. What’s really sad is my complete unawareness that the SQL 2003 standard formalized the use of XML. My assumption was that XML-related features just showed up in the popular databases because they were “cool” and useful. So here is a list of other bits that’s news to me:

• From in June 2005, “Maximize large data with the new MAX data types” shows that the technical gap between the 8000-character-limit in varchar and the binary text data types has been closed with varchar(MAX).

• VistaDB for .NET feels like SQLite for anything written in C. The assumption here is that is not intentionally ‘crippled’ like the smaller SQL database from Microsoft—like SQL Server 2005 Mobile Edition. Too bad there’s no open source version of this… yet.

• I simply forgot about the drawbacks mentioned in “SQL Server: Advantages and Drawbacks of User-Defined Functions.” This cost me about two hours last week. I know I forget because I’m sure I read “Creating User-defined Functions.”

• My searching for a finite set of unknowns with a single select statement trick does not scale.

• It is possible to run SQL 2000 DTS packages connecting to SQL 2005 databases. It’s just that Microsoft does not celebrate this with a summarizing webcast or MSDN article about “interoperability.” Another one of those “low-priority,” exotic edge cases…

rasx()