When to begin T-SQL query with USE?

Question

I'm a T-SQl and database newbie, and a little confused. In the T-SQL book I'm reading it says that a USE statement is written to set the database context of the session. Does that mean that there can be more than one database in an instance of SQL-server and the USE statement tells SQL server which database the query will be on?

Answer 1

Yes, there can be more than one database on a SQL Server instance.

Answer 2

Yes, an instance of SQL Server can maintain many databases. You can also specify your database in the connection string in an applciation.

In Management Studio, when you change the database in the drop down menu it automatically runs the USE command for the currently selected window.

Answer 3

Most often your DB is specified via the connection (or connection string); you can use the USE statement to specify a change in the query context.

Answer 4

It's not quite as simple as that. Yes, a server can have multiple databases, but I would caution against using a USE statement unless all of your queries are that way.

Every connection to SQL server has a "current database". That means that any query run over that connection will use that database (though it's possible to access information from other databases by explicitly indicating this with dot notation). The USE statement changes the current database for the connection, so if one statement expects to run in the MyDatabase database (but doesn't specify it with a USE MyDatabase statement) and another query executes Use OtherDatabase, then the first query will fail when run.

For instance:

• Connect to SQL Server to the MyDatabase database
• Execute the following query:

select * from sometable

• Execute the next query:

  use OtherDatabase

  select * from othertable

If we try to execute the first query again it will fail (assuming that sometable doesn't also exist in OtherDatabase) because we've changed the context of the connection with the USE statement. The upshot here is that the USE statement is connection-wide, not specific to an individual query.

In software development, the more common approach is to maintain a connection to the server for each database you plan to query against. That means that the query against MyDatabase will use a different connection than the one used by the query for OtherDatabase. This helps mitigate the possibility of a change in query context causing queries to fail.

Answer 5

you typically do not need to use the "use" statement, because the database name is usually included in the connection string. you can use that if you want to switch databases tho.

sql server can support many databases accessible through the same server. the use statement tells the server what database context your connection will be using.

the maximum number of databases per instance of SQL Server is 32,767.

Answer 6

Yes you are correct. There may be more than one DB per server instance.

Using "USE", lets the server know which database you are refering to by default. You may however make reference to any database (of that instance), by explicitly specifying the database.

The syntax is database.schema.sqlobject (sqlobject is table, view, SP etc.)

For example:

SELECT * FROM myDbOfBirds.dbo.MigratoryTable

or even

USE myDbOfBirds
GO
SELECT * 
-- note how we don't explicity qualify MigratoryTable table (can but do not have to)
-- but that we do so for the Country table  (have to)
FROM MigratoryTable  MT
JOIN MyOtherDB.dbo.Country C ON C.CountryCode = MT.CountryCode
WHERE MigrationMonth = 11

It is important to understand that, like many other settings, USE is a connection-wide setting, which affects all pieces of logic sharing the same connection. For this reason, USE's usage is either reserved to ad-hoc types of queries, or when there is a single user of the connection (or when several users of said connections are known to exclusively reference a single database)

Answer 7

Yes, a SQL Server can host many databases. The USE statement instructs SQL Server to execute following statements in the context of the specified database.

The USE statement is often used to help prevent queries from "accidentally" running in the wrong database. For example, before running a script that creates or deletes tables, you might want to add a USE statement at the top to mitigate the risk of accidentally running the script in the master database.

That said, in most scenarios a USE statement is not necessary, because the connection string used to connect to the server will also include the database.