Experience SQL Server2005

Error handling has always been a tricky part of T-SQL development. You have to manually check for errors after running each statement based on a scenario-by-scene basis and processing them. Transaction management is also cumbersome because you have to point out the state of your transaction and properly rollback or submit it. In SQL Server2005, Microsoft adds a try ... Catch blocks into T-SQL, providing an updated, more powerful error-handling capability.

exception handling is simple; when something unexpected happens in the execution of your code, such as an exception, you need to have a routine way of handling the exception. How to deal with this anomaly is determined by you as a developer. To add an exception to your T-SQL code, use the Try ... A catch block.

If you've recently used visual Studio, you're probably familiar with try ... The concept of a catch block. Execution in T-SQL is similar to it, but in T-SQL it actually has less choice than the full deployment you encounter in Visual Studio. To use a try ... Catch, put the T-SQL you want to run into a try block. If an error with a severity level of 10 or higher occurs during the execution of the code in the try, execution jumps to the exception handling code in the catch block. The basic structure looks like this:

BEGIN TRY
(T-SQL代码放在这里)
END TRY
BEGIN CATCH
(异常处理代码放在这里)
END CATCH

Row_number function

SQL Server2005 introduces us to a row_number function. Do you ever need to make a row number for your query result set? You sometimes find it useful to be able to do serial numbers for a row. Once upon a time, you had to do something tricky, like creating a temporary table with ordinal columns and inserting your select results into this temporary table. Now, with the Row_number function, you can get the number of rows added to the added column of your result set. To get the row number, you simply add the Row_number function as a column into your SELECT statement. You must also add the over statement to tell SQL Server how you want to add the row number.

SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', * from dbo.employee

Results

Row number	employee_id	Firstname	Lastname	Soc_sec
1	5623222	Tim	Jones	123-65-8745
2	5632111	Rob	Kinkad	456-69-8754
3	6365666	Jim	Miller	236-56-8989
4	7563333	Joe	Roberts	564-89-5555