Roles Across Schemas

Question

Roles Across Schemas

Steve Jones - SSC Editor

SSC Guru

Points: 736150
More actions
July 12, 2023 at 12:00 am

#4220765

Comments posted to this topic are about the item Roles Across Schemas

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

edwardwill SSCertifiable Points: 5848 More actions · Answer 1

Why write this:

SELECT TOP 10 
s.stateid, 
s.statecode, 
s.statename
FROM webapi.states AS s;

when you could save yourself some typing and write:

SELECT TOP 10 
stateid, 
statecode, 
statename
FROM webapi.states;

I've never understood the compulsion to alias tables except where absolutely required (e.g. JOINing the same table twice etc.)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

standards - and for those that do work a lot on this and use tools to help with coding, putting the alias can be automatic, and expanding the columns will also include the alias if one is given from start - so no typing saved at all.

and should you need to add another table to the query, those columns are already aliased and saves you time and also prevents errors where you could pick up the alias/column from the other table.

And... in many cases where you didn't do it from start, and you add a new table, many people will then NOT alias the columns unless they are common to multiple tables - and that leads to people in the future having to figure out which table the does the column belong to.

edwardwill SSCertifiable Points: 5848 More actions · Answer 3

frederico_fonseca wrote:

standards - and for those that do work a lot on this and use tools to help with coding, putting the alias can be automatic, and expanding the columns will also include the alias if one is given from start - so no typing saved at all.
and should you need to add another table to the query, those columns are already aliased and saves you time and also prevents errors where you could pick up the alias/column from the other table.
And... in many cases where you didn't do it from start, and you add a new table, many people will then NOT alias the columns unless they are common to multiple tables - and that leads to people in the future having to figure out which table the does the column belong to.

What standards? In any case, any standards I've seen regarding aliasing tables refer to the undesirable practice of using a single letter as an alias which, in a complex, multi-join SELECT, makes matters worse rather than better.

If you don't provide disambiguating table prefixes to columns with names shared across multiple tables then the SQL will throw an error. In the simple example to which I referred, it was one table and therefore no need for an alias at all.

In your final comment, I agree that not prepending the table to the column in the SELECT where there are JOINed tables is sub-optimal; in my practice I use that actual table name which is a whole lot more useful than a single letter. That way the reader can see at once which column belongs to which table.

YMMV of course.

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 4

In this case, tooling did this for me. No typing involved. This makes it clear where the column is located, and if there were joins, this is required.

We often alias because adding the full name is cumbersome and can be more typing.

edwardwill SSCertifiable Points: 5848 More actions · Answer 5

I don't use "tooling" - just SSMS and touch-typing for me. I remain unconvinced by the arguments for aliasing, but as I'm 66 and retiring in a few months I'm not particularly exercised one way or another! But thank you for responding, and thank you for the QotD.