OFFSET Command in SQL Server

OFFSET Command in SQL Server

Introduction-

In SQL Server, the OFFSET command is used in conjunction with the ORDER BY clause to specify the number of rows to skip before starting to return rows from a query. This is particularly useful for implementing pagination in applications or when you need to skip a certain number of rows before fetching results.

 

Here's the basic syntax of the OFFSET command-

SELECT column1, column2, ...

FROM table_name

ORDER BY column1, column2, ...

OFFSET {number_of_rows | expression} ROWS

FETCH {FIRST | NEXT} {number_of_rows | expression} ROWS ONLY;

 

Let's break down each part:

OFFSET-

This specifies the number of rows to skip before starting to return rows. It is always used with FETCH to define the number of rows to return.

 

{number_of_rows | expression}-

This can be a numeric value or an expression that evaluates to the number of rows to skip or fetch.

 

ROWS-

This keyword specifies that the value specified after OFFSET and FETCH is in terms of rows.

 

FETCH {FIRST | NEXT}-

This part is used to specify whether to fetch the first or next set of rows after the offset.

 

{number_of_rows | expression} ROWS ONLY-

This part specifies the number of rows to fetch after the offset.

Now let's see an example to understand how

to use the OFFSET command:

 

Suppose we have a table called Employees with columns EmployeeID, FirstName, and LastName.

We want to fetch 5 rows from the table starting from the 6th row, ordered by EmployeeID.

 

SELECT EmployeeID, FirstName, LastName

FROM Employees

ORDER BY EmployeeID

OFFSET 5 ROWS

FETCH NEXT 5 ROWS ONLY;

 

In this example:

OFFSET 5 ROWS-

Skips the first 5 rows.

 

FETCH NEXT 5 ROWS ONLY-

Fetches the next 5 rows after skipping the first 5.

 

This query will return 5 rows from the Employees table starting from the 6th row, ordered by EmployeeID.

Keep in mind that the OFFSET and FETCH clauses are available in SQL Server 2012 and later versions. If you're using an older version, you may need to use alternative methods like ROW_NUMBER () function with subqueries to achieve similar results.

Normalization in SQL Server

Normalization in SQL Server

Introduction-

Normalization in SQL Server is a crucial concept in database design that aims to organize data efficiently, reduce redundancy, and maintain data integrity. It involves structuring relational databases to ensure that they meet certain criteria regarding the relationships between tables and the dependencies among their attributes.

 

Normalization's primary objectives are-

Eliminating Redundancy-

Redundant data storage wastes space and can lead to inconsistencies and anomalies when updating or deleting data. Normalization helps to minimize redundancy by breaking down large tables into smaller, more manageable ones.

Avoiding Update Anomalies-

Update anomalies occur when updating data in one place but not in others, leading to inconsistencies. Normalization helps prevent such anomalies by ensuring that data is stored in a way that makes it easy to update without risking inconsistency.

Ensuring Data Integrity-

Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. Normalization contributes to data integrity by structuring data to reduce the risk of errors, inconsistencies, and inaccuracies.

 

Database Normalization-

Every database you construct should undergo the process of database normalization. Normal Forms are a collection of formal criteria and principles that are applied to a database design.

 

The disadvantages of data redundancy include-

• Data upkeep becomes difficult; data deletion and upgrades become problematic.
• It generates data discrepancies.
• Insert, update, and delete abnormalities become common. An update anomaly, for example, means that all copies of the same record, duplicated in different places in the database, must be updated to maintain the record consistent.
• Redundant data increases the size of a database and takes up an excessive amount of space on disk.

 

Normal Forms-

 

The goal of the following article is to present the essential information about database normalization. Although normalization is a broad topic, this article aims to give readers enough background knowledge to comprehend the first three types of database normalization.

• 1 NF, or First Normal Form
• Form 2 Normal, second (NF)
• Form 3: Third Normal (3 NF)

If a database satisfies the prerequisites of the first three normal forms, it is regarded as the third normal form.

 

First Normal Form (1NF)-

Ensures that each column in a table contains atomic (indivisible) values.

Example: Original Table:

| Student_ID | Name            | Courses            |

|------------|-----------------|--------------------|

| 1          | John Doe        | Math, Physics      |

| 2          | Jane Smith      | Chemistry, Biology |

 

After normalization to 1NF- 

Students Table-

| Student_ID | Name      |

|------------|-----------|

| 1          | John Doe  |

| 2          | Jane Smith|

 

Courses Table-

| Student_ID | Course   |

|------------|----------|

| 1          | Math     |

| 1          | Physics  |

| 2          | Chemistry|

| 2          | Biology  |

 

Second Normal Form (2NF)-

Every non-prime attribute is fully functionally dependent on the primary key.

Example-

 

Original Table-

 

| Student_ID | Course   | Instructor   |

|------------|----------|--------------|

| 1          | Math     | Mr. Smith    |

| 1          | Physics  | Mr. Johnson  |

| 2          | Chemistry| Mrs. Brown   |

| 2          | Biology  | Mr. Green    |

 

After normalization to 2NF-

Students Table-

| Student_ID | Name      |

|------------|-----------|

| 1          | John Doe  |

| 2          | Jane Smith|

 

Courses Table-

| Course   | Instructor |

|----------|------------|

| Math     | Mr. Smith  |

| Physics  | Mr. Johnson|

| Chemistry| Mrs. Brown |

| Biology  | Mr. Green  |

 

Enrollments Table-

| Student_ID | Course   |

|------------|----------|

| 1          | Math     |

| 1          | Physics  |

| 2          | Chemistry|

| 2          | Biology  |

 

Third Normal form (3NF)-

 

Every non-prime attribute is non-transitively dependent on the primary key.

Example-

Original Table-

 

| Student_ID | Course   | Instructor   | Instructor_Phone |

|------------|----------|--------------|------------------|

| 1          | Math     | Mr. Smith    | 555-1234         |

| 1          | Physics  | Mr. Johnson  | 555-5678         |

| 2          | Chemistry| Mrs. Brown   | 555-9876         |

| 2          | Biology  | Mr. Green    | 555-4321         |

 

After normalization to 3NF-

Students Table-

| Student_ID | Name      |

|------------|-----------|

| 1          | John Doe  |

| 2          | Jane Smith|

 

Course Table-

 

| Course   | Instructor |

|----------|------------|

| Math     | Mr. Smith  |

| Physics  | Mr. Johnson|

| Chemistry| Mrs. Brown |

| Biology  | Mr. Green  |

 

Instructor Table-

 

| Instructor   | Phone     |

|--------------|-----------|

| Mr. Smith    | 555-1234  |

| Mr. Johnson  | 555-5678  |

| Mrs. Brown   | 555-9876  |

| Mr. Green    | 555-4321  |

 

Enrollment Table-

 

| Student_ID | Course   |

|------------|----------|

| 1          | Math     |

| 1          | Physics  |

| 2          | Chemistry|

| 2          | Biology  |

 

These three normalization forms are the ones that are discussed the most. Higher normal forms do exist, albeit they are not as commonly employed in practice as Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF). In a relational database, each normalization form aids in reducing anomalies, redundancies, and preserving data integrity.

Trigger and create a simple logon Trigger

Trigger and create a simple logon Trigger

Introduction-

In order to connect using SSMS or client apps, SQL Server offers Windows and SQL authentication methods. For DBAs, security is always the first priority, particularly in the production setting. We can manage database access by giving users the minimal and suitable permissions for their task. SQL Server's provide and DENY statements can be used to provide each user the proper task permissions.

Trigger-

In SQL Server, a trigger is a special kind of stored procedure that is triggered automatically by specific events, such adding, modifying, or removing data from a table. Triggers can be used to audit data changes, uphold data integrity, and enforce business rules.

Types-

DML (Data Manipulation Language) and DDL (Data Definition Language) triggers are the two primary categories of triggers in SQL Server. DDL triggers are triggered by events connected to the database structure, including adding or removing tables, whereas DML triggers are triggered by insert, update, or delete statements.

 

Logon Trigger in SQL Server-

Database code known as "triggers" is run when a specific event occurs. Login triggers are a useful tool for managing SQL login security. When a logon event has place, SQL Server initiates the logon triggers automatically. It is carried out prior to the establishment of a user session and the accomplishment of authentication.

 

For an efficient database connection in any database, we use two terms.

 

Authentication-

To establish a connection using SQL authentication, we provide a username and password. It is an authentication process for the user.

 

Authorization-

Authorization is a permission that grants you the ability to carry out particular tasks. You might not be allowed to make any changes at the instance level, but you might be granted complete access to a particular database.

 

Create a simple Logon Trigger-

 

USE master;

GO

 

CREATE TABLE LogonAudit (

   LoginName nvarchar(128) NOT NULL,

   LoginTime datetime NOT NULL

);

GO

 

CREATE TRIGGER LogonAuditTrigger

ON ALL SERVER

FOR LOGON

AS

BEGIN

   INSERT INTO LogonAudit (LoginName, LoginTime)

   VALUES (ORIGINAL_LOGIN(), GETDATE());

END;

GO

In this example, we first create a table named "LogonAudit" to store the login audit information. The table has two columns: "LoginName" and "LoginTime".

Next, we create the logon trigger named "LogonAuditTrigger" those fires for every LOGON event. The trigger is created on the "ALL SERVER" scope, which means it applies to the entire SQL Server instance.

The trigger body is enclosed in the "BEGIN" and "END" keywords. It contains a single statement that inserts the login name and login time into the "LogonAudit" table.

The "ORIGINAL_LOGIN()" function returns the name of the original SQL Server login that initiated the session. The "GETDATE()" function returns the current date and time.

The "ORIGINAL_LOGIN()" function returns the name of the original SQL Server login that initiated the session. The "GETDATE()" function returns the current date and time.

CDC in SQL Server

A brief description of SQL Server's CDC-

Change Data Capture, or CDC for short, was initially introduced in the SQL Server 2008 edition as a useful feature to track and record changes made to SQL Server database tables without requiring extra programming work. Prior to SQL Server 2016, a SQL Server database could only have Change Data Capture enabled with the SQL Server Enterprise edition; however, as of SQL Server 2016, this is no longer necessary.

 

A mirrored table with the same column structure as the source tables and extra columns to capture the description of these changes is created by Change Data Capture, which keeps track of the INSERT, UPDATE, and DELETE actions on the database table. For each INSERT statement, SQL Server creates one record that displays the values that are inserted; for each DELETE statement, it creates one record that displays the data that is deleted; and for each UPDATE statement, it creates two records—one that displays the data that was there prior to the change and the other that displays the data that was there after it was made.

 

Design and configuration-

 

A SQL Server instance must have a SQL Server Agent active in order to use Change Data Capture. A SQL Server database table that has this feature enabled will have two SQL Server Agent jobs created for it. The first job's task is to update database change tables with the updated data, and the second job's task is to tidy up the change tables by removing records that are older than the three-day configurable retention period. As the data source changes, Change Data Capture is dependent on the SQL Server Transaction Log. A modification will be recorded in the Transaction Log file as soon as it is made.

 

Change Data Capture as a Solution for Audits-

Without the ability to track SELECT statements, Change Data Capture can be used as an asynchronous SQL Server Audit solution to monitor and audit DML changes made to the table, such as INSERT, UPDATE, and DELETE operations.

Change Data Capture is an effective SQL Server audit solution because it is simple to set up with a few T-SQL statements, offers historical data about values prior to the change process, and gives comprehensive information on the data modification procedure.

Let's see how we can audit the SQL Server DML modifications using Change Data Capture.

 

Enabling DAC-

Step 1-

Open the Management Studio for Microsoft SQL Server and make a new database and table.

 

Create Database CDC1

use CDC1

 

CREATE TABLE CDC_TAB

(

 ID INT,

 Name VARCHAR (50),

 Place VARCHAR (50),

 Salary INT,

)

Step 2-

After creating the previously mentioned database and table, use the following Insert SQL Statements to add some data to the CDC_TAB table.

Step 3-

CDC use in SQL Server

In SQL Server, CDC must be enabled in two steps. They are listed in the following order:

CDC must first be enabled at the database level.

Next, we must specify which tables require CDC activation.

Enable CDC on Database Level: 

To enable CDC on the database level, run the following stored procedure in the system.

 

Step 4-

Enable CDC at Table Level

Using the CDC1 database, we generated the CDC_TAB table. Let's now activate CDC for this Employee table. As demonstrated below, we must run the system stored procedure sp_cdc_enable_table in order to enable CDC at the table level. Here, we are giving the stored procedure access to the schema, table name, and roll.

 

EXEC sys.sp_cdc_enable_table

@source_schema = 'dbo',

@source_name = 'CDC_TAB',

@role_name = null

Step 5-

The SQL Server Agent service must be running for the CDC to operate properly.

Step 6-

The tables that truly record insert, update, and delete action for a specific table are called _CT tables. For instance, every Insert, Update, and Delete operations made on the CDC_TAB table will be recorded in the dbo_CDC_TAB_CT table. A table named dbo_EMP_CT will be created to record all DML operations made on the EMP table if you have another table named EMP and you enable CDC for that table.

Step 7-

For instance, updating data in the CDC_TAB table

UPDATE CDC_TAB SET Name = 'Ayush' WHERE ID = 7

 

UPDATE CDC_TAB SET Name = 'Abhishek' WHERE ID = 6

Step 7-

It will be recorded in the system table.

Step 8-

Deleting from the CDC_TAB table, for instance

To observe what occurs in the dbo_CDC_TAB_CT table, let's remove one record from the CDC_TAB. In order to remove the CDC_TAB with ID number three, please run the SQL statement below.

DELETE FROM CDC_TAB WHERE ID = 7

Please review the dbo_CDC_TAB_CT table as indicated below after deleting the CDC_TAB. It creates a single record for the removed CDC_TAB, as you can see.

Step 9-

Example: Inserting into the CDC_TAB table.

Let's add one record to the CDC_TAB

 

INSERT INTO CDC_TAB VALUES (8, 'Yash', 'meerut', 900000)

Step 10 –

Identify which row is utilized for what type of activity.

The operation column can take one of four values (1, 2, 3, or 4), and we'll look at what each means.

Delete Statement = 1

Insert Statement = 2

Value before update statement = 3

Value after update statement = 4

Step 11-

To Disabled CDC Database Level and table level

 

EXECUTE sys.sp_cdc_disable_db;

EXEC sys.sp_cdc_disable_table

    @source_schema = 'dbo',

    @source_name   = 'CDC_TAB',

    @capture_instance = 'all';

Advantages and Disadvantages-

 

Advantages of CDC in SQL Server-

 

Efficient Change Tracking-

CDC provides an efficient mechanism for tracking changes to data without the need for manual triggers or logging mechanisms.

It captures changes at the database level, making it suitable for scenarios where detailed change history is required.

 

Minimized Performance Impact-

CDC is designed to have minimal impact on the performance of the database system.

It uses log-based change tracking, ensuring that changes are captured without affecting the operational performance of the database.

 

Support for ETL Processes-

CDC is often used in conjunction with Extract, Transform, Load (ETL) processes to identify and replicate changes to data between systems.

ETL processes can use CDC to efficiently identify and process only the changed data.

 

Built-in Change Tables-

CDC automatically creates change tables to store the historical changes, making it easy to query and analyze the historical data.

 

Integration with Replication-

CDC can be integrated with transactional replication to efficiently replicate changes to other databases or servers.

 

Disadvantages of CDC in SQL Server-

 

Configuration Complexity-

Setting up and configuring CDC can be complex, especially for users who are not familiar with the feature.

It requires specific permissions and steps to enable CDC on databases and tables.

 

Maintenance Overhead-

Change tables can grow in size over time, leading to increased storage requirements.

Regular maintenance is required to manage and clean up change tables, which may add administrative overhead.

 

Limited to Certain Editions-

CDC is not available in all editions of SQL Server. For example, it's not available in the Express edition, which may limit its use in certain environments.

 

Lack of Granular Control-

CDC operates at the database and table level, which means that it may capture more data than necessary in certain scenarios.

There is limited control over which specific columns or types of changes to capture.

 

Increased Storage Requirements-

The change tables can consume additional storage space, especially in scenarios where there are frequent and significant changes to the data.

 

In summary, CDC is a powerful feature for tracking changes in SQL Server, especially in scenarios where a comprehensive change history is required. However, its implementation should be carefully considered based on the specific requirements and constraints of the environment.

 

That covers everything regarding the CDC.