sql - 包含字符 0xFFFF 有效标识符的表名

Question

关于兼容性级别 80 和级别 90 之间差异的 MS 文档在兼容性级别 80上讲述了“包含字符 0xFFFF 的对象名称是有效标识符”，但是字符 0xFFFF 可以将对象名称命名为表名，.. .?

能否请您帮忙，谢谢！

标签： sqlsql-serversql-server-2005

Answer 1

您的数据库极不可能包含具有带有此字符的标识符的任何表或其他对象。话虽如此，它是可以做到的。我使用 SQL Server 2008 运行以下脚本：

create database TestFFFF
go
alter database TestFFFF set compatibility_level = 80
go
use TestFFFF

declare @s nvarchar(200)

-- Build a string that will create a table
-- In our first example, we'll use a simple character
-- nchar(65) is the same as capital letter 'A'
-- so this will create a table named tungnAguyen.

set @s = N'create table [tungn' + NCHAR(65) + N'guyen] ( IDA int )'
exec sp_executesql @s

-- Now we will create a table name with character U+FFFF.
-- Note 65535 is decimal for hexadecimal FFFF.
-- Note this will only work if the database compatibility level is 80 or less.
-- Otherwise you'll get an error message.

set @s = N'create table [tungn' + NCHAR(65535) + N'guyen] ( IDF int )'
exec sp_executesql @s

-- Let's see if it worked.
-- I'll convert the table name to hexadecimal so you can see each character.

select name, CAST(name as varbinary(1000)) as BinName 
from sysobjects where name like N'tun%'

-- The result looks like this:
-- name        BinName                                    
-- tungnAguyen 0x740075006E0067006E00410067007500790065006E00
-- tungnguyen  0x740075006E0067006E00FFFF67007500790065006E00
--
-- The first row shows the first table we created using letter 'A'.
-- In the hexidecimal version you see 4100 in the middle.  That's the 'A'
-- 
-- In the second row, there appears to be nothing in the middle
-- of the name.
-- The reason is that character FFFF is not printable. 
-- In the middle of the hexidecimal you see FFFF.  This is proof
-- that the identifier contains that character.
--
-- Let's see if we can access these tables.
-- Start with the table that contains letter 'A'

set @S = N'select * from [tungn' + NCHAR(65) + N'guyen]'    
exec sp_executesql @S

-- It works. 
-- We've not inserted any rows, so the result is empty, but it works.
-- Now let's access the other table.

set @S = N'select * from [tungn' + NCHAR(65535) + N'guyen]'    
exec sp_executesql @S

-- This works too, as long as we are using compatibility level 80.
-- Let's change the compatibility level to 90 
-- and see what happens.

go
alter database TestFFFF set compatibility_level = 90
go

declare @S nvarchar(200)
set @S = N'select * from [tungn' + NCHAR(65535) + N'guyen]'
exec sp_executesql @S

-- This time it fails. We get an error message:
-- Msg 1055, Level 16, State 1, Line 73
-- 'tun...' is an invalid name because it contains 
-- a NULL character or an invalid unicode character.
--
-- The U+FFFF is the invalid Unicode character it is talking
-- about.  According to the Unicode standard there is 
-- no such character.