玩sqlserver
AskTOMmorow
不会理财的DBA不是一个好哲学家
展开
-
外键级联更新和删除
--直接玩两个表就能完全理解了--创建学生表create table Tstudent(stuno varchar(3) primary key,stuname varchar(4),stuclass varchar(3))--插入数据insert into Tstudent values('001','gh','101')insert into Tstudent values('0原创 2017-03-28 09:30:48 · 694 阅读 · 0 评论 -
truncate 和 delete对自增ID的影响
if(object_id('dbo.testa') is not null) drop table testa;gocreate table testa(id bigint not null primary key identity(1,1) ,tname nvarchar(max) );--3 Timesinsert into testa(tname) values(N'ddcawsdqw原创 2017-04-20 20:06:25 · 1230 阅读 · 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 · 203 阅读 · 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 · 270 阅读 · 0 评论 -
Insert Data Demo--插入数据的四种方式及测试
--Insert Data Demo--basic tableuse tempdb;GOif(object_id('dbo.orders','U') is not NULL) drop table dbo.orders;GOcreate table dbo.orders(orderid int not null /*identity(1,1)*/ constraint PK_orders原创 2017-05-16 17:07:46 · 1707 阅读 · 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 · 812 阅读 · 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 · 805 阅读 · 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 · 353 阅读 · 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 · 375 阅读 · 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 · 262 阅读 · 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 · 261 阅读 · 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 · 318 阅读 · 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 · 303 阅读 · 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 · 377 阅读 · 0 评论 -
索引视图测试
--索引视图/*原理:对视图创建唯一聚集索引后,结果集将存储在数据库中,就像带有聚集索引的表一样。适用范围:(1)大量行进行复杂处理(如聚合大量数据或联接许多行)的视图。(2)在查询中频繁地引用这类视图。不适用情景:(1)具有大量写操作的 OLTP 系统。(2)具有大量更新的数据库(维护索引视图的成本可能高于维护表索引的成本)。(3)数据频繁变化(维护索引视图数据的成本可能超过使用索引原创 2017-04-20 20:05:08 · 309 阅读 · 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 · 266 阅读 · 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 · 324 阅读 · 0 评论 -
top,windows function,distinct testing
1 导入基本数据---IMP basic data--创建成绩表if(object_id('Tgrade') is not null) drop table Tgrade;GOcreate table Tgrade(grade_stuno varchar(3) ,grade_lessonno varchar(3) ,grade decimal(5,2))--插入成绩数据inser原创 2017-03-29 16:07:03 · 233 阅读 · 0 评论 -
相关子查询等价改写
if(object_id('dbo.saleorder','U') is not null) drop table saleorder;create table saleorder(orderid varchar(50) primary key,orderPrice decimal(20,8) null,orderdate date not null,userid varchar(50) not原创 2017-04-07 19:17:38 · 347 阅读 · 0 评论 -
Effect of the remote end of the remote data fetch index modulation(Link Server)
相关数据已经脱敏处理直接测试--远程调取数据时远程端索引的影响set statistics time on;set statistics io on;--REMOTEsp_helpindex DataSync--The object 'DataSync' does not have any indexes, or you do not have permissions.select * F原创 2017-04-07 19:34:20 · 242 阅读 · 0 评论 -
有趣的SQL执行顺序和3值逻辑
1 那些能看出SQL执行顺序蛛丝马迹的DEMO sql执行顺序:from>where>group by>having>select(over>distinct>top)>order by 相同优先级的执行顺序:ANSI标准:同时执行;sqlserver实现:select部分同时执行(ALL-AT-ONCE OPERATION),where部分除去优先级(AND>OR)sqlserver会按原创 2017-03-30 10:41:54 · 350 阅读 · 0 评论 -
引号分割标识符,字符串联结方式 相关的系统参数Testing
-------------引号分割标识符select '1223' nn,"1233445" mm;--消息 207,级别 16,状态 1,第 1 行--Invalid column name '1233445'.set QUOTED_IDENTIFIER OFF;select '1223' nn,"1233445" mm--1223 1233445set QUOTED_IDENTIFI原创 2017-03-31 11:19:44 · 379 阅读 · 0 评论 -
相关子查询等价改写2
前面写过一篇相关的文章(链接:http://blog.csdn.net/asktommorow/article/details/69620645),实现了开窗函数和JOIN的相关子查询改写,本片展示开窗函数偏移相关的子查询改写。if(object_id('dbo.saleorder','U') is not null) drop table saleorder;create table sale原创 2017-04-10 21:18:46 · 327 阅读 · 0 评论 -
self-join 生成数字表
with seed as (select 0 num union allselect 1 num union allselect 2 num union allselect 3 num union allselect 4 num union allselect 5 num union allselect 6 num union allselect 7 num union allse原创 2017-04-11 19:35:27 · 351 阅读 · 0 评论 -
连接探索(交叉连接,内连接,外连接)
创建临时表select * into #t1 from (select 0 num union allselect 1 num union allselect 2 num union allselect 3 num union allselect 4 num union allselect 5 num union allselect 6 num union allselect 7 n原创 2017-04-11 20:08:06 · 254 阅读 · 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 · 340 阅读 · 0 评论 -
tempdb test
临时表主要用来干什么的用户对象(user_object_reserved_page_count) 由用户显式创建。包括: 用户定义的表和索引系统表和索引全局临时表和索引局部临时表和索引table 变量表值函数中返回的表内部对象(internal_object_reserved_page_count) 由 SQL Server 数据库引擎创建,用于处理 SQL Server 语句。原创 2017-04-28 18:30:34 · 285 阅读 · 0 评论 -
各种通配符
--通配符测试if object_id('dbo.tstudent','U') is not null drop table tstudent; create table tstudent(id int identity(1,1) not null primary key,name varchar(100) );insert into tstudent(name) values('Abiga原创 2017-04-05 16:35:36 · 1223 阅读 · 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 · 452 阅读 · 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 · 273 阅读 · 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 · 469 阅读 · 0 评论