Why does SQL Server 2000 treat SELECT test.* and SELECT t.est.* the same?

Question

I butter-fingered a query in SQL Server 2000 and added a period in the middle of the table name:

SELECT t.est.* FROM test

Instead of:

SELECT test.* FROM test

And the query still executed perfectly. Even SELECT t.e.st.* FROM test executes without issue.

I've tried the same query in SQL Server 2008 where the query fails (error: the column prefix does not match with a table name or alias used in the query). For reasons of pure curiosity I have been trying to figure out how SQL Server 2000 handles the table names in a way that would allow the butter-fingered query to run, but I haven't had much luck so far.

Any sql gurus know why SQL Server 2000 ran the query without issue?

Update: The query appears to work regardless of the interface used (e.g. Enterprise Manager, SSMS, OSQL) and as Jhonny pointed out below it bizarrely even works when you try:

SELECT TOP 1000 dbota.ble.* FROM dbo.table

Answer 1

SQL Server 2005 and up has a "proper" implementation of schemas. SQL 2000 and earlier did not. The details escape me (its been years since I used SQL 2000), all I recall clearly is that you'd be nuts to create anything that wasn't owned by "dbo". It all ties into users and object ownership, but the 2000 and earlier model was pretty confusticated. Hopefully someone will read up on BOL, do some experimentation, and post their results here.

Answer 2

Maybe table names are constructed from a naive concatenation of prefix and base name.

't' + 'est' == 'test'

And maybe in the later versions of SQL Server, the distinction was made more semantic/more rigorously.

{ owner = t, table = est } != { table = test }

Answer 3

Is it in the "Open table" view of SSMS or via Enterprise Manager or via an SSMS Query Window?

There is/was a SQL Server 2005 issue with SSMS so how you run the query affects how it behaves.

Answer 4

S-SQL reference manual:

"[dot] Can be used to combine multiple names into a name of the form A.B to refer to a column in a table, or a table in a schema. Note that you calso just use a symbol with a dot in it."

So I think if you referenced tblTest as tblT.est it would work OK as long as there isn't a column called 'est' in tblTest.

If it can't find a column name referenced with the dot I imagine it checks the parent of the object.

Answer 5

This is a bug.

It has to do with internal representation of column names in SQL server 2000 that leaked out.

You will also not be able to create tablecolumn with a name which collides with table+column concatenation with another column, like, if you have tables User and UserDetail, you won't be able to have columns DetailAge and Age in these tables, respectively.

Answer 6

I found a reference to it being a bug

Note: as a result of a comparison algorithm bug in SQL Server 2000, dot symbols themselves have no effect on matching, so "dbo.t" will successfully match with tables "dbot", "d.b.o.t", etc

from http://blogs.msdn.com/b/ialonso/archive/2007/12/21/msg-1013-the-object-s-and-s-in-the-from-clause-have-the-same-exposed-names-use-correlation-names-to-distinguish-them.aspx

It's been fixed in SQL Server 2005. Same link > Changes introduced in SQL Server 2005

1. Dot-related comparison bug has been fixed.