S- Q- L- Server2005-中的十個(gè)最重要的- T- S- Q- L增強(qiáng)功能課件

SQL

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

Microsoft?

SQL

Server

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

Transact

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

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

Large-Value

Data

Types先進(jìn)的錯(cuò)誤處理

ErrorHandling通用表表達(dá)式

CommonTable

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

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

EXCEPTand

INTERSECTOUTPUT在DML中的特別運(yùn)用TOP增強(qiáng)功能T-SQL和CLR的集成Newsystemdefined

data

typesAlso

capability

to

define

user

defined

data

types

usingCLR

–

separate

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

Server2000

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

text,ntext,和image

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

Server2005提供的MAX

定義符

增強(qiáng)并且延伸了varchar,nvarchar

和varbinary

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

varchar(max),nvarchar(max),和varbinary(max)從此成為標(biāo)準(zhǔn)T_SQL的大容量存儲(chǔ)數(shù)據(jù)類型最多可存儲(chǔ)到場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.大容量存儲(chǔ)數(shù)據(jù)類型–(繼續(xù))關(guān)于大或小容量數(shù)據(jù)類型的統(tǒng)一的編程模式對比

Comparisons連接

Concatenation變量

Variables參數(shù)

Parameters觸發(fā)器

Triggers集合

Aggregates排序和索引

Index

Included

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

.WRITE

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

data

typesAlso

capability

to

define

user

defined

data

types

usingCLR

–

separate

session.大容量存儲(chǔ)數(shù)據(jù)類型實(shí)例演示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)于錯(cuò)誤處理的提高在SQL

Server2000

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

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

配置提供捕獲所有SQL

SERVER異?；蝈e(cuò)誤的功能可以捕獲和處理過去會(huì)導(dǎo)致批處理終止的錯(cuò)誤,從而阻止批處理的中斷提供處理和登錄異?；蝈e(cuò)誤的功能當(dāng)錯(cuò)誤發(fā)生時(shí),阻止T-SQL交易環(huán)境的丟失可以對錯(cuò)誤的具體內(nèi)容進(jìn)行讀取TRY…CATCH任何在TRY模塊中產(chǎn)生的錯(cuò)誤會(huì)將控制的流程轉(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運(yùn)行過程中的錯(cuò)誤T-SQL語句中斷錯(cuò)誤T-SQL水平中斷錯(cuò)誤T-SQL批處理中斷錯(cuò)誤T-SQL交易中斷錯(cuò)誤不處理以下的情況:任何嚴(yán)重性在0-10范圍的警告和報(bào)告性的信息任何嚴(yán)重性在20-25范圍的中斷數(shù)據(jù)庫連接的錯(cuò)誤注意事項(xiàng)KILL語句RAISERROR

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

Active

Committable

Transaction0=

No

Active

Transactions-1

=

Active

Uncommittable

Transaction錯(cuò)誤信息的各類函數(shù)錯(cuò)誤信息可通過運(yùn)用以下函數(shù)而在CATCH模塊中被獲取ERROR_NUMBER()ERROR_SEVERITY()ERROR_STATE()ERROR_LINE()ERROR_PROCEDURE()ERROR_MESSAGE()交易信息任何交易中斷的錯(cuò)誤都會(huì)最終導(dǎo)致成一個(gè)未成功交易XACT_STATE()總是返回任何交易的狀態(tài)(1,0,-1)錯(cuò)誤處理實(shí)例演示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.錯(cuò)誤處理實(shí)例演示–(繼續(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.通用表表達(dá)式樣(CTE)通用表表達(dá)式（CTE）是一個(gè)可以由定義語句引用的臨時(shí)表命名的結(jié)果集;可視為類似于視圖和派生表混合功能的改進(jìn)版本它可以被定義在任何一個(gè)SELECT,INSERT,UPDATE,DELETE,或CREATE

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

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

*FROM

<cte_alias>WITH子句和

SELECT/INSERT/DELETE/UPDATE結(jié)合在一起形成一個(gè)單獨(dú)的T-SQL語句在一個(gè)單獨(dú)的T-SQL語句中,可以有多個(gè)CTE被定義在一個(gè)單獨(dú)的WITH子句里通用表表達(dá)式樣(CTE)實(shí)例演示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è)查詢（或者稱為兩個(gè)成員）構(gòu)建的，一個(gè)是非遞歸查詢，也成為固定成員，只能調(diào)用一次，另外一個(gè)是遞歸查詢，也成為遞歸成員（RM），可以反復(fù)調(diào)用，直到查詢不再返回行。通用表表達(dá)式樣(CTE)和遞歸查詢?nèi)魏我粋€(gè)引用它自己的CTE可以被認(rèn)為是遞歸的包含一個(gè)固定成員和遞歸成員;遞歸成員可以被反復(fù)調(diào)用遞歸查詢會(huì)直到遞歸成員不在返回行時(shí)才會(huì)結(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>通用表表達(dá)式樣(CTE)和遞歸查詢實(shí)例演示--

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ā)器可以被定義在:單個(gè)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ā)器實(shí)例演示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ù)返回和提供任何一個(gè)結(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

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

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

每一個(gè)結(jié)果行的等級取決于當(dāng)前行與先前結(jié)果行之間就排序列值的比較結(jié)果;含相同排序列值的結(jié)果行不會(huì)計(jì)算在當(dāng)前行的等級值里NTILE將在一個(gè)排序分區(qū)里的結(jié)果行分布成特定的小組對于每一個(gè)結(jié)果行,NTILE會(huì)返回這個(gè)結(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ù)實(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ù)實(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)系運(yùn)算符

PIVOT

and

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

open-schema景象生成跨越表格的報(bào)表以便更好的統(tǒng)計(jì)數(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實(shí)例演示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實(shí)例演示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)系運(yùn)算符

APPLYAPPLY關(guān)系運(yùn)算符允許您對外部表的每個(gè)行調(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實(shí)例和演示--

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é)果集運(yùn)算符

EXCEPT

and

INTERSECT從另外兩個(gè)結(jié)果集產(chǎn)生出一個(gè)新的結(jié)果集EXCEPT返回所有屬于第一個(gè)結(jié)果集但不屬于第二個(gè)結(jié)果集的行INTERSECT返回所有共同屬于兩個(gè)結(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語句中返回?cái)?shù)據(jù)從OUTPUT返回的數(shù)據(jù)會(huì)直接傳到調(diào)用的應(yīng)用程序中從OUTPUT

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

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

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

改<dm前l(fā)

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

INTO

<@table_var

|

output_table>DE用LET途E

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

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

Server2000SELECT

TOP

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

Server2005

SELECT[TOP(<表達(dá)式>)[PERCENT][WITHTIES]]<表達(dá)式>可以是變量,子查詢(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增強(qiáng)功能實(shí)例演示--

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)擴(kuò)展存儲(chǔ)過程(XP)SQL

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

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

Server2005用Create

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

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

2005提供的‘SQL

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

picture

summarizes

my

talk

in

one

slide.A

SQL

Server

developer

will

write

business

logic