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.