摘要:无法在Enterprise Manager或SqlServerManagementStudio中修改Id字段,如图所示:解决方案:SETIDENTITY_INSERTBingot1ONINSERTINTOBINGOT1VALUESSETIDENTY_INERTBingot1关闭-用完后,请确保关闭执行。您查看的数据如下:从LogDate开始,您可以看到Id=1的数据最后插入。要保留标识计数器,请使用DELETE。TRUNCATETABLE删除表中的所有行,而不记录单行删除操作。TRUNCATETABLE在功能上与没有WHERE子句的DELETE语句相同;然而,TRUNCATETABLE更快,使用更少的系统资源和事务日志资源——查看表的所有列信息。SELECT*FROMSysColumnsWHERidINSys.types存储物理存储类型。
一、 从主键说起
CREATETABLE BingoT1
(
id INTIDENTITY(1, 1),
NAMENVARCHAR(10),--姓名
IdentityNo VARCHAR(18),--身份证号码
LogDate DATETIMEDEFAULT(GETDATE()),--录入日期
PRIMARYKEY( id )--主键约束
)
Go
INSERTINTO BingoT1(NAME, identityNo )
VALUES('李斌','123456789012345')
INSERTINTO BingoT1(NAME, identityNo )
VALUES('张霖青','123456789012345678')
INSERTINTO BingoT1(NAME, identityNo )
VALUES('李菲','12345678901234500x')
INSERTINTO BingoT1(NAME, identityNo )
VALUES('戈晓娟','123456789012345')
Go
SELECT*FROM BingoT1
Go
我们创建示例表BingoT1并插入若干条数据,主键是一个自增字段。
问题一:误删了一条记录如何修复?
在企业管理器(sql 2000)或Sql Server Management Studio中无法修改Id字段,如图:
解决办法:
SETIDENTITY_INSERT bingot1 ON
INSERTINTO BingoT1(id,NAME, identityNo )
VALUES( 1,'李斌','123456789012345')
SETIDENTITY_INSERT bingot1 OFF--用完一定要关闭
执行后查看数据如下:
从LogDate的时间上可以看出Id=1的数据是最后插入的。
问题二:Delete掉所有的数据后,为什么Id不从1 开始?
DELETEFROM bingot1
Go
INSERTINTO BingoT1(NAME, identityNo )
VALUES('李斌','123456789012345')
INSERTINTO BingoT1(NAME, identityNo )
VALUES('张霖青','123456789012345678')
INSERTINTO BingoT1(NAME, identityNo )
VALUES('李菲','12345678901234500x')
INSERTINTO BingoT1(NAME, identityNo )
VALUES('戈晓娟','123456789012345')
Go
SELECT*FROM BingoT1
解决办法:
TRUNCATETABLE BingoT1
GO
如果表包含标识列,该列的计数器重置为该列定义的种子值。如果未定义种子,则使用默认值 1。若要保留标识计数器,请使用 DELETE。
TRUNCATE TABLE删除表中的所有行,而不记录单个行删除操作。TRUNCATE TABLE 在功能上与没有 WHERE 子句的 DELETE 语句相同;但是,TRUNCATE TABLE 速度更快,使用的系统资源和事务日志资源更少。
二、 SQL SERVER 的中枢神经--系统表
Ø SysObjects
存储数据库中创建的每个对象(例如约束、默认值、日志、规则以及存储过程)。
SELECT*FROM sysobjects WHERE xtype='U'
SELECT*FROM SysObjects WHERE xtype='D'
SELECT*FROM SysObjects WHERE xtype='PK'
执行后可以看到我们新创建的表BingoT1、表的默认约束DF__BingoT1__LogDate__07020F21(以DF开头)、表的主键PK__BingoT1__060DEAE8(以PK开头)。
Xtype标识对象类型。可以是下列对象类型中的一种:
C = CHECK 约束 D = 默认设置或 DEFAULT 约束
F = FOREIGN KEY 约束 L = 日志 FN = 标量函数 IF = 内联表函数
P = 存储过程 PK = PRIMARY KEY 约束(类型为 K) RF = 复制筛选器存储过程
S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 UQ = UNIQUE 约束(类型为 K)
V = 视图 X = 扩展存储过程
可以通过下面的语句查看和表相关的各种资源:
--查看和表相关的各种资源
SELECT*
FROMsysobjects
WHEREparent_obj IN(SELECT id
FROM sysobjects
WHEREname='BingoT1')
Ø SysColumns
存储表和视图中的列,并保存数据库中的存储过程的每个参数。可以使用如下语句查看表BINGOT1的所有列信息。
--查看表的所有列信息
SELECT*
FROMSysColumns
WHEREid IN(SELECT id
FROM sysobjects
WHEREname='BingoT1')
Ø Sys.types
存放物理存储类型。内容如下所示:SELECT*FROMsys.types
问题:系统表我很少用,有什么实际应用吗?
下面我们就用这3个系统表来写一个简单的代码生成器!
应用一,生成实体类属性:
SELECT'public virtual '
CASE t.name WHEN'int'THEN'int'ELSE'string'END' ' c.name ' { get; set; }'
FROMdbo.sysobjects AS o
INNERJOIN dbo.syscolumns AS c ON c.id = o.id
INNERJOIN dbo.systypes AS t ON c.xusertype = t.xusertype
WHEREo.type ='U'
AND o.name ='BingoT1'
输出结果如下:
public virtual int id{ get; set; }
public virtual string NAME{ get; set; }
public virtual string IdentityNo{ get; set; }
public virtual string LogDate{ get; set; }
应用二,生成列表页面和添加页面:
首先需要给每个字段添加一个扩展属性,其中参数@value赋值备注、@level1name赋值表名、@level2name赋值字段名。
EXECsys.sp_addextendedproperty @name=N'MS_Description',
@value=N'主键', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BingoT1', @level2type=N'COLUMN',@level2name=N'id'
EXECsys.sp_addextendedproperty @name=N'MS_Description',
@value=N'姓名', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BingoT1', @level2type=N'COLUMN',@level2name=N'NAME'
EXECsys.sp_addextendedproperty @name=N'MS_Description',
@value=N'身份证号码', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BingoT1', @level2type=N'COLUMN',@level2name=N'IdentityNo'
EXECsys.sp_addextendedproperty @name=N'MS_Description',
@value=N'录入日期', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BingoT1', @level2type=N'COLUMN',@level2name=N'LogDate'
然后就可以使用系统函数生成列表页面代码.
SELECT'<asp:BoundColumn DataField="' objname '" HeaderText="'
CAST(valueASNVARCHAR)'"'
CASECAST(valueASNVARCHAR)
WHEN'主键'THEN' Visible="False"'
ELSE''END'></asp:BoundColumn> '
FROMFN_LISTEXTENDEDPROPERTY(NULL,'user','dbo','table','BingoT1',
'column',DEFAULT)
结果如下(在查询分析器中以文本格式显示结果,快捷键Ctrl T):
<asp:BoundColumn DataField="id" HeaderText="主键" Visible="False">
</asp:BoundColumn>
<asp:BoundColumn DataField="NAME" HeaderText="姓名">
</asp:BoundColumn>
<asp:BoundColumn DataField="IdentityNo" HeaderText="身份证号">
</asp:BoundColumn>
<asp:BoundColumn DataField="LogDate" HeaderText="录入日期">
</asp:BoundColumn>
再生成添加页面代码,其中Char(13)代表回车符.
SELECT'<tr>'CHAR(13)
' <td style=" 30%;">'CAST(VALUEASNVARCHAR)':</td>'CHAR(13)
' <td style=" 70%;">'
'<asp:TextBox ID="txt' objname '" runat="server" style="220px"></asp:TextBox> <td>'CHAR(13)
'<tr> '
FROMFN_LISTEXTENDEDPROPERTY(NULL,'user','dbo','table','BingoT1',
'column',DEFAULT)
结果如下(在查询分析器中以文本格式显示结果,快捷键Ctrl T):
<tr>
<td style=" 30%;">主键:</td>
<td style=" 70%;"><asp:TextBox runat="server" style="220px"></asp:TextBox> <td>
<tr>
<tr>
<td style=" 30%;">姓名:</td>
<td style=" 70%;"><asp:TextBox runat="server" style="220px"></asp:TextBox> <td>
<tr>
<tr>
<td style=" 30%;">身份证号码:</td>
<td style=" 70%;"><asp:TextBox runat="server" style="220px"></asp:TextBox> <td>
<tr>
<tr>
<td style=" 30%;">录入日期:</td>
<td style=" 70%;"><asp:TextBox runat="server" style="220px"></asp:TextBox> <td>
<tr>
页面效果图如下所示: