The T-SQL enhancements in SQL Server 2005

Rich data types Richer-Types

1, varchar (max), nvarchar (max), and varbinary (max) data types can hold up to 2GB of data and can replace text, ntext, or image data types.

CREATE TABLE myTable
(
id INT,
content VARCHAR(MAX)
)

2. XML data type

XML data types allow users to save XML fragments or documents in a SQL Server database.

Fault Handling error Handling

1, the new exception handling structure

2, can capture and deal with the past will lead to the termination of the batch process errors. The premise is that these errors do not cause the connection to be interrupted (usually an error of more than 21 severity, such as a suspicious table or database integrity, hardware error, and so on.) ）。

3. Try/catch Construction

SET XACT_ABORT ON
BEGIN TRY
<core logic>
END TRY
BEGIN CATCH TRAN_ABORT
<exception handling logic>
END TRY
@@error may be quired as first statement in CATCH block

4. Demo Code

Use Demo
Go
--Create a worksheet
CREATE TABLE Student
(
Stuid INT not NULL PRIMARY KEY,
Stuname VARCHAR (50)
)
CREATE TABLE Score
(
Stuid INT not NULL REFERENCES student (Stuid),
Score INT
)
Go
INSERT into student VALUES (' Zhangsan ')
INSERT into student VALUES (102, ' Wangwu ')
INSERT into student VALUES (the ' Lishi ')
INSERT into student VALUES (' Maliu ')
--Invoke a run-time error
SET Xact_abort off
BEGIN TRAN
INSERT into score VALUES (101,90)
INSERT into score VALUES (102,78)
INSERT into score VALUES (107, 76)/* FOREIGN KEY Error * *
INSERT into score VALUES (103,81)
INSERT into score VALUES (104,65)
COMMIT TRAN
Go
SELECT * FROM Student
SELECT * FROM Score
--Use a Try ... Catch construct, and call a run-time error
SET Xact_abort off
BEGIN TRY
BEGIN TRAN
INSERT into score VALUES (101,90)
INSERT into score VALUES (102,78)
INSERT into score VALUES (107, 76)/* FOREIGN KEY Error * *
INSERT into score VALUES (103,81)
INSERT into score VALUES (104,65)
COMMIT TRAN
PRINT ' Transaction commit '
End TRY
BEGIN CATCH
ROLLBACK
PRINT ' Transaction rollback '
SELECT Error_number () as ErrorNumber,
Error_severity () as ErrorSeverity,
Error_state () as ErrorState,
Error_message () as errormessage;
End CATCH
Go
SELECT * FROM Score
Go