What is Microsoft SQL Server?
Microsoft SQL Server (popularly called SQL Server or MSSQL) is Microsoft’s implementation of the RDBMS philosophy and used to build, maintain and run relational databases in an enterprise environment. A relational database is one where the tables in the database are related to each other through common data fields (i.e. columns or attributes) that have keys defined on them. The primary key which is local to a table and the foreign key which is the primary key of a table but foreign to the referring table. The keys help one table to access and pull the data of another table so that the combined data becomes a collective unit and can be manipulated collectively.
The chief competition to Microsoft SQL Server comes from MySQL and Oracle Database with Microsoft SQL Server placed ahead of Oracle Database and a bit behind MySQL. To understand the place of Microsoft SQL Server and appreciate it we will go briefly through its history and then do a comparison with one of its chief competitor MySQL. The comparison will help us understand it strengths and limitations and the motivation and effort that has gone into the product over the years to make it a world class client server RDBMS solution across the world.
The Microsoft SQL Server architecture has two main components – the database engine (which includes the single storage engine and query processor) and the operating system SQLOS. The storage engine is responsible for data storage and retrieval. The query processor is responsible for generating query execution plan, handling batch tasks, thread management and managing IO, memory and buffer for the executing queries. SQLOS is responsible for providing operating system services of IO and memory management, interrupt and exception handling and synchronization services.
History and Evolution
SQL Server was initially jointly developed by Microsoft with Sybase and the first version called SQL Server 1.0 was released for the OS/2 operating system in April 1989. Later on, Sybase sold the product to Microsoft and it became Microsoft SQL Server. Microsoft SQL Server is a 31-year-old product and there have been 15 versions of the product so far with the latest being Microsoft SQL Server 2019 which was released in November last year. Till 2016 Microsoft SQL Server only worked in the Microsoft Windows operating system. Post 2016 support was added for Linux and now it also supports MacOS via docker. The code of Microsoft SQL Server is powered by C, C++.
Comparison
The following table is a broad-brush comparison of Microsoft SQL Server and MySQL for a better comprehension of what MSSQL brings to the table.
Comparison Parameter | MSSQL | MySQL |
Ownership | Microsoft Corporation | Oracle Corporation |
OS support | Primarily compatible with Microsoft Windows but also supports Linux and MacOS (only through docker) | Supports almost all operating systems |
Distribution | There are two free editions – Microsoft SQL Server Developer Edition (not for production use) and Microsoft SQL Server Express Edition (can be used in medium workload production environments). The remaining 3 editions are paid editions and include Microsoft SQL Server Standard Edition, Enterprise Edition, Web Edition. So it has more customization and variety for customers to choose from as per need. | There is a free production use community edition which is distributed under GNU GPL. The remaining editions are paid and include MySQL Standard, Enterprise and Cluster Grade Edition (CGE) |
Price | Paid versions are significantly costlier than MySQL paid versions | Paid versions are significantly cheaper than MSSQL paid versions |
Installation and Setup | Installation is through installer exe and easy and straightforward | Installation is equally easy through RPM albeit a little complicated to MSSQL installation. |
Language Support | It is available in a multitude of languages including English, Japanese, French, Spanish, Chinese etc. | It is available only in English |
Programming language support | Primarily compatible with .NET languages like C#, Visual Basic but supports other major programming languages including Java, PHP, Python, Ruby, Delphi, Go and R | Supports all programming languages supported by MSSQL and additionally Perl, Scheme, Tcl, Haskel and Eiffel |
Development Language | Developed in C and C++ | Developed in C and C++ |
Backup | It can back up the database without blocking it and hence database operations are not affected | Blocks the database during backup making it unavailable. Takes more time to backup and restore |
Storage engine | It uses a single Microsoft developed storage engine | Supports multiple storage engines and even allows different tables in the same database to be managed by different storage engines |
Performance and Scalability | Performance wise it is very good and scales better than MySQL | Performance wise it is equally good though it is a bit weaker in terms of inserts and deletes. Scalability is also bit of an issue and performance is seen to drop on scaling. |
IDE Support | The primary IDE is SQL Server Management Studio (SSMS) which is more feature rich and robust | Primary IDE is MySQL Workbench which is not as good as SSMS |
Filtering capabilities | Filtering is better and easier | Filtering is difficult and has to be done separately by running multiple queries individually on the object to filter |
Security | it is proprietary, supports Microsoft Baseline Security Analyzer (MBSA) for security baselining and does not allow direct access and edits to database files which make it more secure | It does not have any inbuilt security tool like MBSA, allows access and edits to database files while it is online and is open source which makes it less secure. |
Triggers | It supports triggers on views and multiple triggers on a table | It does not support triggers on views and multiple triggers on a table |
Product Support | Support is mostly paid and costly | Very good community support which makes paid support redundant even though it is available |
SQL support | It follows the ANSI SQL standard more completely and extends it through Transact-SQL (T-SQL) and is syntactically easier | It does not follow the full ANSI SQL standards and is syntactically relatively difficult |
DBaaS offerings | None available | Available as Azure Database for MySQL and ScaleGrid for MySQL |
Query cancellation | It allows running query cancellation | It does not allow running query cancellation |
BI capabilities | It has better BI capabilities with tools like BIDS and SSDT for data analysis, reporting and ETL | It has less inbuilt capabilities though there are third part tools which support BI functionality for MySQL |
From the above comparison we can see that Microsoft SQL Server is a very strong RDBMS product and which can hold its own and even prevail over other such products including its chief competitor in the market.