SQL

Server2005中的十個最重要的T-SQL增強功能講座的總體內(nèi)容:TM關(guān)于

Microsoft?

SQL

Server

2005Transact-SQL數(shù)據(jù)庫語言中的十個最重要的增強功能的總體介紹和概括通過清晰的,簡捷的景象分析,實例介紹和演示來詳細的總結(jié)和解析每一個功能的特性和運用參加講座的前提條件最好具備關(guān)于Microsoft

Transact

SQL數(shù)據(jù)庫語言的相關(guān)知識和相應(yīng)的工作經(jīng)驗Level

200講座議程新型超大數(shù)據(jù)類型

Large-Value

Data

Types先進的錯誤處理

ErrorHandling通用表表達式

CommonTable

ExpressionsDDL觸發(fā)器DDLTriggers新的排序函數(shù)

RankingFunctions新的關(guān)系運算符PIVOT/UNPIVOT/APPLY新的結(jié)果集運算符

EXCEPTand

INTERSECTOUTPUT在DML中的特別運用TOP增強功能T-SQL和CLR的集成Newsystemdefined

data

typesAlso

capability

to

define

user

defined

data

types

usingCLR

–

separate

session.大容量存儲數(shù)據(jù)類型關(guān)于SQL

Server2000

任何超過8KB的大容量數(shù)據(jù)(文檔,圖片,音像)必須要存儲在

text,ntext,和image

數(shù)據(jù)類型中任何超過8KB數(shù)據(jù)面臨非常有限的操作功能SQL

Server2005提供的MAX

定義符

增強并且延伸了varchar,nvarchar

和varbinary

傳統(tǒng)數(shù)據(jù)類型的存儲能力

varchar(max),nvarchar(max),和varbinary(max)從此成為標準T_SQL的大容量存儲數(shù)據(jù)類型最多可存儲到場2GB的大容量數(shù)據(jù)Similar

in

behavior

to

the

smaller

counterpartsText

pointer

support

will

be

removed

in

afuture

version

of

SQL

Server.

We

recommend

converting

your

text,

ntext

and

image

columns

to

thenew

large-value

data

types.大容量存儲數(shù)據(jù)類型–(繼續(xù))關(guān)于大或小容量數(shù)據(jù)類型的統(tǒng)一的編程模式對比

Comparisons連接

Concatenation變量

Variables參數(shù)

Parameters觸發(fā)器

Triggers集合

Aggregates排序和索引

Index

Included

Columns當小容量數(shù)據(jù)增長并且超越8K極限時,整個過度過程十分平滑和簡捷通過

.WRITE

(expression,@Offset,@Length)可對大小容量數(shù)據(jù)實行局部或正體的直接更改對于小于8K的數(shù)據(jù),相比text,ntext和image存取效率明顯提高建議取代對text,ntext和image的應(yīng)用Newsystemdefined

data

typesAlso

capability

to

define

user

defined

data

types

usingCLR

–

separate

session.大容量存儲數(shù)據(jù)類型實例演示CREATE

TABLE

MyPublications(PublicationID

int,AbstractPublicationnvarchar(max),varbinary(max));Use

.WRITE

(expression,

@Offset,

@Length)

to

update

the

word‘writer’

(@Offset=20,

@Length=6)

with

‘a(chǎn)uthor’

in

the

AbstractcolumnUPDATE

MyPublicationsSET

Abstract

.WRITE

(N‘a(chǎn)uthor",

20,

6)WHERE

PublicationID

=

1;關(guān)于錯誤處理的提高在SQL

Server2000

@@ERROR返回最后一個執(zhí)行的T-SQL語句的錯誤代碼@@ERROR的值會隨著每一個T-SQL語句而被更新在SQL

Server2005中的豐富的異常處理框架TRY…CATCH

配置提供捕獲所有SQL

SERVER異常或錯誤的功能可以捕獲和處理過去會導(dǎo)致批處理終止的錯誤,從而阻止批處理的中斷提供處理和登錄異?；蝈e誤的功能當錯誤發(fā)生時,阻止T-SQL交易環(huán)境的丟失可以對錯誤的具體內(nèi)容進行讀取TRY…CATCH任何在TRY模塊中產(chǎn)生的錯誤會將控制的流程轉(zhuǎn)移到CATCH模塊中TRY…CATCH配置是可以被包含和兼容的語法和定義BEGIN

TRY{

sql_statement

|

statement_block

}END

TRYBEGIN

CATCH{

sql_statement

|

statement_block

}END

CATCH[

;

]TRY…CATCH–(繼續(xù))可處理所有付值給@@ERROR的T-SQL運行過程中的錯誤T-SQL語句中斷錯誤T-SQL水平中斷錯誤T-SQL批處理中斷錯誤T-SQL交易中斷錯誤不處理以下的情況:任何嚴重性在0-10范圍的警告和報告性的信息任何嚴重性在20-25范圍的中斷數(shù)據(jù)庫連接的錯誤注意事項KILL語句RAISERROR

可以用來自行生成錯誤控制流程會轉(zhuǎn)移到最接近的CATCH模塊中1=

Active

Committable

Transaction0=

No

Active

Transactions-1

=

Active

Uncommittable

Transaction錯誤信息的各類函數(shù)錯誤信息可通過運用以下函數(shù)而在CATCH模塊中被獲取ERROR_NUMBER()ERROR_SEVERITY()ERROR_STATE()ERROR_LINE()ERROR_PROCEDURE()ERROR_MESSAGE()交易信息任何交易中斷的錯誤都會最終導(dǎo)致成一個未成功交易XACT_STATE()總是返回任何交易的狀態(tài)(1,0,-1)錯誤處理實例演示BEGIN

TRY--

Divide-by-zero

error

shifts

control

flow

to

the

CATCH

blockSELECT

1/0;END

TRYBEGIN

CATCH--

Retrieve

error

informationSELECTERROR_NUMBER()

AS

ErrNumber,ERROR_SEVERITY()

AS

ErrSeverity,ERROR_STATE()

AS

ErrState,ERROR_PROCEDURE()

AS

ErrProc,ERROR_LINE()

AS

ErrLine,ERROR_MESSAGE()

AS

ErrMessage;END

CATCH;ErrNumberErrSeverityErrStateErrProcErrLineErrMessage8134161NULL3Divide

by

zero

error

encountered.錯誤處理實例演示–(繼續(xù))USE

AdventureWorks;GOCREATE

PROCEDURE

usp_GetErrorInfo

ASSELECTERROR_NUMBER()

AS

ErrNumber,ERROR_SEVERITY()

AS

ErrSeverity,ERROR_STATE()

as

ErrState,ERROR_LINE()

as

ErrLine,ERROR_PROCEDURE()

as

ErrProc,ERROR_MESSAGE()

as

ErrMessage;GOBEGIN

TRYBEGIN

TRANSACTION;--

Generate

a

constraint

violation

errorDELETE

FROM

Production.Product

WHERE

ProductID

=

980;COMMIT

TRANSACTION;END

TRYBEGIN

CATCHEXECUTE

usp_GetErrorInfo;IF

XACT_STATE()

<>

0ROLLBACK

TRANSACTION;END

CATCH;A

common

table

expression

(CTE)

can

be

thought

of

as

atemporary

result

set

that

is

defined

within

the

execution

scope

of

aSELECT,

INSERT,UPDATE,

DELETE,

or

CREATE

VIEW

statement.

A

CTE

is

similar

toa

derived

table

in

that

it

is

not

stored

as

an

object

and

lasts

only

for

theduration

of

the

query.

Unlike

aderived

table,

aCTEcan

be

self-referencing

and

can

be

referenced

multiple

times

in

the

same

query.通用表表達式樣(CTE)通用表表達式（CTE）是一個可以由定義語句引用的臨時表命名的結(jié)果集;可視為類似于視圖和派生表混合功能的改進版本它可以被定義在任何一個SELECT,INSERT,UPDATE,DELETE,或CREATE

VIEW的T-SQL語句中它可以被自己引用并在查詢中多次被引用用途:遞歸查詢替代那些不需要存儲在元數(shù)據(jù)中的視圖聚合派生表生成的表列可以在同一個T-SQL語句中多次引用結(jié)果集通用表表達式樣(CTE)語法和定義WITH

<cte_alias>(<column_aliases>)AS(<cte_query_definition>)SELECT

*FROM

<cte_alias>WITH子句和

SELECT/INSERT/DELETE/UPDATE結(jié)合在一起形成一個單獨的T-SQL語句在一個單獨的T-SQL語句中,可以有多個CTE被定義在一個單獨的WITH子句里通用表表達式樣(CTE)實例演示W(wǎng)ITH

mid_cteAS(SELECT

((MAX(value)

–

MIN(value))

/

2

)

AS

midvalFROM

Invoices)SELECTCASE>

mid_cte.midval

THEN

0WHEN

valueELSE

1END

AS

half,Invoices.*FROM

Invoices,

mid_cteORDER

BY

half;A

common

table

expression

(CTE)

can

be

thought

of

as

atemporary

result

set

that

is

defined

within

the

execution

scope

of

aSELECT,

INSERT,UPDATE,

DELETE,

or

CREATE

VIEW

statement.

A

CTE

is

similar

toa

derived

table

in

that

it

is

not

stored

as

an

object

and

lasts

only

for

theduration

of

the

query.

Unlike

aderived

table,

aCTEcan

be

self-referencing

and

can

be

referenced

multiple

times

in

the

same

query.遞歸的CTE是根據(jù)至少兩個查詢（或者稱為兩個成員）構(gòu)建的，一個是非遞歸查詢，也成為固定成員，只能調(diào)用一次，另外一個是遞歸查詢，也成為遞歸成員（RM），可以反復(fù)調(diào)用，直到查詢不再返回行。通用表表達式樣(CTE)和遞歸查詢?nèi)魏我粋€引用它自己的CTE可以被認為是遞歸的包含一個固定成員和遞歸成員;遞歸成員可以被反復(fù)調(diào)用遞歸查詢會直到遞歸成員不在返回行時才會結(jié)止WITH

<cte_alias>(<column_aliases>)AS(<cte_query_definition>

--

Anchor

member

is

definedUNION

ALL<cte_query_definition>

--

Recursive

member

is

defined--

referencing

cte_alias)SELECT

*FROM

<cte_alias>通用表表達式樣(CTE)和遞歸查詢實例演示--

Returns

all

employees

reporting

to

Employee

withEmployeeID=109WITH

EmpCTE

(EmployeeID,

ManagerID,

Title)AS(SELECT

EmployeeID,

ManagerID,

TitleFROM

HumanResources.EmployeeWHERE

EmployeeID

=

‘109’UNION

ALLSELECT

E.EmployeeID,

E.ManagerID,

E.TitleFROM

HumanResources.Employee

AS

EJOIN

EmpCTE

AS

MON

E.ManagerID

=

M.EmployeeID)SELECT

*

FROM

EmpCTEDDL觸發(fā)器允許為所有發(fā)生的T-SQL的數(shù)據(jù)定義語言(DDL)事件定義觸發(fā)器DDL觸發(fā)器可以被定義在:單個DDL語句:CREATE_TABLE,

ALTER_PROCEDURE,

DROP_LOGIN,etc一組語句:DDL_DATABASE_LEVEL_EVENTS,DDL_DATABASE_SECURITY_EVENTS,

etcDDL觸發(fā)器可被規(guī)劃在數(shù)據(jù)庫和服務(wù)器的范圍內(nèi)EventData()函數(shù)

在DDL觸發(fā)器內(nèi)部，可以通過訪問eventdata()函數(shù)獲得與激發(fā)該觸發(fā)器的事件有關(guān)的數(shù)據(jù)。該eventdata()函數(shù)返回有關(guān)事件的xml數(shù)據(jù)。DDL觸發(fā)器實例演示CREATE

TRIGGER

trg_disallow_create_tableON

DATABASEFOR

CREATE_TABLEASPRINT

"CREATE

TABLE

Issued.’;SELECT

EVENTDATA().value("(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]","nvarchar(max)");RAISERROR

("New

tables

cannot

be

created

in

this16,

1);database.",ROLLBACK;;ROW_NUMBER:

Returns

the

sequential

number

of

arow

within

apartition

of

aresult

set,

starting

at

1

for

the

first

row

in

each

partition.RANK:

Returns

the

rank

of

each

row

within

the

partition

of

a

result

set.

The

rank

of

a

row

is

one

plus

the

numberof

ranks

that

come

before

therow

in

question.DENSE_RANK:

Returns

the

rank

of

rows

within

the

partition

of

a

result

set,

without

any

gaps

in

the

ranking.

The

rank

of

a

row

is

one

plus

thenumber

of

distinct

ranks

that

come

before

the

row

in

question.NTIILE:

Distributes

the

rows

in

an

ordered

partition

into

aspecified

number

of

groups.

The

groups

are

numbered,

starting

atone.

For

each

row,NTILEreturns

the

number

of

the

group

to

which

the

row

belongs.Ties

work

differently

in

different

functionsROW_NUMBER

–

always

uniqueRANK

–

ties

produce

space

and

duplicates

in

rankingDENSE_RANK

–

duplicates

but

not

tiesNTILE

–

divided

into

n

approximately

equal

tiles新排序函數(shù)返回和提供任何一個結(jié)果行在分區(qū)里的排序值ROW_NUMBERRANKDENSE_RANKNTILE用途給結(jié)果行制定連續(xù)的整數(shù)排序值數(shù)據(jù)分析和采集:Paging,Scoring,HistogramsROW_NUMBER:

Returns

the

sequential

number

of

arow

within

apartition

of

aresult

set,

starting

at

1

for

the

first

row

in

each

partition.RANK:

Returns

the

rank

of

each

row

within

the

partition

of

a

result

set.

The

rank

of

a

row

is

one

plus

the

numberof

ranks

that

come

before

therow

in

question.DENSE_RANK:

Returns

the

rank

of

rows

within

the

partition

of

a

result

set,

without

any

gaps

in

the

ranking.

The

rank

of

a

row

is

one

plus

thenumber

of

distinct

ranks

that

come

before

the

row

in

question.NTIILE:

Distributes

the

rows

in

an

ordered

partition

into

aspecified

number

of

groups.

The

groups

are

numbered,

starting

atone.

For

each

row,NTILEreturns

the

number

of

the

group

to

which

the

row

belongs.Ties

work

differently

in

different

functionsROW_NUMBER

–

always

uniqueRANK

–

ties

produce

space

and

duplicates

in

rankingDENSE_RANK

–

duplicates

but

not

tiesNTILE

–

divided

into

n

approximately

equal

tiles新排序函數(shù)ROW_NUMBER

對于在一個結(jié)果集的一個分區(qū)里的一個結(jié)果行,返回一個連續(xù)的整數(shù)排序值RANK對于在一個結(jié)果集的一個分區(qū)里的結(jié)果行,返回行的等級

每一個結(jié)果行的等級取決于當前行與先前結(jié)果行之間就排序列值的比較結(jié)果;含相同排序列值的結(jié)果行會計算在當前行的等級值里DENSE_RANK對于在一個結(jié)果集的一個分區(qū)里的結(jié)果行,返回不間斷的行的等級

每一個結(jié)果行的等級取決于當前行與先前結(jié)果行之間就排序列值的比較結(jié)果;含相同排序列值的結(jié)果行不會計算在當前行的等級值里NTILE將在一個排序分區(qū)里的結(jié)果行分布成特定的小組對于每一個結(jié)果行,NTILE會返回這個結(jié)果行所屬的小組的號碼The

TVF

acts

as

the

right

input

and

the

outer

table

expression

acts

as

the

left

input.

The

right

input

is

evaluated

for

each

row

from

the

left

inputand

the

rows

produced

are

combined

for

the

final

output.

The

list

of

columns

produced

by

the

APPLY

operator

is

the

setofcolumns

in

the

leftinput

followed

by

the

listof

columns

returned

by

the

right

input.新排序函數(shù)實例演示SELECTROW_NUMBER()

OVER(ORDER

BY

City)

AS

Row_Num,RANK()

OVER(ORDER

BY

City)

AS

Rank,RANK()

OVER(PARTITION

BY

CityORDER

BY

LastName)

AS

Part_Rank,DENSE_RANK()

OVER(ORDER

BY

City)

AS

Dense_Rank,AS

NTile_4,NTILE(4)

OVER(ORDER

BY

City,

)LastName,

FirstName,

CityFROM

EmployeesORDER

BY

City,

LastNameThe

TVF

acts

as

the

right

input

and

the

outer

table

expression

acts

as

the

left

input.

The

right

input

is

evaluated

for

each

row

from

the

left

inputand

the

rows

produced

are

combined

for

the

final

output.

The

list

of

columns

produced

by

the

APPLY

operator

is

the

setofcolumns

in

the

leftinput

followed

by

the

listof

columns

returned

by

the

right

input.新排序函數(shù)實例演示:結(jié)果1

111

LeverlingKirkland2

211Janet21

BuchananStevenLondon3

2221

DodsworthAnneLondon4

2322

KingRobertLondon5

2422

SuyamaMichaelLondon6

613LastNameFirstNameCityw_RNaunmkbPearrt_DReannsNkeT_iRlaen_k4Performsome

manipulation

on

an

input

table-valued

expression

and

produce

an

output

table

as

a

resultPIVOT

provides

syntax

that

is

simpler

and

more

readable

than

what

may

otherwise

be

specified

in

acomplexseries

of

SELECT...CASEstatements.新的關(guān)系運算符

PIVOT

and

UNPIVOT可將一個table-valued的表達式轉(zhuǎn)換成一個表可被定義在FROM子句里PIVOT將行旋轉(zhuǎn)為列可能同時執(zhí)行聚合用途:處理

open-schema景象生成跨越表格的報表以便更好的統(tǒng)計數(shù)據(jù)UNPIVOT與PIVOT相反的操作;將列旋轉(zhuǎn)為行更多的用來轉(zhuǎn)換已經(jīng)經(jīng)過PIVOT處理的數(shù)據(jù)Performsome

manipulation

on

an

input

table-valued

expression

and

produce

an

output

table

as

a

resultPIVOT

provides

syntax

that

is

simpler

and

more

readable

than

what

may

otherwise

be

specified

in

acomplexseries

of

SELECT...CASEstatements.O實例演示MMaakkeePIVYYeeaarrTSSaalleessHHoonnddaaHHoonnddaa11999900119999002200000011000000AAccuurraa11999900550000Make19901991Honda30003000Acura500900Honda19913000Acura1991300Acura1991600Acura1992800SELECT

*

FROM

CarSalesPIVOT(SUM(Sales)FOR

Year

IN

([1990],

[1991]))

AS

PVTPerformsome

manipulation

on

an

input

table-valued

expression

and

produce

an

output

table

as

a

resultPIVOT

provides

syntax

that

is

simpler

and

more

readable

than

what

may

otherwise

be

specified

in

acomplexseries

of

SELECT...CASEstatements.I實例演示MMaakkeeUNPYYeeaarrVOTSSaalleessAAccuurraaAAccuurraa1199990011999911550000990000HHoonnddaa1199990033000000Honda19913000Make19901991Honda30003000Acura500900SELECT

Make,

Year,

SalesFROM

CarSalesPivotUNPIVOT(Sales

FORYear

IN

([1990],

[1991]))

AS

UNPVTThe

TVF

acts

as

the

right

input

and

the

outer

table

expression

acts

as

the

left

input.

The

right

input

is

evaluated

for

each

row

from

the

left

inputand

the

rows

produced

are

combined

for

the

final

output.

The

list

of

columns

produced

by

the

APPLY

operator

is

the

setofcolumns

in

the

leftinput

followed

by

the

listof

columns

returned

by

the

right

input.新的關(guān)系運算符

APPLYAPPLY關(guān)系運算符允許您對外部表的每個行調(diào)用指定的表值函數(shù)(TVF)一次可被定義在FROM子句里APPLY的兩種形式:CROSS

APPLY對于從表值函數(shù)中生成結(jié)果集的外部表,CROSSAPPLY從中返回相應(yīng)的行和內(nèi)部鏈接(INNER

JOIN)效果相同OUTER

APPLYOUTER

APPLY從外部表中返回所有的行用NULL替代空結(jié)果集里的表值函數(shù)列和外部鏈接(left

outer

join)效果相同The

TVF

acts

as

the

right

input

and

the

outer

table

expression

acts

as

the

left

input.

The

right

input

is

evaluated

for

each

row

from

the

left

inputand

the

rows

produced

are

combined

for

the

final

output.

The

list

of

columns

produced

by

the

APPLY

operator

is

the

setofcolumns

in

the

leftinput

followed

by

the

listof

columns

returned

by

the

right

input.APPLY實例和演示--

Returns

nothing

or

a

table

with

a

single

rowCREATE

FUNCTION

fn_greater

(@v

AS

FLOAT,

@n

AS

FLOAT)RETURNS

TABLEASRETURN

SELECT

@v

AS

Val

WHERE

@v

>

@n;GO--

Returns

rows

in

CarSales

where

Sales

>

1000SELECT

*

FROM

CarSalesCROSS

APPLY

fn_greater

(Sales,

1000);--

Returns

all

rows

in

CarSalesSELECT

*

FROM

CarSalesOUTER

APPLY

fn_greater(Sales,

1000);新的結(jié)果集運算符

EXCEPT

and

INTERSECT從另外兩個結(jié)果集產(chǎn)生出一個新的結(jié)果集EXCEPT返回所有屬于第一個結(jié)果集但不屬于第二個結(jié)果集的行INTERSECT返回所有共同屬于兩個結(jié)果集的行--

Get

authors

with

booksSELECT

au_id

FROM

authorsINTERSECTSELECT

au_id

FROM

titleauthor--

Get

authors

without

booksSELECT

au_id

FROM

authorsEXCEPTSELECT

au_id

FROM

titleauthorDML與OUTPUT新的OUTPUT子句可以從

INSERT/UPDATE/DELETE

T-SQL語句中返回數(shù)據(jù)從OUTPUT返回的數(shù)據(jù)會直接傳到調(diào)用的應(yīng)用程序中從OUTPUT

INTO返回的數(shù)據(jù)會直接被輸入到表或表變量中OU可TPU以T

用<dm來l_s對el”ect被_l輸ist入>

”和”被刪除”的數(shù)據(jù)進行OU被TPU更T

改<dm前l(fā)

_后se的lec數(shù)t_據(jù)lis值t>

INTO

<@table_var

|

output_table>DE用LET途E

:Sal隊es列.Sh操opp作in,gCa掌rt握Ite中m

O間UT結(jié)PUT果D集ELETED.*;TOP增強功能指定只有第一組結(jié)果行將會從查詢結(jié)果中返回在SQL

Server2000SELECT

TOP

<const>只可用在SELECT語句中在SQL

Server2005

SELECT[TOP(<表達式>)[PERCENT][WITHTIES]]<表達式>可以是變量,子查詢(SUB-QUERY)或常量還可用在INSERT,UPDATE,DELETE語句中TOP…WITH

TIES

只能用在SELECT語句中The

TVF

acts

as

the

right

input

and

the

outer

table

expression

acts

as

the

left

input.

The

right

input

is

evaluated

for

each

row

from

the

left

inputand

the

rows

produced

are

combined

for

the

final

output.

The

list

of

columns

produced

by

the

APPLY

operator

is

the

setofcolumns

in

the

leftinput

followed

by

the

listof

columns

returned

by

the

right

input.TOP增強功能實例演示--

Use

TOP

with

variable

to

return

the

n

most

recent

invoicesDECLARE

@n

AS

INT;SET

@n

=

2;SELECT

TOP

(@n)

*FROM

InvoicesORDER

BY

InvoiceDate

DESC;--

Use

TOP

with

subquery

to

calculate

the

average

number

of--

monthly

invoices

and

return

that

many

most

recent

invoicesSELECT

TOP

(SELECT

COUNT

(*)

/

DATEDIFF

(month,MIN(InvoiceDate),MAX(InvoiceDate))FROM

Invoices)

*FROM

InvoicesORDER

BY

InvoiceDate

DESC;T-SQL和CLR集成SQL

Server2000現(xiàn)有編程模型TransacT-SQL(T-SQL)擴展存儲過程(XP)SQL

Server2005新增加的CLR集成編程模型用任何CLR托管代碼編寫存儲過程、觸發(fā)器和函數(shù)可創(chuàng)建自定義的數(shù)據(jù)類型和聚合函數(shù)CLR基本概念CLR是WINDOWS

.NET架構(gòu)C#,VB.NET等編程語言的運行環(huán)境程序可被編譯成托管代碼并支持以下功能:自動內(nèi)存管理通用數(shù)據(jù)類型系統(tǒng)代碼校驗和讀取安全豐富的代碼庫獨立的應(yīng)用域支持反匯編什么是SQLCLR?用.NET編程語言編寫以下類型數(shù)據(jù)庫程序并在SQLSERVER中運行用戶自定義函數(shù)(UDF)存儲過程(SP)計算和邏輯密集的代碼用戶自定義數(shù)據(jù)類型(UDT)觸發(fā)器SSIS軟件包報表系統(tǒng)中的表達式和數(shù)據(jù)供應(yīng)源SQLCLR概述注冊和執(zhí)行數(shù)據(jù)庫中CLR托管代碼編寫.NET程序,編譯成托管代碼,并生成程序集程序集上載到SQL

Server2005用Create

Assembly數(shù)據(jù)定義語言(DDL)將其存儲到系統(tǒng)目錄(部署)創(chuàng)建T-SQL對象,并將其綁定到已經(jīng)上載的程序集的入口點，用Create

Procedure/Function/Trigger/Type/Aggregate應(yīng)用程序可以象T-SQL例程一樣的調(diào)用構(gòu)建和部署VS.NET

2005提供的‘SQL

Server項目’的代碼模板部署過程自動創(chuàng)建程序集中定義的例程、類型和聚合調(diào)試不受客戶端到服務(wù)器連接類型的影響跨語言無縫調(diào)試，例如從T-SQL過程進入CLR過程This

picture

summarizes

my

talk

in

one

slide.A

SQL

Server

developer

will

write

business

logic