How to write a SELECT query

My First SELECT Statement

Microsoft SQL Server makes it really easy for us to query tables. In SQL Server Management Studio (SSMS) for instance, we can right-click on any table we have access to and select the top 1000 rows from that table.

Don’t do this

Please don’t query tables this way in a production environment. It’s a bad way to do it, and you should feel bad.

Writing a SELECT statement should be done manually, the way Ada Lovelace and Grace Hopper intended.

Let’s assume we want to get a list of the stores in our database that we created in the First Look at Normalization post.

The table had the following columns: StoreID, StoreCode, StoreName, Address, ManagerName, and ManagerEmail.

To get a list of all rows and all columns in a table, we write the following statement:

SELECT
[StoreID],
[StoreCode],
[StoreName],
[Address],
[ManagerName],
[ManagerEmail]
FROM
[dbo].[Stores];

Remember from previous posts that the square brackets are a matter of style, and we could just as easily exclude them.

I leave them in because humans are terrible at naming things, and the square brackets make the code less likely to fail.

You’ll notice that there is a semi-colon at the end of the statement. We could have placed the entire statement on one line, like so:

SELECT [StoreID], [StoreCode], [StoreName], [Address], [ManagerName], [ManagerEmail] FROM [dbo].[Stores];

This is more difficult to read. SQL Server doesn’t really care about white space, including carriage returns, so feel free to format your code nicely so that it’s easy for you to read.

If you’re typing this in Management Studio, you can now press the Execute button in the menu, or the F5 key on the keyboard, to run the command.

Tip: in Management Studio, we can select just the text (T-SQL code) we want to run, using the mouse, and then press F5. This will guarantee that no other part of the script will run as well. It’s a useful way to run portions of code in a longer script.

Once we run the SELECT statement, we see a result set.

Click to enlarge

Congratulations! We have asked SQL Server for data, and it has responded with the data we asked for.

Next time, we will be adding data to a table using the INSERT command. Stay tuned.

Look me up on Twitter if you want to ask any questions about this series, on @bornsql.

2 thoughts on “How to write a SELECT query

  • So why is this bad? I am a little confused because this doesn’t explain why this is bad and the the only difference between the query you wrote and the query that SSMS generates is the “TOP 1000”. If I have a table with tens of millions of rows and I want to see a sample of the data I would have to write a very similar query to the one that SSMS generates so that I don’t have to pull several million rows which would destroy the CPU and memory.

    I’m not arguing so much as I would like a better explanation as to why I should “feel bad” :).

    • Hi Ross. This is problematic in a production environment, because firstly, it brings back all the columns in the table, which is as good as using SELECT *, plus it sets an arbitrary limit of 1000 rows, which is probably more than you need to establish a problem (why not 1 or 10, or even 100 rows?). Bringing back that many rows would flush more important data pages from the buffer pool. It’s such an arbitrary number. Add to this the overhead with SSMS constructing the query behind the scenes, doing some fairly ugly stuff to the system tables, it’s getting very expensive on resources. My advice is, don’t do this on production. Your test and dev environment I have no problem with. I hope that answers your question! Randolph

Comments are closed.

%d bloggers like this: