SQL中select与set的区别.docx

SQL赋值SET和SELECT有什么区别？事实上SQL Server数据库的开发者在SQL Server 7.0（1999)版本之前都是用SELECT赋值的，但在SQL Server 7.0 之后，推出了SET方法赋值，而且Microsoft在其联机帮助文档中明确提出推荐使用SET方法赋值。这让开发者很迷惑，Microsoft也没有明确说明为什么SET方法是被推荐的。本文会讲明SET和SELECT的区别，而且让你明白什么时候用SET，什么时候用SELECT。以下代码为使用SET和SELECT赋值。DECLARE Variable1 AS int, Variable2 AS intSELECT Variable1 = 1SET Variable2 = 2下面就可以说说区别了：1.假如你是标准SQL的开发者，那么请使用SET吧，因为SET是ANSI标准的SQL语句，SELECT不是。2.你可以使用SELECT一次给两个以前变量赋值，但是SET不能。DECLARE Variable1 AS int, Variable2 AS intSELECT Variable1 = 1, Variable2 = 2SET Variable1 = 1SET Variable2 = 2到目前来说，这没有问题，但是如果你曾经写过处理错误的T-SQL语句。你可能会意识到系统变量ERROR and ROWCOUNT必须要在一句SQL语句中捕获。并且是在DML语句（INSERT, UPDATE, DELETE等）之后立即捕获。如果不是这样，这样系统变量会马上重新设置为0。如果这时你还是使用校准SET来赋值，那你就麻烦了。如下例子说明这个问题：DECLARE Error int, RowCount intSELECT price/0 FROM dbo.titlesSET RowCount = ROWCOUNTSET Error = ERRORSELECT Error AS ErrorGO以上代码如果在pubs数据库中运行，ERROR值会显示为0，但实际是显示 division by zero，错误号为8134.在这样的情况下，我们可以忘掉SET语句，使用SELECT吧。DECLARE Error int, RowCount intSELECT price/0 FROM dbo.titlesSELECT RowCount = ROWCOUNT, Error = ERRORSELECT Error AS Error但是如果你坚持使用标准ANSI SQL赋值方法，也是有办法的，但是可读性不好，不推荐这样。DECLARE ErrorAndRowcount AS varchar(25), Error int, RowCount intSELECT price/0 FROM dbo.titlesSET ErrorAndRowcount = CAST(ERROR AS varchar(12) + . + CAST(ROWCOUNT AS varchar(12)SET Error = CAST(PARSENAME(ErrorAndRowcount, 2) AS int)SET RowCount = CAST(PARSENAME(ErrorAndRowcount, 1) AS int)SELECT Error AS Error, RowCount AS Row_CountSET Error = CAST(LEFT(ErrorAndRowcount, CHARINDEX(., ErrorAndRowcount)-1) AS int)SET RowCount = CAST(RIGHT(ErrorAndRowcount, CHARINDEX(., REVERSE(ErrorAndRowcount)-1) AS int)SELECT Error AS Error, RowCount AS Row_CountGO3.SET和SELECT还有一个区别是，当使用查询出来的值赋值给变量时，SET和SELECT都可以实现，但当查询出的值为多个是，SET会提示错误，但SELECT不会，只会接受最后一个值。这点很重要，也是很多程序Bug容易被忽略的地方。如下是例子：SET NOCOUNT ONCREATE TABLE #Test (i int, j varchar(10)INSERT INTO #Test (i, j) VALUES (1, First Row)INSERT INTO #Test (i, j) VALUES (1, Second Row)GODECLARE j varchar(10)SELECT j = j FROM #Test WHERE i = 1SELECT jGO但你用SET重写上面的SQL语句。DECLARE j varchar(10)SET j = (SELECT j FROM #Test WHERE i = 1)SELECT j将会报错：Server: Msg 512, Level 16, State 1, Line -1074284106Subquery returned more than 1 value. This is not permitted when the subqueryfollows =, !=, , , = or when the subquery is used as an expression.所以我们在查询赋值时推荐使用SET，如果你还想用SELECT，那么请这样使用：DECLARE j varchar(10)SELECT j = (SELECT j FROM #Test WHERE i = 1)SELECT j但是如果查询不返回任何记录时，请小心。DECLARE Title varchar(80)SET Title = Not FoundSET Title =(SELECT titleFROM dbo.titlesWHERE title_id = InvalitTitleID)SELECT TitleGODECLARE Title varchar(80)SET Title = Not FoundSELECT Title = titleFROM dbo.titlesWHERE title_id = InvalitTitleIDSELECT TitleGO最后一点，SET和SELECT在性能上有没有区别呢，这也是很多开发者不太清楚的地方。经过测试我们发现，SET和SELECT在赋值方面，性能没有很大的区别。但是SELECT语句可以实现一句给多个变量赋值，所以性能略高于SET。以下为测试代码：DECLARE Test1 int, Test2 int, Test3 int, TestVar1 int, TestVar2 intDECLARE Loop int, Start datetime, CTR int, TimesToLoop1 int, TimesToLoop2 intSET Test1 = 0SET Test2 = 0SET Test3 = 0SET Loop = 0SET TestVar2 = 0SET TimesToLoop1 = 10SET TimesToLoop2 = 50000WHILE Loop TimesToLoop1BEGINSET Start = CURRENT_TIMESTAMPSET CTR = 0WHILE CTR TimesToLoop2BEGINSET TestVar1 = 1SET TestVar2 = TestVar2 - TestVar1SET CTR = CTR + 1ENDSET Loop = Loop + 1SET Test1 = Test1 + DATEDIFF(ms, Start, CURRENT_TIMESTAMP)ENDSET Loop = 0SET TestVar2 = 0WHILE Loop TimesToLoop1BEGINSELECT Start = CURRENT_TIMESTAMPSELECT CTR = 0WHILE CTR TimesToLoop2BEGINSELECT TestVar1 = 1SELECT TestVar2 = TestVar2 - TestVar1SELECT CTR = CTR + 1ENDSELECT Loop = Loop + 1SELECT Test2 = Test2 + DATEDIFF(ms, Start, CURRENT_TIMESTAMP)ENDSET Loop = 0SET TestVar2 = 0WHILE Loop TimesToLoop1BEGINSELECT Start = CURRENT_TIMESTAMP, CTR = 0WHILE CTR TimesToLoop2BEGINSELECT TestVar1 = 1, TestVar2 = TestVar2 - TestVar1, CTR = CTR + 1ENDSELECT Loop = Loop + 1, Test3 = Test3 + DATEDIFF(ms, Start, CURRENT_TIMESTAMP)ENDSEL