SQL Server Express for Beginners – Introduction

Last modified date:

MS SQL Server exists in many different editions so where to start? Best way is to get edition which is free and that is MS SQL Server Express. This edition is suitable for small projects and as a tool to familiarize yourself with SQL Server.

SQL Server Express edition offers complete database engine and of course supports data manipulation using T-SQL language – as in every SQL Server edition.

Functions and Requirements of MS SQL Server Express

SQL Server Express has a lot of built-in tools. For example SQL Server Management Studio, Configuration Manager, Advance Query Optimizer, Service Broker, Import / Export guide, Business intelligence development studio.

One of the advanced services you will certainly appreciate is Reporting services and possibility of partial automation of ETL processes through Integration services. SSIS packages cannot be uploaded on server and played by Job Agent but they can be used by manual execution from Data Tools app.

As you can see, Express provides a lot right from the start. On the other hand it is free and therefore has a lot of restrictions (Express 2016):

  • Size restrictions – MS SQL Server Express supports databases not larger than 10 GB
  • Memory – you can use only 1 GB of memory
  • Processor – you can use only 1 processor, 4 cores
  • SQL Agent cannot be used (for automated job launch)
  • Analysis services (for OLAP creating) are missing
  • It does not consist of SSIS catalogs (for deploy SSIS packages on SQL Server)
  • Notification services as dbmail, certain triggers
  • Maximal amount of SQL Express sessions on one machine is 50
  • High Availability is missing
  • Master data services are missing
  • other features

There is obviously plenty of restrictions which are limiting mostly for experienced database makers and developers. You will not miss them at the beginning. Express is apart from small projects suitable edition for business intelligence basics (you may use examples on this web).

SSAS is missing (Analysis services) but SSRS (Reporting Services) are present. As a part of the installation Report server will be installed on your machine along with the database engine.

All reports produced by Business intelligence Data Tools (BIDS) are stored on this server. This edition is perfect for practice with databases connected to Excel or Power BI tools – Power Pivot, power query and power view (this tool is being put aside lately).

Conclusion – Super starting line for familiarization with T-SQL and BI

If you decide to use SQL Server Express you will get to use good tool perfectly suitable for e.g. small data mart as a data storage for monthly or weekly reporting. You can easily create reports (SSRS or power bi) connected directly to the database. Then you can present your data to the management in visual form with zero investments. Law commanding via Active Directory is a sure thing. It can be also used as a database for a small app, but it is not suitable for the big projects.

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SQL Server

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.