Collate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Collate

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-08-04 : 09:48:40

Hello,

I am using a code from a open project to create one of the tables of my project:


create table dbo.Errors
(
  Id uniqueidentifier rowguidcol not null
    constraint DF_Errors_Id default (newid()),
    constraint PK_Errors primary key nonclustered,
  Application nvarchar(60) collate SQL_Latin1_General_CP1_CI_AS not null,
  Host nvarchar(50) collate SQL_Latin1_General_CP1_CI_AS not null,
  Type nvarchar(100) collate SQL_Latin1_General_CP1_CI_AS not null,
  Source nvarchar(60) collate SQL_Latin1_General_CP1_CI_AS not null,
  Message nvarchar(500) collate SQL_Latin1_General_CP1_CI_AS not null,
  [User] nvarchar(50) collate SQL_Latin1_General_CP1_CI_AS not null,
  StatusCode int not null,
  TimeUtc datetime not null,
  [Sequence] int identity (1, 1) not null,
  AllXml ntext collate SQL_Latin1_General_CP1_CI_AS not null
)

Why do I have collate SQL_Latin1_General_CP1_CI_AS in all columns?

Thanks,
Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 09:53:26

i think this was generated by scripting feature of sql server management studio which adds the default collation setting of db to all character data columns by default.you may ignore it if you want

johnconstraint
Starting Member

23 Posts

Posted - 2009-08-04 : 09:57:21

I found some info on this here:
http://www.eggheadcafe.com/community/aspnet/7/10011947/reason-for-collate-sqlla.aspx

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-08-04 : 09:58:13

Thank You.

I have been including some code from 2 other projects and their SQL has a lot of this in opposition to mine which is much "cleaner".

Thanks,
Miguel

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-04 : 13:21:02

In General you can leave of the COLLATION if all your servers run the same collation and you don't need something special (like case sensitive or something).

Just beware of the server collation if you are moving/creating schema on different servers. I've run into some "oddities" when people move schema from a 2000 server to a 2008 server and the default collations were different on the servers. So, query that spanned databases would complain about mismatched collation. Easy to fix, but work none the less.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources