随笔分类 - SQL
摘要：概念简单点说游标的作用就是存储一个结果集，并根据语法将这个结果集的数据逐条处理。观点正因为游标可以将结果集一条条取出处理，所以会增加服务器的负担。再者使用游标的效率远远没有使用默认的结果集效率高，在默认结果集中，从客户端发送到服务器的唯一一个数据包是包含需执行语句的数据包。而在使用服务器游标时，每一个FETCH语句都必须从客户端发送到服务器，然后在服务器中将它解析并编译为执行计划。除非要再sqlserver上进行很复杂的数据操作。基本知识一. SQL Server 2005 支持两种请求游标的方法 1.Transact-SQL （支持 SQL-92）； 2.数据库应用程序编程接口（API）..
阅读全文
摘要：设计背景由于历史原因，线上库环境数据量及其庞大，很多千万级以上甚至过亿的表。目标是让N张互相关联的表 按照一张源表为基表，数据搬移归档 这里我们举例N为50 每张表数据5000W最差性能sql进化客串2表KeyName 字段意义 名称等相同 从bug01 表中取出前500条不在bug02 表中的数据最差性能：?SELECT TOP 500 a.KeyName FROM bug01 a LEFT JOIN bug02 b on a.KeyName = b.KeyName WHERE (a.KeyName not in (select distinct b.KeyName From bug02))
阅读全文
摘要：目录1、什么是执行计划？执行计划是依赖于什么信息 2、 统一SQL语句的写法减少解析开销 3、 减少SQL语句的嵌套 4、 使用“临时表”暂存中间结果 5、 OLTP系统SQL语句必须采用绑定变量 6、 倾斜字段的绑定变量窥测问题 7、 begin tran的事务要尽量地小 8、 一些SQL查询语句应加上nolock 9、加nolock后查询经常发生页分裂的表，容易产生跳读或重复读 10、聚集索引没有建在表的顺序字段上，该表容易发生页分裂 11、使用复合索引提高多个where条件的查询速度 12、使用like进行模糊查询时应注意尽量不要使用前% 13、SQL Server 表连接的三种方式 1
阅读全文
摘要：Sql代码 sql over的作用及用法 RANK ( ) OVER ( [query_partition_clause] order_by_clause ) DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序, 其中PARTITION BY 为分组字段，ORDERBY 指定排序字段 over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。 其参数：over（partition by colum...
阅读全文
摘要：1. CREATE TABLE Team ( TeamID int identity not null， TeamDoc xml DEFAULT '＜Team /＞' NOT NULL )query方法 注意：你可以在表和视图列、T-SQL语句或存储过程的参数中使用XML。 这个方法允许你指定一个要计算的Xquery或XPath表达式，该方法的结果是一个XML数据类型对象。它的具体语法形式如下：query(XQuery) 第一个参数总是一个Xquery表达式。下面的例子使用一个查询来返回一个XML文档-其中包含有关每一个小组的投球手的信息：SELECT TeamDoc.quer
阅读全文
摘要：创建 for触发器use test gocreate trigger t1on afor insert,updateasinsert into b(name) values('a')在a表插入或更新数据后,在b表同时插入一条数据
阅读全文
摘要：事务事务的点:1.begin tran是事务开始的地方,也是 事务回滚的起点.也就说他会忽略这个起点之后的最终没有提交的所有语句,2.commit tran事务的提交 是一个事务的终点当发出 commit tran命令时,可以认为 该事务是 持久的.撤销已完成事务的唯一方法 是 发出一个新的事务.从功能上而言,该事务 是对第一个事务的反转.3.rollback tran事务回滚到 begin执行之前的状态. 不过也有例外,也可以 使用 保存点.4.save tran保存事务从本质上说就是创建书签. 为书签起个名字,就是在回滚中 引用它.创建书签的好处就是 可以 回滚到代码中的特定位置.注意:
阅读全文
摘要：用户自定义函数 和存储过程是类似的,是一组 有序的t-sql语句,udf被 预先优化和编译,并且可以作为一个单元来进行调用.使用存储过程 时 可传入参数,传出参数.可以返回值,不过该值用于指示成功或者失败,而非返回数据.也可以返回结果集,但是在没有将结果集插入到某种表(通常是临时表)中以供后面使用的情况下,不能在 查询中真正使用它们. 即使使用表值 输出参数,在查询中使用结果之前,也要额外的一个步骤.那么 UDF可以传入参数,但是不可传出参数. 但是可以返回值,和系统函数一样,可以返回标量值,这个值的好处是 不像存储过程那样只限于整型数据类型,而是可以返回大多数sqlserver的数据类型.下
阅读全文
摘要：数据库结构:表内的数据:自定义函数: 递归查出 树下所有节点 ,参数是 父id create function sss(@id as int) returns @t table ( id int not null, name int not null, pid int null ) as begin declare @lay as int; insert into @t select * from tree where pid =@id; select @lay = min(id) from tree where pid =@id; --第一次 @l...
阅读全文
摘要：输入输出参数:给存储过程传参数,叫做输入参数,用户告诉存储过程需要 利用这个参数干些什么.输出参数: 从存储过程得到那些数据.创建一个可选参数的存储过程:create proc pa1@name varchar(50)=NULLasif(@name is not null) select * from a where name like @name+'%';else select * from aexec pa1 exec pa1 a 创建输出参数:使用输出参数 传递 最后一次插入的标识列的值:create proc pa2 @id int output as ins...
阅读全文
摘要：创建简单视图:use test go create view v1(视图名) as select name from b这样视图就创建好了.下面说下视图的本质:当执行 select * from v1 时, 那么实质上市告诉 sql server 把执行 select name from b 语句返回的结果给我.视图就像在命令执行的查询那样运行---没有任何的预先优化过程.这意味着数据在请求和所交付的数据之间增加额额外的一层系统开销,而视图的运行总是比 执行运行内部的sql 更慢.视图存在的理由就是---对于用户是安全或者简化的.视图可以隐藏敏感数据,作为过滤器 例子如下: ...
阅读全文
摘要：查看最后一行插入标识列的值use testgoinsert into a(name) values('ss')declare @ident intselect @ident=@@identityselect @ident结果: 查看语句响应了多少行use test godeclare @rowCount intselect * from bselect @rowCount=@@rowcountselect @rowCount效果如图:批处理:使用go可以将一个脚本,分为多个批处理下面脚本创建一个表,if not exists( select s.name,t.name from
阅读全文
摘要：存储过程: 做的就是 返回最后一次插入的标识列 id值create proc pa2@id int outputasinsert into a(name) values('5')set @id=@@identity表: id 是标识列 ,后台代码: string cons = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString(); using (SqlConnection con=new SqlConnection(cons)) { if (con.St...
阅读全文
摘要：第一范式: 全部是关于消除重复数据组和保证原子性的规范化信息.通俗讲 就是 创建主键,然后将任何重复的数据组移动到新的表中,为这些表创建新键.原始表结构:一个客户可能对应很多订单,那么表中将出现大量的重复数据.解决办法就是 将重复列 移到另一个表中第二范式:进一步减少重复的数据出现.(不一定是数据组)尽量使所有列 都依赖于真个键.第三范式:处理的问题是 使得表中所有列 不仅仅是依赖于某个事物--而是要依赖于 正确的事物.第三范式的规则:1.首先实现第二范式.2.任何列都不能依赖于非键列.3.不可以有派生的数据.(派生 就是 有 表中其他的列 计算得到的列)Boyce-Codd范式(实际是第三范
阅读全文
摘要：sql server的存储机制区段: 是用来为表和索引 分配空间的基本存储单元. 由 8个连续的页面构成,大小为64kb.区段的注意事项:一旦区段已满,那么下一记录 将要占据的空间不是记录的大小,而是整个区段的大小.通过预先分配空间,sql server节省了为每个记录分配新空间的时间页: 页是在到达实际数据行 之前所能达到的最后一个存储级别.尽管每个区段中的页数是固定的,但是每一页中的行数 不是固定的--这完全取决于行的大小,而行的大小 可以变化的. 可以把 页 看作是 表行和索引行的容器.通常不允许行跨页.就是行的大小 最大为8kb.下面介绍两种不同的页类型:数据页: 就是表中的实际数据索
阅读全文
摘要：索引设计的建议：一.检查where子句和连接条件列当一个查询提交到sql server时，查询优化器尝试为查询中引用的所有表查找最佳的数据访问机制，一下是它所进行的方式。1.优化器识别Where子句和连接条件中包含的列、2.接着优化器检查这些列的索引.3.优化器通过从索引上维护的统计确定子句的选择性(也就是返回多少行),评估每个索引的有效性4.最终,优化器根据前面几个步骤中收集的信息,评估读取所限定的行开销最低的方法.下面看个例子: Table_1表内 又 30000条数据 执行查询:查看结果:逻辑读取的次数为 95次.下面加上where 条件句, where 的列 是 含有聚集索引的.结果:
阅读全文
摘要：查看数据库的信息：INFORMATION_SCHEMA.CHECK_CONSTRAINTSINFORMATION_SCHEMA.COLUMN_DOMAIN_USAGEINFORMATION_SCHEMA.COLUMN_PRIVILEGESINFORMATION_SCHEMA.COLUMNS 查看数据库中的所有列INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGEINFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGEINFORMATION_SCHEMA.DOMAIN_CONSTRAINTSINFORMATION_SCHEMA.DOMAI
阅读全文
摘要：子查询 构建嵌套子查询测试表: stu secselect * from stu where sec in ( select ID from sec) select a.* from stu a join sec b on a.sec=b.ID这两个语句返回的相同的查询结果.关联子查询关联子查询 信息传递是双向的.此查询分三个步骤完成:1.外部查询获得一个记录,然后将该记录传递到内部查询混合数据类型cast和convert这两个函数 都可以执行 数据类型转换,不同的是 convert还提供一些日期格式的转换,而cast没这个功能.print cast(getdate() as varchar.
阅读全文
摘要：表组织表包含在一个或多个分区中，每个分区在一个堆或一个聚集索引结构包含数据行。堆页或聚集索引页在一个或多个分配单元中进行管理，具体的分配单元数取决于数据行中的列类型。聚集表、堆和索引SQL Server 表使用下列两种方法之一来组织其分区中的数据页：聚集表是有聚集索引的表。数据行基于聚集索引键按顺序存储。聚集索引按 B 树索引结构实现，B 树索引结构支持基于聚集索引键值对行进行快速检索。索引中每个级别的页（包括叶级别的数据页）链接在一个双向链接的列表中。但是，通过使用键值来执行从一个级别到另一级别的导航。堆是没有聚集索引的表。数据行不按任何特殊的顺序存储，数据页也没有任何特殊的顺序。数据页不在
阅读全文
摘要：master数据库数据库记录 SQL Server 系统的所有系统级信息。这包括实例范围的元数据（例如登录帐户）、端点、链接服务器和系统配置设置。此外，master 数据库还记录了所有其他数据库的存在、数据库文件的位置以及 SQL Server 的初始化信息。因此，如果 master 数据库不可用，则 SQL Server 无法启动。在 SQL Server 中，系统对象不再存储在 master 数据库中，而是存储在 Resource 数据库中。不能在 master 数据库中执行下列操作：添加文件或文件组。更改排序规则。默认排序规则为服务器排序规则。更改数据库所有者。master 归 dbo
阅读全文