The SQL querying environment - SQL Server Tutorial

- [Instructor] One of the most common database queries is to retrieve a number of records from a data table. In fact, this query is so common that the graphical interface here in Management Studio provides this query as a menu item, so it can be executed without having to write any SQL code ourselves. Let's take a look at it by expanding the AdventureWorks database and opening up the Tables folder. Then, I'm going to find the table called HumanResources.Department. Let's right click on it and choose Select Top 1,000 Rows. That'll execute a query that answers the basic question, "What data is in this table?" Since some data tables can be very large, the results on the bottom half of the screen will only display up to 1,000 records that are stored in the table, if your table has that many. And these results are useful for getting a basic feel of the contents. In the case of the Departments table, we can see all of the department names and what group they belong to. This query is useful, but not very flexible. Every time I run the query it's going to return the same information, unless the data is changed in the original table. Before we get into writing our own queries, so that we can ask our own questions about the data, I thought it would be beneficial to take a tour around the screen to get our bearings in the interface. At the top of the interface, we have the query itself. This is the SQL code that was executed when we chose the Select Top 1,000 Rows from the pop up menu. Here is where we ask our question of the data, and tell the system what information we'd like returned, as well as any conditions or filters that we want to apply to the results. This is where we're going to spend the majority of our time in this course, or any, to write these query statements. Once you've written the query, you'll come up to the menu bar and press the Execute button. The shortcut key for this is F5 on your keyboard. You're going to be Executing a lot of queries in this course, if you follow along, so F5 is a good shortcut to commit to memory. Executing the query tells the system to go out and fetch the requested data from the database. This is when the data is read from the tables, compiled into the format that you specified, and filters are applied so that you only get back the data you requested to see. Finally, the Results window opens up at the bottom of the screen to show you what came back from the database. These results show you the current state of the data when you ran the query, but don't allow you to modify any values. The results are formatted like a data table grid by default. But using these buttons up here on the Toolbar, you can change that output. You can see the default here is to send the results to grid. That's what we're seeing down below. Another option that I have is with this button here, that says Results to Text. Let's turn that on and execute the query again to see the difference. Now I can see that the results are being returned in a text format that I can copy out. The other option I have is with this third option. It says Results to File. Let's toggle that option on and press the Execute button again. That'll prompt me to save the results as a Report File with the .rpt extension, but this is just a standard text file that you can open up in any text editor, such as NotePad. I'm going to press Cancel here, and switch it back to Sending Results to the Grid. Then I'll press the Execute button again to refresh the results. Next to the Results tab is a tab for messages. This will display any error messages if your query was unable to execute for any reason. Or some basic statistics if the query ran without any problems. In this case, I simply see that 16 records were returned from the department table. We also have the time and date stamp that the query was executed. The number of records returned by a query is often a very useful piece information to have. So in addition to finding that here on the Messages screen, you'll also find it in the yellow information bar at the bottom of the screen, way over in the bottom right-hand corner. Any time I run a query, this is the very first place that I look to get a very high-level picture of what was returned from the database. Now, this query is pretty basic, and it didn't take the database engine any time at all to return the results. It's not always going to be the case, though, that your query results appear immediately. Some queries that you execute might involve complex calculations, or joining information, from very large data tables. And it's not uncommon for queries to take minutes, or even hours, to complete. Up on the Tool Bar you have a button you can use to stop a long running query. It's this square cancel button there. You can press this if a query is taking too long to run and you want to back out of the operation. Now the query itself is just a basic text file and you can save the script of your query to your computer if you'd like to. To do that you'll come up to the File menu, and then come down to Save. The default is to save the script with just this generic file name, SQL Query, and then a number. The shortcut key is Ctrl+S. If I select that option, it will prompt me to save the file out to my computer. I'm just going to place this here on my desktop and we'll give it a better name. I'll call it Departments. You'll notice that the name changes up here on the Tab. Now I can close the Tab and go on with some additional work, and when I want to, I can reload the query from the file. To do that, you'll come up here to the File menu, you'll choose Open, and then we'll choose to open a File. The shortcut key for this is Ctrl+O. Then I can navigate to my desktop, I'll find that sequel file right there and press Open, and then I'll reload the script, where I can execute it again. So this can save you some typing if you have queries you need to run on a regular basis. Finally, there's one small change that I want to make to the interface that'll make it easier for you to follow along throughout the course, and that's to add line numbers to the screen. To do that, I'll come up to the Tools menu and come down to options. Then I'll find the options for the Text Editor, and I'll open that up. And then I'll find the options for Transact Sequel. Here I have a check mark to turn on line numbers. I'll turn that on and press okay. That'll add line numbers to left side of my text window, and now I can help direct your attention to specific lines, as we continue to learn how to write custom queries for SQL Server.