DBA THINGS

SQL Server Features

Sql Server 2000 :

  • Version 8
  • Query analyzer & enterprise manager are separate
  • We can create 65535 databases only
  • Datetime datatype used for both date & time

Sql Server 2005 :

  • Version 9
  • Query analyzer & 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 CTP2.2
  • Integration with Hadoop
  • Data masking
  • Live execution plan
  • Row level security

Reference : dbavimal.blogspot.com

 

Sachin Diwakar

,