-
利用SqlBulkCopy类实现大数据量拷贝
2009-08-27 17:37:07
-
逻辑查询处理
2009-07-31 10:38:09
v 写在前面
SQL编程有许多独特的方面，如：面向集合的编程思想，查询元素的逻辑处理次序，三值逻辑 等。本章只介绍查询处理的逻辑方面，建议阅读时尽量不考虑性能问题。本章中描述的某些逻辑
处理步骤可能看起来有些低效。但在实践中要记住：查询的实际物理处理可能与逻辑处理有很大 不同。 在SQL Server中负责生成实际工作计划(执行计划)的组件是查询优化器，以何种顺序访问表 、使用哪种访问方法和哪个索引、应用哪种联结算法等都是由优化器来决定的。优化器会生成多 个有效的执行计划，并选择其中成本最低的执行计划。逻辑查询处理的各个阶段都有特定的顺序 。另一方面，优化器却经常在它生成的物理执行计划中走捷径，当然，只有在保证结果集正确的 情况下优化器才走捷径。例如，为了使用索引，优化器可能会决定使用一个比逻辑处理所规定的 筛选器快的多的筛选器。
鉴于上述原因，明确区分查询的逻辑处理和物理处理非常有必要的。
v 逻辑查询处理的各个阶段
SQL不同于其他编程语言的最明显的特征是处理代码的顺序。在大多数的语言中，代码按顺序 被处理，但在SQL语言中，第一个被处理的子句是FROM，尽管Select语句第一个出现，但几乎 总在最后被处理。 每个步骤都会生成一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者不可 用。只有最后一步生成的表才会返回给调用者。
v 逻辑查询处理的各个阶段 – 逻辑查询处理阶段简介
1. FROM:对From子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1
2. ON:对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入到VT2
3. OUTER(JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN和INNER JOIN)，保留表 中未找到匹配的行将作为外部行添加到VT2中，生成VT3.如果FROM子句中包含两个以上的 表，则对上一个联接生成的结果表和下一个表重复执行步骤一到三，直到处理完所有表。
4. WHERE:对VT3应用WHERE筛选器。只有使其为true的行才能被插入到VT4。
5. GROUP BY:按GROUP BY子句中的列列表对VT4中的行分组，生成VT5。
6. CUBE|ROLLUP:吧超组插入到VT5，生成VT6。
7. HAVING:对VT6应用HAVING筛选器，只有使其为TRUE的组才会被插入到VT7。
8. SELECT:处理SELECT列表，生成VT8
9. DISTINCT:将重复的行从VT8中移除，产生VT9
10. ORDER BY: 将VT9中的行按ORDER BY子句中的列列表排序，生成一个游标(VC10)
11. TOP:从VC10的开始出选择指定数量或比例的行，生成表VT11，并返回给调用者。
v 步骤一：执行笛卡尔乘积(交叉联接)
对FROM子句的前两个表执行笛卡尔乘积，生成虚拟表VT1.如果左表包含n行，右表包含m行 ，VT1将包含n×m行。
v 步骤二：应用ON筛选器
ON筛选器是可以用于查询的三个筛选器(ON、WHERE、HAVING)中的第一个。ON筛选器中 的逻辑表达式被应用到上一步返回的虚拟表(VT1)中的所有行。只有使<联接条件>为TRUE的那 些行才会被包含在由步骤2返回的虚拟表(VT2)中。
三值逻辑(Three-Valued Logic)
三值逻辑是SQL特有的，大多数的编程语言的逻辑表达式只有TRUE和FALSE，SQL多了一个 UNKNOWN。 SQL中的UNKNOWN通常是由包含NULL的逻辑表达式而来的(下面的三个表达式的逻辑值都 是UNKNOWN:NULL>42;NULL=NULL等)。NOT UNKNOWN的结果还是UNKNOWN。 UNKNOWN逻辑结果在不的语言元素中被区别对待。例如，所有的查询筛选器(ON, Where,Having)都把UNKNOWN当作FALSE处理。是筛选器为UNKNOWN的行会被排除在结果 集之外。而CHECK约束中的UNKNOWN值被当作TRUE对待。假设表中包含一个CHECK约束， 要求salary列的值必须大于0，想该表中插入salary为NULL的行可以被接受，因为NULL>0等于 UNKNOWN，在CHECK约束中被视为和TRUE一样。 而UNIQUE约束、排序操作和分组操作认为两个NULL值相等。
v 步骤三：添加外部行
这一步只与外部链接(outer join)有关。通过指定一种外部链接(left,right,full)，你可以把一 个或两个输入表标记为保留表。把一个表标记为保留表表示我们希望返回该表的所有行，即使 <联接条件>过滤掉了一些行。左外部联接把坐标标记为保留表，以此类推。 步骤3返回VT2中的行以及保留表在步骤2被过滤掉的行。保留表中的这些行被称为外部行。外 部行中非保留表的属性( 查看(154) 评论(0) 收藏 分享 管理
OPENXML
2009-07-31 10:36:34
OPENXML
OPENXML 通过 XML 文档提供行集视图。由于OPENXML 是行集提供程序，因此可在会出现行集提供程序（如表、视图或 OPENROWSET 函数）的 Transact-SQL 语句中使用 OPENXML。
语法
OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])
[WITH (SchemaDeclaration | TableName)]参数
idoc
是 XML 文档的内部表式法的文档句柄。通过调用 sp_xml_preparedocument 创建 XML 文档的内部表式法。
rowpattern
是 XPath 模式，用来标识要作为行处理的节点（这些节点在 XML 文档中，该文档的句柄由 idoc 参数传递）。
flags
表示应在 XML 数据和关系行集间使用映射以及应如何填充溢出列。flag 为可选输入参数，可以是下列值之一。
字节值
描述0
默认为以特性为中心的映射。
1
使用以特性为中心的映射。
在某些情况下，可以将它与 XML_ELEMENTS 组合使用。使用时首先应用以特性为中心的映射，然后对于所有仍未处理的列应用以元素为中心的映射。2
使用以元素为中心的映射。
在某些情况下，可以将它与 XML_ATTRIBUTES 组合使用。使用时先应用以特性为中心的映射，然后对于所有仍未处理的列应用以元素为中心的映射。8
可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用（逻辑 OR）。
在检索的上下文中，该标志指明不应将已消耗的数据复制到溢出属性 @mp:xmltext。SchemaDeclaration
是窗体的架构定义：
ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...]ColName
是行集中的列名。
ColType
是行集中列的 SQL 数据类型。如果列类型不同于特性的基础 XML 数据类型，那么将发生类型压制。如果列的类型是 TIMESTAMP，则当从 OPENXML 行集中进行选择时，将忽略 XML 文档中现有的值并返回自动填充值。
ColPattern
是可选的通用 XPath 模式，它说明应如何将 XML 节点映射到列。如果没有指定 ColPattern，那么发生默认的映射（由 flags 指定的以特性为中心或以元素为中心的映射）。
指定为 ColPattern 的 XPath 模式用于指定特殊的映射性质（如果发生以特性为中心和以元素为中心的映射），这些特殊的映射性质可以重写或增强由标志所指定的默认映射。
指定为 ColPattern 的通用 XPath 模式也支持元属性。
MetaProperty
是由 OPENXML 提供的元属性之一。如果指定元属性，则该列包含元属性提供的信息。这些元属性使您得以析取 XML 节点的信息（如相对位置、命名空间信息），以提供比文本化表示法更详细的信息。
TableName
如果具有期望架构的表已经存在且不要求列模式，则为给定的表名（而不是 SchemaDeclaration）。
通过使用 SchemaDeclaration 或者指定一个现有的 TableName，WITH 子句提供一种行集格式（根据需要还提供其它映射信息）。如果没有指定可选的 WITH 子句，那么以 edge table 格式返回结果。边缘表在单个表中表示 XML 文档的细密结构（例如，元素/特性名、文档层次结构、命名空间、PI 等）。
下表描述边缘表的结构。
列名
数据类型
描述
id
bigint
是文档节点的唯一 ID。
根元素的 ID 值为 0。保留负 ID 值。
parentid
bigint
标识节点的父节点。此 ID 所标识的父节点不一定是父元素，而是取决于此 ID 所标识节点的子节点的 NodeType。例如，如果节点是文本节点，则其父节点可能是特性节点。
如果节点位于 XML 文档的顶层，则其 ParentID 为 NULL。
nodetype
int
标识节点类型。是对应于 XML DOM 节点类型编号的整数（有关节点信息，请参见 DOM）。
三种节点类型是：
1 = 元素节点
2 = 特性节点
3 = 文本节点localname
nvarchar
给出元素或特性的本地名称。如果 DOM 对象没有名称则为 NULL。
prefix
nvarchar
是节点名称的命名空间前缀。
namespaceuri
nvarchar
是节点的命名空间 URI。如果值是 NULL，则命名空间不存在。
datatype
nvarchar
是元素或特性行的实际数据类型，否则是 NULL。从内嵌 DTD 中或从内嵌架构中推断数据类型。
prev
bigint
是前一个兄弟元素的 XML ID。如果前面没有兄弟元素则为 NULL。
text
ntext
包含文本格式的特性值或元素内容（如果边缘表项不需要值则为 NULL）。
示例
A.使用带有 OPENXML 的 SELECT 语句。
下面的示例使用 sp_xml_preparedocument 创建 XML 图像的内部表示。然后对 XML 文档的内部表示法执行使用 OPENXML 行集提供程序的 SELECT 语句。
flag 值设置为 1，表示以特性为中心的映射。因此，XML 特性映射到行集中的列。指定为 /ROOT/Customers 的 rowpattern 标识要处理的 <Customers> 节点。
没有指定可选的 colpattern（列模式），因为列名和 XML 特性名称匹配。
OPENXML 行集提供程序创建了一个双列行集（CustomerID 和 ContactName），SELECT 语句从该行集中检索必要的列（在本例中检索所有的列）。
DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc =<ROOT><Customer CustomerID=\"VINET\" ContactName=\"Paul Henriot\"><Order CustomerID=\"VINET\" EmployeeID=\"5\" rderDate=\"1996-07-04T00:00:00\"><OrderDetail rderID=\"10248\" ProductID=\"11\" Quantity=\"12\"/><OrderDetail rderID=\"10248\" ProductID=\"42\" Quantity=\"10\"/></Order></Customer><Customer CustomerID=\"LILAS\" ContactName=\"Carlos Gonzlez\"><Order CustomerID=\"LILAS\" EmployeeID=\"3\" rderDate=\"1996-08-16T00:00:00\"><OrderDetail rderID=\"10283\" ProductID=\"72\" Quantity=\"3\"/></Order></Customer></ROOT>--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement that uses the OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, /ROOT/Customer,1)WITH (CustomerID varchar(10),ContactName varchar(20))下面是结果集：
CustomerID ContactName---------- --------------------VINET Paul HenriotLILAS Carlos Gonzlez如果将 flags 设置为 2（表示以元素为中心的映射）并执行相同的 SELECT 语句，由于 <Customers> 元素没有任何子元素，则对于 XML 文档中两个 Customer 的 CustomerID 和 ContactName 的值都作为 NULL 返回。
下面是结果集：
CustomerID ContactName---------- -----------NULL NULLNULL NULLB. 为列和 XML 特性之间的映射指定 ColPattern
下面的查询从 XML 文档返回客户 ID、订单日期、产品 ID 和数量等特性。rowpattern 标识 <OrderDetail> 元素。ProductID 和 Quantity 是 <OrderDetails> 元素的特性。而 CustomerID 和 OrderDate 是父元素 (<Orders>) 的特性。 查看(287) 评论(0) 收藏 分享 管理
存储过程与XML参数
2009-07-31 10:28:37
v 写在章节前面
存储过程是一段可执行的服务端程序。不同于用户定义函数(UDF)，存储过程允许有副作用
，也就是说可以修改表中的数据，甚至可以修改对象的架构。
v 存储过程种类
SQL Server2005支持不同类型的存储过程：用户定义存储过程、系统存储过程和扩展存储
过程。你可以使用T-SQL或CLR开发用户定义存储过程。
用户定义存储过程
用户定义存储过程在用户数据库中创建，通常与数据库对象进行交互。要调用一个用户定义存
储过程，指定EXEC命令和限定架构的存储过程名称以及参数：
EXEC dbo.usp_Proc1 <args>
v 存储过程接口
下面我们介绍存储过程接口(即输入和输出参数)。
输入参数
你可已在存储过程头定义输入参数。当调用存储过程时，必须为输入参数提供值，除非使用默认值。下面的代码创建存
储过程usp_GetCustOrders，他接受一个消费者ID和一个时间范围作为输入，并返回消费者在指定时间范围内的订单。
USE Northwind
GO
IF OBJECT_ID(‘dbo. usp_GetCustOrders’) IS NOT NULL
DROP PROC dbo.usp_GetCustOrders;
GO
CREAET PROC dbo.usp_GetCustOrders
@custid AS NCHAR(5)
@fromdate AS DATETIME = ‘19000101’
@todate AS DATATIME = ‘99991231’
AS
SET NOCOUNT ON;
SELECT OrderID,CustomerID,EmployeeID,OrderDate
From dbo.Orders
Where CustomerID = @custid
And OrderDate >= @fromdate
And OrderDate <@todate
GO
【注】SET NOCOUNT ON通知SQL Server不要生成表示受影响行数的消息。一些客户端数据库接口，如OLEDB，会把该消息作为一个行集。结果，你得到的第一个结果集将是受影响行数的消息，而你只想获取查询的结果集。通过SET NOCOUNT ON就可以避免在这些接口中出现这种问题。
当调用存储过程的时候，你必须为那些在定义中未提供默认值的输入参数指定输入值。调用存储过
程时有两种为参数赋值的格式：非命名格式和命名格式。在非命名格式中，我们不需要指定参数名，只
需要指定参数值，但必须按参数声明的顺序指定输入。我们可以忽略那些包含默认值且位于参数列表末
尾的参数，但是却不能忽略已经为其提供值的两个参数之间的参数。如果想让参数使用默认值，需要为
该参数指定DEFAULT关键字。
下面我们只传第一个参数，后两个用默认值
EXEC dbo.usp_GetCustOrders N’ALFKI’;
如果你想为第三个参数指定值，但让第二个参数使用默认，应为第二个参数指定DEFAULT
EXEC dbo.usp_GetCustOrders N’ALFKI’ , DEFAULT , ‘20060212’
使用非命名赋值格式会导致很多维护方面的问题。如，必须按顺序指定参数，不能忽略可选参数等
。因此使用命名赋值格式是一个好的习惯：
EXEC dbo.usp_GetCustOrders
@custid = N’ALFKI’
@fromdate = ‘19970201’
@todate = ‘19980101’
输出参数
输出参数允许你从存储过程返回输出值。
下面我们修改dbo. usp_GetCustOrders存储过程，为其添加输出参数@numrows
ALTER PROC dbo.usp_GetCustOrders
@custid AS NCHAR(5)
@fromdate AS DATETIME = ‘19000101’
@todate AS DATATIME = ‘99991231’
@numrows AS INT OUTPUT
AS
SET NOCOUNT ON;
DECLARE @err AS INT
SELECT OrderID,CustomerID,EmployeeID,OrderDate
From dbo.Orders
Where CustomerID = @custid
And OrderDate >= @fromdate
And OrderDate <@todate
SELECT @numrows = @@rowcount, @err = @@error;
return @err;
GO
Set与Select在赋值时的不同
set
select
同时对多个变量同时赋值
不支持
支持
表达式返回多个值时
出错
将返回的最后一个值赋给变量
表达式未返回值
变量被赋
操纵数据
2009-07-31 10:22:15
本章的内容包括：
1. T-SQL对like和通配符的扩充
2. 字符串操作
3. 数据类型转换和convert函数
4. 日期时间的处理
5. 数学函数、集合函数和isnull
6. order by子句和compute子句
9.1 T-SQL对like子句和通配符的扩充
1. like谓词与通配符
在select子句的where筛选器或having筛选器中，使用like谓词来确定与括在引号‘’或“”中、可能包含通配符的字符串相匹配的数据。
通配符用于匹配串中，代表一个或多个字符，或一个字符的取值范围或集合。它必须与like谓词一起使用。
2. ANSI-89 SQL标准中的通配符
在AINSI-89 SQL标准中，通配符包括：
1. % 表示任意个字符的字符串，也可以表示没有字符
2. _ 表示任一单个字符
3. T-SQL对通配符的扩充
T-SQL扩充了两个表示字符取值范围或集合的通配符：
1. [ ]表示任意在给定范围或集合内的单个字符，如[a-z]或[abc]
2. [^]表示任意不再跟定范围或集合内的单个字符，如[^a-z]
4. 在like谓词中的escape子句
4.1 转义字符和escape子句
在like匹配串中，通配符% , _ , [] , [^]具有特殊的意义而被作为保留字符。但在like匹配串中，包含作为普通字符而不是具有特殊意义的通配符时，就需要用一个专用字符来指定，这个专用字符被称为转义字符。转义字符在like谓词中用escape子句来定义。
4.2 ANSI-89 SQL标准的转义字符定义方式
使用like谓词中的 escape来定义转义字符，下面我们要查找name列以’_bo’结尾的行：
select *
from dbo.Employee
where Name like '%/_bo' escape '/'
1. 转义字符可以是任意的单个字符
2. 转义字符只作用于紧随其后的一个字符，这个字符必须是通配符或转义字符本身
4.3 T-SQL对转义字符定义方法的扩充
T-SQL的转义字符定义方法有两种，除上面说的escape方法外，T-SQL扩充使+用[]作为转义字符。即在T-SQL的like谓词中，对于通配符% , _ , [ , ^若作为一个普通字符时，可以不适用escape子句来指定转义字符，而使用将该字符扩在方括号中来表示它是一个普通字符。而右括号]不需要使用转义字符，使用它自己即可。
9.2 字符串操作
常用的字符串函数
函数
语法
举例
substring
Substring(expression,start,length)
right
Right(char_expr,length)
upper
Upper(char_expr)
lower
Lower(char_expr)
charindex
Charindex(expression,expression)
patindex
PATINDEX(pattern,expression)
ascii
Ascii(char_expr)
char
Char(int_expr)
Ltrim
ltrim(char_expr)
删除起始空格后返回字符表达式
Rtrim
rtrim(char_expr)
截断所有尾随空格后返回一个字符串
space
Space(integer_expr)
返回由重复的空格组成的字符串。
str
Str(approx_numeric,length,decimal)
查看(205) 评论(0) 收藏 分享 管理 修改数据
2009-07-31 10:21:10
Summary and Statistical Technique
2009-07-31 10:09:32
本章节我们将会解决如下问题：
1. 找出重复数据
2. 找出the most common value
3. 计算累计总值
4. 为行编号
5. 从一个表中选择top n行
在这个过程中，我们会学习很多关于Group By跟Having的知识。
How can I find authors with the same last name?
Select lastname, number_dups = count(*) from authors group by lastname having count(*)>1
【注1】 在select列表中的列必须是在group by中出现的，或是聚合函数，像count()等。
【注2】 count(*)等聚合函数作用于的是每一个分组中，而不一定是所有的行。
【注3】 在没有外连接等的情况下，count(*)比count(列名)效率快。
How can I find the value that occurs most frequently within a set of data?
Select total_sales, ccurs = count(*)
From titles
Group by total_sales
Having count(*) = max(count(*)) --这种方式在SQL Server好像不行，我换成了下面的方式
select top 1 age , COUNT(*)
from dbo.Employee
group by Age
order by COUNT(*) desc
How can I create cumulative total?
id
sales
1
20
2
10
3
50
4
22
5
32
6
48
7
12
Join the table to itself using ‘<=’join condition,group rows by key and data value and compute sum of data value within groups
select x.id,total = SUM(y.sales)
from dbo.titles x,dbo.titles y
where y.id <= x.id
group by x.id
How can I add row numbers to my result set?
以上表为例
本质上与上面的累计总和一样，不过就是把sum改成了count
select x.id,line_no = COUNT(y.sales)
from dbo.titles x,dbo.titles y
where y.id <= x.id
group by x.id
How can I flag non-contiguous data?
date
2009-07-27 20:24:00.000
2009-07-27 20:27:42.020
2009-07-27 20:29:42.023
2009-07-27 20:30:42.023
2009-07-27 20:31:42.027
2009-07-27 20:32:42.027
select datebefore = x.date,dateafter = MIN(y.date)
from dbo.sequentialData x,dbo.sequentialData y
where x.date < y.date
group by x.date
having DATEDIFF(MI,MIN(y.date),x.date) <> -1
Ranking
要求从数据库选择前几行(Top n)，这是一个经常性的问题，有几种方式可以解决这个问题：
1. 使用set rowcount n来限制查询返回的行数
2. 使用游标遍历结果集
3. 使用select top n
每种方式都有自己的优缺点，这需要根据实际情况来决定使用哪种方式
使用ROWCOUNT方式
显示按销量排列在前5的书
条件逻辑
2009-07-31 10:05:49
Datetime列跟T-SQL函数
2009-07-31 09:59:36
在T-SQL中，datetime数据类型在列中存储date和time
数据类型
大小
范围
例子
Datetime
8 bytes
>=Jan 1, 1753 00:00
Smalldatetime
4 bytes
Jan 1, 1900 00:00 to June 6, 2079 00:00
关于日期的提醒：
1. 如果未显示指定时间，默认为12:00 AM
2. 如果未显示指定日期，默认为Jan 1 1900
Select Dates
如何选出所有March 20，1991的订单
表中数据如下
Stored_id
Ord_num
date
5023
136312
Mar 21 1991 12:10AM
5023
006313
Mar 21 1991 12:20AM
5023
313212
Mar 21 1991 12:30AM
8042
313131
Mar 20 1991 12:40AM
8042
313123
Mar 20 1991 1:10AM
8042
949652
Mar 20 1991 1:10AM
Solution 1 ：
Select stor_id , ord_num , date from sales where data = ‘3/20/1991’
这个方案不会得到任何的输出，因为查询条件’ data = ‘3/20/1991’’假定默认的时间是12:00AM
Solution 2 :
Select stor_id , ord_num , date from sales where date >= ‘3/20/1991’ AND date < ’3/21/1991’
这个方案会得到最后三行。
日期的默认格式如下：
1. 当使用分隔符的时候默认是mdy
2. 当未使用分隔符的时候默认是ymd
当使用分隔符时，我们可以使用SET FORMAT来规定每个日期部分的格式，参数可以是：
mdy , dmy , ymd , ydm , myd , dym
例子：
Set dateformat dmy
Select stor_id , ord_num , date from sales where date >= ‘20/3/1991’ AND date < ’21/3/1991’
Solution 3 ：
Select stor_id , ord_num , date from sales where datediff(dd , date , ‘3/20/1991’) = 0
我们可以指定datepart作为datename , datepart , datediff , dateadd的参数，从而仅仅获取日期值的一部分：
Datepart
Argument
Range
Year
yy
1753-9999
Quarter
qq
1-4
Month
mm
1-12
Dayofyear
dy
1-366
Day
dd
1-31
Week
wk
1-53
Weekday
查看(285) 评论(0) 收藏 分享 管理 Good Coding Practices
2009-07-31 09:52:07
标题搜索
我的存档
数据统计
- 访问量: 25106
- 日志数: 49
- 建立时间: 2008-02-23
- 更新时间: 2009-10-13
RSS订阅
