What's new in SQL Server

A frequent question I get is what the new stuff is in some version of SQL Server. And, sure, Microsoft has a very exhaustive "what's new" section for each version of SQL Server. This is lost after some 5-10 years, though. The purpose of this page is to list all news on one page and keep a longer trail than 5-10 years. Focus is the SQL engine, on-premise.

Each version adds a new database compatibility level (the concept of database compatibility level was introduced in 7.0). Starting with SQL Server 2014 this can also affect performance, for instance enabling new functionality in the optimizer as well as controlling the Cardinality Estimation version used.

SQL Server 2022

https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022
Project name Dallas. Version 16.
  • Language
    • New language elements
      • WINDOW clause, named window that can be referred to in OVER clause. Link
      • IS [NOT] DISTINCT FROM, Allow NULL comparison to be TRUE Link
      • DATE_BUCKET(), DATETRUNC()
      • GENERATE_SERIES()
      • GREATEST() and LEAST()
      • JSON_PATH_EXISTS (), JSON_OBJECT(), JSON_ARRAY()
      • LEFT_SHIFT(), RIGHT_SHIFT(), BIT_COUNT(), GET_BIT(), SET_BIT()
      • APPROX_PERCENTILE_CONT(), APPROX_PERCENTILE_DISC()
    • Enhancements for:
      • STRING_SPLIT() with ordinal
      • LTRIM(), RTRIM(), TRIM(), remove other characters than space
  • Performance
    • System page latch concurrency enhancements, allow concurrent updates on GAM and SGAM - especially beneficial for tempdb.
    • Buffer pool parallel scan, benefit some operations on large memory machines. Link
    • Ordered clustered columnstore index. Link and one more.
    • Increased data type support for segment elimination (now includes for instance string data types).
    • New algorithm for log file growing regarding number of VLFs. Link
    • Log can grow with Instant File Initialization, up to 64 MB
    • Reason for query not going parallel in execution plan.
  • Query Store and intelligent query processing
    • Query Store is on by default when you create a new database.
    • Query Store on secondary replicas. Link
    • Query Store Hints. Link
    • Memory grant feedback, persistence and percentile. Link
    • Degree of parallelism (DOP) feedback, back off the DOP until performance suffers. Link
    • Cardinality estimation feedback. Link
    • Parameter sensitive plan optimization, several plans for same query. Link
    • Optimized plan forcing, less overhead for forced query plans. Link
    • Approximate percentile functions
  • Management
    • Integrated setup experience for the Azure extension for SQL Server, install the ARC extension with SQL Server setup.
    • Manage the Azure extension from SQL Server Configuration Manager.
    • Setup calculates recommendation for max server memory differently differently. Link
    • Create snapshot backup using T-SQL. Link
    • Backup to S3 compatible storage Link
    • Support for backup compression hardware Link
    • Shrink database with low priority. Link
    • XML compression can also compress XML columns, similar to data compression but for MXL columns.
    • Auto update statistics (async) wait low priority
    • Backup to S3 compatible object storage. Link
  • Platform
    • Native Client has been removed.
    • Hybrid buffer pool with direct write, on PMEM devices. Link
    • Integrated acceleration and offloading. Link
    • Improved optimization using HW CPU capabilities. Link
  • Availability
    • Link to Azure SQL Managed Instance (fail to MI and possibly back again) Link
    • Contained Availability Groups, msdb and master resources are in the AG. Link
    • Distributed availability Group, using multipla TCP connections
    • Improved backup metadata, backupset has last valid restore time
    • Further improvements for Accelerated Database Recovery. Link
    • Parallel redo enhancements
    • Resumable ADD CONSTRAINT
    • CREATE INDEX with WAIT_AT_LOW_PRIORITY
  • Security
    • Microsoft Defender for Cloud integration, requires SQL Server extension for Azure (ARC)
    • Purview integration, requires ARC
    • Ledger, verify that data hasn't been tampered with Link
    • Azure AD authentication to SQL Server instance, allowing AAD account login to SQL Server. Requires ARC. Link
    • More T-SQL supported for Always Encrypted using Secure Enclaves (ORDER BY, JOIN, GROUP BY) Link
    • New granular permissions Link
    • A bunch of new fixed server roles. Link
    • Dynamic Data Masking more granular permissions, schema, table and column level. Link
    • Support for PFX certificates etc
    • New TDS version, making encryption mandatory among other things. Link
  • Analytics
    • Azure Synapse Link for SQL. Link
    • Object storage integration Link
    • Data Virtualization

SQL Server 2019

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15
Project name Seattle. Version 15.
  • Big data clusters. Link
  • Intelligent query processing
    • Row mode memory grant feedback (EE). Link
    • Batch mode on rowstore (EE). Link
    • Scalar UDF Inlining. Link
    • Table variable deferred compilation. Link
    • Approximate query processing with APPROX_COUNT_DISTINCT(). Link
  • In-memory database
    • Hybrid buffer pool, allow persistent memory act as Buffer Pool. Link
    • Memory-optimized TempDB metadata. Link
    • In-Memory OLTP support for Database Snapshots.
  • Intelligent performance
    • Index option OPTIMIZE_FOR_SEQUENTIAL_KEY, can reduce page ex latches. Link
    • Using Query Store to force fast forward and static cursors. Link
    • Some limits for Resource Governor are now float instead of int, allow for better granularity (EE).
    • Reduced recompilations for workloads using temporary tables across multiple scopes. Link
    • Indirect checkpoint scalability. Link
    • Concurrent PFS updates to reduce latch contention for PFS pages.
    • Scheduler worker migration, allow a worker migrate fro one scheduler to another. Link
  • Monitoring
    • New wait type WAIT_ON_SYNC_STATISTICS_REFRESH
    • Custom capture policy for Query Store
    • Database option LIGHTWEIGHT_QUERY_PROFILING
    • The column command in sys.dm_exec_request will show SELECT(STATMAN) for sync statistics update.
    • New DMV sys.dm_exec_query_plan_stats, show last known actual execution plan. Link
    • Database option LAST_QUERY_PLAN_STATS to enable above. Off by default.
    • Extended Event query_post_execution_plan_profile to grab actual exec plan, cheaper than query_post_execution_showplan. Link
    • DMV sys.dm_db_page_info() to substitute old DBCC PAGE. Link
  • Programming related
    • Graph tables
      • Edge constraint cascade delete actions
      • New graph function - SHORTEST_PATH
      • Graph tables now support table and index partitioning.
      • Use derived table or view aliases in graph match query
    • UTF-8 encoding for Unicode data types. Potential for space savings. Collation decides which encoding is used.
    • Language extensibility, Java. Link
    • New spatial reference identifier: Australian GDA2020
    • More detailed information for data truncation errors. Controlled using VERBOSE_TRUNCATION_WARNINGS database option. On by default for db compat level 15.
  • Security
    • Always Encrypted with secure enclaves. Allow for less limitations when Always Encrypted is used. Link
    • Certificate management in SQL Server Configuration Manager. Link
    • Data Discovery & Classification
  • High Availability
    • Up to 5 sync replicas (up from 3)
    • Secondary-to-primary connection redirection (re-route the connection to the primary, even if it was made to a secondary). Link
    • HA DR licensing benefits. Link
  • Accelerated database recovery. Link
  • Resumable operations
    • Online clustered columnstore index build and rebuild
    • Resumable online rowstore index build
    • Suspend and resume initial scan for Transparent Data Encryption (TDE). Link 
  • Expanded support on Linux
    • Replication
    • DTC
    • OpenLDAP support for third-party AD providers
    • Machine Learning Services
    • Tempdb, creation of several files by default
    • Polybase
    • Change Data Capture
  • Containers
    • Microsoft Container Registry replaces Docker Hub for MS official container images
    • Non-root containers, ability to create SQL server as a non-root user.
    • Containers on Red Hat Enterprise Linux.
    • Polybase and Machine Learning support
  • Setup
    • Can set max and mim server memory, according to link.
    • Can set maxdop according to link.
  • Support for columnstore statistics in DBCC CLONEDATABASE
  • New polybase connectors for SQL Server, Oracle, Teradata and MongoDB
  • Support for COLUMNSTORE and COLUMNSTORE_ARCHIVE in sp_estimate_data_compression_savings

SQL Server 2017
https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2017

Project name Helsinki. Version 14.
  • SQL Server on Linux OS
  • White-listing of assemblies along with sp_configure option clr strict security
  • New DMV to get statistics for transaction log for a database: sys.dm_db_log_stats().
  • Resumable online index rebuild.
  • IDENTITY_CACHE allowing for non-gaps in identity on hard shutdown (but with a performance penalty).
  • Graph tables
  • Adaptive Query Processing:
    • Adaptive joins.
    • Memory grant feedback.
    • Interleaved execution for MSTVF.
  • Automatic tuning
    • Automaticv plan correction. Identify regressions and force the better plan automatically.
    • On Azure SQL Database we also have "automatic index management", auto create and drop indexes.
  • Significant performance improvements when rebuilding BW-tree non-clustered indexes om memory-optimized tables.
  • Column modified_extent_page_count in sys.dm_db_file_space_usage tracks differential changes in each database file.
  • SELECT INTO has option to specify filegroup.
  • Setup allow for 256 GB tempdb files. Watch out for using a large file size if you don't have Instance File Initialization.
  • Improved backup performance for small databases on servers with lots of memory (avoid multiple sweeps of the BP).
  • Query Store track wait stats summary info.
  • Temporal tables
    • Cascade delete and update for foreign keys.
    • Supported retention policy (keep x number of days/months...)
  • Improvements to indirect checkpoints.
  • Columnstore indexes
    • On-line build and rebuild of non-clustered
    • Clustered supports LOB columns
  • Availability groups:
    • Cross database transactions.
    • Clusterless.
    • Minimum Replica Commit Availability Groups.
    • Linux, including mixed environments.
  • New DMVs
    • sys.dm_db_log_stats exposes summary
    • sys.dm_tran_version_store_space_usage
    • sys.dm_db_log_info. Replacement and enhancement to DBCC LOGINFO.
    • sys.dm_db_stats_histogram
    • sys.dm_os_host_info
  • Database Engine Tuning Advisor allow specify Query Store for workload
  • In-memory
    • Computed columns
    • JSON
    • CROSS APPLY
  • Functions
    • New
      • CONCAT_WS
      • TRANSLATE
      • TRIM
    • WITHIN GROUP for STRING_AGG
  • BULK INSERT and OPENROWSET(BULK...) support CSV.
  • Improvements to the way incremental statistics update thresholds are computed (140 compatibility mode required).
  • Enhanacements to memory-optimized objects
    • Sp_spaceused support
    • Sp_rename
    • CASE
    • Remove limitation for 8 indexes
    • TOP(N) WITH TIES
    • ALTER TABLE faster
    • Transaction log redo now parallell
    • Azure storage support for storing databases and backup

SQL Server 2016
https://docs.microsoft.com/en-us/sql/database-engine/whats-new-in-sql-server-2016

Version 13.0.
  • Sp1
    • CREATE OR ALTER for procedures, views, functions and triggers.
    • A bunch of functionality that earlier required Enterprise Edition is now available in lower editions. Examples:
      • Partitioning
      • Data compression
      • Fine grained auditing (database audit specifications)
      • In-memory OLTP
  • Columnstore
    • Broader support for batch mode execution
    • Updateable non-clustered index
    • Non-clustered index can have filter clause
    • Columnstore index for memory-optimized table
    • Support for row-store non-clustered indexes on clustered columnstore table
    • Primary keys and foreign keys on clustered columnstore table
    • Compression delay
    • Support for read-committed snapshot and snapshot isolation
    • Remove deleted rows using ALTER INDEX ... REORGANIZE
    • Access CS indexes on AG replica
  • In-memory OLTP
    • Storage format change. No longer uses filestream code for storage.
    • ALTER TABLE is optimized regarding usage of transaction log. And can also run in parallel.
    • Statistics is update automatically
    • Parallel (index) and heap scan
    • Unique constraints and indexes
    • FK between memory optimized tables
    • CHECK constraints
    • Non-unique index allos NULL values in keu
    • Triggers (AFTER)
    • Full collation support
    • ALTER (add and drop indexex, chage bucket count, add columns etc)
    • Row length can exceed 8060 bytes
    • LOB support
    • Other performance and scalability enhancements
  • No need to opt-in to get optimizer fixes (trace flag 4199 no longer "needed")
  • Compatibility level guarantees for performance (get the old plan with old db compatibility level)
  • Up number of FK's to a table from 253 to 10,000.
  • Parallel update of samples statistics
  • No need to update as many row to get auto update of statistics (changes from 20% to SQRT based formula.
  • INSERT part of INSERT ... SELECT can go parallel
  • Live Query Statistics
  • Query Store
  • Temporal tables
  • Backup
    • Striped backups to Azure Blog storage
    • File-snapshot backups to Azure Blob storage
    • Manages backups
  • Tempdb
    • No need for trace flag 1117 (grow one and all files grow) and 1118 (use uniform extent from the start). These are on by default.
    • Setup default to several data files.
    • Initial file size by default is 8 MB and autogrow is 64 MB. You can change this in setup.
  • JSON support
    • FOR JSON
    • OPENJSON
    • ISJSON
    • JSON_VALUE
    • JSON_QUERY
    • JSON_MODIFY
    • No JSON data type, though
  • Polybase, run queries on Hadoop or Azore Blob Storage from TSQL.
  • Stretch database (to Azure SQL Database)
  • Some tooling get support for UTF-8 (BCP, BULK INSERT and OPENROWSET)
  • New, increased values by default for database file size and autogrow.
  • TRUNCATE TABLE can truncate specified partition.
  • More support for "online" ALTER TABLE operations,
  • NO_PERFORMANCE_SPOOL optimizer hint
  • Increase key size for nonclustered indexes from 900 to 1700 bytes.
  • DROP x IF EXISTS ... (x can be table, index, etc)
  • MAXDOP for DBCC CHECKDB and other DBCC CHECK.
  • Advanced Analytics (R)
  • New and enhanced functions:
    • COMPRESS() and DECOMPRESS()
    • SESSION_CONTEXT()
    • DATEDIFF_BIG() and AT TIME ZONE
    • New properties in SERVERPROPERTY()
    • HASHBYTES(), restriction of 8000 bytes removes
    • STRING_SPLIT()
    • STRING_ESCAPE()
  • Traceflag 1117 is replaced by database file group option AUTOGROW_ALL_FILES. Traceflag is now a dummy. Option is not on by default (except for tempdb).
  • Traceflag 1118 is replaced by MIXED_PAGE_ALLOCATION database setting, which is on y default. Trace flag is now a dummy.
  • Lots of increased support for natively compiled moduled, for example
    • UNION and UNION ALL
    • DISTINCT
    • OUTER JOINS
    • Subqueries
    • We can use native compilation for scalar functions. and inline table functions.
  • Security-related
    • Row-level security
    • Always encrypted
    • Dynamic data masking
  • High Availability enhancements
    • Basic Availability Groups for Standard Edition (basically a replacement for database mirroring)
    • AG: Load-balancing among read-only replicas
    • AG: Increased to three replicas for auto fail over (from two)
    • GSMA for failover cluster
    • AG: MSDTC support between instances
    • Can configure failover if database goes offline
    • Distributed availability group (AG over separate windows cluster)
    • Direct seeding (we don't have to restore database)
    • Performance enhancements

SQL Server 2014
https://docs.microsoft.com/en-in/SQL/database-engine/whats-new-in-sql-server-2016?view=sql-server-2014

Version 12.0.
  • SQL server data file in Azure
  • BAckup and restore
    • Backup to URL
    • Managed backups to Azure
    • Backup encryption
  • Standard Edition support 128 GB memory (from 64 GB)
  • In-memory OLTP introduced
    • Memory-optimized tables
    • Natively compiled stored procedures
  • Database files in Azure Blog Storage
  • Manage the lock priority of online operation
  • New Cardinality Estimator
    • For the first time since 7.0
  • Delayed Durability
  • Always On Availability group enhancements
    • From 4 to 8 secondary replicas
    • When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads
    • Can use Cluster Shared Volumes (CSVs) as cluster shared disks
    • New function sys.fn_hadr_is_primary_replica and new DMV sys.dm_io_cluster_valid_path_names
  • Rebuild index and table at partition level
  • Columnstore indexes
    • Clustered (updateable)
    • COLUMNSTORE_ARCHIVE compression in ALTER INDEX REBUILD
    • More details for col-store in execution plan
  • Buffer Pool Extension
  • Incremental statstics at partition level
  • Resource Governor: I/O
  • Compatibility level 90 (2005) removed
  • In-line syntax to create index (in CREATE TABLE)
  • SELECT INTO can go parallel
  • ReFS support for database files

SQL Server 2012

Project name Denali. Version 11.

From memory...

  • Availability Groups
  • FCI
    • Multi-subnet failover clusters
  • Online Index Rebuld also for (max) data types
  • Contained databases
  • Tuning Advisor can use plan cache
  • File Tables
  • Spatial enhancements
  • SC collations
  • SEQUENCE
  • THROW
  • 14 new scalar functions
    • PARSE, CONVERT, TRY_CONVERT
    • CHOOSE, IIF
    • CONCAT, FORMAT
    • DATEFROMPARTS, DATETIME2FROMPARTS, ...
  • Extended Events as full replacement for SQL Trace (Profiler)
  • Windowing, i.e. OVER, ROWS BETWEEN, RANGE BETWEEN, LAG, LEAD, etc
  • Columnstore index
  • 15000 partitions (from 1000)
  • BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are no longer sysadmins
  • Create your own server roles
  • Auditing, Server-level for all editions
  • Backup to Azure Blob storage
  • Databasfiler on SMB (3.0), UNC
  • Tempdb locally FCI
  • Virtual Service-accounts for services, is default in setup
  • Support for Managed Service Accounta (MSA)

SQL Server 2008 R2, 2010

Project name Kilimanjaro. Version 10.5.

To be done

SQL Server 2008

Project name Katmai. Version 10.0.

To be done

SQL Server 2005

Project name Yukon. Version 9.0.

  • Service Broker
  • Tools
    • SQL Server Management Studio, SSMS
    • SQL Server Configuration Manager
    • Database Engine Tuning Advisor (replaces Index Tuning Wizard)
  • Programmability stuff
    • .NET/CLR built into the database engine
      • Allow for objects be written in .NET language
    • HTTP Endpoints
    • XML data type
    • Bunch of enhancements for FOR XML, including PATH mode
    • TRY-CATCH
    • Catalog views
      • System table totally reworked and now hidden from us, unless you connect using DAC
    • Windowing, OVER clause
      • Focus was on RANK. DENSE_RANK, NTILE and ROW_NUMBER
    • OUTPUT clause for INSERT, UPDATE and DELETE
    • New LOB types varchar(max), nvarchar(max) and varbinary(max)
    • Common Table Expressions, CTE. Including recursive CTEs
    • APPLY
    • PIVOT and UNPIVOT
    • Query Notifications
    • OPENROWSET using BULK provider
    • TOP now allow subquery and variable
    • EXEC at linbked_server
    • TABLESAMPLE
    • Foreign keys, SET NULL
    • Row overflow, a row can be more than 8000 without using LOB types
    • SMO API replaces DMO
    • XQuery, the query(), value(), exist(), modify() and node() methods on XML columns
  • DAC, Dedicated Admin Connection
  • Resource database
  • DDL Triggers
  • Event Notifications
  • Bunch of improvements in security including "all permissions grantable" initiative
  • User-schema separation
  • Partitioning
  • ATTACH_REBUILD_LOG
  • Instant File Initialization
  • Ability to disable trigger
  • ALTER INDEX with REBUILD and REORGANIZE
  • XML format files for bulk loading
  • Database Mirroring
  • Database Snapshots
  • Page checksum
    • And page read-retry
  • Index on XML columns
  • Scalability and performance
    • Plan guides
    • Snapshot isolation levels
    • Forces parameterization database setting
    • Async update statistics
    • Persisted computed columns
    • Multiple Active Result Sets, MARS
    • Included columns
    • New query hints, such as RECOMPILE, OPTIMIZE FOR, USE PLAN and PARAMATERIZATION
    • Deferred page allocations when droppting large objects (more than 128 extents)
    • Scalable Shared Databases
      • Having more than one instance connected to database files
      • One R/W the other R/O
  • Supplementary character support (SC collations)
  • Security
    • Surface Area Configuration tool (anybody remembers this?)
    • Cryptographic functionality in the engine (key, certs, EncryptByPassphrase() etc)
    • EXECUTE AS command and option
    • Agent proxies, as we know of them today
  • Bunch of replication enhancements, which I won't list here
  • Logon triggers (sp2)
  • Common Criteria (sp2)

SQL Server 2000

Project name Shiloh. Version 8.0.

  • Multiple (and named) instances
  • Some XML support, like OPENXML() and FOR XML
  • Federated Database Servers, aka Distributed Partitioned Views
  • Create own functions
  • Indexed views
  • New data types
    • Bigint
    • sql_variant
    • table
  • INSTEAD OF triggers
  • New options for foreign keys
    • CASCADE
    • SET DEFAULT
    • SET NULL
  • Collations
    • Replaces the old "sort order" concept
    • Windows collations
    • Database level collations
  • Index
    • On computed columns
    • ASC/DESC
    • Parallelism while building index
    • Use tempdb as work area
  • Support for 64 bit OS
  • OPENDATASOURCE
  • Support for Kerberos for authentication
  • Backup and restore
    • The concept of "recovery model (SIMPLE, BULK_LOGGED and FULL) replaces old "trunc log on chkpt" etc.
    • Restore to named transaction
    • Password protected backups (pretty useless)
    • Differential backups now has a GAM-like page to know which extents to include in backup.
  • DBCC can go parallel.
  • Text in row, option to have data in-page for text, ntext and image types.
  • Tools:
    • Object browser in Query Analyzer
    • Debugger for stored procedures in Query Analyzer
    • Copy database Wizard
    • C2 auditing
    • Log shipping automation
  • Bunch of replication enhancement, which I won't list here.

SQL Server 7.0, 1998

Project name Sphinx

  • Database engine totally re-written with a new architecture. Only the (major part of) T-SQL remained, basically.
    • Now 8KB pages and 64 KB extents.
    • Transaction log no longer maintained as a table (old syslogs table), and it is now mandatory to separate it from data.
    • No longer disk devices (no more DISK INIT, and whatever the commands were).
    • Database autogrow, including for tempdb.
  • Bunch of new sp_configure options
    • Dynamic memory, and no setting for procedure cache
      • "We no longer need a DBA",. if anybody remembers.
    • Bunch of other dynamic settings, like locks, worker threads.
    • ANSI-SQL compliance, for SQL-92
      • I believe, but don't hold med to this, that MS hired Joe Celko to help out with this.
  • Database compatibility levels.
  • Bunch of work in the Replication area.
  • SQL Server Agent, used to be SQL Executive,
    • Bunch of improvements in this area.
  • SQL Server Profiler (we still didn't use the term "SQL Trace" for the engine parts of tracing.
  • The Enterprise Manager administration tool
    • ISQL/w was "renamed" (not the exe file) to Query Analyzer
  • Row-level locking and dynamic locking.
  • Hash and merge joins (in addition to loop joins)
  • Index intersection and loads of other performance and query processor related improvements.
  • Auto-update and auto-create statistics.
  • Number of tables referred to in a query upped from 16 to 256.
  • Parallelism, using multiple cores for a single query (operator).
  • Security re-architectured, thanks to DENY. Earlier, the final outcome dependent on order of GRANT and REVOKE.
  • New T-SQL statements and language improvements
    • ALTER
      • PROCEDURE
      • TRIGGER
      • VIEW
      • TABLE
    • BULK INSERT
    • COMMIT WORK (synonym to COMMIT [TRAN])
    • ROLLBACK WORK (synonym to ROLLBACK [TRAN])
    • DENY
    • RESTORE
      • FILELISTONLY
      • HEADERONLY
      • LABELONLY
      • VERIFYONLY
    • Recursive triggers
    • Multiple triggers per table and statement
    • Local cursors and cursor variables/parameters
    • Linked Servers
    • New and improved data types
      • Unicode (nchar, nvarchar and ntext)
      • Uniqueidentifier
      • Max size for (restricted) string data now 8000 bytes
    • TOP
    • Identifiers not upped to 128 characters (used to be 30).
      • Unfortunately, this is when we god the horrendous and NON-ANSI square brackets (in addition to double-quotes) ad identifier separators..
  • INFORMATIoN_SCHEMA views (remember them, anybody?).
  • Deferred name resolution in procedures, triggers and batches.
  • A bunch of things related to backup and restore.
  • Proper Integrated security
  • Roles
    • Database
    • Application
  • Full-test search
  • Non-clustered indexes now uses to clustering key instead of RID as row locator.

SQL Server 6.5, 1996

Project name Hydra
  • Backuphistory
  • Table level backup and restore
  • Maintenance plan wizard
  • SQL Server Web Assistant (generate HTML file from SQL query)
  • SQL Trace
  • Fallback support, what later evolved to fail-over cluster
  • SNMP support
  • MS DTC was introduced
  • Bound connections (several connections sharing the same transaction workspace)
  • INSERT ... EXEC was introduced
  • CUBE and ROLLUP, with a different syntax compared to what is in ANSI SQL and what we have to day
  • Row level locking for INSERT (a special case, back then we had page-level locking)
  • Ability to configure what CPUs to use (Thread Affinity)
  • OLE Automation stored procedures
  • Support for "modern-style" (ANSI SQL-92) join syntax
  • APP_NAME(), ISDATE(), ISNUMERIC()

SQL Server 6.0, 1995

Project name SQL95
  • Constraints (PRIMARY KEY, FOREIGN KEY etc)
  • IDENTITY property
  • Server-side cursors
  • CASE
  • Dynamic SQL
  • Comments using --
  • Decimal and numeric data types
  • COALESCE(), NULLIF()
  • Enterprise Manager

SQL Server 4.2, 1992-1993

OS/2 and Windows NT
I'm treating these versions the same, even though there are 4.2a, 4.2b (both OS/2) and 4.21a (Windows NT). Below is from memory.

  • A version for Windows NT
  • We got new GUI tools (to replace the quasi-GUI SAF tool)
    • SQL Administrator
    • SQL Object Manager
    • ISQL/w
  • Integrated Security on NT, the precursor to Windows Logins, but the mapping was manual and the mapping tool created SQL Logins for us.
  • Remote Servers, a precursor to Linked Servers. You could execute a stored procedure against a different server.

SQL Server 1.1, 1990
Project name Pietro. OS/2

From memory...

  • UNION

SQL Server 1.0, 1989

Project name Filipi. OS/2
This was the first release of Microsoft SQL Server. There were earlier releases from Sybase, but this list is for Microsoft SQL Server, running on either OS/2 or Windows.