3

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
jimasp
  • 962
  • 9
  • 26

1 Answers1

8
  • 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
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    Is there the case that whether or not the schema name is present makes a marginal difference? But that this is only relevant in table reference not the field references? (Possibly due to checking that the table(s) definitions are the same as the schema it was originally compiled against? Though that's a guess...) – MatBailie Dec 14 '11 at 09:56
  • 2
    @Dems: yes, if schema is omitted from the FROM clause then there is a difference. In the select or where etc it makes no different – gbn Dec 14 '11 at 09:57
  • 1
    I would enjoy SO having a Required comment field with down-votes. I can't see how someone expects their down-vote to be useful without saying why they down-voted.... – MatBailie Dec 14 '11 at 10:10