What is the correct SQL Server syntax for addressing tables?

Question

This seems like a pretty obvious question, but I couldn't think of the right term for what I'm trying to ask, so coming up with reference materials for this was difficult. The answers seem obvious.

When examining Pluralsight materials for SQL Server, they recommend always referring to tables both in “regular” queries (something you can write for a basic web service) and in a SQL query, possibly in a stored procedure, for example:

[databasename].[dbo].[some_table].[sometimesacolumngoeshere] 

Although I found that stored procedures, etc., that simply use are very common:

 my_column or [my_column] 

The difference here, obviously, is that it provides an absolute “address” explicitly, while the other is implicit.

How do you know when it is advisable to use one above the other, as well as what you call it?

My preference would always be explicit, and if you need to save space and / or make things clearer, could you alias the explicit full "address" alias?

Answer 1

You're right. Basically, SQL will try to find the field in which you search for "my_column" in all tables in the FROM and JOIN sections. If, however, you have "my_column" in table A and in table B, you need to explicitly specify which "my_column" you are looking for by including the table name. This continues until the chain to dbo and databasename, if you have a collision there.

In most cases, you will find that people do not explicitly invoke the tables in which the column is located unless they join multiple tables.

For example, I write my queries as follows:

 SELECT a.field1, b.field2 FROM tableA AS a INNER JOIN tableB AS b ON a.id = b.a_id WHERE a.id = 123 

Here I use AS for alias tableA and tableB to more readable a and b. I could just as easily write my query as follows:

 SELECT tableA.field1, tableB.field2 FROM tableA INNER JOIN tableB ON tableA.id = tableB.a_id WHERE tableA.id = 123 

Or how it is, if field1 and field2 are unique to tables, but this is a bit confusing where each piece of data comes from.

 SELECT field1, field2 FROM tableA INNER JOIN tableB ON tableA.id = tableB.a_id WHERE tableA.id = 123 

Answer 2

SQL Server has four names:

• Most often you refer to an object by name

   SELECT * FROM MyTable 

• Then you can specify the owner or scheme of the object:

   SELECT * FROM dbo.MyTable 

• Then you can refer to the database in which the object is located:

   SELECT * FROM master.dbo.MyTble 

• Finally, you can reference the table on another server

   SELECT * FROM test1.master.dbo.MyTable 

This was explained better than I can on MSDN

Answer 3

Although implict names for columns can be used, this is a poor choice from a support aspect. I never put a production code that does not correspond to each column, because when you return in a year to change this report or query, you really do not want to find out which of the 20 tables you joined this from. Also, without indicating that the database works more to find a column, and you have more coding errors, where you have column names that are the same in two tables without a link. It is a good habit to use exclusive links.

Answer 4

The plural site is wrong.

Given an example of a stored procedure that uses a fully qualified name that refers to objects in the same database, here are two more reasons to not fully qualify in this way:

• If you have a stored procedure related to an object in the same database, it will break if you rename your database.

• If you should start using Visual Studio database tools to add your database scripts to the original control, you will receive many warnings to work with

It’s a good idea to correctly understand and use circuits.

Answer 5

I think this is one of those issues that are subjective, and will prove to be preferable, but:

In terms of readability, I would argue that it is explicitly ONLY when necessary. Quite often, SQL statements are quite complex, without having to read a lot of unnecessary text.

I think that

 SELECT TOP 1000 [StoreNumber] ,[Address1] ,[Address2] ,[City] ,[St] ,[Zip] ,[ZipSuffix] ,[LocationType] ,[LocationSubType] ,[Corp] ,[Division] ,[ZoneNumber] ,[DistrictNumber] ,[StateNumber] FROM [CommonData].[dbo].[vw_StoreData] 

is a lot more readable than

 SELECT TOP 1000 [CommonData].[dbo].[vw_StoreData].[StoreNumber] ,[CommonData].[dbo].[vw_StoreData].[[Address1] ,[CommonData].[dbo].[vw_StoreData].[[Address2] ,[CommonData].[dbo].[vw_StoreData].[[City] ,[CommonData].[dbo].[vw_StoreData].[[St] ,[CommonData].[dbo].[vw_StoreData].[[Zip] ,[CommonData].[dbo].[vw_StoreData].[[ZipSuffix] ,[CommonData].[dbo].[vw_StoreData].[[LocationType] ,[CommonData].[dbo].[vw_StoreData].[[LocationSubType] ,[CommonData].[dbo].[vw_StoreData].[[Corp] ,[CommonData].[dbo].[vw_StoreData].[[Division] ,[CommonData].[dbo].[vw_StoreData].[[ZoneNumber] ,[CommonData].[dbo].[vw_StoreData].[[DistrictNumber] ,[CommonData].[dbo].[vw_StoreData].[[StateNumber] FROM [CommonData].[dbo].[vw_StoreData] 

(It gets worse when you start joining tables, and even worse if you join tables in different databases.)

I can see where you can argue that the second is more readable if you need to know exactly which database, schema, and table a particular field appears in, looking only at the request.

But in SQL Server, for example, you can open this query in the designer and see it in a much more convenient graphical representation.

IMHO, the only time I will use the full syntax, if necessary , when crossing the borders of a table / database / schema, or if you have two tables with the same field name.

Example:

 SELECT TOP 1000 [CommonData].[dbo].[vw_StoreData].[StoreNumber] ,[Address1] ,[Address2] ,[City] ,[St] ,[Zip] ,[ZipSuffix] ,[LocationType] ,[LocationSubType] ,[Corp] ,[Division] ,[ZoneNumber] ,[DistrictNumber] ,[StateNumber] FROM [CommonData].[dbo].[vw_StoreData] Inner Join [CommonData].[dbo].[vw_StorePhones] ON [CommonData].[dbo].[vw_StorePhones].[StoreNumber] = [CommonData].[dbo].[vw_StoreData].[StoreNumber] 

And even so, I would use Table Aliases to shorten it and make it more readable.

All this suggests that in the real world you are likely to find yourself in a company that has already decided on a standard format, and you will need to encode in accordance with the standard of the company.

Answer 6

Objects can be transmitted through one or more identifiers.

You can use one identifier to refer to an object, but if the identifier is ambiguous, you should use more identifiers to uniquely identify the object.

For example, two tables named TableA that exist in different schemas must be specified in the query using at least two identifiers, schema_one.TableA and schema_two.TableA .

There's an MDSN page where you can learn more about object names and identifiers.

Regarding the use of multiple identifiers for object names, if you are more specific, you reduce the uncertainty in queries and speed up query parsing, since the database engine should not solve the ambiguity problem, due to the readability of the queries.

My personal preferences (for the most frequently used objects) use schema.Table when referencing tables and column if a single table is specified in the query, and table.column if multiple tables are referenced in the query.

Answer 7

In most cases, I always recommend using the full address to be safe.

  [databasename].[dbo].[some_table].[sometimesacolumngoeshere] 

However, this is really necessary when you have multiple databases. I only ever faced one or two problems when choosing databases, and this is usually fixed by choosing the correct sql server.

As soon as you are inside the request and you have indicated the abbreviated alias in the table, then there really is no need to include the full address, because this is already indicated.

eg

  FROM [databasename].[dbo].[some_table].[sometimesacolumngoeshere] SOME SELECT SOME.Name