Author Archives: Matt

T-SQL: More things to SELECT

Last week I posted about the basic usage of SELECT when extracting data from a table:-

SELECT [columns or columns (comma separated)] FROM [a database table]

SELECT doesn’t just work on comma separated column names from a database table though.   Did you know you could do this:-

SELECT 'Hello, Matt'

Try it, and then try this:-

SELECT 12345

And now this:

SELECT 12+150

What I’m trying to demonstrate is that you don’t just have to supply column names or a FROM to your SELECT statements – in fact any allowable expression or value will do – this will come in handy in later tutorials.  Until then try the following and see what happens:-

SELECT 12/4
SELECT 'Mat' + ' ' + 'Hello'

You can even comma separate hard-coded values:-

SELECT
    'This',
    'Is',
    'Cool',
    12,
    18/9

Quick quiz – what column names does T-SQL provide when you run these statements?  Any ideas how you might change these?

Answers next time.  In the meantime think about how what we’ve looked at today might be useful.

SQL is a declarative language

In the second of my articles about T-SQL I want to take a slight detour and talk about the kind of programming language SQL is.

In my previous article I wrote “One thing that you’ll discover with SQL as a language is that it is quite descriptive” – what I was alluding to was that SQL is a declarative language.

What do I mean by that? 

A declarative language is one where you tell the computer in code what you want it do, but how it goes about doing it is decided by the compiler/engine.

Let’s look at a really simple example:-

SELECT FirstName, LastName FROM People

This basic SQL statement asks the database to provide the first and last names from a table called People.  It doesn’t ask any more or less from the database engine.  No order is specified for the records to be returned in, no technical details about how to best extract the data from the database is given.  All the decisions on how the data ends up being returned from the database engine is completely up to the database engine itself.

In some other programming languages you might have to write a considerably larger body of code to get similar information, but this isn’t the case with a declarative language like SQL.

There are many more in-depth discussions of what declarative programming is here, but some of these are (I think) outside the scope of what I’m trying to explain.

Next time round we’ll look at more stuff you can do with the SELECT statement.

T-SQL: The SELECT Statement

In this, the first of a series of articles about T-SQL, I want to talk about the (very) basic usage of the humble SQL SELECT statement.

The SELECT statement is the fundamental statement you need to use in T-SQL (or any other flavour of the SQL language) when you are wanting to retrieve data from a database.  It’s purpose is to provide a dataset consisting of columns (sometimes referred to as fields), and rows (sometimes referred to as records).

When retrieving data, the basic syntax of a SELECT statement is as follows:-

SELECT
  [columns or columns (comma separated)]
FROM [a database table]

Let’s assume your database contains one simple table (Addresses) with just five rows of data in it:-

select table

To retrieve a resultset that looks like the above, your SELECT statement would look something like this:-

SELECT
  Name, House, StreetName
FROM Addresses

One thing that you’ll discover with SQL as a language is that it is quite descriptive.  What you’re asking the database engine to do is to SELECT the columns ‘Name’, ‘House’ and ‘Streetname’ FROM the table ‘Addresses’ and return all rows for you.

At this stage I’d like to point out that the two main SQL keywords in this statement (SELECT and FROM) are not case-sensitive in T-SQL (or any other flavour of the SQL language as far as I’m aware).  Nor is there any particular rule in terms of code layout (indenting, new lines etc).  With that in mind, the following versions of the SELECT statement are equivalent in terms of their output:-

SELECT Name, House, StreetName FROM Addresses
select
  Name,
  House,
  StreetName
from
  Addresses

How you choose to lay out your SQL code is up to you or the standards set by your organisation (I’ll cover standards in a future article).

In terms of columns, you can provide any of the available columns, or all of them.  There are no rules around a minimum or maximum number you can provide.  All of the following statements are valid:-

SELECT Name FROM Addresses
SELECT Name, House FROM Addresses
SELECT Name, StreetName FROM Addresses
SELECT StreetName, Name FROM Addresses

Note that the columns returned in your dataset will appear in the same order of their declaration in the SELECT statement.

In the next few articles I’ll go into further exploration of the uses of SELECT but I’m going to leave it here for now.

Summary

  • The SELECT statement is used to retrieve columns and rows from the database.
  • A basic SELECT statement to retrieve data from a table only requires SELECT and FROM.
  • You can provide some, or all, of the columns in your table in the SELECT statement.

SSRS: Remove the toolbar from an embedded SSRS report

If you embed an SSRS report on your page in an iframe using the Report Server URL, you’ll get the toolbar included:-

toolbar

To get rid of it, add the following to the end of your URL:-

&rc:Toolbar=false

…and the toolbar will disappear:-

toolbargone

Really useful if you want to embed a report containing just a chart or a simple table without the unnecessary cruft.