tsunami

log in
history

SQL2008 Features

Luke Breuer
2010-12-30 02:51 UTC

Introduction

SQL Server 2008 Versions
SQL2008 features vs. server version

Of particular interest, these are Enterprise-only:
  • compression of data files and backup files
  • resource governor
  • transparent database encryption
  • auditing of data read
  • partitioning
  • database snapshots
  • online and parallel indexing
compound assignment operators
  • +=
  • -=
  • *=
  • /=
variable initialization during declaration
declare @v int         = 5
declare @c varchar(20) = 'much better'
merge
merge Stock_Holding h
using Stock_Trade t
on t.stock = h.stock
    when matched and (t.quantity + s.quantity = 0) then
        delete
    when matched then
        update set t.last_trade_date = s.trade_date, t.quantity += s.quantity
    when not matched then
        insert values (s.stock, s.trade_date, s.quantity)
    output $action, inserted.stock, deleted.stock
multi-row insert
This converts to select ... union all ... under the hood.
insert  Customers (custid, companyname) values 
(1, 'cust 1'),
(2, 'cust 2');
table expressions
select  *
from    (values
    (1, 'cust 1'),
    (2, 'cust 2')
) as C(custid, companyname);
grouping sets
pre-SQL2008
select  customer_type,
        territory_id = null,
        modified = max(modified_date)
from    Customer
group by customer_type
union all
select  customer_type = null,
        territory_id,
        modified = max(modified_date)
from    Customer
group by territory_id
SQL2008
select  customer_type,
        territory_id,
        modified = max(modified_date)
from    Customer
group by grouping sets ((customer_type), (territory_id))
table types
create type my_table as table(
    a int,
    b varchar(100)
)
Can define indexes and constraints.
tables as parameters to sprocs
Table-Valued-Parameters (TVPs) can be sproc parameters — one uses table types as discussed above. Currently, TVPs are readonly, although MS is considering making them writable.
object dependency dynamic management views
  • sys.sql_expression_dependencies
    • new catalog view; replaces sys.sql_dependencies
    • tracks both schema-bound and non-schema-bound dependencies
    • tracks cross-database and cross-server references by name
  • sys.dm_sql_referenced_entities
    • replaces sp_depends
    • returns a row for each entity referenced by a given entity
    • ex: show all object referenced by a given UDF
  • sys.dm_sql_referencing_entities
    • replaces sp_depends
    • returns a row for each entity that references a given entity
    • ex: show all objects that would break if a given table is dropped

Notes:
  • TSQL only; now SQLCLR objects.
  • sys.dm_sql_referencing_entities does not work cross-database

Links:
spacial data types
Look elsewhere.
new date and time datatypes
datetime has precision 3. If a precision is not specified for the below, 7 is assumed.
  • date
    • 3 bytes
  • datetime2(0-7)
    • goes up to 100 nanosecond granularity
    • < 3 — 6 bytes
    • 3 - 4 — 7 bytes
    • > 5 — 8 bytes
  • datetimeoffset
    • combines datetime2 with time zone information
    • requires an additional 3 bytes
    • offset can be from +/- 00:00 to +/- 23:59
  • time(0-7)
    • < 3 — 3 bytes
    • 3 - 4 — 4 bytes
    • > 5 — 5 bytes

beyond relational

  • HierarchyID
    • store arbitrary hierarchies of data and efficiently query them
  • large UDTs
    • no more 8K limit on UDTs
  • sparse columns
    • optimized storage for sparsely populated columns
  • wide tables
    • support for hundreds of thousands of sparse columns
  • filtered indexes
    • define indices over subsets of data in tables
  • documents & multimedia
    • remote BLOBStore API
    • filestream
    • integrated Full Text Search
  • spatial
    • geometry and geography data types, functions, and indexes
    • full set of Open Geospatial Consortium components (OGC/SQL MM, ISO 19125)
    • Spatial Builder Interface
    • SSMS visualization
    • integration with Virtual Earth
  • XML
    • storing and validating Office 12 document formats
    • lax validation
      • unknown elements are [sometimes?] OK
    • full xs:dateTime support
      • values without timezones
      • timezone preservation
    • let-clause in XQuery
    • fn:upper-case()/fn:lower-case()
    • add support insert sql:variable("@xml") into /a/b
  • relational BR support
    • large UDTs
    • sparse columns
    • wide tables
    • filtered indices
    • HierarchyID
filestream
  • stored as varbinary(max) with a filestream attribute
  • requires NTFS
  • can retrieve via
    • TSQL (same as standard varbinary(max)
    • Win32 streaming APIs with TSQL transactional semantics
      • must include a transaction context, so a special SqlFileStream object is required
  • backed up and restored with standard SQL Server methods
  • security is SQL Server-based, not NTFS-based

FileStream has to be enabled via the SQL Server configuration (look for a filestream tab), where you choose a Windows share name. The following also needs to be run:
exec sp_reconfigure filestream_access_level, 2
reconfigure

The database needs to have a filegroup that has contains filestream in the definition.
full text search (FTS)
  • For SQL2005, indexes were stored outside the database and made it hard to mix relational predicates and full text predicates in an efficient manner. In 2008, FTS is integrated and this problem is alleviated, as the query optimizer has knowledge about the FT index.
  • FT index content and word breaking behavior is exposed
  • There is now an FDHOST process under control of SQL Server, because IFilters need to run and they aren't all written by MS. The two processes communicate via shared memory.
semi-structured data
  • property bags scenarios
    • distinct customized property sets associated with data
    • large number of unique properties, user annotations
    • examples
      • document management systems
      • media stores
  • databases with heterogeneous record types in a table
    • type specific properties, inherited properties in a type hierarchy
    • examples
      • product catalogs
      • location/business specific properties
characteristics
  • large number of sparsely populated properties
  • distinct property sets
  • heterogeneous structures
  • sets, nested structures
requirements
  • efficient storage for sparse properties
  • eficient relational access to metadata properties: query, DML, indexing
  • ability to get/set metadata property sets generically
  • index subsets of relevant properties for a property set
  • retrieve and analyze hierarchical data
SQL 2008 features
  • sparse columns: optimized storage for sparse columns
    • do not use space for null values
  • column sets/wide tables: support thousands (29K sparse + 1K non-sparse) of sparse columns
  • filtered indexes: index subset of rows in a table
  • HierarchyID: System CLR type for hierarchical organization of data
  • XML: for fast dynamic evolution (open schema), lists and tree objects
hierarchical data
  • forum and mailing lists threads
  • business organization charts
  • content management categories
  • product categories
  • file/folder management

In SQL2005, one would use a parent/child column or XML datatype. SQL2008 introduces the HierarchyID datatype.
  • implemented as varbinary
  • root: hierarchyid::GetRoot()
  • descendant: @p.GetDescendent(@before, @after) (all variables are hierarchyid)
    • if @before and @after are both null, we get first descendant
    • if @before is not null, we get something immediately before
    • if @after is not null, we get something immediately after
    • if @before and @after are both non-null, we get something exactly in between

Here we pull all descendants of an item, which is implemented as a scan and not recursive query. d stands for descendant and p for parent
select  d.id.ToString(), d.level, d.name
from    FileSystem d, FileSystem p
where   d.id.IsDescendantOf(p.id) = 1 and p.name = 'images'
order by d.id

HierarchyID
  • CLR UDT
  • Microsoft.SqlServer.Types
  • Varbinary encoding (< 900 bytes) (can be used as a primary key)

Here is a tree with the HierarchyID converted to a string via .ToString()
                               /
                /1/           /2/            /3/
         /1/1/      /1/2/             /3/1/      /3/2/
/1/1/1/
/1/1/2/

Currently, deleting /3/ will not delete /3/1/ or /3/2/.
sparse columns
  • 29,000 sparse columns allowed
  • requires defining a "sparse column set"
    • an un-typed XML column with published format
    • logical grouping for all sparse columns in a table
    • select * returns all non-sparse-columns, sparse column set (XML)
    • allows generic retrieval/update of all sparse columns as a set
  • ~20% performance hit on non-null values
  • additional 2-4 bytes for non-null values
  • sparse columns are beneficial when space savigs > 40%
filtered indexes and statistics
  • index a portion of the data in a table
  • filtered/co-related statistic creation and usage
  • query/dml optimization uses filtered indexes and statistics
  • restrictions
    • simple grammar for the predicate: column comparison value [and ...]
  • benefits
    • lower storage/mainteance costs
    • query/dml performance benefits: I/O only for qualifying rows
user defined aggregates can store > 8000 bytes
Compare the 2005 docs to the 2008 docs. This makes sense when one considers that the 8000 byte limit for UDTs was also removed.
More supported assemblies for SQL CLR
Supported namespaces/dlls in SQL 2005:
  • CustomMarshalers
  • Microsoft.VisualBasic
  • Microsoft.VisualC
  • mscorlib
  • System
  • System.Configuration
  • System.Data
  • System.Data.OracleClient
  • System.Data.SqlXml
  • System.Deployment
  • System.Security
  • System.Transactions
  • System.Web.Services
  • System.Xml

Additional supported namespaces/dlls in SQL 2008:
  • System.Core.dll
  • System.Xml.Linq.dll