SqlServer
AskTOMmorow
不会理财的DBA不是一个好哲学家
展开
-
【Sqlserver】复杂去重需求实现DEMO
use tempdbGOif(object_id('asktom','U') is not null) drop table asktom;create table asktom(id bigint not null identity(1,1) constraint pk_asktom primary key,pid varchar(10) not null,is_childp bit n原创 2017-08-03 20:58:53 · 307 阅读 · 0 评论 -
identity 完全测试
--identity--include: seed(initial value),step(increment value)--usage:surrogate key(代理键,业务无关,系统生成)--basic tableif(object_id('dbo.t1','U') is not null) drop table t1;GOcreate table dbo.t1(id int n原创 2017-05-16 17:21:30 · 846 阅读 · 0 评论 -
子查询列名中的替换错误
create table #T (id int);create table #B (id int,iid int);insert into #T values(1),(2),(3);insert into #B values(1,4),(2,5),(4,6);select * from #T where id in (select id from #B);/*12*/alter tabl原创 2017-04-24 19:09:31 · 285 阅读 · 0 评论 -
CROSS APPLY--windows function 改写
--客户最新的三个订单(1:N关系)select * from sales.orders where custid =85 order by orderdate desc;--select * from Sales.Customers where custid=85;--CROSS ALPPLYSELECT C.custid, d.* FROM Sales.Customers AS C原创 2017-04-24 19:08:04 · 224 阅读 · 0 评论 -
【Sqlserver】堵塞问题解决思路
背景: App userA:主要负责数据入库(线程较少) ; userB:负责数据查询和校验(线程较多,多为个人连接数据库查询)。 userA和userB之间经常会存在锁导致堵塞,从公司层面看,userA的是主体核心业务,userB是从业务。最好的改进方式是全任务的计划执行,在App改进之前,先用如下定时任务临时缓解问题。 - 操作:kill user=userB,而且堵塞原创 2017-05-05 20:13:22 · 510 阅读 · 0 评论 -
sqlserver UNION-OR 改写一例
------原SQL info:select name=name,xtype=0,rowcnt=-1 from sysobjects with (nolock) where status>=0 and objectproperty(id,'ISUSERTABLE')=1unionselect name,1,-1 from sysobjects with (nolock) where statu原创 2017-05-05 11:28:50 · 615 阅读 · 0 评论 -
索引视图测试
--索引视图/*原理:对视图创建唯一聚集索引后,结果集将存储在数据库中,就像带有聚集索引的表一样。适用范围:(1)大量行进行复杂处理(如聚合大量数据或联接许多行)的视图。(2)在查询中频繁地引用这类视图。不适用情景:(1)具有大量写操作的 OLTP 系统。(2)具有大量更新的数据库(维护索引视图的成本可能高于维护表索引的成本)。(3)数据频繁变化(维护索引视图数据的成本可能超过使用索引原创 2017-04-20 20:05:08 · 326 阅读 · 0 评论 -
inline table-valued function DEMO
--------------inline table-valued function DEMOIF OBJECT_ID('dbo.fn_usacus') is not null drop function dbo.fn_usacus;gocreate function dbo.fn_usacus(@i varchar(20) ) returns tableasreturnSELECT原创 2017-04-20 20:03:54 · 290 阅读 · 0 评论 -
VIEW DEMO
--------------VIEW DEMO-------BASICIF OBJECT_ID('dbo.vw_usacus') is not null drop view dbo.vw_usacus;gocreate view dbo.vw_usacus as SELECT custid id, companyname nameFROM InsideTSQL.Sales.Custo原创 2017-04-20 20:02:29 · 343 阅读 · 0 评论 -
元数据查询
--------------元数据查询--目录视图select schema_name(schema_id) as table_schema_name,--格式转换name as table_name from sys.tablesselect name as column_name,TYPE_NAME(system_type_id) as column_type,max_length,原创 2017-04-06 19:51:48 · 305 阅读 · 0 评论 -
datetime about DEMO
--语言设置对时间的影响set language british;select cast('02/12/2017' as datetime);--2017-12-02 00:00:00.000select cast('20170212' as datetime);--2017-02-12 00:00:00.000select cast('2017-02-12' as datetime)-原创 2017-04-05 20:34:40 · 472 阅读 · 0 评论 -
tempdb test
临时表主要用来干什么的用户对象(user_object_reserved_page_count) 由用户显式创建。包括: 用户定义的表和索引系统表和索引全局临时表和索引局部临时表和索引table 变量表值函数中返回的表内部对象(internal_object_reserved_page_count) 由 SQL Server 数据库引擎创建,用于处理 SQL Server 语句。原创 2017-04-28 18:30:34 · 305 阅读 · 0 评论 -
集合运算
--集合运算(结果不一定是集合<可能存在重复行>,可能是多集)--基础数据if(object_id('dbo.testa','U') is not null) drop table testa;GOcreate table testa (id int not null,tname varchar(50) NULL);if(object_id('dbo.testb','U') is not原创 2017-04-28 18:07:15 · 403 阅读 · 0 评论 -
select into VS insert select VS bulk insert 效率测试
--select into VS insert select VS bulk insert 效率测试--数据库恢复模式:简单--Basic tableuse tempdb;GOif(object_id('dbo.orders','U') is not NULL) drop table dbo.orders;GOcreate table dbo.orders(orderid int no原创 2017-05-16 20:17:51 · 862 阅读 · 0 评论 -
透视,逆透视,分组集 pivoting ,unpivoting ,grouping set
--透视,逆透视,分组集 pivoting ,unpivoting ,grouping set---------------基本数据use tempdbgoif(object_id('dbo.asktom') is not null ) drop table asktom;create table asktom(id int not null identity(1,1),adate dat原创 2017-05-09 20:46:28 · 371 阅读 · 0 评论 -
Merge into test
--merge into test/*使用Merge关键字的好处:简洁有效,性能更强Note:在SQL Server 2008之前没有Merge*/create table testsou( --源测试表id int,name varchar(10),des varchar(50))create table testtag( --目标表id int,name varchar(1原创 2017-06-06 16:37:40 · 402 阅读 · 0 评论 -
【SqlServer】Cross Apply的另一个妙用和for xml查询字段赋别名问题的解决
--创建测试表create table ta (id int ,val varchar(10));create table tb (id int ,val varchar(10));insert into ta values(1,'A'),(2,'B');insert into tb values(1,'AA'),(2,'BB'),(1,'AC'),(2,'BC');--有一个字符串拼接需求,原创 2017-08-03 14:54:43 · 500 阅读 · 0 评论 -
【Sqlserver】基于备份和日志备份的还原测试
--------------------step1. create database & table USE master;-- Drop databaseIF DB_ID('TestBakDB') IS NOT NULL DROP DATABASE TestBakDB;-- If database could not be created due to open connections, ab原创 2017-08-03 14:30:58 · 407 阅读 · 0 评论 -
【Sqlserver】基于备份和差异备份的还原测试
--------------------step1. create database & table USE master;-- Drop databaseIF DB_ID('TestBakDB') IS NOT NULL DROP DATABASE TestBakDB;-- If database could not be created due to open connections, ab原创 2017-08-03 14:30:00 · 328 阅读 · 0 评论 -
过滤条件顺序导致不一致问题
背景:派生表中的条件已经过滤掉非法的数据,但是最终查询的时候还是出现报错的问题。 原因:sqlserver在决定生产最终的执行计划的时候会在不改变最终查询结果的前提下会做条件的合并(派生表中的条件和最外层条件处于相同优先级),然后按照cost最优的原则选择条件的执行顺序。 if(object_id('testa','U') is not null) drop table testa;原创 2017-08-07 17:36:49 · 360 阅读 · 0 评论 -
output Demo
--output Demo--Basic dataif(object_id('dbo.prd','U') is not null) drop table dbo.prd;CREATE TABLE dbo.prd(id bigint not null identity(1,1) constraint pk_prd primary key,code varchar(20) not null,p原创 2017-08-07 17:21:18 · 488 阅读 · 0 评论 -
【in - exists-join(distinct)】SQL调优改写一例
相关数据已经脱敏处理原SQLMERGE INTO IntoTable t USING ( SELECT x.PID, b.SEC_ID, b.T_SYB, b.E_CD, x.END_DATE, x.xh 区间级别, isnull(x.hj, 0) 区间人数 FROM原创 2017-07-10 11:13:34 · 296 阅读 · 0 评论 -
【SQL SERVER】删除过期的SQL Server备份文件
近期遇到定时任务中清理数据库过期文件失效的问题,总结了下删除过期数据库备份文件的相关方法,总结如下:1.BAT脚本法脚本DEMO(删除两天前的数据): FORFILES /P “E:\Backup” /M *.bak /C “cmd /C del /Q @path” /d -2 说明: FORFILES /P “文件路径” /M 文件名(通配符) /C “cmd /C del /Q @pat原创 2017-07-03 14:32:12 · 1017 阅读 · 0 评论 -
【数据库需求】生成指定区域内所有扩展数据
-------需求:生成指定区域内所有扩展数据--(id,id2,create_number<=num,num) DEMO:(1,1,3)->(1,1,1,3),(1,1,2,3)(1,1,3,3)if(object_id('tempdb..#a','U') is not null) drop table #a;create table #a(pk_id int not null iden原创 2017-07-10 19:58:26 · 241 阅读 · 0 评论 -
sp_OACreate和SQL Server中执行正则表达式
前面数据库迁移有遇到sp_OACreate的相关报错,当时修改数据库选项并赋权解决了该问题,但具体是哪边使用了sp_OACreate导致的并没有深究,今天专门研究了一下。--报错EXEC sp_OACreate;SQL Server 阻止了对组件“Ole Automation Procedures”的 过程“sys.sp_OACreate”的访问,因为此组件已作为此服务器安全配置的一部分而被关原创 2017-07-10 16:25:09 · 680 阅读 · 0 评论 -
update demo
-- update demo--BASIC DATAuse tempdb;GOif(object_id('torder','U') is not null) drop table torder;create table torder(id bigint not null identity(1,1) constraint PK_TORDER primary key,onumb varcha原创 2017-06-06 17:11:20 · 337 阅读 · 0 评论 -
Delete DEMO
-- delete DEMOuse tempdb;GOif (object_id('tuser','U') is not null) drop table tuser;create table tuser(id int not null identity(1,1) constraint PK_TUSER primary key ,uid int not null constraint TU原创 2017-06-06 17:10:00 · 281 阅读 · 0 评论 -
插入数据简单测试
--Create tableif(object_id('testa','U') is not null) drop table testa;create table testa (id int not null identity(1,1) primary key,val varchar(200) not null constraint testa$BPK_AK_Key unique non原创 2017-06-06 17:08:34 · 279 阅读 · 0 评论 -
【SQL 改写】not exists-left join (distinct) where is null改写
数据已经过脱敏处理--原SQL 30min出不来结果select a.*from TeableA awhere is_open = '1' and ecd = 'xshg' and not exists(select * from bvaluation b where a.calendar_date = b.tdate) and a.calendar_date >=原创 2017-04-13 21:06:27 · 453 阅读 · 0 评论 -
记一次大批量更新导致的问题
问题背景 1. 数据库版本信息:Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) ; 2. 架构:AlwaysON ,DB1读写,DB2只读,最迟半个小时会同步日志。问题起因:数据需原创 2017-04-17 16:45:52 · 733 阅读 · 0 评论 -
【SQLSERVER】sqlserver表行数,存储占用统计
--Edit By bzm 20160602--exec sp_spaceused 'equ_div' 实现select a.name 表名, a.rows 行数, ltrim(str(reservedpages * 8192 / 1024., 15, 0) + ' KB') 保留空间,--reserved ltrim(str(pages * 8192原创 2017-03-02 11:08:17 · 477 阅读 · 0 评论 -
【sqlserver】那些令你一脸懵逼的执行结果
--IN失效问题create table ta (id int,val varchar(10))create table tb (di int, --注意不是IDval varchar(10))insert into ta values(1,'x'),(2,'y')insert into tb values(1,'x')SET STATISTICS PROFILE ON --执原创 2016-10-24 11:17:40 · 199 阅读 · 0 评论 -
【sqlserver】一个CTE递归调用案例优化
/*** 相关数据已脱敏 ***/--set statistics io on--set statistics time on --待优化高耗时SQLwith cte as (select t.sid, t.end_date, t.nav, t.d_value, t.s_value, t.factor,原创 2016-10-21 18:26:31 · 733 阅读 · 0 评论 -
【SQLSERVER】自定义函数:实现中文首字母大写
IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'[fn_ChineseToSpell]') AND xtype IN (N'FN', N'IF', N'TF')) DROP FUNCTION [fn_ChineseToSpell]GO/*创建取原创 2016-09-22 09:04:34 · 623 阅读 · 0 评论 -
【SQLSERVER】自定义函数:去零函数ClearZero
----去零函数 ---去除小数点后多余的零 select dbo.ClearZero('1245.3400') ---结果为1245.34 create function [ClearZero] (@Number varchar(200)) returns varchar(200) as begin if @Number = '' or @Number原创 2016-09-22 09:01:26 · 1428 阅读 · 0 评论 -
执行计划、索引 是否会影响SQL的执行结果
以前一自认为数据库的执行计划、索引属于数据库调优的范畴,最多只影响数据库的执行结果,但是接触的案例多了,理解执行计划深入了之后发现,有的时候执行计划会影响数据库的执行结果,甚至能使原来正确运行的SQL报错,无法正常产生结果集。下面分享下笔者在日常工作中遇到的两个案例: 注:案例在原库能重现,此处没有提供可以重现的DEMO,只做参考之用,相关表和字段已做脱敏处理。案例一:DB:oracle 10原创 2016-09-01 18:12:21 · 692 阅读 · 0 评论 -
【SQLSERVER】获取完整建表DDL SQL
-------获取完整建表DDL SQL--definedeclare @tabname varchar(50)set @tabname='equ_ipo_plan'--表名-------------------------------------获取建表DDL(+主键)if ( object_id('tempdb.dbo.#t') is not null)beginDROP TABLE原创 2016-08-30 15:57:14 · 2925 阅读 · 0 评论 -
【SQLSERVER】获取索引,约束(主键)的DDL
--获取索引,约束(主键)的DDLdeclare @tabname varchar(50)set @tabname='testbzm'--表名if ( object_id('tempdb.dbo.#IDX') is not null)beginDROP TABLE #IDXDROP TABLE #IDX2DROP TABLE #IDX3endSELECT a.name IndexNa原创 2016-08-30 15:55:50 · 641 阅读 · 0 评论 -
【SQLSERVER】获取建表DDL(+主键)
--获取建表DDL(+主键)declare @tabname varchar(50)set @tabname='testbzm'--表名if ( object_id('tempdb.dbo.#t') is not null)beginDROP TABLE #tendselect 'create table [' + so.name + '] (' + o.list + ')'原创 2016-08-30 15:54:59 · 1327 阅读 · 0 评论 -
【SQLSERVER】执行字符串
--执行字符串EXECUTE sp_executesql N'select CREATE_TIME from md_institution where PARTY_ID=93'--执行变量生成结果集declare @test nvarchar(500)set @test='select CREATE_TIME from md_institution where PART原创 2016-08-30 15:53:36 · 722 阅读 · 0 评论