SQL 101: What Happens When…

I had an interesting question asked this last Saturday when I was giving my Intro to SQL session at SQL Saturday Redmond: “What happens if you cast a NVARCHAR string as  VARCHAR?”

I had an educated guess but I didn’t have the time during the session to test it out: Casting NVARCHAR to VARCHAR. It won’t throw an error but it will destroy any data in the string that isn’t VARCHAR compatible. Try out the test below yourself!

/* Preface the string with N or
SQL will pre-emptively destroy
the data before storing the string */
DECLARE @nv AS NVARCHAR(10) = N'╥«┘{║‼℈≠'
, @v AS VARCHAR(10);
SELECT @nv, CAST(@nv AS VARCHAR(10));
/* And by reassigning we can see
the data is permanently lost */
SELECT @v = @nv;
SELECT @v, CAST(@v AS NVARCHAR(10));

The question was asked initially because she had found a place where she had to convert a uniqueID to a string and was concerned that perhaps this could cause issues. The uniqueidentifier is a series of hexadecimal numbers concantenated together with hyphens (for example: 851CC794-E0CB-49DC-976F-1687F62D2188). They will always be 36 “characters” long. The good news is that according to BOL the uniqueidentifier is consider a character type for the purposes of conversion. Attempting to cast a string that is longer than 36 characters will truncate the original string to the first 36 before it attempts the conversion. Conversely casting a uniqueidentifier to a string datatype will result in a 36 character string (unless you specify less than 36 characters in which case SQL will throw an error).

Presenting Away!

I’ll be presenting my introduction to SQL queries in just a few short days at SQL Saturday Redmond! If you know somebody that’s just getting started with SQL and is wants an introduction to how to query and join tables: have I got a session for you! (It’s this one.) This will be my first time presenting away from my home turf of Spokane so I’m only a little nervous about it: I hope I see you there!

SQL 101: Query Basics

SQL is an acronym for Structured Query Language so when getting started with it I think it’s best to think about how it was put together to be able to ask questions about data. With that in mind let’s a take a look at some beginning SQL to understand how to write queries.

Queries can be very simple but they won’t answer very many questions in their most simple forms:

SELECT 'MyData';
/*
Best practices of course would be to include the specific columns
in your select list so the SQL query engine can optimize the return
of the necessary data. Selecting * from the table is a lot like
eating an entire pizza because you wanted a single slice.
*/
SELECT *
FROM myTable;
view raw sqlquery1.sql hosted with ❤ by GitHub

Will not a table and filter are clearly not required to write a query, the most common use for writing a query will require 3[1] parts to be useful:

  • a SELECT-ion of things (columns)
  • FROM a a collection of things (a table)
  • WHERE a criteria is met (filter(s))

I happen to have a database of movie data. Let’s explore some common filter operators by asking some questions about movies. Filters based on mathematical operators are the easiest to start with. We can ask for films with exactly 2 Oscar nominations or more than 4 or less than 6. We make these filters exclusive or inclusive by adding the “=” operator to the comparison. We can also exclude a specific number by using “<>” (Microsoft SQL Server will also support != but this is outside the ANSI standard so “<>” would be best practice as it will be usable in any version of SQL.)

/* films with exactly 2 oscar nominations */
SELECT name, releaseDate
FROM films
WHERE oscarNoms = 2;
/* films with more than 4 oscar nominations*/
SELECT name, releaseDate
FROM films
WHERE oscarNoms > 4;
/* films with 6 or less oscar nominations*/
SELECT name, releaseDate
FROM films
WHERE oscarNoms <= 6;
/* films with any number of oscar nominations except 3 */
SELECT name, releaesDate
FROM films
WHERE oscarNoms <> 3;

We can have multiple conditions in our WHERE clause and these are combined with AND or OR statements. We can ask for films with 1, 2 or 3 Oscar nominations (but we’ll also see we have a new operator IN that is cleaner and accomplishes the same goal). We can check for movies released on or after Jan 1, 2017 and were nominated for at least 1 Oscar. We can ask about movies released before the year 1950 that made more money than they spent.

/* What movies have been nominated for 1, 2 or 3 Oscars? */
SELECT name, releaseDate, oscarNoms
FROM films
/* if any one of the following conditions is true for a row it will be returned */
WHERE oscarNoms = 1
OR oscarNoms = 2
OR oscarNoms = 3;
/* These can more easily be written with the IN clause */
SELECT name releasedDate
FROM films
WHERE oscarNoms IN (1, 2, 3); -- this is the same as the OR statement above
/* Oscar nominated moveis released on or after Jan 1, 2017 */
SELECT name, releaseDate, oscarNoms
FROM films
/* both of the conditions on either side of the AND need to be true for the row to be valid */
WHERE releaseDate >= '2017-01-01' -- SQL will convert this string into a date YYYY-MM-DD is the easiest way to write this
AND OscarNoms >= 1;
/* we can even compare column values in rows to each other */
SELECT name, releaseDate
FROM films
WHERE releaseDate < '1950-01-01'
AND boxOffice > budget;
/* Bonus query!
You can combine and seperate WHERE clauses with parenthesis
these can be used to have more complex statements for
returning rows: essentially you working to return a true/false
statment for returning any given row (also called tuples)
*/
SELECT name, releaseDate
FROM films
WHERE ( /* this outer statement is true if either of the two
parentheticals below are true */
(oscarNoms > 2) OR
(releaseDate > '1980-12-31' AND budget >= 100000 )
) /* if the previous was true AND the box office was less
than $500,000 then the row can be returned */
AND boxOffice < 500000;
view raw sqlqueryorderby.sql hosted with ❤ by GitHub

If you’re just getting started with writing SQL queries I hope this was helpful! Next up I’ll cover a few tips for working with dates as well as specific operators for filtering strings.

[1] It’s important to note that SQL does not guarantee any sort of order in your results and thus the last part of a query is generally an ORDER BY statement that tells SQL what order to put the results in.