Commentary
Introducing SQL Server 2005 Everywhere Edition
by Michael Otey
In June, Microsoft added a new member to the SQL Server family: SQL Server 2005 Everywhere Edition. As with SQL Server 2005 Express, SQL Server Everywhere is a free relational database that's limited to 4GB and designed for small-scale database applications. You can use the ADO.NET programming model to access both databases and use SQL Server Management Studio (SSMS) to manage them. Because SQL Server Express and SQL Server Everywhere are so similar, you might have a difficult time deciding which product to choose. So let's take a look at some of the differences between the products.
The primary difference between SQL Server Express and SQL Server Everywhere is that SQL Server Express is a multiuser database server that runs as a system service--meaning it's not bound to any specific application. SQL Server Express runs outside the application and can provide database services to multiple local or remote applications. In contrast, SQL Server Everywhere is an in-process database that provides relational-database support for the application it's embedded in. In other words, you use SQL Server Everywhere within an application to provide database functionality to only that application.
Another difference lies in the products' heritage. SQL Server Express uses the same core database engine that SQL Server 2005 Enterprise, Workgroup, and Standard Editions use. That's not the case for SQL Server Everywhere; it's based on SQL Server 2005 Mobile Edition. SQL Server Everywhere has a different core database engine, a smaller footprint than SQL Server Express, and is designed to run on mobile devices. It runs as a mobile or desktop application and lacks many of the more powerful database features in SQL Server Express. For example, although SQL Server Everywhere does support referential integrity (by using cascading deletes and updates) and the ability to commit roll-back transactions, it doesn't support more advanced database features such as stored procedures, the XML data type, or CLR integration. For building distributed mobile applications, SQL Server Everywhere, like SQL Server Express, supports data exchange with other members of the SQL Server family by using merge replication.
So, when should you use SQL Server Everywhere and when should you use SQL Server Express? SQL Server Everywhere is the clear choice when you need database support for mobile-device applications, or for single-user applications that require only basic database functionality. SQL Server Express is a better choice for multiuser applications or for applications that need advanced database features such as stored procedures and XML support.
Check It Out
SQL Server 2005 Everywhere Edition CTP
by Michael Otey
If you're interested in developing mobile and desktop applications that use an embedded database with a very small footprint, you might want to check Microsoft's newest release in the SQL Server family: SQL Server 2005 Everywhere Edition. You can download the Community Technology Preview (CTP) of SQL Server Everywhere free from the Microsoft site.
From the Community
One of our readers noticed an error in the code in a recent Jump Start column ("Using Views," June 5, 2006. Here's the reader's note:
Hello Michael. In your Jump Start column titled "Using Views," you included code. When I ran the code, it appears to drop the Media table before trying to create a view on that table. Did you intend to check for the existence of the view rather than the table, then drop the view?
- John Kelso
John, you're absolutely right, and thanks for pointing it out. Here’s the corrected code listing.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'vwMedia' AND type = 'V')
DROP VIEW vwMedia
GO
CREATE VIEW vwMedia
AS
SELECT MediaID, MediaType, MediaTitle
FROM Media
First, this code queries the sysobjects system table to check for the existence of the view named vwMedia. If the vwMedia view exists, SQL Server executes the DROP VIEW statement to delete the view. This existence-check and drop-statement combination lets you rerun the code to easily recreate the view. Otherwise, running the CREATE VIEW statement for an existing view would result in an error. Finally, the CREATE VIEW statement creates the vwMedia view by using only the MediaID, MediaType, and MediaTitle columns from the base Media table.
- Michael Otey
