SQL Server Data Tools provides an interactive, full featured query window for testing T-SQL commands against the connected database. This tutorial explores the Query windows, showing how to change data source and how to choose output formats for the results. There is a section that looks at how to use DDL to create a table in the database. Finally, you’ll see how to write a query that filters data by a numeric range.
- [Voiceover] In the next two videos in the chapter, I'll be working with views and stored procedures. While editing these items, I need a way to test my SQL before saving the items to the database. SQL server data tools provides a query window for this purpose. To open the query window, right-click on your database, and choose new query. You get a blank window. At the bottom of the screen, you can see some status information. New connection open, you can see which database I'm connected to, and some other information here. At the top of the window, you can see which database I'm running my queries against.
In this drop-down, I can switch over to other databases inside this server. I can see I've also got the toursEditable database available here. Another way you can specify the database is by typing a use statement at the top of the query window. Now, as I start typing N-O, you'll see I get a drop-down or intelliSense window that's prompted me for the names of the items that start with N-O. If you do not see this drop-down, make sure it's enabled by going to the SQL menu, and look at this top item. This is a toggle menu.
If there's a yellow border around the item over here, that means that the intelliSense is enabled. Now it's time to write a query. Usually the first query that you'll learn to write is a select statement, which means you're selecting some of the data out of a table, or one or more tables. Select all the fields from a table. Now, what kind of table do I have? Let's go look. I've got about 15 tables in this database. One of the simplest tables is this one here, the shippers table. So I use that. Again, I get an intelliSense here to verify that I am using the right entity.
Now that I've got my select statement, it's time to execute it. You execute it by clicking on this button here. There's two choices available here, execute and execute with debugger. Let's start with the top item. When I execute the query, I see the results on the bottom of the screen. They're shown in a grid, and this grid has three columns. That's because there's three fields inside the shippers table. There they are, and here they're showing in the results. I can change the type of information that's returned.
I should say, I can change the format that the information's returned from the query by using this drop-down. Currently it's set to results as grid. I'll choose to set it as results as text, and when I re-execute the query, now I get to set text representation of that information. And I can also choose results as file. This will prompt me for a file name to save the output. Switch back to the grid. There's some other buttons up here. I can disconnect from the database.
I can change the connection to another source, but I already did that here by using the use statement. There's also the ability to write a brand new query window from here, instead of right-clicking on your project, and choosing new query. SQL contains commands for defining the database objects. These are called DDL, or Data Definition Language, so I could create or modify a table from that query window. So what I'll do is I will comment this out, and then I'll come down here and I'll do a create table. Within the parentheses, you define the fields for the table, and there's a comma to separate the different fields.
See if I get any errors. I don't see any red squiggles. See that if I remove the parenthesis here, I get a red squiggle, which is like you see in code in Visual Studio. This is prompting me, and telling me that something's wrong with my SQL statement. Okay, I'm ready to run this, so I'll execute the query. Tells me that the command completed successfully. This command, create table, does not return any data. It returns a status code, but it doesn't return any data. You know, if I have to go over here to look in my database, right-click on tables and choose refresh, I can see that there's a vendors table in there.
Did you notice that using the query window for DDL statements bypassed the power buffer? So use DDL statements with caution. They are available, but you are missing the benefits that go with using the power buffer features. Essentially, what Microsoft's giving you here is a query window that lets you do any operation that you have permission to do against the database. Let's run another query. This time, I'll query the products table. Again, I'm getting some intelliSense here. The name of the products table is there. I'm going to do a dot operator here, which will drill down and get one of the fields in the products table, and I can alias this field with a new name for the query, so what I'll say is, I'll call this products in range, and I'll move this down to the next row, and I'll try to execute it and see what happens.
So it went into my products table and pulled out one column, and it's showing me all the records. There are 77 rows available, and I'm seeing one column, and I renamed the column to products in range from the original value, which is productName. So the next step is I'll use another field, I want the unit price. Forgot my comma, that's why I'm not getting intelliSense. I need a comma after this value. And I should have used as here. Makes it clearer what I'm doing. And I'll try writing this query, and I'm getting two values back.
Next thing I want to do is I want to order them by the unit price. Descending order. And then the last thing I want to do is I want to put in a where clause, and I want only the products that unitPrice is greater than five dollars. Gonna scroll down to the bottom of the list, I can see that the maximum value in here is six dollars, so it looks like my query's working. I can also do a range of values. I'll say where products.unitPrice between five and 10.
Now I've got a query that returned 12 rows. Highest price item in this is 10, lowest price item is six. I like this query. I know I'll use it frequently for reporting purposes. One way to save it to the database is to turn it into a view.
- Exploring integration with Azure data sources and other Microsoft databases
- Working with SQL Server Express LocalDB
- Connecting to databases with Server Explorer
- Working with database tables
- Manipulating tables with the SQL Server Object Explorer
- Creating queries and custom views
- Creating stored procedures
- Comparing databases
- Building a data project in Visual Studio
- Refactoring a database object
- Deploying the database
- Connecting to Azure databases
- Deploying data projects to Azure
- Integrating Visual Studio with Entity Framework