Monday, September 1, 2008

Tech Ed 2008 Day One - DAT 302 Overview of SQL Server 2008 - new features

  • (Speaker: Jeremy Boyd)
  • Auditing to file
  • windows application log
  • windows security log
  • Native Encryption
  • Enhanced Mirroring - Auto Page repair and log stream compression

sp_estimate_data_compression_savings ROW/PAGE

Filtered Index
Create Nonclusted index blah on Production.xyz (blah, blah) WHERE PID >= 27 AND PID <= 36;

Resource Governor
(Handles Contention) E.g Limit resources used for reporting. Resource Pools, Workload Groups, Classifier Functions (Which workload group to assign a connection to)

New Data Types
Date, Time, DataTime2 (System clock precision), DatetimeOffset (For use with timezones) select * from sys.systypes where name like '%date%' or name like '%time%' SELECT SYSDATETIME() AS SYSDATETIME, SYSDATETIMEOFFSET() as SYSDATETIMEOFFSET, SYSUTCDATETIME() as SYSUTCDATETIME SELECT SWITCHOFFSET ( '2005-01-20 23:00:00.00000 +4:00', datepard(TZoffset, sysdatatimeoffset()) TODATETIMEOFFSET - Adds timezone information to a DateTimeOffset.

HierarchyID - supports depth-first and breadth-first indexes. CLR based. Parent.GetDescendant(Child1, Child2) Node.IsDescendantOf() Node.GetLevel GetRoot Node.GetReparentValue(oldParent, newParent)

Grouping Sets - multiple group by clauses

Multirow insert CREATE TABLE tbl_RowCon (PDT VARCHAR(10) PRIMARY KEY, val int) INSERT tbl_RowCon VALUES ('PDT A', 10), ('PDT B', 5), ('PDT C', 10)

+= assignment

Merge Statement insert (DML statement combining multiple operations into one) WHEN MATCHED THEN (so UPDATE) WHEN NOT MATCHED THEN (so INSERT) SOURCE NOT MATCHED (so DELETE)

conditional insert

Table parameters - Readonly when used as a parameter. - using multiple parameters requires multiple round trips for multirow data. - temporary tables use disk resources and could be prone to locking. Leads to stored procedure recompilation. + offers more flexibility. Well defined scope - won't require locks. Can reduce round trips. CREATE TYPE EmployeeTableType AS TABLE (EmpID INT, EmpName nvarchar(100))

Spares columns - Generally the column wont have any data c XML COLUMN_SET FOR ALL_SPARSE_COLUMNS Values stored in XML will be expanded out to columns.

Large user defined types supported (>8000)

Spatial Data - Vector based. Geometry (Points, line strings, polygons) - Geography (geodetic)

Filestream Storage Attribute on varbinary(MAX) Use when objects are larger than 1MB and provides faster read access. Small objects are often better stored in the database. Exists as a folder on disk as specified - $FSLOG & filestream.hdr (header) .PathName() - filepath of the varbinary(max) column.