易截截图软件、单文件、免安装、纯绿色、仅160KB

SQL Server 2005: Recursive Hierarchies to XML

Suppose we have a recursive hierarchy stored in a relational database and we want to write it to XML.  This might be for a variety of reasons – e.g. as a pre-cached input to a UI control, to export to another system using a pre-defined format, etc.
 
In SQL Server 2000, in order to get it straight to XML using FOR XML EXPLICIT, we would have to know the depth of the lowest node beforehand (without doing some very ugly dynamic SQL), so this does not help us.
 
It would be useful to access the data in the same order that it will appear in the XML.  I.e.
Node1
Node2
Node3
Node4
Node5
 
Getting at the data in this order will allow us to iterate through the nodes in sequential order.  This avoids using the DOM and is significantly quicker and more efficient as it avoids loading the whole structure into memory.
 
We could achieve this in SQL Server 2000 using a recursive table-valued UDF.  In SQL Server 2005, we also have the option of using a recursive Common Table Expression (CTE) to achieve the same functional result.  Let’s compare the two ways of doing it.
 
A CTE is a temporary named resultset referenced by a subsequent “outer query”.  They can provide similar functionality to views and derived tables, but their real value is in recursive queries.  Recursive CTE’s contain an “anchor member” and a “recursive member”, which are connected by a UNION ALL operator.  They can be encapsulated by UDFs for reusability.
 
 
 
Data Preparation
 
Let’s create a table and insert hierarchical values.
 
CREATE TABLE Employees
(
  empid   int         NOT NULL,
  mgrid   int         NULL,
  empname varchar(25) NOT NULL,
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT F


相关文档:

SQL位运算

SQL位运算
select 2|8       --10
select 2|8|1    --11
select 10&8    --8,包含,10=8+2
select 10&2    --2,包含,10=2+8
select 10&4    --0,不包含
select 19&16  --16,包含,19=16+2+1
s ......

在SQL Server 2005中用存储过程实现搜索功能


现在很多网站都提供了站内的搜索功能,有的很简单在SQL语句里加一个条件如:where names like ‘%words%’就可以实现最基本的搜索了。
    我们来看看功能强大一点,复杂一点的搜索是如何实现的(在SQL SERVER200/2005通过存储过程实现搜索算法)。
    我们 ......

将Excel导入SQL SERVER2005数据库

/*
本文专注于将Excel导入SQL SERVER2005数据库
此路径下的这个工具,是SQL SERVER2005 用来导入导出数据的工具。
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe
一般在数据库名上--右键-->Tasks-->Import Data -->界面就出来了,和点击上面的工具是一个东西。
首次使用这个D ......

让SQL SERVER突破2G内存限制


32
位的操作系统只能用
4G
的内存(不确定这句话是否正确),因为
2

32
次方是
4G
。默认的情况下,操作系统给自己留了
2G
,剩下的
2G
给应用程序。所以,每个应用程序所能使用的内存,最大不超过
2G
。据说可以改
WINDOWS

BOOT.INI
,强制操作系统只使用
1G
,即使这样,应用程序也至多是 ......

贴上收藏的SQL常用分页的办法~~

应一个朋友的要求,贴上收藏的SQL常用分页的办法~~
表中主键必须为标识列,[ID] int IDENTITY (1,1)
1.分页方案一:(利用Not In和SELECT TOP分页)
语句形式: 
SELECT TOP 页记录数量 *
from 表名
WHERE (ID NOT IN
  (SELECT TOP (每页行数*(页数-1)) ID
  from 表名
  ORDER BY ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号