By default, SQL Server will generate scripts like this:

CREATE TABLE [dbo].[Questionnary](
    [QuestionnaryId] [int] IDENTITY(1,1) NOT NULL,
    [QuestionnaryName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ActiveYN] [bit] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [RecordStamp] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [QuestionnaryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

This specifies the collation that should be used for the name field:

COLLATE SQL_Latin1_General_CP1_CI_AS

Some time ago I witnessed a situation in which collations created some serious problems between different database servers. Fortunately, this is quite rare so chances are that you will never run into such a situation.

A few days ago I encountered another interesting situation: for some reason, in one page the list of items was displayed in the wrong order. In all other pages the list was sorted correctly, and the only difference was that for this page the entries were sorted in the application code instead of the database.

A clue to the problem was the fact that the items contained special chars (like ', ., ", etc).

Here is how this particular situation has been solved.

Also, here is a bit of information about SQL collations extracted from here:

"Each SQL Server collation specifies three properties:

• The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
• The sort order to use for non-Unicode character data types (char, varchar, and text)."

So, the SQL collation affects sorting. If you want to avoid some problems (including sorting problems), don't specify the collation.

In SQL Server Management Studio, you can change the default behavior for generating scripts from Tools -> Options -> Scripting: