SQL Server vs VistaDB

What is Microsoft SQL Server

Microsoft SQL Server is the leading enterprise Relational Database Management System (RDBMS). With its powerful variant of SQL (called Transact SQL or T-SQL) it’s empowered developers for decades solving real world problems. There are SQL Server editions that work from a modest server with a few GB of RAM up through databases many terabytes in size. Clustering support, disaster recovery through AlwaysOn Availability Groups - SQL Server has been an incredible success in the enterprise Relational Database market with its wide breadth of capabilities.

We love SQL Server so much we wanted to use it everywhere we could - including smaller scenarios where SQL just couldn’t fit. That lead us to create VistaDB - the scale down SQL Server.

Both SQL Server and VistaDB use Transact SQL (T-SQL) as their primary language. This includes both a set of data types and syntax for manipulating data and for defining a database schema. VistaDB supports a strict subset of T-SQL - so your skills and experience with T-SQL transfer from SQL Server to VistaDB, and code written against VistaDB will work the same way with SQL Server.

VistaDB also offers T-SQL stored procedures, user defined functions (UDF’s), and Views just like SQL Server.

Designing and optimizing your database works like SQL Server as well - you design a relational schema, set up primary and foreign keys, and add indexes for the best performance. For more details, see What’s in the VistaDB Database Engine

SQL Server is Big

There are several versions of SQL Server with different installation sizes. The smallest distributions are for SQL Server LocalDB and SQL Server Express which still weigh in at between 90 and 280MB. Once installed they are significantly larger on disk. By comparison, VistaDB typically adds around 100KB to an installation and uses less than 2MB of disk once installed.

Once installed the difference is just as large - The minimum installed footprint for SQL Server 2017 Express is nearly 100MB. Add in SQL Server Management Studio and it nears 500MB. Compare both of those to the total deployment of VistaDB around 2 MB.

The running footprint is also large - SQL Server is optimized to run on its own OS, where it can consume all of the resources of the OS to provide the maximum scalability and performance possible. VistaDB is designed from the ground up to work within your application and the host OS constraints, scaling down as needed to be sure your application can run.

That size just too big for most desktop or small office database applications. The focus of VistaDB is not to compete with SQL Server Express for full blown server applications. Instead, VistaDB is the ideal solution when SQL Server Express is being used for an application that’s scaling down to run on one or a few desktops.

VistaDB Doesn’t Have to be Installed

SQL Server Express requires installation and configuration with elevated rights to the local machine, which makes deployment a complicated process. VistaDB is a Zero config deployment using XCopy or FTP with zero configuration, no registry changes. VistaDB can even run as a guest on Windows with UAC enabled. VistaDB can even be FTP’d to shared ASP.NET hosting (shared web server) without any additional configuration.

The installer for even the simplest version of SQL Server can be daunting to people outside of IT. Get rid of the need to do any additional installation and just deploy VistaDB along with the files for your application. No elevated permissions or reboots required, it’s the easiest database to distribute on the market.

Deploying VistaDB Databases is just Copying a File

Once you’ve installed SQL Server you then need to deploy your database to it - typically by running long SQL scripts you need to maintain. With VistaDB you just include a small template database file and copy it to the location where you want to access it from. No complicated security configuration, no risks of your install failing.

Can’t Decide? Use Both!

It’s easy to make your .NET application work with both SQL Server and VistaDB. Because we share a common dialect of SQL and ADO.NET has a provider model designed to let applications swap data access libraries you can switch back and forth just by changing your connection string. There are other options that all help you create common application libraries you can put your core application logic in and share between SQL Server and VistaDB, even from Windows through to an iPhone!

For more information and to see some examples of how to write common ADO.NET data access code, check out our VistaDB documentation: Common Operations using Any ADO.NET Provider