‘数据库’ 分类的存档
[转载]SQL Server中的临时表和表变量 – peter521 – 博客园. 在SQL Server的性能调优中，有一个不可比拟的问题：那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。记 得在给一家国内首屈一指的海运公司作SQL Server应用性能评估和调优的时候就看到过大量的临时数据集处理需求，而他们的开发人员就无法确定什么时候用临时表，什么时候用表变量，因此他们就简 单的使用了临时表。实际上临时表和表变量都有特定的适用环境。 先卖弄一些基础的知识： 表变量 变量都以@或@@为前缀，表变量是变量的一种，另外一种变量被称为标量(可以理解为标准变量，就是标准数据类型的变量，例如整型int或者日期 型DateTime)。以@前缀的表变量是本地的，因此只有在当前用户会话中才可以访问，而@@前缀的表变量是全局的，通常都是系统变量，比如说 @@error代表最近的一个T-SQL语句的报错号。当然因为表变量首先是个变量，因此它只能在一个Batch中生存，也就是我们所说的边界，超出了这 个边界，表变量也就消亡了。 表变量存放在内存中，正是因为这一点所有用户访问表变量的时候SQL Server是不需要生成日志。同时变量是不需要考虑其他会话访问的问题，因此也不需要锁机制，对于非常繁忙的系统来说，避免锁的使用可以减少一部分系统负载。 表变量另外还有一个限制就是不能创建索引，当然也不存在统计数据的问题，因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了)，这一特性有的时候是件好事，而有些时候却会造成一些麻烦。 临时表 临时对象都以#或##为前缀，临时表是临时对象的一种，还有例如临时存储过程、临时函数之类的临时对象，临时对象都存储在tempdb中。以# 前缀的临时表为本地的，因此只有在当前用户会话中才可以访问，而##前缀的临时表是全局的，因此所有用户会话都可以访问。临时表以会话为边界，只要创建临 时表的会话没有结束，临时表就会持续存在，当然用户在会话中可以通过DROP TABLE命令提前销毁临时表。 我们前面说过临时表存储在tempdb中，因此临时表的访问是有可能造成物理IO的，当然在修改时也需要生成日志来确保一致性，同时锁机制也是不可缺少的。 跟表变量另外一个显著去别就是临时表可以创建索引，也可以定义统计数据，因此SQL Server在处理访问临时表的语句时需要考虑执行计划优化的问题。 表变量 vs. 临时表 表变量 临时表 数据集的存储位置 内存(不考虑被换到页面文件这种情况) 磁盘(不考虑访问后被缓存到内存中) 是否需要日志 否 是 是否可以创建索引 否 是 是否可以使用统计数据 否 是 是否可以在多会话中访问 否 是 是否需要锁机制 否 是 结论 综上所述，大家会发现临时表和表变量在底层处理机制上是有很多差别的。 简单地总结，我们对于较小的临时计算用数据集推荐使用表变量。如果数据集比较大，如果在代码中用于临时计算，同时这种临时使用永远都是简单的全 数据集扫描而不需要考虑什么优化，比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等)，也可以考虑使用表变量。使用 [...]
[转载]SQL Server 2008 参数化查询 – 小máo的专栏 – 博客频道 – CSDN.NET. 我将讨论如果一个查询可以被参数化,那么SQL Server优化器怎样尝试将其参数化,以及你可以怎样建立你自己的参数化查询. 1.什么是参数化查询? 一个简单理解参数化查询的方式是把它看做只是一个T-SQL查询,它接受控制这个查询返回什么的参数.通过使用不同的参数,一个参数化查询返回不同的结果.要获得一个参数化查询,你需要以一种特定的方式来编写你的代码,或它需要满足一组特定的标准. 有两种不同的方式来创建参数化查询.第一个方式是让查询优化器自动地参数化你的查询.另一个方式是通过以一个特定方式来编写你的T-SQL代码,并将它传递给sp_executesql系统存储过程,从而编程一个参数化查询.这篇文章的后面部分将介绍这个方法. 参数化查询的关键是查询优化器将创建一个可以重用的缓存计划.通过自动地或编程使用参数化查询,SQL Server可以优化类似T-SQL语句的处理.这个优化消除了对使用高贵资源为这些类似T-SQL语句的每一次执行创建一个缓存计划的需求.而且通过创建一个可重用计划,SQL Server还减少了存放过程缓存中类似的执行计划所需的内存使用. 2.现在让我们看看使得SQL Server创建参数化查询的不同方式. 参数化查询是怎样自动创建的? 微软编写查询优化器代码的人竭尽全力地优化SQL Server处理你的T-SQL命令的方式.我想这是查询优化器名称的由来.这些尽量减少资源和最大限度地提高查询优化器执行性能的方法之一是查看一个T-SQL语句并确定它们是否可以被参数化.要了解这是如何工作的,让我们看看下面的T-SQL语句: SELECT * FROM AdventureWorks.Sales.SalesOrderHeader WHERE SalesOrderID = 56000; GO 在这里,你可以看到这个命令有两个特点.第一它简单,第二它在WHERE谓词中包含一个用于SalesOrderID值的指定值.查询优化器可以识别这个查询比较简单以及SalesOrderID有一个参数(“56000″).因此,查询优化器可以自动地参数化这个查询. 如果你使用下面的SELECT语句来查看一个只包含用于上面语句的缓存计划的,干净的缓冲池,那么你会看到查询优化器将T-SQL查询重写为一个参数化T-SQL语句: SELECT stats.execution_count AS cnt, p.size_in_bytes AS [size], . AS [plan_text] FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql JOIN sys.dm_exec_query_stats stats [...]
[转载](原创)ROW_NUMBER()函数的使用 – InSky – 博客园. ROW_NUMBER()函数是SQL 2005中新添的一个函数。通常它被用在分页的SQL语句中。 微软官方的对此函数的描述是：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。 我对此的理解：利用此函数可以为表中的某个字段建立序列，从1开始。就是说，根据已存在的某列，利用此函数可建立一新列，新列是数字，按照已存在列的顺序从1开始。 上边可能说的不是很清楚，请结合下边这个例子来看吧！ 1USE Northwind 2GO 3 4– 5SELECT OrderID, 6 CustomerID, 7 OrderDate 8FROM dbo.Orders WITH(NOLOCK) 9 10SELECT OrderID, 11 CustomerID, 12 OrderDate, 13 ROW_NUMBER() OVER(ORDER BY orderID DESC) AS 字段编号 14FROM dbo.Orders WITH(NOLOCK) 第一个查询只是一个普通的查询，查询出Orders表中的3个字段的记录。其结果为： 第二个查询相比第一个查询仅仅是多了“ ROW_NUMBER() OVER(ORDER BY orderID DESC) AS 字段编号 ”这段语句。我们来分析一下这段语句。 这段语句的作用就是利用ROW_NUMBER()函数根据OrderID这列来生成一个新的数据列，这列的名称为字段编号。然后我们根据ORDER BY orderID DESC来指定字段编号这列按照OrderID的逆序来生成，结果如下： 比较两个结果集，会发现第二个结果集是按照OrderID字段逆序的记录，其实也可看成是字段编号这个字段的正序排序，ROW_NUMBER()函数相当于为SELECT语句末尾加了ORDER BY子句，第二个SELECT语句其实等价于： 1SELECT OrderID, 2 CustomerID, 3 OrderDate, [...]
[转载]SQL Server 2005 中的Row_Number()函数 – 挖土. – 博客园. 我们知道，SQL Server 2005和SQL Server 2000 相比较，SQL Server 2005有很多新特性。这篇文章我们要讨论其中的一个新函数Row_Number()。数据库管理员和开发者已经期待这个函数很久了，现在终于等到了！ 通常，开发者和管理员在一个查询里，用临时表和列相关的子查询来计算产生行号。现在SQL Server 2005提供了一个函数，代替所有多余的代码来产生行号。 我们假设有一个资料库[EMPLOYEETEST]，资料库中有一个表[EMPLOYEE]，你可以用下面的脚本来产生资料库，表和对应的数据。 USE [MASTER] GO IF EXISTS (SELECT NAME FROM SYS.DATABASES WHERE NAME = N’EMPLOYEE TEST’) DROP DATABASE [EMPLOYEE TEST] GO CREATE DATABASE [EMPLOYEE TEST] GO USE [EMPLOYEE TEST] GO IF EXISTS SELECT * FROM SYS.OBJECTS HERE [...]
[转载]支持定位当前页,自定义排序的分页SQL(拒绝动态SQL) – 牟向阳 – 博客园. 1,场景：根据学生编号查询，返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。 CREATE PROC [dbo].[Sp_testpagerandsorting] (@GroupID INT, @CurrentId INT, @TimeFrom DATETIME, @TimeTo DATETIME, @OrderBy CHAR(50), @PageSize INT, @CurrentPage INT) AS SET nocount ON BEGIN DECLARE @StartNumber INT, @EndNumber INT, @CurrentIdRowNumber INT, @RecordCount INT, @EndPageIndex INT DECLARE @RowNumberTable TABLE ( rownumber INT IDENTITY (1, 1), id INT ) –step 1: Build sort [...]
