# SQL Server Interview Questions
###### tags: `Tech Interview`

## Normalization
**What is it?**: Database desgin technique to remove redundant data.

**Example:**
| ID | Name | Country |
| -------- | -------- | -------- |
| 1 | Paula | Egypt |
| 2 | Zack | USA |
| 3 | Mark | EG |
| 4 | Lizzy | UStates |

**How to implement?**: Implemented by splitting tables *(foreign keys..)*

## 1st, 2nd, and 3rd Normal form
Always remember (**APT**, appropriate)
**A:** **A**tomic
Each columns should hold atomic values
- Instead of: "Paula, Fahmy, Ramzy" for Name
- It should be: FirstName: Paula, MidName: Fahmy, LastName: Ramzy

**P:** ***(no)*** **P**artial Dependencies
There should be no columns that depend on a primary key columns, like the follwing:


- CountryId, CountryName should both be extracted to their separate table.

**T:** ***(no)*** **T**ransient Dependencies
There should be no columns that depend on a **non** primary key columns, like the follwing:


- PerCost depends on Product and should be extracted to a separate table

## Denormalization
**What is it?**: Database desgin technique to improve search performance *(no joins or querying multiple tables)*

## OLTP vs OLAP
**OLTP**: Online transaction processing
**OLAP**: Online analysis processing
**Relation to Normailization:**
- For high throughput systems, OLTP is used for CUD operations, while OLAP is used for R operations
- OLTP: Normalized Tables
- OLAP: Denormalized Tables for best read performance
- OLTP ◀ ETL ▶ OLAP
- ETL: Extraction, Transformation, and Loading, ie, moving data from one database (OLTP) to the other (OLAP)


## Primary vs Unique keys
**Both:** inforce uniqueness but:
**"2 Ns RULE"**:
- Unique keyed column can have **N**ull values
- There can be any **N**umber of unique keys on columns, but **only one Primary Key**

## Char(x) vs VarChar(x)
**Char(x):** Datalength would be == x even if data is less than x in length
.. Should only be used when it is expected that all cells in columns would have the same size

**VarChar(x):** Datalength would be == length of characters with a max limit of x

| Data | Type |Length | ConsumedSpace(DataLength) |
| -------- | -------- | -------- | -------- |
| USA |Char(10)| 3 | 10 |
| USA |VarChar(10)| 3 | 3 |

## Char(x) vs NChar(x)
**Char(x):** No support for unicode, size is 1 byte per character

**NChar(x):** Support for unicode, size is 2 byte per character

.. Unicode represents all languages
.. ASCII only english

## Indexes
**What is its usage?**: Used to increase search performance.
**How does it work?**: Utilizes a B-Tree (B stands for balanced) structure to point at table records (just like an index of a book)


User searches for 52, 52 > 50, no need to scan from 0 to 50.

**Types of Indexes:**
1. Clustered Indexes
- Only one per table
- **Leaf Node** points to the actual record
- Logical order matches physical order on disk
- Stored in the same place, **does not consume extra storage**
- Faster than Non-Clustered because no extra search step is needed
2. Non-Clustered Indexes
- There can be multiples
- **Leaf Node** points to a node in a clustered indexes (uses help of Clustered index)
- That's why there can be only one clustered and multiple non clustered
- Logical order deos not match physical order on disk
- Stored in the separate place, **does consume extra storage**
- Slower than Clustered because there is an extra search step that's needed (consultation of clustered index or looking up the address of the record)
Both of them uses B-Tree

## Function vs Stored Procedure

| | Function | Stored Procedure |
| -------- | -------- | -------- |
| Execution | Can be called inside a `SELECT`, `WHERE`, or a **Stored Procedure** | It functions as a mini program or a batch script and hence cannot be called from inside another query. |
| Goal | It cannot change the environment, no `INSERT`, `DELETE` or `UPDATE` operations can be done | It can change the environment and modify existing data. |
| Output | **Mostly scalar values**, but can result in table outputs too | Single or multiple outputs |

## Triggers
Peaces of logic that occurs after an event
`AFTER`: Logic after the event
`INSTEAD OF`: Event is discarded and the logic occurs.

## Identity
- Auto-incremented column.
- Cannot manually insert data into that column
- Usually used with primary keys

## Transactions
- Treats a series of activities as a single unit (Atomicity).
- Either all succeeds or all fails.

## Joins
#### Inner Join
Selects **matching** records from both tables

#### Left Join
Selects all records from left table but only **matching** records from right table

#### Right Join
Selects all records from right table but only **matching** records from left table

#### Full Join
Selects all records from both tables, **matching and unmatching**

#### Cross Join
- Cartesian product
- Each record from left is matched to right and vice versa.
- No **`ON`** in the query

## Group By
https://learnsql.com/blog/error-with-group-by/