United States   Change   |   All Microsoft Sites

Home

Microsoft Home  |  Servers and Tools

Programmability

T-SQL Enhancements

T-SQL Enhancements

Overview

Microsoft SQL Server 2008 enables developers to be more productive and more easily create the next generation of robust database solutions using the powerful SQL Server T-SQL programming language and rich data types that manage virtually any type of data.

Top New Features

  • Precisely store and manage date and time information

  • Store sparsely populated data efficiently using Sparse Columns

  • Integrated Full-Text Indexes provide high-performance, scalability and manageability

  • User Defined Types and Aggregates over 8KB

  • Pass large amounts of data to functions or procedures using new Table-Valued Parameters

  • Perform multiple operations with the new MERGE command

  • Model hierarchical data using the new HierarchyID data type

  • Build location-aware apps using new spatial data types, spatial methods and spatial indexes

  • Manage files and documents efficiently using Filestream

  • Identify dependencies across objects and databases using New Dependency Management

  • Faster queries and reporting with Grouping Sets in GROUP BY

  • Filtered Indexes provide faster data access for subsets of data

  • Change Data Capture automates database change tracking

  • Fully aligned and compatible with Windows Vista collation support

Develop powerful applications

Use SQL Server 2008’s new and enhanced T-SQL programming features to create next generation database applications that support the most demanding end-user requirements.

New Table-Valued Parameters

  • Pass large amounts of data efficiently to functions and procedures using new Table-Valued Parameters

T-SQL Enhancements

  • Perform multiple operations efficiently with the new MERGE command

  • Benefit from fully compatibility with Windows Server collation support, plus experience other programming improvements including the ability to:

    • Insert multiple rows using a single INSERT statement

    • Operate on values using rich assignment operators

    • Initialize variables during declaration

SQLCLR Enhancements

  • Create powerful User Defined Types and User Defined Aggregates that are greater than 8KB in size

  • Create User Defined Aggregates that allow multiple input arguments

Dependency Management

  • Streamline development by easily identifying dependencies across objects and databases using New Dependency Management

Related Links

Deep Dive

Control any type of data

Leverage SQL Server 2008’s enhanced data type support to manage relational and non-relational data including precise date and time management, XML data, external documents and files, and new spatial information using either planar or geodetic representations.

New DATE and TIME Data Types

  • Store more precise date and time information with larger year range, user definable fractional precision, and time zone awareness using the following new ANSI SQL Standard compatible data types:

    • DATE – new date-only type

    • TIME(precision) - time-only type

    • DATETIMEOFFSET(precision) - time zone aware DATETIME type

    • DATETIME2(precision) - new type with larger fractional seconds and year range than DATETIME

New HIERARCHYID Date Type

  • Model hierarchical data such as org charts and files and folders using the new HIERARCYID data type and easily perform operations on hierarchy data using powerful built-in methods for manipulating hierarchies efficiently

New Support for Spatial Data

  • Build powerful location-aware applications using SQL Server’s new spatial data types and built-in spatial functionality

  • Create responsive and highly engaging solutions that take advantage of the built-in spatial indexing capabilities

Manage files and documents with new FILESTREAM data type

  • Manage files and documents efficiently while leveraging SQL Server security and transaction support using the powerful new FILESTREAM data type

  • Move files and documents to economical hardware and benefit from low cost file system storage that provides storage capacity limited only to volume size

  • Dual programming model enables the reuse of existing BLOB-centric T-SQL on new FILESTREAM BLOBs with the advantage of T-SQL transactional semantics, or take advantage of file system streaming access through Windows file management APIs that provide flexible operations on files and documents

  • Additional benefits include:

    • Consistency between metadata and the data store

    • Rich data streaming performance

    • Reduction in database size

Related Links

Deep Dive

Deliver optimized solutions

Enhancements in the SQL Server storage engine and data store enables developers to store, manage and find data efficiently using sparse columns, filtered indexes and a new fully integrated Full Text Search.

Sparse Columns

  • Store semi-structured and sparsely populated sets of data efficiently using Sparse Columns

  • Allows NULL data to consume no physical space in a database, thereby ensuring optimized data storage

Fully-integrated Full-Text Search

  • Quickly find textual information across databases by leveraging SQL Server’s new high-performance fully integrated Full-Text search technology

  • Manage how Full-Text Indexes are created and stored to build powerful and scalable applications

Filtered Indexes

  • Experience a new level of efficient data access and index storage using new Filtered Indexes that provide high-performance lookups of subsets of data

Grouping Sets

  • Experience faster queries and reporting with Grouping Sets through powerful ANSI standard compliant extensions to the GROUP BY clause

  • Define multiple groupings in the same query to produce a high-speed single result set that is equivalent to the results from a UNION ALL of differently grouped rows

Change Data Capture

  • Automatically captures and maintains changes to data and schema across tables, which eliminates the need to develop custom change tracking logic

  • Built-in T-SQL methods enable developers to capture database

Related Links

Deep Dive

Visual Studio Integration

Bring all of the Microsoft Data Platform technologies together in a highly productive and easy to use manner with Microsoft Visual Studio.

Streamline collaboration

  • Write code that targets rich Internet applications, client applications, and server applications; and experience a powerful end-to-end development environment

  • Boost productivity through Microsoft IntelliSense and built-in support for the ADO.NET Entity Framework and LINQ

  • Streamline collaboration between architects, developers, and testers by using Visual Studio Team System Edition

Related Links

Deep Dive

Related Technologies

  • LINQ

    Write queries by using LINQ in the programming language of your choice  to build queries over data directly instead of learning the SQL dialect native to your data source.

  • ADO.NET Entity Framework

    Simplify development by using conceptual models to access and manage data in your database.

  • ADO.NET Data Services

    Expose the richness of your data via a simple URI syntax.

  • Microsoft Sync Framework

    Enable occasionally connected systems by synchronizing your data between any data store, using any protocol over any network.

  • Connectivity Solutions

    Microsoft provides many connectivity solutions including Native and Managed providers along with ODBC, JDBC, and PHP Drivers.

 

Additional Resources

Related Sites

 

Take the next step

Try it
Try SQL Server 2008

Buy SQL Server

Take a closer look

Technologies

Download the Essential Backpack
TechNet IT Pros Start Here
MSDN Developers Start Here