select * from Person.Person
where FirstName = 'Ken'	COLLATE Latin1_General_CS_AS

Note that SQL is not case sensitive. However, it is a good practice to write the SQL keywords in CAPS and other names and variables in a small case.

-- find everyone with first_name contains d case insensitive manner
Make everthing either lower or upper case
SELECT FIRST_NAME , LAST_NAME
FROM EMPLOYEES
WHERE LOWER(FIRST_NAME) LIKE '%d%' ; 

-- Postgresql case insensitive:
SELECT * FROM people WHERE name ILIKE 'JOHN'
-- John
-- JOHN
-- john