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