The Great Escape

Question

The Great Escape

Viewing 15 posts - 16 through 30 (of 42 total)

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

Marcia J SSCertifiable Points: 5649 More actions · Answer 1

Got it wrong because of missing that [t-a] wouldn't work, but learned about the escape clause.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I got it wrong because I didn't take into account the possibility that the question assumed a case insensitive collation. As posted both queries will return 1 row each if the collation is case sensitive.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Sean Pearce (6/25/2014)
palotaiarpad (6/25/2014)
It would be better to define the collation. If it's case sensitive, then 'Tea' will not match 'tea'. 😛
No, it would be better to assume the default collation applies.

And if the default collation is case sensitive?

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 4

Lynn Pettis (6/25/2014)
Sean Pearce (6/25/2014)
palotaiarpad (6/25/2014)
It would be better to define the collation. If it's case sensitive, then 'Tea' will not match 'tea'. 😛
No, it would be better to assume the default collation applies.
And if the default collation is case sensitive?

(according to my knowledge)

well... I am not sure if that would be considered as default, CI is the collation which comes pre selected at the time of installation, and changing to CS would be custom or user specific which is definitely not default.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Raghavendra Mudugal (6/25/2014)
Lynn Pettis (6/25/2014)
Sean Pearce (6/25/2014)
palotaiarpad (6/25/2014)
It would be better to define the collation. If it's case sensitive, then 'Tea' will not match 'tea'. 😛
No, it would be better to assume the default collation applies.
And if the default collation is case sensitive?
(according to my knowledge)
well... I am not sure if that would be considered as default, CI is the collation which comes pre selected at the time of installation, and changing to CS would be custom or user specific which is definitely not default.

I would disagree. Just because SQL Server has a CI collation as a default on installation does not make it the default collation. That's like saying you should always assume that tlog autogrowth is 10% and datafile autogrowth is 1MB just because that's the defaults for a database if you don't change it when creating a new database.

I have worked with applications that required the use of CS collations. We setup the servers to use the required collation so all databases created on the server would have that collation.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 6

Lynn Pettis (6/25/2014)

Raghavendra Mudugal (6/25/2014)
Lynn Pettis (6/25/2014)
Sean Pearce (6/25/2014)
palotaiarpad (6/25/2014)
It would be better to define the collation. If it's case sensitive, then 'Tea' will not match 'tea'. 😛
No, it would be better to assume the default collation applies.
And if the default collation is case sensitive?
(according to my knowledge)
well... I am not sure if that would be considered as default, CI is the collation which comes pre selected at the time of installation, and changing to CS would be custom or user specific which is definitely not default.

I would disagree. Just because SQL Server has a CI collation as a default on installation does not make it the default collation.

what? it has "default" collation, that's the reason it is called as "default"

That's like saying you should always assume that tlog autogrowth is 10% and datafile autogrowth is 1MB just because that's the defaults for a database if you don't change it when creating a new database.

When creating a dummy or local database for checking something or for example there are so many questions in this SSQ's qtod where the batch starts from create database... I don't need to go and change all available parameters to make it perfect database, I just use the default values and use all the settings what MSSQL provides. Changing those values comes with proper requirement or specificity where certain things needs to be in certain way.

I have worked with applications that required the use of CS collations. We setup the servers to use the required collation so all databases created on the server would have that collation.

yea... specificity

I am not sure how you define "default"... for me it is just as this simple "use the value what it is already been provided, any changes will not be considered as default."

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I define default as the setting used when initially installing SQL Server. I don't define it as the default used if not specified when initially installing SQL Server unless that happens to be the collation you want.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 8

Lynn Pettis (6/25/2014)
I define default as the setting used when initially installing SQL Server. I don't define it as the default used if not specified when initially installing SQL Server unless that happens to be the collation you want.

What I am trying to say is... there are so many people around who don't even bother to look what the installation is providing, they just click next and move on focusing on the main parameters (like login details or auth mode which to select.... so on)

take this screen for example: even if you move to the next screen, installer sets one collation by itself what we call as "factory settings" or default value, this value is already there even if you want it not

my focus is only on this.

even though we have so many apps where the CS is needed, we still don't care or bother to look what is the default collation is when installing the server, we just select the proper collation at the time of creating that specific database, and the rest remains on the value what MSSQL is already provided.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Okay, walking away. I apologize for not agreeing that the SQL Server default collation is a CI collation based on the fact that it is what is selected when SQL Server is installed and you don't override it.

We are free to agree to disagree.

Revenant SSC-Forever Points: 42467 More actions · Answer 10

A fun question and interesting discussion... Thanks, Andy!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Raghavendra Mudugal (6/25/2014)
Lynn Pettis (6/25/2014)

I see what Lynn is getting at. And it is a matter of semantics and verbiage (even MS messes this one up).

http://technet.microsoft.com/en-us/library/aa224256(v=sql.80).aspx

That is what Microsoft said back in SQL 2000 - that you could specify the "default" collation of SQL Server.

It seems Microsoft has shied away from this verbiage.

If you look at http://msdn.microsoft.com/en-us/library/ms144260(v=sql.105).aspx, you will see that Microsoft states "the default English-language (US) collation is SQL_Latin1_General."

While the default is one thing for the instance, if you change that installed default you will see a side effect. The side effect is that new databases will adopt this changed collation as their default collation. In the end it doesn't change the fact that the default collation was changed and a custom collation has been implemented.

It just means you two were talking about different things. Yes new databases take on the new setting as if it were the default because the custom setting has been made at the instance level. This is the default behavior of SQL Server.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ken SSCoach Points: 17191 More actions · Answer 12

Fun question. Thanks Andy! Not sure that I will ever need to use Escape but good to know that it is an option.

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 13

My point of view, assume things is never good enough. What if Microsoft decides to change the factory settings?

In my oppinion declaration is the perfect way to choose.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 14

Heh... it would appear that the default definition of default is in default. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rhythmk SSCertifiable Points: 7271 More actions · Answer 15

Very interesting question. Never used Escape.Thanks Andy.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂