Is there a rule for naming columns a certain name?

Question

So we have a SQL Server 2017 instance with the default collation of Vietnamese_100_CI_AS_KS.

When we create a Temp Table, we see that if a specific name is used, it somehow sees one letter as case sensitive.

Example:

CREATE TABLE #MaterialTest
(
    Product VARCHAR(50),
    Quality VARCHAR (50),
    MaterialOriginGroup VARCHAR (50) 
)

INSERT INTO #MaterialTest VALUES ('Papers', 'Good', 'Test1')

If I query this table using:

Select Product, Quality, MaterialOriginGroup from #MaterialTest

It works fine. If I query it using pretty much anything else it works fine

But if I use a lower case "G" for "Group" in MaterialOriginGroup it fails.

Example:

Use TempDB
Select product, quality, materialorigingroup from #MaterialTest

Msg 207, Level 16, State 1, Line 2 Invalid column name 'materialorigingroup'.

If I query it with:

Use TempDB
Select product, quality, materialoriginGroup from #MaterialTest

It works.

Any idea why?

I can't replicate this problem: DB<>FIddle. Are you sure it's a G character? — Thom A
– Thom A ♦, Commented Dec 6, 2019 at 14:45
@Larnu You need to either use a SQL Server setup on the Vietnamese_100_CI_AS_KS collation or create a new database with that collation and create the table as non-temporary. I can reproduce the issue — Martin
– Martin, Commented Dec 6, 2019 at 14:46
So collating the character itself doesn't replicate it? That seems like unintended behaviour, @Martin . Literal Strings and Object Names should behave the same for comparison. — Thom A
– Thom A ♦, Commented Dec 6, 2019 at 14:48
This is specifically to do with the letter combination ng. If you create a column nG, then using ng causes the same failure. However, if it's aG, there is no case sensitivity... — Martin
– Martin, Commented Dec 6, 2019 at 14:52
Yep, i can replicate with nG as well, @Martin . I was explicitly testing G, not nG. (DB<>Fiddle) — Thom A
– Thom A ♦, Commented Dec 6, 2019 at 15:00

Martin · Accepted Answer · 2019-12-06 15:25:04Z

Building on my own investigations and @Larnu's SQL Fiddle, I've devised a little script to test two-letter combinations:

DECLARE @Letter VARCHAR(2);
SET @Letter = 'ch';

DECLARE @LetterA VARCHAR(1);
DECLARE @LetterB VARCHAR(1);
SET @LetterA = LEFT(@Letter, 1);
SET @LetterB = RIGHT(@Letter, 1);

SELECT CASE WHEN V.G = UPPER(@Letter) COLLATE Vietnamese_100_CI_AS_KS THEN 1 ELSE 0 END,
       CASE WHEN V.G = LOWER(@Letter) COLLATE Vietnamese_100_CI_AS_KS THEN 1 ELSE 0 END,
       CASE WHEN V.G = CONCAT(LOWER(@LetterA), UPPER(@LetterB)) COLLATE Vietnamese_100_CI_AS_KS THEN 1 ELSE 0 END,
       CASE WHEN V.G = CONCAT(UPPER(@LetterA), LOWER(@LetterB)) COLLATE Vietnamese_100_CI_AS_KS THEN 1 ELSE 0 END
FROM (VALUES(@Letter COLLATE Vietnamese_100_CI_AS_KS)) V(G);

This will output 1 or 0 depending upon whether a comparison is valid for the two letter combination. The comparisons are:

Upper case letters (i.e. NG)
Lower case letters (i.e. ng)
One upper case, one lower case letter (i.e. Ng)
One lower case, one upper case letter (i.e. nG)

Running this for ab produces:

1 1 1 1

So ab does not exhibit this issue. However, for ch you get:

1 1 0 1

So ch does exhibit the same issue.

This seems related to declared consonants in the Vietnamese alphabet, which include these two-letter consonants:

Ch = 1 1 0 1 (has the same issue)
Gh = 1 1 0 1 (has the same issue)
Gi = 1 1 0 1 (has the same issue)
Kh = 1 1 0 1 (has the same issue)
Ng = 1 1 0 1 (has the same issue)
Nh = 1 1 0 1 (has the same issue)
Ph = 1 1 0 1 (has the same issue)
Qu = 1 1 0 1 (has the same issue)
Th = 1 1 0 1 (has the same issue)
Tr = 1 1 0 1 (has the same issue)

Unfortunately, I do not know why these are affected by this issue, which is not helpful. In addition, I don't know why it only affects the column name when it is a lowercase followed by an uppercase letter.

Perhaps someone else may see this and know the answer?

The reference for the alphabet is here.

Collectives™ on Stack Overflow

Is there a rule for naming columns a certain name?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related