Transcript What’s New in SQL Server 2005?

WHAT’S NEW IN SQL SERVER
2008: T-SQL
Martin Bell
SQL Server MVP
Features we will look at:
• Date and Time Functionality
• Dependency Reporting
• Filestream Storage
• Merge Statement
• Row Constructors
• Sparse Columns and Column Sets
• Hierarchy ID Data Type
• User Defined Table Type
• Table Values Parameters
Date and Time Functionality
• Date
• Supports Years 0 – 9999 i.e. 01-01-0001 to
31-12-9999 (Gregorian Calendar)
• Native Datatype (not CLR)
• Size - 3 bytes
Date and Time Functionality
• Time
• Variable Precision - 0 to 7 decimal places for
seconds
• Precise to 100 nanoseconds
• Separate Date and Time saves space
• Ansi compatible
• Size - 3 bytes for precisions <= 2;
4 bytes for precisions of 3 and 4
5 bytes for precisions > 4
Date and Time Functionality
• DateTimeOffset
• Supports Years 0 – 9999 i.e. 01-01-0001 to 31-12-9999
(Gregorian Calendar)
• Precise to 100 nanoseconds
• Time Zone Offset (From UTCTime) Preserved
• Not Time Zone Aware - No Daylight Saving Time Support
• Size - 8 bytes for precisions <= 2;
9 bytes for precisions of 3 and 4
10 bytes for precisions > 4
Date and Time Functionality
• DateTime2
• Supports Years 0 – 9999 i.e. 01-01-0001 to 31-12-9999
(Gregorian Calendar)
• Precise to 100 nanoseconds
• Size - 6 bytes for precisions <= 3;
7 bytes for precisions of 4 and 5
7 bytes for precisions > 5
Date and Time Functionality
• Can still use existing date/time functions
• DATENAME (datepart, date)
• DATEPART (datepart,date)
• DATEDIFF (datepart, startdate, enddate)
• DATEADD (datepart, number, date)
• Datepart can also be microsecond, nanosecond,
TZoffset or ISO_WEEK
• MONTH
• DAY
• YEAR
• CONVERT extensions
Dependency Reporting
• New dependency views replace
sp_depends
• Kept in sync as changes occur
• sys.dm_sql_referenced_entities
• Lists all named entities that an object
references
• sys.dm_sql_referencing_entities
•Lists all named entities that use an
object
• Can see references at OBJECT, DATABASE
DDL TRIGGER, SERVER DDL TRIGGER level
Filestream Storage
• Currently limited to 2GB with varchar(max)
• Poor streaming performance
• High cost of data
Filestream:
• Size limited by NTFS volume
• Kept transactionally consistent
• Can be stored on compressed volumes
Filestream Storage
• Check out Paul Randal’s Blog for the performance
graphs
Filestream reads better than varbinary
Filestream inserts perform better for files over ≈2MB for Win32
Varbinary inserts perform than T-SQL Filestream
Filestream updates perform better for files over ≈1MB for Win32
Filestream updates perform better than varbinary for T-SQL
Filestream Storage
• SqlFileStream Managed API in VS 2008 SP1
Merge Statement
• Lets you INSERT, UPDATE, AND DELETE in a
single operation (i.e. single pass of the data)
• Specify commands for each case (Insert,
Update or Delete)
• Cannot be remote table of distributed
partitioned view
• NOLOCK, READUNCOMMITED disallowed (to
maintain index integrity)
• Special $ACTION column allows you to record
whether MERGE did an INSERT, UPDATE,
DELETE
Merge Statement
[ WITH <common_table_expression> [,...n] ]
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ [ AS ] table_alias ]
[ WITH ( <merge_hint> ) ]
USING <table_source>
ON <search_condition>
[ WHEN MATCHED [ AND <search_condition> ]
THEN <merge_matched> ]
[ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ]
THEN <merge_not_matched> ]
[ WHEN SOURCE NOT MATCHED [ AND <search_condition> ]
THEN <merge_ matched> ]
<output_clause>
[ OPTION ( <query_hint> [ ,...n ] ) ]
;
Row Constructors (Values Clause)
Before SQL 2008:
DECLARE @t TABLE (id int, name varchar(20));
INSERT INTO @t VALUES
SELECT 1, 'Fred‘
UNION ALL SELECT 2, 'Jim‘
UNION ALL SELECT 3, 'Sue'
SQL 2008:
DECLARE @t TABLE (id int, name varchar(20));
INSERT INTO @t (id, name)
VALUES (1, 'Fred'), (2, 'Jim'), (3, 'Sue');
Row Constructors (Values Clause)
SQL 2008:
DECLARE @t TABLE (id int, name varchar(20));
INSERT INTO @t (id, name )
SELECT num, fore
FROM (
VALUES (1, 'Fred'), (2, 'Jim'), (3, 'Sue')
) d (num, fore);
Sparse Column and Column Sets
Ways of modeling sparse data:
One row per attribute:
256 table limit in SQL Server JOIN
Multiple nullable columns one per attribute:
1024 column limit in SQL Server tables
Modeled as XML:
Common properties are elements, sparse are
attributes
Hierarchy Id Data Type
• Compact way to store hierarchies with SELECT
query support
• Can allow some hierarchy queries without
having to resort to recursive queries
• Slower on re-parenting operations (not just
moving pointers!)
• See BOL for discussions of the various tradeoffs vs. XML and parent/child tables
Hierarchy Id Data Type
• CLRUDT implementation
• Uses ORDPATH representation (materialized
path)
• "Level" property - allows breadth-first
indexing
Hierarchy Id Data Type
• Depth First indexing
• Breadth
First indexing
Hierarchy Id Data Type
• Methods for common hierarchical operations
• GetRoot
• GetLevel
• IsDescendant
• GetDescendant, GetAncestor
• Reparent
User Defined Table Type
CREATE TYPE mytab AS TABLE (id int);
DECLARE @t mytab;
Table Valued Parameters
How do I pass a table to a T-SQL stored procedure?
How do I efficiently pass multiple values from a
database client?
Erland Sommarskog’s Web Site
http://www.sommarskog.se/arrays-in-sql.html
Table Valued Parameters
• Parameters must use strongly typed table
variables
• Tables are passed by reference
• No Statistics support exposed on these, so
be careful in large query plans
References (SQL 2008)
•What’s New
http://www.microsoft.com/sqlserver/2008/en/us/overview.
aspx
• Learning Resources
http://www.microsoft.com/sqlserver/2008/en/us/learning.a
spx
• Technet Insiders Blog
http://blogs.technet.com/industry_insiders/
• Technet
http://technet.microsoft.com/en-us/library/cc721270.aspx
References (Filestream)
• Should I store my images in the database (SQL
2000)
http://databases.aspfaq.com/database/should-i-storeimages-in-the-database-or-the-filesystem.html
• To BLOB or not to BLOB
http://research.microsoft.com/research/pubs/view.aspx?m
sr_tr_id=MSR-TR-2006-45
References (Filestream)
• Paul Randal’s Blog
http://www.sqlskills.com/blogs/paul/2008/03/09/SQLServe
r2008FILESTREAMPerformance.aspx
• Guy Burstein's Blog
http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/
12/sqlfilestream-managed-api-for-sql-server-2008filestream.aspx
References (HierarchyId)
• Simon Sabin’s Blog
http://sqlblogcasts.com/blogs/simons/
• SQL Server Magazine
http://www.sqlmag.com/articles/index.cfm?articleid=99369
References (Merge)
• Connor Cunningham’s Blog
http://blogs.msdn.com/conor_cunningham_msft/archive/20
09/02/04/conor-vs-merge-and-primary-key-collisions.aspx
References (TVPs)


Erland Sommarskog’s Articles

http://www.sommarskog.se/arrays-in-sql.html

http://www.sommarskog.se/arrays-in-sql-perftest.html
Guy Burstein’s blog


http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/12/02/sql-server-2008-table-valuedparameters.aspx
Mike Taulty’s blog

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/12/03/9991.aspx