Difference between SQL Server Versions

There are different versions available in SQL Server and so many differences and enhancements on that versions. There are some difference and enhancements between different versions.

SQL Server 2000 and SQL Server 2005

SQL Server 2000 SQL Server 2005
Query Analyser and Enterprise manager are separate Both are combined as SSMS (Sql Server management Studio)
No XML data type is introduced XML data type is introduced
We can create maximum of 65,535 databases. We can create 2 (pow(20))-1 databases
Exception Handling is not here. Exception Handling is there
Cannot compress the tables and indexes can compress the tables and indexes
Datetime datatype is used for both date and time Datetime is used for both date and time
No varchar (max) or varbinary (max) is available varchar (max) or varbinary (max) is available
No SSIS is included SSIS is started using
PIVOT and UNPIVOT functions are not used PIVOT and UNPIVOT functions are used.
Can’t Bulk Copy Update Bulk Copy Update
Can’t Encrypt the Database Can Encrypt the Database

 

SQL Server 2005 and SQL Server 2008

SQL Server 2005 SQL Server 2008
We can’t able to Encrypt the entire Database We can able to Encrypt the entire Database
Does not provide Backup Encryption. Introduced Back-Up Encryption.
XML-Datatype is introduced XML Datatype is implemented and used.
FILESTREAM is not there. FILESTREAM is introduced
LINQ is not there LINQ is introduced for retrieving multiple types of Data
Table-Valued Parameters is not there in sql 2005 Table-Valued Parameters is introduced in sql 2008
Merge Statement is not included Merge Statement is included
Datetime is used for both date and time
  • Major Changes in DateTime. four DATETIME
  • Data types introduced. They are DATE, TIME,
  • DATETIMEOFFSET and DATETIME2
In Sql-2005, UDTs were restricted to a maximum size of 8 kilobytes The restriction has been removed for UDTs in sql 2008
Data Synchronization is not introduced. Data Synchronization is introduced.

 

SQL Server 2008 and SQL Server 2008R2

SQL Server 2008 SQL Server 2008R2
It support maximum of 64 logical process. It support maximum of 256 logical process.
Master Data Services (MDS) part of BI is not included in SQL 2008 Master Data Services (MDS) part of BI is included in SQL 2008 R2
PowerPivot in BI is not implemented in sql 2008 PowerPivot in BI is implemented in SQL 2008R2
Introduced geospatial data types with few common features in SSRS 2008 Additional features added to geospatial data type n SSRS 2008 R2 including mapping, routing, and custom shapes

SQL Server 2008R2 and SQL Server 2012

SQL Server 2008 R2 SQL Server 2012
Code Name: Katmai Code Name: Denali
Query Page splitting is not there in SQL-2008 Query Page splitting is implemented in SQL 2012
lt can support only 1000 partitions It can support up to 15,000 partitions
The SQL Server 2008 uses 27 bit precision for spatial. The SQL Server 2012 uses 48 bit precision for Spatial.
String functions CONCATE and FORMAT are not available. String functions CONCATE and FORMAT are not available
Analysis Services in SQL Server does not have BI Semantic Model (BISM) concept. Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes:

  • Data Model
  • Buiness Logic
  • Data Access
ORDER BY Clause does not have OFFSET / FETCH. ORDER BY Clause with OFFSET / FETCH options.

 

SQL Server 2012 and SQL Server 2014

SQL Server 2012 SQL Server 2014
Memory Optimization Memory Optimized (or Hekaton) Table
Stored Procedure Native Compiled Stored Procedure.
XTP (eXtreme Transaction Processing) with Hekaton Tables and Native Compiled SP’s.
Memory Optimization with index. New Memory Optimized Indexes | Hash vs Range Index.
Memory Optimized Table Variables.
Column Stored Index ColumnStored Index (enhancements from SQL Server 2012)
Buffer Pool Extension.
AlwaysOn AlwaysOn Enhancements in SQL Server 2014
Optimizing Query Plans Optimizing Query Plans improvements with the Cardinality Estimator.

 

New Features in SQL Server 2016

  1. Query Store: The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed.
  2. Polybase: This feature will benefit you if your regular data processing involves dealing with a lot of large text files — they can be stored in Azure Blob Storage or Hadoop, and queried as if they were database tables.
  3. Stretch Database: The basics of Stretch Database are that some part of your tables (configurable or automated) will be moved into an Azure SQL Database in the cloud in a secure fashion. When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly.
  4. JSON Support: Providing the ability to quickly move JSON data into tables
  5. Row Level Security: This restricts which users can view what data in a table, based on a function. SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments where you may want to limit data access based on customer ID.
  6. Always Encrypted: Always Encrypted is new functionality through the use of an enhanced client library at the application so the data stays encrypted in transit, at rest and while it is alive in the database.
  7. In-Memory Enhancements: Optimally designed for high-speed loading of data with no locking issues or high-volume session state issues.

Leave a Reply

Your email address will not be published. Required fields are marked *