Sometimes I generate execution plans on SQL that I suspect needs optimising.

Here's an example of a plan that was generated today:

SELECT ALL
"MessageDateTime" AS "MessageDateTime",
"Subject" AS "Subject",
"Caller" AS "Caller",
"Company" AS "Company",
"Phone" AS "Phone",
"RecipientName" AS "RecipientName",
"SenderName" AS "SenderName",
FROM "Messages"
WHERE "Deleted" = False
ORDER BY "MessageDateTime" ASC

So my questions are (if anyone knows):

* What is the advantage of having ALL after SELECT?

* What is the advantage of enclosing column names in double quotes?

* Why does it add the AS alias after each column when the names are identical?

Thanks.

= Steve

On 10/22/2020 5:02 PM, Steve Gill wrote:

Steve

> So my questions are (if anyone knows):

Tim would be the only one that knows but i'll take a stab at it though
some of these are pure guesses (i.e. i would do so something similar if
i had  to write a sql parser).

> * What is the advantage of having ALL after SELECT?

I suspect this is added in pre processing or such to make SQL parsing
match the spec more consistently - spec says it's either SELECT ALL ..
or SELECT DISTINCT ... but ALL/DISTINCT can be omitted so it defaults to
ALL.

> * What is the advantage of enclosing column names in double quotes?

Same reason IMHO - identifiers can use double-quotes and must do so if
they include spaces so would make sense to make it easy for parser.
This way one is always parsing double-quote identifiers internally.


> * Why does it add the AS alias after each column when the names are identical?

Again same reason to pre-process all the identifiers to ensure they are
actually unique.

Your case might be a special case where you did not happen to cinlude
dupes (or used wildcard like *) but imagine something like this

SELECT MessageDateTime, MessageDateTime, Subject ...

which would have been been turned into

SELECT ALL
"MessageDateTime" AS "MessageDateTime",
"MessageDateTime" AS "MessageDateTime1",
"Subject" AS "Subject" ...


or joins etc where tables might have same field names - this way
internally again it's all resolved already to unique field correlation
names

Raul

Makes perfect sense.

Thanks Raul.

= Steve

Raul


Brilliant -= have a +200

Roy Lambert