--
生成测试数据
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ dbo ] . [ test ] (
[ name ] [ varchar ] ( 20 ) NULL ,
[ quarter ] [ int ] NULL ,
[ profile ] [ int ] NULL ,
[ dates ] [ smallint ] NULL ,
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
CONSTRAINT [ PK_test ] PRIMARY KEY CLUSTERED
(
[ ID ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [ dbo ] . [ test ] ON
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' a ' , 1 , 1000 , 421 , 1 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' Aa ' , 2 , 2000 , 421 , 2 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' AA ' , 3 , 4000 , 421 , 3 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' A ' , 4 , 5000 , 421 , 4 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 1 , 3000 , 421 , 5 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 2 , 3500 , 421 , 6 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 3 , 4200 , 421 , 7 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 4 , 5500 , 421 , 8 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' A ' , 1 , 1000 , 421 , 9 )
SET IDENTITY_INSERT [ dbo ] . [ test ] OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ dbo ] . [ test ] (
[ name ] [ varchar ] ( 20 ) NULL ,
[ quarter ] [ int ] NULL ,
[ profile ] [ int ] NULL ,
[ dates ] [ smallint ] NULL ,
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
CONSTRAINT [ PK_test ] PRIMARY KEY CLUSTERED
(
[ ID ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [ dbo ] . [ test ] ON
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' a ' , 1 , 1000 , 421 , 1 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' Aa ' , 2 , 2000 , 421 , 2 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' AA ' , 3 , 4000 , 421 , 3 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' A ' , 4 , 5000 , 421 , 4 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 1 , 3000 , 421 , 5 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 2 , 3500 , 421 , 6 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 3 , 4200 , 421 , 7 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 4 , 5500 , 421 , 8 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' A ' , 1 , 1000 , 421 , 9 )
SET IDENTITY_INSERT [ dbo ] . [ test ] OFF
--
sql server 查询区分大小写 collate Chinese_PRC_CS_AS_WS
select * from test where [ name ] = ' A ' collate Chinese_PRC_CS_AI
select * from test where ascii (name) = ascii ( ' A ' )
select * from test where [ name ] = ' A ' collate Chinese_PRC_CS_AI
select * from test where ascii (name) = ascii ( ' A ' )
(图:效果图)