CODEWITHSUNDEEP
sql-serverparametershoisting
Zach Smith
Zach Smith

Reputation: 8961

MS SQL: How are variables and aliases evaluated within query execution?

I'm working on a MS SQL project, and quite new to SQL in general.

In a query that contains this:

SELECT DISTINCT u.sUserType
FROM @users u

And alias (the variable 'u') is being used before a variable has been declared (@param has been previously declared). Then again:

SELECT  
    ...,
    aType.hMy as hAssetType,
    ...,
    ...,
    ...,
    ...,
    etc
FROM @users u
        LEFT JOIN somTableName aType on UPPER(u.sAssetType) UPPER(aType.sType)
        LEFT JOIN etc
        LEFT JOIN etc

I come from very much a Javascript background, where there is the concept of 'hoisting'.

Q: Is it possible to discuss SQL queries in terms of being compiled or interpreted?

Q: Is query execution where vars are defined after usage comparable to Javascript hoisting?

Q: Also, how does this compare to other implementations of the SQL standard?

----- EDIT -----

Answer below points out that an alias is different to a variable.

A variable needs to be declared before it can be referred to, but an

Upvotes: 2

Views: 309

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

  1. Absolutely, queries are interpreted and an execution plan for the entire query is chosen by the SQL Engine before execution begins.

  2. You are conflating aliases with variables. In SQL a variable has to be declared before it can be used. An alias can be referenced "higher up" in a SQL query, and defined "further down", but I doubt it's comparable to "hoisting" whatever that is, because JavaScript is a line-by-line thing, and SQL queries are interpreted as a single set-based operation.

  3. You mean how does this vary among different RDBMS? Not much at all, from the few I've worked with.

Upvotes: 2

Related Questions