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.
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
· 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)
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.
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 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.
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.
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
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
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.
--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.
Creating a custom type
CREATE TYPE EMPLOYEE (EMP_ID int, NAME varchar(10), SALARY money);
CREATE PROC NEW_EMPLOYEE (@NEW_EMP EMPLOYEE)
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.
Use Resource Governor to manage CPU and memory resources within an instance of the relational engine.
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
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.
SQL Server 2008 Product Overview