SQL Server 2008 R2中USE指令能否提升同库只读查询性能？

Great question—let’s break this down, focusing specifically on read-only queries where all tables and joins live within the same database, like your example.

Core Conclusion #

For read-only queries (or any queries) that only interact with objects in a single database, the USE command does not provide any performance benefit.

Why USE Doesn’t Boost Performance #

Here’s the breakdown:

• The USE statement only switches your session’s database context—it doesn’t change how SQL Server parses, optimizes, or executes your query.
• When you run a query with unqualified tables (like [table1] instead of [ourDatabase1].[dbo].[table1]), SQL Server checks your current context to locate those tables. If you’re already in the target database, adding USE ourDatabase1 does nothing extra. If you’re in a different database, USE just lets you skip typing the full database name—but the query execution plan and runtime performance will be identical to using fully qualified table names.
• Query performance depends on factors like index design, statistics freshness, query structure, and available resources—not whether you used USE to set the context. The SQL Server 2008 R2 query optimizer generates the same execution plan regardless of whether you switch context via USE or use fully qualified object names.

When USE Is Useful (But Not for Performance) #

While it doesn’t help with speed, USE has practical uses:

• Syntax convenience: If you’re running multiple queries against the same database, switching context once with USE saves you from repeating the database name in every object reference.
• Avoiding errors: If your session is in the wrong database, USE ensures unqualified table references point to the correct objects (preventing "invalid object name" errors). But this is about correctness, not performance.

Your Example in Context #

Take your sample query:

USE ourDatabase1 
SELECT T1.id, T2.id, T1.Col1, T1.Col2, T2.Col3 
FROM [table1] T1 
LEFT JOIN [table2] T2 ON (T1.id = T2.id) 
WHERE (T1.Col1 IS NOT NULL) 
ORDER BY T1.Col1, T1.Col2

• If your session was already in ourDatabase1, the USE line is redundant—removing it won’t change how fast the query runs.
• If your session was in another database, USE lets you omit the ourDatabase1. prefix, but the query will perform exactly the same as if you wrote FROM [ourDatabase1].[table1] T1 without the USE command.

Final Notes for SQL Server 2008 R2 #

This behavior isn’t unique to 2008 R2—it’s consistent across most modern SQL Server versions. The context switch from USE is a lightweight operation, but it doesn’t give the query optimizer any extra information or leverage to improve performance for single-database queries.

内容的提问来源于stack exchange，提问作者JosephDoggie