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.

Querying a Database

When we want to retrieve information from a database, we query the structure with language appropriate to the database.

Remember right at the start of this series we saw that a database could be a phone book or a recipe book. So how do we find the phone number of Randolph West? By looking up the surnames starting with W, and going through all the Wests in alphabetical order until we get to that entry.

The same goes for finding the recipe for lemon meringue in a recipe book. Start at the index at the back, look through the desserts, and then search for meringue.

In a RDBMS (relational database management system), the language for querying data is called Structured Query Language, or SQL. We can pronounce it like “sequel”, or sound out each letter.

SQL Server is commonly pronounced “Sequel Server”. MySQL is pronounced “My-S-Q-L”, sounding out the letters (some people pronounce it “My-Sequel”). It all depends on who’s saying it. PostgreSQL is just pronounced “Postgres”, because seriously.

These, along with Oracle, are the major players in the RDBMS industry.

ANSI SQL

When it comes to putting information into a database server, and getting information out, we can write queries that look very similar across platforms. This is thanks to a standards body called ANSI (American National Standards Institute), which (with proposals from each vendor) has suggested ANSI SQL syntax that all vendors should use.

For the sake of this series, we will assume that the vendors do follow the standard exactly, but in reality it isn’t that simple.

Putting data in: INSERT

Adding new data to a database is performed using an INSERT operation.

Changing data: UPDATE

Modifying existing data is done with an UPDATE operation.

Getting rid of data: DELETE

Removing rows from a table is performed with a DELETE operation.

Getting data out: SELECT

The vast majority of operations in a database has to do with data retrieval. To get data out, we SELECT it.

CRUD

In technical circles, between software developers and database developers, we might refer to these four operations using the mnemonic CRUD, which stands for Create, Read, Update, Delete.

When referring to a specific database or application, it could mean that the database is just being used as a data store (a virtual box of random stuff) and may not have proper relationships between the tables, nor be normalized.

There’s nothing necessarily evil about denormalized data, because the application code may handle that intelligence. Be wary though. Using an RDBMS to store non-relational data might cause headaches.

Next time we will write our first SELECT statement to query a database. Stay tuned!

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

SQL Server Management Studio v17.0

Version numbers are confusing. SQL Server Management Studio (SSMS), the client user interface by which most DBAs access SQL Server, was decoupled from the server product for SQL Server 2016.

For the last 18 months or so, we have been receiving semi-regular updates to SSMS (which we can download from Microsoft, for free), which is amazing and awesome.

SQL Server 2017 was recently announced, as I mentioned last week, and the internal server version is going to be some variation of 14.0. The compatibility mode is 140. This follows logically from previous versions.

Management Studio, however, has taken a different tack with version numbers. The latest version, which was released at the end of April, is 17.0.

So if you’re confused, you’re not alone.

To make up for some of that confusion, here’s a neat trick in SSMS 17. Let’s say I want to present a session at a SQLSaturday. In the past, I had to modify the fonts and sizes for myriad settings in Management Studio. Paul Randal has a seminal post about this very thing.

With version 17, we can enable a new feature called Presenter Mode, which automatically sets the fonts and sizes of the SSMS interface to improve visibility when sharing your screen in a conference call or using a projector.

In the Quick Launch textbox on the top right of the screen (press Ctrl+Q), type the word PresentOn.

Our standard SSMS interface changes from this:

to this:

To switch it back to the default view, it’s not PresentOff as we might expect. Instead, we must type RestoreDefaultFonts in the Quick Launch textbox.

Note: the results grid will not take on the new settings until SSMS is restarted. Keep this in mind when switching Presenter Mode on and off.

If you have any more SQL Server Management Studio 17 tips to share, find me on Twitter at @bornsql.

SQL Server 2017 Announced

By now you will have heard that the next version of SQL Server has been announced. There’s no release date yet, but Brent Ozar seems to think it’ll be before June.

There are many new features, but the biggest deal is that SQL Server 2017 runs on both Windows Server and Linux. Yup, SQL Server 2017 is certified to run on Windows Server, Red Hat Enterprise Linux, Ubuntu, and SuSE. (You can even run it on macOS in a Docker container.)

There are some big improvements to the Query Optimizer as well, starting with Adaptive Query Processing. It’s like a smart go-faster button, making incremental improvements to your queries the more often they run.

Despite being in pre-release mode, Microsoft states that 2017 is production-ready, which means that it should work as expected.

Watch the announcement, and download the latest technical preview to try it for yourself.