I was working with a client just the other day that has several SQL Server 2000 servers still in mission critical production environments, and a couple of newer versions of SQL Server 2005, 2016, 2019.

Not often do I admit that I do still occasionally work on SQL Server 2000, and given that we are now in 2022, that makes SQL Server 2000 a 22 year old product. Look around at the software you run, the tools you use, and the programs you rely on, how many of those are running on a 22 year old platform without being updated to something newer.

I was describing how the data that I needed to manipulate on SQL Server 2000 was just too difficult to work with, the table had over 10 million rows, a fairly wide table, and only a couple of indexes that were not being very useful for me. I am afraid to add indexes to this SQL Server 2000 server because I don’t want to be the one who runs it out of disk space, or crosses some memory threshold that bogs down the system.

After I told them that I was moving the data to a newer version of SQL Server (2016 or 2019) to do analysis, they asked the question about, why would those newer versions of SQL Server process the query faster, and why would it be easier for me to work on something newer than SQL Server 2000. I was a bit shocked at this, having seen the evolution of SQL Server over the last 30+ years this question totally confused me.

Here is the version I was working on:

Microsoft SQL Server 2000 – 8.00.2039 (Intel X86) Standard Edition

So lets take a walk down memory lane and see what makes SQL Server 2019 better than SQL Server 2000, simply for the perspective of writing and running queries.

Memory limits

SQL Server 2000 Standard Edition has a theoretical maximum of the operating system maximum of 4GB  (more if you are using Enterprise, which we are not). Of that 4GB includes entire operating system needs to run, any applications you have on the server, and the SQL Server process. In the end SQL Server ends up with somewhere between 1gb and 2gb that it uses.

Here are what other versions of SQL Server support for memory:

SQL Server 2000: 2GB Standard, more with Enterprise.

SQL Server 2005 : 32GB Standard, 1TB Enterprise.

SQL Server 2008 : 32 GB, Enterprise 1TB.

SQL Server 2008 R2 : 64GB standard, Enterprise 2TB.

SQL Server 2012 : 64GB Standard, operating system max for Enterprise.

SQL Server 2014 : 128GB Standard, operating system max for Enterprise.

SQL Server 2016 : 128GB Standard, operating system max for Enterprise.

SQL Server 2017 : 128GB Standard, operating system max for Enterprise.

SQL Server 2019 : 128GB Standard, operating system max for Enterprise.

The newer versions of SQL Server also make much better use of that memory.

Query Optimizer Changes

To start with SQL Server 2005 was a pretty massive improvement over SQL Server 2000 on query performance, then in 2012 Microsoft did another major overhaul on the query optimizer. All resulting in faster queries.

Stability

There are some specific scenarios where writing queries on SQL Server 2000 can crash the SQL Server process. There are far less of those on the newer versions.

SQL Server Management Studio

Although SQL Server Management Studio supports connecting to older versions of SQL Server, it doesn’t work well when trying to do things from the object explorer. Right click to script out an existing index on SQL Server 2000? Nope, that doesn’t work. Sure, I can keep a SQL Server 2012 version of Management Studio around if I want to connect to SQL Server 2000, but that seems like a bit of a waste.

SQL Server 2000 limitations

Things that just weren’t included yet relating to performance and writing queries.

• Exception Handling
• Varchar(MAX) data type
• Assignment of variables when declaring them.  DECLARE @var INT = 17;
• Common Table Expressions
• Windowing Functions
• DMV’s most of the DMV’s for checking performance and status did not exist on SQL Server 2000.
• Functions – a massive amount of functions were added newer versions.
• CONCAT function for combining values into a string.
• Filtered indexes.
• Just to name a few.

Wrap up

Really the way I finally answered it is that Microsoft has probably spent hundreds of millions of dollars on the development of SQL Server over the last 20  years, and that has likely added some performance improvements.

Please post a comment on your favorite SQL Server feature that was not there a few versions ago.

We help with SQL Server updates – I bet you didn’t know!

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!