@PeterVandivier Thanks. I'll have a look.

Morning

Morning

Morning

@McNets You would have to create the database with Modern_Spanish_CS_AS.

My (laptop) instances are all CS (case-sensitive) unless I have to import CI databases.

Latin1_General_CS_AS is my default.

    |    Server Name    |   (No column name)   |
    |-------------------|----------------------|
    | notebook           | Latin1_General_CS_AS |
    | notebook\sql2019   | Latin1_General_CS_AS |
    | notebook\sql2016   | Latin1_General_CS_AS |
    | notebook\sql2017   | Latin1_General_CS_AS |
    | notebook\sql2017ci | Latin1_General_CI_AS |
    | notebook\sql2016ci | Latin1_General_CI_AS |

@Johnakahot2use if you create a new script in one of the CI_AS, can you declare a variable in lowercase and use it in uppercase?

Sorry

My instance is CS and one of the dbs is case insensitive

declare @t1 int, set @T1=10

...is mixing cases.

This will work if the database is CI.

It will not work in a CS database.

it doesn't work when importing the script from a CI database via a CS Server into a CI database. (I learnt the hard way; and that is why I have multiple local installations for importing and down-grading 2019 Databases to 2017 or 2016 databases of various CI / CS collations)

During the import of a scripted SCHEMA & DATA (database) using the Generate Scripts... wizard, the script relies on the collation of the target server.

@Johnakahot2use I don't know how DB has been created

Via script I suppose

My first statement was wrong You would have to create the database with Modern_Spanish_CS_AS.

The script should actually work if the database is using a CI collation

I've checked and it doesn't work

So your database is using Modern_Spanish_CI_AS?

Your instance is set to Latin1_General_BIN?

Instance = Latin1_General_BIN, the new DB Modern_Spanish_CI_AS

When you are running your script, did you:

USE YOUR_DATABASE;

Yes

Does the current database's collation affect how names in the script being run are interpreted?

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Test', FILENAME = N'F:\BD\Test.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Test_log', FILENAME = N'G:\LOG\Test_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
 COLLATE Modern_Spanish_CI_AS
GO

USE [Test]
GO

DECLARE @t1 int;
SET @T1 = 0

Mens. 137, Nivel 15, Estado 1, Línea 5
Debe declarar la variable escalar "@T1".

You must declare scalar variable @T1

You'll get a red squiggly line underneath the @T1 when writing.

Apparently, scripts are handled differently than stored procedures.

In a stored procedure the upper-case, lower-case notation wouldn't matter in a CI database.

I can't create a new SP because I receive the same error

And I would expect an error too

I had issues when importing a CI database that had stored procedures with upper-case and lower-case when importing on a CS instance and CI database, but it worked fine when importing the scripts on a CI instance with a CI database.

I'll fidget around on my instances and let you know.

@Johnakahot2use thanks, by now my answer to the provider has been: Use a valid syntax.

They know the instance's collation in advance.

I usually don't have problems with inconsistent casing in my scripts with the collation I use, which is Latin1_General_OCD

I usually work with Modern_Spanish_CI_AS

Here are my findings.

Running the following code:

DECLARE @t1 INT

SET @T1 = 1

... on the following combinations will result in the following errors:

Instance | DB    | Error
---------+-------+--------------
 CI      | CI    | No
 CI      | CS    | No
 CS      | CI    | Yes
 CS      | CI    | Yes

It looks like the instance defines whether a script is valid or not.

@Johnakahot2use Thanks

@Johnakahot2use Can you create a stored procedure with casing inconsistencies on a CI instance but then restore the DB with that proc on a CS instance and run it there?

I'll guess: restore = yes, execute = no

Let me check.

CI Instance running CI Database = no red squiggly line

Even CI instance running CS database doesn't produce an error

@AndriyM ..in a CI database?

I don't think the database collation would matter but let it be a CI collation for the DB.

So a stored proc in a CI database, created on a CI instance, but then the DB restored and the SP run for the first time on a CS instance.

I created a function on the CI instance CI Database:

perhaps it could be solved using a self-contained db

docs.microsoft.com/en-us/sql/relational-databases/databases/…

USE [CI_Instance_Modern_Spanish_CI_AS_Database]
GO

/****** Object:  UserDefinedFunction [dbo].[CI_Instance_CI_Database_Function]    Script Date: 07.08.2020 12:17:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[CI_Instance_CI_Database_Function]
(
	-- Add the parameters for the function here

casing issues ----^

runs on CI instance in CI database.

After restoring to a CS instance and executing:

Msg 137, Level 15, State 1, Procedure CI_Instance_CI_Database_Function, Line 18 [Batch Start Line 0]
Must declare the scalar variable "@T1".
Msg 137, Level 15, State 2, Procedure CI_Instance_CI_Database_Function, Line 19 [Batch Start Line 0]
Must declare the scalar variable "@T1".

Completion time: 2020-08-07T12:16:16.3537148+02:00

I'll restore a CI instance CS Database to the CS instance and see what happens.

Collations apply not only to data stored in user tables, but to all text handled by SQL Server, including metadata, temporary objects, variable names, etc. The handling of these differs in contained and non-contained databases.

same error

So even though the stored procedures are part of the database, they interact with the instance.

:-)

Off for lunch

👍

TIL

Yep

Cool

❄

Nice, thanks for the tests and sharing the results, John!

You're welcome

@McNets I guess that would work.

Candidate for most horrible SQL construct of the month (year)?

I created a contained database CI_Instance_Modern_Spanish_CI_AS_Database_Contained on a case-insensitive instance and inserted the function with mismatched case.

I then restored the database to a case-sensitive instance and the function works.

@Johnakahot2use All right. Now if you USE CI_Instance_Modern_Spanish_CI_AS_Database_Contained on this case-sensitive instance and then ALTER PROCEDURE CI_Instance_CI_Database_Function, does it work?

I mean if you keep the same text of the proc

I'm not sure if this was covered in the discussion above

I hit Modify... on the function which created the ALTER FUNCTION script and it executed without any issues.

:-)

However, the @T1 parameter was underlined in the contained database.

So to summarise, when you are sending a script to the server, it's the instance-level collation that the instance uses to parse your script, unless the current context is a contained database, in which case the contained database's collation is used. Sounds right?

Sounds good

Well unless you can prove otherwise.

Yes, this is a conclusion based on the current findings. If something else comes up, it will need to be corrected of course

@Johnakahot2use Nice

I think this MS doc state just that.