What is new in SQL Server 2008

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)