I want to only selects rows from a table where the last name of the employees stands with a lowercase letter. However, when I run the query below
SELECT empid, lastname
WHERE lastname COLLATE Latin1_General_CS_AS LIKE '[a-z]%';
According the SQL Server Books Online, the characters included in range searches depend on the sorting rules of the collation. Collation Latin1_General_CS_AS uses dictionary order so both upper and lower case characters of the specified range are included.
Specify a binary collation to get the behavior you want (code point range):
SELECT empid, lastname FROM HR.Employees WHERE lastname COLLATE Latin1_General_BIN LIKE '[a-z]%';