New in SQL Server 2008

--Compound Operators

DECLARE @NoOfIterations int
SET @NoOfIterations = 0
- - Do something
SET @NoOfIterations += 1
-------------------------------
--DECLARE Statement

DECLARE @NoOfIterations int = 0
-------------------------------
--GROUPING SETS

use AdventureWorks2008
-- Using UNION ALL
SELECT CustomerID, NULL AS Year, SUM(SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
UNION ALL
SELECT NULL AS CustomerID, YEAR(OrderDate), SUM(SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
-- Using GROUPING SETS
SELECT CustomerID, YEAR(OrderDate), (SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY GROUPING SETS ((CustomerID), (YEAR(OrderDate)))
------------------------------------
--Row Constructors

INSERT INTO Person.CountryRegion
VALUES ('UK', 'United Kingdom', SYSDATETIME()),
('GB', 'Great Britain', SYSDATETIME())
-------------------------------------
--datetime2 new data type

• Key features: larger and more accurate than datetime. Contains a new
SYSUTCDATETIME function that returns the Coordinated Universal Time
(UTC) date and time, as opposed to the system date and time.
• Range: from 0001/01/01 00:00:00:0000000 to 9999/31/12 23:59:59:9999999.
• Accuracy: 100 nanoseconds.

DECLARE @newDateTime datetime2
SET @newDateTime = SYSDATETIME()
PRINT @newDateTime
-- OUTPUT
-- 2008-02-24 20:50:46.7892464
--------------------------------------
--date new data type

• Key features: consists of the date portion of datetime2.
• Range: from 0001/01/01 to 9999/31/12.
• Accuracy: one day.

The following code example uses the date data type.

DECLARE @newDate date
SET @newDate = SYSDATETIME()
PRINT @newDate
-- OUTPUT
-- 2008-02-24
---------------------------------------
time new data type

• Key features: consists of the time portion of datetime2.
• Range: from 00:00:00:0000000 to 23:59:59:9999999.
• Precision: variable precision, defined when you declare the variable.
• Accuracy: 100 nanoseconds.

The following code example uses the time data type.

DECLARE @newTime time(3)
SET @newTime = SYSDATETIME()
PRINT @newTime
-- OUTPUT
-- 20:50:46.789
---------------------------------------
datetimeoffset new data type

• Key features: similar to datetime2, but includes a time-zone offset. Time-zone
comparisons are performed against UTC and you use the SWITCHOFFSET
function to modify the time-zone offset and preserve the actual time.
The following code example uses the datetimeoffset data type.
DECLARE @newDateTime datetimeoffset
SET @newDateTime = SYSDATETIMEOFFSET()
PRINT @newDateTime
-- OUTPUT
-- 2008-01-24 20:50:46.7892464 -08:00
----------------------------------------
The User-Defined Table Data Type

CREATE TYPE ProductionHistory AS TABLE
(TransactionID int,
-- Remaining column definitions here
)
CREATE PROCEDURE usp_ArchiveHistory
@PH ProductionHistory ReadOnly
AS -– Remaining SP definition here
DECLARE @PH AS ProductionHistory
INSERT INTO @PH –- Populate table here
EXEC usp_ArchiveHistory @PH;
GO

-------------------------------------------
The hierarchyid Data Type

CREATE TABLE OrgTable
(
OrgNode hierarchyid, EmpID int, EmpName varchar(50)
)
INSERT INTO OrgTable
VALUES (
hierarchyid::GetRoot(), 0, 'Darren')
DECLARE @NodeLevel hierarchyid
SELECT @NodeLevel = OrgNode FROM OrgTable WHERE EmpName = 'Darren'
DECLARE @Manager hierarchyid
SELECT @Manager = max(OrgNode) FROM OrgTable
WHERE OrgNode.GetAncestor(1) = @NodeLevel
INSERT INTO OrgTable
VALUES (@NodeLevel.GetDescendant(@Manager, null), 1,
'Dominic')
SELECT OrgNode.ToString(), OrgNode, EmpID, EmpName
FROM OrgTable
----------------------------------------------
FILESTREAM Data

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

• 0 disables FILESTREAM support for this instance.
• 1 enables FILESTREAM for Transact-SQL access.
• 2 enables FILESTREAM for Transact-SQL and Win32 streaming access.
CREATE TABLE ResumeData
(
ApplicantID int,
Resume varbinary(max) FILESTREAM,
ResumeID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
)
INSERT INTO ResumeData
VALUES
(
1, CAST('Resume Text' AS varbinary(max)), newid()
)