T-SQL 101: 9 Finding out what's in a SQL Server table

In the last post, I described what tables were, and then showed how to view their contents using an option in Object Explorer in SQL Server Management Studio (SSMS).

If you’ve ever seen a SQL query of any type, you will have seen one like this:

It says to return all columns from the table, and to return all rows.

Fair enough.

But there are a few things to understand about this simple statement:

Column Order

The first is, that you haven’t said what order you want the columns to be returned in. In the SQL standard, columns don’t have a predefined order within a table. This means that the database could return the columns to you in any order that it wants, and that could change every time you run the query, and it would still comply with the standard.

SQL Server is different because it does have an ID for each column, and it will always return them to you in that order. However, you don’t want to depend upon the order, because someone could recreate the table with a different column order, and if you’re just using the position of the column to identify it, you’d have an issue.

Row Order

The second issue is that you haven’t said which order you want the rows returned in. The database engine could return the rows to you in whichever order it wants to. There’s a common myth that rows will always be returned to you in a particular order if you don’t say which order you want. That’s not true.

Schema Name

You’ll notice that I said dbo.Cinemas, not just Cinemas in that query. It’s a long story, that’s inappropriate for this introductory material, but just, for now, assume that you should always specify the schema name as well as the table name.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-03-19