Sql Server 2000 :
- Version 8
- Query analyser & enterprise manager are seperate
- We can create 65535 databases only
- Datetime datatype used for both date & time
Sql Server 2005 :
- Version 9
- Query analyser & enterprise manager are combined as SSMS
- XML datatype is introduced
- We can create max (2^20-1) databases
- Ranking functions (Row_Number, Rank, Dense_Rank, Ntile for paging)
- varchar(max) datatype
- Exception Handling (Try catch block)
- Database mirroring
- CTE (Common table expression)
- Pivot, Unpivot
- Cube, Rollup, Grouping set
- Synonyms
- Bulk copy insert
- DDL triggers
- Table fragmentation
- Full text search
- Can compress the table & indexes (In sql 2005 SP2)
- SSIS introduced
- Table datatype introduced
Sql Server 2008 :
- Version 10
- XML datatype used
- Initialize variables (Declare & initialize variable in single statement)
- Compound assignment operators (+=, -=, *=, /=, %=)
- Enhanced convert function (Conversion between binary & hexadecimal)
- Merge statement
- Filtered Index
- Change data capture
- Can encrypt entire db in 2008 introduced
- PBM (Policy based management) introduced
- CMS (Centralized management server) introduced
- Table datatype available
- Date, time,geospatial,timestamp introduced for different date time format
Sql Server 2008 R2 :
- Version 10.50
- PowerPivot for sharepoint, excel
- Multi-server administration & data-tier application
- Master data services introduced
- Extended protection to connect database engine
Sql Server 2012 :
- Version 11
- AlwaysOn Availability Groups HADR (High Availability and Disaster Recovery)
- Column store Index introduced
- User-defined server roles
- Sql Server data tool for BI
- Order by clause with Offset/fetch option
- New features to sql try_convert(), format()
- In-memory OLTP introduced
Sql Server 2014 :
- Version 12
- In-memory OLTP extended
- Column store Index extended
- Resource governor for I/O & I/O control
- Incremental statistics
- Most other features are related to Sql Azure
Sql Server 2016 :
- Version CTP3.2
- Integration with Hadoop
- Data masking
- Live execution plan
- Row level security
- Non Clustered Index Key length in SQL Server 2016 CTP 3.0 has been increased from 900 bytes to 1700 bytes
SQL Versions & Builds :
| RTM (no SP) | SP1 | SP2 | SP3 | SP4 | |
|---|---|---|---|---|---|
 SQL Server 2017codename vNext | 14.0.1000.169 *new | ||||
| SQL Server 2016 | 13.0.1601.5 | 13.0.4001.0 or 13.1.4001.0 | 13.0.5026.0 or 13.2.5026.0 | ||
| SQL Server 2014 | 12.0.2000.8 | 12.0.4100.1 or 12.1.4100.1 | 12.0.5000.0 or 12.2.5000.0 | ||
| SQL Server 2012 codename Denali | 11.0.2100.60 | 11.0.3000.0 or 11.1.3000.0 | 11.0.5058.0 or 11.2.5058.0 | 11.0.6020.0 or 11.3.6020.0 | 11.0.7001.0 or 11.4.7001.0 |
| SQL Server 2008 R2 codename Kilimanjaro | 10.50.1600.1 | 10.50.2500.0 or 10.51.2500.0 | 10.50.4000.0 or 10.52.4000.0 | 10.50.6000.34 or 10.53.6000.34 | |
| SQL Server 2008 codename Katmai | 10.0.1600.22 | 10.0.2531.0 or 10.1.2531.0 | 10.0.4000.0 or 10.2.4000.0 | 10.0.5500.0 or 10.3.5500.0 | 10.0.6000.29 or 10.4.6000.29 |
| SQL Server 2005 codename Yukon | 9.0.1399.06 | 9.0.2047 | 9.0.3042 | 9.0.4035 | 9.0.5000 |
| SQL Server 2000 codename Shiloh | 8.0.194 | 8.0.384 | 8.0.532 | 8.0.760 | 8.0.2039 |
| SQL Server 7.0 codename Sphinx | 7.0.623 | 7.0.699 | 7.0.842 | 7.0.961 | 7.0.1063 |
All SQLServer service packs are cumulative, meaning that each new service pack contains all the fixes that are included with previous service packs and any new fixes.
Reference :
