Sql server 实用技巧总结
一、 从主键说起
CREATE TABLE BingoT1
CREATE TABLE BingoT1
(
id INT IDENTITY(1, 1) ,
NAME NVARCHAR(10) , --姓名
IdentityNo VARCHAR(18) , --身份证号码
LogDate DATETIME
DEFAULT (
GETDATE()
) , --录入日期
PRIMARY KEY ( id )--主键约束
)
Go
INSERT INTO BingoT1( NAME, identityNo )
VALUES ( '李斌',
'123456789012345' )
INSERT INTO BingoT1( NAME, identityNo )
VALUES ( '张霖青',
'123456789012345678' )
INSERT INTO BingoT1( NAME, identityNo )
VALUES ( '李菲',
'12345678901234500x' )
INSERT INTO BingoT1( NAME, identityNo )
VALUES ( '戈晓娟',
'123456789012345' )
Go
SELECT * FROM BingoT1
Go
我们创建示例表BingoT1并插入若干条数据，主键是一个自增字段。
问题一：误删了一条记录如何修复？
在企业管理器（sql 2000）或Sql Server
Management Studio中无法修改Id字段，如图：
解决办法：
SET IDENTITY_INSERT bingot1 ON
INSERT INTO BingoT1(id, NAME, identityNo
)
VALUES ( 1, '李斌',
'123456789012345' )
SET IDENTITY_INSERT bingot1 OFF --用完一定要关闭
执行后查看数据如下：
从LogDate的时间上可以看出Id=1的数据是最后插入的。
问题二：Delete掉所有的数据后，为什么Id不从1
开始？
DELETE FROM bingot1
Go
INSERT INTO BingoT1( NAME, identityNo )
DELETE FROM bingot1
Go
INSERT INTO BingoT1( NAME, identityNo )
VALUES ( '李斌',
'123456789012345' )
INSERT INTO BingoT1( NAME, identityNo )
VALUES ( '张霖青',
'123456789012345678' )
INSERT INTO BingoT1( NAME, identityNo )
VALUES ( '李菲',
'12345678901234500x' )
INSERT INTO BingoT1( NAME, identityNo )
VALUES ( '戈晓娟',
'123456789012345' )
Go
SELECT * FROM BingoT1
Go
执行后查看数据如下：
解决办法：
TRUNCATE TABLE BingoT1
TRUNCATE TABLE 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标识对象类型。可以是下列对象类型中的一种：
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 *
FROM sysobjects
WHERE parent_obj IN ( SELECT id
FROM sysobjects
WHERE name = 'BingoT1' )
Ø SysColumns
存储表和视图中的列，并保存数据库中的存储过程的每个参数。可以使用如下语句查看表BINGOT1的所有列信息。
--查看表的所有列信息
--查看表的所有列信息
SELECT *
FROM SysColumns
WHERE id IN ( SELECT id
FROM sysobjects
WHERE name = 'BingoT1' )
Ø Sys.types
存放物理存储类型。内容如下所示：
SELECT
* FROM sys.types
问题：系统表我很少用，有什么实际应用吗？
下面我们就用这3个系统表来写一个简单的代码生成器！
应用一，生成实体类属性：
下面我们就用这3个系统表来写一个简单的代码生成器！
应用一，生成实体类属性：
SELECT 'public virtual '
CASE t.name WHEN 'int' THEN 'int' ELSE 'string' END ' ' c.name ' { get; set;
}'
FROM dbo.sysobjects
AS o
INNER JOIN dbo.syscolumns
AS c ON c.id = o.id
INNER JOIN dbo.systypes
AS t ON c.xusertype = t.xusertype
WHERE o.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赋值字段名。
EXEC
sys.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'
EXEC
sys.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'
EXEC
sys.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'
EXEC
sys.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(value AS NVARCHAR) '"'
CASE CAST(value AS NVARCHAR)
WHEN '主键' THEN '
Visible="False"'
ELSE
'' END '></asp:BoundColumn> '
FROM
FN_LISTEXTENDEDPROPERTY(NULL, 'user', 'dbo', 'table', 'BingoT1',
'column', DEFAULT)
结果如下（在查询分析器中以文本格式显示结果，快捷键Ctrl T）：
<asp:BoundColumn DataField="id" HeaderText="主键" Visible="False">
<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 class="add_tdr" style="width: 30%;">'
CAST(VALUE AS NVARCHAR) '：</td>' CHAR(13)
' <td class="add_tdl" style="width: 70%;">'
'<asp:TextBox ID="txt' objname '" runat="server" style="width:220px"></asp:TextBox>
<td>' CHAR(13)
'<tr> '
FROM FN_LISTEXTENDEDPROPERTY(NULL, 'user', 'dbo', 'table', 'BingoT1',
'column', DEFAULT)
结果如下（在查询分析器中以文本格式显示结果，快捷键Ctrl T）：
<tr>
<td class="add_tdr" style="width:
30%;">主键：</td>
<td class="add_tdl" style="width:
70%;"><asp:TextBox ID="txtid" runat="server"
style="width:220px"></asp:TextBox> <td>
<tr>
<tr>
<td class="add_tdr" style="width:
30%;">姓名：</td>
<td class="add_tdl" style="width:
70%;"><asp:TextBox ID="txtNAME" runat="server"
style="width:220px"></asp:TextBox> <td>
<tr>
<tr>
<td class="add_tdr" style="width:
30%;">身份证号码：</td>
<td class="add_tdl" style="width:
70%;"><asp:TextBox ID="txtIdentityNo" runat="server"
style="width:220px"></asp:TextBox> <td>
<tr>
<tr>
<td class="add_tdr" style="width:
30%;">录入日期：</td>
<td class="add_tdl" style="width:
70%;"><asp:TextBox ID="txtLogDate" runat="server"
style="width:220px"></asp:TextBox> <td>
<tr>
页面效果图如下所示：
