Views and Collations

Question

Post reply

Views and Collations

shindle 17293

Ten Centuries

Points: 1374
More actions
January 4, 2018 at 1:36 am

#325285

Hi there
Is there a way of changing the collation for a view, and not by individual columns in a view?
ie. SELECT * FROM vwName COLLATION...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Hamid-Sadeghian SSC Eights! Points: 890 More actions · Answer 1

Hi,
No you cannot use collate in select from view. you can change collation in body of your view .

shindle 17293 Ten Centuries Points: 1374 More actions · Answer 2

Thanks for the reply.

Can this be done for a whole table within a view then, say:
SELECT ... FROM table <COLLATION>
or does it have to be done by individual column within the view?

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

shindle 17293 - Thursday, January 4, 2018 5:56 PM
Thanks for the reply.
Can this be done for a whole table within a view then, say:
SELECT ... FROM table <COLLATION>
or does it have to be done by individual column within the view?

Cant be done for a whole table in this way. Per BOL:
"[SQL Server COLLATE] is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast."

You will have to change the underlying table definition or apply the desired collation to the column references in your view. Heres a good article on this topic:

https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collations-shy-ask/

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001