New Features 

notes from: ISV Innovation: SQL Challenge

• Date/Time
  • DATETIME 1753-999 years
  • DATETIME 0.0333 Second Accuracy
  • New Date Types 001-999 Years (ANSI Compatible)
  • New Date/Time Types 100 nanosecond precisions
  • Variable Precision to save space
  • Separate Date and Time to save space
  • DATE type - stores only dates: 01-01-001 to 31-12-9999 Gregorian Calendar
  • TIME type - stores only time; variable presicion 0 to 7 decimal places to 100 nanoseconds.
  • DATETIME2 (combines features of DATE and TIME above)
  • DATETIMEOFFSET - TZ offest preserved, not TZ aware e.g. no daylight savings support
    • functions: SWITCHOFFSET, TODATETIMEOFFSET
  • SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFEST - higher precision functions
  • Localization Functions: SET DATEFIRST, SET DATEFORMAT, SET LANGUAGE, @@DATEFIRST, @@LANGUAGE
  • better CAST/CONVERT of Date/Time to and from VARCHAR strings
• Table-Valued Parameters
  • Use a table as a parameter (similar to having an array)
    • useful to reduce db roundtrips
  • Table variable declaration: DECLARE @t TABLE (id int)
  • strongly typed variable:
    • CREATE TYPE mytab AS TABLE (id int);
    • DECLARE @t mytab;
  • new row constructor: INSERT INTO @t VALUES (1), (3), (5);
  • parameters must use strongly typed table varibles, and READONLY
    • CREATE PROCEDURE usetable ( @t mytab READONLY ) AS INSERT INTO myitems SELECT * FROM @t;
  • performance: Table Value Parameters are very close to the speed of BCP, much faster than parameter list
• T-SQL Syntax Enhancements
  • single statement declare and init:
    • DECLARE @i int = 4
  • Increment Operators
    • SET @i += 1
  • Row Constructors:
    • DECLARE @t TABLE (id int, name varchar(20));
    • INSERT INTO @t VALUES (1, 'Fred'), (2, 'Jim');
  • Row Constructors as data sources:
    • SELECT * FROM (VALUES('John',25,5), ('Jane',36,6)) tab(name, age, score) -- notice data types not declared!
• Grouping Sets - use multiple GROUP BY in a single statement (ANSI standard)
  • nested subtotals; single read pass for performance
  • COMPUTE BY is deprecated
  • WITH ROLLUP procudes grouping sets with business value
  • WITH CUBE produces all possible grouping sets based on GROUP BY parameters
• MERGE statement
  • merge data from source into destination
  • options WHEN MATCHED; WHEN [TARGET] NOT MATCHED; WHEN SOURCE NOT MATCHED
  • ANSI SQL 2006 compliant - with extensions
  • transactional!
  • $action column can be referenced in OUTPUT clause to see what happened
  • MATCHED and SOURCE NOT MACTHED can have multple WHEN clauses
  • can use any table source
  • MERGE causes triggers to be fired once
  • rows affected includes total rows affected by all clauses
  • superior to UPDATE with JOIN because is deterministic and transactional
    • deterministic: with MERGE if more than one source row mathes the ON clause it is an error
• Dependencies
  • dependency views replace sp_depends
    • sys.dm_sql_referenced_entities
    • sys.dm_sql_referencing_entities
• Plan Guides
  • sp_create_plan_guide
  • sp_control_plan_guide
  • select * from sys.plan_guides

(see also MSDN Webcast: New T-SQL Programmability Features in SQL Server 2008; event id: 1032357753)

Part 2

Spatial Data (location-based)

• Spatial Data Types (from Open Geospatial Consortium, supporting 2D): Point, Linestring, Polygon, MultiPoint, MultiLinestring, MultiPolygon, GeomCollection, etc (Parent Type is Geometry)
• Geometry - flat earth
• Geography - round earth
  • Coordinate order is Longitude then Latitude
• X and Y or Lat and Long members; Z for elevation; M for measure
• spatial datatypes are SQLCLR UDTs; use '.' syntax for properties and methods; use '::' for static methods; case-sensitive!
• SRID - Spacial Reference Id - every instance has one!
  • specifies the spec: SRID 4326 = GPS; SRID 0 = default for GEOMETRY
    • full list: sys.spatial_reference_systems
  • methods must use types with the same SRID otherwise you will get NULL!
  • many methods start with 'ST' e.g. STArea, STUnion, STIntersects
• Spatial Indexes (uses Tessellation process)
  • first level of row elimination e.g. can produce false positives, never false negatives
  • You define:
    • top-level bounding box for GEOMETRY indexes
    • cells per object
    • grids: 4 level, 3 densities per level (low, med, high)

Filestream storage

• addresses large binary objects
• data stored on filesystem, managed by sql server
• requires
  • NTFS (note: files still deletable with appropriate permissions)
  • VARBINARY(MAX) with FILESTREAM attribute
  • table must have UNIQUEIDENTIFIER column
• enabled at the database engine level (configuration manager)
• enabled at the database level (sp_configure 'filestream access level'...)
• ADD FILEGROUP name CONTAINS FILESTREAM
• to create a folder:
  • ALTER DATABASE dbname ADD FILE { NAME = name, FILENAME = path } TO FILEGROUP fgname
• to insert:
  • INSERT INTO tablename VALUES(CAST(data as varbinary(max))
• file I/O (.net)
  • SELECT columnname.PathName() FROM tablename
  • SELECT GET_FILESTREAM_TRANSATION_CONTEXT() to get transaction
  • use pathname to read/write file with handle = OpenSqlFilestream(...) and FileStream( handle, ... ) in .Net
  • close filestream, close handle, then commit transaction
• from Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008
  • The advantages of using FILESTREAM are:
    • You store and retrieve your BLOBs together with your relational data in a single data store
    • The BLOBs are included in database backups and restores
    • Inserting, updating and deleting BLOBs and your relational data happens in the same database transaction
    • The 2 GB max size for a varbinary(max) column doesn't apply; you are only limited by the available space on the NTFS file system
    • The SQL Server buffer pool memory is not used to operate on BLOBs; in previous versions large BLOBs could consume a lot of this memory
    • All BLOB access can be performed from .NET code, allowing your middle tier code to easily work with these objects
    • The NTFS file system can save and retrieve large BLOBs faster than SQL Server
• from SQL Server 2008 – Manage unstructured data using FILESTREAM Feature
  • Before opting for the FILESTREAM attribute check the following:-
    • The average data size is larger than 1 MB
    • Fast read access is required
    • The Application is being developed using a middle tier for application logic
  • FILESTREAM Limitations
    • Objects can be stored only on local volumes
    • Size is limited to the volume size
    • Not supported in database snapshot
    • Database mirroring is not supported
    • Transferent Encryption is not supported
    • Can not be used in table valued parameters

XML improvements

• XQuery methods: exists, value, query, nodes, modify
• supports new date/time types, lax validation, 'let' in FLOWR
• Hierarchical Data
  • HierarchyID data type (SQLCLR UDT) (SqlHierarchyId in .Net)
    • uses ORDPATH
    • GetRoot, GetLevel, IsDescendant, GetDescendant, (51:00)

Part 3

SQL Service Broker

• introduced in SQL 2005
• one service broker per db
• transactional messages
  • arrive in order - even across transactions
  • no distributed xaction when messages and db used together
• Asynch Programming
  • offload work to another system
  • data push
  • 2008 can activate an external process
• Distributed Workflow
  • decouple steps in the workflow
• Types of Messages
  • XML
  • XML with Schema Collection (validates two schemas: message type schema and message schema)
  • Binary
• CREATE QUEUE ... (physical endpoint)
• CREATE SERVICE ... (logical endpoint)
• contracts (optional):
  • CREATE CONTRACT...
  • CREATE BROKER <name> FOR CONVERSATION ...
• BEGIN DIALOG CONVERSATION ...
• SEND ON CONVERSATION ... (cant INSERT, UPDATE, DELETE)
• RECEIVE ... (pulls message off the queue, locks a conversation)
  • single message into variable
  • all messages into a table variable (faster)
  • process one msg per transaction
  • process group of msgs per transaction (faster)
• SELECT ... (gets message from queue, but leaves the message in the queue)
• select * from sys.conversation_endpoints
• service can have 0-n service contracts
• conversation handles:
  • initiator - returned from BEGIN CONVERSATION
  • target - retrieved when message received (initiator must send first)
• Conversation Group
  • Locking on Initiator
  • RECEIVE into table variable reads/locks single conversation group
• Poison Message
  • RECEIVE transaction roll-back puts message back into queue
  • Loop of receive + rollback + repeat = poison message
  • Service broker deactivates queue after same message received 5 times (default).
  • catch and handle QUEUE DEACTIVATION message
• Routing
  • scale out by routing to other instances or hosts
  • default - round robin selection of service in the local instance based on service name
• Security
  • on by default
  • types
    • dialog security (end-to-end message encryption by certificates)
    • transport security (endpoint)
      • windows auth (uses sql server service account)
      • certificate auth
• SSBDiagnose Utility
  • can verify broker setup
  • command-line: ssbdiagnose
• Activation
  • create stored proc to receive messages when messages arrive in queue
  • ALTER QUEUE <name> WITH ACTIVATION ... PROCEDURE_NAME = ...
• An Introduction to SQL Service Broker
• Building Reliable, Asynchronous Database Applications Using Service Broker