0

I know that I can change the collation of database object in localdb once they are created but, is there any option I can change that sets a default collation for the newly created objects? Or even better, is there anyway to say to the SQL Server process in charge of localdb instances to have a default collation different of SQL_Latin1_General_CP1_CI_AS?

I've searched for that, but I find nothing more than nasty procedures to change collation for already created databases... Is it possible that almost on year 2017 SQL Server doesn't offer this option?

Smit
  • 2,279
  • 1
  • 12
  • 22
Vi100
  • 4,080
  • 1
  • 27
  • 42
  • Use SQL Server Express instead for full control – ErikEJ Dec 13 '16 at 08:55
  • Entity framework allows you to specify database collations, use migration scripts, seeding etc. Why do you care about the default collation anyway? Are you using `varchar` instead of `nvarchar`? – Panagiotis Kanavos Dec 13 '16 at 10:13
  • There is no default database collation, you are talking about the *server's* collation. You shouldn't depend on a server having a specific collation. You should specify the collation you want for your database/columns. A database's collation is defined when it's created. Collations affect indexes and queries too. If SQL Server offered what you ask it would have to rebuild every single index in a database. It's a case of "If you don't know how to do it, you shouldn't do it" – Panagiotis Kanavos Dec 13 '16 at 11:15

1 Answers1

5

From this link: SQL Server 2016 Express LocalDB

The instance collation for LocalDB is set to SQL_Latin1_General_CP1_CI_AS and cannot be changed. Database-level, column-level, and expression-level collations are supported normally.

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • Bad news then! That narrows the options a lot since I'm not in control of the database creation (Entity Framework does) so I cannot set the database collation previously of creating objects on it. I don't understand why Microsoft don't surfaces this setting somewhere... Anyway thanks, I didn't catch it on the docs! – Vi100 Dec 13 '16 at 09:41
  • 1
    @Vi100 who says you aren't in control? EF does what you tell it to. You can use migrations and seeding in the same way as with any other database. You can set the collation you want for any database. Why do you want to change the default/server collation anyway? – Panagiotis Kanavos Dec 13 '16 at 10:12
  • @Panagiotis Kanavos - I need the collation to be accent independent. Can I set the collation on database creation with EF? I didn't know that. Anyway I'm using EF Core so the most probable is that's impossible... – Vi100 Dec 13 '16 at 10:27
  • @Vi100 you should mention that in the question and tags. In EF6 you could override the InitializeDatabase method [as shown here](http://stackoverflow.com/questions/25401290/code-first-setting-database-collation). EF Core has no strategies, so you may have to create the database in your startup code. You don't have to do everything through EF – Panagiotis Kanavos Dec 13 '16 at 10:36
  • @Panagiotis Kanavos - Thanks, but anyway that was not the question. I want to set a default, because I always use the same accent independent collation and I dont wan't to worry about overriding it every time. – Vi100 Dec 13 '16 at 10:39
  • 1
    @Vi100 the *default/server* collation makes sense only when you deploy against your own server. You can't (should not) be certain of the server collation when deploying to production. – Panagiotis Kanavos Dec 13 '16 at 11:11