Database

DBMS and RDMBS

SQL Server

SQL Server Architecture

SSMS

Authentications Modes in SQL Server

System Databases in SQL Server

SINGLE_USER,READ_ONLY and MULTI_USER Modes in Sql Server Databases

Creating , Altering and Dropping Databases

MDF and LDF Files

Pages in Sql Server (Data Pages and Index Pages)

SQL Commands

Creating, Altering and Dropping Tables

Constarints in SQL Server

Primary Key and Foriegn Key Constraints

Cascading Referential Integrity

Not Null Constraint 

Default Constraint and It's Behaviour

Check Constraint

Unique Key Constraint

SQL Server DataTypes

SQL Server Variable

Identity Column

Retrieving Identity Column

SELECT

Group By

Where vs Having

Order of Statement Execution

Different Ways to Replace Null Values: ISNULL(), COALESCE(), CASE

Joins

Union , Union All, Except and Intersect

Store Procedures and It's Advantages

Output Parameter vs Return Values

String Functions

DateTime Functions

Cast vs Convert

Mathematical Functions

User Defined Functions: Scalar, Inline and MultiStatement UDF

Difference Between Stored Procedure and Functions

Temporary Tables: Local vs Global

Indexes

Views

DML Triggers

Derived Tables and CTE

Database Normalization

Pivot Operator

Error Handling

Transactions and ACID Properties, Nested Transactions

SubQueries and Types of SubQueries

Cursors

Merge Operator

Concurrency and Isolation Level

DeadLocks in SQL Server

Cross Apply and Outer Apply

DDL Triggers

Table Valued Parameters

GROUPING SETS, ROLLUP, CUBE

GROUPING() and GROUPING_ID()

OVER and PARTITION BY 

ROW_NUMBER(), RANK(), DENSE_RANK()

Window Functions

Rows vs Range

UnPivot

OFFSET FETCH

Identify Object Dependiencies

SEQUENCE

GUID

Dynamic SQL

QUOTENAME() and PARSENAME()

Output Parameter in Dynamic SQL

Temp tables in dynamic sql

 Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. The query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

1. If there is a significant change in the key values in the index
2. If a large amount of data in an indexed column has been added, changed, or removed (that is if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3. The database is upgraded from a previous version

Look up SQL Server books online for the following commands:

UPDATE STATISTICS,
STATS_DATE,
DBCC SHOW_STATISTICS,
CREATE STATISTICS,
DROP STATISTICS,
sp_autostats,
sp_createstats,
sp_updatestats

 An execution plan is a graphical or textual way of showing how the SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.

Query Analyzer has an option, called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on, it will display a query execution plan in a separate window when the query is run again.

Q1: What is a SQL Server Execution Plan?

SQL Server Execution Plan is a binary representation of the steps that are followed by the SQL Server Engine to execute the query. It also is known as the most efficient roadmap for the query

For more information, check SQL Server Execution Plans overview

Q2: Which component of the SQL Server Engine is responsible for generating an Execution Plan for the submitted query?

A: The SQL Server Query Optimizer is responsible for creating the most efficient plan to execute the provided query

For more information, check SQL Server Execution Plans overview

Q3: Where will the generated Execution Plan be stored?

A: The SQL Server Execution Plan will be stored in the Plan Cache memory storage

For more information, check SQL Server Execution Plans overview

Q4: What is the goal behind storing the Execution Plan for the query in the Plan Cache?

A: The process of generating the most optimal execution plan is an expensive process. Instead of creating a new Execution Plan each time a new query is submitted, the SQL Server Query Optimizer will search in the plan cache storage for an existing Execution Plan for the submitted query and use it. If there is no plan that can be used for that query, the Query Optimizer will create a new plan, taking more time to execute that query. The Execution plans reuse mechanism is very helpful when there are stored procedures executed frequently

For more information, check SQL Server Execution Plans overview

Q5: What are the main types of Execution Plans that you can generate for a T-SQL query and what is the difference between these two types?

A: The Estimated Execution Plan. It is the plan that is generated by parsing the submitted query as an estimate of how the query will be executed, without being executed

The Actual Execution Plan, that is generated by executing the submitted query, displaying the actual steps that followed while executing the query

For more information, check SQL Server Execution Plans types

Q6: What are the three Execution Plan formats?

A: Graphical Format, Text Format and XML Format

Could you please some items which you may see in an execution plan indicating the query is not optimized. 

1. Index Scan or Table Scan
2. Hash Joins
3. Thick arrows (indicating large work tables)
4. Parallel streams (Parallelism)
5. Bookmark lookup (or key lookup)

 Ref:

https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/what-are-columnstore-indexes/

IN vs. EXISTS Comparison Chart

The following comparison chart explains their main differences in a quick manner:

Syntax to use IN clause:
SELECT col_names 
FROM tab_name 
WHERE col_name IN (subquery);

Syntax to use EXISTS clause:
SELECT col_names
FROM tab_name
WHERE [NOT] EXISTS (subquery);

Ref:

https://www.javatpoint.com/in-vs-exists#:~:text=The%20main%20difference%20between%20them,Boolean%20value%20TRUE%20or%20FALSE.

In SQL Server 2000, there used to be hard limit on the data that can be stored in a single row, which is 8,060 bytes. So, if the data exceeds this limit, the update or insert operation would fail!

Fortunately, in later SQL Server versions, rows are dynamically managed to exceed this limit and the combined width of the row can now exceed the 8,060 byte limit. I wanted to refresh this in our memory as this will help us to better understand the allocation units concept.

Introduction

SQL Server organizes all the data in pages. That includes the actual table data, index data, large object data and row overflow data. The pages that make up a table are however not all assigned to the table itself. Instead, they are grouped in logical units called allocation units.

The Allocation Unit

A table can have many indexes. Each index or heap, including the base table can in turn have many partitions. In fact, in SQL Server every table or index is partitioned. However, if you do not specify an explicit partition scheme, all the data of the index or heap goes into a single partition.

Each partition stores the table or index rows that belong to that partition. All the pages required to store that information are grouped into an allocation unit. If the partition also contains row overflow data, another allocation unit is created to contain all pages with row overflow data. If large binary objects are present, all the pages for that type of data make up yet another allocation unit. That means, in SQL Server versions up to 2012, a single partition of an index or heap can contain up to three separate allocation units.

--Create a sample db AllocationUnitsDemo
USE master
GO
CREATE DATABASE AllocationUnitsDemo
GO

--Cretae a sample table ProductDetails in the AllocationUnitsDemo db
--Total length of the row in this table is 1000 + 4000 = 5000 (< 8000)
Use AllocationUnitsDemo
GO
CREATE TABLE ProductDetails
(
ProductName varchar(1000),
ProductDesc varchar (4000), 
)
GO

--Check the allocation unit type
Use AllocationUnitsDemo
GO
SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('ProductDetails'))

Results:

--Add an extra column to the above table ProductDetails
--Make the total length of the row to become 5000 + 4000 = 9000 (>8000)
Use AllocationUnitsDemo
GO
ALTER TABLE ProductDetails ADD ProductSummary nvarchar(4000) 

--Now, Check the allocation unit type
Use AllocationUnitsDemo
GO
SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('ProductDetails'))

Results:

--Add LOB data type column to the table ProductDetails
Use AllocationUnitsDemo
GO
ALTER TABLE ProductDetails ADD ProductImage Image

--Again, Check the allocation unit type
Use AllocationUnitsDemo
GO
SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('ProductDetails'))

Results:

--Cleanup
Use master
GO
DROP DATABASE AllocationUnitsDemo