資源描述：
《T-SQL Enhancements in SQL Server 》由會員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、DAT326 T-SQLEnhancementsinSQLServer2008ItzikBen-GanMentor&CofounderSolidQualityMentorsItzik@SolidQ.comAgendaGoodies&MiscellaneousMERGEStatementGroupingSetsTable-ValuedParametersDatatypeEnhancementsT-SQLGoodies&MiscellaneousDeclareandInitializeVariablesCo
2、mpoundAssignmentOperatorsRowConstructorsCONVERTEnhancementsComposableDMLObjectDependenciesDeclareandInitializeVariablesCannowdeclareandinitializevariablesinthesamestatementValueisaself-containedexpressionofthetargettype(oratleastimplicitlyconvertible)DEC
3、LARE@iASINT=0,@sASNVARCHAR(25)=(SELECTlastnameFROMNorthwind.dbo.EmployeesWHEREemployeeid=5),@dASDATETIME=CURRENT_TIMESTAMP;CompoundAssignmentOperatorsNewassignmentoperators:+=,-=,*=,/=,%=UPDATEdbo.ProductsSETUnitPrice*=1.05--Alsotry+=10,-=10,/=2,UnitsInS
4、tock%=10OUTPUTinserted.ProductID,deleted.UnitPriceASOldPrice,inserted.UnitPriceASNewPriceWHEREProductID=10;RowConstructorsVALUESclausereturnsrelationaltablewithmultiplerowsUsewithINSERTstatementtoinsertmultiplerowsasanatomicoperation:INSERTINTOdbo.Custom
5、ers(custid,companyname,phone,address)VALUES(1,'cust1','(111)111-1111','address1'),(2,'cust2','(222)222-2222','address2'),(3,'cust3','(333)333-3333','address3'),(4,'cust4','(444)444-4444','address4'),(5,'cust5','(555)555-5555','address5');ComposableDMLCom
6、binemultipleDMLactionsinonestatementHaveonestatementoperateonrowsaffectedbyanotherSQLServer2008introducesINSERTFROMDML:INSERTINTOSELECT...FROM(7、UPDATE
8、DELETE
9、MERGE statementwithOUTPUTclause>)ASDWHERE...;ComposableDML,ExampleUpdateallp
10、roductssuppliedbysupplier1;increasetheunitpriceby15%Auditonlyproductupdatesthatchangedfrombelow20.0to20.0ormoreINSERTINTOdbo.ProductsAudit(ProductID,ColName,OldVal,NewVal)SELECTProductID,N'UnitPrice',OldVal,NewValFROM(UPDATEdbo.ProductsSETUnitPrice*=1.15
11、OUTPUTinserted.ProductID,deleted.UnitPriceASOldVal,inserted.UnitPriceASNewValWHERESupplierID=1)ASDWHEREOldVal<20.0ANDNewVal>=20.0;CONVERTEnhancementsNewstylestoconvertbetweencharacterstringsandbinaryvaluesStyle0:default,be
