SQL Server Express Edition and Its Limitations
Microsoft SQL Server Express Edition is a free and feature-rich edition of SQL Server that is ideal for learning, developing, powering desktop, web & small server applications, and for redistribution by ISVs.
Express (SQLEXPR) – Express edition includes the SQL Server database engine only. Best suited to accept remote connections or administer remotely.
Express with Tools (SQLEXPRWT) – This package contains everything needed to install and configure SQL Server as a database server including the full version of SQL Server Management Studio for 2014 version. Choose either LocalDB or Express depending on your needs.
SQL Server Management Studio Express (SQLManagementStudio) – This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure for SQL Server 2014, full version of SQL Server Management Studio for SQL Server 2014, etc. This can be used if you already have the database and only need the management tools.
Express with Advanced Services (SQLEXPRADV) – This package contains all the components of SQL Server Express including the SQL Server Management Studio. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.
Although SQL Server Express Edition is great for low cost applications, but it has many limitations too as mentioned below.
Maximum Number of CPUs – SQL Server 2005 and SQL Server 2008 R2 Expression Editions can only use one CPU. Starting with SQL Server 2012, it can be either 1 Socket or 4 cores which ever is lesser.
Maximum Memory – SQL Server 2005, 2008 and R2 Express Editions can only use up to 1 GB of memory per instance. If you install 2 instances of SQL Server Express Edition on the same server, then each one can use up to 1 GB RAM. Start with SQL Server 2012 and 2014 SQL Server Express Edition can use 1.4 GB for Database Cache and other caches can use some more memory, so up to 2 GB can be used but 1.4 GB for data and index pages.
Maximum Size of Each Database – SQL Server 2005 Express Edition can have each database only to a maximum of 4 GB size, however this has been extended to 10 GB starting from SQL Server 2008 or higher.
This 4 GB for SQL 2005 and 10 GB for SQL 2008 or higher is only for data file, but not for Log files or FileStream data. So in SQL Server Express Edition a database can be of more than 10 GB size as the data file may be less than 10 GB and transaction log file or filestream data may be of any size making it look larger.
Maximum Number of Databases Per Instance – There are no limits to the number of databases that can be attached to the server.
Maximum Number of Connections – There is no limit on number of connection for SQL Server Express Edition, so it will default to 32,767, but you will not be able to use these many connections as before that you will hit one of the other above mentioned limitations.
There are other limitations which include that SQL Server Express Edition cannot be used in features like Log Shipping, AlwaysON Availability Groups, Alwayson Failover Cluster Instances, SQL Server Failover Cluster Instances, Online Indexing, Page level restores, Fast Recovery, etc. Almost SQL Server Express Edition does not have any features other than just a basic database engine.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.