case sensitive selection by using Collation Clause

1) Create temporary table to insert values to check the implemenation

Create Table #temp
( ID int identity(1,1)
, val1 varchar(50)
)

2) Values insertion
Insert into #temp values('abc')
Insert into #temp values('ABC')
Insert into #temp values('aBc')

3) Selection by using collation function
select * from #temp where val1 like 'abc%'
collate SQL_Latin1_General_CP1_CS_AS

4) above collation is only for general english, you can use the colloation accordingly.
5) you can view the all available collations by using the following statement.
SELECT * FROM fn_helpcollations()

6) General Syntax for Collate Clause
COLLATE { | database_default }

collation_name

Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified Windows_collation_name or a SQL_collation_name.

database_default

Causes the COLLATE clause to inherit the collation of the current database.