Microsoft SQL Server 一些简单的干货

组态软件,使用SqlServer做报表的一种方式

关注老董,学习更多电气自动化知识! 组态软件要往数据库里存数据,各个品牌的实现方法不同,但是其实实现的思路基本相同,而且所有组态软件都有这个功能!我就不讲怎么存的了,根据组态软件不同,各位自己去搜索如:“WINCC存数据到SqlServer”,”InTouch存数

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

【云技术在线】为您提供以下专业技术服务:

上云服务:上云架构设计,大流量高并发架构设计,混合云架构设计,上云迁移,云上容灾,跨国云访问加速等;

安全服务:等保咨询及实施,渗透测试,漏洞扫描,木马查杀,安全加固,应急响应,DDOS/CC防护,勒索病毒防护,安全托管等;

运维服务:Linux系统运维,数据恢复及备份,负载均衡集群搭建及维护,数据库集群搭建及维护,网站运行环境搭建及维护,系统性能调优,中间件性能调优,数据库性能调优,数据库读写分离,分库分表,性能(压力)测试,7*24小时监控及故障应急响应,故障排查,大流量高并发活动护航等。