---
# System prepended metadata

title: SQL Server Interview Questions
tags: [Tech Interview]

---

# 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:

![](https://i.imgur.com/qYLdYNX.jpg)

- 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:

![](https://i.imgur.com/fPc8MM0.jpg)

- 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)

![](https://i.imgur.com/5iWj5sk.jpg)


## 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)

![](https://i.imgur.com/KTath7h.png)

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/