Introduction

1. SQL Server 2005What’s new in the Relational & Storage EnginesTony BainSQL Server Practice ManagerRed Rock Consulting

2. What I Cover Database Admin Indexes Meta Data Query Processor Partitioning Exception Handling What I Don’t Cover Service Broker Event Notification .NET Integration Queues XQuery Reporting Services Data Transformation Services Analysis Services Introduction

3. Agenda This is a “What’s New” presentation • What’s new in TSQL • What’s new in Meta Data • What’s new in the Storage Engine • Misc • Was also going to do security but don’t have time • Come and see this at Tech Ed

4. TSQL Commands

5. Common Table Expressions • ANSI SQL 99 Feature • Code simplification • Two distinct types • “Run time” views • Recursion

6. Run Time Views WITH cte_name AS ( QUERY ) SELECT * FROM cte_name

7. Recursion CTE’s WITH recusvice_cte(parentkey(parentkey, key, value) AS ( SELECT parentkey, key, value FROM mytable UNION ALL SELECT m.parentkey, m.key, m.value FROM mytable m INNER JOIN recusvice_cte cte on m.key=cte.parentkey ) SELECT * FROM recusvice_cte

8. TOP • Top now supports a parameter value • About time too! declare @rows int select @rows=rand()*10 select top(@rows) * from dbo.Employee

9. APPLY • Similar to JOIN but feeds the left hand rows into the right • Two Types • CROSS APPLY – No rows are returned for the evaluation where the right hand side table source does not produce any rows. • OUTER APPLY – A row is produced for the evaluation even when the right hand side table source does not produce any rows.

10. ON X=Y ROW AB ROW B ROW AC ROW F ROW B ROW AG ROW AH ROW F JOIN FROM TABLEA INNER JOIN TABLEB ON X=Y ROW AA ROW A ROW AB ROW B ROW AC ROW C ROW AD ROW D ROW AE ROW E ROW AF ROW F ROW AG ROW G ROW AH ROW H

11. ROW AA ROW AB ROW AC ROW AD ROW AE ROW AF ROW AG ROW AH ROW AA ROW AA ROW C ROW A ROW AB ROW B APPLY FROM TABLEA CROSS APPLY dbo.fn_tab1(TABLEA.COLA) Table Valued Function ROW A ROW B ROW C ROW D ROW E ROW F ROW G ROW H

12. TABLESAMPLE • A random selection of rows • Returns approximately the number of rows you specify • Specify as % or # SELECT * FROM tablex TABLESAMPLE SYSTEM (100 ROWS) • REPEATABLE(seed) allows you to get same selection of rows

13. PIVOT / UNPIVOT • Rotate rows into columns • Useful if you have a “soft” schema • For example, ProductAtrribute table • Current Limitation • Only the list of columns in the result is static and cannot be the result of a query.

14. OUTPUT • Allows result sets to be returned from INSERT, UPDATE & DELETE commands • Have INSERTED & DELETED tables similar to triggers in SQL Server 2000. • Returns a standard result set that you can access from your application • Of course you will have to use a result set handling method instead of .ExecuteNonQuery • Could be used for selective audit

15. OUTPUT Example update dbo.Customers Set City=UPPER(City) OUTPUT 'Changed ' + DELETED.City + ' to ' + INSERTED.City Changed Berlin to BERLIN Changed México D.F. to MÉXICO D.F. Changed México D.F. to MÉXICO D.F. Changed London to LONDON Changed Luleå to LULEÅ Changed Mannheim to MANNHEIM Changed Strasbourg to STRASBOURG Changed Madrid to MADRID Changed Marseille to MARSEILLE Changed Tsawassen to TSAWASSEN

16. Try / Catch • Not as first appears - Not TRY / CATCH as per .NET • Code simplification that only “handles” a transaction abort exception • Creates a wrapper that directs control to error handling function on transaction abort error • Set XACT_ABORT on, which means any runtime error will cause transaction abort • Cannot actually handle the error. The transaction is dead, all you can is pass the error up the application stack.

17. RAISERROR WITH TRAN_ABORT • RAISERROR has been extended to have a TRAN_ABORT parameter • Allows you to manually invoke you tran abort method • Kills the transaction though, again no handling possible

18. EXECUTE AT • EXECUTE has been extended to include the AT parameter. • Allows commands to be passed through to a linked server • E.g. EXEC('SELECT * FROM Northwind.dbo.Products') AT OurLinkedServer

19. META DATA

20. fn_virtualindexstats() • Great new feature  • Helps reduce the problem of the “Every increasing number of indexes” • Shows allocation stats & also usage stats • KPI’s • # range scans • # singleton_lookups

21. fn_transactions() • Returns information on the transactions that are currently in progress • Replaces DBCC OPENTRAN() of SQL 2000 • Returns • SPID • Transaction Name (if any) • Elapsed time • If transaction is using Snapshot isolation level

22. DDL Triggers • Triggers can now be created on events other the UPDATE, INSERT & DELETE • Includes DDL statements such as CREATE TABLE • Can be useful in auditing database changes • The eventdata() function contains an XML representation of the event data.

23. eventdata() <EVENT_INSTANCE> <PostTime>2004-01-12T16:42:50.237</PostTime> <SPID>52</SPID> <EventType>CREATE_TABLE</EventType> <Database>AdventureWorks</Database> <Schema>dbo</Schema> <Object>MyTable</Object> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>CREATE TABLE MyTable(IDCOLINT)</CommandText> </TSQLCommand> </EVENT_INSTANCE>

24. DDL Triggers (cont)

25. DDL Triggers (cont)

26. Storage Engine

27. Persisted Computed Columns • Traditionally a Computed Column is calculated at run time • In SQL Server 2005 we have the option to persist the values in our computed columns • Persisted Computed Columns can be indexed

28. Non Key Index Column Inclusion • In SQL Server 2000, to make an index covering all columns had to form the index key • INCLUDE allows additional columns to be included in the index leaf level, but not included in the index keys • Uniqueness and sorting only maintained for the key columns

29. Full Text Indexes • Now brought inside SQL Server • FT Catalogs now backed up as part of the database backup • You can also just backup the catalog • BACKUP DATABASE dbname FILE=‘fulltext_cat’ TO device • Now has a Thesaurus

30. Disabling Indexes • Indexes can be disabled • Stops them being used • Stops them being updated • Can be used during large data loads instead of drop / recreate • Debug / index clean up etc

31. Database Views • Database Views allow the state of a database at a given point in time to be preserved and made available in a read only form • I.e. a logical representation of the database at the time of view creation CREATE DATABASE …. AS VIEW OF dbname

32. Database Views (cont) • Efficient as they only store the inverse of changes made to the database being viewed • Utilize a type of data file called a spares file (NTFS 5 feature). • Spares files provide a logical representation of a file separate to the way the file is physically stored on disk • Logically – Fully allocated • Physically – Only actual data

33. Database Views (cont) • Spares Files Free Free Free Free Data Data Free Free Logical File Data Data Physical File

34. Database View (MDF) Free Free Free Free Free Free Free Free Database Views (cont) CREATE DATABASE DB1VIEW …. AS SNAPSHOT OF DB1 Database (MDF) Data Data Data Data Data Data Data Data

35. Database Views (cont) SELECT * FROM Orders WHERE ORDERID=100 Database View (MDF) Free Free Free Free Free Free Free Free Database (MDF) Data Data Data Data Data Data Data Data

36. Database Views (cont) SELECT * FROM Orders Database View (MDF) Free Data Free Free Free Free Data Free Free Free Data Database (MDF) Data Data Data Data Data Data Data Data UPDATE Orders SET Price=Price*1.5 WHERE OrderData BETWEEN 20040101 AND 20040131

37. Database Views (cont) Database Database View Database View Database View

38. Database Views (cont) • There is major current limitation….. • They cannot be backed up • Makes sense, yes • But still will seriously limit any practical application of such views. • Hope we will see a “synchronized backup” strategy in place soon.

39. Mirrored Backups • Allow you to create a duplicate backup set • Not a stripe • Useful if backing up directly to tape • Is anyone doing this by the way? • Improve redundancy of the backup

40. Mirrored Backups (cont) • Assume we backup database XYZ to: • Mirror A, which consists of backup devices A1, A2 and A3 • Mirror B, which consists of backup devicesB1, C2 and C3 • Mirror C, which consists of backup devices C1, C2 and C3 • If backup devices A1, B2 and C3 where to become damaged we would still be able to recover our database by restoring from the combination of B1, A2 and C3.

41. Large Object Data • TEXT, NTEXT and IMAGE are gone • About Time Too!! • Welcome, • VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) • While they may be internally stored different, externally we don’t care. They behave the similar to non-blob types (ala Access).

42. Large Object Data (cont) CREATE TABLE #TABLE_ABC ( IDCOL INT PRIMARY KEY, TextBlob TEXT ) INSERT #TABLE_ABC VALUES(1,‘Short Text Data') INSERT #TABLE_ABC VALUES(2,‘Some more Text Data') --The following causes an error SELECT * FROM #TABLE_ABC WHERE TextBlob='Short Text Data ' Server: Msg 306, Level 16, State 1, Line 13 The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

43. Data Partitioning • In SQL Server 2005 strategy has changed • Moved away from impacting the physical data model • Sits between data model and physical page allocations • Becomes data storage issue • AS IT SHOULD BE! • New concepts • Partition Functions – Routine that chooses “bucket” • Partitioning Scheme – Bucket to Filegroup mapping

44. Data Partitioning (cont)

45. Data Partitioning • Splitting large volumes of data into more manageable sub sets • SQL Server 2000 • Create multiple tables • Assign PK constraints to each table • Create view over tables • Query / Modify view • Difficult to change • Difficult to manage (indexes on each table)

46. Instant File Initialization • SQL Server 2005 creates databases without zeroing out all the pages allocated.

47. Row Overflows • Rows can now be over the 8Kb limit • Comes into play when variable length columns are over 8kb (excluding blob) • SQL Server will move the largest variable length column into it’s own page and link that child page to the original row.

48. 1 DEF ABC Row Overflows (cont) INSERT TABLE_ABC VALUES(1,’ABC’,’DEF’) INSERT TABLE_ABC VALUES(1,REPLICATE(’A’,8000),REPLICATE(’B’,8000)) TABLE_ABC KEYCOL INT COL1 VARCHAR(8000) COL2 VARCHAR(8000) Page Page 1 BBBBBBBBBBBB… AAAAAAAAAAA… Column Link Page BBBBBBBBBBBB… Page

49. Deferred Drop • When an object over 128 extents (1024KB) is dropped SQL Server 2005 does not immediately reclaim the space allocated by that object • A background process outside the scope of the drop transaction handles the physical deallocation • This improves the performance of maintenance operations such as index rebuilds on large tables • Because deferred drops do not deallocate pages at the time of dropping, the space consumed by the object may not be immediately available for resue.

50. Statistics On Views • By reading BOL you won’t get a feeling for how cool this feature is. • If you’re keen read “Statistics on Views” • Galindo-Legaria, Joshi, Wass, Wu • Will link from www.tonybain.com