.NET Common Language Runtime integration: This means you can write stored procedures, functions, triggers, user-defined types, and even new aggregates in any .NET language.
Native HTTP SOAP Access: This means that the database server can serve up data via Web Services with no additional middleware at all, not even an instance of IIS.
New xml data type: You can store XML data natively in its own column type now.
XQuery support: SQL Server supports XQuery for querying against XML data, including extensions that allow XML DDL operations.
Improved Error Handling: T-SQL now supports TRY-CATCH blocks for modern error handling.
Query Notifications: With query notifications, an external application can request the database engine to notify it when the data in a cached result set changes. This can dramatically lessen the load from requerying.
Failover clustering now extends to 8 nodes on 32-bit systems, and 4 nodes on 64-bit systems.
Database mirroring introduces a new reliability model for hot standby on a database-by-database basis.
Users now have acess to a database during a restore operation, within certain limits. This helps get you up and running faster after problems.
Multiple Active Result Sets (MARS): SQL Server 2005 introduces the ability for multiple statements to return result sets at the same time on a single connection. MARS enables you to execute multiple queries yielding multiple results, over a single connection. An application can move between open result sets as needed. The performance and scalability benefits are obvious.
This new trick is courtesy of the new ADO.NET, in tandem with SQL Server 2005's ability to accommodate multiple active commands. Since MARS is part SQL Server 2005 and part ADO.NET 2.0, it is only available if you're using both.
Recursive Queries and Common Table Expressions:CTE is a view build especially for a query and used in one shot: each time we execute the query. In one sense it can be called a "non persistent view". The basic use of a CTE is to make clear some expression that contains a query twice or more in a complex query.
PIVOT and UNPIVOT Operators: Use PIVOT and UNPIVOT relational operators to manipulate a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where necessary on any remaining column values that are desired in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
APPLY, CROSS APPLY and OUTER APPLY Operators: The APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression. You specify APPLY in the FROM clause of a query, similar to the way you use the JOIN relational operator. APPLY comes in two forms: CROSS APPLY and OUTER APPLY.
CROSS APPLY invokes a table-valued function for each row in an outer table expression. You can refer to columns in the outer table as arguments to the table-valued function. CROSS APPLY returns a unified result set out of all of the results returned by the individual invocations of the table-valued function. If the table-valued function returns an empty set for a given outer row, that outer row is not returned in the result. OUTER APPLY is very similar to CROSS APPLY, but it also returns rows from the outer table for which the table-valued function returned an empty set. Null values are returned as the column values that correspond to the columns of the table-valued function.
Database snapshots:A database snapshot is a static, read-only copy of a source database that gives you a consistent view of a database as of a certain point in time. You can use the snapshot for operations supported by a read-only database, such as reporting applications. You can create multiple database snapshots on a source database. You can recover the source database to the point when the snapshot was created from a database snapshot. However, when recovering from a snapshot, the source database must be available. Database snapshots are available only in the Enterprise Edition of Microsoft SQL Server 2005 and all recovery models support database snapshots.
Ranking functions: Ranking functions return a ranking value for each row in a partition. Depending on the function used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
RANK()
NTILE()
DENSE_RANK()
ROW_NUMBER()
Transact-SQL Enhancements: New Metadata Views
Transact-SQL Enhancements: TOP Enhancements: TOP is enhanced in the following major ways:
You can now specify a numeric expression returning the number or percentage of rows to be affected by your query, optionally using variables and subqueries.
You can now use the TOP option in DELETE, UPDATE, and INSERT queries.
Transact-SQL Enhancements: TABLESAMPLE The TABLESAMPLE clause limits the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. You can use TABLESAMPLE to quickly return a sample from a large table when either of the following conditions is true:
1.The sample does not have to be a truly random sample at the level of individual rows.
2.Rows on individual pages of the table are not correlated with other rows on the same page.
SELECT FirstName, LastName FROM Person.Contact TABLESAMPLE (10 PERCENT) ;
Varchar(max) Data Type: The max specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data. These are similar to text, ntext & image data types in previous versions.
Output Keyword: Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable.
DDL Triggers: DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. DDL triggers, like regular triggers, fire stored procedures in response to an event. However, unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP.
No comments:
Post a Comment