1. Inline variable assignment

    DECLARE @myVar int
    SET     @myVar = 5

    you can do it in one line:
     DECLARE @myVar int = 5

  2. C like math syntax. SET @i += 5

  3. Table Value Parameters (TVP)

    CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int)

    DECLARE @myPeeps PeepsType
    INSERT     @myPeeps SELECT 'murphy', 35
    INSERT     @myPeeps SELECT 'galen', 31
    INSERT     @myPeeps SELECT 'samuels', 27
    INSERT     @myPeeps SELECT 'colton', 42
    exec     sp_MySproc2 @myPeeps

    And the sproc would look like this:

    CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY)

  4. New Management Studio - Easily Write Query With Autosuggest Feature

  5. SQL Server 2008's new data types:

    • Date and Time: Four new date and time data types have been added, making working with time much easier than it ever has in the past. They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
    • Spatial: Two new spatial data types have been added--GEOMETRY and GEOGRAPHY--which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.
    • HIERARCHYID: The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures, such as the organization chart of a business.
    • FILESTREAM: FILESTREAM is not a data type as such, but is a variation of the VARBINARY(MAX) data type that allows unstructured data to be stored in the file system instead of inside the SQL Server database. Because this option requires a lot of involvement from both the DBA administration and development side, I will spend more time on this topic than the rest.
  6. Merge

    SQL 2008 includes the TSQL command MERGE. Using this statement allows a single statement to UPDATE, INSERT, or DELETE a row depending on its condition. The example below demonstrates the MEGRE being used to combine a new list of Inventory Item descriptions into the existing Inventory Master. In addition to new Descriptions, there are some new parts included in the NewInventory table. Without the Merge statement, two commands would need to run. The first would look for a match then upgrade the Description. The second statement would look for non matches and then INSERT. With MERGE, one statement can perform both tasks as shown below.

    MERGE InventoryMaster AS im
    USING     (SELECT InventoryID, Descr FROM NewInventory) AS src
    ON     im. InventoryID = src. InventoryID
    WHEN     MATCHED THEN
    UPDATE SET im.Descr = src.Descr
    WHEN     NOT MATCHED THEN
    INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr)
  7. 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)