Missing Schema in Join Condition Suggestion

rldawg

An old bug seems to have reared it's ugly head. I am using version 7.1.0.315 on SQL 2012

SELECT * FROM Billing.Invoices
INNER JOIN dbo.jobJobs ON jobJobs.iJobID = MISSINGSCHEMA.Invoices.iJobId

The Invoices table has a schema of Billing. But when the join condition is suggested, the Billing schema is not used with the table name.

I hadn't noticed this before because I normally use aliases. But I was doing a quick query and I didn't use an alias. I am not sure how long this bug has been around.

Robert.

Aaron L

Hi Robert,

I think this is what I'd expect here as the Billing schema isn't required? 3 part column names are now deprecated, so we wouldn't want to generate them by default ( "Two-part names is the standard-compliant behavior." - https://msdn.microsoft.com/en-us/library/ms143729.aspx )

If there were two tables with the same name on different schemas then I think SQL Prompt should add the extra qualification which it doesn't currently, so we'll look into improving it in those cases. A better solution as you mentioned is to alias the tables.

Thanks,
Aaron.

rldawg

Schemas are ABSOLOUTELY required. I can have a schema called Billing and another Schema called Archive and have tables called Billing.Invoices and Archive.Invoices.

AND when I type in Select From and I pick the invoices table from the Suggestions, it automatically resolves the schema name for me and puts Billing.Invoices.

If I say SELECT from Invoices, then SQL Server assumes the schema to dbo which is not correct.

There is nothing in that link you gave referring to SQL schemas. Only XML schemas which do not apply to my scenario.

rldawg

SELECT * FROM Invoices

Invalid object name 'Invoices'.

SELECT * Biling.Invoices
(Results)

SQL Prompt had this issue in an older version where the join conditions did not properly fill in the schema and it was corrected years ago.

Perhaps you should create a schema and a table in that schema and try it.

One more thought. Aliasing is a convenience, yes. But SQL prompt should not require me to ALIAS a table to work properly.

Aaron L

Hi Robert,

Apologies, we seem to have got our wires crossed - In your original example the MISSINGSCHEMA wasn't required as it was for a column name and adding the schema would make it a 3-part column name, which is now deprecated according to the link I posted:


In the example you've just posted (SELECT * FROM Invoices) the schema is needed for the table name. From my testing here SQL Prompt 7.1.0.315 is auto-inserting the Billing schema when I auto-complete Invoices, is that not the case for you?

Thanks,
Aaron.

rldawg

Well, I guess I can eat crow. The join condition works fine without the schema.

However I did create and Invoices table in a second schema to illustrate my point. While requiring 3 part naming for a column may be deprecated, it's still best to qualify the columns.

SELECT * FROM Job.Invoices
INNER JOIN Billing.Invoices ON Invoices.iJobId = Invoices.iJobId

Msg 1013, Level 16, State 1, Line 1
The objects "Billing.Invoices" and "Job.Invoices" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Aaron L

Agreed, if there's ambiguity because the two tables are named the same then it'd be better for SQL Prompt to qualify the columns with both the table and the schema so the sql runs (even if it is technically deprecated)

Thanks,
Aaron.

rldawg

It's deprecated in future versions of SQL server. I am using SQL 2012 and others still use SQL 2008.

My contention is that SQL prompt should always provide suggestions that are syntactically correct and will actually work.

You will note that you're jumping the gun on a deprecated feature. It hasn't even been determined when 3 and 4 part naming will not be supported.

The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined.

Aaron L

Hi Robert,

We had a look into implementing using 3 part column names if they're needed but it'll require a little more work than we'd hoped. We've put it on our backlog which will be prioritised against other work and it should be included in SQL Prompt 7.3.

According to Microsoft, 3 and 4 part column names were a deprecated feature in 2012 and 2008 too, and while Microsoft might not remove the feature outright it's still best practice to avoid using them if possible.

Thanks,
Aaron.

rldawg

Thanks for the update.

Without aliasing the tables, it's impossible to avoid. I find it hard to believe that MS and Red-Gate are telling me that I must alias tables in order for joins to work properly in the future. And perhaps that's why they haven't gotten rid of 3-4 part naming capability yet. Aliasing has always been optional, not required.

Thank you for addressing my concerns, I appreciate the feedback and the consideration.

Thanks,
Robert