tsunami
log in
email
password
links
newest items
tag list
syntax reference
tag:tsunami
history
item name
tags
===Introduction - PDC 2008: "SQL Server 2008 New and Future T-SQL Programmability"[1] - PDC 2008: "SQL Server 2008 Beyond Relational"[2] - "SQL2008 Product Overview"[3] (.docx) - "SQL2008 white papers"[4] [1]http://channel9.msdn.com/pdc2008/BB25/ [2]http://channel9.msdn.com/pdc2008/BB16/ [3]http://download.microsoft.com/download/6/9/d/69d1fea7-5b42-437a-b3ba-a4ad13e34ef6/SQL2008_ProductOverview.docx [4]http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx ==SQL Server 2008 Versions "SQL2008 features vs. server version"[1] 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 [1]http://msdn.microsoft.com/en-us/library/cc645993.aspx ==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: - "Keeping sysdepends up to date in SQL Server 2008"[1] [1]http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx - "Build Your Own SQL Server 2008 Object Dependency Viewer"[1] [1]http://cmille19.wordpress.com/2009/03/14/build-your-own-sql-server-2008-object-dependency-viewer/ - "Redgate SQL Dependency Tracker"[1] [1]http://www.red-gate.com/products/sql-development/sql-dependency-tracker/ - "ApexSQL Clean"[1] [1]http://www.bestshareware.net/download/apexsql-clean.htm ==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"[1] to the "2008 docs"[2]. This makes sense when one considers that the 8000 byte limit for UDTs was also removed. [1]http://msdn.microsoft.com/en-us/library/ms131051(SQL.90).aspx [2]http://msdn.microsoft.com/en-us/library/ms131051.aspx ==More supported assemblies for SQL CLR Supported namespaces/dlls "in SQL 2005"[1]: - 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"[2]: - System.Core.dll - System.Xml.Linq.dll [1]http://technet.microsoft.com/en-us/library/ms403279(SQL.90).aspx [2]http://technet.microsoft.com/en-us/library/ms403279(SQL.100).aspx
some permissive license goes here
contact