October 31, 2018 at 3:00 pm
andycadley - Wednesday, October 31, 2018 2:10 PMYes, by "small" scope I meant it's only guaranteed to be in effect for a single query. It might be a good idea not to confuse people by changing aliases for the same object in multiple queries in a procedure (and I mostly agree there) but that's a convention, not a scope.What I mean by not deriving them from the name of the table is that it often devolves into poor readability when you start having self-joins or joins to the same table multiple times. For example, if you have a "Person" table that is joined to twice, once for the manager details and the other the employee, I'd much rather see aliases like MGR and EMP than P1, P2 because it's a lot easier to understand. YMMV.
I try to stay broadly consistent. Thus, my aliases in that case would be, say, p_mgr and p_emp (or in upper case if you prefer). I only use an _ in the alias if it's self-join of some type, or for CROSS APPLYs used only to gen column alias names, since they are inherently distinct from a standard join.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 1, 2018 at 6:16 am
Lynn Pettis - Wednesday, October 31, 2018 8:56 AMsgmunson - Wednesday, October 31, 2018 8:48 AMLynn Pettis - Wednesday, October 31, 2018 8:36 AMsgmunson - Wednesday, October 31, 2018 8:12 AMPhil Parkin - Wednesday, October 31, 2018 7:49 AMThe aliases are in scope only for the current statement batch – not throughout entire procs or scripts. Perhaps that is what was meant by small?Perhaps, but imagine a long stored procedure (of which we have perhaps 1,000 or more), and then having different aliases throughout for the same object. Really bad coding at such point, so no, I can't quite go along with that. Long stored procedures are REALLY COMMON in my current assignment.
You should use the same alias for the same table in multiple queries in a stored procedure. For example using sys.tables I use tab for the alias. If I am using sys.tables in multiple separate queries in a stored procedure I will use that alias each time. I even use the same the same alias with each CTE where the table is used in a single query as the alias is only relevant in the CTE not across CTEs.
Not sure you interpreted my statement correctly. I would certainly NOT want a different alias for the same object in different parts of the procedure or script. If I had a really small scope for aliases, I might well be forced into such behavior, which is why I can't support that concept. Scope for aliases, from my perspective, needs to be rather broad.
Not sure how you can scope an alias beyond the query it is used. That just doesn't make sense. That an alias for a table can be used in a CTE and the same alias used for the same table in the outer query that uses the CTE makes sense to me as it is simply dynamically created single use (meaning the query it is defined in) view.
So, please define what you mean by broadly scoped.
I prefer to use table aliases as it makes reading the code easier for me.
Ahhhh... there's our problem.... When I use the word scope in respect to aliases, I'm referring to the breadth of the alias itself, not to the query scope. Sorry I didn't make that clear.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply