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.

VistaDB and SQL Server share a Common Language

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.

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!