When you install SQL Server, you can select a server collation. The default collation depends on your system locale. For instance with a Swedish system locale, the default will be Finnish_Swedish_CI_AS, which is a case-insensitive collation. (That's what CI stands for.) For other locales, the collation will be different. However, it will always be a CI collation.
The server collation then serves as the default collation for the database collation when you create new databases. And the database collation in its turn serves as the default collation of columns you create.
Thus, if you just go with the flow, you will have a case-insensitive collation and there is no need to worry.
So if you have a CS (case-sensitive) collation in your database, it is because someone deliberately choose that collation. I suggest that you talk to that person why that choice was made. It is still possible to override this choice for specific columns, but maybe that is over the top.