T-SQL常见基础疑点问答总结(1)

贴子只代表个人看法和观点，仅作交流使用，如有错误，敬请指正。

--建立测试环境

IF object_id('tb') IS NOT NULL

	DROP TABLE tb

GO



CREATE TABLE tb(id INT IDENTITY(1,1),v VARCHAR(10))

GO

INSERT tb SELECT 'a'

UNION ALL SELECT 'b'

INSERT tb SELECT 'x'

UNION ALL SELECT 'z'

GO

(1)字串变量当数据库对象用

--=**********************************************

--为什么我执行下面的语句选不到正确数据

CREATE PROC p

(

@field VARCHAR(10),--字段名

@value VARCHAR(10) --值

)

AS

	SELECT * FROM tb WHERE @field=@value

GO



EXEC p 'v','a'

GO

DROP PROC p

GO

--为什么我执行下面语句报错

CREATE PROC p

(

@table VARCHAR(10),--表名

@value VARCHAR(10) --值

)

AS

	SELECT * FROM @table WHERE v=@value

说明:
在这二个存储过程中，@table,@field,@value都被定义为varchar
第一个实际上执行的是两个变量的比较，它的作用相当于
IF @field=@value
SELECT * FROM tb
语法未错，意思上却大错特错

第二个实际上执行的是
SELECT * FROM 一个字串 WHERE v=@value
如何能从一个字串中查询结果集呢？错误的把字串当成表对象来理解.
请记住@table是个表名,它是个字段，而非表对象,不是object

可以用EXEC执行动态语句来解决，比如

EXEC('SELECT * FROM [' + @table + '] WHERE v=''' + @value + '''')

(2)为什么我在执行一个批语句(可能是存储过程，
也可能是个FUNCTION,也可能只是几条语句的组合)时，
提示错误，我照着提示的错误，检查，但是那里没有报错啊
--=**********************************************
比如，上面的第二个存储过程,@table明明是存储过程的输入参数，它为什么提示我@table未定义
在上面，我已经讲了这句为什么出错的原因，
当然@table如果是表变量的话，那么那句select是不会有问题的，
但表变量不能用做输入参数。但它为什么这样提示呢?
这与sql内部机制有关,sql查询语句执行前先由命令解析器进行语法检查，如果语法检查未通过，
会扔出错误信息（通常这里的提示是精确的），
当语法检查通过，则将其编译为可执行的内部格式(查询树),
而非语法错误时，因为是执行时报错,执行期间是内部格式代码，只能扔出个大致错误信息.
了解了这一点，当您的sql语句报错后，先检查是否语法错误，
如果不是，那么需要仔细检查了，因为按着错误提示去找，很有可能兜圈子。
--=**********************************************

(3)为什么我明明定义了@n变量，却提示我变量不存在?

--=**********************************************

DECLARE @field VARCHAR(10),@n INT

SET @field='v'

EXEC('SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=''a''')

上面有三条语句:
rows 语句
1 定义两变量
2 给@field赋值
3 执行动态语句

那么这三条语句，在执行时进行语法检查和编译,大至为:

DECLARE

SET

EXEC

这是编译后要执行三步操作，它们在一进程空间中进行编译。
随着语句的运行，exec内部的语句被购造成'select @n=count(*) from tb where v=''a'''
这时，EXEC要执行的这条语句，继续要被命令解析器进行检查和编译,
它的编译内存空间与外部这个批不同，术语我可能表达不太清楚，
只想说明一点，动态语句在执行过程中才被首次编译,所以在这个空间中，提示@n未定义.

解决方式，sqlserver为我们提供了sp_executesql来完成这个操作。
上面要完成的操作，可以改写为:

DECLARE @field VARCHAR(10),@n INT,@sql NVARCHAR(4000)

SET @field='v'

SET @sql=N'SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=''a'''

EXEC sp_executeSQL @sql,N'@n INT OUTPUT',@n OUTPUT

SELECT @n

--=**********************************************

(4)以下语句为什么提示我临时表不存在，明明我生成了临时表的
--=**********************************************

EXEC('SELECT * INTO # FROM tb')

SELECT * FROM #

说明:
在上面解释@n为何未定义时说了关于编译时进程内空间的问题。
我们再联合局部临时表的生名周期，局部临时表在当间会话进程中有效
EXEC内部的语句是新开辟的一个进程空间执行的，所以当它执行完毕后，
生命周期结束，因此外部无法再访问

那为什么下面的又可以呢?

SELECT * INTO # FROM tb

EXEC('SELECT * FROM #')

PRINT 'aa'

DROP TABLE #

--=********************************************

这个很明显,上面执行了三行，在一个批中,直到PRINT 'aa'结束后，
进程结束，#临时表才会被销毁，因为它的生命周期在整个会话中.
所以当EXEC执行时它仍存在

(5)为什么我给一个变量赋值，得到的不是我想要的,即为什么@v的值不是a,而是z

--=**********************************************

DECLARE @v VARCHAR(10)

SELECT @v=v FROM tb

SELECT @v

说明
SELECT 变量=字段 FROM tb
这种赋值与SET赋值的主要区别:
a, SET赋值，一次只能给一个变量赋值，而SELECT 则可多个
b, SET赋值语义更明确，是赋值。而SELECT 可能是赋值也可能是数据查询
c, 最重要的一个,SELECT 可以从表中取值，而SET不能。
说到这有人会说 SET @v=(SELECT TOP 1 v FROM tb) 也可以,
这样确实可以，但实际上它还是调用SELECT来完成.
d, SELECT赋值时是滚动赋值(或许用词不科学），我来说明一下我的'滚动赋值'指的什么
即，当SELECT v FROM tb有多个结果时，
SELECT @v=v FROM tb 在产生结果集的过程中，每得到一条记录，
@v都被赋一次值，也就是说，语句会滚动结果集，每次都对@v赋值。
这样，也就产生了递规查询变量:"

SELECT @v=@v+v FROM tb

这样就解释了为什么您的语句得到的结果为z而不是a
try:

SELECT TOP 3 @v=v FROM tb --滚动到结果集的第三行,或者说，结果集中只有三行，取最后一行

--=**********************************************

(6)我要获得tb上新插入记录的标识值，为什么我的@@identity全局函数取的值不对?

--=**********************************************

IF object_id('ta') IS NOT NULL

	DROP TABLE ta

CREATE TABLE ta(id INT IDENTITY(1,1),b_id INT,logTime DATETIME NOT NULL DEFAULT GETDATE())

GO

CREATE TRIGGER t ON tb

FOR INSERT

AS

	INSERT ta (b_id) SELECT id FROM INSERTED

GO



INSERT tb SELECT 'dd'

SELECT @@identity  --为什么这里得到的不是5,而是1?

说明:
@@identity是个全局函数，返回当前会话所有作用域最后产生的标识值(执行SELECT @@identity之前的最后)
我们可以看到ta上有insert触发器 t,触发器里对ta进行了insert，所以这里得到的是ta中新增的标识值
建议使用 SCOPE_IDENTITY

INSERT tb SELECT 'ww'

SELECT SCOPE_IDENTITY() --这里得到6,因为它返回当前会话，当前作用域最后产生的标识值

--=**********************************************

(7)为什么我执行distinct去重复后没有效果?如下，我想得到v值不同的记录，也即结果集中每个v值只出现一次

--=**********************************************

INSERT tb SELECT 'a'

UNION ALL SELECT 'b'

SELECT * FROM tb  -- 8 rows

SELECT DISTINCT v,id FROM tb --8 rows

--=**********************************************

出现这个意外，您需要注意两点:
1,DISTINCT 有效范围是后面的字段列表,而非紧告其的第一个字段
2,为什么DISTINCT 会有这么个奇怪的特点？这与二维关系统有关
就说tb中v=a的记录id有1,7
DISTINCT v,id 如果仅对v有效，那么引id有1,7,在一行一列中如何填充数据？是用1还是用7?
您太难为它了，因为它不知道如何为您将id填充到结果集中.
其实这个问题就是常见的同组取一条，那么你需要指定多一个条件，
每组(v相同的记录为一组)取id最大的或最小的，那么这样的语句，写的很多，论坛上问的也很多了，我就不再累赘了。

先整到这，明天继续

P.S:帮你整理了一下排版,希望没有曲解你的意思哈(hery2002):)