如果使用默認(rèn)排序規(guī)則選項(xiàng)安裝了SQL Server，則可能會(huì)發(fā)現(xiàn)以下查詢(xún)返回相同的結(jié)果：
CREATE TABLE mytable
(
mycolumn VARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT mytable VALUES('Case')
GO
SELECT mycolumn FROM mytable WHERE mycolumn='Case'
SELECT mycolumn FROM mytable WHERE mycolumn='caSE'
SELECT mycolumn FROM mytable WHERE mycolumn='case'
您可以通過(guò)在列級(jí)別強(qiáng)制排序規(guī)則來(lái)更改查詢(xún)：
SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE'
SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'case'
SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case'
-- if myColumn has an index, you will likely benefit by adding
-- AND myColumn = 'case'
SELECT DATABASEPROPERTYEX('<database name>', 'Collation')
由于更改此設(shè)置會(huì)影響應(yīng)用程序和SQL查詢(xún)，因此我將首先隔離此測(cè)試。從SQL Server 2000，您可以輕松地運(yùn)行ALTER TABLE語(yǔ)句來(lái)更改特定列的排序順序，從而使其必須區(qū)分大小寫(xiě)。首先，執(zhí)行以下查詢(xún)以確定將其更改回的內(nèi)容：
EXEC sp_help 'mytable'
在默認(rèn)情況下，第二個(gè)記錄集應(yīng)包含以下信息：
Column_Name排序規(guī)則
mycolumn SQL_Latin1_General_CP1_CI_AS
無(wú)論“歸類(lèi)”列返回什么，現(xiàn)在您都知道在進(jìn)行以下更改后需要將其更改回什么，這將強(qiáng)制區(qū)分大小寫(xiě)：
ALTER TABLE mytable
ALTER COLUMN mycolumn VARCHAR(10)
COLLATE Latin1_General_CS_AS
GO
SELECT mycolumn FROM mytable WHERE mycolumn='Case'
SELECT mycolumn FROM mytable WHERE mycolumn='caSE'
SELECT mycolumn FROM mytable WHERE mycolumn='case'
如果這使事情變?cè)?，則只需發(fā)出新的ALTER TABLE語(yǔ)句即可將其改回（確保將我的COLLATE標(biāo)識(shí)符替換為之前找到的標(biāo)識(shí)符）：
ALTER TABLE mytable
ALTER COLUMN mycolumn VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CI_AS
如果您對(duì)SQL Server 7.0感到困惑，則可以嘗試以下解決方法，這可能會(huì)對(duì)性能造成一些影響（您應(yīng)該只為FIRST匹配項(xiàng)獲得結(jié)果）：
SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10))
SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10))
SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10))
-- if myColumn has an index, you will likely benefit by adding
-- AND myColumn = 'case'
