Even if you’ve checked out Microsoft Data
Engine‘s specifications, you might conclude that it’s little
more than a testbed for developers on a budget. Microsoft’s
documentation suggests that MSDE is useful only for a small number
of users, yet many developers are pushing it far beyond the
advertised limits. I know of a couple of installations where 100
users are hitting a single database on a single server.

Microsoft-centric developers now have three
database choices: Access, SQL Server and, most recently, MSDE. Like
Access databases, MSDE can be freely distributed. In addition, it’s
a genuine database, just like SQL Server.

Now let’s examine the performance differences
between the three databases. Given a query that pulls 100 rows from
a table, Access sends the entire table (or tables where joins are
involved) to the client, where the front end filters out the
irrelevant rows; whereas, in SQL Server and MSDE, the filtering is
performed on the server. The client receives only the relevant rows
and not the whole table(s).

How I see the database landscape

At the low end of the spectrum are stand-alone
databases. These could be something as simple as a database in
which to catalog your CDs, or an order-entry system whose maximum
size will never exceed a couple of gigabytes.

At the opposite end of the spectrum, a large
enterprise may stake its future on your database. In that case, you
want all the bells and whistles that SQL Server offers: stored
procedures, views, user functions, logins, roles, DTS, Query
Analyzer, and so on.

In the middle, let’s say you have a small
enterprise that could be bigger. Currently, the enterprise doesn’t
have the budget for SQL Server licenses for all its users. But
since there is serious potential for growth, you want to design for
success so you don’t have to re-invent the database a year from
now. In this case, MSDE is the best choice.

MSDE is also a good choice for the application
developer who sells to both small and large enterprises. When
selling to a large company, she can assume that the client has
already licensed SQL Server. Then, when selling to a small
business, she can include a copy of MSDE, and thus pave the way for
future growth without rewriting the application or redesigning the
database or revising a single stored procedure.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!