The T-SQL enhancements in SQL Server 2005

Server

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, you can capture and deal with the past will lead to the termination of the batch process error
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 is quired as a 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

Snapshot Isolation Snapshot Isolation

1, the writer does not hinder the reader program
2. Snapshot isolation must is enabled for DB
ALTER Database Database SET allow_snapshot_isolation on
3, Snapshot isolation must is enabled for connection
Set Transaction ISOLATION Level Snapshot
4, UPDATE transactions keep old versions of the data in a linked list
5, the new isolation level provides the following benefits:
1 improves data availability for read-only applications
2 Allow non-blocking read operations to be performed in an OLTP environment
3 Automatic forced conflict detection for write transactions
6. Demo Code
CREATE DATABASE Demo2
Go
Use Demo2
ALTER DATABASE Demo2 SET allow_snapshot_isolation on
CREATE TABLE Test
(
Tid INT not NULL primary key,
Tname VARCHAR (m) not NULL
)
INSERT into Test VALUES (1, ' version1 ')
INSERT into Test VALUES (2, ' Version2 ')

--Connect a

Use Demo2
BEGIN TRAN
UPDATE test SET tname= ' Version3 ' WHERE tid=2
SELECT * FROM Test

--Connection Two
Use Demo2
SET Transaction ISOLATION Level Snapshot
SELECT * FROM Test

Top Enhancements

1, top enhancement
You can specify a numeric expression to return the number of rows or percentages to be affected by a query, or you can use a variable or subquery depending on the situation.
You can use the top option in Delete, update, and insert queries.

2, better replace the SET ROWCOUNT option to make it more efficient.

OUTPUT

1. SQL Server 2005 introduces a new output clause so that you can flush the data back into the table variable in the Modify statement (INSERT, UPDATE, DELETE).

2. The syntax for the new output sub-bureau is:
OUTPUT <dml_select_list> into @table_variable
You can access the old/movie image of the modified row by referencing the inserted table or deleted table in the same way as the access trigger. In an INSERT statement, you can access only the inserted table. In the DELETE statement, only the deleted table can be accessed. In the UPDATE statement, you can access the inserted tables and the deleted tables.

3, Code Demo
Use Demo
Go
CREATE TABLE TT
(
ID INT IDENTITY,
C1 VARCHAR (15)
)
Go

INSERT into TT VALUES (' R1 ')
INSERT into TT VALUES (' R2 ')
INSERT into TT VALUES (' R5 ')
INSERT into TT VALUES (' R6 ')
INSERT into TT VALUES (' R7 ')
INSERT into TT VALUES (' R8 ')
INSERT into TT VALUES (' R9 ')
INSERT into TT VALUES (' R10 ')

DECLARE @del as TABLE (Deletedid INT, Deletedvalue VARCHAR (15))
DELETE TT
OUTPUT deleted.id, deleted.c1 into @del
WHERE ID < 3
SELECT * from @del
Go
-----------------------------------------------
Use Demo
Go
CREATE TABLE toptest (column1 VARCHAR (150))
Go
INSERT into Toptest VALUES (' t1 ')
INSERT into Toptest VALUES (' T2 ')
INSERT into Toptest VALUES (' T3 ')
INSERT into Toptest VALUES (' T4 ')
INSERT into Toptest VALUES (' T5 ')
INSERT into Toptest VALUES (' T6 ')
INSERT into Toptest VALUES (' T7 ')
INSERT into Toptest VALUES (' T8 ')
SELECT * from Toptest
Go

CREATE TABLE Toptest2 (Column2 VARCHAR (150))
Go
INSERT into Toptest2 VALUES (' C1 ')
INSERT into Toptest2 VALUES (' C2 ')

--Declare 3 variables
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT

--Assigning value
SET @a = 10
SET @b = 5
SELECT @c = @a/@b

--Using a calculation expression
SELECT Top (@c) * from Toptest

--Using a SELECT statement as a condition
Select Top (select COUNT (*) from TOPTEST2) *
From Toptest

--Indicates top
DELETE Top (2) toptest where column1> ' T6 '

--Update Top
UPDATE Top (2) toptest SET column1 = ' Hi ' where column1<= ' T2 '

SELECT * from Toptest

Sort function Ranking Functions

1, SQL Server introduced several new sorting functions: such as Row_number, RANK, Dense_rank and so on. These new functions allow you to efficiently analyze data and provide sorted values to the query's result rows.

2, the sort function follows a similar syntax pattern:
() over
([PARTITION by]
ORDER BY)
This function can only be specified in the two clauses of a query-in a SELECT clause or an ORDER BY clause. Different functions are discussed in detail below.

3, Row_number
Row_number is the order of result sets, not the original order in which records are stored in the database
Use Demo
Go
CREATE TABLE Rankorder
(
OrderID INT,
Qty INT
)
Go
INSERT rankorder VALUES (30001,10)
INSERT rankorder VALUES (10001,10)
INSERT rankorder VALUES (10006,10)
INSERT rankorder VALUES (40005,10)
INSERT rankorder VALUES (30003,15)
INSERT rankorder VALUES (30004,20)
INSERT rankorder VALUES (20002,20)
INSERT rankorder VALUES (20001,20)
INSERT rankorder VALUES (10005,30)
INSERT rankorder VALUES (30007,30)
INSERT rankorder VALUES (40001,40)
Go
SELECT Orderid,qty,
Row_number () Over (qty) as RowNumber,
Rank () Over (Order by qty) as Rank,
Dense_rank () Over (qty) as Denserank
From Rankorder
Order BY Qty

Universal table Expression Common table Expressions

A common table expression (CTE) is a result set that can be named by a temporary table that is referenced by a definition statement. In their simple form, you can treat a CTE as an improved version that resembles the blending of views and derived tables. The way in which you reference a CTE in the FROM clause of a query is similar to how you refer to derived tables and views. To define a CTE only once, you can refer to it multiple times in a query. In the definition of a CTE, you can reference variables that are defined in the same batch of processing. But the real power of a CTE is their recursive function, which is that a CTE can contain references to itself.

General forms of queries within views, derived tables, and CTE

1, view
CREATE VIEW <view_name> (<column_aliases>) as <view_query>

2. Derived table
SELECT * FROM (<derived_table) query>) as <dericed_table_alias> (<column_aliases>)

3. CTE
With <cte_alias> (<column_aliases>)
As
{
<cte_query>
)
SELECT * from <cte_alias]>
After the keyword with, provide an alias for the CTE, provide an optional list of aliases for its result column, write the body of the CTE, and then reference it from the external query.

4. Demo Code
Use AdventureWorks
Go
With Salescte (ProductID, SalesOrderID)
As
(
SELECT ProductID, COUNT (SalesOrderID)
From Sales.SalesOrderDetail
GROUP by ProductID
)
SELECT * from Salescte

Common table expressions for recursive CTEs recursion

A recursive CTE is built based on at least two queries (or two members). A non-recursive query, also a fixed member, can only be called once, and the other is a recursive query, also a recursive member (RM), which can be called repeatedly until the query no longer returns rows. The query is connected by the union ALL operator to a single CTE.

--common table expressions that use recursion
Use Demo
Go
CREATE TABLE Carparts
(
Carid INT not NULL,
Part VARCHAR (15),
Subpart VARCHAR (15),
Qty INT
)
Go
INSERT carparts VALUES (1, ' body ', ' Door ', 4)
INSERT carparts VALUES (1, ' body ', ' Trunk Lid ', 1)
INSERT carparts VALUES (1, ' body ', ' car Hood ', 1)
INSERT carparts VALUES (1, ' Door ', ' Handle ', 1)
INSERT carparts VALUES (1, ' Door ', ' Lock ', 1)
INSERT carparts VALUES (1, ' Door ', ' Window ', 1)
INSERT carparts VALUES (1, ' body ', ' rivets ', 1000)
INSERT carparts VALUES (1, ' Door ', ' rivets ', 100)
INSERT carparts VALUES (1, ' Door ', ' Mirror ', 1)
Go
SELECT * from Carparts
Go

With Carpartscte (Subpart, Qty)
As
(
--Fixed member (AM):
--Select query without reference CARPARTSCTE
SELECT Subpart, Qty
From Carparts
WHERE part = ' body '
UNION All
--Recursive member (RM):
--Select query reference carpartscte
SELECT Carparts.subpart, Carpartscte.qty * carparts.qty
From Carpartscte
INNER JOIN carparts on carpartscte.subpart = Carparts.part
WHERE Carparts.carid = 1
)
--External query
SELECT Subpart, SUM (Qty) as Totalnum
From Carpartscte
GROUP by Subpart

The new relational operator pivot/unpivot/apply

1, PIVOT
The pivot operator rotates the row into columns and may perform aggregations at the same time. The important thing to note when using the pivot operator is that you need to provide it with a query expression that uses a view, a derived table, or a CTE to return only the columns of interest.

2, Unpivot
The UNPIVOT operator performs the opposite of the pivot operator; He rotates the column as a row.

3, APPLY
The Apply relational operator allows you to invoke the specified table-valued function once for each row of the external table. You can specify apply in the FROM clause of the query in the same way that you use the Join relational operator. Apply has two forms: CROSS apply and outer apply.

Demonstrate:

Use Demo
Go

CREATE TABLE Orders
(
The Customer VARCHAR (%) is not NULL,
Product VARCHAR not NULL,
Quantity INT not NULL
)
Go
INSERT orders VALUES (' Mike ', ' Bike ', 3)
INSERT orders VALUES (' Mike ', ' Chain ', 2)
INSERT orders VALUES (' Mike ', ' Bike ', 5)
INSERT orders VALUES (' Lisa ', ' Bike ', 3)
INSERT orders VALUES (' Lisa ', ' Chain ', 3)
INSERT orders VALUES (' Lisa ', ' Chain ', 4)
INSERT orders VALUES (' Lisa ', ' Bike ', 2)

SELECT * FROM Orders

SELECT * FROM Orders
PIVOT (SUM (quantity) for product in ([Bike],[chain])) as a
Use Demo
Go
CREATE TABLE SALES1
(
[Year] Int
Quarter CHAR (2),
Amount FLOAT
)
Go
INSERT into SALES1 VALUES (2001, ' Q1 ', 80)
INSERT into SALES1 VALUES (2001, ' Q2 ', 70)
INSERT into SALES1 VALUES (2001, ' Q3 ', 55)
INSERT into SALES1 VALUES (2001, ' Q3 ', 110)
INSERT into SALES1 VALUES (2001, ' Q4 ', 90)
INSERT into SALES1 VALUES (2002, ' Q1 ', 200)
INSERT into SALES1 VALUES (2002, ' Q2 ', 150)
INSERT into SALES1 VALUES (2002, ' Q2 ', 40)
INSERT into SALES1 VALUES (2002, ' Q2 ', 60)
INSERT into SALES1 VALUES (2002, ' Q3 ', 120)
INSERT into SALES1 VALUES (2002, ' Q3 ', 110)
INSERT into SALES1 VALUES (2002, ' Q4 ', 180)
Go

SELECT * from SALES1
PIVOT
(SUM (Amount)--use Sum to aggregate quantity columns
For [quarter]--pivot Quarter column
In (Q1, Q2, Q3, Q4))--use season
As P
Go

SELECT * into Temp1 from orders
PIVOT (sum (quantity) for product in ([Bike],[chain])) as a

SELECT * FROM Temp1

SELECT customer, product,quantity
from Temp1
Unpivot (Quantity to product in ([Bike],[chain])) as a
------- ---------------------------------------------
Use demo
Go
CREATE TABLE Arrays
(
  aid INT not NULL IDENTITY PRIMARY KEY,
  array VARCHAR (7999) not NULL
)
Go
insert into Arrays VALUES (')
Insert I NTO Arrays values (' ")
INSERT into Arrays values (' 20,40,30 ')
inserts into Arrays values (' -1,-3,-5 ')
Go
CREATE function  function1 (@arr as VARCHAR (7999))
  RETURNS @t TABLE (pos int NOT NULL, value INT NOT null)
as
BEGIN
  DECLARE @end as int, @start as int, @pos as int
  SELECT @arr = @arr + ', ', @pos = 1,
&N bsp;   @start = 1, @end = CHARINDEX (', ', @arr, @start)
  while @end > 1
  BEGIN
 &nbs p;  INSERT into @t VALUES (@pos, SUBSTRING (@arr, @start, @end-@start))

SELECT @pos = @pos + 1,
@start = @end + 1, @end = CHARINDEX (', ', @arr, @start)
End
Return
End

--Test
SELECT * from function1 (' 200,400,300 ')
Go

SELECT A.aid, f.*
From Arrays as A
CROSS APPLY function1 (array) as F
Go
SELECT A.aid, f.*
From Arrays as A
OUTER APPLY function1 (array) as F
Go

DDL triggers DDL triggers

SQL Server 2005 can define triggers for DDL events for a range of the entire server or database. You can also define DDL triggers for a single DDL statement (for example: Creat_table, drop_table, and so on) or for a set of statements (for example, specifying that ddl_database_level_events want triggers to trigger all DDL events in the database).

Within a DDL trigger, you can  about the event that fired the trigger by accessing the EVENTDATA () function. The EVENTDATA () function returns the XML data about the event.

The scenarios that are particularly useful for DDL triggers include integrity checking of DDL changes, audit schemes, and other scenarios.

Code Demo:

Use Demo
Go
CREATE TRIGGER prevent_drop_table on DATABASE for drop_table
As
RAISERROR (' no permission to delete the table. ', 10, 1)
PRINT ' tries to delete the table in database ' + db_name () + '.
PRINT CONVERT (nvarchar (1000), eventdata ())
ROLLBACK
Go
--Test
CREATE TABLE Testdrop (col1 INT)
Go
INSERT into Testdrop VALUES (1)

DROP TABLE Testdrop

--Server
CREATE TRIGGER Audit_ddl_logins on the all SERVER
For Create_login, Alter_login, Drop_login
As
PRINT ' occurs with DDL LOGIN. '
PRINT CONVERT (nvarchar (1000), eventdata ())
Go

--Test
CREATE LOGIN login1 with PASSWORD = ' 123 '
ALTER LOGIN login1 with PASSWORD = ' xyz '
DROP LOGIN Login1

Summarize

The transaction-sql enhancements in SQL Server 2005 improve the ability of users to write queries, enable users to improve the performance of their code, and extend the ability to handle errors.

The improvements that SQL Server 2005 has made on Transaction-sql reflect its better meeting the requirements of the ANSI-99 SQL specification and the needs of the customer.

The choice between Transaction-sql and managed code.