1.Intellisense in SQL Server Management Studio – It is easy to learn the database schema and the objects on the fly with this feature
2.Inline Variable Assignment – Inline variable initialization is possible now
DECLARE @i INT = 2
SELECT @i AS VALUE
3.Compound Assigment of variables
DECLARE @i INT = 3
SELECT @i += 1
4.Filtered Indexes - Filtered index is used to index a portion of rows in a table. It is an optimized nonclustered index which can improve query performance and reduce index cost.
5.Table Valued Parameters – These are new parameter types in SQL Server 2008 which allows to send mulitple rows of data to a T-SQL statement or routine.
6.Row Constructors – These allow to insert multiple rows with a single INSERT statement
7.Merge statement – It is a new DML statement which allows to perform Insert/Update/Delete operations on one table using single scan.
8.New datatypes – SQL 2K8 has various new datatypes like spatial datatypes, datetime datatypes (DATE, TIME, DATETIME2) and Hierarchy datatypes. Of all FileStream Data type interests me the most (we don’t have to be creative inventing ways to store unstructured data anymore).
9.Grouping sets – This clause allows us to easily specify combinations of groupings to see different levels of aggregated data.
10.Sparse Columns – Columns created with this clause takes less space than the regular columns when it contains zero or null. These also allow us to create tables with more than 1024 columns.