2019年01月_LongRui888

原创 Power BI中row函数+union函数实现SQL Server中的虚拟表

Power BI是微软开发的一款功能非常强大的的BI工具，可以制作非常美观的报表，但这个工具的核心是DAX，翻译为中文就是数据分析表达式，有点类似于excel函数，但又很不一样。DAX中的很多功能可以与SQL对应，但差异也很大。本文要讲的是用 row函数+union函数实现SQL Server中的虚拟表。 1、sql server中的虚拟表不是一张真实存在表，是只存在于当前...

2019-01-31 15:22:55 3592 1

原创 Power BI中表构造函数（Table Constructor）实现SQL Server中的虚拟表

Power BI是微软开发的一款功能非常强大的的BI工具，可以制作非常美观的报表，但这个工具的核心是DAX，翻译为中文就是数据分析表达式，有点类似于excel函数，但又很不一样。DAX中的很多功能可以与SQL对应，但差异也很大。本文要讲的是用表构造函数（Table Constructor）实现 sql server中的虚拟表。 1、sql server中的虚拟表不是一张真...

2019-01-31 15:02:50 1499

原创 Power BI中datatable函数实现SQL Server中的虚拟表

Power BI是微软开发的一款功能非常强大的的BI工具，可以制作非常美观的报表，但这个工具的核心是DAX，翻译为中文就是数据分析表达式，有点类似于excel函数，但又很不一样。DAX中的很多功能可以与SQL对应，但差异也很大。本文要讲的是用 datatable函数实现 sql server中的虚拟表。 1、sql server中的虚拟表不是一张真实存在表，是只存在于...

2019-01-31 14:32:52 2539

原创 Power BI中calculatetable函数实现SQL Server中的where

Power BI是微软开发的一款功能非常强大的的BI工具，可以制作非常美观的报表，但这个工具的核心是DAX，翻译为中文就是数据分析表达式，有点类似于excel函数，但又很不一样。DAX中的很多功能可以与SQL对应，但差异也很大。下载Power BI 以及输入实验数据，请参考前一篇文章： Power BI中selectcolumns函数+related函数实现SQL Server中...

2019-01-31 13:14:43 3348

原创 Power BI中summarizecolumns函数、sum函数实现SQL Server中的group by分组汇总

Power BI是微软开发的一款功能非常强大的的BI工具，可以制作非常美观的报表，但这个工具的核心是DAX，翻译为中文就是数据分析表达式，有点类似于excel函数，但又很不一样。DAX中的很多功能可以与SQL对应，但差异也很大。下载Power BI 以及输入实验数据，请参考前一篇文章： Power BI中selectcolumns函数+related函数实现SQL Server中...

2019-01-31 11:43:43 12900 1

原创 Power BI中filter函数实现SQL Server中的where

Power BI是微软开发的一款功能非常强大的的BI工具，可以制作非常美观的报表，但这个工具的核心是DAX，翻译为中文就是数据分析表达式，有点类似于excel函数，但又很不一样。DAX中的很多功能可以与SQL对应，但差异也很大。上篇文章中讲到用power bi实现sql中的join，代码如下：SELECTCOLUMNS('销售表', "产品编...

2019-01-31 10:55:47 4390

原创 Power BI中selectcolumns函数+related函数实现SQL Server中的join

Power BI是微软开发的一款功能非常强大的的BI工具，可以制作非常美观的报表，但这个工具的核心是DAX，翻译为中文就是数据分析表达式，有点类似于excel函数，但又很不一样。DAX中的很多功能可以与SQL对应，但差异也很大。下面就来讲一讲用Power BI中DAX的selectcolumns函数+related函数，实现SQL中的join表关联。1、下载power ...

2019-01-31 10:00:28 7855

原创 SQL Server系统函数：类型转换函数

1.基本的转化SELECT CAST(2008 as varchar(4)) + ' year!'SELECT CONVERT(varchar(4),2008) + ' year!'2、把日期转化为文本SELECT CONVERT(VARCHAR(30),GETDATE(),120) --年-月-日时:分:秒(24h)SELECT CONVERT(VARCHA...

2019-01-31 10:00:16 6044

原创 SQL Server系统函数：元数据函数

1、列的长度、列名--列的长度select COL_LENGTH('dbo.wct', --表名 'wcid') --列名--列名select COL_NAME(object_id('dbo.wct'), --表ID 1) --列IDcolumnPrope...

2019-01-31 10:00:07 671

原创 SQL Server系统函数：日期函数

1、返回当前日期和时间select GETDATE() '当前日期-精确到33毫秒'select GETUTCDATE() 'UTC日期和时间-精确到33毫秒'select SYSDATETIME() '当前日期和时间-精确到100纳秒(高精度)' select SYSUTCDATETIME() 'UTC-精确到100纳秒(...

2019-01-31 09:59:58 1391

原创 SQL Server系统函数：字符串函数

1、字符转化为ASCII,把ASCII转化为字符,注意返回的值是十进制数select ASCII('A'),ASCII('B'),ASCII('a'),ASCII('b'),ASCII('?')select CHAR(65),CHAR(66),CHAR(97),CHAR(98),CHAR(63)2、unicode字符转化为整数,把整数转化为unicode字符select...

2019-01-30 14:54:01 772

原创 SQL Server系统函数：系统信息函数

1、会话id，服务器信息、用户信息select @@SPID, --返回当前连接的会话ID:SPID @@servername, --SQL Server实例名称 @@sevicename, --SQL Server版本信息 APP_NAME(), --客户端连接的应用程序名称 HOST_ID(), ...

2019-01-30 14:40:58 782

原创数据仓库之抽取数据：通过openrowset执行存储过程

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。目标数据库是sql server，通过openrowset函数调用存储过程，但是存储过程中不能带参数。 1、开启即席查询--修改高级参数sp_configure 'show adv...

2019-01-30 14:16:59 1470

原创数据仓库之抽取数据：openrowset函数带bulk操作符的用法

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。目标数据库是sql server，通过openrowset函数带bulk操作符的用法，导入、导出、更新数据。带bulk操作符的方式相对于其他方式来说，速度更快。使用方法：第1种用法：...

2019-01-30 14:11:47 2076

原创数据仓库之抽取数据：通过bcp命令行导入数据

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。目标数据库是sql server，通过bcp命令行导入数据。bcp方式相对于其他方式来说，速度更快，是做了优化的。以下为bcp命令行常用的参数，注意大小写： -c 以char作为存储类...

2019-01-30 14:05:07 887

原创数据仓库之抽取数据：通过链接服务器获取Access中的数据

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。目标数据库是sql server，现在要从 Access中抽取数据。1、开启即席查询--修改高级参数sp_configure 'show advanced options',1go...

2019-01-30 13:58:01 718

原创数据仓库之抽取数据：通过链接服务器获取txt、csv中的数据

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。目标数据库是sql server，现在要从txt、csv文件中抽取数据。1、开启即席查询--修改高级参数sp_configure 'show advanced options',1g...

2019-01-30 13:54:26 1148

原创数据仓库之抽取数据：通过链接服务器获取Excel中的数据

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。目标数据库是sql server，现在要从Excel文件中抽取数据。但在实际操作中，需要注意以下几点：（1）很难从显示的错来知道：openrowset,opendatasource这两个函...

2019-01-30 13:43:27 1013

原创数据仓库之抽取数据：通过链接服务器获取Oracle数据

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。目标数据库是sql server，现在要从异构的Oracle数据库中抽取数据。步骤如下： 1、开启即席查询--修改高级参数sp_configure 'show advanced...

2019-01-30 13:34:38 1513

原创数据仓库之抽取数据：通过链接服务器获取SQL Server数据

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。目标数据库是sql server，现在要从同为sql server的数据源，抽取数据。步骤如下： 1、开启即席查询--修改高级参数sp_configure 'show adva...

2019-01-30 13:26:17 1749

原创 SQL优化中的重要概念：死锁

上面几篇文章讲到事务、锁定、阻塞，最后还有一种比较极端的情况，就是死锁，这也是锁定、阻塞的一种情况。死锁是当两个事务分别锁定了资源，而又继续请求对方已获取的资源，那么就会产生死锁。发生死锁的原因：A、会话以不同的顺序访问表。B、会话长时间运行事务，在一个事务中更新了很多表或行，这样增加了冲突的可能。C、会话1申请了一些行锁，会话2申请了一些行锁，之后决定将其升级为表锁。 ...

2019-01-30 12:31:38 1001

原创 SQL优化中的重要概念：阻塞

上一篇讲到锁定的概念，那么接下来就是如何找到由于锁定而发生阻塞的进程，并解决阻塞问题。1、会话1，修改数据，但没有提交事务BEGIN TRAN select @@SPID --输出：287UPDATE t SET v = '88888'WHERE idd = 12、会话2，由于会话一事务没有提交，导致阻塞BEGIN TRAN select @@SP...

2019-01-30 12:26:23 487

原创 SQL优化中的重要概念：锁定

上篇文章讲的是事务，这篇就引出另一个重要概念，就是锁定。当一个用户要读取另一个用户正在修改的数据，或者一个用户正在修改另一个用户正在读取的数据，或者一个用户要修改另一个用户正在修改的数据，就会出现并发问题。锁定能防止并发问题。资源的锁定方式称为锁定模式，SQL Server中的锁定模式：共享锁，意向锁，更新锁，排他锁，架构稳定锁，架构修改锁，大批量更新锁，键范围锁。不是所有锁模式都是...

2019-01-30 12:16:19 1190

原创 SQL优化中的重要概念：事务

sql 优化和事务有关系？从表面上看，让sql跑的更快，似乎和事务这个概念没什么联系，但是关系数据库中最重要的2个概念就是关系、事务。关系，对应到sql中，是通过主外键以及join 来实现的，当然，没有主外键，照样能关联表。事务，是数据库提供的，特别是在高并发的情况下，保障数据一致的一种机制。但实际上，当一个会话在修改数据，而另一个会话又要读取数据时，事务就自动发挥作用了...

2019-01-30 12:10:59 497

原创 SQL优化之监控篇：找到IO争用

上一篇讲到如何从实例级别，知道最大的瓶颈在哪儿。一般情况下数据库的瓶颈很有可能在 IO上，因为当数据库比较大的时候，内存没办法缓存下所有数据，所以，总是需要从磁盘读取数据到内存，在从内存访问数据进行计算的，而磁盘的访问速度和内存的访问速度相差千倍，就会拖慢整个处理速度。可以通过sys.dm_io_virtual_file_stats 函数，获取到数据库-文件级别的性能统计信...

2019-01-30 11:49:06 1154

原创 SQL优化之监控篇：查看系统最大瓶颈

有时候我们需要从sql server的实例级别，了解等待类型及其等待的时间，从而指导系统的最大瓶颈在哪儿。我们可以通过视图 SYS.DM_OS_WAIT_STATS来获取到这些信息，视图中的字段定义：列名数据类型说明 wait_type nvarchar(60) 等待类型的名称。 waitin...

2019-01-30 11:40:26 622

原创 SQL优化之监控篇：查看缓存的执行计划的性能统计信息

上篇文章讲到，捕获正在运行的sql，以及查看实际执行计划和运行时信息，而本文要讲的是，如何抓取到之前一个时间段内，sql的运行时信息。因为有问题的sql可能已经运行完了（比如，有个sql从早上6点开始运行，到7点运行结束，而现在已经是早上10点了），没办法获取到这个sql，而sql server会把之前运行过的sql的信息记录下来，所以，我们可以从缓存中找到这些历史的sql。下...

2019-01-30 11:31:44 879

原创 SQL优化之监控篇：查看实际的执行计划、运行时信息

上一篇讲了查看估计的执行计划，这一篇要讲如何查看实际的执行计划，以及在分析和编译、执行sql时，花了多久时间。另外，我们还能知道一个sql中，引用到的各个表的扫描次数、逻辑读次数、物理读次数，这些对于sql优化是非常重要的。 1、显示实际执行计划、运行时cpu、io的开销--显示实际的执行计划及统计信息、各种操作SET STATISTICS PROFILE ON --...

2019-01-30 11:20:59 1900

原创 SQL优化之监控篇：查看估计的执行计划

上一篇文章讲了，如何捕获到正在执行的sql，拿到sql文本后，就可以看一下sql的估计执行计划，注意，只是估计，不是实际的执行计划。查看执行计划是优化sql的第一步。 1、显示估计的执行计划注意，下面列出了3种方式，在实际使用中需要根据需求，选择一种就可以了。SET SHOWPLAN_TEXT ON --显示估计的执行计划SET SHOWPLAN_ALL ON ...

2019-01-30 11:11:32 2244

原创 SQL优化之监控篇：捕获正在执行的查询

sql监控是优化的第一步，能帮助我们找到执行有问题的sql语句，再进行有针对性的优化。SYS.DM_EXEC_REQUESTS视图中包含了正在执行的查询。SYS.DM_EXEC_SQL_TEXT试图中包含了sql对应的文本，也就是具体的sql语句，方便我们进行优化。代码如下：select r.session_id, --会话id r.status, ...

2019-01-30 11:00:14 2798

原创 SQL优化：统计信息的更新时会锁表

在更新某个表的统计信息时，会导致阻塞问题吗？通过实验发现（这里省略实现过程），在更新某个表的统计信息时，会对表加上X锁，这个肯定会导致阻塞问题： ...

2019-01-17 13:26:30 2071

原创 SQL优化：统计信息的更新时间

通过视图，可以查询到某个表的某个统计信息是什么时候更新过的，从而可以判断统计信息在数据大量变化时，SQL Server是否自动更新了统计信息。如果没有自动更新，就可以手动出发统计信息。代码如下：可以先查看某个表有多少统计信息：sp_helpstats '表名'然后在查更新时间：select stats_date(s.object_id,s.stats_id) [统计信息...

2019-01-17 13:23:12 1689

原创 SQL优化：统计信息的批量创建、更新

在查询执行时间很重要并且不能等待查询优化器以生成单列统计信息时，sp_createstats对于基准确定之类的应用程序十分有用。 1、sp_createstatssp_createstats的参数包括：参数值默认都是NO.@indexonly：仅对位于现有索引中并且不是任何索引定义中的第一列的列，创建统计信息@fullscan：将CREATE STATISTICS语句与FU...

2019-01-17 13:16:23 652

原创 SQL优化：统计信息的显示

显示表或索引视图的当前查询优化统计信息对于表，统计信息是根据索引或表列的列表创建的。统计信息包含一个带有统计信息的相关元数据的标题、一个用于度量各列之间的相关性的密度向量、一个带有统计信息第一个键列中的值的分布的直方图。DBCC SHOW_STATISTICS 根据统计信息对象中存储的数据显示标题、直方图和密度向量。使用以下语法，您可以指定表或索引视图以及target(目标索引名称、统...

2019-01-17 13:06:41 610

原创 SQL优化：统计信息的更新、删除

一、更新表或索引视图的统计信息 1、更新表中为某些列建立的统计信息update statistics txt(txtStats) update statistics txt(txtStats)with sample 50 percent update statistics txt(txtStatsRow)with resample, --使用最近的采样...

2019-01-17 13:03:43 3466

原创 SQL优化：统计信息的创建

统计信息的作用：查询优化器使用统计信息来估计查询结果中的基数或行数，查询优化器就可以创建高质量的查询计划。数据库引擎可以使用统计信息中的任何数据计算基数估计。例如，查询优化器可以使用基数估计，在查询计划中选择索引查找运算符而不是索引扫描运算符，避免消耗大量资源的索引扫描，提高查询性能。调用CREATE STATISTICS语句，可以对不是统计信息对象中第一列的列，创建单列统计信...

2019-01-17 13:01:26 339

原创 SQL优化：找到缺少的索引

上篇文章讲了如何查询索引的使用情况，前提是索引已经创建了。这篇文章要讲的是SQL Server对系统中运行的sql进行监控，把系统中缺少的索引信息保存下来，提供给我们查询。我们根据这些辅助的信息，结合实际的情况，比如：表上已经有的索引，涉及到的字段，以及具体的sql，判断如果创建了这些索引，是否真的能提高性能，如果真的能提高性能，就可以创建这些索引。通过查询3个视图，可以了解如果创建了...

2019-01-17 12:16:56 2083

原创 SQL Server Management Studio中复制查询结果保持原有的格式

1、运行如下的监控语句，看当前某个会话的sql运行到哪一步了，找到卡住的sql，然后再有针对的进行优化：--监控SQL执行到哪一步SELECT session_id, b.text, DB_NAME(b.dbid) AS DBname, OBJECT_NAME(b.objectid) AS objname, SUBSTRING( ...

2019-01-17 12:04:24 1870

原创 SQL优化：索引使用情况

通过使用视图 sys.dm_db_index_usage_stats，可以查询到索引的使用情况，包括：seek次数、scan次数、lookup次数、更新次数。如果seek次数很高，说明索引的使用情况很好，因为索引就是用来seek的。如果scan次数很高，说明大部分都是索引扫描，使用的方式不是太理想。代码如下：create table txt(id int primary key,...

2019-01-14 16:08:10 482

原创 SQL优化：索引碎片

通过函数 sys.dm_db_index_physical_stats(db_id('wcc'), --数据库id null, --对象id：数据库名称.架构.对象名称 null, --索引id null, --分区号 '' --模式 )可以查看索引碎片的具体情况...

2019-01-14 15:50:59 644

空空如也

空空如也