SQL Server / MySQL / PostgreSQL cheat sheet

Last update July 2017, please let me know if anything becomes out of date. Current versions:

  • SQL Server 2016 / 2017 CTP
  • MySQL 5.7
  • PostgreSQL 9.6
TypeDescriptionSQL ServerMySQLPostgreSQL
GUINative Graphical User Interface toolSQL Server Management Studio (SSMS)MySQL WorkbenchpgAdmin
GUIConfigure F5 to execute the query in GUI. Just to make the live easier after using SSMS for too many years.SSMS - F5 runs the query by defaultGreat post how to configure MySQL Workbench
http://lancelarsen.com/remapping-mysql-workbench-execute-sql-to-f5/
pgAdmin - this tool is unusable (at least version 4 1.4)
General queriesCurrent Session IDSELECT @@spid;SELECT connection_id();SELECT pg_backend_pid();
General queriesSleepWAITFOR DELAY 'hh:mm:ss.000'DO SLEEP(s); -- s - time in secondsSELECT pg_sleep(s); -- s - time in seconds
General queriesVersion of the databaseSELECT @@version;SELECT @@version;
or
SELECT version();
SELECT version();
Development/LoopsWHILE LoopWHILEWHILE -- works only inside a procWHILE
Development/LoopsQuit WHILE LoopBREAKLEAVE (only inside stored procedures)
Development/DBAservername @@servernameinet_server_addr()
Development/DBAAnalyze execution planhttp://www.dalibo.org/_media/understanding_explain.pdf
DevelopmentString to a columnPoor implementation with SQL2017
or
with a tally table
with a tally table
DevelopmentConcatenate with separatorStarting with SQL2017:
CONCAT_WS()
CONCAT_WS()CONCAT_WS()
DevelopmentError codehttps://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html
DevelopmentCTESupport for CTEs and recursive CTEsWhere are my CTEs?
Definitely not in MySQL ...
Support for CTEs and updatable CTEs
Developmentsetting value for the variable GET DIAGNOSTICS integer_var = ROW_COUNT
DevelopmentOUTPUT ClauseAvailable after insert/update/delete/mergeDoes not exists
Development/LoopsLoopsNo BEGIN / END, everything after IF and before END IF is the same block
DevelopmentAssign variableSET @var = (SELECT abc FROM def)SELECT abc INTO v_var FROM def
Developmentvariable@ variables are at session level! they can be passed between the sotored procedures!!!
v_variable are expired when the batch finishes
only in functions
$$
DECLARE
v_variable1 integer;
v_variable2 integer;
v_variable3 integer;
DevelopmentSELECT INTOSELECT *
INTO NewTable
FROM OldTable
CREATE TABLE AS
SELECT * FROM ;
DevelopmentOUTTER/INNER APPLYOUTTER/INNER APPLYLetheral JOINS
DevelopmentISNULL()ISNULL / COALESCECOALESCE
Developmentdual table conceptnot requiredExistsExists
DevelopmentDelete multiple table with a joinDELETE a,b,c FROM a JOIN b JOIN c
DevelopmentTally table/*
DROP TEMPORARY TABLE IF EXISTS tally;

CREATE TEMPORARY TABLE tally(
N int signed NOT NULL
, PRIMARY KEY (N)
);

SET @row_number := 0;

INSERT tally (N)
SELECT
(@row_number:=@row_number + 1) AS num
FROM information_schema.columns a
INNER JOIN information_schema.columns b ON 1=1
LIMIT 10000;


SELECT N
FROM tally
WHERE N <= 10
ORDER BY N
*/

SET @string = 'value A¬value B¬value C¬value D¬value E¬value F¬value G';
SET @string = CONCAT('¬', @string, '¬');

SELECT
N
, SUBSTRING(@string,N,1)
, N+1
, LOCATE('¬',@string,N+1)
, CAST((N + 1) as signed)
, LOCATE('¬',@string,N+1) - (N + 1)
, SUBSTRING(@string, N+1, LOCATE('¬',@string,N+1)-N-1)
FROM tally
WHERE N <= LENGTH(@string)
AND SUBSTRING(@string,N,1) = '¬'
ORDER BY N;

-- SHOW warnings;
DevelopmentTable valued functionsWide rangedont exist ...some ...
Development
Check the current transaction isolation levelSELECT @@GLOBAL.tx_isolation, @@tx_isolation;
Developmentextra featues in GROUP_CONCAT!!!DISTINCT, SEPARATOR
DevelopmentGROUP CONCATSubstitute - STUFF, or appending to a variableGROUP_CONCAT() !!!string_agg()
DevelopmentCASE blockStatements in CASE block!
DevelopmentMerge statementAvailable - advancedNot Available (v9.7, v8)Exists
DevelopmentGet row affected@@ROWCOUNTSELECT FOUND_ROWS( ) ; -- select
SELECT ROW_COUNT(); -- insert / update
DevelopmentChange transaction Isolation levelSET TRANSACTION ISOALTION LEVEL SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
DevelopmentWindow functionsnot supported, in beta in MariaDBYes, they work
Developmentjson supportYes (since version 2016?)Yes (since version 5.7)Yes (since version ??)
Developmentjsoon to column??/with a tally tablebuild in function
DevelopmentGet row count
DBAMax number of partitions15000 starting from SQL 2008 R2 SP2 (1000 before)8192 for version greater thant 5.6.7 (1024 before that version) the number includes the subpartitions
DBAPage size8k4-64? InnoDB -16k8k
DBAClustered indexesYesNoNo (can be created but the order of the records is not maintained as new records inserted)
DBAList partitions per tableSELECT *
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'table_name'
DBACurrent Session ID@SPIDconnection_id()pg_backend_pid()
DBAIndex usage
DBAGet list of tables and their size/*https://wiki.postgresql.org/wiki/Disk_Usage*/
SELECT
*
, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a
ORDER BY total_bytes DESC;
DBAProcess list + infosp_whoisactiveSELECT *
FROM information_schema.processlist
-- WHERE command <> 'Sleep'
ORDER BY time DESC

-- note: there are better views to source this information
SELECT
query
, NOW() - query_start AS time_running
, query_start
, *
FROM pg_stat_activity WHERE state <> 'idle'
AND pid <>pg_backend_pid();
DBApartition sizeSELECT
PARTITION_NAME
, round( data_length / ( 1024 *1024 ) , 2 ) data_MB
, round( index_length / ( 1024 *1024 ) , 2 ) index_MB
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'table_name';
DBAtable / index sizeSELECT
table_name
, round( data_length / ( 1024 *1024 ) , 2 ) data_MB
, round( index_length / ( 1024 *1024 ) , 2 ) index_MB
, round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) total_size_MB
FROM information_schema.TABLES
WHERE TABLE_NAME = 'table_name'
ORDER BY data_length DESC;
DBAshow all configuration settingsSHOW ALL;
DBAFill factorDatabase or table level setting??????
DBAunused indexesSELECT *
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_NAME = ''
AND index_name IS NOT NULL;
DBAInvisible indexesdisable index?from version 8 invisible indexes!
DBAPoint in time recovery???? impossible?
DBAKill process/session
KILL KILL SELECT pg_terminate_backend()

KILL ALL PROCESSES FOR A DB:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
AND pid <> pg_backend_pid();
DBAIndex fragmentationSELECT engine,
table_name,
Round(data_length / 1024 / 1024) AS data_length,
Round(index_length / 1024 / 1024) AS index_length,
Round(data_free / 1024 / 1024) AS data_free,
( data_free / ( index_length + data_length ) ) AS frag_ratio
FROM information_schema.tables
WHERE table_name = ''
DBAGet database sizeSELECT pg_size_pretty(pg_database_size('')) As fulldbsize;
DBAPLEcant get?
DBAtable sizepg_total_relation_size()
DBAdiskpsacePANIC: could not write to file "pg_xlog/xlogtemp.9360": No space left on device

http://blog.endpoint.com/2014/09/pgxlog-disk-space-problem-on-postgres.html
https://bucardo.org/wiki/Tail_n_mail
DBAAdmin, restore database???? impossible?
Execute stored procedures / functionsEXEC proc_name
EXECUTE proc_name
CALL proc_name ()???
SELECT my_function();
PERFORM my_function();
DevelopmentClear proc cacheDBCC FREEPROCCACHE
DevelopmentClear buffer poolDBCC DROPCLEANBUFFERS
DevelopmentPivot a tablePIVOTNot AvailableCREATE EXTENSION tablefunc;

SELECT FROM crosstab('') AS ct ();

A bit clunky, can't run aggregated functions on values, can't ...
DevelopmentISNUMERIC functionISNUMERIC()N/A
REGEXP workaround
N/A
REGEXP workaround
UDF workaround
DevelopmentIndex views / Materialized viewsIndexed views - views is written into the disk as soon as a clustered index on the view is created.
Some limitations: INNER JOINS only, SCHEMABOUND view, Clustered index has to be unique.
Advantage, view is always up to data, no need to refresh it. Nonclustered indexes can be also created on top of the view.
Not supported
Table with a set of triggers can be used as a workaround
Materialized views - however this is nothing more than just table with a query linked to it so it can be refreshed on demand.
Limitation: Data not refreshed automatically. In order to keep the view up to data we have to add set of triggers or a cron job.
Development/DBAFiltered indexes /
Partial Indexes
Filtered indexesNot AvailablePartial indexes
Development/DBANonkey columns in index (INCLUDE feature)Nonkey columns can be added to any NONCLUSTERED index in INCLUDE clauseNot supportedNot supported
Development/DBAAccessing objects on remote serverLinked servers are supported. Tables, procs, fictions from remote server can be called using 4 part naming convention: ...Not supportedFDF (Foreign Data Wrappers) and DBLink extensions available.
Limitations: Sequences not supported on inserts into Postgres FDW tables
Development/DBAList table on linked/remote server:?Not supported-- table mapped with FDW:
SELECT *
FROM information_schema.tables
WHERE table_type = 'FOREIGN TABLE'
DevelopmentGenerate hash of the stringHASHBYTES()
number of algorithms supported; MD2, MD4, MD5, SHA, SHA1, SHA2_256, SHA2_512
SHA()
SHA1()
SHA2()
MD5()
MD5()
more algorithms supported with pgcrypto module
DBARename database-- ensure no active connections to the db
Option 1: sp_rename 'old_db', 'new_db' ,'DATABASE';
Option 2: Deattach the attach under a different name. (add sample script)
no native command
workaround:
1. create new database
2. Migrate all the tables with RENAME TABLE old_db.table_name TO new_db.table_name;
3. DROP old database
-- ensure no active connections to the db
ALTER DATABASE RENAME TO
Development/DBAReseed identity
/ Restart sequence
DBCC CHECKIDENT ('', RESEED, );to be added laterALTER SEQUENCE RESTART WITH ;

Note: sequence name is usually: __seq
DBAGet table row lengthcustom scriptsSELECT AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '' -- add TABLE_SCHEMA optionally
-- to get a full row length
SELECT pg_column_size() FROM LIMIT 1;

-- to get average on the table level (do not run on prod or tables with millions records)
SELECT AVG(pg_column_size()) FROM ;

-- to get a single column length
SELECT pg_column_size() FROM LIMIT 1;
Development/DBARename Primary Keycan't be doneALTER TABLE . RENAME CONSTRAINT TO ;
DevelopmentTimerDROP TABLE IF EXISTS tmp_timer;
CREATE TABLE tmp_timer AS SELECT clock_timestamp()::timestamp start_time;

-- here are some queries
/
SELECT
to_char(start_time::timestamp, 'HH24:MI:SS.MS') AS start_time
, to_char(clock_timestamp()::timestamp, 'HH24:MI:SS.MS') AS end_time
, to_char((clock_timestamp() - start_time), 'HH24:MI:SS.MS') AS total_time
FROM tmp_timer;

DevelopmentUPDATE with JOINUPDATE t1 SET
column1 = r.column1
FROM table1 AS t1
INNER JOIN ref_table AS r ON r.id = t.id
UPDATE table_1 AS t1
INNER JOIN ref_table AS r ON r.id = t1.id
SET column1 = r.column1
UPDATE .table1 AS t1 SET
column1 = r.column1
FROM .ref_table AS r
WHERE r.id = t.id
Development/DBACreate table with a primary key, Unique key, Foreign Key, Dafault and Index in a single statement.??????CREATE TABLE . (

, CONSTRAINT PRIMARY KEY ()
, CONSTRAINT DEFAULT ()
, CONSTRAINT UNIQUE ()
, CONSTRAINT FOREIGN KEY () REFERENCES ()
);

-- indexes have to be created outside of the statement
CREATE INDEX ON .();
Development/DBAGet last sequence value -- last value generated in the session
SELECT currval('.');

-- last globally generated value
SELECT last_value FROM .;
Development/DBAReset current sequence value
Disk size by schema-- modified query taken from Stack Overflow

SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint),
ROUND((sum(table_size) / pg_database_size(current_database())) * 100, 2) AS "db %"
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY 3 DESC

SQL Server vs MySQL vs PostgreSQL – database engine version

Firstly, I would like to know what is the version of the database engine installed on the server/workstation. We all know SQL Server, it’s all provided by @@version variable, but how to get this info in MySQL or PostgreSQL?
Just to remind, SQL Server:

Sample output below, it provides the details about both, the database and the operating system:

MySQL has 2 variable available, one the same as SQL Server:

and variable version():

Both will return the same output, ie:

PostgreSQL:

Sample output: