An interview with Adam Machanic

 

by Douglas Reilly

I have never met Adam Machanic, but I regularly read his blog as part of the SQL Junkies RSS feed. He is one of the authors of the recently released book Pro SQL Server 2005, in which he contributed chapters covering T-SQL for DBAs, .NET integration, and programming assemblies. He also contributes articles to SQL Server Professional.

Adam is a Microsoft MVP and a Microsoft Certified Professional. He speaks at Code Camps and has just become an independent consultant for SQL Server.

Adam answered the following questions via email.

 

Doug: How did you become involved in database development? Was it an accident or something you had planned?

Adam: It was by accident. I studied computer science as an undergraduate, and the university didn’t even offer a database course. I was learning foundations-algorithms, data structures, etc., and I didn’t understand why anyone would want to use a database. I assumed that if I needed to store data, I would code my own storage engine. I remember telling a friend that I would never work with something as boring as a pre-built database!

Then I entered the real world. I got my first job, which involved database work, and I was instantly hooked.

 

Doug: How did you learn about databases, and how do you keep current?

Adam: By doing lots of reading, experimentation and, whenever possible, work on interesting database projects. I’ve been fortunate that my recent positions involved research and proof of concept, which gave me time to read and learn.

But my secret weapon is my participation in newsgroups and forums. Answering questions online exposes me to a variety of technical and business problems that I would never see in my day-to-day work; it has rounded out my skill set.

I also read whatever database-related material I can get my hands on, not just SQL Server stuff. I just finished C.J. Date’s latest book, Database in Depth, which I highly recommend.

 

Doug: Doing a book on a beta product can be a real challenge. How did you find writing about SQL Server 2005 as it moved from beta to released product?

Adam: One word: nightmare.

My main contribution to Pro SQL Server 2005 was content on SQLCLR integration, and that area changed dramatically from CTP to CTP. I pushed back on my deadlines so many times that I was waiting by the phone expecting an angry editor to tell me that I was fired from the project. But the editors at Apress were very understanding. They’ve been through this before, and in my opinion they are great to work with.

 

Doug: People dream about writing all or part of a book. How did you happen to co-author Pro SQL Server 2005?

Adam: I’d been purchasing Apress books for a while; I’m definitely a fan of their style and technical content. But a couple of years ago, I purchased an Apress book on ADO.NET. I won’t mention the title, because it is the worst technical book I’ve ever read. It was horribly written and edited, and technically inaccurate in many areas. It has since been discontinued.

So I dashed off an angry letter to Gary Cornell, the CEO of Apress. I didn’t expect to receive a reply, but to my surprise he wrote back almost immediately, apologized, and asked if he could send me a free copy of another book to make up for the inconvenience. We emailed back and forth a few times, and he said that since I was so opinionated, I should try tech reviewing. He then introduced me to Tony Davis, the database editor, which led to my involvement with the book.

Moral of the story: If you don’t like something, you might get a chance to change it if you step up to the plate.

 

Doug: Have you done much with XML?

Adam: I have done a lot more than I like. If you read some of my writing, you’ll understand that I’m not a fan of what XML has become. XML is a document interchange format, and I think it’s wonderful in that regard. But when you try to mold it into a data management format, you suddenly run into all sorts of issues.

Using XML for data management is like jumping in a time machine and going back to the days of hierarchical DBMSs. We’ve been there, and we’ve seen the problems and ambiguities. So why go back? It’s unfortunate that many IT professionals are so affected by marketing that logic and common sense are ignored.

 

Doug: One of the bigger controversies in the SQL Server world is whether to use stored procedures or dynamic SQL statements. Where do you stand? Is it an all or nothing thing for you?

Adam: There is no question for me: Stored procedures are the only way to go. I’ve been involved in a lot of debates on this topic, and unfortunately they usually end up focused on the wrong issue, which is performance. Performance is important and, under the right conditions, stored procedures and ad hoc (parameterized) SQL can be shown to perform almost equivalently. But the real issue is flexible software design.

By relying on ad hoc SQL generated from an application tier, developers tightly couple the application to the database schema. A change to the schema or a change to the data format in the database requires a change to the application. Likewise, all hope of encapsulation of data logic is lost. The application must “understand” the correct way to massage the data into various formats to send queries to do so.

Stored procedures eliminate these issues. Developers should learn about contracts and interfaces. A stored procedure should provide a consistent interface-that is, a consistent set of inputs and outputs. As long as the stored procedure provides that same interface, it is said to abide by the contract it has with the application.

Once that contract has been established, the developer can make any change necessary within the database-schema, data format or performance modifications, for instance. As long as the stored procedure’s inputs and outputs remain consistent, the application will not need to be modified.

 

Doug: When you program in .NET, what is your favored language?

Adam: I really prefer C#. I’ve been programming in C and C++ for quite a while, and C# is a natural progression from there. I was briefly a VB programmer, but I never learned to see BEGIN and END the same way as curly-braces. To me, C# code has a certain Zen minimalism compared to VB.NET code.

Doug: What sort of work are you doing now?

Adam: I’m doing a lot of writing. With the recent releases of SQL Server 2005 and Visual Studio 2005, publishers are rushing to get as much content out the door as possible, as quickly as possible. So it’s a good time to be a writer!

I’m also doing some performance tuning and architecting a reporting system for a client. I have them almost convinced to adopt SQL Server 2005, so I have high hopes for that project.

 

Doug: When I first heard about using the CLR for stored procedures and triggers, I was excited. When I learned the process for adding and maintaining them, however, I was less excited. What do you think about using VB.NET or C# for stored procedures?

Adam: I’m not enamored of the idea. The real power of CLR integration is user-defined functions.

Stored procedures in SQL Server are great for data access, but they are not especially flexible. They can’t be called easily for each row of a resultset, for instance. Functions, on the other hand, promote encapsulation and reuse of logic. They can be used anywhere, and thanks to the CROSS APPLY operator in SQL Server 2005, even table-valued functions can be called row by row.

As for the SQLCLR data access capabilities, I haven’t seen many good-use cases. The interesting pieces I’ve seen are centered on the ability to exploit the power of the BCL for data manipulation. Again, that is where UDFs take center stage. It would be a waste of time to write a CLR stored procedure to do regular expression matching, but a CLR UDF doing the same work is a powerful and reusable tool.

 

Doug: When using CLR data types inside SQL Server, versioning is awkward. What happens to existing data when you change a type? Do you have any advice other than “Just don’t do it!”?

Adam: I have spent a huge amount of time thinking about this issue. I am working on a paper on the topic, although I am not sure if it will be published or by whom. Watch my blog for more information.

But “just don’t do it” doesn’t cut it. Try telling that to your manager when you’re on a deadline and you need to solve a business problem. Somehow you’ll find a way to make it work!

It is possible to version and maintain types on an ongoing basis, but it is something that must be considered carefully. Maintenance must be considered from a downtime point of view. Is it possible to update the type without altering every instance of the type in the database? What if you have several million rows of data in a table with a UDT column?

That is where versioning comes in, but it must be done in such a way that it does not affect type ordering, semantics or instance comparisons. I think it is possible, and my ideas for how to do it are the basis of the paper. But I’m still tweaking things, so you’ll have to wait for further information!

 

Doug: Can you think of a cool tip or trick that many database developers do not know about?

Adam: I think the best “trick” is to use a table of sequential numbers to help with set-based solutions to iterative logic problems. I’ve blogged about this many times (http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx). In the forums I suggest methods using numbers tables. This technique is so powerful and enabling of many tasks that would otherwise require cursors, that it’s amazing when you finally get that aha! moment and realize how to use it.

I talk to lots of developers who’ve never heard of it. And it still applies in SQL Server 2005-I’ve been doing some performance testing and have discovered that some tasks I thought would be better suited to recursive CTEs or CLR routines are still more efficient with a numbers table. It’s a classic, and classics never go out of style!

 

Do you know someone who deserves to be a Database Geek of the Week? Or perhaps that someone is you? Send me an email at editor@simple-talk.com and include “Database Geek of the Week suggestion” in the subject line.