Close Menu

SQLSERVER



 



SQL- Server Differences between each version from 2000, 2005, 2008, 2008R2, 2012, 2014


There are soomany instances u might end up with a question if you really need to decide for an upgrade. However the question arises "Do we really need to upgrade and to what version should I upgrade"

The journey of SQL Server .initially started in 1988. Since the start of the SQL-Server there were many changes and enhancements took place.

However SQL-Server got its importance and was recognized world wide after the release of SQL-Server 2000 at a whole new level. After the release of SQL-Server 2000 it was widely introduced and used by business .After the year 2000 the E-commerce boom took place and since then because of increase of the need for new features, Microsoft started started fixing bugs by releasing new patches for existing sql-server 2000.

After the release of 2000 microsoft did not release another until 2005. however Microsoft wanted to really make a difference at what they are really Good at which is User friendlyness .So microsoft wanted to intorduce SSMS an all In one tool to do most of the complicated regular work they integrated Query-Analyser into the Enterprise Manager and released the new Sqlserver 2005 with all new enhanced features and new GUI. Since then SSMS (Sequel Server Management Studio) Still exists.

From SQL-Server 2000 to todays SQL-Server 2017 microsoft kept adding new features along with new market standards and with the Competition. Many new Security features, Enhancements and possibilities started to show up in every new edition and version of SQL-server.

If at all you are Currintly Running on MS-SQL-Server 2008 and looking at Feature Comparison's for upgrading Pleas see SQL-Server Feature Comparision from 2008 to 2016

Below given are the differences between each version from 2000, 2005, 2008, 2008R2, 2012, 2014

Please Note: I will keep updating this page as soon as I come across anything new


2000 and 2005


2000 2005
No Schema Schema
No reporting services Reporting services
No XML Inroduced XML
No Compression Compression
Query analyzer Query editor
No Varchar, Varbinary Varchar, Varbinary
No encryption Encryption of Tables
MAX 16 instance max 50 instances
65535 Max DB's allowed 1048575 Max DB's
No SSMS SSMS
No Mirroring Mirroring
No Snapshotisolation Snapshotisolation
No SSIS SSIS

 

2005 and 2008


2005 2008
Tabular level Encryption Full DB Encryption
  Encrypted backup
64 Logical Processe 256 logical process
  File Streaming
  Merge Statement
only Datetime DATE, time , DATETIMEOFFSET, DATETIME2
  Policy based Management
(facets)
  data compression
  backup compression
  GUI DIFFERENCES

 

2008 and 2008R2


2008 2008R2
64 Logical Process 256 logical process
  Power PIVOT Introduction
  Data shapes like Mapping, routing, &custom shapes
   
   
   
   
   
   
   

2008 and 2012


2008 2012
TRY….CATCH THROW
Log Shipping, Replication, Mirroring & Clustering ALWAYS ON
no supportfor Windows Server Core. Support for Windows Server Core
Columnstore Indexes not supported. New Columnstore Indexes feature.
PowerShell Supported Enhanced PowerShell Supported
Distributed replay features not available. Distributed replay Feature.
PowerView not available in BI features PowerView Introduction.
EXECUTE … with RECOMPLIE feature Enhanced EXECUTE with many option like WITH RESULT SET….
Maximum numbers of connections is 32767 unlimited concurrent connections.
27 bit bit precision for spatial calculations. 48 bit precision for spatial calculations
TRY_CONVERT() and FORMAT() functions are not available TRY_CONVERT() and FORMAT() functions are newly included
ORDER BY Clause does not have OFFSET / FETCH options for paging ORDER BY Clause have OFFSET / FETCH options for paging
In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. In SQL Server 2012,support for server auditing is expanded to include all editions of SQL Server.
Sequence is not available in SQL Server 2008 Sequence is included in SQL Server 2012.
The Full Text Search Enhanced Full Text Search
Analysis Services in SQL Server does not have BI Semantic Model Analysis Services will include a new BI Semantic Model (BISM). BISM is a
(BISM) concept.
The BCP not support –K Option. The BCP Utility and SQLCMD Utility utilities enhanced with -K option, which allows to specify read-only access to a secondary replica in an AlwaysOn availability group.
sys.dm_exec_query_stats

sys.dm_exec_query_stats added four columns to help troubleshoot long running queries.

new function concat to strings


2012 and 2016


2012 2014
IN-MEMORY OLTP
ony resource governer IO Governance
VDHX VDHX Support
allways on Enhanced ALlways on
Query optimization Enhancements
power Query
Windows Azure HDInsight Service
Analytics Platform System (PDW V2)
power BI, Power Map, Mobile interfaces
AZURE backup
Cloud-Backup encryption and support
ALWAYS ON Replicas for cloud DR
Windows Azure Deployment UI
Larger SQL Server VMs and memory sizes now available in Windows Azure