SQL Server Express Interview Questions
SQL Server Express is a free redistributable database from Microsoft to take advantage of the same powerful database engine in its bigger siblings, in a version tailored for embedding. A lot of ISV partners use SQL Server express edition shipped with their product. This very reason, a lot of developers needs to know about SQL Server Express. This post is exactly about that, where we talk about SQL Server Express Interview Questions.
Question: What is a SQL SERVER Express Edition?
Answer: SQL SERVER Express edition is a scaled down version of the full blown version of SQL SERVER 2012/2014 or 2016 Editions of SQL Server. It is designed to be the new successor of MSDE back in the SQL Server 2000 and 2005 days. Over the years Microsoft has also released features like LocalDB, Contained database, which is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.
Here are some of major features of SQL Express:
- Native programming language support like T-SQL, .NET. With the CLR integration, you can also you can write your stored procedures using .NET.
- SQL SERVER Express support unlimited connections, but with limitation of supporting to 1 CPU Core on the host machine or VM.
- SQL SERVER Express supports XCOPY.
- SQL SERVER Express also support a limited free edition of reporting services which comes with the Express with Advanced Edition package.
- SQL SERVER Express has native support for XML datatypes.
Question: What is the Maximum Size per Database for SQL Server Express?
Answer: This might become a tricky question, since Microsoft has changed the capacity over time. They are as follows:
- Microsoft SQL Server 2005 Express edition has a database size limit to 4GB
- Microsoft SQL Server 2008 Express edition has a database size limit to 4GB
- Microsoft SQL Server 2008 R2 Express edition has a database size limit to 10GB
- Microsoft SQL Server 2012 Express edition has a database size limit to 10GB
- Microsoft SQL Server 2014 Express edition has a database size limit to 10GB
SQL Server Express maximum size of 4/10 GB per database excludes all the log files. 4/10 GB is not a very large size however however, the limit is per database. This allows you to properly design and the tables arrange them in a separate databases so that the limitations can be resolved to a certain extent.
Question: What are the limitations of SQL Server Express Edition?
In addition to the size limitations mentioned above, other limitations are specific to its scalability and performance.
- Till SQL Server 2008 R2, it was limited to use a single CPU Core. This Constraint was however removed in 2012, and has been changed to “The lesser of one socket or four cores”, so some scaling and multi-threading is now possible.
- Max memory support of 1GB RAM which is still the same today.
- No support for DTS/SSIS services. If you need to do ETL, you need to buy a minimum of SQL Server Standard.
- SQL Server Agent is not present, so if you want to schedule and run jobs, you need to find alternatives.
- No SQL Profiler
- Dedicated admin connection under trace flag
- Maximum number of SQL Server Express instances = 16 on a machine.
- Other popular features like Database compression, Encryption, High Availability etc are only possible on higher editions of SQL Server.
Official Link to Download SQL Server