T-SQL 101: 19 Querying literals, expressions, and functions in T-SQL

Apart from data just in a table SQL server can select other things like the ones shown here:

If I say SELECT 2, it just returns the value 2.

If I say SELECT ‘Hello’, it just returns Hello.

Both of those are examples of what’s called a literal value, which is an exact value that doesn’t change.

SELECT 4 + 5 is an example of an expression. This is where we can work something out to get the value that needs to be returned. No surprise, that will return 9 just as you’d.

Finally, we can also select from functions. SYSDATETIME()  is a function that returns the current date and time at the server. We don’t have to care about how that works internally, we can just SELECT it, and it’ll tell us the current date and time.

So we’re able to use expressions and values and both can be returned. Functions, though, are programmable objects, which can be used either as values or even as tables in a FROM clause but we’ll see more about that in a later post.

Not all SQL engines do this

It’s worth noting that not all SQL database engines allow us to have a SELECT statement without a FROM clause. For example, in Oracle, you can’t just say SELECT 4 + 5. Instead, they have a dummy table called dual, which is a single column, single row table with a dummy value. Then you can say SELECT 4 + 5 FROM dual; instead.

I’m glad SQL Server doesn’t require this.

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-05-27