Today, I'd want to revisit one of the most typical interview questions and replies, which I see frequently referenced in subsequent interviews.

Transaction
A transaction is a collection of SQL statements that operate as a single unit. In layman's terms, a transaction is a unit in which a series of tasks is completed in order to finish the entire activity. To better appreciate this, consider a bank transaction.

SQL Server ACID Properties
SQL Server's ACID properties assure data integrity during a transaction. A transaction is a series of SQL (insert, update, and delete) statements that are handled as a single unit and are executed using the completed or Not principle. To be successful, a transaction must adhere to the ACID Properties. Atomicity, Consistency, Isolation, and Durability are abbreviated as ACID.

Atomicity
Every transaction adheres to atomicity. It indicates that if a transaction is initiated, it must be completed or rolled back. To illustrate, if a person transfers money from account "A" to account "B," the money should be credited to account B after the transaction is completed. If a failure occurs, the modification should be rolled back after debiting the money from account "A." In other words, each transaction will either succeed or fail.

Consistency
Consistency says that after the completion of a transaction, changes made during the transaction should be consistent. Let’s understand this fact by referring to the above example, if account “A” has been debited by 200 RS, then after the completion of the transaction, account “B” should be credited by 200 RS. It means changes should be consistent. In simple words, Consistency means a guarantee that a transaction never leaves your database in a half-finished state.

Isolation
Isolation states that every transaction should be isolated from each other. There should not be any interference between the two transactions. In simple words, Any other operation cannot affect my operation.

Durability
Durability means that once the transaction is completed, all the changes should be permanent. It means that in case of any system failure, changes should not be lost. In simple words, When the Transaction is complete, the changes are saved.Now we can understand this with the help of a Figure. This is very easy for us. This Figure is shown below.

FAQs
Q. What is the difference between the Local and the Distributed SQL Server transactions?
The Local transaction is an SQL Server transaction that processes data from the local database server

The Distributed transaction is an SQL Server transaction that processes data from more than one database server

Q. Some transactions may not complete their execution successfully. Explain these transactions.
These transactions are known as,

Aborted.

Whenever a transaction finishes its final statement, it ultimately enters into this state:

Partially committed.
Q. What is the SQL Server Transaction Log LSN?
Each Transaction log record that is written to the SQL Server transaction log file can be identified by its Log Sequence Number (LSN).

When working with SQL, one of the most significant data types you'll come across is DateTime. DateTime, which represents dates and times, is critical for managing time-sensitive data such as event scheduling, transaction timestamps, and historical records. In this post, we will go deep into DateTime in SQL to help you become a professional SQL developer, looking at its features, functions, real-world examples, and recommended practices.

Understanding DateTime in SQL Server
DateTime is a SQL data type that combines date and time data into a single value. It is required for capturing temporal moments accurately. SQL databases provide a variety of DateTime data types, including DATE, TIME, DATETIME, and TIMESTAMP, each with its own function.

Date and Time Functions
With the help of SQL's DateTime methods, you have numerous options for handling and modifying date and time data. Understanding the syntax and breadth of applications for these functions is critical to realizing their full potential.

1. GETDATE() / CURRENT_TIMESTAMP
Retrieves the current date and time.

Syntax
GETDATE() | CURRENT_TIMESTAMP

Examples
SELECT GETDATE() AS CurrentDateTime;

Output

SELECT CURRENT_TIMESTAMP AS CurrentTimestamp;

Output

2. DATEADD()
Adds or subtracts a specified time interval to/from a DateTime value.

Syntax
DATEADD(interval, number, date)

interval: Specifies the unit of time (e.g., year, month, day) to add or subtract.
number: Represents the quantity of intervals to add (positive) or subtract (negative).
date: The starting date or time to which the operation is applied.

Example
SELECT DATEADD(year, 2, '2023-09-13') AS NewDate;In this example, we add 2 years to the given date, resulting in '2025-09-13'.

Example
SELECT DATEADD(day, -10, '2023-09-13') AS NewDate;

Output

Here, we subtract 10 days from the given date, resulting in '2023-09-03'.

Example
SELECT DATEADD(hour, 3, '2023-09-13 10:00:00') AS NewTime;

Output

This adds 3 hours to the given time, resulting in '2023-09-13 13:00:00'. As per the above example of DateAdd, we can modify the datetime value in different ways.

3. DATEDIFF()
Calculates the difference between two DateTime values in a specified unit (e.g., years, months, days).

Syntax
DATEDIFF(interval, start_date, end_date)

interval: Specifies the unit of time (e.g., year, month, day) to calculate the difference in.
start_date: The beginning date or time.
end_date: The ending date or time.

Example
SELECT DATEDIFF(day, '2023-09-10', '2023-09-13') AS DaysDifference;

Output

In this example, we calculate the difference in days between '2023-09-10' and '2023-09-13', resulting in '3'.

Example
SELECT DATEDIFF(month, '2023-01-15', '2023-09-20') AS MonthsDifference;

Output

Here, we calculate the difference in months between '2023-01-15' and '2023-09-20', resulting in '8'.

Example
SELECT DATEDIFF(year, '1995-08-19', GETDATE()) AS AgeInYears;

Output

In this example, we determine the age of a person born on '1995-08-19' by calculating the difference in years between their birthdate and the current date using GETDATE(). The result is the person's age in years.

4. CONVERT()
Converts DateTime values between different formats.

Syntax
CONVERT(data_type, expression, style)

data_type: Specifies the target data type to which you want to convert the expression.
expression: The value or column to be converted.
style: Defines the format for the conversion (optional).

Example
SELECT CONVERT(DATE, GETDATE()) AS DateOnly;

Output

In this example, we convert the current date and time obtained using GETDATE() into a Date data type. This results in extracting only the date portion, like '2023-09-13'.

Example
SELECT CONVERT(DATETIME, '2023-09-13 15:16:00', 120) AS ConvertedDateTime;

Output

Here, we convert the string '2023-09-13 15:16:00' into a DateTime data type using style '120'. This results in a DateTime value like '2023-09-13 15:16:00.000'.

5. FORMAT()
Formats DateTime values into user-friendly strings.

Syntax
FORMAT(expression, format)
expression: The value or column you want to format, often a DateTime value.
format: Specifies the desired format for the expression.

Example
SELECT FORMAT(GETDATE(), 'd') AS ShortDate;

Output

In this example, we format the current date and time obtained using GETDATE() into a short date format (MM/DD/YY or equivalent based on localization). The result could be something like '09/13/23'.

Example
SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy HH:mm:ss') AS CustomFormattedDateTime;

output

Here, we take the current date and time and format it into a custom string that includes the full month name, day, year, and time in the 'MMMM dd, yyyy HH:mm:ss' format. The result might look like 'September 13, 2023 15:23:52'.

Example
SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS TimeOnly;

Output

In this example, we format the current date and time into a time-only format (HH:mm:ss), showing only the hours, minutes, and seconds. The result could be something like '15:25:43'.

Upcoming Events
Retrieve events that are scheduled for the future.

CREATE TABLE Events (
    EventID INT PRIMARY KEY,
    EventName VARCHAR(100),
    EventDateTime DATETIME
);


INSERT INTO Events (EventID, EventName, EventDateTime)
VALUES
    (1, 'Tech Conference', '2023-09-20 14:30:00'),
    (2, 'Product Launch', '2023-10-05 09:00:00'),
    (3, 'Workshop of SQL', '2023-09-25 10:00:00'),
    (4, 'Seminar', '2023-11-15 15:45:00'),
    (5, 'Annual Conference', '2022-08-01 18:00:00'),
    (6, 'Annual MVP Seminar', '2022-10-14 11:30:00');


Select * From Events

SELECT EventName, EventDateTime
FROM Events WHERE EventDateTime > GETDATE() Order By EventDateTime;

Output

Best Practices

When working with DateTime functions in SQL, consider these best practices:

• Data Validation: Ensure that your DateTime values are valid to avoid unexpected results.
• Avoid Mixing Data Types: Be cautious when mixing different DateTime data types in calculations.
• Optimize Queries: Index DateTime columns for improved query performance, especially in large datasets.
• Handle Time Zones: Address time zone issues when dealing with international data.

Summary
DateTime type in SQL, enabling you to handle date and time-related data effectively. By understanding its properties, functions, and best practices, you can ensure data accuracy and precision in your SQL databases.

If you find this article valuable, please consider liking it and sharing your thoughts in the comments.

Thank you, and happy coding.

HostForLIFEASP.NET SQL Server 2022 Hosting

In SQL Server, you can use the MERGE statement to perform conditional INSERT, UPDATE, or DELETE operations in a single query. The MERGE statement is often used for synchronizing data between two tables or performing upserts (INSERT or UPDATE, depending on whether a matching row exists). Here's an example of how you can use the MERGE statement with multiple examples for each operation (INSERT, UPDATE, DELETE):

Assume you have two tables: TargetTable and SourceTable. We'll use these tables for our examples.
INSERT

Suppose you want to insert rows from SourceTable into TargetTable if they don't already exist in the target table.

MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name)
    VALUES (Source.ID, Source.Name);

This SQL code will insert rows from SourceTable into TargetTable where the ID doesn't match.
UPDATE

Suppose you want to update existing rows in TargetTable with data from SourceTable. When there's a match.
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name;

This code updates the Name column in TargetTable If there's a matching ID in SourceTable.
DELETE

Suppose you want to delete rows from TargetTable If they don't exist in SourceTable.
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

This code will delete rows from TargetTable where the ID doesn't match any in SourceTable.

Now, let's see how you can execute these MERGE statements with multiple examples.
-- Example 1: Insert
INSERT INTO SourceTable (ID, Name)
VALUES (1, 'John'), (3, 'Alice');

-- Example 2: Update
UPDATE SourceTable
SET Name = 'Bob'
WHERE ID = 2;

-- Example 3: Delete
DELETE FROM SourceTable
WHERE ID = 4;

-- Execute MERGE for all operations
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name)
    VALUES (Source.ID, Source.Name)
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

In this example, we first perform individual INSERT, UPDATE, and DELETE operations on the SourceTable. Then, we execute the MERGE statement to synchronize the TargetTable with the SourceTable using a single query. The MERGE statement handles all three operations based on the specified conditions.

HostForLIFEASP.NET SQL Server 2022 Hosting

In SQL, a default value is a value that is assigned to a column when no other value is given. Default values can be used to ensure that every row in a table has a value for a specific column, even if the value is unknown when the row is produced.

Assume we have a table of employees and we want to ensure that each employee has a gender. We could add a Gender column and set the default value to male. This means that if no other value is supplied, any new employee will be assigned the gender male by default.

Now we'll look at the significance of default values in SQL, their benefits, and potential drawbacks.

SQL Default Values' Importance
Default values serve several essential purposes in SQL databases.

• Data Integrity: They ensure that essential fields are never left empty. For instance, in a user table, a default value for the 'registration_date' column can ensure that every new user has a registration date.
• Simplifying Data Entry: Defaults can simplify data entry by providing initial values for fields, reducing the workload on users and preventing errors.
• Compatibility: Default values can make your database more compatible with external systems or APIs that expect specific data formats.

How to Create a SQL Default Value
In SQL, you can use the DEFAULT term to specify a default value. The examples below demonstrate how to set dafault values in SQL.

Example 1
The following is an example of how to set a default value of 0 for a numeric column.     

-- Creating a table with default values
CREATE TABLE Products (
  ID INT NOT NULL,
  ProductName VARCHAR(20) NOT NULL,
  Price INT DEFAULT 0
);

-- Inserting a new user with defaults
INSERT INTO Products (ID , ProductName) VALUES (1, 'Laptop');
INSERT INTO Products (ID , ProductName) VALUES (2, 'Keyboard');

-- Querying the table
SELECT * FROM Products;

Output

The Price column in this example has a default value of 0. This means that if no other value is supplied, any new entry entered into the Products table will have a value of 0 for the Price column.

Example No. 2
The example below shows how to define a default value for a date and string column.

-- Creating a table with default values
CREATE TABLE Users(
    Id INT PRIMARY KEY,
    UserName VARCHAR(50) NOT NULL,
    Registration_Date DATE DEFAULT GETDATE(),
    Gender VARCHAR(6) DEFAULT 'Male'
);

-- Inserting a new user with defaults
INSERT INTO Users (Id, UserName) VALUES (1, 'Peter');
INSERT INTO Users (Id, UserName) VALUES (2, 'Scott');
INSERT INTO Users (Id, UserName) VALUES (3, 'Laura');

-- Querying the table
SELECT * FROM Users;

In this example, the Registration_Date column has a default value of GETDATE(), which means the current date of the server, and the gender column has a default value of Male.

Advantages of Using Default Values
Now, let's delve into the advantages of utilizing default values in SQL.

• Consistency: Default values promote consistency across your database. When multiple records are inserted without specifying a particular column's value, they all get the same default value.
• Time-Saving: They save time during data insertion, especially for columns that often have the same value. This efficiency can boost developer productivity.
• Error Prevention: Defaults can help prevent human errors by ensuring that important fields are never left blank. This is especially critical for fields containing critical data.

Disadvantages of Using Default Values
While default values offer numerous benefits, they can also have some drawbacks:

• Hidden Data: Default values may hide missing or incorrect data. If users rely too heavily on defaults, it can lead to data quality issues.
• Complexity: In some cases, handling default values can add complexity to SQL queries, especially when you need to override or update them.
• Overuse: Overusing default values can make your database less flexible. It's essential to strike a balance between defaults and explicit data entry.

Summary
Default values can be a useful tool for ensuring data integrity, simplifying data entry, and improving performance. However, it is important to be aware of the potential disadvantages of using default values before using them. If you find this article valuable, please consider liking it and sharing your thoughts in the comments.
Thank you, and happy coding.

HostForLIFEASP.NET SQL Server 2022 Hosting