New Features Added in SQL Server
This
post helps you in quickly reviewing the “New Features Added in SQL
Server” from 2008 to 2016. The most common interview question is
“What are the new features added in SQL Server XXXX?” To simplify
the answer we are just giving the single line abbreviations. While
preparing for an interview just have a quick look and try to remember
4 to 5 features on current working and last released version. Let’s
say you are currently working on 2012 then you need to look for 2012
and 2008 R2. Also people would expect you to know few new
features added in the latest version which is SQL Server 2016.
Features are categorised for DBA, Developer and Security. That
doesn’t mean that SQL Developer need not look into DBA section or
vice versa. This is just to make it more readable, when a feature is
more related to Developer we added in Developer section when a
feature is more relevant administration part it will be in DBA
section. Believe this will be helpful for a quick review for version
wise feature support.
New Features Added in SQL Server 2008
SQL DBA:
Activity
Monitor: Great
tool to showcase resource utilization and performance using GUI.
Policy
Based Management: The
ability to manage standards on multiple servers
Enhanced
Database Mirroring: Automatic
data page repair and compressing outgoing log stream
Resource
Governor: We
can configure it to control SQL Server resource utilization and
workload.
External
Key Management: Provides
a comprehensive solution for encryption and key management.
Hot
Add CPU: Adding
resources online without downtime.
PowerShell: SQL
Server 2008 ships with PowerShell snap-in built on .Net framework
2.0.
Table
Compression: Compress
data and index pages to save memory and I/O.
Backup
Compression:
Native backup compression.
Performance
data collection: Centralized
data repository for storing and reporting performance data
Extended
Events: Event
collection is easier now compares to running a trace
SQL Developer:
File-stream
Data: To
store binary large objects out on the file system.
Multi-Server
Queries: being
able to submit a batch of queries against multiple servers
simultaneously.
Object
Explorer Details: 36
possible columns of information about a database
Object
Search: Searches
for all objects within the selected scope: server, database, tables
SSMS
Debug: the
ability to debug any Transact-SQL within SSMS using breakpoints etc.
Intellisense
in SQL Server Management Studio: interactive
help support similar to Visual Studio.
Plan
Freezing: Now
we can lock down query plans
Spatial
Data Types: Geometry
and Geography
DATE
/ TIME Data Types: DATE,
TIME, DATETIMEOFFSET
CLR
Enhancements: User-defined
aggregates and User-defined types now enhanced up to 2GB.
Table-Valued
Parameters: It
allows stored procedures to accept and return lists of parameters.
MERGE
command: For
incremental load using INSERT, UPDATE and DELETE operations.
HierarchyID
Datatype: Provides
tree like functionality among the data elements in a table
Grouping
Sets: An
extension to the GROUP BY
Filtered
Indexes and Statistics: We
can create non clustered index on a subset of a table
Object
Dependencies: New
DMV’s provided for reliable information on depending objects
Sparse
Columns: Columns
that are optimized for the storage of NULL values.
Security:
TDE
– Transparent Database Encryption: Encrypt
database without code changes
Change
Data Capture: Track
data changes.
SQL
Auditing: the
ability to audit at the server, database and table levels.
New Features Added in SQL Server 2008 R2
SQL DBA:
SQL
Server 2008 R2 Datacenter: Supports
256 logical processors
SQL
Server Utility: Central
repository control for multiple SQL Servers
Multi
Server Dashboards: Dashboards
showing combined server data can be created.
SQL Developer:
Master
Data Services: To
manage enterprise central database
Data-Tier
Application: To
map Database and Visual Studio builds
StreamInsight: Can
analyse streaming data on the fly
Unicode
Compression: New
algorithm for Unicode storage
PowerPivot
for SharePoint and Excel: Process
datasets and reports.
Report
Builder 3.0: Improved
visualizations for SSRS
Parallel
Data Warehouse: Data
warehouses to be scaled over several physical SQL Servers.
SSMS
enhancements for SQL Azure: SSMS
support for cloud
New Features Added in SQL Server 2012
SQL DBA:
Licensing
Model: It’s
not based on sockets introduced new core based licensing model
Edition
changes: Introduced
new BI edition and retired Datacenter, Workgroup and standard for
small business.
AlwaysOn
availability: Provides
both Disaster Recovery and High Availability
Enhanced
PowerShell Support: More
CMDLETS introduced
Windows
Server Core: Core
is the GUI less version of Windows that uses DOS and PowerShell for
user interaction SQL Server 2012 supports Windows Core
SQL
Azure Enhancements: DB
size limit increased to 150 GB, Azure data sync allows hybrid model
etc.
SQL Developer:
Indirect
checkpoints: Now
we can configure checkpoint intervals database wise
ColumnStore-Index: Stores
columns on page instead of rows
SSDT: BIDS
is now SSDT SQL Server Data Tools.
FileTable: Builds
upon FILESTREAM and SQL Server can access windows files on
non-transactional
Sequence
objects: an
alternative for IDENTITY property
THROW: Improved
error handling
New
Conversion Functions: PARSE,
TRY_PARSE, TRY_CONVERT
New
Logical functions: CHOOSE,
IIF
New
String functions: CONCAT,
FORMAT
New
Date & Time functions: DATEFROMPARTS,
DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS,
SMALLDATETIMEFROMPARTS, TIMEFROMPARTS.
ROWS
and RANGE: Support
for Window framing on result sets
LAG
and LEAD: To
get the previous and next rows data
New
Rank distribution functions: PERCENT_RANK,
PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST
OFFSET
/ FETCH: Supports
paging for ad hoc queries
FORCESCAN: New
table hint
WITH
RESULT SETS: More
control on stored procedure returned result set metadata
sp_describe_first_result_set: Advanced
version for SET FMTONLY option. Also DMV’s added for this
Statistical
Semantic Search: Advanced
feature builds upon the existing full-text search
Data
Quality Services- DQS: A
service added to MDS for advanced data profiling and cleansing
Power
View: Light
weight tool for BI reports
BI
Semantic Model: Hybrid
model that allows one data model will support all BI experiences
Big
Data Support: ODBC
driver for SQL Server that will run on a Linux platform etc.
Security:
User-Defined
Server Roles: Customization
for Server Roles
Database
Audit: Like
SQL Server Audit and it performs audits database level
Contained
Databases: Users
can be added on a database without login can easier migration
New Features Added in SQL Server 2014
SQL DBA:
Standard
Edition Memory Capacity: Increased
to 128 GB whereas 2012 standard editions it is 64 GB.
SQL
Server Developer Edition is free: Microsoft
made SQL Server 2014 Developer Edition license free.
AlwaysOn
AG more secondaries: 2012
supports 4 whereas 2014 supports up to 8 secondaries
AlwaysOn
AG readable secondaries: Readable
secondaries remain online even though primary replica is down.
Add
Azure Replica Wizard: Helps
us to create asynchronous secondary replicas in Windows Azure
Buffer
Pool Extension: SQL
Server 2014 enable users to use Solid State Disk (SSD) to expand the
SQL Server 2014 Buffer Pool as non-volatile RAM (NvRAM).
RG
I/O control: In
2014 Resource Governor can also be configured to control the physical
I/O.
Backup
Encryption: SQL
Server 2014 introduced native backup encryption. It supports several
encryption algorithms AES 128, AES 192, AES 256, and Triple DES.
Managed
Backup to Azure: SQL
Server 2014 native backup supports Windows Azure with auto
scheduling.
SQL Developer:
In-Memory
OLTP Engine: We
can enable memory optimization for selected tables and stored
procedures.
Updateable
Column-store Indexes: On
SQL Server 2012 to utilize the column-store index, the underlying
table had to be read-only. SQL Server 2014 eliminates this
restriction. In 2014 Column-Store Index must use all the columns in
the table and can’t be combined with other indexes.
Cardinality
Estimator Improvements:
Cardinality Estimator redesigned in SQL Server 2014.
Delayed
Durability: Introduced
delayed durable transactions. A delayed durable transaction returns
control to the client before the transaction log record is written to
disk. Durability can be controlled at the database level, COMMIT
level, or ATOMIC block level.
Partition
Switching and Indexing: The
individual partitions of partitioned tables can now be rebuilt.
Lock
priority for Online Operations:
The ONLINE = ON option now contains a WAIT_AT_LOW_PRIORITY option
which permits you to specify how long the rebuild process should wait
for the necessary locks. We can also be able to configure terminating
the blocking process related to rebuild.
Incremental
Statistics: Now
we can create partition level statistics by using the option
INCREMENTAL
Inline
Specification for Indexes: Inline
specification of CLUSTERED and NONCLUSTERED indexes is now allowed
for disk-based tables. Creating a table with inline indexes is
equivalent to issuing a create table followed by corresponding CREATE
INDEX statements. Included columns and filter conditions are not
supported with inline indexes.
SELECT
… INTO Enhancement: This
statement now can be operated in parallel
Power
View and Power BI: Power
View now in 2014 supports OLAP cubes along with the tabular data.
Power BI for office 365 is a cloud based BI solution.
Security:
SELECT
ALL USER SECURABLES: A
new server level permission. When granted, a login such as an auditor
can view data in all databases that the user can connect to.
CONNECT
ANY DATABASE: A
new server level permission when granted, a login can connect to any
existing / future database in that instance. Combine with SELECT ALL
USER SECURABLES or VIEW SERVER STATE to allow an auditing process.
IMPERSONATE
ANY LOGIN: A
new server level permission when granted, allows a middle-tier
process to impersonate the account of clients connecting to it, as it
connects to databases.
New Features Added in SQL Server 2016
SQL DBA:
SQL
Server Developer Edition is free: Microsoft
made SQL Server 2014 Developer Edition license free and same
continued with SQL Server 2016 Developer Edition.
AlwaysOn
Enhancements: Standard
Edition will come with AGs support with one database per group
synchronous or asynchronous, not readable (HA/DR only). 3
sync replicas supported whereas it was 2 in SQL 2014. Listener will
be able to do round-robin load balancing of read-only requests on
multiple secondaries. Now supports Microsoft DTC. SQL Server AlwaysOn
to Azure Virtual Machine.
Database
Scoped Configurations: The
new ALTER DATABASE SCOPED CONFIGURATION allows us to control database
level configurations. Ex: Parameter sniffing at database level.
Striped
Backups to Microsoft Azure Blob Storage: In
SQL Server 2016, SQL Server backup to URL using the Microsoft Azure
Blob storage service now supports striped backups sets using block
blobs with the maximum backup size of 12.8 TB.
File-Snapshot
Backups to Microsoft Azure Blob Storage: In
SQL Server 2016, SQL Server backup to URL now supports using Azure
snapshots to backup databases in which all database files are stored
using the Microsoft Azure Blob storage service.
Managed
Backup: In
SQL Server 2016 SQL Server Managed Backup to Microsoft Azure uses the
new block blob storage for backup files. It supports automatic and
custom scheduling, backups for system databases and backups for
databases with simple recovery model.
No
need to enable Trace flag 4199: Most
of the query optimizer behaviours controlled by this trace flag are
enabled unconditionally under the latest compatibility level (130)
TempDB
enhancements: Trace
Flags 1117 and 1118 are not required for tempdb anymore. When tempdb
is having database files all files will grow at the same time based
on growth settings. All allocations in tempdb will use uniform
extents. By default, setup adds as many tempdb files as the CPU count
or 8, whichever is lower. We can have the control on tempdb
configuration while installing SQL Server 2016.
New
Default Database Size and Autogrow Values: For
model database these default values are changed in 2016. Default data
and log file size is 8 MB and auto-growth is 64 MB.
MAXDOP
option for DBCC: Now
we can specify MAXDOP option for DBCC CHECKTABLE, DBCC CHECKDB and
DBCC CHECKFILEGROUP.
Replication
Enhancements: Replication
supports memory-optimized tables and replication support enabled for
Azure SQL Database.
SQL Developer:
ColumnStore
Index Enhancements: A
read-only nonclustered columnstore index is updateable after upgrade
without rebuilding the index. Also columnstore indexes can be created
for In-Memory tables
Live
Query Statistics: Management
Studio provides the ability to view the live execution plan of an
active query.
Query
Store: It
can quickly find the performance differences caused by changes in
query plans. The feature automatically captures a history of queries,
plans, and runtime statistics, and retains these for your review. It
separates data by time windows, allowing you to see database usage
patterns and understand when query plan changes happened on the
server.
Temporal
Tables: SQL
Server 2016 now supports system-versioned temporal tables. A temporal
table is a new type of table that provides correct information about
stored facts at any point in time.
Built-in
JSON support: Java
Script Object Notation (JSON) SQL Server 2016 enables ability to move
JSON data to SQL Server tables using various clauses and functions.
Ex: FOR JSON, OPENJSON, ISJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY
PolyBase: PolyBase
allows you to use T-SQL statements to access data stored in Hadoop or
Azure Blob Storage and query it in an ad-hoc fashion. It also lets
you query semi-structured data and join the results with relational
data sets stored in SQL Server. PolyBase is optimized for data
warehousing workloads and intended for analytical query scenarios.
Stretch
Database: SQL
Server migrate historical data transparently and securely to the
Microsoft Azure cloud. SQL Server handles the data movement in the
background based on the policy we can configure. The entire table is
always online and queryable. Stretch Database doesn’t require
any changes to existing queries or applications the location of the
data is completely transparent to the application.
In-Memory
OLTP Enhancements: To
be frank it’s not just enhancements. In-Memory OLTP on 2014 is just
a trail version and in 2016 this is the first version where we have
seen lot of things has been fixed and supported.
In-Memory
– ALTER TABLE is log-optimized, and runs in parallel:
Only the metadata changes are written to the log also now it can run
in parallel.
In-Memory
Statistics: Now
statistics are updated automatically
In-Memory
Parallel Scan: Memory-optimized
tables, and hash indexes, are now scannable in parallel.
In-Memory
– LOBs with large row size: Now
memory optimized tables can have LOB type columns
In-Memory
– TDE and MARS: MARS
and TDE support enabled for In-Memory optimized tables
In-Memory
T-SQL support: SQL
Server 2016 overcomes the maximum limitations on 2014 In-Memory OLTP
T-SQL Support. Now In-Memory OLTP supports OUTPUT clause,
UNIQUE index, FOREIGN KEY, Alter, Schema changes, Triggers, Check
constraint, UNIION, UNION ALL, DISTINCT, OUTER JOIN, Sub queries and
lot many native features supports in 2016 In-Memory optimized tables
and natively compile stored procedures.
Foreign
Key Relationship Limits: SQL
Server 2016 increases the limit for the number of other table and
columns that can reference columns in a single table from 253 to
10,000.
Support
for UTF-8: BULK
INSERT, bcp Utility and OPENROWSET now support the UTF-8 code page.
TRUNCATE
TABLE – Partition:
The TRUNCATE TABLE statement now permits the truncation of specified
partitions.
ALTER
TABLE Enhanced: Now
allows actions to be performed while the table remains available.
NO_PERFORMANCE_SPOOL: New
query hint can prevent a spool operator from being added to query
plans.
DROP
IF:
Really useful feature. It drops the object if it exists in database.
sp_execute_external_script:
Advanced
Analytics Extensions allow users to execute scripts written in a
supported language such as R.
COMPRESS
– DECOMPRESS:
Functions to convert values into and out of the GZIP algorithm.
New
DATETIME:
DATEDIFF_BIG, AT TIME ZONE functions and sys.time_zone_info view are
added to support date and time interactions.
STRING_SPLIT
and STRING_ESCAPE: New
string functions added
Security:
Row-Level
Security: This
is a predicate based access control.
Always
Encrypted: Encrypt
entire data (Data file, Log file, Backup, Communication Channel) and
only application can see the data.
Dynamic
Data Masking: Dynamic
data masking limits sensitive data exposure by masking it to
non-privileged users.
Transparent
Data Encryption Enhanced: TDE
supports Intel AES-NI hardware acceleration of encryption. This will
reduce the CPU overhead of turning on Transparent Data.
AES
Encryption for Endpoints: The
default encryption for endpoints is changed from RC4 to AES.
New
Credential Type: A
credential can now be created at the database level in addition to
the server level credential that was previously available.
No comments:
Post a Comment