Q) When installing SQL Server 2005, do people just accept the collation defaults? Is collation a big decision?
This setting will define, for instance, if SQL will consider lowercase different from uppercase or if characters will accents will be considered different from the same characters without accents. The default setting is to be case insensitive and accent insensitive, which is OK for most scenarios.
This affects not only the way SQL sorts data but also other things, like handling of object names. If you set SQL to be case sensitive, a table named "Test" is different from a table named "test".
Changing the default collation after install time is still troublesome (you need to rebuild the master database), but these days, you can have different settings for each database or even a different setting for a specific column, which eases things. In older versions, you had a single setting that applied to all databases in that server.
Q) I noticed that when I typed the t-sql into sqlcmd it was case sensitive. I couldn't type "person.contact" but had to type "Person.Contact". Case sensitivity is great in C# 🙂 but terrible in T-SQL. Is this the way it is or can I change it to not be case sensitive? Maybe this is an AdventureWorks setting?
This is related to the collation. You probably installed the server with a case sensitive collation (which is not the default).
Read more on how to change collation and rebuild the system database at http://msdn2.microsoft.com/en-us/library/ms144259.aspx