Search code examples
sqlsql-serverjoinaliasdbo

SQL Alias vs dbo.prefix - query performance


Assuming you're using MS SQL Server:

Which of these queries will execute the fastest, and WHY?

I'm going to guess the last, as I'm thinking that Aliases are only resolved to their tables once?

SELECT  Account.AccountName, 
        AccountStatus.AccountState
FROM 
        dbo.Account
        INNER JOIN dbo.AccountStatus ON Account.AccountStatusID = AccountStatus.AccountStatusID
GO

SELECT  dbo.Account.AccountName, 
        dbo.AccountStatus.AccountState
FROM 
        dbo.Account
        INNER JOIN dbo.AccountStatus ON dbo.Account.AccountStatusID = dbo.AccountStatus.AccountStatusID
GO

SELECT  A.AccountName, 
        AST.AccountState
FROM 
        dbo.Account AS A
        INNER JOIN dbo.AccountStatus AS AST ON A.AccountStatusID = AST.AccountStatusID
GO

Solution

    • They will be exactly the same in execution terms
    • The compile time differences won't be measurable
    • The 3rd form is the most readable
    • The 2nd form bloats the query
    • The 1st form bloats the query and is the most confusing