表变量:USE northwind DECLARE @min INT SET @min=101 DECLARE @max INT SET @max=110 declare @temp TABLE([TempID] [int] IDENTITY (1, 1),[RowID] [int]) SET NOCOUNT ON INSERT @temp (RowID) SELECT OrderID FROM orders WHERE employeeID=3 ORDER BY RequiredDate DESC SET NOCOUNT OFF SELECT Orders.* FROM @temp t INNER JOIN Orders ON Orders.OrderID=#Temp.RowID WHERE t.TempID<=@max AND t.TempID>=@min
USE northwind DECLARE @min INT SET @min=101 DECLARE @max INT SET @max=110 declare @temp TABLE([TempID] [int] IDENTITY (1, 1),[RowID] [int]) SET NOCOUNT ON INSERT @temp (RowID) SELECT OrderID FROM orders WHERE employeeID=3 ORDER BY RequiredDate DESC SET NOCOUNT OFF SELECT Orders.* FROM @temp t INNER JOIN Orders ON Orders.OrderID=t.RowID WHERE t.TempID<=@max AND t.TempID>=@min
#Temp不一定会自动删除,如果你的连接还要继续使用,有可能会冲突。
http://expert.csdn.net/Expert/topic/908/908680.xml?temp=.043194 1:if object_id('tempdb..#temp') is not null drop table #temp2:临时表是在用户与SQL Server断开连接时被删除。3:多个用户执行同样的SQL语句产生的临时表不会冲突。但自己创建的临时表,在未断开连接再继续执行创建语句时会出错,所以一般都会在用临时表之前用 if object_id('tempdb..#temp') is not null drop table #temp 先删除前一次执行留下的临时表。我上面说的只对本地临时表(#temp)有效。
DECLARE @min INT SET @min=101
DECLARE @max INT SET @max=110
declare @temp TABLE([TempID] [int] IDENTITY (1, 1),[RowID] [int])
SET NOCOUNT ON
INSERT @temp (RowID)
SELECT OrderID FROM orders WHERE employeeID=3 ORDER BY RequiredDate DESC
SET NOCOUNT OFF
SELECT Orders.*
FROM @temp t
INNER JOIN Orders ON Orders.OrderID=#Temp.RowID
WHERE t.TempID<=@max AND t.TempID>=@min
DECLARE @min INT SET @min=101
DECLARE @max INT SET @max=110
declare @temp TABLE([TempID] [int] IDENTITY (1, 1),[RowID] [int])
SET NOCOUNT ON
INSERT @temp (RowID)
SELECT OrderID FROM orders WHERE employeeID=3 ORDER BY RequiredDate DESC
SET NOCOUNT OFF
SELECT Orders.*
FROM @temp t
INNER JOIN Orders ON Orders.OrderID=t.RowID
WHERE t.TempID<=@max AND t.TempID>=@min
1:if object_id('tempdb..#temp') is not null drop table #temp2:临时表是在用户与SQL Server断开连接时被删除。3:多个用户执行同样的SQL语句产生的临时表不会冲突。但自己创建的临时表,在未断开连接再继续执行创建语句时会出错,所以一般都会在用临时表之前用
if object_id('tempdb..#temp') is not null drop table #temp
先删除前一次执行留下的临时表。我上面说的只对本地临时表(#temp)有效。
我只是理论上想知道如果真的要并发时该怎么办。
我用的是dotnet的Connection.
我想用#temp和@temp区别应该不会太大。
--------------
如果我想把#temp的结果用Cache的方式储存起来,那么应该怎样弄才好呢??
(Where,Order By等信息在外部程序已经准备好,可以用来做Cache的标记)