MQ85M0m6

1* Databases
2* Schemas
3* Tables
4* Rows
5* Columns
6* Normalization
7* Views
8* Materialized views
9* Transactions
10* Triggers
11* Indexing
12* Queries
13* Subqueries
14* Table functions
15* Stored procedures
16* Common table expressions
17* Window functions
18
19### Views
20
21### Materialized views
22A 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!
23
24### Queries
25
26### Indexing
27##### What is an Index
28An 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.
29
30##### Advantages and Disadvantages
31**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).
32
33You can create an index on one or many columns at a time.
34
35When 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.
36
37#### Operators
38`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.
39
40### Table functions vs Stored procedures
41
42#### Table function:
43The 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.**
44
45
46#### Stored procedure:  
47* Stored procedures are normally used for computing **highly complex business logic** and either return the result or update the values in tables.  
48* Stored procedures can have complex SQL statements which may perform **permanent environmental changes in the database.**
49
50
51Table function | Stored procedure
52------------ | -------------
53Function 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
54Function should have only input parameters. | Stored procedure can have both input and out parameters.
55Functions should have at least one input parameter. | In stored procedures input parameters are optional.
56A maximum of 1024 input parameters can be used in a function. | A maximum of 2100 parameters can be used in a stored procedure.
57A function can be called from inside a stored procedure. | A stored procedure cannot be called from inside a function.
58Functions cannot perform any permanent environmental change to the database. | Stored procedures can perform any permanent environmental change to the database.
59Functions 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.
60Transaction management: Begin / Commit / Rollback Transactions cannot be used in functions. | Transactions can be used in stored procedure.
61Exception or error handling using try/catch cannot be used in a function. | Exception or error handling is allowed to be used in stored procedure.
62Function 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.
63Table-Valued Function can be used in JOIN clause just like a table. | Stored procedures cannot be used in JOINs.
64On 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.
65Functions 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.
66You 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.
67Non-deterministic built-in functions like NEWID,NEWSEQUENTIALID,RAND and TEXTPTR cannot be used in user defined function. | No such condition.
68Function cannot be executed using EXECUTE or EXEC command. | Stored procedures can be executed using EXECUTE or EXEC command.
69
70### Common table expressions (WITH queries)
71A CTE allows you to chain queries together, passing the result of one to the next
72auxiliary statements for use in a larger query
73defining temporary tables that exist just for one query
74can be a SELECT, INSERT, UPDATE, or DELETE