Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Face-off: SQL Server Express vs. MSDE
Here are some of the differences between SQL Server Express and MSDE with my ratings in each category. Let's see how SQL Server Express and MSDE stack up against each other.
6 Min Read
1. Commentary
Face-off: SQL Server Express vs. MSDE
by Michael Otey, [email protected]
In my previous columns, I’ve compared SQL Server 2005 Express to Microsoft Access and provided the steps for upgrading from Microsoft Data Engine (MSDE) to SQL Server Express. However, I haven’t compared SQL Server Express to MSDE. Surprisingly, the toughest competitor for SQL Server Express is MSDE. Here are some of the differences between SQL Server Express and MSDE with my ratings in each category. Let's see how SQL Server Express and MSDE stack up against each other.
Database Engine Features
As you might expect, SQL Server Express's database engine features have a clear advantage over MSDE. SQL Server Express includes all of the MSDE database engine features, such as support for triggers, functions, and stored procedures. In addition, SQL Server Express provides other features, such as support for the Common Language Runtime (CLR) and for the new xml and varbinary(max) data types.
SQL Server Express: 1 and MSDE: 0
Platform Support
In terms of platform support, MSDE has the edge over SQL Server Express. SQL Server Express requires Windows 2000 Service Pack 4 (SP4) or higher, but MSDE runs on older Windows 9x systems, Win2K systems, and Windows XP systems.
SQL Server Express: 0 and MSDE: 1
Database Size
SQL Server Express clearly wins the point for supported database size. SQL Server Express supports databases as large as 4GB, whereas MSDE supports databases 2GB and smaller. It’s worth noting that for both products the database size is per database and not per server.
SQL Server Express: 1 and MSDE: 0
Workload Scalability
System scalability is a close call. MSDE supports dual-CPU systems and a maximum storage capacity of 2GB of RAM; SQL Server Express supports single-CPU systems and has a maximum storage capacity of 1GB of RAM. However, MSDE has a workload governor that limits the number of concurrent operations the database engine can perform and therefore limits MSDE's performance capacity. SQL Server Express doesn't have a workload governor and therefore has minimal limitations on performance capacity, so I’ll rate this category as a tie.
SQL Server Express: 1 and MSDE: 1
Enterprise Integration
Enterprise integration is another tight category. MSDE provides built-in support for the SQL Agent, the DTS runtime (this feature lets you run DTS packages), and Merge Replication publishing. SQL Server Express doesn’t have the SQL Agent or the ability to act as a replication publisher, but SQL Server Express can act as a subscriber to Merge and Transactional replication. In addition, the DTS runtime is a downloadable component. SQL Server Express is also able to function as a client for SQL Server Service broker applications. I’ll rate this one as a tie.
SQL Server Express: 1 and MSDE: 1
Application Deployment
SQL Server Express has a clear advantage when it comes to application deployment because it supports User Instances and can automatically attach a database, by using extensions, to the ADO.NET connection. MSDE doesn't support any of these features.
SQL Server Express: 1 and MSDE: 0
Management Tools
MSDE provides only the osql utility management tool to manage a database. If you want to use a graphical management tool with MSDE, you can use the SQL Server Enterprise Manager if you have a full SQL Server installation, or you can purchase a third-party product. In comparison, the free SQL Server Management Studio is available to manage SQL Server Express databases.
SQL Server Express: 1 and MSDE: 0
That makes the final total SQL Server Express 6 and MSDE 3.
2. SQL Server Express Jump Start
Columns and Data Types
by Michael Otey, [email protected]
In previous Jump Starts, I've shown you how to create databases and tables. Now, let's take a look at columns and data types, which are the cornerstone of database design.
Database hierarchy begins with a server instance, then comes the database. One server instance can contain multiple databases. Databases comprise tables, and tables comprise groups of related columns. When you create a column, you need to assign a particular data type to it. The data type specifies to SQL Server 2005 Express how to handle the object and also helps to insure that the correct data is stored in each column. For instance, if you create a numeric data type that is an integer (i.e., int), SQL Server Express knows that only integer values can go into this numeric type column. Further, SQL Server Express can perform aggregation or other numeric operations with data stored in this column that wouldn't be allowed or possible if the data stored weren't a numeric data type. SQL Server Express supports five data types, which I list below. I’ll show you how to use these data types to create columns next time.
Numeric Data Types:
bit--an integer data type that can take a value of 1, 0, or NULL
bigint--integer values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
decimal--fixed precision numbers from -10^38 + 1 through 10^38 - 1
float--floating point numbers from -1.79E+308 to -2.23E-308, 0, and 2.23E-308 to 1.79E+308
int--integer values from -2,147,483,648 to 2,147,483,647
numeric--identical to the decimal numeric data type
money--currency values from 922,337,203,685,477.5808 to 922,337,203,685,477.5807
real--floating point numbers from - 3.40E + 38 to -1.18E - 38, from 1.18E - 38 to 3.40E + 38, and 0
smallint--integer values from -32,768 to 32,767
tinyint--integer values from 0 to 255
smallmoney--currency values from - 214,748.3648 to 214,748.3647
Date and Time Data Types:
datetime--January 1, 1753, through December 31, 9999
smalldatetime--January 1, 1900, through June 6, 2079
Character Data Types:
char--fixed length character data up to 8000 bytes
nchar--fixed length Unicode data up to 4000 bytes
ntext--variable length Unicode data up to 1GB
nvarchar--variable length Unicode data up to 4000 bytes
text--variable length character data up to 2GB
varchar--variable length character data up to 8000 bytes
Binary Data Types:
binary--fixed length binary data up to 8000 bytes
image--variable length binary data up to 2GB
varbinary--variable length binary data up to 8000 bytes
Other Data Types:
sql_variant--stores other supported data types except text, ntext, image, timestamp, and sql_variant
timestamp--an 8-byte binary timestamp used for row versioning
uniqueidentifier--a 16-bit globally unique identifier (GUID), which is often used by replication
xml--XML data up to 2GB
3. Check It Out
SQL Server 2005 Express Redistribution License
by Michael OteyYou probably know that you can freely distribute SQL Server 2005 Express with your applications, but did you know that you must first obtain a SQL Server 2005 Express Edition redistribution license? The redistribution license is free; all you have to do is register for it.
6. New Product
An Easy Way to Create SQL Scripts
by Blake EnoRed Earth Technologies announced Superior SQL Builder Standard Edition to help you reduce the time it takes to cleanse, convert, and transform database data. Use Superior SQL Builder to help eliminate syntax, logic, and typographical errors when you build SQL scripts. Superior SQL Builder's interactive UI provides a drag-and-drop approach to script creation. Pricing for Superior SQL Builder Standard Edition starts at $399 for one to four licenses. A free trial version is available. For more information, contact Red Earth Technologies at [email protected] or [email protected].
About the Authors
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
