Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

You're Invited: Easily Visualize Your SQL Server Result Sets with Charts and Graphs
 

SQL Server Express Versions


By:   |   Read Comments (1)   |   Related Tips: More > Express Edition

Problem

SQL Server Express edition is a free version of SQL Server that can be used in production environments.  In my previous tip we looked at reasons why you would or would not want to use SQL Server Express.  In this tip we will cover the different versions of SQL Server Express that are available for you to download and use.

Solution

In the past few years we have seen SQL Server grow with many new features. Just to name a few: in SQL 2014 Microsoft added In-Memory OLTP, which was further enhanced in version 2016. In SQL 2016 came Machine Learning as a feature with the possibility to use R scripts inside T-SQL code. Finally, SQL Server 2017 brought us as an unexpected surprise of a Linux edition.

The New Versions of SQL Server Express

With all of those changes we shouldn't be surprised with the fact that Microsoft has decided to create different versions of SQL Server Express in order to adapt the product according to the market needs.

Basically, in recent years Microsoft was focused on making SQL Server a product that is more than a conventional database. In other words, Microsoft wanted SQL Server to be different than something that can be seen as a data holding place by adding more features and trying to differentiate itself from the competition. Just for comparison, let’s think about SQLite, which is a lightweight embedded and ACID-compliant database that implements most of the SQL standards, but lacks remote access capabilities. In other words, if someone labels SQLite as a data holding place, that person won’t find any resistance to that argument.

SQL Server 2017 Express

By downloading and using this version, you have the core express database server that includes the database engine features. Also, this version includes SQL Server Replication features, but it’s limited to work as a subscriber for Merge, Peer to Peer and Transactional replication.

This version is ideal when you need to accept remote connections. This, like all versions of SQL Server Express is available for free. On the next image you will see a screen capture of the Feature Selection step on the SQL Server Express installer.

SQL Server Express Installer - Description: This are the features available on SQL Server Express.

SQL Server 2017 Express with Advanced Services

This version gives users more features than the ones you will get with Express core edition. It includes the SQL Server database engine, SQL Server Replication (limited to work as a subscriber for Merge, Peer to Peer and Transactional replication) and full text search. Something that is worth mentioning is the inclusion of data analysis tools with Machine Learning Services (In-Database), allowing you to use both Python and R language. Also, you have the PolyBase Query Service for External Data, and for reporting, you have Reporting Services available as a downloadable add on.

In contrary to the core express version, if you want to download and use SQL Server 2017 Express with Advanced Services, you must join the Visual Studios Dev Essentials program. You can do so by following this link: https://www.visualstudio.com/es/dev-essentials/. On the following screen capture you will see the feature selection screen of the SQL Server 2017 Express with Advanced Services installer.

SQL Server 2017 Express with Advanced Services Installer - Description: This are the features available on SQL Server 2017 Express with Advanced Services.

This version, which is the most complete, is the right choice for advanced developers which are working on applications that use Business Intelligence or Machine Learning Algorithms. Also, this is the proper version if you are developing an application that uses PolyBase to connect to a Hadoop data source or Azure blob storage.

SQL Server 2017 Express LocalDB

This version allows you to embed SQL Server Express into applications with LocalDB. It is a lightweight version of the SQL Server Express edition which is a replacement of the deprecated SQL Server Compact. This version is the best choice when you need to use a database in your application and you don’t want to get involved with the administration of the database.

You can think about LocalDB as an alternative to SQLite database. SQL Server 2017 Express LocalDB contrary to SQLite, it is a client-server database engine that allows you to create an application where multiple users or processes on the computer where LocalDB is running needs to access the database. Furthermore, with LocalDB you have the possibility to use functions, triggers and stored procedures which are not available on SQLite.

But LocalDB also has the following limitations:

  • It can’t act as a subscriber in a merge replication scenario.
  • It does not support FILESTREAM.
  • Only allows local queues for Service Broker.

This version is your best choice if you are a developer that wants to code your application and you don’t want or don’t have time to spend on database configuration and administration.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, July 12, 2018 - 3:03:58 AM - Clement Back To Top

 Great information.

I am put off by the fact that now the latter SQL Express editions do not cater for 32-bit machines.

I have a lot of 32-bit mobile devices and having recently upgraded my system to run on SQL 2016 I am at a tether's end as to how to utilise my devices.

Would there be any 32-bit version of SQL Express 2016/2017?


Learn more about SQL Server tools