2018年01月_LongRui888

原创 SQL Server 2017新增：trim函数（移除左右空格、指定字符）

SQL Server 2017新增了 trim函数，实现移除左右两侧的空格，以及删除左右两侧指定字符。语法：TRIM ( [ characters FROM ] string ) 默认参数是 string 字符串，前面的characters from 一起用，表示要从string字符串的左右两侧，去掉的字符。（1）移除左右的空格SELECT tri

2018-01-29 15:31:50 9508

原创 SQL Server 2017新增：string_agg函数（分组合并字符串）

SQL Server 2017新增了 string_agg函数，可以轻松实现分组合并字符串，而不是用xml path，或者写个自定义函数来实现。语法：STRING_AGG ( expression, separator ) [ ] ::= WITHIN GROUP ( ORDER BY [ ASC | DESC ] ) 有2个参数，第1个是要合

2018-01-29 15:10:11 17660 6

原创 SQL Server 2017新增：Translate 函数（实现批量替换）

SQL Server 2017新增 Translate 函数，可以实现批量替换。语法如下：TRANSLATE ( 输入字符串, 要替换的字符, 替换成的字符) 也就是把输入字符串中的要进行替换的字符，替换为其他字符。比如，现在有个字符串： 123#456*789!/0，要把其中的# * ！ / 替换掉，如果用replace函数来做：declare @v var

2018-01-29 14:39:56 3526 2

原创 Linux安装SQL Server 2017（连接篇SQL Server on linux）

安装篇：Linux下安装SQL Server 2017（安装篇）连接篇：Linux下安装SQL Server 2017（连接篇）连接数据库（1）下载客户端连接工具的源curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo（2）安

2018-01-29 14:05:58 6636

原创 Linux安装SQL Server 2017（安装篇SQL Server on linux）

之前写过，Linux下安装SQL Server 2016的文章如下：准备篇：Linux下安装SQL Server 2016（准备篇）安装篇：Linux下安装SQL Server 2016（安装篇）连接篇：Linux下安装SQL Server 2016（连接篇）2017年10月，发布了SQL Server 2017，下面会写如何在Linux下安装SQL Server 2017

2018-01-29 12:47:58 25605 13

原创 SQL Server 2016新增：string_split函数（拆分字符串）

SQL Server 2016新增了string_split函数，专门用来拆分字符串。之前，在开发中经常会有拆分字符串的需求，要么用xml来实现，要么写个自定义函数来实现。所以，期盼了很久，总算有这个函数了，必须要SQL Server开发组点个赞。希望以后还能有个string_join函数（名称类似python中的字符串合并函数join），专门用来合并字符串，而非用xml path，

2018-01-28 16:36:35 59937 7

原创改写优化SQL（6）：尽量少用标量子查询

前面几篇文章讲到了：标量子查询、聚合标量子查询、行转列标量子查询、带top的标量子查询如何转成left join。之所以要转换，主要是因为标量子查询虽然写法上比较直观，容易理解，不用想就知道怎么写，但是存在：代码重复、多次访问同一个表问题，所以效率比较低。一、标量子查询的模板按标量子查询方式，写出来的sql，都类似下面的代码：select tb.col1,

2018-01-25 12:29:47 2767

原创改写优化SQL（5）：带top的标量子查询改写

带top的标量子查询，是在标量子查询中，加上了top 1，因为如果不加top 1，关联后可能返回超过1条数据，就导致sql报错了。但是加了top 1，又没有排序，比如：去最早的一条，或者最晚的一条。这种写法，在语义上有点随意，因为top 1就是随机返回一条，这种写法不是很严谨，从语句的意思上，可以直接改为求max或者min。更进一步，就发现，这种写法，其实和上篇文章里的标量子查

2018-01-24 11:40:40 929

原创改写优化SQL（4）：行转列标量子查询改为left join

前面两篇文章写的是，如何把标量子查询、聚合标量子查询，转为 left join，标量子查询还可以实现行转列，但是同样的，销量比较差，建议转成 left join。（1）建表--学生表CREATE TABLE student( s# INT , sname NVARCHAR(32) , sage INT , ssex NVAR

2018-01-24 11:39:05 1520

原创改写优化SQL（3）：聚合标量子查询改为left join

聚合标量子查询，是标量子查询的升级版，因为不是简单的返回一个字段，而是要对字段求 sum，avg等，也就是标量子查询+聚合函数。但是这种写法，本质上还是标量子查询的写法，所以效率不高，建议改为left join方式。（1）建表CREATE TABLE tb_emp ( emp_id INT NOT NULL PRIMARY KEY CLUSTERED, emp_

2018-01-24 11:37:27 1651

原创改写优化SQL（2）：not in改为left join

在sql语句中，not in是经常会用到的一种写法，因为这种写法很直观，容易理解。但如果不注意的话，很容易写出错误的sql，而且性能存在严重问题，所以，不建议使用not in，要尽量把 not in写法，改为left join。一、建表CREATE TABLE tb_emp(emp_id INT NOT NULL PRIMARY KEY CLUSTERED,emp_name

2018-01-24 11:34:07 9409

原创改写优化SQL（1）：标量子查询改为left join

标量子查询就是在select中出现的子查询，效率相对来说比较低，建议修改为效率更高的left join。一、建表CREATE TABLE tb_emp(emp_id INT NOT NULL PRIMARY KEY CLUSTERED,emp_name VARCHAR(20) NOT NULL,tel VARCHAR(20) null)INSERT INTO dbo.tb

2018-01-24 11:33:27 3705 2

原创 SQL Server2016新特性（3）：把数据转成JSON格式

SQL Server 2016 针对导入和导出 JSON 以及处理 JSON 字符串添加了内置支持，本文主要讲如何把数据转成json格式。一、建表创建tb_emp、tb_org两个表，插入数据。CREATE TABLE tb_emp(emp_id INT NOT NULL PRIMARY KEY CLUSTERED,emp_name VARCHAR(20),)I

2018-01-24 10:25:54 4838 1

原创 SQL Server2016新特性（2）：时态表 Temporal Table

SQL Server 2016 引入了对版本由系统控制的临时表的支持，其附带的内置支持可以提供表中存储的数据在任意时间点的相关信息，而不仅仅是数据在当前时刻正确的信息。临时表是 ANSI SQL 2011 中引入的数据库功能。版本由系统控制的临时表是用户表的一种类型，旨在保留完整的数据更改历史记录，并实现轻松的时间点分析。这种类型的临时表之所以称为版本由系统控制的临时表，是因为每一行的有效

2018-01-24 10:25:48 2781

原创 SQL Server2016新特性（1）：动态的显示执行计划(效果类似小视频)

SQL Server2016种的一个新特性是可以动态的显示执行计划的过程，就像小视频一样的效果。一、准备工作在数据库有一个表tb，数据量在8000w左右，如果数据量少了，显示的动态过程会一闪而过。演示的例子很简单，就是要计算tb表的准确记录数，代码如下：SELECT COUNT(*) FROM tb之前版本ssms上有2个按钮：显示估计的执行计划、包括实际的

2018-01-24 10:25:20 1016

原创索引的访问方式：索引查找、索引扫描

索引的访问方式主要是索引查找、索引扫描。（1）索引查找在执行计划中为 index seek，适用于查找少量数据。对应随机IO，能快速的定位一条数据。（2）索引扫描在执行计划中为 index scan，适合扫描整个索引的数据。对应顺序IO，IO效率本身比较高。（3）效率对比索引查找和索引扫描，单从IO效率上来说，肯定是索引扫描的效率更高，因为顺序

2018-01-24 10:23:24 1836

原创 SQL Server的条件索引、include索引

SQL Server中创建索引时，有2个比较特殊的关键字： where 、include。（1）什么是条件索引呢？where顾名思义，就和sql语句中的where是一个意思，起到过滤作用。加上where子句，创建的索引就是条件索引，索引中只包含满足条件的数据，这样会使得索引的体积更小，不管是索引扫描，还是查找，需要访问的页数也就更少。举例：create in

2018-01-24 10:23:13 12093

原创 SQL Server查询提示（6）：优化器提示

优化器提示主要有以下几种：（1）fast number_rows这个提示，可以看作是优化器目标，做为一种指导方针，影响优化器的决策。比如：select * from A with(fast 100)就是告诉sql server的优化器，以最快的速度返回100行数据，以这个为目标，生成执行计划，所以，返回100行数据的速度会很快，但是对后面的数据来说，这个执行计划可能不

2018-01-23 13:28:44 719

原创 SQL Server查询提示（5）：分组、合并提示

分组、合并，这里主要是指sql语句中的 group by 和 union 操作。通过分组、合并提示，可以强制sql server优化器采用特定的分组、合并算法。（1）分组group by提示分组主要有 hash、order 两种算法。这里的hash算法，和关联提示中的hash算法，是相类似的，只不过这里通过hash算法不是用来join表，而是用来分组、去重数据的。or

2018-01-23 13:26:33 644

原创 SQL Server查询提示（4）：隔离级别提示

SQL Server中有4个隔离级别：读未提交、读已提交、可重复读、串行化，这4个都有对应的提示。此外，还支持 nowait，readpast。（1）隔离级别READUNCOMMITTED 或 nolock：读未提交，也叫脏读，可以读到正在被修改的数据。READCOMMITTED：读已提交，读到的都是已经提交的，确定的数据REPEATABLEREAD：可重复读，

2018-01-23 13:25:12 1122

原创 SQL Server查询提示（3）：锁提示

锁提示可以用来强制锁的粒度、锁的模式。（1）锁的粒度就是锁定行，还是页，还是表。ROWLOCK 行锁PAGLOCK 页锁TABLOCK 表锁（2）锁的模式就是独占锁，还是共享锁。TABLOCKX 独占表锁UPDLOCK 更新锁XLOCK 独占锁（3）组合用法注意，下面的例子都要在事务中，才有效，也就是要在语句的前面加上 be

2018-01-23 13:23:14 510

原创 SQL Server查询提示（2）：索引提示

索引提示一般用来强制查询使用特定索引，或者强制使用索引的方式。（1）使用特定的索引有时候，发现一个查询很慢，然后创建了一个索引，照理，查询应该用这个索引，但实际执行的时候，执行计划却没有用到这个索引，怎么办呢？首先，可以尝试更新下统计信息。其次，如果更新完统计信息后，还是没用到索引，这种情况下，可以考虑强制使用索引提示。使用方法：select * from A wh

2018-01-23 13:22:42 1124

原创 SQL Server查询提示（1）：关联提示

查询提示主要用来手动改变执行计划，这里要讲的关联提示，主要是用来指定2个表关联时，用何种关联算法。创建测试表：if object_id('t1') is not null drop table t1if object_id('t2') is not null drop table t2goselect * into t1from sys.objects select * i

2018-01-23 13:22:06 570

原创 sql执行的并行化

默认情况下，sql优化器在生产执行计划时，不会考虑生成并行的执行计划，只有当预估的执行开销超过了阀值，才会考虑生成并行执行计划。如果返回的数据量较大（几十万、百万），我们可以手动设置sql并行执行，原来是单线程执行这个sql，现在变成了多个线程来执行sql，速度会明显提高。（1）基本原理从优化思路上说，就是投入更多资源来做任务，而且和IT项目上有很多共同点。比如，公

2018-01-23 13:21:18 4257

原创如何更新统计信息

在更新统计信息之前，先说一下什么是统计信息，有什么作用。统计信息其实就是表中的记录数、字段个数、每行数据占用字节数、某个字段中每一种值的个数等。在一个sql通过语法检查、语义检查、权限检查后，sql优化器会用统计信息，来优化sql，生成多个执行计划，计算每个执行计划的cost（预估的），看哪个最小，就选择这个执行计划，接下来按照这个plan执行sql。所以，如果统计信息

2018-01-23 13:20:18 4586

原创索引的缺点（4）：过多的索引会降低性能

创建索引的目的是为了加快查询速度，但是任何事情都是过犹不及的。如果建的索引太多，反而会降低性能。（1）多少索引算多？先说个真实的事。前几年的时候，有一次写一个简单的sql，觉得查的很慢，想想是不是少了索引。于是打开ssms客户端，点开表下面的索引，发现这个表尽然有100多个索引，当时就懵了。。。这100多个索引，实在是太多了，同时也说明这个数据库没有DBA管理，很混

2018-01-23 13:18:37 25537

原创如何创建高效的索引

要让创建的索引真正有效，是需要一个过程的，不是系统上线之前调一下就行了，而是需要在系统运行过程中，不断调整。（1）考虑索引本身的特性在前面的文章中，已经说了，索引的3个特性：有序、索引层数不高、索引包含字段值。第一个特性是有序，可以用到sql中的分组操作（group by），排序操作（order by），关联（join）上。第二个特性是索引层数不高，说明当一个表的数据量越

2018-01-23 13:18:13 6903

原创索引的缺点（3）：降低增删改的性能

索引对于增删改操作的性能影响比较大。（1）insert操作insert的过程是，先把数据插入到表中，然后再把数据插入到相关索引中，如果这个表有5个索引，那么就得维护这5个索引，不管这个插入的数据是否为NULL值。所以，索引个数越多，对于insert操作来说，维护的成本就越大，插入一条数据的速度也就越慢。如果发现插入速度很慢，可以检查一下是否这个表的索引太多了。

2018-01-22 15:43:00 15179

原创索引的缺点（2）：创建索引有开销

索引的一个缺点是创建索引有开销。下面从创建索引的过程、对DML语句的影响两方面进行说明。（1）创建索引的过程创建索引，就是把表里的数据从磁盘读到内存，在内存中排序，在内存中构造B树的根、分支、叶子节点，最后写回到磁盘上。从这个过程来看，主要的开销是消耗一定的内存，还有消耗cpu来排序操作。当索引创建一定时间后，索引中会有很多碎片，这个时候，要对索引进行

2018-01-22 15:42:01 2556

原创索引的缺点（1）：需要占用磁盘空间

索引的其中一个缺点就是需要占用磁盘空间。这个缺点是其他优点的基础，因为任何一种机制，不可能只有优点，而没有缺点，正因为创建索引，就是把排序后组织成B树的数据，放到磁盘上，才有了索引，才有可能运用索引的各种优势。既然是索引，就一定会占用空间，那么这里的主要问题就成了如何减少占用的空间？（1）减少字段个数如果索引中包含了很多字段，比如：A表，一共有20个字段，而创建的索引中包

2018-01-22 15:41:23 12245

原创 SQL Server中的聚集索引、非聚集索引

SQL Server中按照数据的组织方式，可以分为：聚集索引、非聚集索引。共同点是，本质上都是B树索引。（1）聚集索引在默认的情况下，当在创建表时，指定了主键字段（primary key），那么sql server会自动给这个字段创建聚集索引。聚集索引的特点是，包含了索引 + 数据。也就是说在这个B树索引中，根节点、分支节点都是存的索引，而到了叶子节点，存的是数据。

2018-01-22 15:40:39 825

原创索引优点（5）：加快表连接的速度（对nested loop关联算法的影响）

索引的第5个优点是可以加快表连接的速度。要说明这个问题，首先要降到表连接的3种算法，分别是：hash，nested loop，merge ，这里主要讲hash，nested loop这2种算法。现在有A、B两个表，A表数据量1000w，B表数据2000w，关联字段是ID。对nested loop算法的影响（1）无其他过滤条件，select A.*,B.*nested loop算法实现两个表join...

2018-01-22 15:38:06 2389

原创索引优点（4）：降低分组、排序时间

索引的一个优点是可以降低分组、排序的时间。先说对排序的影响。（1）排序说对排序的影响，就不得不说一下索引本身的特点，就是索引是对字段进行排序的，语句如下：select ... from A order by ID同时，我们对A表的ID字段创建了索引，那么在执行sql过程中，可以直接从索引获取ID的值，而且这个值是已经排过序的，就省了排序的时间，速度就能提高。

2018-01-22 15:37:21 1947

原创索引优点（3）：加快表连接的速度（对hash关联算法的影响）

索引的第3个优点是可以加快表连接的速度。要说明这个问题，首先要降到表连接的3种算法，分别是：hash，nested loop，merge ，这里主要讲hash，nested loop这2种算法。现在有A、B两个表，A表数据量1000w，B表数据2000w，关联字段是ID。对hash算法的影响（1）无其他过滤条件，select A.*,B.*hash算法实现两个表join时，

2018-01-22 15:36:37 4375 1

原创索引优点（2）：索引是如何加快查询速度？

索引优点中，最重要的就是加快查询速度。索引有如下3个特点，这3点虽然看上去，句句都是废话，但仔细想想就发现，这3个特点包含的东西，远远超过了3句话所涵盖的。（1）索引有序创建索引后，之所以查询速度会快，是由于创建索引时，会按照字段进行排序，如果有多个字段，那么会按照多个字段逐个排序。在查找的时候，可以用类似二分查找的算法，快速判断要找的值在哪个B树的分支。（2）索引高度

2018-01-22 15:35:51 2709

原创索引优点（1）：保证数据唯一性

索引的第一个优点是保证数据的唯一性。（1）准确来说，要保证数据的唯一性，这里特指主键索引、唯一索引。这2种是比较特别的索引，可以保证数据的唯一性。主键索引，其实就是在建表时，指定主键，可以是1个字段，也可以是多个字段的组合，但不管怎样，都不能重复，而且也不能是NULL。唯一索引，和主键索引很相似，区别在于唯一索引的字段，可以包含NULL，由于NULL （

2018-01-22 15:34:43 4421

原创快速优化SQL的5个步骤（SQL Server）

优化sql的总体思路，就是要减少、降低sql的IO访问、逻辑读、物理读。具体落实下来，大致有5个步骤：1、索引如果返回的数据量相对较小（一般不超过10万条数据）通过索引，可以让sql在执行时，只访问需要的数据，减少表扫描，效果非常好，有时性能可以提高千倍，而且相对后面的方法来说，修改成本是比较低的。 2、更新统计信息如果索引已经有

2018-01-22 15:17:34 611

SQL Server探索