MSSQL condition query and case sensitivity

If exists select, else  insert

if not exists (select 1 from  [ClinRefFileTypeMaster] where [ClinRefTypeName] =@name)
begin
Insert into [ClinRefFileTypeMaster] ([ClinRefTypeName]) values (@name) 
end
else
begin
select (as desired) from ClinRefFileTypeMaster where where [ClinRefTypeName] =@name
end

https://stackoverflow.com/questions/32095623/return-id-if-record-exist-else-insert-and-return-id/32095900

MSSQl case sentitive?

depends on collation, default collation ends with _cs is case insensitive _cs means case

case insensitive 

utf8_general_ci  makes it case insensitive, where CI means case insensitive

Comparisons are case insensitive when the column uses a collation which ends with _ci (such as the default latin1_general_ci collation) and they are case sensitive when the column uses a collation which ends with _cs or _bin (such as the utf8_unicode_cs and utf8_bin collations).

https://stackoverflow.com/questions/3936967/mysql-case-insensitive-select