TempDB Recovery Model

TempDB always in Simple Recovery Model. TempDB is a temporary workspace and SQL Server does NOT need to retain its transaction log for point-in-time recovery. TempDB stores only temporary and non-persistent data. SQL Server recreates TempDB every restart, so nothing needs recovery. It is extremely write-intensive, so SIMPLE model avoids log growth and improves performance. TempDB cannot be backed up, so Full/Bulk Logged recovery is meaningless. Therefore, SQL Server enforces SIMPLE mode permanently we cannot change it.

Let’s check the recovery model

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'tempdb';

We can’t change it. Let’s try to change it from SIMPLE to Full.

ALTER DATABASE tempdb SET RECOVERY FULL;

Let’s create a temp table and store data into that table

Before loading the data see the size of temp db.

Now inserting the records

use AdventureWorks2019 ;with cte as ( SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b union all SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b union all SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b union all SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b union all SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b ) select * into #BigTable from cte

See the records in this table

Now we will see the size of temp DB.

Temp DB size is grown.

After the Server restart the table and DB size clean.

Let’s restart the Server and see.

Showing invalid object

See the size of the Temp DB.

Log shrinks automatically because SIMPLE = auto truncation No log backup required.

We can not take backup of TempDB.

what happen internally when we are creating a table in SQL server

When a CREATE TABLE statement is executed in SQL Server, it performs several internal operations involving parsing, metadata updates, permission checks, and physical storage preparation.

Below are the internal steps of table creation

Ø  Parsing and Validation
Ø  Permission verification
Ø  Metadata updates
Ø  Lock Management
Ø  Storage allocation
Ø  Constraint and index creation
Ø  Transaction logging 

Ø  Completion

Architecture Diagram

 Parsing and Validation

The SQL Server Database Engine first parses the CREATE TABLE statement to ensure correct syntax and structure. It checks if the specified table name is unique within the database/schema and if the column names, data types, and constraints are valid. If any error during the parsing and validation then it will throw and error and table is not created.

Permission verification

After parsing and validation phase pass then go to the next step and check with permission where that user which has necessary CREATE TABLE permission in the database and ALTER permission on the target schema.

Metadata updates

Upon validation, the database's system catalog (also known as the data dictionary) is updated. New entries are added to system tables and catalog views (such as sys.objects, sys.columns, sys.indexes, and sys.partitions) to record the new table's name, structure, columns, data types, and constraints.

System table	Description
sys.objects	Table object definition
sys.tables	Table type entry
sys.columns	Column definitions
sys.types	Data types reference
sys.schemas	Schema mapping
sys.filegroups	Filegroup reference
sys.key_constraints sys.check_constraints	Constraints metadata

 Object ID Gets Generated then SQL Server assigns object_id , table_id and schema_id.

Lock Management

During the operation, the database management system acquires necessary locks to prevent other concurrent operations from conflicting with the table creation process.

Storage Allocation

The table is assigned to a specific filegroup (e.g., the default filegroup, or a user-specified one) which determines its physical location on disk.

Constraint and index creation

If PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, or DEFAULT constraints are specified, SQL Server internally sets up the corresponding objects. Primary and unique keys automatically result in the creation of an index (clustered by default for a primary key, non-clustered otherwise).

Transaction Logging

The entire DDL operation is part of a transaction. All changes to the system catalog and initial storage allocations are logged in the transaction log to ensure the operation is atomic and can be rolled back in case of an error.

Completion:

Once all steps are successfully completed and committed, the locks are released, and the new table is ready to store data using INSERT statements. 

Prevent Hard Delete instead of Delete do Soft delete with Trigger in SQL Server

Do we know that a single DELETE query can destroy millions of production records in seconds? In banking, healthcare, or finance — that’s unacceptable! In the data ware house, we have soft deleted the records it means we are updating flags to identify the deleted records or inactive records. There also we do not have control to prevent to run the delete command. If some by mistake run the delete command then we can loss the data due to the that we are doing the soft delete even user run the delete command.

We can archive this by using trigger. 

Let’s see the demo

Here we are creating the audit table and main table.

--Main table CREATE TABLE Emp_SD_tbl (     EmpId INT PRIMARY KEY,     EmpName VARCHAR(50),     Salary INT,     IsActive BIT DEFAULT 1     DeletedBy varchar(50) null,     DeletedOn Datetime null );

Table created successfully. Now creating trigger on the table.

CREATE TRIGGER tr_preventdelete_emp_sd_tbl ON emp_sd_tbl instead OF DELETE AS   BEGIN       UPDATE e       SET    isactive = 0,              deletedby = SYSTEM_USER,              deletedon = CURRENT_TIMESTAMP       FROM   emp_sd_tbl e              INNER JOIN deleted d                      ON e.empid = d.empid;   END;

 Inserting few records into the main table

--inserting few records   insert into emp_sd_tbl (EmpId,EmpName,Salary) values   (1,'Bagesh',10000),(2,'Amit',20000)

See the records in the table

Now we are trying to delete the records from this table.

Delete from Emptbl where EmpId=1

Getting the error

See the records in the table

No record deleted. Instead of deleting the record it has update the record. It means did soft delete.

Prevent Hard Delete with Trigger in SQL Server

Do we know that a single DELETE query can destroy millions of production records in seconds? In banking, healthcare, or finance — that’s unacceptable! In the data ware house, we have soft deleted the records it means we are updating flags to identify the deleted records or inactive records. There also we do not have control to prevent to run the delete command. If some by mistake run the delete command then we can loss the data.

Suppose if we have a requirement that no one can delete the records from the table. If someone want to delete the record from this table we will track and log this information into log table and prevent the delete.

We can archive this by using trigger. 

Let’s see the demo

Here we are creating the audit table and main table.

--Main table CREATE TABLE Emptbl (     EmpId INT PRIMARY KEY,     EmpName VARCHAR(50),     Salary INT,     IsActive BIT DEFAULT 1 );   --Audit table CREATE TABLE EmpDeleteAudit(     Id INT IDENTITY(1,1) PRIMARY KEY,     AuditMessage VARCHAR(500),     createdBy SYSNAME  DEFAULT         SYSTEM_USER,     createdOn DATETIME DEFAULT        CURRENT_TIMESTAMP );

Both tables created successfully. Now creating trigger on the main table.

CREATE TRIGGER tr_preventdelete_emptbl ON emptbl FOR DELETE AS   BEGIN       INSERT INTO empdeleteaudit                   (auditmessage)       SELECT 'Delete operation blocked. User : '              + Suser_sname()              + ' Trying to delete record which EmpID '              + Cast(empid AS VARCHAR(20)) + ' on '              + Cast(Getdate() AS VARCHAR(30))       FROM   deleted;         RAISERROR('Delete operation blocked. Instead of hard delete use soft delete                  update IsActive flag to 0. Data is logged in Audit Table.',16,1       );     ROLLBACK TRANSACTION;   END;

 

Inserting few records into the main table

--inserting few records   insert into Emptbl (EmpId,EmpName,Salary) values   (1,'Bagesh',10000),(2,'Amit',20000)

See the records in the table

Now we are trying to delete the records from this table.

Delete from Emptbl where EmpId=1

Getting the error

See the Audit log table

See the records in the main table

No record deleted.

Multiple way to create tables in SQL Server

Tables are the backbone of every database. In the SQL server there are multiple way to create table. Based on our need we are creating table. Let’s see how and when we are creating the table.

Ø  CREATE TABLE

Ø  SELECT INTO

Ø  Temp Table (#, ##)

Ø  Table Variable

Ø  External Table

Ø  System Versioned Temporal Table

Ø  Table with Clustered Index/Filegroup

Let’s see the demo and use of this type of table.

Create table

CREATE TABLE is used to create a new table in SQL Server where our data is stored in rows and columns. This is a Standard Way to creating a table.

Syntex to create table

CREATE TABLE schema_name.table_name (     column_name datatype [NULL | NOT NULL] [constraint],     column_name datatype [NULL | NOT NULL] [constraint],     ... );

Ø  schema_name = usually dbo

Ø  datatype = INT, VARCHAR, DATETIME, etc.

Ø  NULL / NOT NULL = allow or restrict nulls

Ø  Constraints control data rules

Creating a table and inserting the records into this table.

CREATE TABLE dbo.Employee (     EmpId INT NOT NULL,     EmpName VARCHAR(100) NOT NULL,     Dept VARCHAR(50),     Salary DECIMAL(10,2),     CreatedOn DATETIME DEFAULT GETDATE(),               CONSTRAINT PK_Employee_ID PRIMARY KEY (EmpId asc) on [PRIMARY],               CONSTRAINT CHK_Employee_Salary CHECK (Salary > 0) ); --inserting few records. INSERT INTO dbo.Employee(EmpId, EmpName, Dept, Salary) VALUES (101,'Bagesh','IT',85000),        (102,'Rajesh','HR',65000);

See the records into this table

SELECT INTO

This is a fastest way to create table from query result. It will copy structure and data but it does NOT copy constraints, indexes, triggers. It is useful when we are ETL or creating temp table. Generally, we are using this table when we are debugging the code or need to quick data storage. It is faster. With the help of this we can create permanent table as well as temp table. We are not recommending to create permanent table using this method.

Read more

https://bageshkumarbagi-msbi.blogspot.com/2024/08/select-into-vs-insert-select-statement.html

see the demo

select EmpId, EmpName, Dept, Salary, CreatedOn into dbo.EmployeeBK from dbo.Employee

Table created. See the data.

Limitations of SELECT INTO

Ø  Primary Keys

Ø  Foreign Keys

Ø  Indexes

Ø  Constraints

Ø  Triggers

Ø  Identity property (unless same column selected)

Meaning table is created without relational rules, which can break data integrity if used wrongly.

See the below example

INSERT INTO dbo.EmployeeBK(EmpId, EmpName, Dept, Salary) VALUES (101,'Manoj','IT',0),        (102,'Raheem','HR',-50); select * from EmployeeBK;

Which break the pk, check and default constrain.

Create Table with Clustered Index on Specific Filegroup

This is useful for performance tuning & storage optimization

See the example below

CREATE TABLE dbo.OrderHeader (     OrderId INT NOT NULL,     OrderDate DATE );  CREATE CLUSTERED INDEX CX_OrderHeader ON dbo.OrderHeader(OrderId) ON FG_SalesData;

Temporary Tables

There are two types of temp table

Ø  Local Temp Table (#Table) : scope of this table is in current session.

Ø  Global Temp Table (##Table): scope of this table is current server session.

Read more about this below

https://bageshkumarbagi-msbi.blogspot.com/2017/08/types-for-temporary-tables.html

 Table Variable

Variable table is a one kind of temp table scope of this table is current session only.

https://bageshkumarbagi-msbi.blogspot.com/2016/05/difference-between-temp-table-and.html

DECLARE @Employee TABLE (   EmpId INT NOT NULL,   EmpName VARCHAR(100) NOT NULL,   Dept VARCHAR(50),   Salary DECIMAL(10,2) );  INSERT INTO @Employee (EmpId,EmpName,Dept,Salary) SELECT EmpId,EmpName,Dept,Salary FROM Employee; select * from @Employee

See the data

This table is created in memory but temp table is created on the temp db.

System Versioned Temporal Table

A temporal table automatically keeps full history of data changes with time validity. SQL Server maintains a current table or a history table and automatically tracks Insert / Update / Delete with timestamps. This feature is introduced in SQL Server 2016.

CREATE TABLE Customer (    CustId INT PRIMARY KEY,    CustName VARCHAR(50),    CustAdd VARCHAR(100),    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON);

Table is created successfully. SQL Server automatically creates a history table.

Inserting some records into this table

insert into Customer(CustId,CustName,CustAdd) values (1,'Bagesh','Noida') insert into Customer(CustId,CustName,CustAdd) values (2,'Amit','Noida')

See the records in the table

Let’s update the record

Latest record is stored in main table and old versions stored on history table. When we are inserting new record No entry in history table yet. When we are updating or deleting the table records into main table and historical table.

Let’s update the record

To see the historical table

Ø  Use FOR SYSTEM_TIME

Ø  past point-in-time 

SELECT * FROM Customer FOR SYSTEM_TIME ALL;

If we want to see the point of time

SELECT * FROM Customer FOR SYSTEM_TIME AS OF '2025-12-24 07:10:40.6467336';

 

 External Table (PolyBase / Azure / Hadoop)

Such type of table is used in Big Data / Azure Synapse

CREATE EXTERNAL TABLE dbo.ExtCustomer (    Id INT,    Name VARCHAR(100) ) WITH (    LOCATION='/customer/',    DATA_SOURCE = MyExternalSource,    FILE_FORMAT = MyFileFormat );