Use of Case Sensitive result in where clause of SQL Server

An example of using the Case Sensitive result in the Where clause column in the result set is as follows:

1. Create a table and insert data into the table:

CREATE TABLE [dbo].[test_new](
	[name] [varchar](10) NULL
);
GO

-- Insert different case value of same kind as shown below:
insert into test_new values('TEST');
insert into test_new values('Test');
insert into test_new values('test');

2. Example of where condition as follows:

select * from test_new where name like '%Test%'

Name
---------
TEST
Test
test

3. Check the database collation setting:

SELECT SERVERPROPERTY('Collation')
-------
SQL_Latin1_General_CP1_CI_AS

4. CI means not case sensitive, So we need to mention in the query to handle case-sensitive data as follows:

select * from test_new where name COLLATE SQL_Latin1_General_Cp1_CS_AS like '%Test%'

Name
-----
Test