Thursday, February 21, 2013

New Features in SQL Server 2008

Posted by Rahul Kharde at 2:27 AM
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.
Activity Monitors
This feature is totally revamped in SSMS 2008. When opening “Activity Monitor” it shows a screen similar to the following image.

IntelliSense for Query Editing

IntelliSense offers a few additional features besides just completing the world. You can see those options from SSMS
  1. List Members
  2. Parameter Info
  3. Quick Info
  4. Complete Word
  5. Refresh Local Cache
Table-Value Parameter
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
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.
Example
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
File Stream data
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
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.

Marge Statement
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:
  • The rows that match
  • The rows that do not match in the source dataset
  • The rows that do not match in the target table
Syntax


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.


If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top