TechEd '04: DAT326 SQL Server 2005 (code named "Yukon"): Be More Efficient with T-SQL
Gert Drapers is doing this session. Should be fun
- Adding tons and features and making continued investments in this
technology.
- Standard list of new features
- Ranking functions
- Talked about Rank, Dense_Rank and RowCount
- Ranking helpful in data analysis
- Uses SQL Standard OVER syntax
- Talked about PIVOT and UNPIVOT
- Talked about the changes to TOP
- Common Table Expressions
- Sort of like an enumerator
- with <name>(cols...) as name (sub-query)
- Example: select empid,empname,mgrid from employees where id='12345'
union all select e.empid, e.empname, e,mgrid from employees e where ...
doh, missed the rest.
- Use union all to merge
- Recursion stops when subquery returns nothing.
- Filtering should be done on outer query
- Expect some performance gains from doing this since QP will have a
better clue.
- Indexing changes
- ALTER INDEX syntax
- Talked about ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, MAXDOP and ONLINE.
- New functions fn_indexinfo and fn_virtualindexstats
- Indexed View Changes
- Compilation time improved
- Scalar aggregates and Scalar UDFs
- Improved the query-to-undexed-view matching
- Computed columns are intelligently used
- Improved intelligence of ranges
- A bit of conditional equivalence usage
- XML showplan was done for efficiency reasons
- Discussion of table variables now supporting INSERT...EXECUTE
- Snapshot isolation (think Oracle)
- Increased access (readers don't block writers, writers don't
readers)
- Mandatory consistency checker
- At the database level
- Trade of is CPU and I/O for a consistent view
- Did a demo of setting this up.
- Clash resolution is at the cell, not row, level.
- Added Data Types: XML, n/varchar(max) and varbinary(max)
- Confirmed Date, Time and UTCDateTime have removed
- Talked about XML Data Type
- Talked about the new MAX types with a 2gb limit. No need to text
pointers, Can now be used like any other data-type (essentially.) Chunking
is still supported.
- Talked about updating BULKINSERT with OPENROWSET
- DRI changes
- Adding SET DEFAULT and SET NULL via DML
- Talked DML without output
- Access to inserted and deleted
- update orders set status='processed' output deleted.*,inserted.*
where status='uncprocessed'
- Yields better concurrency
- Talked about DDL triggers
- Scoped at the server and database level
- Data in eventdata() function
- Useful for rule inforcement
- Protect from drops
- Check-in, check-out
- Source versioning
- Log management activity
- Talked about statement recompilation
- Discussion of TRY...CATCH
- B1 was essentially limited to a transaction abort
- B2 will catch @@error, simplies the Try Catch Syntax and cleaner
error raising
- Can be nested
- Details in EventData, error_number(), etc
- BEGIN TRY/END TRY, BEGIN CATCH/END CATCH
Fairly good session, I was pleased that Gert wasn't afraid to talk about the
dropped features and I felt like he gave us the straight dop about the product.