SQLSERVER2005 EXPRESS部分特性小结

最近研究了一下SQLSERVER数据库技术，现拿出来跟大伙分享，首先给出T-SQL脚本。 建立一张测试表： CREATE TABLE TestTable ( Column1 varchar ( 1 ), Column2 int ) 加入一些测试数据： INSERT INTO TestTable VALUES ( ' A ' , 1 ); INSERT INTO TestTable

    最近研究了一下SQLSERVER数据库技术，现拿出来跟大伙分享，首先给出T-SQL脚本。

建立一张测试表：

<p><span>CREATE</span><span>TABLE</span><span> TestTable (<br>   Column1 </span><span>varchar</span><span>(</span><span>1</span><span>),<br>   Column2 </span><span>int</span><span><br>)</span></p>

加入一些测试数据：

<p><span>INSERT</span><span>INTO</span><span> TestTable </span><span>VALUES</span><span> (</span><span>'</span><span>A</span><span>'</span><span>, </span><span>1</span><span>);<br></span><span>INSERT</span><span>INTO</span><span> TestTable </span><span>VALUES</span><span> (</span><span>'</span><span>A</span><span>'</span><span>, </span><span>1</span><span>); </span><span>--</span><span> 重复</span><span><br></span><span>INSERT</span><span>INTO</span><span> TestTable </span><span>VALUES</span><span> (</span><span>'</span><span>A</span><span>'</span><span>, </span><span>2</span><span>);<br></span><span>INSERT</span><span>INTO</span><span> TestTable </span><span>VALUES</span><span> (</span><span>'</span><span>B</span><span>'</span><span>, </span><span>1</span><span>);<br></span><span>INSERT</span><span>INTO</span><span> TestTable </span><span>VALUES</span><span> (</span><span>'</span><span>B</span><span>'</span><span>, </span><span>2</span><span>);<br></span><span>INSERT</span><span>INTO</span><span> TestTable </span><span>VALUES</span><span> (</span><span>'</span><span>B</span><span>'</span><span>, </span><span>2</span><span>); </span><span>--</span><span>重复</span><span><br></span><span>INSERT</span><span>INTO</span><span> TestTable </span><span>VALUES</span><span> (</span><span>'</span><span>C</span><span>'</span><span>, </span><span>2</span><span>)</span></p>

使用sqlserver2005新特性删除重复记录：

<p><span>DELETE</span><span><br></span><span>FROM</span><span>  TestTable<br></span><span>WHERE</span><span> TestTable.</span><span>%%</span><span>lockres</span><span>%%</span><span><br>      </span><span>NOT</span><span>IN</span><span> (</span><span>SELECT</span><span>MIN</span><span>(b.</span><span>%%</span><span>lockres</span><span>%%</span><span>)<br>              </span><span>FROM</span><span>   TestTable b<br>              </span><span>GROUP</span><span>BY</span><span> b.column1, b.Column2)</span></p>

sqlserver2008新特性删除重复记录：

<p><span>DELETE</span><span><br></span><span>FROM</span><span>  TestTable<br></span><span>WHERE</span><span> TestTable.</span><span>%%</span><span>physloc</span><span>%%</span><span><br>      </span><span>NOT</span><span>IN</span><span> (</span><span>SELECT</span><span>MIN</span><span>(b.</span><span>%%</span><span>physloc</span><span>%%</span><span>)<br>              </span><span>FROM</span><span>   TestTable b<br>              </span><span>GROUP</span><span>BY</span><span> b.column1, b.Column2);</span></p>

当然了如果TestTable 具备主键（假设主键为ID且identity(1,1))，我们可以通过传统的T-SQL语句获取不重复的记录：

<p><span>--</span><span>建立主键</span><span><br></span><span>alter</span><span>table</span><span> TestTable </span><span>with</span><span>nocheck</span><span>add</span><span> <br>id </span><span>int</span><span>identity</span><span>(</span><span>1</span><span>,</span><span>1</span><span>) </span><span>primary</span><span>key</span><span><br></span><span>go</span><span><br><br></span><span>--</span><span>取唯一记录</span><span><br></span><span>select</span><span>min</span><span>(ID)</span><span>from</span><span> TestTable<br></span><span>group</span><span>by</span><span> column1, column2<br></span><span>go</span></p>

SQLSERVER2005随机取数据：

<p><span>select</span><span>top</span><span>3</span><span>*</span><span>from</span><span> TestTable </span><span>order</span><span>by</span><span>newid</span><span>() </span></p>

上述语句每次从TestTable内取出的3条随机记录。

SQLSERVER2005 row_number 函数：

该函数主要用于sql分页方面，给出简单范例代码：

<p><span>select</span><span>*</span><span>from</span><span>(    <br>   </span><span>select</span><span> id,   column1, column2,   row_number() </span><span>OVER</span><span>(</span><span>order</span><span>by</span><span> id </span><span>desc</span><span> ) </span><span>as</span><span> row </span><span>from</span><span> TestTable<br>) a    <br></span><span>where</span><span>   row   </span><span>between</span><span>1</span><span>and</span><span>3</span></p>

这里id为 TestTable 的自增主键，每次按主键id降序后取前三条数据行。

查看SQLSERVER2005版本：

<p><span>SELECT</span><span>  SERVERPROPERTY(</span><span>'</span><span>productversion</span><span>'</span><span>), SERVERPROPERTY (</span><span>'</span><span>productlevel</span><span>'</span><span>), SERVERPROPERTY (</span><span>'</span><span>edition</span><span>'</span><span>)</span></p>

这里给出不同版本的SQLSERVER2005版本：

RTM版：

sp3版：

SQLSERVER2005所有版本：

发行版	Sqlservr.exe
RTM	2005.90.1399
SQL Server 2005 Service Pack 1	2005.90.2047
SQL Server 2005 Service Pack 2	2005.90.3042
SQL Server 2005 Service Pack 3	2005.90.4035

最后，希望本文可以给您带来帮助。