如何指定View中的欄位定序

1.確認目前資料庫定序

SELECT DATABASEPROPERTYEX ('ricotest1' ,'Collation' )

CI: 指定不區分大小寫，CS 指定區分大小寫。
AI 指定不區分腔調字，AS 指定區分腔調字。
2.Create Source Table and Insert Data

create table mytest

(

c1 varchar(10),

c2 varchar(10)

)

insert mytest select 'rico','123 abc'

insert mytest select 'ricochen','456 def'

insert mytest select 'RICOCHEN','456 DEF'

insert mytest select 'RICO','123 ABC'

select * from mytest

  查詢 Table (c1=rico ,c2 like '%def')

select * from mytest

where c1='rico'

go

select * from mytest

where c2 like '%def'

go

 因為來源c1和c2欄位定序為不區分大小寫。
3.我希望在View中c1和c2兩個欄位都可以區分大小寫

create view dbo.v_mytest

as

SELECT

c1 COLLATE Chinese_Taiwan_Stroke_CS_AS AS c1_CS,

c2 COLLATE Chinese_Taiwan_Stroke_CS_AS AS c2_CS

FROM dbo.mytest;

 4.查詢View (c1=rico ,c2 like '%def')
 這次查詢的結果才是我要的。