Depending on the input values, scalar functions in SQL return a single value. Instead of working with sets of rows, these functions work with individual values.

Common Scalar Functions
LEN(): Returns the length of a string.
UPPER(): Converts a string to uppercase.
LOWER(): Converts a string to lowercase.
ROUND(): Rounds a number to a specified decimal place.
GETDATE(): Returns the current date and time.

Example Usage of Scalar Functions

1. Using LEN() Function
SELECT LEN('Hello World') AS StringLength;

2. Using UPPER() and LOWER() Functions
SELECT UPPER('hello') AS UpperCase, LOWER('WORLD') AS LowerCase;

Output

3. Using ROUND() Function
SELECT ROUND(123.456, 2) AS RoundedValue

4. Using GETDATE() Function
SELECT GETDATE() AS CurrentDateTime;

5. Using ABS() Function
SELECT ABS(-25) AS AbsoluteValue;

6. Using SQRT() Function
SELECT SQRT(49) AS SquareRoot;

7. Using SUBSTRING() Function
SELECT SUBSTRING('SQL Functions', 5, 9) AS SubstringResult;

8. Using REPLACE() Function
SELECT REPLACE('Hello SQL', 'SQL', 'World') AS ReplacedString;

Advanced Use of Scalar Functions
1. Combining Scalar Functions
SELECT UPPER(LEFT('advanced scalar functions', 8)) AS Result;

2. Using Scalar Functions in Computations
SELECT ROUND(AVG(Salary), 2) AS AverageSalary FROM Employees;

3. Formatting Dates Using Scalar Functions
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;

4. Custom Scalar Function Example
CREATE FUNCTION dbo.Getfullname(@FirstName NVARCHAR(50),
                                @LastName  NVARCHAR(50))
returns NVARCHAR(100)
AS
  BEGIN
      RETURN ( @FirstName + ' ' + @LastName )
  END;

Usage
SELECT dbo.GetFullName('John', 'Doe') AS FullName;

Advantages of Scalar Functions

• Helps in data formatting and transformation.
• Improves code readability and maintainability.
• Enhances query flexibility with built-in SQL functions.

Scalar functions are essential for manipulating individual values in SQL queries.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

A roadmap that describes how a query will be run is called a SQL Execution Plan. It aids in SQL query analysis and optimization.

Execution Plan Types
Estimated Execution Plan: This illustrates how the query would function even if it were not run.
Actual Execution Plan: This displays runtime information along with the query's actual execution.

How to Get the Execution Plan?
Using SQL Server Management Studio (SSMS)
Estimated Execution Plan: Press Ctrl + L or go to Query > Display Estimated Execution Plan.

Actual Execution Plan: Press Ctrl + M or go to Query > Include Actual Execution Plan, then run the query.

Using T-SQL Commands
Estimated Execution Plan
SET SHOWPLAN_XML ON;
SELECT * FROM Users WHERE UserID = 1;
SET SHOWPLAN_XML OFF;

Actual Execution Plan
SET STATISTICS XML ON;
SELECT * FROM Users WHERE UserID = 1;
SET STATISTICS XML OFF;

Understanding Execution Plan Components

Tips to Optimize SQL Queries

Use Indexes: Create indexes on frequently used columns.
Avoid SELECT *: Retrieve only the required columns.
Optimize Joins: Prefer INNER JOIN over OUTER JOIN if possible.
Check Execution Plan: Avoid Table Scans and use Index Seeks.
Avoid Functions on Indexed Columns: Example: WHERE YEAR(DateColumn) = 2023 prevents index usage.

In the next part, we will dive deeper into SQL execution plans, covering advanced topics like operator costs, parallelism, query hints, and execution plan caching, helping you gain a more comprehensive understanding of how SQL Server processes queries efficiently. Stay tuned!

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Statement of the Problem
Numerous database developers run into unforeseen inconsistencies while using SQL Server for calculations.  When the same mathematical phrase is evaluated differently, one typical problem occurs.  Take the following SQL Server code snippet, for example:

DECLARE @Number1 AS DECIMAL(26,7) = 0.9009000;
DECLARE @Number2 AS DECIMAL(26,7) = 1.000000000;
DECLARE @Number3 AS DECIMAL(26,7) = 1000.00000000;
DECLARE @Result  AS DECIMAL(26,7);

SET @Result = (@Number1 * @Number2) / @Number3;
SELECT @Result; -- 0.0009000
SET @Result = (@Number1 * @Number2);
SET @Result = (@Result / @Number3);
SELECT @Result; -- 0.0009009

In the first case, the output is 0.0009000, while in the second case, the output is 0.0009009. This divergence raises the question: Why are the results different when the same calculation is performed?

Explanation. Single Step Calculation
In the first approach, the entire expression (@Number1 * @Number2) / @Number3 is computed in a single step:

SQL Server first computes the product of @Number1 and @Number2, which equals 0.9009000.
Next, it divides that result by @Number3 (1000.00000000).

The result of this division is affected by how SQL Server handles precision and rounding for decimal operations. This might introduce slight inaccuracies, leading to the outcome of 0.0009000.

Multiple Step Calculation
In the second approach, the operations are separated into two distinct steps:

First, the calculation @Number1 * @Number2 is executed and stored in @Result. This retains the value of 0.9009000.
Then, the variable @Result is divided by @Number3 in a separate statement.

This step-by-step division allows SQL Server to apply different rounding and precision rules, which can sometimes yield a more accurate result of 0.0009009.

Conclusion
The difference in outputs can often be attributed to the varying treatment of precision and rounding during calculations:

• In a single-step calculation, SQL Server evaluates the entire expression at once, potentially altering precision during the process.
• In a multiple-step calculation, SQL Server retains more precision through intermediate results, leading to a different output.

Resolution
To achieve consistent results in SQL Server calculations, developers should consider controlling precision explicitly. For example, applying rounding can help standardize outcomes:
SET @Result = ROUND((@Number1 * @Number2) / @Number3, 7);

By managing precision and rounding explicitly, programmers can avoid discrepancies and ensure that their numerical calculations yield the expected results. Understanding these nuances in SQL Server can lead to more reliable and accurate database operations.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

A Memory-Optimized Table Variable: What Is It?
A unique kind of table variable that makes use of SQL Server's In-Memory OLTP engine is called a Memory-Optimized Table Variable. It is maintained in memory, which lowers tempdb contention and improves performance for workloads involving frequent data processing, in contrast to ordinary table variables or temporary tables (#temp).

Why is it Useful?
Memory-Optimized Table Variables provide.

• Faster performance: Avoids disk I/O by keeping data in memory.
• Tempdb contention reduction: Traditional temp tables and table variables rely on tempdb, which can be a bottleneck.
• Optimized latch-free concurrency: Uses memory-optimized data structures for ultra-fast access.
• Efficient for short-lived data: Ideal for scenarios where data exists only within a session or batch.

When to Use It?
You should use Memory-Optimized Table Variables when,

• Your queries experience tempdb contention.
• You perform frequent batch operations with temporary data.
• You need high-performance OLTP workloads with frequent inserts and lookups.
• You are working with stored procedures that rely on table variables.

Where to Use It?

• Stored procedures that process intermediate data.
• ETL workloads require temporary staging tables.
• High-performance transaction processing systems.
• Session-based data caching to avoid repeated database calls.

How to Use a Memory-Optimized Table Variable?
1. Enable Memory-Optimized Tables in the Database
Before using memory-optimized table variables, you must enable In-Memory OLTP.

ALTER DATABASE YourDatabase
ADD FILEGROUP MemoryOptimizedFG CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE YourDatabase
ADD FILE (NAME = 'MemOptData', FILENAME = 'C:\Data\MemOptData')
TO FILEGROUP MemoryOptimizedFG;

2. Declare a Memory-Optimized Table Variable
Unlike a regular table variable, you must use MEMORY_OPTIMIZED = ON.
DECLARE @MemOptTable TABLE
(
ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
Name NVARCHAR(100) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);

3. Insert and Query Data Efficiently
INSERT INTO @MemOptTable (ID, Name) VALUES (1, 'SQL Server'), (2, 'DBA Expert');

SELECT * FROM @MemOptTable;

4. Compare with Traditional Table Variables
A traditional table variable.
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(100));

Uses tempdb, causing I/O overhead.
Suffers from locking and latching under high concurrency.

Memory-Optimized Table Variables

• Avoid tempdb entirely.
• Are lock-free and latch-free, making them 10x faster in some cases.

Real-Time Example: Improving Performance in a High-Traffic System

• Scenario: A banking application processes thousands of real-time transactions per second. Using traditional table variables slows down the system due to tempdb contention.
• Solution: By replacing standard table variables with Memory-Optimized Table Variables, the system eliminates tempdb bottlenecks, resulting in a 40% faster transaction processing time.

DECLARE @TransactionLog TABLE
(
TransactionID INT NOT NULL PRIMARY KEY NONCLUSTERED,
AccountID INT NOT NULL,
Amount DECIMAL(10,2) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);

Memory-optimized table Variables were introduced in SQL Server 2016 and are available in the following versions.
Compatible SQL Server Versions
SQL Server 2016 (13.x)
SQL Server 2017 (14.x)
SQL Server 2019 (15.x)
SQL Server 2022 (16.x)

Not Available In
SQL Server 2014 and earlier (Memory-optimized tables were introduced in 2014, but table variables were not supported as memory-optimized until 2016).

Best Practices & Recommendations
Enable In-Memory OLTP before using Memory-Optimized Table Variables.
Use them in stored procedures or batch processing scenarios for best performance.
Avoid using them for large datasets due to memory limitations.
Use Non-Clustered Indexes properly to avoid performance bottlenecks.
Test with different workloads before applying in production environments.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Using ROW_NUMBER() and PARTITION BY (Preferred Approach)
The ROW_NUMBER() function assigns a unique row number to each record within a partition (group). We can use this to identify and delete duplicates while keeping only the required data.

Query Syntax
WITH CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS row_num
    FROM table_name
    WHERE condition  -- Apply filtering condition here
)
DELETE FROM CTE
WHERE row_num > 1;

Example
Consider a Customer table with duplicate entries based on Email.

Removing Duplicates While Keeping the First Entry.

;WITH CTE AS (
    SELECT ID
    FROM (
        SELECT ID,
               ROW_NUMBER() OVER (PARTITION BY NAME, Email, City ORDER BY ID) AS RN
        FROM Customers
        WHERE City = 'NY'  -- Only NY state filtering condition
    ) AS sub
    WHERE RN > 1
)
DELETE FROM Customers
WHERE ID IN (SELECT ID FROM CTE);

Explanation of the Query
Identifies Duplicates

• ROW_NUMBER() OVER (PARTITION BY Name, Email, City ORDER BY ID)
• Assign a row number (RN) for each duplicate group, keeping the first record (RN = 1).

Filters Out Duplicates (RN > 1): Only marks duplicate records where City = 'NY'.
Deletes Duplicate Records: Deletes only IDs from the CTE that have RN > 1
This syntax will be useful when we are joining more tables and deleting duplicates from one specific table.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Performance is frequently the main issue in any SQL Server system when working with big datasets. The INSERT operation is one frequent operation that occasionally turns into a bottleneck. The time required to input data increases with its size, which can have a major effect on system performance and user experience in general. The usage of the TABLOCK hint is one of the many methods and improvements that SQL Server offers to help speed up data insertions. When working with huge datasets or when parallelism is crucial, this straightforward yet effective method can significantly increase the pace of your INSERT operations.

What is the TABLOCK Hint?
The TABLOCK hint is a table-level lock hint that forces SQL Server to take a schema modification (Sch-M) lock on the target table when performing an INSERT, UPDATE, or DELETE operation. This hint ensures that the table is locked for the duration of the operation, which can help speed up data loading by minimizing logging and reducing contention.

A key benefit of the TABLOCK hint is that it reduces the amount of log space used during the operation, as the minimal logging mechanism is activated. This means that SQL Server does not have to log each individual row insertion, but rather just the metadata for the bulk operation. As a result, this significantly reduces the overhead and speeds up data loading.

Additionally, because the table is locked at the schema level, it allows SQL Server to parallelize the operation, leading to faster execution times. This is particularly useful for large-scale data-loading tasks.

When to Use TABLOCK Hint
The TABLOCK hint is ideal for scenarios where:

• You are inserting a large number of rows into a table.
• You can afford to lock the table for the duration of the operation (i.e., no other transactions need access to the table while the insert is in progress).
• You want to reduce the logging overhead and speed up bulk insertions.
• You want to use parallel insertions to take advantage of SQL Server's ability to use multiple threads for data loading.

It’s also important to note that the TABLOCK hint works well with temporary tables, so you can take advantage of these performance benefits when working with temp tables, often used in ETL processes or batch operations.

Benefits of Using TABLOCK

• Improved Performance: The primary benefit of using the TABLOCK hint is the performance improvement during large INSERT operations. By reducing the amount of logging, SQL Server can insert rows much faster.
• Parallel Insertion: With TABLOCK, SQL Server can use parallelism to load the data, speeding up the operation of systems with sufficient resources.
• Reduced Logging Overhead: Since SQL Server logs less information, the system consumes less log space, which can be crucial when working with large datasets.
• Works with Temp Tables: You can apply TABLOCK to temporary tables as well, giving you the same performance benefits for in-memory operations.

Example
Let’s consider a scenario where we need to insert a large number of rows from the Sales.SalesOrderDetail table into the Sales.SalesOrderDetailTemp table in the HostForLIFE database.
Create table script for Sales.SalesOrderDetailTem
USE [HostForLIFE]
GO

DROP TABLE IF EXISTS [Sales].[SalesOrderDetailTemp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Sales].[SalesOrderDetailTemp](
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal]  [money] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderDetailTemp_SalesOrderID_SalesOrderDetailTempID] PRIMARY KEY CLUSTERED
(
    [SalesOrderID] ASC,
    [SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Without the TABLOCK hint, this operation may take a considerable amount of time, especially when the table is large and the database is under load.

Here’s a basic example of how you can speed up the INSERT operation by using the TABLOCK hint.
USE HostForLIFE
GO

SET STATISTICS TIME, IO ON

SET NOCOUNT ON

INSERT INTO Sales.SalesOrderDetailTemp
SELECT *
FROM Sales.SalesOrderDetail;

Truncate the table.
USE HostForLIFE
GO

TRUNCATE TABLE Sales.SalesOrderDetailTemp

Now, let’s modify the query to use the TABLOCK hint.
USE HostForLIFE
GO

SET STATISTICS TIME, IO ON

SET NOCOUNT ON

INSERT INTO Sales.SalesOrderDetailTemp
WITH (TABLOCK)
SELECT *
FROM Sales.SalesOrderDetail;

Comparison
Execution 1 (without TABLOCK) took longer, with higher CPU and elapsed time (204 ms and 284 ms), indicating a slower operation. Execution 2 (with TABLOCK) performed better, completing in 125 ms CPU time and 157 ms elapsed time, making the TABLOCK version more efficient in this case.

Considerations When Using TABLOCK
While the TABLOCK hint can greatly improve performance, it’s important to be aware of some considerations:

• Table Locking: The TABLOCK hint locks the entire table for the duration of the operation. This means that other transactions cannot access the table until the INSERT operation is complete, so be sure that this behavior aligns with your application’s requirements.
• Transaction Log Growth: Although TABLOCK reduces the amount of logging, it still logs certain details of the operation. If you’re inserting a massive amount of data, you may need to monitor transaction log growth and ensure that you have enough log space available.
• Not Suitable for OLTP Workloads: The TABLOCK hint is more suited to batch operations or bulk-loading scenarios. It may not be appropriate for transactional systems that require frequent concurrent access to the table.

Conclusion
If you are working with large datasets and want to speed up your INSERT operations in SQL Server, the TABLOCK hint can be a game-changer. By reducing logging overhead and enabling parallel insertions, it helps improve performance and can significantly reduce the time it takes to load data.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

To effectively store NULL values while consuming the least amount of storage space, SQL Server offers Sparse Columns. When NULL values appear in a column in a sizable portion of rows, sparse columns are the best option.

1. What Are Sparse Columns?
Sparse columns are ordinary columns optimized for NULL storage. When a column is declared as SPARSE, it does not consume storage for NULL values, making them beneficial when a large number of rows have NULLs.

• Benefits of Sparse Columns.
• Saves storage by not allocating space for NULL values.
• Reduces I/O operations and improves performance for sparse datasets.
• Supports filtered indexes for better query performance.
• Drawbacks of Sparse Columns.
• Non-NULL values take up more space than regular columns.
• It cannot be used with.
• Text, Ntext, Image, Timestamp.
• User-defined data types.
• Computed columns.
• Default values (unless explicitly specified in an insert).
• CHECK constraints (except NULL constraints).

2. Declaring Sparse Columns
To use sparse columns, declare them with the SPARSE attribute.
Example. Creating a Table with Sparse Columns.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
PhoneNumber VARCHAR(20) SPARSE NULL,
Address NVARCHAR(255) SPARSE NULL
);

PhoneNumber and Address will not consume storage when NULL.

When storing non-NULL values, they use more storage than regular columns.

3. Storage Considerations
The impact on storage depends on the data type.

• For NULL values: Storage savings are significant.
• For Non-NULL values: Sparse columns require an additional 4 bytes.

When to Use Sparse Columns?

• When at least 20-40% of values are NULL, sparse columns save space.
• If NULLs are less frequent, regular columns are more efficient.

Example of Storage Cost for INT Data Type.

4. Using Sparse Columns with Column Sets
SQL Server provides Column Sets to handle sparse columns dynamically.

Example. Using Column Set for Dynamic Queries.
CREATE TABLE EmployeeData (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    PhoneNumber VARCHAR(20) SPARSE NULL,
    Address NVARCHAR(255) SPARSE NULL,
    AdditionalData XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);

AdditionalData (XML) aggregates all sparse column values into a single XML column dynamically.

Retrieving Data Using Column Set
SELECT EmployeeID, AdditionalData FROM EmployeeData;

The Column Set simplifies handling dynamic attributes.

5. Querying Sparse Columns Efficiently
Use Filtered Indexes to optimize queries on sparse columns.

Example. Creating a Filtered Index.
CREATE INDEX IX_Employees_PhoneNumber
ON Employees(PhoneNumber)
WHERE PhoneNumber IS NOT NULL;

This improves query performance for non-NULL sparse column searches.

Example. Query with Index Utilization.
SELECT Name, PhoneNumber
FROM Employees
WHERE PhoneNumber IS NOT NULL;

The filtered index ensures efficient lookups.

6. Checking Sparse Column Storage Space
You can analyze storage savings using sys.dm_db_index_physical_stats.

Check Space Savings.
SELECT name, is_sparse, max_length
FROM sys.columns
WHERE object_id = OBJECT_ID('Employees');

This shows which columns are SPARSE.

7. When NOT to Use Sparse Columns

Avoid sparse columns when:

• NULL values are less than 20-40% of total rows.
• The column is part of frequent aggregations.
• Additional 4-byte overhead is unacceptable.

8. Test Tables with sparse and without parse columns
Create two tables as below:

Add random data in both tables.

Check Table space.

In SQL Server, sparse columns are an effective technique to maximize NULL storage, minimize space consumption, and enhance performance. They function best when a large portion of the values are NULL and can be effectively queried with column sets and filtered indexes.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, we will learn how to create derived columns. Derived column task in SSIS is used to,

• Create new column
• Update existing columns
• Merge different columns into one column. For example, businesses want to concatenate first name, middle name, and last name to make it a full name.

Now let’s understand this with an example.
We will use the database, which has already been loaded into the SQL server, to create a derived column function.

Now let’s go to SSMS and see the table how it looks right now.

So here let’s say, the business wants to merge the first name, middle name, and last name of the customers in one column, so to perform this operation we need to use the derived column task. Now let’s go on the Data Flow Task in SSIS and perform the below steps.

Step 1. Create OLE DB Source connection – we will add OLE DB Source and Derived Column Task in the Data Flow Task and establish a source connection. We can see this in the screenshot below.

Step 2. Derived Column Transformation –To create new column values, we perform derived column transformation by applying expressions. We will go on Derived Column Editor to add derived column names and specify expressions to create new column values.

Step 3. Now click Ok and Create an OLE DB Destination connection – We will establish a new OLE DB Destination connection in the editor to push new data. So here will give the connection manager name and the new table name as CustomerNameDerivedCol.

Step 4. Now hit ok and go to Mappings to see available input columns and available destination columns, here you can notice that the same column name is mapped.

Step 5. Now the destination connection is established and the SSIS package is executed successfully.

Step 6. Now let’s verify this in SQL Server Management Studio (SSMS). In the below screenshot at the end of the result, we can see the CustomerName column. That’s how we derive a new column.

Summary
In this article, you have learned how to create the derived column in SSIS, hope you liked it. Looking forward to your comments and suggestions in the section below.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In SQL Server, a transaction is a sequential set of actions (such statements or queries) carried out as a single task. Data in a database may be read, written, updated, or deleted throughout these activities. Transactions that adhere to the ACID characteristics guarantee data integrity.

• Atomicity: Ensures that a transaction is treated as a single unit. Either all operations in the transaction are completed successfully, or none are applied, maintaining the "all-or-nothing" principle.
• Consistency: Guarantees that a transaction transforms the database from one valid state to another, adhering to all defined rules, constraints, and relationships.
• Isolation: Ensures that concurrent transactions do not interfere with each other, maintaining data integrity as if transactions were executed sequentially.
• Durability: Ensures that once a transaction is committed, its changes are permanently recorded in the database, even in the event of a system failure.

Transaction Control
The following are the commands used to control transactions.

• BEGIN TRANSACTION: Marks the start of a transaction. All subsequent operations will be part of this transaction until it is committed or rolled back.
• COMMIT: Saves all the changes made during the transaction permanently to the database. Once committed, the changes cannot be undone.
• ROLLBACK: Reverts all changes made during the transaction to their state at the start of the transaction, effectively canceling the transaction.
• SAVEPOINT: Creates a checkpoint within a transaction. This allows rolling back a transaction to a specific point without undoing the entire transaction.
• RELEASE SAVEPOINT: Deletes a previously defined SAVEPOINT. Once released, SAVEPOINT can no longer be used for rollback.
• SET TRANSACTION: Configures a transaction with specific properties, such as setting it to read-only or read/write, or associating it with a specific rollback segment.

Types of Transactions

• Implicit Transactions
  
  • Automatically initiated by the database system when specific commands (e.g., INSERT, DELETE, UPDATE) are executed.
  • The transaction remains active until explicitly committed or rolled back by the user.
• Explicit Transactions
  
  • Manually initiated and controlled by the user.
  • Typically defined using BEGIN TRANSACTION, followed by COMMIT or ROLLBACK to either save or undo changes.
• Autocommit Transactions
  • The default transaction mode in most SQL systems.
  • Each individual SQL statement is automatically committed if it executes successfully. No explicit commands are needed to commit or rollback.
• Savepoints
  • Checkpoints within a transaction that allow partial rollbacks.
  • Useful for rolling back a specific part of a transaction without undoing the entire

Basic Transaction Syntax
Explicit Transaction Example
BEGIN TRANSACTION;

-- Deduct from one account
UPDATE EmpSalary_int
SET Salary = Salary - 100
WHERE EmpID = 1;

-- Add to another account
UPDATE EmpSalary_int
SET Salary = Salary + 100
WHERE EmpID = 2;

-- Commit the transaction
COMMIT;

Using ROLLBACK
BEGIN TRANSACTION;

UPDATE products
SET stock_quantity = stock_quantity - 10
WHERE product_id = 5;

-- Simulating an error
IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    PRINT 'Transaction failed and was rolled back.';
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction completed successfully.';
END

Failed transaction

Savepoints for Partial Rollbacks
BEGIN TRANSACTION;

-- Step 1
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (101, 1, GETDATE());

SAVE TRANSACTION SavePoint1;

-- Step 2
INSERT INTO order_details (order_id, product_id, quantity)
VALUES (101, 2, 5);

-- Rollback to SavePoint1 if needed
ROLLBACK TRANSACTION SavePoint1;

-- Commit remaining operations
COMMIT;

Here, we can see in the second table that order_details data is not saved because we have set rollback savepoint1.

TRY...CATCH Example
BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE EmpSalary_int
    SET Salary = Salary - 100
    WHERE EmpID = 1;

    UPDATE EmpSalary_int
    SET Salary = Salary + 'null'
    WHERE EmpID= 2;

    COMMIT;
    PRINT 'Transaction completed successfully.';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'An error occurred. Transaction rolled back.';
END CATCH;

Failed transaction

Conclusion
In SQL, transactions are sequences of operations performed as a single logical unit of work, ensuring data consistency and integrity. A transaction follows the ACID properties: Atomicity (all-or-nothing execution), Consistency (ensures data validity), Isolation (independence of concurrent transactions), and Durability (changes persist after completion). Transactions are crucial for managing database operations reliably and are typically controlled with commands like BEGIN, COMMIT, and ROLLBACK.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, we will learn how to create derived columns. Derived column task in SSIS is used to,

• Create new column
• Update existing columns
• Merge different columns into one column. For example, businesses want to concatenate first name, middle name, and last name to make it a full name.

Now let’s understand this with an example. We will use the database, which has already been loaded into the SQL server, to create a derived column function. Now let’s go to SSMS and see the table how it looks right now.

So here let’s say, the business wants to merge the first name, middle name, and last name of the customers in one column, so to perform this operation we need to use the derived column task. Now let’s go on the Data Flow Task in SSIS and perform the below steps.

Step 1. Create OLE DB Source connection – we will add OLE DB Source and Derived Column Task in the Data Flow Task and establish a source connection. We can see this in the screenshot below.

Step 2. Derived Column Transformation –To create new column values, we perform derived column transformation by applying expressions. We will go on Derived Column Editor to add derived column names and specify expressions to create new column values.

Step 3. Now click Ok and Create an OLE DB Destination connection – We will establish a new OLE DB Destination connection in the editor to push new data. So here will give the connection manager name and the new table name as CustomerNameDerivedCol.

Step 4. Now hit ok and go to Mappings to see available input columns and available destination columns, here you can notice that the same column name is mapped.

Step 5. Now the destination connection is established and the SSIS package is executed successfully.

Step 6. Now let’s verify this in SQL Server Management Studio (SSMS). In the below screenshot at the end of the result, we can see the CustomerName column. That’s how we derive a new column.

Summary
In this article, you have learned how to create the derived column in SSIS, hope you liked it.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.