-- Select database to create these objects USE [YourDatabaseName] GO -- Create the table CREATE TABLE [dbo].[CaseSensitiveTest] ( [UID] [int] NOT NULL , [Value1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DescValue1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO -- Add primary key ALTER TABLE [dbo].[CaseSensitiveTest] WITH NOCHECK ADD CONSTRAINT [PK_CaseSensitiveTest] PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO -- Select server collation and sort information SELECT SERVERPROPERTY('Collation'), SERVERPROPERTY('SqlSortOrder') GO -- Insert test records INSERT INTO CaseSensitiveTest VALUES (1, 'TEST', 'All Upper Case') INSERT INTO CaseSensitiveTest VALUES (2, 'test', 'All Lower Case') INSERT INTO CaseSensitiveTest VALUES (3, 'Test', 'Mixed Case') INSERT INTO CaseSensitiveTest VALUES (4, 'TEST1', 'All Upper Case') INSERT INTO CaseSensitiveTest VALUES (5, 'test1', 'All Lower Case') INSERT INTO CaseSensitiveTest VALUES (6, 'Test1', 'Mixed Case') INSERT INTO CaseSensitiveTest VALUES (7, '123Test', 'Mixed Case') -- Select all records where value like Test SELECT * FROM dbo.CaseSensitiveTest WHERE Value1 LIKE '%Test%' GO -- Select all records where value like Test SELECT * FROM dbo.CaseSensitiveTest WHERE Value1 LIKE '%Test%' COLLATE SQL_Latin1_General_Cp1_CS_AS GO