sql server 知识积累（一）

最新推荐文章于 2021-10-15 13:46:13 发布

我家有奥特曼

最新推荐文章于 2021-10-15 13:46:13 发布

阅读量424

点赞数

分类专栏： sql 文章标签： sql server 数据库 table 存储 sql object

本文链接：https://blog.csdn.net/xyyz150/article/details/4962488

版权

sql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文主要是一些常用的系统命令，函数，表等，主要方便自己查看

当然sql server 联机丛书是最佳选择了

创建表

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 )
drop table [dbo].[temp]
GO

dbo.sysobjects 为系统表

查询是否存在"temp“表，存在删除

创建存储过程

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[temp]
GO

OBJECTPROPERTY ( id , property ) 返回当前数据库中对象的有关信息。

OBJECTPROPERTY(id, N'IsProcedure') 表示是否存在名字为"[dbo].[temp]“的存储过程

IsProcedure 是存储过程，返回1；不是，返回0

"temp"为存储过程的名字

参考：http://www.yesky.com/imagesnew/software/tsql/ts_oa-oz_708p.htm

创建数据库角色

DECLARE @ret int, @Error int
IF NOT EXISTS( SELECT 1 FROM [dbo].[sysusers] WHERE name=N'temp' and issqlrole=1 )
BEGIN

EXEC @ret = sp_addrole N'temp'

SELECT @Error = @@ERROR

IF @ret <> 0 or @Error <> 0
RAISERROR( '未能添加 "temp" 角色', 16, -1 )
END
GO

[dbo].[sysusers]为存储sql server用户，角色，windows用户，组的系统表

issqlrole ：该帐户是否为 SQL Server 角色

参考：http://www.yesky.com/imagesnew/software/tsql/ts_sys-u_76ur.htm

EXEC sp_addrole N'temp' 添加角色"temp"到[dbo].[sysusers]表

@ret表示返回值，0为成功，1为失败

参考：http://www.yesky.com/imagesnew/software/tsql/ts_sp_addp_33s5.htm

RAISERROR( '未能添加 "temp" 角色', 16, -1 )

返回用户定义的错误信息并设系统标志，记录发生错误。通过使用 RAISERROR 语句，客户端可以从 sysmessages 表中检索条目，或者使用用户指定的严重度和状态信息动态地生成一条消息。这条消息在定义后就作为服务器错误信息返回给客户端

"未能添加 "temp" 角色"为定义的错误信息，16为严重级别，用户可以用0-18，19 到 25 之间的严重级别只能由 sysadmin 固定服务器角色成员使用。-1：表示有关错误调用状态的信息，从 1 到 127 的任意整数。负值默认为 1。

参考：http://www.yesky.com/imagesnew/software/tsql/ts_ra-rz_5ooi.htm

建表时指定排序规则

CREATE TABLE temp

(

[id] [uniqueidentifier] not null,

[name] [nvarchar(20)] COLLATE SQL_Latin1_General_CP1_CI_AS null

)

COLLATE SQL_Latin1_General_CP1_CI_AS null 表示该列的将按照SQL_Latin1_General_CP1_CI_AS 规则来排序

COLLATE：用于数据库定义或列定义以定义排序规则，或应用于字符串表达式以应用排序规则投影。多用在建表时。

select * from ::fn_helpcollations() 可以得到sql server 支持的所有排序规则

但两个表连接查询时，若连接的字段在被创建时排序规则不一样，就会引发“无法解决 equal to 操作的排序规则冲突。”的错误

此时需要查询时再次指定其排序规则。

例：SELECT * FROM A AS a,B AS b WHERE a.name=b.name COLLATE Chinese_PRC_CI_AI_WS

建表时对主键的选择

数据库设计时主键的选择有三种：uniqueidentifier,identity,可标示的属性。

在建立表其有一字段为必填项，且在其生存周期内一般不可改变，这种情况一般用属性(字段)做主键。

在需要多个数据库之间，多个网段之间需要进行数据库的复制时，我们就需要在每一个唯一的标识来区别每一个单条记录，在没有合适的属性来做主键的情况下可以用Uniqueidentifier列来生成主键。(全球唯一性)

不需要数据库的复制，和系统比较小的情况下（50表以内）可以用 Identity列来生成主键　，适合于快速开发。

建立索引

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED ] INDEX index_name ON {table_name|view_name} (column_name)

UNIQUE 为表或视图建立唯一索引（即建立索引的列不能出现重复的值），视图上的聚集索引必须为UNIQUE索引。

CLUSTERED 创建聚集索引，其中行的物理排序与索引排序相同。先创建非聚集索引，后创建聚集索引会重建表中现有的非聚集索引。

NONCLUSTERED 创建非聚集索引，行的物理排序独立于索引排序

视图必须先创建聚集索引，才可以创建非聚集索引

参考：http://www.yesky.com/imagesnew/software/tsql/ts_create_64l4.htm

建立事务

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

declare @errorSum int

UPDATE table SET name='name' WHERE id=1;

@errorSum=@errorSum+@@error

if @errorSum>0

BEGIN

print '交易失败，回滚事务.'
ROOLBACK TRANSACTION
END

ELSE

BEGIN

print '交易成功，提交事务，写入硬盘，永久保存！'
COMMIT TRANSACTION

END

GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED：指定不能读取正在被其他事务修改但尚未提交的数据。该选项是 SQL Server 的默认设置。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED：指定可以读取正在被其他事务修改但尚未提交的数据。

---

READ COMMITTED 的行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置：

如果将 READ_COMMITTED_SNAPSHOT 设置为 OFF（默认设置），则数据库引擎会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。共享锁类型确定它将于何时释放。行锁在处理下一行之前释放。页锁在读取下一页时释放，表锁在语句完成时释放。

如果将 READ_COMMITTED_SNAPSHOT 设置为 ON，则数据库引擎会使用行版本控制为每个语句提供一个在事务上一致的数据快照，因为该数据在语句开始时就存在。不使用锁来防止其他事务更新数据。

当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时，您可以使用 READCOMMITTEDLOCK 表提示为 READ COMMITTED 隔离级别上运行的事务中的各语句请求共享锁，而不是行版本控制。

参考：http://msdn.microsoft.com/zh-cn/library/ms173763.aspx

SET NOCOUNT ON :执行sql后不返回操作所影响的行数