Max Masnick, PhD

Knowledge Base > Programming >

Microsoft SQL Server (MSSQL) / T-SQL

Last updated: September 29, 2019

What is T-SQL?

Notes for using the SQL dialect used by Microsoft SQL Server (MSSQL), which is called T-SQL.

SQL dialects

From Quassnoi on StackOverflow:

  • SQL is a query language to operate on sets.

It is more or less standardized, and used by almost all relational database management systems: SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc.

  • PL/SQL is a proprietary procedural language used by Oracle

  • PL/pgSQL is a procedural language used by PostgreSQL

  • TSQL is a proprietary procedural language used by Microsoft in SQL Server.

Be careful when searching for “how to do x in SQL”. Differences in dialects may cause confusion.

Resources

Basic language features

Code Comments

-- This is a single line comment

/* This is a multi-line comment
*/

Limiting to a specified number of rows

select limit 100 * from tablename

Recoding values with CASE statements

select
    case
        when race ='White' then 'white'
        when race ='Black/African American' then 'black'
        when race ='Unknown' then 'unknown'
        -- ...
        else 'undefined' end
    as race_recode
from tablename

Subquery factoring (the WITH clause)

This is a very useful alternative to traditional subqueries. For example, if you want to count all the rows returned by a query:

with
    some_query
    as
    (
        select *
        from tablename
        where col1 = true
    )
select count(1)
from some_query

You can have multiple WITH clauses chained together like:

with query1 as (...), query2 as (...) select ...

More here.

Temporary tables

There are multiple types of temporary tables:

  1. Per-session temporary tables named like #temp_table_name
  2. Global temporary tables named like ##global_temp_table_name

More information here.

Random sampling from a table

Use TABLESAMPLE. Note that this is fast but doesn’t guarantee a consistent number of rows. Try REPEATABLE($seed) with different integer values for $seed to find a value that give you roughly the number of rows you want.

SQL Server Management Studio (SSMS)

SSMS is an IDE for writing SQL code against a Microsoft SQL Server database. It’s a free download from Microsoft.

Here are some tips on customizing SSMS to make it easier to use.

Exporting data