New Features in SQL Server 2008

Data Compression
SQL Server 2008’s data compression enables you to compress data stored in the database. This reduces storage requirements and can actually improve the performance of workloads that have high I/O requirements. SQL Server 2008 also supports compressing backups.

This feature is totally revamped in SSMS 2008. When opening “Activity Monitor” it shows a screen similar to the following image.

 

IntelliSense offers a few additional features besides just completing the world. You can see those options from SSMS

In many situations, it is necessary to pass a set of table structured values to a stored procedure or function. These values may be used for updating/population a table.

CREATE TYPE Customer AS TABLE

(

            CustomerId INT IDENTITY(1,1),

            CustomerName VARCHAR(30)

);

GO

DECLARE @T as Customer

INSERT INTO @T(CustomerName)

VALUES           (‘a’),(‘b’),(‘c’),(‘d’),(‘e’)

SELECT * FROM @T

Grouping Sets is an extension to the GROUP BY clause that lets users define multiple grouping in the same query. Grouping Sets produce a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.

SELECT year (order_date) AS Year, quarter (order_date) AS Quarter, COUNT (*) AS Orders

FROM sales_order GROUP BY GROUPING SETS ((Year, Quarter), (Year))

ORDER BY Year, Quarter

The SQL Server File Stream object allows large binary data to be stored in the file system yet remain an integral part of the database with transactional consistency.

Integrated Full-Text Search makes the change between text searching and relational data faultless, while enabling users to use text indexes to perform text searches on large text columns.

With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data and the join condition specified between them.

When merging a dataset to a table, you specify one set of criteria in which you can perform actions based on:

MERGE dbo.TargetTable tgt   — Target Table

USING dbo.SourceTable src   — Source Table

ON tgt.ID = src.ID          — Main comparison criteria

WHEN MATCHED                — When ID’s exist in both tables

— DO SOMETHING

WHEN NOT MATCHED            — When ID’s from Source do not exist in Target

— DO SOMETHING

WHEN NOT MATCHED BY SOURCE  — When ID’s from Target do not exist in Source

— DO SOMETHING

Language-Integrated Query
LINQ is Microsoft’s latest application data-access technology. It enables Visual Basic and C# applications to use set-oriented queries that are developed in the native language, rather than requiring that the queries be written in T-SQL. LINQ uses the native SqlClient to connect to SQL Server, and its development-time connection lets developers create strongly typed queries.