Even if you’re completely new to SQL Server, you may have seen references to SQL Server Express. In fact, our last post was on installing SQL Server 2008 R2 Express. But you might want to back up a bit. Exactly what is Express and what can it do?
Starting with SQL Server 2005, SQL Server Express has been a free edition of the Microsoft SQL Server database engine (if you’re not familiar with databases, go here). The great thing about SQL Server Express is that it is the exact same database engine that ships with all of the other paid editions, like Enterprise or Data Center. And you can redistribute it and use it in real-world applications. The only difference is that it lacks some advanced features and has specific limitations, such as 1 CPU and 10GB database size. But these limitations leave you with a lot of room to learn and use SQL Server until you require additional space, processing power, or features.
If you want to get a great overview of SQL Server 2008 Express, view this channel 9 video: SQL Server Express: Overview. Just note that this was created for 2008 rather than 2008 R2. So the database limitation of 4GB that they mention has been increased to 10GB in R2. To see exact details on the differences between editions, try out this great comparison tool: Compare SQL Server 2008 R2 Editions.
And if you want to understand the differences between all SQL editions, a page like this is invaluable: Microsoft SQL Server 2008 R2 Editions.
Finally, there is often confusion between SQL Server Express and SQL Server Compact Edition. The compact edition has even less features than Express, but this is by design. It is meant to be extremely small and light, so that you can embed it in an application. A typical use might be a phone application that then synchronizes with an online data source. Both Compact and Express are freely distributable. But the Compact edition has more specific use cases. If you’re trying to learn SQL Server with the intention of being able to work in multiple editions, then use SQL Server Express. If you want to understand the differences better, download a white paper that discuss the differences between the Compact edition and the Express edition. It talks about 2005 rather than 2008, so some of the information is out-of-date. But the concepts are the same. On a side-note, I really believe in not discounting older material like this, because it is often when a feature is first released that the most introductory content is created. Since many of the featues in SQL Server 2005 have moved into SQL Server 2008, you may find excellent SQL Server 2005 resources that still help you in the latest release.
- SQL Server 2008 R2 Express Overview and Installation page (Microsoft.com)
- Difference Between SQL Server Compact Edition (CE) and SQL Server Express Edition (SQLAuthority.com)
- Editions and Components of SQL Server 2008 R2 (MSDN)
- SQL Server Express Edition (SQLServerPedia.com)