What's new in SQL Server 2005 for the T-SQL programmer

I have been taking a course on SQL Server 2005 Transact-SQL and having already worked on SQL Server 7 & 2000, I was keen on knowing what new Transact-SQL features SQL Server 2005 offers. With help from other online resources as well, I have summarized the key enhancements for quick reference -
  1. TOP — Row count based on an expression. TOP can also be used with INSERT, UPDATE, and DELETE.
  2. OUTPUT clause with DML Commands - to determine the changes from an INSERT, UPDATE, or DELETE
  3. PIVOT/UNPIVOT - to create crosstab queries
  4. TRY-CATCH - for improved error handling
  5. Ranking Functions [ ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() ] - ranking adds an extra value column to the resultset that is based on a ranking algorithm being applied to a column of the result.
  6. DDL Triggers - fire when CREATE, ALTER, or DROP statements are executed, useful for auditing
  7. Common Table Expression (CTE) - to create recursive query expressions
  8. TABLESAMPLE clause - lets you efficiently sample pages from a table.
  9. SNAPSHOT isolation level — Additional isolation level (not a ANSI Transaction Isolation Level) that does not use write locks
  10. Statement-level recompile — More efficient recompilation of stored procedures
  11. Event notifications — Integration of Data Definition Language (DDL) and DML operations with Service Broker
  12. Large data types— The MAX specifier that can be used with VARCHAR, NVARCHAR & VARBINARY allows storage of up to 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes).These new data types deprecate TEXT, NTEXT & IMAGE data types (known as Large Object data types, or LOBs)
  13. APPLY operator — New JOIN syntax made for use with user-defined functions and XML
  14. EXCEPT & INTERSECT operators - EXCEPT returns any distinct values from the left query that are not also found on the right query while INTERSECT returns distinct rows that appear in both result sets.
  15. Dynamic Management Views [DMV] - returns server state information that can be used to monitor the server, check for problems, and to facilitate performance.
  16. Catalog Views - a completely new relational interface to the system catalog metadata.
  17. XML data type - for storing XML in the database.
  18. XQuery - a language that can query structured or semi-structured XML data.

To be continued....

Comments

Post a Comment