Microsoft SQL Server (MSSQL) / T-SQL
Last updated: July 18, 2020
What is T-SQL?
Notes for using the SQL dialect used by Microsoft SQL Server (MSSQL), which is called T-SQL.
From Quassnoi on StackOverflow:
SQLis 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/SQLis a proprietary procedural language used by Oracle
PL/pgSQLis a procedural language used by PostgreSQL
TSQLis 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.
Basic language features
-- 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
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
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 ...
There are multiple types of temporary tables:
- Per-session temporary tables named like
- Global temporary tables named like
More information here.
Random sampling from a table
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.