SQLServer返回最后插⼊记录的⾃动编号ID
SQL Server 返回最后插⼊记录的⾃动编号ID
最近在开发项⽬的过程中遇到这么⼀个问题，就是在插⼊⼀条记录的后⽴即获取其在数据库中⾃增的ID，以便处理相关联的数据，怎么做？在sql server 2000中可以这样做，有⼏种⽅式。详细请看下⾯的讲解与对⽐。
⼀、要获取此ID，最简单的⽅法就是:(以下举⼀简单实⽤的例⼦)
--创建数据库和表
create database MyDataBase
use MyDataBase
create table mytable
(
id int identity(1,1),
name varchar(20)
)
--执⾏这个SQL，就能查出来刚插⼊记录对应的⾃增列的值
insert into mytable values('李四')
select @@identity
⼆、三种⽅式的⽐较
SQL Server 2000中，有三个⽐较类似的功能：他们分别是：SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY，它们都返回插⼊到 IDENTITY 列中的值。
IDENT_CURRENT 返回为任何会话和任何作⽤域中的特定表最后⽣成的标识值。IDENT_CURRENT 不受作⽤域和会话的限制，⽽受限于指定的表。IDENT_CURRENT 返回为任何会话和作⽤域中的特定表所⽣成的值。
@@IDENTITY 返回为当前会话的所有作⽤域中的任何表最后⽣成的标识值。
SCOPE_IDENTITY 返回为当前会话和当前作⽤域中的任何表最后⽣成的标识值
SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所⽣成的最后⼀个标识值。但是，SCOPE_IDENTITY 只返回插⼊到当前作⽤域中的值；@@IDENTITY 不受限于特定的作⽤域。
例如，有两个表 T1 和 T2，在 T1 上定义了⼀个 INSERT 触发器。当将某⾏插⼊ T1 时，触发器被激发，并在 T2 中插⼊⼀⾏。此例说明了两个作⽤域：⼀个是在 T1 上的插⼊，另⼀个是作为触发器的结果在 T2 上的插⼊。
假设 T1 和 T2 都有 IDENTITY 列，@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。
@@IDENTITY 返回插⼊到当前会话中任何作⽤域内的最后⼀个 IDENTITY 列值，该值是插⼊ T2 中的值。
SCOPE_IDENTITY() 返回插⼊ T1 中的 IDENTITY 值，该值是发⽣在相同作⽤域中的最后⼀个 INSERT。如果在作⽤域中发⽣插⼊语句到标识列之前唤醒调⽤ SCOPE_IDENTITY() 函数，则该函数将返回 NULL 值。
⽽IDENT_CURRENT('T1') 和 IDENT_CURRENT('T2') 返回的值分别是这两个表最后⾃增的值。
ajqc的实验:(40条本地线程,40+40条远程线程同时并发测试，插⼊1200W⾏)，得出的结论是:
1.在典型的级联应⽤中.不能⽤@@IDENTITY，在CII850,256M SD的机器上1W多⾏时就会并发冲突.在P4
2.8C,512M DDR上，才6000多⾏时就并发冲突.
2.SCOPE_IDENTITY()是绝对可靠的，可以⽤在存储过程中，连触发器也不⽤建，没并发冲突
SELECT  IDENT_CURRENT('TableName')  --返回指定表中⽣成的最后⼀个标⽰值
SELECT  IDENT_INCR('TableName')--返回指定表的标⽰字段增量值
SELECT  IDENT_SEED('TableName')--返回指定表的标⽰字段种⼦值
返回最后插⼊记录的⾃动编号
SELECT IDENT_CURRENT('TableName')
返回下⼀个⾃动编号:
SELECT  IDENT_CURRENT('TableName')  +  (SELECT  IDENT_INCR('TableName'))
SELECT @@IDENTITY --返回当前会话所有表中⽣成的最后⼀个标⽰值
geovindu
2009-1-8 12:18:54
0.把长⽇期转换为短⽇期  Convert(char(10),getdate(),120)
1.按姓⽒笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同
3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@sql)
4.查看硬盘分区:
p_fixeddrives
5.⽐较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc
8.如何修改数据库的名称:
sp_renamedb 'old_name', 'new_name'
9：获取当前数据库中的所有⽤户表
select Name from sysobjects where xtype='u' and status>=0
10：获取某⼀个表的所有字段
select name from syscolumns where id=object_id('表名')
11：查看与某⼀个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id like '%表名%'
12：查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
13：查询⽤户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid ＜> 0x01
14：查询某⼀个表的字段和数据类型
select column_name,data_type from lumns
where table_name = '表名'
[n].[标题]:
Select * From TableName Order By CustomerName
[n].[标题]:
来⾃
⼀、只复制⼀个表结构，不复制数据
select top 0 * into [t1] from [t2]
⼆、获取数据库中某个对象的创建脚本
1、先⽤下⾯的脚本创建⼀个函数
<div><div>if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0) drop function fgetscript
go
create function fgetscript(
@servername varchar(50)    --服务器名
,@userid varchar(50)='sa'    --⽤户名，如果为nt验证⽅式，则为空
,@password varchar(50)=''    --密码
,@databasename varchar(50)    --数据库名称
,@objectname varchar(250)    --对象名
) returns varchar(8000)
as
begin
declare @re varchar(8000)        --返回脚本
declare @srvid int,@dbsid int      --定义服务器、数据库集id
declare @dbid int,@tbid int        --数据库、表id
declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量
--创建sqldmo对象
exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
if @err<>0 goto lberr
--连接服务器
if isnull(@userid,'')='' --如果是 Nt验证⽅式
begin
exec @err=sp_oasetproperty @srvid,'loginsecure',1
if @err<>0 goto lberr
exec @err=sp_oamethod @srvid,'connect',null,@servername
end
else
exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password
if @err<>0 goto lberr
--获取数据库集
exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
if @err<>0 goto lberr
--获取要取得脚本的数据库id
exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
if @err<>0 goto lberr
--获取要取得脚本的对象id
exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
if @err<>0 goto lberr
--取得脚本
exec @err=sp_oamethod @tbid,'script',@re output
if @err<>0 goto lberr
--print @re
return(@re)
lberr:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4)
set @errb=cast(@err as varbinary(4))
p_varbintohexstr @errb,@re out
set @re='错误号: '+@re
+char(13)+'错误源: '+@src
+char(13)+'错误描述: '+@desc
return(@re)
end
go
</div></div>
2、⽤法如下
⽤法如下,
<div><div>print dbo.fgetscript('服务器名','⽤户名','密码','数据库名','表名或其它对象名')
</div></div>
3、如果要获取库⾥所有对象的脚本，如如下⽅式
<div><div>declare @name varchar(250)
declare #aa cursor for
select name from sysobjects where xtype not in('S','PK','D','X','L')
open #aa
fetch next from #aa into @name
while @@fetch_status=0
begin
print dbo.fgetscript('onlytiancai','sa','sa','database',@name)
fetch next from #aa into @name
end
close #aa
deallocate #aa
</div></div>
4、声明，此函数是csdn邹建邹⽼⼤提供的
三、分隔字符串
如果有⼀个⽤逗号分割开的字符串，⽐如说"a,b,c,d,1,2,3,4"，如何⽤t-sql获取这个字符串有⼏个元素，获取第⼏个元素的值是多少呢？因为
t-sql⾥没有split函数，也没有数组的概念，所以只能⾃⼰写⼏个函数了。
1、获取元素个数的函数
<div><div>create function getstrarrlength (@str varchar(8000))
returns int
as
begin
declare @int_return int
declare @start int
declare @next int
declare @location int
select @str =','+ @str +','
select @str=replace(@str,',,',',')
select @start =1
select @next =1
select @location = charindex(',',@str,@start)
while (@location <>0)
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
select @int_return = @next-2
return @int_return
end
字符串函数怎么获取</div></div>
2、获取指定索引的值的函数
<div><div>create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1 --如果习惯从0开始则select @next =0
select @location = charindex(',',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出，则认为逗号在字符串后
select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗号之后的位置或者就是初始值1
if (@index <> @next ) select @str_return = '' --如果⼆者不相等，则是因为逗号太少，或者@index⼩于@next的初始值1。
return @str_return
end
</div></div>
3、测试
<div><div>SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)
</div></div>
四、⼀条语句执⾏跨越若⼲个数据库
我要在⼀条语句⾥操作不同的服务器上的不同的数据库⾥的不同的表，怎么办呢?
第⼀种⽅法:
<div><div>select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').库名.dbo.表名
</div></div>
第⼆种⽅法:
先使⽤联结服务器:
<div><div>EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=⽤户;PWD=密码;'
exec sp_addlinkedsrvlogin  @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码' GO
</div></div>
然后你就可以如下：
<div><div>select * from 别名.库名.dbo.表名
insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
go
</div></div>
五、怎样获取⼀个表中所有的字段信息
蛙蛙推荐：怎样获取⼀个表中所有字段的信息
先创建⼀个视图
<div><div>Create view fielddesc
as
select o.name as table_name,c.name as field_name,t.name as type,c.length as
length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp
from syscolumns c
join systypes t pe = t.xusertype
join sysobjects o on o.id=c.id
left join    sysproperties p on p.lid and p.id=o.id
pe='U'
</div></div>
查询时:
<div><div>Select * from fielddesc where table_name = '你的表名'</div></div>
还有个更强的语句，是邹建写的，也写出来吧
<div><div>SELECT
(case lorder=1 then d.name else '' end) N'表名',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占⽤字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'⼩数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx
FROM  syscolumns  a left join systypes b
on  a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id lid = g.smallid
order by object_name(a.id),a.colorder

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。