From the course: Querying Microsoft SQL Server 2022

Change database context with USE - SQL Server Tutorial

From the course: Querying Microsoft SQL Server 2022

Start my 1-month free trial Buy for my team

Change database context with USE

“

- [Instructor] I think writing queries against a database can be a fun process of diving into the data tables and seeing what kinds of useful insights you can reveal. Queries can get rather long, especially when you're joining multiple tables together, performing calculations, and grouping rows to get the final output. So after you've spent a bunch of time crafting the perfect query, it would be a sensible thing to want to save the text of that query as a script. That way you can easily run it again at a later date or share it with a colleague to allow them to benefit from all of your hard work. Before you do that though, there are a couple of enhancements that you can add to your queries to make sure that anyone who runs it has a good experience. Let's create a new query, and let's suppose that this simple select statement is a masterpiece of query design. When you execute the query it goes out to the selected table and returns all of the columns that you've asked for. So far, so good, but how does SQL Server know to query the Adventure Works database and not some other database that happens to be on the same server? Here in Management Studio we can adjust the context of the query using this dropdown menu in the toolbar. But not everyone uses Management Studio for working with SQL Server. Some people log in using a text-based console or another graphical front end that might have a different interface. If we switch our context to a different database, for instance I'll select the master database, and try and execute the query again. This time I get an error. That's because there is no person table in the master database. In order to make sure that the query executes in the intended database, you should add a use statement to the beginning of your script. I'll come up here to line number one and I'll just type in use and the name of the database, Adventure Works 2019. We can also include the go utility command on a line all by itself. This is not a structured query language command so don't put a semicolon at the end of it. Go simply tells SQL Server to execute the lines above as a separate batch of commands from the ones below. The go command helps ensure that operations get executed in the proper sequence. While go is not technically required after the use command, some commands such as create schema will only work if they're isolated as the only command in the batch. For instance, if I come down a couple of lines and type create schema test, you'll see that you get a red underline, and if you hover your mouse over it, it says that the create schema command must be the only statement in the batch. The solution here is to add go on either side of the create schema command. When I do that, the error goes away. If I were to execute the query now, the create schema test command will get isolated and run by itself before selecting information out of the person table. I'll go ahead and remove this from the script though since I don't actually want to create any schemas right now. So that's the use command and the go command. Now, when you execute the query, even if you're inside of the master database still and press execute, you'll see it jumps over to the AdventureWorks database and executes properly. The use statement will improve the experience of other users running your script by automatically ensuring that the query is pointed to the correct database.

Contents