Which returns a record set in a statement.
CTE can be thought of as a temporary result set which can be defined in the SELECT,INSERT,UPDATE and DELETE statements. CTE can be self-referencing and can be referenced multiple times in the same query.
The basic syntax structure for a CTE is:
WITH expression_name [ ( column_name [,…n] ) ]
AS ( CTE_query_definition )
- CLR support To build business objects
(stored procedures,triggers, functions, etc) which enables you to execute .NET code in the DBMS.
- ServiceBroker handles the messaging between a sender and receiver in a loosely coupled manner.
- Data Encryption
Native supported functions to encrypt the data in user defined databases.
- Microsoft incorporates SMTP mail to improve the native mail capabilities.
- Multiple Active Result Sets(MARS) Allow a persistance database connection to server more requests from a single client.
- SQL server Integration Services (SSIS) has replaced
DTS(Data Transformation Services) as the primary ETL tool and ship with the SQl server for free.
- XML enhancements
- Try Catch Functionality
- Data Partioning
- Backup enhancements
- Better Email handling
- Full 64-bit support
- Database Mirroring
- Dedicated administrator connection
- Database snapshots