如何防止归类影响SQL语法中的表名？ [英] How to prevent collation affecting table names in SQL syntax?

问题描述

有人可以解释如何保护表名不受排序规则设置的约束吗？
我当前收到错误消息：

Can someone explain how to protect table names from being subject to the collation settings? I'm currently getting the error message:

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Dataarchive'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'MyDatabase.dbo.Dataarchive'.

通过此SQL：

USE master;  
CREATE DATABASE MyDatabase COLLATE Danish_Norwegian_CI_AS;
GO

USE MyDatabase
CREATE TABLE DataArchive (id INT);
GO

SELECT * FROM DataArchive; -- succeeds
SELECT * FROM dataArcHIVE; -- succeeds
SELECT * FROM [MyDatabase].[dbo].[DataArchive]; -- succeeds
GO
SELECT * FROM Dataarchive; -- fails - interprets aa as special A character.
GO
SELECT * FROM [MyDatabase].[dbo].[Dataarchive]; -- fails
GO

USE MASTER;
DROP DATABASE MyDatabase;
GO

我希望排序规则适用于对数据进行排序，而不是对表名本身进行排序。

I expected collation to apply to sorting my data, not to table names themselves.

背景

出现这种情况是因为客户负责安装SQL Server，并将服务器排序规则设置为Danish_Norwegian_CI_AS，因此，默认情况下，任何数据库都具有此排序规则（并且在通过代码/脚本创建新数据库时，我们没有专门设置数据库的排序规则）。在这种情况下，我仍然不希望我们的表名会被完全不同地解释。意味着我们唯一的选择是强制对数据库进行拉丁排序，如果用户想要不同的内容，用户可以指定每列排序？

This situation has arisen because the customer was responsible for installing the SQL Server, and set the server collation to Danish_Norwegian_CI_AS, thus any database, by default has this collation (and we do not specifically set the collation of the Database when creating a new database through code/script). In this situation, I still did not expect that our table names would be interpreted differently at all. Meaning our only option is to force latin collation on the database and users can specify per-column collation if they want something different?

推荐答案

我认为SQL Server在执行查询，检查或编译之前，例如会检查表验证，如下所示：

I think SQL Server before execute a query, check or compile it, for example it checks for table validation like this:

select *
from sys.objects
where name = N'Dataarchive'

将没有结果。

因此，它将引发：

That will have no result. instead for other modes that will return a result.
Because of this it will raise:

无效的对象名称'数据存档。

Invalid object name 'Dataarchive'.

但是您可以在 sys.object 中检查另一个 COLLATION 像这样：

But you can check over sys.object with another COLLATION like this:

select *
from sys.objects
where name COLLATE latin1_General_CI_AI = N'Dataarchive'

您不能强制SQL Server DBMS这样进行检查或编译。

That will have a result, AFAIK, You can't force SQL Server DBMS to do its check or compile like this.

BTW，您可以获取以下数据在这种情况下的表-使用Dynamic SQL-像这样：

BTW, You can get data of a table in that case - with using Dynamic SQL - like this:

declare @tablename nvarchar(255) = 'Dataarchive';
declare @sql nvarchar(255) = 
    N'SELECT * FROM '+ (
        select name 
        from sys.tables 
        where name = @tablename COLLATE Latin1_General_CI_AI);
exec sp_sqlexec @sql;

这篇关于如何防止归类影响SQL语法中的表名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！