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!