SQL Server 2005
Differences from SQL Server 2000
The Big Differences
Performance Tuning/Monitoring/Troubleshooting - Index Tuning Advisor replaced by Database Engine Tuning Advisor - more tools for troubleshooting (one report was reducing something that took hours to solve in 2000 down to minutes in 2005)
XML Native Support Added - greatly expanded XML capabilities
HTTP Endpoints - allows stored procedures and functions to be called from apps using SOAP protocol
Partitions - expanded from what could be done in 2000
Full-Text Catalogs - Windows text searching copied within 2005 to allow indexes on masses of text and documents
| Additional Changes | |||||
|---|---|---|---|---|---|
| 1. | Database Mail | able to send/receive mail without using an external program like Outlook | |||
| 2. | Schemas | tables organized into groups within table group | |||
| 3. | Encryption | can encrypt data down to column level | |||
| 4. | Create User statement | ||||
| 5. | Create Login statement | ||||
| 6. | varchar(max), nvarchar(max), varbinary(max) | these have been expanded with the aim of dropping text, ntext & image data types in future versions | |||
| 7. | Computed Column | Persisted keyword added | |||
| 8. | Table Variables | Declare @Customer table (....) | |||
| 9. | Create Type statement | user-defined data type | |||
| 10. | CLR UDTs | used with date and time data and other small and discrete data | |||
| 11. | Create Index...online option | index available during indexing operation | |||
| 12. | Alter Index...rebuild option | ||||
| 13. | index included columns | ||||
| 14. | Pivot/Unpivot operators in Select statement | ||||
| 15. | Full-text indexes | new functions like: Contains and FreeText | |||
| 16. | TableSample clause in Select statement | ||||
| 17. | Try and Catch error handling | ||||
| 18. | Partitioning | speeds up process of handling huge #'s of rows -> millions | |||
| 19. | Create Partition Function statement | ||||
| 20. | Create Partition Scheme statement | ||||
| 21. | $Partition function | ||||
| 22. | Managing Partitions: split, merge, switch | ||||
| 23. | Create Indexed View in any version of 2005 | ||||
| 24. | XML support extended alot | ||||
| 25. | XML data type | ||||
| 26. | XQUERY & XPATH query languages | search XML data -> XQUERY is a complete programming language | |||
| 27. | store xml-formatted data in text or XML data type column | ||||
| 28. | composition & shredding techniques applied to XML | ||||
| 29. | Create XML Schema Collection statement | ||||
| 30. | For XML clause in Select statement has Path option added to others: Raw, Auto, Explicit | ||||
| 31. | ROOT instruction | ||||
| 32. | Create Function has "with execute as caller" option added | ||||
| 33. | DDL Triggers | ||||
| 34. | Bulk Insert statement's security tightened up | ||||
| 35. | Importing Bulk XML data as a Single_Blob | ||||
| 36. | DTS replaced by SSIS (SQL Server Integration Services) | ||||
| 37. | more flexible ways to do backups | Restore option can skip damaged media sectors using best-effort restore | |||
| 38. | validating backups more meaningful now | goes beyond simply checking media headers | |||
| 39. | Index Fragmentation levels | ||||
| 40. | Viewing column statistics in different ways has been added. | ||||
| 41. | Create Endpoint statement | this has to do with SOAP and HTTP Endpoints | |||
| 42. | Job schedules are separate entities so can be used in multiple jobs. | I suspect this may not be all that useful because adjusting job schedules as time and needs change is a fact of administration. | |||
| 43. | Maintenance plans are now SSIS packages. | ||||
| 44. | Database Engine Tuning Advisor | replaces Index Tuning Wizard, part of performance monitoring | |||
| 45. | Dynamic Management Views (DMVs) | part of performance monitoring | |||
| 46. | Dynamic Management Functions (DMFs) | part of performance monitoring | |||
| 47. | Dedicated Administrator Connection (DAC) | allows administrator to connect at all times -> last resort connection method if all other means fails | |||
| 48. | can include ShowPlan info in Profile traces | called ShowPlan XML event | |||
| 49. | Database Snapshot | create read-only copies of database - very fast - can be used as part of a recovery operation | |||
| 50. | Database Mirroring | ||||
| 51. | Log Shipping | ||||
| 52. | Peer-to-Peer Replications | ||||
| 53. | db_owner role can set up replication now | ||||
| 54. | Publication Access List (PAL) | more control over access to publications | |||
| 55. | Service Broker | message-queuing system for distributed apps | |||
| 56. | Create Message Type statement | Service Broker | |||
| 57. | Hidden Tables | Service Broker | |||
| 58. | Create Queue statement | Service Broker | |||
| 59. | Create Service statement | Service Broker | |||
| 60. | Begin Dialog Conversation command | Service Broker | |||
| 61. | Create Route statement | Service Broker | |||
| 62. | Full-Text Catalogs | ||||
| 63. | Create FullText Catalog statement | Full-Text Catalog | |||
| 64. | Create FullText Index statement | Full-Text Catalog | |||
| 65. | FreeText function | Full-Text Catalog, does a fuzzy search for word forms | |||
|
|
|||||