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.