Relational databases

relationalDB.md

• Databases
• Schemas
• Tables
• Rows
• Columns
• Normalization
• Views
• Materialized views
• Transactions
• Triggers
• Indexing
• Queries
• Subqueries
• Table functions
• Stored procedures
• Common table expressions
• Window functions

Views

Materialized views

A view is just a projection of data from a source. Nothing is stored anywhere, a view actually queries the underlying tables through it. The only way to index a view is if it exists on disk, which is what the “materialized” view is. They’re great for speeding things up, especially if you need to throw an index in!

Queries

Indexing

What is an Index

An index is a specific structure that organizes a reference to your data that makes it easier to look up. In Postgres it is a copy of the item you wish to index combined with a reference to the actual data location. When accessing data, Postgres will either use some form of an index if it exists or a sequential scan. A sequential scan is when it searches over all of the data before returning the results.

Advantages and Disadvantages

Indexes are great for accessing your data faster. In most cases adding an index to a column will allow you to query the data faster. However, the trade off is that for each index you have you will insert data at a slower pace. Essentially when you insert your data with an index it must write data to two places as well as maintain the sort on the index as you insert data. Certain indexes additionally will be more effective than others, such as indexes on numbers or timestamps (text is expensive).

You can create an index on one or many columns at a time.

When Postgres creates your index, similar to other databases, it holds a lock on the table while its building the index. By using CREATE INDEX CONCURRENTLY your index will be built without a long lock on the table while its built.

Operators

IN - tests if an expression matches any value in a list of values. It is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Table functions vs Stored procedures

Table function:

The major usage of User Defined Function is its ability to compute small business logic and return a value in the form of a single scalar or a table result set which can be used inline in a SELECT query or WHERE or HAVING clause.

Stored procedure:

• Stored procedures are normally used for computing highly complex business logic and either return the result or update the values in tables.
• Stored procedures can have complex SQL statements which may perform permanent environmental changes in the database.

Table function	Stored procedure
Function should return a value, either a scalar value or a table.	Stored procedure may or may not return value. It can even return multiple scalar values or tables
Function should have only input parameters.	Stored procedure can have both input and out parameters.
Functions should have at least one input parameter.	In stored procedures input parameters are optional.
A maximum of 1024 input parameters can be used in a function.	A maximum of 2100 parameters can be used in a stored procedure.
A function can be called from inside a stored procedure.	A stored procedure cannot be called from inside a function.
Functions cannot perform any permanent environmental change to the database.	Stored procedures can perform any permanent environmental change to the database.
Functions cannot use DML statements like INSERT, UPDATE or DELETE against any tables, temp tables or views. These statements can be used only against local table variables.	Stored procedures and use DML statements against permanent tables, temp tables or views.
Transaction management: Begin / Commit / Rollback Transactions cannot be used in functions.	Transactions can be used in stored procedure.
Exception or error handling using try/catch cannot be used in a function.	Exception or error handling is allowed to be used in stored procedure.
Function can be called from inside SELECT statement and WHERE or HAVING classes.	Stored procedure cannot be called from inside SELECT statement and WHERE or HAVING classes.
Table-Valued Function can be used in JOIN clause just like a table.	Stored procedures cannot be used in JOINs.
On using as function in SELECT, WHERE or HAVING clause, you can pass the column as a parameter.	Column cannot be passed as parameter in stored procedure.
Functions are normally used for computing small logic and return the result.	Stored procedures are normally used for highly complex business logic and either return the result or update the values in tables.
You cannot use temporary tables in functions. Instead they can use table variables.	In stored procedures you can use temp tables as well as table variables.
Non-deterministic built-in functions like NEWID,NEWSEQUENTIALID,RAND and TEXTPTR cannot be used in user defined function.	No such condition.
Function cannot be executed using EXECUTE or EXEC command.	Stored procedures can be executed using EXECUTE or EXEC command.

Common table expressions (WITH queries)

A CTE allows you to chain queries together, passing the result of one to the next auxiliary statements for use in a larger query defining temporary tables that exist just for one query can be a SELECT, INSERT, UPDATE, or DELETE