Sqlserver中一直在用又经常被忽略的知识点一

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

已经有快2个月没有更新博客了,实在是因为最近发生了太多的事情,辞了工作,在湘雅医院待了一个多月,然后又新换了工作......

在平时的工作中,Sqlserver中许多知识点是经常用到的,但是有时候我们往往忽略了它们,在过去的一年里,一直使用的是Mysql,现在又开始接触Sqlserver了,所以就把一些常用又容易忽视的Sqlserver知识点总结一点,以便备忘之用。

所有的操作都将基于Northwind数据库来进行操作。

注意:当采用附加Northwind数据库的方式进行数据库迁移的时候,可能会出现错误:“Access is denied”。解决方案: right-click the SQL Server  Management Studio icon and select  Run as administrator。

SET NOCOUNT ON 介绍

在存储过程中,经常用到SET NOCOUNT ON;

作用:阻止在结果集中返回显示受T-SQL语句或则usp影响的行计数信息。
当SET NOCOUNT ON 时候,不返回计数,当SET NOCOUNT OFF时候,返回计数。

当SET NOCOUNT ON 时候,会更新@@RowCount,但是不向客户端发送存储过程每个语句的DONE_IN_proc消息。

如果存储过程中包含一些并不返回实际数据的语句,使用SET NOCOUNT ON时,网络通信流量便会大量减少,可以显著提高应用程序性能。

SET NOCOUNT 指定的设置只在执行或运行时候生效,分析时候不生效。

示例:

USE Northwind
GO
SET NOCOUNT OFF;
SELECT TOP 5 OrderDate FROM Orders
GO

执行结果如下:

USE Northwind
GO
SET NOCOUNT ON;
SELECT TOP 5 OrderDate FROM Orders
GO

执行结果如下:

Go 介绍

如果只是执行一条语句,有没有GO都一样。

如果多条语句之间用GO分隔开就不一样了。

每个被GO分隔的语句都是一个单独的事务,一个语句执行失败不会影响其它语句执行。

GO 不是 Transact-SQL 语句;而是可为  SQL Server 查询分析器识别的命令。

如果你的SQL过长的时候,就要写GO,或者有一些语句,它只能是第一句操作的,在之前你也得写 GO ,GO的意思是分批处理语句,有加这个 GO ,就执行GO 行的代码,执行后再执行接下来的代码。

像以下这种情况下就要用到GO ,以达到分批处理数据的目的,否则将会报错。

复制代码
IF EXISTS(SELECT 1 FROM sys.views WHERE name='View_OrderInfo')
DROP VIEW View_OrderInfo
create view View_OrderInfo
as
select c.ContactName,c.CompanyName,o.OrderDate,o.ShipName,o.OrderID,o.Freight from [Orders] o inner join Customers c on o.CustomerID=c.CustomerID
复制代码

会报错

必须是:

复制代码
IF EXISTS(SELECT 1 FROM sys.views WHERE name='View_OrderInfo')
DROP VIEW View_OrderInfo
GO
create view View_OrderInfo
as
select c.ContactName,c.CompanyName,o.OrderDate,o.ShipName,o.OrderID,o.Freight from [Orders] o inner join Customers c on o.CustomerID=c.CustomerID
复制代码

select count(*)  count(1) count(2) count('a') 之间的区别

count(*):找表中最短的列进行统计行数

count(1) count(2) count('a'):对常数列进行统计行数。它们的执行方式是一样的,没有任何区别。

很显然采用count(1) count(2) count('a')的方式,效率会更高,因为count(*)会先去算出最短的列,然后再去统计。虽然现在的Sqlserver查询分析器自动会帮我们做一些优化,但是我们必须知道它们的实现原理。

WITH (NOLOCK)

缺点:

  1.会产生脏读

  2.只适用于select查询语句

优点:

  1.有些文章说,加了WITH (NOLOCK)的SQL查询效率可以增加33%。

  2.可以用于inner join 或者left join等语句

脏读: 一个用户对一个资源做了修改,此时另外一个用户正好读取了这条被修改的记录,然后,第一个用户放弃修改,数据回到修改之前,这两个不同的结果就是脏读。

详细内容:

  要提升SQL的查询效能,一般来说大家首先会考虑建立索引(index)。其实除了index的建立之外,当我们在下SQL Command时,在语法中加一段WITH (NOLOCK)可以改善在线大量查询的环境中数据集被LOCK的现象藉此改善查询的效能。

不过有一点千万要注意的就是,WITH (NOLOCK)的SQL SELECT有可能会造成Dirty Read(脏读)。

例如:

SELECT o.OrderID,o.OrderDate,o.Freight,d.Quantity,d.UnitPrice
FROM [dbo].[Orders] o WITH (NOLOCK)
JOIN [dbo].[Order Details] d WITH (NOLOCK)
ON o.OrderID=d.OrderID

DELETE、INSERT、UPDATE这些需要transaction的指令就不能使用WITH (NOLOCK)。

加了WITH (NOLOCK)即告诉SQL Server,我们的这段SELECT指令无需去考虑目前table的transaction lock状态,因此效能上会有明显的提升,而且数据库系统的Lock现象会有明显的减少(包含Dead Lock)。

当使用NoLock时,它允许阅读那些已经修改但是还没有交易完成的数据。因此如果有需要考虑transaction事务数据的实时完整性时,使用WITH (NOLOCK)就要好好考虑一下。
如果不需考虑transaction,WITH (NOLOCK)或许是个好用的参考。

Sqlserver高效分页

在SQLserver2012之前,分页我们一般是使用ROW_NUMBER()。以获取第11条到第50条数据为例:

ROW_NUMBER()的实现方式:

select * from (
    select *, ROW_NUMBER() OVER(Order by a.OrderID DESC ) AS RowNumber from Orders as a
  ) as b
  where RowNumber BETWEEN 11 and 50 

SQLserver2012中OFFSET & FETCH实现方式:

SELECT o.* FROM dbo.Orders o
ORDER BY o.OrderID DESC OFFSET 10 ROWS FETCH NEXT 40 ROWS ONLY
OFFSET & FETCH实现方式 更加精简更加高效。
使用 OFFSET-FETCH 中的限制
  • ORDER BY 是使用 OFFSET 和 FETCH 子句所必需的。
  • OFFSET 子句必须与 FETCH 一起使用。永远不能使用 ORDER BY … FETCH。
  • TOP 不能在同一个查询表达式中与 OFFSET 和 FETCH 一起使用。
  • OFFSET/FETCH 行计数表达式可以是将返回整数值的任何算术、常量或参数表达式。该行计数表达式不支持标量子查询 View Code

自己写的一个存储过程例子

  View Code

demo2:分页存储过程

  View Code
本文转自邹琼俊博客园博客,原文链接:http://www.cnblogs.com/jiekzou/p/5988099.html,如需转载请自行联系原作者
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
11天前
|
SQL Oracle 关系型数据库
干货!sqlserver数据库所有知识点总结整理,含代码(挺全的)
干货!sqlserver数据库所有知识点总结整理,含代码(挺全的)
14 0
|
SQL Oracle 关系型数据库
干货!sqlserver数据库所有知识点总结整理,含代码(挺全的)
干货!sqlserver数据库所有知识点总结整理,含代码(挺全的)
162 0
|
11天前
|
SQL 安全 算法
【SQL server】玩转SQL server数据库:第四章 数据库安全性
【SQL server】玩转SQL server数据库:第四章 数据库安全性
75 12
|
11天前
|
SQL 数据库 数据库管理
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
73 11
|
11天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
122 6
|
3天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
23 2