Microsoft SQL Server 一些简单的干货

1、创建视图 ：

CREATE VIEW [schema].[view_name] //[schema].[view_name] 视图的名称

--WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA

AS

///视图需要执行的查询语句

-- WITH CHECK OPTION

GO

 

2、创建索引

CREATE NONCLUSTERED INDEX index_name //NONCLUSTERED 可选参数 UNIQUE-唯一索引

ON [schema].[owner_name] ( column_name ) // [schema].[owner_name] 数据库表 //column_name 要创建索引的列名

--WITH PAD_INDEX

-- | FILLFACTOR = fillfactor

-- | IGNORE_DUP_KEY

-- | DROP_EXISTING

-- | STATISTICS_NORECOMPUTE

-- | SORT_IN_TEMPDB, .. as required

-- ON filegroup

GO

 

3、查询数据库所有的序列

SELECT * FROM sys.sequences

4、将当前序列的值初始化到我需要的值

SELECT

NEXT VALUE FOR dbo.S_住院_床位信息表_床位ID

GO 61

 

 

 

5、创建索引

CREATE NONCLUSTERED INDEX index_company

ON dbo.Company (ognName,parentId,sort )

--WITH PAD_INDEX

-- | FILLFACTOR = fillfactor

-- | IGNORE_DUP_KEY

-- | DROP_EXISTING

-- | STATISTICS_NORECOMPUTE

-- | SORT_IN_TEMPDB, .. as required

-- ON filegroup

GO

 

CREATE NONCLUSTERED INDEX index_department

ON dbo.Department( ognName,parentId,sort,head,c_head,branched )

--WITH PAD_INDEX

-- | FILLFACTOR = fillfactor

-- | IGNORE_DUP_KEY

-- | DROP_EXISTING

-- | STATISTICS_NORECOMPUTE

-- | SORT_IN_TEMPDB, .. as required

-- ON filegroup

GO

CREATE NONCLUSTERED INDEX index_user

ON dbo.[User] (account,password,sort,name,sex )

--WITH PAD_INDEX

-- | FILLFACTOR = fillfactor

-- | IGNORE_DUP_KEY

-- | DROP_EXISTING

-- | STATISTICS_NORECOMPUTE

-- | SORT_IN_TEMPDB, .. as required

-- ON filegroup

GO

CREATE NONCLUSTERED INDEX index_userKey

ON dbo.UserKey ( userId,ognId )

--WITH PAD_INDEX

-- | FILLFACTOR = fillfactor

-- | IGNORE_DUP_KEY

-- | DROP_EXISTING

-- | STATISTICS_NORECOMPUTE

-- | SORT_IN_TEMPDB, .. as required

-- ON filegroup

GO

 

6、创建触发器

---单位触发器

CREATE TRIGGER trigger_Upate_Company

ON dbo.Company

AFTER UPDATE

AS

BEGIN

IF (SELECT enable FROM Deleted)=0

BEGIN

UPDATE dbo.Department SET enable=0 WHERE parentId IN(SELECT Deleted.ognId FROM Deleted)

END

END

GO

--部门触发器

CREATE TRIGGER trigger_Upate_Department

ON dbo.Department

AFTER UPDATE

AS

BEGIN

IF (SELECT enable FROM Deleted)=0

BEGIN

UPDATE dbo.UserKey SET enable=0 WHERE ognId IN(SELECT Deleted.ognId FROM Deleted)

END

END

 

GO

 

---部门和用户的中间件触发器

CREATE TRIGGER trigger_Upate_UserKey

ON dbo.UserKey

AFTER UPDATE

AS

BEGIN

IF (SELECT enable FROM Deleted)=0

BEGIN

IF(SELECT COUNT(userId) FROM dbo.UserKey WHERE userId IN(SELECT Deleted.userId FROM Deleted))=1

BEGIN

UPDATE dbo.[User] SET enable=0 WHERE userId IN(SELECT Deleted.userId FROM Deleted)

END

END

END

 

GO