SQL Server 2k & Case Sensitivity

[infinitium]

Hey guys...

Is MS SQL Server 2k case sensitive when searching an SQL query?

Eg: If I do a:

SELECT * from table_A where username = 'Smith'

and

SELECT * from table_A where username = 'smith'

What would happen if a "smith" exists in the d/b?

[joelwills]

No, smith, Smith, SMITH, SmItH etc are all the same things.

Not sure what you mean by the 2nd part? If 'Smith' and 'smith' exist in the database it would bring back both rows regardless of which variant of the word Smith you used.

[infinitium]

Thanks joelwills...

For the 2nd part, I meant that if an entry "smith" was in the d/b, what would both my queries return? I assume they should both return the entry.

[joelwills]

Yes, you're right. If smith was in the db, both your queries would return the same correct row.

[JimBolla]

To determine the case sensitivity of a server, execute the stored procedure sp_server_info, and then examine the contents of row 18. If the server has been installed with the case-insensitive setting, the option for sort_order will be set to nocase. You can run a stored procedure from the Query Analyzer.

By default, it is case-insensitive, but this is something that can be changed during the SQL install.

[infinitium]

great... thanks guys!