在数据库应用开发中,经常会遇到需要对特定格式的数据进行排序和搜索的情况。例如,一个应用程序需要处理带有字母前缀的作业编号(Job Number),这些编号可能是"J123"、"W34"、"1"或"5000"等形式。在应用程序交付后,客户提出了一个增强需求,希望能够按作业编号的范围进行搜索,如查找所有在"J1"到"J500"之间的作业编号。由于作业编号字段(JobNumber)已经设置为nvarchar(20)
,并且应用程序已经交付,希望找到一个快速实现的方法,而不是将作业编号拆分为字母和数字两个列。
通过快速搜索,发现了Trent Tobler在CodeProject上发表的一篇文章,提供了一种排序方法,只需要将其高效地集成到SQL Server中。虽然最终的解决方案并非最佳,也不是最高效的方法,但考虑到时间和最小化更改的需求,决定采用字母数字排序,并使用SQL Server的CLR(公共语言运行时)特性。
将跳过代码生成可排序字符串的部分,因为大部分代码没有从Trent Tobler的文章中修改,只是将扩展方法更改为简单的静态方法。还设置了字段为只读,以便于集成到SQL Server中。SQL Server不允许在将程序集添加为安全程序集时使用非只读的静态字段。最后一个更改是移除了方法重载,因为SQL Server不喜欢将方法用作SQL函数时进行重载。
设置SQL Server以使用编译的程序集是一个相对简单的任务,Microsoft提供了大量关于如何操作的指导。基本上遵循了'CreatingCLRFunctions'上的说明和链接来弄清楚该怎么做。使用这些说明配置了SQL Server 2008 Express版,但它应该适用于任何大于2005版的SQL Server,无论是Express版还是完整版。总结文章和链接,基本步骤如下:
-- 启用CLR集成
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
-- 创建SQL Server程序集
CREATE ASSEMBLY AlphaNumeric FROM 'SQLAlphaNumericSort.dll' WITH PERMISSION_SET = SAFE;
-- 创建SQL Server函数
CREATE FUNCTION [dbo].GetAlphaNumericOrderToken (@str nvarchar(40)) RETURNS nvarchar(50)
AS EXTERNAL NAME [AlphaNumeric].[AlphaNumericSort].[GetAlphaNumericOrderToken];
在结束本文之前,想分享一些关于SQL Server与CLR集成的有用信息。首先需要注意的是,当在SQL Server中创建.NET程序集时,SQL Server不会链接到程序集,而是采取某种副本。因此,对.NET程序集所做的任何更改都不会反映在数据库中,直到程序集再次添加到数据库。这可以通过以下方式完成:
ALTER ASSEMBLY AlphaNumeric FROM 'SQLAlphaNumericSort.dll' WITH PERMISSION_SET = SAFE;
然而,这有一些限制。引用的方法签名不能更改。如果更改了,这可能会很痛苦,因为所有使用.NET程序集创建的对象(函数、存储过程、类型等)都必须删除。然后必须删除程序集,最后重新创建程序集,然后重新创建数据库对象。在某处读到SQL Compare可以为自动化这个过程,但没有使用过该产品,所以不确定它是否有效,以及它是否是一种有效的方法。在SQL Server中对CLR特性的最小使用量并不需要过多地调查这个问题,因为痛苦更像是轻微的刺痛。
ALTER TABLE dbo.Job ADD JobNumberSortToken AS ([dbo].[GetAlphaNumericOrderToken]([JobNumber])) PERSISTED;
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];