以下内容仅代表本博客主人个人观点:

我以前用的是access数据库,里面有个字段id,为自动编号,现在导入到mssql中了,当添加记录的时候那个id字段怎么办啊,也就是怎么能让mssql中出现自动编号这种字段?谢了!

常见问题
  1,由于SQL2000里面没有"自动编号",所以你的以"自动编号"设置的字段都会变成非空的字段,这就必须手工修改这些字段,并把他的"标示"选择"是",种子为"1",增量为"1"

  (备注:此法可行!!!!)
  2,另外,ACCESS2000转换成SQL2000后,原来属性为"是/否"的字段将被转换成非空的"bit",这时候你必须修改成自己想要的属性了;
  3,另外,大家要注意对时间函数的把握.ACCESS与SQL是有很多不同的.

*******************************************************************

*******************************************************************

    MSSQL还是2000好,2005精简不好用!!!

    下面的为网摘,不知是否可行!

*******************************************************************

*******************************************************************
最近忙于将ACCESS的数据库转化为SQL SERVER的数据库的学习,从中摸索出几条经验,与大家分享.
2.转化时,跟日期有关的字段,SQL SERVER默认为smalldatetime型,我们最好将它变为datetime型,因为datetime型的范围比smalldatetime型大。我遇见这种情况,用smalldatetime型时,转化失败,而用datetime型时,转化成功。
3.对此两种数据库进行操作的sql语句不全相同,例如:在对ACCESS数据库进行删除纪录时用:"delete * from user where id=10",而对SQL SERVER数据库进行删除是用:"delete user where id=10".
4.日期函数不相同,在对ACCESS数据库处理中,可用date()、time()等函数,但对
SQL SERVER数据库处理中,只能用datediff,dateadd等函数,而不能用date()、time()等函数。
5.在对ACCESS数据库处理中,sql语句中直接可以用一些VB的函数,像cstr()函数,而对SQL SERVER数据库处理中,却不能用

 

 

*******************************************************************

SQLServer数据库自增长标识列的更新修改操作

       方法
       在日常的sql server开发中,经常会用到Identity类型的标识列作为一个表结构的自增长编号。比如文章编号、记录序号等等。自增长的标识列的引用很大程度上方便了数据库程序的开发,但是有时这个固执的字段类型也会带来一些麻烦。

一、修改标识列字段的值:(在执行insert时,将ID手动的设置成想要的值)
        有时,为了实现某个功能,需要修改类型为Identity自增长类型的字段的值,但由于标识的类型所限,这种操作默认是不允许的。比如,目前数据库有10条正常记录,引时删除5条,那么如果再添加数据时,自增长的标识列会自动赋值为11,可这时如果想在插入数据时给赋值6呢,默认是不允许的。如果说想改变主键标识符的值,完全由自已控制标识字段的插入,方法是有的,如下:
SET IDENTITY_INSERT [TABLE][ON|OFF]
使用上述语句,可以方便的控制某个表的某个自增长标识列是否自动增长,也就是说是否允许你在inert一条记录时手动指定主键标识列字段的值。

        如果指定为ON,则可以insert时指定主键标识字段的值,该值不自动增长。如果使用完毕,还需使用这个语句将开关关闭到默认状态OFF,不然下次insert数据时该字段还是不会自动增长,要做到有始有终嘛。只是每次插入时都要手动的设置一个主键值,示列如下:
set identity_insert  question_title on   //开启手动设置
insert into question_title (f_id,f_valid,f_questionTitle) values(6,5,'hello');  //手动的向数据库插入数据

二、重置标识列字段值:
        当数据记录被删除一部分后,后面再添加的新数据记录,标识列数值会有很大的空闲间隔,看上去不是很爽。即使你删除表中全部记录,identity标识列的值还是会无何止的自动增加变大,而不是从头开始增长。通过下面这条语句可以重置自增长字段的值:
DBCC CHECKIDENT(TABLE,[RESEED|NORESEED],[1])
        上述语句把指定表的ID值(种子值)强制重设为1。然而,如果你不想将ID重设为1,你可以用你想用的ID值(种子值)替代第三个参数。如果你想知道当前的ID值(种子值),而不想重设标识种子,这时你就要用NORESEED,而不用再去设置第三个参数。

DBCC checkident(question_title,reseed,1)

insert into question_title (f_valid,f_questionTitle) values(5,'hello');

        如果将表中记录都删除后,执行上面的一句话,这时ID的自增长值已被重设,再insert插入记录到数据库时,ID就可以从设置的1开始,以后每次插入的记录还是自动增长。
执行成功后提示如下:
--检查标识信息: 当前标识值 '9',当前列值 '1'。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

说明:以上显示数据库某表中当前ID最大值为9,当前设置的ID值为1。

      以上功能用得很少,我想也很少人知道吧。我上个星期帮一个朋友解决他做的一个网站的块排序问题,他问我能不能用update修改主键的值,他用的是主键自增长,我想这肯定是不可能的,如果想改的话就不要将主键设置为自增长,而是每次增加的时间手动插入。于是他给我看了一篇关于自增长的文章,我总结出设置为自增长的主键ID是不能被update的。

      但我发现一点如果删除数据库里的记录,再次向数据库插入数据时,ID还是从删除的那条记录后面开始记数的,ID能不能重新开始自增长呢,或是接原来的数据后面增长,比如我删除了50~100记录,我要再插入时ID为51而不是101,经过测试,是可以实现的,所以总结了上面的。

 


*******************************************************************
发现MSSQL导入数据时标识列存在的问题,已解决!

(2008/1/11已更新,附上解决方案,可以自动修复标识列的编码规则)
最近在合并异地SQL数据的时候,发现MSSQL的标识列在合并数据时,可以启用标识插入也可以不启用标识插入,两种方法都可能存在一些潜在的问题,最严重的是导入操作改变了目的表的标识列编号规则。

场景:A地的A表,B地有B表,数据结构除了标误列的种子不一样外其它完全一样:
A(ID IDENTITY (1, 2), Content varchar(50)) 生成奇数主键ID
B(ID IDENTITY (2, 2), Content varchar(50)) 生成偶数主键ID
--A地的A表
CREATE TABLE dbo.A
    (
    ID int NOT NULL IDENTITY (1, 2),
    Content varchar(50) NOT NULL
    ON [PRIMARY]
GO
ALTER TABLE dbo.A ADD CONSTRAINT
    PK_A PRIMARY KEY CLUSTERED
    (
    ID
    ) ON [PRIMARY]
--在A表中插入两条数据:预先插入A表两条数据
INSERT INTO [dbo].[A] ([Content]) VALUES ('A1') --生成1, 'A1'
INSERT INTO [dbo].[A] ([Content]) VALUES ('A3') --生成3, 'A3'

--B地的B表
CREATE TABLE dbo.B
    (
    ID int NOT NULL IDENTITY (2, 2),
    Content varchar(50) NOT NULL
    ON [PRIMARY]
GO
ALTER TABLE dbo.B ADD CONSTRAINT
    PK_B PRIMARY KEY CLUSTERED
    (
    ID
    ) ON [PRIMARY]

--在B表中插入1条数据:预先插入B表1表数据
INSERT INTO [dbo].[B] ([Content]) VALUES ('B2') --生成2, 'B2'
现在要将A表数据导入到B表,问题出现了:
1. 不启用标识插入的问题:
    不启用标识,B表会增加两条数据
        4,'A1'
        6,'A3'
    造成同一数据在两地数据库表中的不一致情况,以及无法判断或者找出哪些数据已合并,哪些数据已修改或已删除,哪些数据重复导入等等问题。
2. 启用标识插入的问题:
    启用标识后,B表会增加两条数据
        1,'A1'
        3,'A3'
    用户最不能接受的错误出现了,B表录入的新数据是
        5, '从A表导入两条数据后录入B表的新数据1'
        7, '从A表导入两条数据后录入B表的新数据2'
    导入合并操作改变了B表的标识列编号规则。


2008-1-11,今天终于搞定了,一个存储过程完成解决,自动修复标识列的编码规则。

上例中,调用dbo.sp_RepairIDENTITY 'B', 'ID' 即可,执行结果如下:
当前最大值:3
标识种子:2
标识增量:2
当前标识值:3
需设新标识值:4
检查标识信息: 当前标识值 '3',当前列值 '4'。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
结果:已修复标识,当前标识值重设为4

代码如下:
CREATE PROCEDURE dbo.sp_RepairIDENTITY
(
@Tablename nvarchar(255), --表名
@RowID_Name nvarchar(20) --标识列名
)
as
SET NOCOUNT ON
--Select @TableName=N'B',@RowID_Name=N'ID'

--获取最大标识值MaxID
Declare @sql nvarchar(4000), @MaxID bigint
Set @sql = N'Select @MaxID =Max(' + @RowID_Name + N') from ' + @TableName
Exec sp_Executesql @sql, N'@MaxID bigint output',@MaxID output
--print @MaxID

Declare @IDENT_CURRENT bigint, @NewIDentity bigint, @IDENT_SEED int, @IDENT_INCR int
Select @IDENT_SEED = IDENT_SEED(@TableName), --标识种子
@IDENT_INCR = IDENT_INCR(@TableName), --标识增量
@IDENT_CURRENT = IDENT_CURRENT(@TableName), --当前标识值
@NewIDentity = @MaxID - @MaxID % cast(IDENT_SEED(@TableName) as int) + IDENT_INCR(@TableName) --需要设置的新标识值

Print '当前最大值:' + Cast(@MaxID as nvarchar(20))
Print '标识种子:' + Cast(@IDENT_SEED as nvarchar(20))
Print '标识增量:' + Cast(@IDENT_INCR as nvarchar(20))
Print '当前标识值:' + Cast(@IDENT_CURRENT as nvarchar(20))
Print '需设新标识值:' + Cast(@NewIDentity as nvarchar(20))

------关键代码:启用标识导入数据或手工执行过DBCC CHECKIDENT('table_name',RESEED new_reseed_value)就会造成编码规则改变,所以要修复编码规则
if(@IDENT_CURRENT < @MaxID or @IDENT_CURRENT % @IDENT_SEED >0)
begin
    DBCC CHECKIDENT (@TableName, RESEED, @NewIDentity)
    print '结果:已修复标识,当前标识值重设为' + Cast(@NewIDentity as nvarchar(20))
end
else
begin
    Print '结果:不需要重设标识列。'
end

SET NOCOUNT OFF

 

 


*******************************************************************

mssql 如何更新标识列

 update book set bookid=46080 where bookid=72708
其实bookid为自增长列,执行报如下错误”无法更新标识列 'bookid'。“
通过设置identity_insert为on依然不行。
set identity_insert booklistinfo ON;


关闭原因:


 947 最佳答案
----允许对系统表进行更新
exec sp_configure 'allow updates',1
reconfigure with override
GO

----取消标识列标记
update syscolumns set colstat = 0 where id = object_id('tablename') and colstat = 1
GO

--插入id=8001-8003的行
...

----恢复标识列标记
update syscolumns set colstat = 1 where id = object_id('tablename') and name = '标识列名称'

----重新设置标识的起始值
DBCC CHECKIDENT (表名称, RESEED, 10003)

----禁止对系统表进行更新
exec sp_configure 'allow updates',0
reconfigure with override
GO
这种方法蛮繁锁的。

 

 

*******************************************************************
MSSQL ALTER TABLE 用语句修改Primary key 的ID


alter   table   p_w_picpath   alter   column   id     int   IDENTITY   (1,   1)   NOT   NULL    
     
  我只能上查询分析器,所以只能这么该,对马,系统提示错误,怎么该阿??  
 
================================================== 
不能直接修改为标识列:    
  可以先加新的标识列,再设置允许修改标识列,再用原来的字段值填充标识列,再删除原字段,再对字段改名    
     
  ALTER   TABLE   表   ADD   编号1   bigint   identity(1,1)   not   null    
  go    
  SET   IDENTITY_INSERT   表   ON    
  go    
  update   表   set   编号1=编号    
  go    
  SET   IDENTITY_INSERT   表   OFF    
  go    
  ALTER   TABLE   表   DROP   COLUMN   编号      
  go    
  exec   sp_rename   '表.编号1','编号'    
  go 
================================================== 
alter   table   [p_w_picpath]   alter   column   [id]     int   IDENTITY   (1,1)   NOT   NULL 
 
================================================== 
ALTER   TABLE   表   ADD   编号1   bigint   identity(1,1)   not   null    
  go    
  ALTER   TABLE   表   DROP   COLUMN   编号      
  go    
  exec   sp_rename   '表.编号1','编号'    
  go   
 
================================================== 

 

 

 

 

*******************************************************************

 

mssql 标识类指定值一般情况,我们想对自动增长列进行插入值是不可能的事情,但是有时就需要我们为标识列指定值。

在mssql 查询分析器中对标识列指定值的时候,就是报

当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'FilePath' 中的标识列插入显式值。

也就是说当把IDENTITY_INSERT 指定为on的时候就可以使用了, 去找一下IDENTITY_INSERT这个的用法

SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }

好了,先测试一下

建表

create table test (
   id int identity(1,1),
   name varchar(200)
);

然后指定

set IDENTITY_INSERT test on

insert into test(id) values(0);

插入成功。 (注意,当 IDENTITY_INSERT 为on的时候,要为要指定要插入的列,要不然不能正常插入的。

最后把IDENTITY_INSERT 关闭,不关闭的话,以后都要为id指定值了。

set IDENTITY_INSERT test off

 

原文转自:http://www.22its.net 参考资料:http://www.22tec.net