Fernando Guerrero, SQL Server MVP (Most Valuable Professional), is the author of the Que book, Microsoft SQL Server 2000 Programming, and is currently writing a book on ADO.NET and SQL Server for Apress. He is the Principal Technologist and SQL Server Product Consultant for QA, which is the leading IT training company in the U.K. He also writes articles for SQL Server magazine, and has made presentations on SQL Server 2000 at SQL Pass, TechEd, VBUG, and VBITs.
In this exclusive interview, Fernando shares some of his knowledge and experience on how to get the most out of ADO.NET and SQL Server 2000.
Developers who write applications that use SQL Server as their back-end database are always interested in getting the best performance out of SQL Server. What does ADO.NET offer new to developers who want to get the best performance out of SQL Server?
The SQL Server .NET Data Provider uses TDS (Tabular Data Stream) to connect directly to SQL Server. This is the native packet format that SQL Server uses, and it doesn’t require connecting through other layers, such as ODBC or OLEDB. Programmers used to believe that native access to ODBC from C++ was the fastest way to connect to SQL Server, now there is a new kid in town: the SQL Server .NET Data Provider.
I would recommend learning as many tricks as possible about the SQLCommand class and how to use stored procedures and user-defined functions through SQLCommand objects. Mastering SQLParameter utilization and using the SQLDataReader class will make your application rock. When using ADO.NET, what will developers have to “unlearn” and “learn new” when making the transition from ADO to ADO.NET?
I believe that the most difficult concept to learn is the difference between the connected and disconnected layers. From an ADODB point of view, the Recordset object was something connected to a database at some point, whereas the DataSet doesn’t know anything about where the data comes from. It is so different that a Dataset doesn’t have any property or method to connect to a database. For developers used to working with the disconnected model, it won’t be a problem to adapt themselves to the new rules. For developers who rely on server side cursors, and pessimistic locking, there will be a more difficult transition path to follow.
ADO.NET provides the possibility of de-coupling two programmers’ roles. The client layer deals with data with little or no knowledge of back-end implementation. Middle layer programmers will perform connected operations on behalf of the client applications, such as filling a DataSet or updating the backend database according to changes applied to a DataSet.
Something to unlearn is the concept of “browsing,” as in ADO.NET there is no such concepts of MoveNext, MovePrevious, and so on. These operations have been part of the programmer’s life since dBase times, and now we need to learn how to deal with “collections” of rows instead.
As ADO.NET greatly integrates with XML, and with the arrival of SQLXML 3.0, database developers should learn about XML as much as they can. It is not necessary to be an expert on XML to be a good database developer, but it won’t hurt you, and you’ll greatly benefit from this knowledge. What do Database Administrators (DBAs) need to know about ADO.NET in order for them to get the most of ADO.NET?
DBAs should work very closely with middle layer database programmers to understand the way they need to retrieve and update data in SQL Server, so they can build the most efficient indexing and locking strategies, user-defined functions, and stored procedures for them. Profiler is always a good friend for a conscious DBA, and in this case it will help a lot understanding the way ADO.NET interacts with SQL Server.
In some cases it would be interesting to consider the creation of indexed views as potential sources of performance boost, and the Index Tuning Wizard can help a lot on this topic.
I really believe that every DBA should learn as much as possible about ADO.NET, at least the connected layer, to stay alive in this dynamic market. And it is even more important to do so, having in mind what the next SQL Server release will bring in this line.