SEO

Hi all,
While debugging some old code from someone, I came across this stored
procedure:

SELECT dbo.TBL_COORD.COORD_ID AS ID, dbo.TBL_COORD.LATITUDE AS Latitude,
dbo.TBL_COORD.LONGITUDE AS Longitude,
dbo.TBL_COORD.NORTHING AS Northing,
dbo.TBL_COORD.EASTING AS Easting, dbo.refDROP_VALUES.Drop_Value AS [Geometry
Type],
refDROP_VALUES_1.Drop_Value AS [GPS Datum],
refDROP_VALUES_2.Drop_Value AS [GPS Used]
FROM dbo.TBL_COORD INNER JOIN
dbo.refDROP_VALUES ON dbo.TBL_COORD.GEOMETRYTYPE_ID =
dbo.refDROP_VALUES.ID INNER JOIN
dbo.refDROP_VALUES refDROP_VALUES_1 ON
dbo.TBL_COORD.GPS_DATUM = refDROP_VALUES_1.ID INNER JOIN
dbo.refDROP_VALUES refDROP_VALUES_2 ON
dbo.TBL_COORD.GPS_USED = refDROP_VALUES_2.ID
WHERE
<some conditions here>

This query seems to work fine, however I cannot see ANY source to the tables
refDROP_VALUES_1, and refDROP_VALUES_2. There are no views/tables/stored
procedures of any kind with these names in the databse, so I'm at a loss as
to where they're coming from. Note that there IS a table refDROP_VALUES, and
the fields that refDROP_VALUES_1 and refDROP_VALUES_2 reference ARE fields
in the table refDROP_VALUES. I can view the results from running the query.
Whats going on here? Does MS SQL create these tables?

Jack.

refDROP_VALUES_1 and refDROP_VALUES_2 are referenced first as owner
(schema) names, specifically the owner names of the two tables called
Drop_Value.

Those same names (refDROP_VALUES_1 and refDROP_VALUES_2) are then used
as aliases for another table owned by dbo (dbo.refDROP_VALUES). This is
very confusing naming but is perfectly legal if all the tables exist.
To demonstrate, try:

SELECT TOP 10 * FROM refDROP_VALUES_1.Drop_Value
SELECT TOP 10 * FROM refDROP_VALUES_2.Drop_Value
SELECT TOP 10 * FROM dbo.refDROP_VALUES

Lookup Owner Names in Books Online if you haven't come across two-part
names before.

--
David Portas
SQL Server MVP
--

On Mon, 14 Nov 2005 21:26:51 +0800, Jack wrote:

>Hi all,
>While debugging some old code from someone, I came across this stored
>procedure:
>
>SELECT dbo.TBL_COORD.COORD_ID AS ID, dbo.TBL_COORD.LATITUDE AS Latitude,
>dbo.TBL_COORD.LONGITUDE AS Longitude,
> dbo.TBL_COORD.NORTHING AS Northing,
>dbo.TBL_COORD.EASTING AS Easting, dbo.refDROP_VALUES.Drop_Value AS [Geometry
>Type],
> refDROP_VALUES_1.Drop_Value AS [GPS Datum],
>refDROP_VALUES_2.Drop_Value AS [GPS Used]
>FROM dbo.TBL_COORD INNER JOIN
> dbo.refDROP_VALUES ON dbo.TBL_COORD.GEOMETRYTYPE_ID =
>dbo.refDROP_VALUES.ID INNER JOIN
> dbo.refDROP_VALUES refDROP_VALUES_1 ON
>dbo.TBL_COORD.GPS_DATUM = refDROP_VALUES_1.ID INNER JOIN
> dbo.refDROP_VALUES refDROP_VALUES_2 ON
>dbo.TBL_COORD.GPS_USED = refDROP_VALUES_2.ID
>WHERE
> <some conditions here>
>
>This query seems to work fine, however I cannot see ANY source to the tables
>refDROP_VALUES_1, and refDROP_VALUES_2. There are no views/tables/stored
>procedures of any kind with these names in the databse, so I'm at a loss as
>to where they're coming from. Note that there IS a table refDROP_VALUES, and
>the fields that refDROP_VALUES_1 and refDROP_VALUES_2 reference ARE fields
>in the table refDROP_VALUES. I can view the results from running the query.
>Whats going on here? Does MS SQL create these tables?
>
>Jack.
>

Hi Jack,

In addition to David's comments, this query _requires_ the use of table
aliases. That's because the same table (dbo.refDROP_VALUES) is joined in
three times. Without aliasing, that would result in three copies of the
same table existing in the work result set, and there would be no way to
know which of the three you refer to if you use a column name.

I find it more understandable to never leave out the optional AS keyword
between table name and alias. I also believe that there would be less
confusion if in this case all three occurences of dbo.refDROP_VALUES had
been aliased.

SELECT some columns
FROM dbo.TBL_COORD
INNER JOIN dbo.refDROP_VALUES AS refDROP_VALUES_0
ON dbo.TBL_COORD.GEOMETRYTYPE_ID = refDROP_VALUES_0.ID
INNER JOIN dbo.refDROP_VALUES AS refDROP_VALUES_1
ON dbo.TBL_COORD.GPS_DATUM = refDROP_VALUES_1.ID
INNER JOIN dbo.refDROP_VALUES AS refDROP_VALUES_2
ON dbo.TBL_COORD.GPS_USED = refDROP_VALUES_2.ID
WHERE <some conditions here>

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> I find it more understandable to never leave out the optional AS keyword
> between table name and alias. I also believe that there would be less
> confusion if in this case all three occurences of dbo.refDROP_VALUES had
> been aliased.

And even less confusing if the alias had been short, like DV1, DV2 etc.
In my opinion using alias is a necessity in most cases, as if you alwyas
write out the table names in full, it's difficult to see the forest
for the trees.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thank-you to all for your comments... David - I will be looking into this
to increase my MS SQL knowledge as I've never come across this concept
before!
Thanks,
Jack.

"Jack" <jacksmith@nospam.co.uk> wrote in message
news:4378917b$1@quokka.wn.com.au...
> Hi all,
> While debugging some old code from someone, I came across this stored
> procedure:
>
> SELECT dbo.TBL_COORD.COORD_ID AS ID, dbo.TBL_COORD.LATITUDE AS
> Latitude, dbo.TBL_COORD.LONGITUDE AS Longitude,
> dbo.TBL_COORD.NORTHING AS Northing,
> dbo.TBL_COORD.EASTING AS Easting, dbo.refDROP_VALUES.Drop_Value AS
> [Geometry Type],
> refDROP_VALUES_1.Drop_Value AS [GPS Datum],
> refDROP_VALUES_2.Drop_Value AS [GPS Used]
> FROM dbo.TBL_COORD INNER JOIN
> dbo.refDROP_VALUES ON dbo.TBL_COORD.GEOMETRYTYPE_ID =
> dbo.refDROP_VALUES.ID INNER JOIN
> dbo.refDROP_VALUES refDROP_VALUES_1 ON
> dbo.TBL_COORD.GPS_DATUM = refDROP_VALUES_1.ID INNER JOIN
> dbo.refDROP_VALUES refDROP_VALUES_2 ON
> dbo.TBL_COORD.GPS_USED = refDROP_VALUES_2.ID
> WHERE
> <some conditions here>
>
> This query seems to work fine, however I cannot see ANY source to the
> tables refDROP_VALUES_1, and refDROP_VALUES_2. There are no
> views/tables/stored procedures of any kind with these names in the
> databse, so I'm at a loss as to where they're coming from. Note that there
> IS a table refDROP_VALUES, and the fields that refDROP_VALUES_1 and
> refDROP_VALUES_2 reference ARE fields in the table refDROP_VALUES. I can
> view the results from running the query. Whats going on here? Does MS SQL
> create these tables?
>
> Jack.
>
>