Islander’s Analog life….

I predict that SQL server 2008 is going to be a huge hit. It has been designed for performance and scalability from the start.

Some of the new features:

• Table Valued Parameters - stored procedures will accept a table parameter.  ADO.NET has been modified so from C#, etc you can create a new variable called ‘SqlDBType.Structure‘ and pass this onto your stored procedures.  This is huge!  No more temporary tables, or cursors to emulate the same behavior.
  CREATE TYPE CustomersTableType AS TABLE (NAME VARCHAR(25), LASTNAME VARCHAR(25));
CREATE PROCEDURE UpdateCustomers(@CUSTOMERS CustomersTableType REAONLY)
AS
BEGIN
INSERT INTO CUSTOMERS (NAME, LASTNAME)
SELECT NAME, LASTNAME FROM @CUSTOMERS;
END;
• New date/time data types: date, time, and also same times but time zone aware
  DATE - holds date from 1/1/1 to 12/31/9999
TIME - holds timeaccurate up to 100 nanoseconds
DATETIMEOFFSET - time zone aware.  Stores value in UTC format
DATETIME2 - holds date & time with larger precision
• New geo-spatial (latitude/longitude) data types -SQL server understand natively coordinates so you can create location aware applications.  For instance, get me all the customers within a 10 miles radius
  
• Data Analysis Services - Cubes have been enhanced - New ISO-compliant subclauses in GROUP BY clause (grouping sets, cube and rollup)
• Multiple groupings in the same query
• Data Analysis Services - New Tools Enhanced (Attribute Relationship Designer, Dimension Wizard and Dimension Editor)
• new MERGE statement - Combines INSERT, UPDATE and DELETE operations based on conditional logic.  This is such a powerful statement due that it allows you to easily “merge” or synchronized two datasets.
  MERGE INTO CUSTOMERS C
USING TransactionTable T ON T.OrderID = C.OrderID
WHEN MATCHED THEN UPDATE
SET C.Quantity = T.Quantity
WHEN NOT MATCHED THEN INSERT (OrderID, Quantity)
VALUES (T.OrderID, T.Quantity)
• Big performance gain and enhancements on Grouping Sets
• New Table Types (user defined table types) - can define indexes and constraints
  CREATE TYPE CustomersTableType AS TABLE (NAME VARCHAR(25), LASTNAME VARCHAR(25));
• Common Table Expressions  (CTE)- I guess they do exist in SQL server 2005 but I was not aware of this.  You can define a pseudo table on the fly.
  USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;

• Large Use-Defined Types (UDTs) not limited to 8k like in SQL Server 2005; Analogous to VarBinary(MAX)
• No CURSORS!! I was very surprise to see this one.  Now, it appears that if you want performance you are better off using tables variables.
• Better store for semi-structured data
• Multiple inserts via single INSERT statement
  INSERT INTO CUSTOMERS (NAME, LASTNAME) VALUES ('JOHN',SMITH'),('RAFA','NADAL'),('BILL','GATES);
• Variable declaration / assignment - with the DECLARE statement you will be able to declare a variable and also assign a value to it.
  DECLARE @NAME VARCHAR(25) = N'BILL GATES';
DECLARE @I INT = 0, @J INT = 5;

I am quite impress with all these new features.  There are even more improvements but I don’t recall all of them.  I will blog my experiences when I install the beta version of SQL server 2008 and my experiences trying to use it with Delphi.

Related Articles:

ADO.NET Entity Framework 101