65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL 字母数字排序（使用 CLR 集成）

shteff
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.89/5 (8投票s)

2009年4月5日

CPOL

5分钟阅读

viewsIcon

31978

downloadIcon

203

如何在 SQL Server 中对字母数字字符串进行排序。

引言

最近，我创建了一个 Web 应用程序来跟踪 Civic Screens 的作业。该应用程序需要一个“作业编号”，但它实际上并非一个纯数字。作业编号是一个数字，可能前面带有字母前缀。例如，以下都是有效的作业编号：J123、W34、1、5000 等。应用程序交付后，他们要求进行增强，能够按作业编号范围进行搜索，例如，所有介于 J1 - J500 之间的作业编号。鉴于应用程序已交付，并且 JobNumber 列被设置为 nvarchar(20) 类型，我希望找到一种快速实现此目的的方法。因此，将作业编号拆分为字母和数字列是不可能的。

解决方案

在快速搜索 Google 以了解过去实现过哪些解决方案后，我偶然发现了 CodeProject 上由 Trent Tobler 撰写的一篇文章。它是我的问题的部分解决方案。他已经实现了一种排序方法，我只需要尽可能高效地将其集成到 SQL Server 中。诚然，最终解决方案不是最佳的，也不是最有效率的实现方法；我猜测，最好的方法可能是将作业编号拆分为字母和数字列。由于时间限制以及希望尽可能少地进行更改，我决定采用 Alpha Numeric Sort 并使用 SQL Server 的 CLR 功能。

我将跳过代码如何生成可排序字符串的部分，因为在很大程度上，代码没有从 Trent Tobler 的文章中进行更改。唯一进行的更改是扩展方法已更改为简单的静态方法。我还将字段设置为只读，以便于集成到 SQL Server 中。SQL Server 不允许在使用安全程序集添加程序集时使用非只读的静态字段。最后的更改是删除方法重载，因为同样的原因，SQL Server 不允许将用作 SQL 函数的方法进行重载。

设置 SQL Server 2008

设置 SQL Server 以使用已编译的程序集是一项相对简单的任务，Microsoft 提供了大量关于如何执行此操作的指南。我基本上遵循了“创建 CLR 函数”上的说明和链接来弄清楚该怎么做。我使用这些说明配置了 SQL Server 2008 Express 版本，但它应该适用于任何大于 2005 的 SQL Server 版本，无论是 Express 版还是完整版。总结这篇文章和链接，基本步骤是：

  1. 配置 SQL Server 2008 以启用 CLR 集成。
    2. sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
  2. 创建 SQL Server 程序集。
    3. Create ASSEMBLY AlphaNumeric 
FROM 'SQLAlphaNumericSort.dll'
WITH PERMISSION_SET = SAFE;
GO
  3. 创建 SQL Server 函数。
    4. CREATE FUNCTION [dbo].GetAlphaNumericOrderToken (@str nvarchar(40))
RETURNS nvarchar(50)
AS EXTERNAL NAME [AlphaNumeric].[AlphaNumericSort].[GetAlphaNumericOrderToken];
GO

SQL Server CLR 集成

我将在文章最后分享一些关于 SQL Server CLR 集成的实用知识。

首先需要注意的是，SQL Server 在 SQL Server 中创建程序集时并不会链接到 .NET 程序集。相反，它会复制一份程序集。因此，在再次将程序集添加到数据库之前，对 .NET 程序集所做的任何更改都不会反映在数据库中。可以通过以下方式完成：

Alter ASSEMBLY AlphaNumeric 
FROM 'SQLAlphaNumericSort.dll'
WITH PERMISSION_SET = SAFE;
GO

但这也有一些限制。引用的方法的签名不能更改。如果更改了，并且这可能很麻烦，则必须删除所有使用 .NET 程序集创建的对象（函数、存储过程、类型等）。然后必须删除程序集，最后重新创建程序集，然后重新创建数据库对象。我曾读过 SQL Compare 可以自动执行此操作，但我没有使用过该产品，因此不确定它是否真的有效。我对我使用 SQL Server CLR 功能的最小使用量不需要进行过多调查，因为我的麻烦就像针扎一样。

提高性能

我担心的主要问题是通过作业编号进行搜索和排序的性能影响，实际上我想要通过作业编号排序标记进行搜索和排序。经过一番研究，我发现可以创建一个持久化的计算列，并且可以在该列上创建索引。计算列显然意味着该列是使用另一列的值计算得出的。持久化部分意味着该值将被持久化存储在数据存储中，而不是在每次访问时计算（虚拟）。要对计算列创建索引，该列还必须是持久化的。有一个注意事项是，SQL 函数必须是确定性的。也就是说，对于相同的输入和数据库状态，总是会返回相同的结果。在理解确定性函数时，我发现举一个非确定性函数的例子更容易。例如，NOW() SQL 函数。 NOW() 函数的返回值不是确定的，因为它每次调用时都不会返回相同的结果。由于相同的作业编号将返回相同的排序标记，因此 GetAlphaNumericOrderToken 是确定性的。 CLR SQL 函数要求在函数上应用 [SqlFunction(IsDeterministic = true)] 属性，以便 SQL Server 将该函数视为确定性的。

ALTER TABLE dbo.Job ADD
JobNumberSortToken  AS ([dbo].[GetAlphaNumericOrderToken]([JobNumber])) PERSISTED
GO

CREATE NONCLUSTERED INDEX IX_JobNumber ON dbo.Job
    (
    JobNumberSortToken
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
© . All rights reserved.