Peter Hussey
Microsoft Corporation
August 1997
Designing a Microsoft® SQL Server™ application for optimal performance can seem a daunting challenge. There are so many choices to make—development tools, database design, application structure, query design, choice of interface—and the "right" choices in each of these areas depend on your unique application requirements and on the skills your development team brings to the project. But you don't have time and resources to try out several different approaches to see which one works best for your situation. You don't even have time to read up on all of the different options to make a fully informed decision. So how do you get started?
The good news is that there are many paths to great performing SQL Server applications. You can take any one of a number of different approaches and end up with an application that performs as well as you need it to. Even so, knowing a few basic principles and trade-offs of SQL Server development will help you tremendously as you begin. This paper conveys these basic principles and trade-offs. If you are developing a new SQL Server application or redesigning an existing one, this paper is for you.
One of the first decisions you need to make is where to locate the running code. Should most of the work be done on the client? In the database? On a separate server? Wrapped up in this question is a choice of development tools. Tools such as language compilers or interpreters, debuggers, and run-time deployment platforms often restrict the development model choices. Therefore, you should address decisions about tools and the development model at the same time. This paper introduces the logical three-tier development model and then describes four physical implementations of that model. This is followed by a discussion of database access styles, choosing and optimizing your interface, and tips for leveraging built-in SQL Server features.