- Inline variable assignment
DECLARE @myVar int
SET @myVar = 5
you can do it
in one line:
DECLARE @myVar
int = 5
-
C like math syntax. SET @i += 5
-
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)
-
New Management Studio - Easily Write Query
With Autosuggest Feature
-
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.
- 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)
- 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)