Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Revisions
 

SQL Server 2008 and T-SQL new features

, 5 Jul 2007
Rate this:
Please Sign up or sign in to vote.
This articel emphasizes on two key areas Microsoft data plat form vision and T-SQL new features for next generation of application development.

Introduction

This article presents the new data platform vision of Microsoft Corporation and the next generation of data-driven applications. The key areas to support their data platform vision: mission-critical enterprise data platform, dynamic development, relational data, and business intelligence (BI).

This document covers the following contexts or features of SQL Server 2008.

· Microsoft data platform vision

1. Mission-Critical Platform

2. Dynamic Development

3. Beyond Relational Data

4. Pervasive Business Insight

. New features added to T-SQL

Using the code

To execute the SQL statements in this article you should have SQL Server 2008 installed on your machine. See the link below http://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

Data Platform Vision

SQL Server 2008, the next release of Microsoft SQL Server, provides a comprehensive data platform that is more secure, reliable, manageable and scalable for your mission critical applications, while enabling developers to create new applications that can store and consume any type of data on any device, and enabling all your users to make informed decisions with relevant insights.

SQL Server 2008 capabilities deliver on the four key areas of the data platform vision.

  • Mission-Critical Platform
  • Dynamic Development
  • Beyond Relational Data
  • Pervasive Business Insight

Mission-Critical Platform

SQL Server 2008 enables IT groups to be more productive by providing a more secure, scalable, and manageable platform. SQL Server 2008 also protects valuable information in existing applications and disconnected devices. In addition, SQL Server 2008 delivers predictable query performance with an optimized platform.

Declarative Management Framework (DMF) is a new policy-based management framework and it has the following benefits.

· Ensure compliance with policies for system configuration

· Prevent/monitor changes to the system by authoring policies for the desired configuration.

· Reduce total cost of ownership by simplifying administration tasks

Dimension Design:

· Improved Supportability and Integration of Best Practices

· Enhance UI for creating and editing dimensions to guide users toward designs that follow best practices.

These include: Finish Attribute Relationship Designer, Dimension structure (presentation of attribute relationships), modification to wizards to align output with best practices, simplifying creation of composite keys, and AMO warnings (spanning all objects, not just dimensions)

Dynamic Development

SQL Server 2008, along with the .NET Framework, enables developers to build the next generation of applications. Developers can be more productive because they can work with business entities instead of tables and columns.

Accelerate Your Development with Entities

SQL Server 2008 rather than writing application logic against tables and rows, developers use entities such as 'customer' or 'order.' The ADO.NET Entity Framework enables developers to program against relational data in terms of entities; this allows developers to use Entity-Relationship modeling directly.

Some of the advancements in developing with entities in SQL Server 2008 are covered in this section.

Language Integrated Query

Language Integrated Query (LINQ) enables developers to issue queries against data in terms of their programming language instead of SQL.

It enables faultless, strongly typed, set-oriented queries in Microsoft Visual C#® or Microsoft Visual Basic® against the connected ADO.Net stack (SqlClient)

ADO.Net Object services

The Object services layer of the ADO.NET stack enables materialization, change tracking, and the persistence of data as CLR objects.

SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development and supports entities.

Occasionally Connected Systems

With mobile devices and workers on the go, 'occasionally connected' has become a way of life. In combination with Microsoft Visual Studio®, SQL Server 2008 enables the rapid creation of occasionally connected applications through the combination of new synchronization services in ADO.NET and offline designers in Visual Studio.

SQL Server 2008 provides support for change tracking, enabling customers to develop cache-based, synchronization-based, or notification-based applications using a robust implementation with minimal performance overhead.

Beyond Relational Data

Increasingly, applications are incorporating a much wider variety of data types than are traditionally supported by a database.

SQL Server 2008 support nonrelational data by providing new data types that enable developers and administrators to store unstructured data such as documents and images.

The following sections outline some of the advancements in the area of data storage.

Store Any Type of Data

SQL Server 2008 allows users to enabling users to access documents as data, encode complex hierarchies within XML, and query across relational and text data.

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.

Sparse columns

Sparse columns allow users to store object models onto relational data without undergoing large space costs. They also allows users to build complex content management applications with the underlying database.

Large user-defined types

Large user-defined types allows users to expand the size of defined data types by eliminating the 8‑KB limit.

Pervasive Business Insight

Server 2008 provides a more scalable infrastructure that enables IT to drive business intelligence throughout the organization, manage reports and analysis of any size or complexity, and at the same time provide deep integration with the Microsoft Office System.

Next-Generation Data Warehousing

SQL Serve 2008 provides data warehouse platform that enables organizations to integrate data into the data warehouse faster, scale and manage growing volumes of data and users, while delivering insights to all users.

Following are some of the advancements in data warehousing.

MERGE SQL statement

The MERGE statement performs INSERT/UPDATE/DELETE operations on a target table based on the results of a join with a source table.

or

The MERGE statement performs INSERT/UPDATE/DELETE operations on checking whether a row exists and then executing inserts or updates.

MERGE, a very powerful statement to combine insert/updates and even deletes in a single statement

Example 1:

CREATE TABLE STOCK (STOCK VARCHAR(10) PRIMARY KEY, QTY INT CHECK(QTY > 0))

CREATE TABLE TRADES (STOCK VARCHAR(10) PRIMARY KEY, DELTA INT)

INSERT STOCK VALUES ('MSFT', 10); INSERT STOCK VALUES ('TXN', 5)

INSERT TRADES VALUES ('MSFT', 5); INSERT TRADES VALUES ('TXN', -5);

INSERT TRADES VALUES ('SBUX ', 3)

-- Apply changes to the STOCK table based on daily TRADES tracked in the TRADES table.

-- Delete a row from the STOCK table if all the STOCK has been sold.

-- Update the quantity in the STOCK table if you still hold some STOCK after the daily TRADES.

-- Insert a new row if you acquired a new STOCK.

-- As a result, TXN is deleted, SBUX inserted, MSFT updated

MERGE STOCK S -- target table

USING TRADES T -- source table

ON S.STOCK = T.STOCK

WHEN MATCHED AND (QTY + DELTA = 0) THEN

DELETE -- delete STOCK if entirely sold

WHEN MATCHED THEN

-- update STOCK if you still hold some STOCK

UPDATE SET QTY = QTY + DELTA

WHEN NOT MATCHED THEN

-- insert a row if the STOCK is newly acquired

INSERT VALUES (STOCK, DELTA)

-- output details of INSERT/UPDATE/DELETE operations

-- made on the target table

OUTPUT $action, inserted.STOCK, deleted.STOCK

SELECT * FROM STOCK

GO

Example 2:

DECLARE @Code VARCHAR(3);

DECLARE @Description VARCHAR(15);

SET @Code = 'ABC';

SET @Description ='Merge';

MERGE INTO tblData T

USING (SELECT @Code AS code, @Description AS desc) S

ON T.Code = S.code WHEN MATCHED THEN

UPDATE SET Description = S.desc WHEN NOT MATCHED

THEN INSERT VALUES (code, desc);

Insert Multiple Rows in a single statement

This new feature enables the developer to insert multiple rows in a single SQL Statement.

Example:

--SQL Server 2005

INSERT dbo.EMPLOYEE(SALARY) VALUES (1000)

INSERT dbo.EMPLOYEE(SALARY) VALUES (2000)

INSERT dbo.EMPLOYEE(SALARY) VALUES (3000)

INSERT dbo.EMPLOYEE(SALARY) VALUES (4000)

--SQL Server 2008

INSERT dbo. EMPLOYEE(SALARY) VALUES (1000),(2000),(3000),(4000)

Table Value Parameters:

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.

Example:

Creating a custom type

CREATE TYPE EMPLOYEE (EMP_ID int, NAME varchar(10), SALARY money);

CREATE PROC NEW_EMPLOYEE (@NEW_EMP EMPLOYEE)
AS
INSERT INTO EMP_DETAILS SELECT * FROM @ NEW_EMP;

Data compression - shrink data warehouse fact tables, while also improving query performance.

Backup compression - less storage and backups run significantly faster because less I/O is required.

Partitioned table parallelism - SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by increasing the performance on large partitioned tables.

Star join query optimizations

Star join query optimizations reduce query response time by recognizing data warehouse join patterns.

Resource Management

Use Resource Governor to manage CPU and memory resources within an instance of the relational engine.

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

Change Data Capture (CDC)

With CDC, changes are captured and placed in change tables. This feature captures the complete content of changes and maintains cross-table consistency and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.

Scalable Integration Services

The two key advancements in scalability of Integration Services include:

· SQL Server Integration Services (SSIS) pipeline improvements

Data Integration packages can scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.

· SSIS persistent lookups

SQL Server Integration Services (SSIS) will increase the performance of lookups so that it scales to meet the largest tables.

Reference:

MSDN

SQL Server 2008 Product Overview

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

No Biography provided

Comments and Discussions


| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150123.1 | Last Updated 6 Jul 2007
Article Copyright 2007 by Rajesh Mascon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid