Why does this sproc reference find the correct table to update?

Question

Hi,

If I have tables like this:

ImportSet1.Users
ImportSet2.Users
ImportSet3.Users

Then in my sproc I do this:

CREATE PROCEDURE [ImportSet2].[UpdateUsers]

...
UPDATE Users
   set blah = 234
WHERE id = 234

This seems to work, but why? Since when did that prefix [ImportSet2] signify part of the table name? (in this case ImportSet2)

Answer 1

The sproc is built under the ImportSet2 schema, so, by default, it will reference objects in the ImportSet2 schema if no schema name is specified.

I believe it is best-practice, however, to fully-qualify objects with their schema name.

Answer 2

To answer "since when", certainly by 2000 you could have objects owned by owners other than dbo. Since 2005, MS separated the concept of owners and schemas.

I believe the feature appeared in an early release of SQL Server, either 6.5 or 7.0. So it's hardly new (although there's considerable pain using the feature before 2005)