SQL Server 笔记整理 其一

　　笔者之前比较熟悉的数据库是Mysql，虽然和SQL Server一样都是关系型数据库，但真正在开发环境下进行使用时还是有不小的区别。最近项目中使用的是SQL Server，正好借着这个机会来整理一些笔记吧。比较杂乱，见谅。

XML相关

　　1.生成xml

　　SQL Server是支持将查询的数据直接转为XML的格式的，具体的写法为在Where后面添加‘FOR XML ...’。例如：

　　

　　生成的xml为：

<TestTemp Id="1" Name="Nick" Age="18" hobby="game" IsInGroup="1"/>
<TestTemp Id="2" Name="Tom" Age="20" IsInGroup="1"/>
<TestTemp Id="3" Name="Jam" Age="23" hobby="music" IsInGroup="0"/>

　　注意这种写法写的第二条记录，因为hobby是null，所以生成的xml是没有显示出来这个字段的。其实SQL Server生成XML有多种写法。如利用For XML Path来控制生成的XML具体的格式。

　　如FOR XML PATH('')

<Id>1</Id>
<Name>Nick</Name>
<Age>18</Age>
<hobby>game</hobby>
<IsInGroup>1</IsInGroup>
<Id>2</Id>
<Name>Tom</Name>
<Age>20</Age>
<IsInGroup>1</IsInGroup>
<Id>3</Id>
<Name>Jam</Name>
<Age>23</Age>
<hobby>music</hobby>
<IsInGroup>0</IsInGroup>

　　FOR XML PATH('Result')，在Path中给出非空的参数，可以生成这个参数对应的model的xml.

<Result>
    <Id>1</Id>
    <Name>Nick</Name>
    <Age>18</Age>
    <hobby>game</hobby>
    <IsInGroup>1</IsInGroup>
</Result>
<Result>
    <Id>2</Id>
    <Name>Tom</Name>
    <Age>20</Age>
    <IsInGroup>1</IsInGroup>
</Result>
<Result>
    <Id>3</Id>
    <Name>Jam</Name>
    <Age>23</Age>
    <hobby>music</hobby>
    <IsInGroup>0</IsInGroup>
</Result>

　　注意这里生成的XML依旧没由包含Null值，如果想要显示null值的列，可以在后面加上‘ ,ELEMENTS XSINIL’，加上之后查询结果如下：

<Result
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Id>1</Id>
    <Name>Nick</Name>
    <Age>18</Age>
    <hobby>game</hobby>
    <IsInGroup>1</IsInGroup>
</Result>
<Result
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Id>2</Id>
    <Name>Tom</Name>
    <Age>20</Age>
    <hobby xsi:nil="true"/>
    <IsInGroup>1</IsInGroup>
</Result>
<Result
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Id>3</Id>
    <Name>Jam</Name>
    <Age>23</Age>
    <hobby>music</hobby>
    <IsInGroup>0</IsInGroup>
</Result>

　　SQL Server生成XML还有很多别的方式，如FOR XML RAW, FOR XML EXPLICIT(提供更多控制，也更复杂)等等。具体可以看看园子里这篇博文，传送门。

 

　　2. 解析XML

　　SQL Server中解析XML需要用到两个系统提供的函数，sp_xml_preparedocument和OPENXML。可以理解为sp_xml_preparedocument预处理xml文件并生成句柄，OPENXML利用这个句柄去读取具体的数据。

　　

　　这里有一个地方需要注意一下：SET @xmlResult = '<Top>' + @xmlResult + '</Top>'. 我们刚才利用FOR XML PATH('Result')生成的结果是有多个‘一级’元素的。这样OPENXML是不能识别的，会报错：

消息 6602，级别 16，状态 2，过程 sp_xml_preparedocument，行 1 [批起始行 12]
The error description is 'Only one top level element is allowed in an XML document.'.
消息 8179，级别 16，状态 5，第 25 行
Could not find prepared statement with handle 0.

　　

自增主键相关：

　　组内测试小姐姐有天报了个bug说创建用户失败了，最终debug到错位信息是插入数据时违反了主键的唯一性约束。但其实由与DB中设置了主键自增，在使用EF生成Entity时是不需要指定主键的。这时候可以利用这个命令来检查一下主键自增的顺序：

DBCC CHECKIDENT('[TestTemp]')

　　如果没有问题会给出提示并显示出最新的主键ID是多少：

Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

　　 

DBCC CHECKIDENT的用法比较简单，指定具体的表即可。但需要注意的是，不添加参数时这样写如果有错误会直接试图更正。一般应该使用DBCC CHECKIDENT('tableName'， NORESEED)，这种命令下即使有错误也不会纠正。如果希望指定新的值可以写成DBCC CHECKIDENT('tableName'， RESEED, newSeedValue).

但需要保证新的主键的值大于表中的主键ID的值，否则仍旧会报错。

　　另外，笔者遇到这个问题（即新生成的数据的主键ID小于表中已存在的主键）是因为试图向REPLACATION DB中插入数据，不清楚别的情况下是否会产生这种错误。