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.