Good question; the explanation could have been better.
"To make this query case sensitive we need to either set the collation level to Case Sensitive or use 'COLLATE SQL_Latin1_General_CP1_CS_AS' in the select query with col2"
To me, the above sentence reads as if there are two methods. The second one is to force a different collation in the query (as demonstrated in the profivded code fragment); the first one is to, somehow, "set the collation level". And that, again: to me, sounds as if some SET statement would be used.
This may all be due to my English reading skills (I'm not a native English speaker), but in any case, I wanted to add a comment to clarify this in case ohers have the same misunderstanding,
First, there is no SET statement to control the default collation used in comparisons. The collation used in a comparison will always be the collation of the column used, unless an explicit COLLATE clause is used to override that.
The only way to make this query use a case sensitive collation without specifying it in the table is to change the column's collation. This can be done by adding a COLLATE clause to the CREATE TABLE (or ALTER TABLE) statement. Or it can be done by changing the default collation of the database (using the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement) and not specifying a collation when creating the table. Finally, the last way to get case sensitive comparisons by default is to specify a case sensitive collation when installing the SQL Server instance, then using the defaults when creating database and table. Changing the default collation for an already installed instance is very hard; it requires rebuilding the master.
The link in the explanation does not work, unfortunately. I think the author intended to post two links, but accidentally mashed them together. Here are the links I think he tried to supply:
* On MSDN, the official documentation on COLLATE: http://msdn.microsoft.com/en-us/library/ms184391.aspx
* A recent article on SQLServerCentral about case sensitive searches: http://www.sqlservercentral.com/blogs/nycnet/2012/01/19/case-sensitive-searches-in-t-sql/[/url]