In this article, we will learn how to create a database table in SQL Server using the SSIS package.

As we know, data is growing massively, and we need to have the capability to extract data from numerous sources such as Oracle, Excel files, and SQL Server databases. SSIS provides integration and workflow solutions through which we can connect with multiple sources. We can build inflow and outflow of data and build pipelines through SSIS packages.

In order to work on SSIS, you need to install Sql Server Data Tools (SSDT) and the Integration Service component. I have used the SSDT 2022 version.

Now, let’s explore more and learn how we can create a table in SQL Server using the SSIS package.

We need to follow below steps:
Step 1. Firstly, create a new SSIS package by right-clicking in Solution Explorer.  A new SSIS Package1.dtsx is created, which we can see in the snippet below.

Step 2. Now go to the SSIS toolbox, select Execute SQL Task, and drag and drop it to the new SSIS package. Below is the snippet.

Step 3. Now, establish a new connection and make sure to test the connection.

Step 4. Now consider the script below and put this in the SQLStatement section in the Task Editor.
create table employee_details
  (emp_id int,
   emp_name varchar(50),
   salary int)

  insert into employee_details values(101,'Christina','90000')
  insert into employee_details values(102,'Aman','40000')
  insert into employee_details values(103,'James','45000')
  insert into employee_details values(104,'Jack','80000')
  insert into employee_details values(105,'Jamal','87000')
  insert into employee_details values(106,'Lisa','82000')
  insert into employee_details values(107,'Karan','45000')

After establishing the connection and entering the script, we can see below the red mark is gone.

Step 5. Now, execute the package by hitting the start button.

We can see in the above screenshot that the SQL task was executed successfully.

Step 6. Now, we will see in the SQL Server using SSMS that the employee_details table is created. Below is the screenshot.

Summary
You have learned today how to create a new table using the SSIS task. The benefit of using SSIS is automation since we can run packages either on-demand or using the scheduler. We can further extend this with the help of project parameters. I hope you liked the article. Please let me know your feedback/ suggestions in the comments 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.

SQLite is a file-based relational database management system that is lightweight and ideal for small Web applications, development and testing, and mobile applications such as iOS and Android apps. It is a serverless, self-contained database that works nicely with.NET applications.

To use SQLite in your.NET application, install the "System.Data.SQLite" package from Nuget or run the command below in the terminal.
Install-Package System.Data.SQLite

SQLiteConnection class is used to make connection strings, create SQLite DB files, handle queries, and manage connections from the SQLite database engine. Here, we will take the Create, update, read, delete (CURD) operation example code with C#.
using System;
using System.Data.SQLite;

class Program
{
    static void Main()
    {
        // Create a new database file
        SQLiteConnection.CreateFile("ExampleDatabase.sqlite");

        // Connect to the database
        using (var connection = new SQLiteConnection("Data Source=ExampleDatabase.sqlite;Version=3;"))
        {
            connection.Open();

            // Create a table
            string createTableQuery = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Name TEXT, Age INTEGER)";
            using (var command = new SQLiteCommand(createTableQuery, connection))
            {
                command.ExecuteNonQuery();
            }

            // Insert data
            string insertQuery = "INSERT INTO Users (Name, Age) VALUES ('aaa', 15)";
            using (var command = new SQLiteCommand(insertQuery, connection))
            {
                command.ExecuteNonQuery();
            }

            // Read data
            string selectQuery = "SELECT * FROM Users";
            using (var command = new SQLiteCommand(selectQuery, connection))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"Id: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]}");
                }
            }

            // Update data
            string updateQuery = "UPDATE Users SET Age = 31 WHERE Name = 'Alice'";
            using (var command = new SQLiteCommand(updateQuery, connection))
            {
                command.ExecuteNonQuery();
            }

            // Delete data
            string deleteQuery = "DELETE FROM Users WHERE Name = 'Alice'";
            using (var command = new SQLiteCommand(deleteQuery, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }
}

Once after executing the code, the SQLite file will be created in the build location. Using online SQLite Viewer SQLite database and table able to view in this Link

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.

Managing 100 columns in a table is difficult in real-time projects; in these cases, we must add JSON to the SQL table columns and then manage them.

Example
Suppose we have some Application settings that need to be set up from the database, and it has very complex settings that keep changing so instead of creating multiple columns for those settings, we can club all settings or configurations into one JSON format and save them to a single Sql table column.

SQL Server provides support for handling JSON data, allowing you to store JSON in columns and perform operations on it. This can be particularly useful when dealing with semi-structured or unstructured data. Here are some examples to illustrate how to read JSON from SQL Server columns.
To handle JSON data in SQL Server

    Store JSON in NVARCHAR(MAX) or VARCHAR(MAX) columns.
    Insert JSON using regular INSERT statements.
    Read and Parse JSON using JSON_VALUE, JSON_QUERY, and OPENJSON:
        JSON_VALUE: Extracts a scalar value from JSON.
        JSON_QUERY: Extracts an object or array.
        OPENJSON: Parses JSON and returns a table of key-value pairs.
    Modify JSON using JSON_MODIFY to update or add properties.
    Aggregate Data into JSON using the FOR JSON clause.

Storing JSON Data
JSON data is typically stored in columns of type NVARCHAR(MAX) or VARCHAR(MAX).
Create table
CREATE TABLE ProductsData (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(50),
    ProductDetails NVARCHAR(MAX) -- JSON data stored in this column
);

Insert JSON on ProductDetails column
INSERT INTO ProductsData (ProductID, ProductName, ProductDetails)
VALUES
    (1, 'Laptop',
        '{
            "Brand": "Dell",
            "Specifications": {
                "Processor": "Intel i7",
                "RAM": "16GB",
                "Storage": "512GB SSD"
            }
        }'),
    (2, 'Smartphone',
        '{
            "Brand": "Apple",
            "Specifications": {
                "Model": "iPhone 12",
                "Storage": "128GB"
            }
        }');

Simple Select
SELECT
    ProductID,
    ProductName,
    ProductDetails
FROM
    ProductsData;

Reading JSON Data
You can read JSON data from the table and parse it using built-in functions such as JSON_VALUE, JSON_QUERY, and OPENJSON.Read SQL JSON ColumnSELECT
    ProductID,
    ProductName,
    JSON_VALUE(ProductDetails, '$.Brand') AS Brand,
    JSON_VALUE(ProductDetails, '$.Specifications.Processor') AS Processor
FROM
    ProductsData;

Extract Nested Objects or Arrays with JSON_QUERY
SELECT
    ProductID,
    ProductName,
    JSON_QUERY(ProductDetails, '$.Specifications') AS Specifications
FROM
    Products;

Expand JSON Arrays with OPENJSON

SQL
-- Insert data with JSON array
INSERT INTO ProductsData (ProductID, ProductName, ProductDetails)
VALUES
(3, 'Tablet',
'{
    "Brand": "Samsung",
    "Models": [
        {"Model": "Galaxy Tab S7", "Storage": "256GB"},
        {"Model": "Galaxy Tab S6", "Storage": "128GB"}
    ]
}');

-- Select data and expand JSON array
SELECT
    ProductID,
    ProductName,
    Models.Model,
    Models.Storage
FROM
    ProductsData
CROSS APPLY OPENJSON(ProductDetails, '$.Models')
WITH (
    Model NVARCHAR(50) '$.Model',
    Storage NVARCHAR(50) '$.Storage'
) AS Models;

Aggregating Data into JSON Format
SQL Server allows you to convert query results into JSON format using the FOR JSON clause.

SQL
SELECT
    ProductID,
    ProductName,
    ProductDetails
FROM
    ProductsData
FOR JSON PATH;

Conclusion
We have learned ways to read JSON from SQL Server columns. Thanks!

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.

The SEQUENCE object in SQL Server is an effective tool for producing distinct numerical numbers in a predetermined order. It was first included in SQL Server 2012 to offer features akin to those of IDENTITY columns with more adaptability. Because SEQUENCE is table-independent, developers can utilize it in a variety of scenarios or tables. The fundamentals of SEQUENCE will be covered in this article, along with comparisons to other options like IDENTITY and a fresh real-world example to show how to use it.

What is SEQUENCE in SQL Server?
A SEQUENCE is a user-defined schema-bound object that generates a sequence of numeric values. Unlike IDENTITY, which is tied to a specific table column, SEQUENCE exists independently and can be used across multiple tables or queries.

Key Features of SEQUENCE

• Independent Object: Not tied to a single table or column.
• Customizable: Allows control over the starting value, increment step, minimum, maximum, and cycle behavior.
• Reusable: Can be used in multiple tables or even in calculations.
• Flexible Usage: Values can be retrieved using the NEXT VALUE FOR function.

Syntax
CREATE SEQUENCE schema_name.sequence_name
    AS data_type
    START WITH <initial_value>
    INCREMENT BY <step>
    [MINVALUE <min_value>]
    [MAXVALUE <max_value>]
    [CYCLE | NO CYCLE]
    [CACHE <cache_size> | NO CACHE]

-- START WITH: Specifies the starting value.
-- INCREMENT BY: Specifies the increment between each value (positive or negative).
-- MINVALUE/MAXVALUE: Specifies the minimum and maximum allowed values.
-- CYCLE: Restarts the sequence when it reaches the maximum or minimum value.
-- CACHE: Improves performance by caching sequence values in memory.

Example
We will create a use case for managing order numbers in an e-commerce system. The goal is to assign unique order IDs to transactions using a SEQUENCE object.

Step 1. We will create a SEQUENCE object named order_sequence to generate unique order IDs starting from 1000 and incrementing by 10.
USE Hostforlife
GO

CREATE SEQUENCE dbo.order_sequence
    AS INT
    START WITH 1000
    INCREMENT BY 10

Step 2. Next, we will create a table customer_orders to store customer order details. The order_id column will use the SEQUENCE object to generate unique IDs automatically.
USE Hostforlife
GO

CREATE TABLE dbo.customer_orders (
    order_id INT NOT NULL DEFAULT (NEXT VALUE FOR order_sequence),
    customer_name VARCHAR(100) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    order_date DATE DEFAULT GETDATE()
)

Step 3. Insert a few sample records into the customer_orders table. The order_id column will automatically get its value from the SEQUENCE object.
USE Hostforlife
GO

INSERT INTO dbo.customer_orders (customer_name, product_name)
VALUES
    ('Peter', 'Smartphone'),
    ('leon', 'Laptop'),
    ('Michael', 'Tablet');

Step 4. Retrieve the data to see the order_id values generated by the SEQUENCE.
USE Hostforlife
GO

SELECT * FROM dbo.customer_orders

Step 5. Use the sys.sequences catalog view to check the properties of the SEQUENCE object. This query will provide details such as the current value, increment, and start value of the SEQUENCE.

SELECT * FROM sys.sequences WHERE name = 'order_sequence';

Output

Step 6. When more records are inserted, the SEQUENCE continues generating unique values.
USE Hostforlife
GO

INSERT INTO customer_orders (customer_name, product_name)
VALUES ('John', 'Headphones');

Advantages

• Greater Control: SEQUENCE provides more control compared to IDENTITY, such as restarting, cycling, and specifying custom increments.
• Reusability: It can be used across multiple tables or in ad hoc queries.
• Predictability: Developers can predict the next value without inserting a record (unlike IDENTITY).
• Performance: Caching values improve performance for high-volume applications.

SEQUENCE vs IDENTITY

Conclusion
The SEQUENCE object in SQL Server is a versatile tool for generating sequential numbers, offering greater flexibility and control than IDENTITY. Whether you're building a multi-table system or need precise control over numbering, SEQUENCE is a valuable addition to your SQL Server toolkit. By leveraging SEQUENCE, you can design robust, scalable, and reusable numbering systems tailored to your application’s needs.

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.

SQL Server has two types function system or built-in function and user-defined function. In today’s article, we shall cover User Defined Functions (UDF). UDFs are the custom functions that developers need to create. SQL Server supports two types of User-Defined Functions.

• Table-Valued Functions
• Scalar Valued Functions

Now, let’s look into how to create them. To create User-Defined Functions, let’s consider the table.

Table-Valued Function
When we run a function and return a table as a result, then it’s called a table-valued function.
CREATE FUNCTION [dbo].[Function_Name]()
RETURNS TABLE
AS
RETURN (
    Statement
)

Now, let's create a Table-Valued function.
CREATE FUNCTION [dbo].[udf_ProductDetailsQuantitywise] (@product_quantity INT)
RETURNS TABLE
AS
RETURN (
    SELECT *
    FROM Products
    WHERE product_quantity >= @product_quantity
);

Below is the Output.

As we can see in the above screenshot, the Table-Valued function is created, we took input parameter as @product_quantity which will return table as a result. Now, let’s call the function [dbo].[udf_ProductDetailsQuantitywise] and pass the value as 12 for the input parameter.
SELECT * FROM [dbo].[udf_ProductDetailsQuantitywise] (12)

We can see [dbo].[udf_ProductDetailsQuantitywise] function is used to get the quantity-wise product table. Hence, the table-valued function is useful for returning the table based on business logic.

Scaler Valued Function
Unlike the table-valued function, this function returns a single scaler value based on the input parameter we passed.
CREATE FUNCTION [dbo].[Function_Name] (ParameterList)
RETURNS DataType
AS
BEGIN
    RETURN Return_DataType
END

Now, let's create a Scaler-Valued function.
CREATE FUNCTION [dbo].[udf_ProductPriceTotal]
    (@unit_price INT, @product_quantity INT)
RETURNS INT
AS
BEGIN
    RETURN @unit_price * @product_quantity
END

Below is the Output.

As we can see in the above screenshot, scaler-valued function is created, it took two input parameters @unit_price and @product_quantity which will return a single scaler value as a result.

Now, let’s call the function using [dbo].[udf_ProductPriceTotal].
SELECT
    product_name,
    unit_price,
    product_quantity,
    [dbo].[udf_ProductPriceTotal](unit_price, product_quantity) AS TotalPrice
FROM Products;

We can see the udf_ProductPriceTotal function is used to get the total price of each product in the table. Hence, the scaler function is useful for returning the single scaler value.

Summary
UDFs are a great way to customize the function based on specific needs. In the article, we covered how to create them by taking simple examples. I hope you liked the article, and I am looking forward to your comments and suggestions.

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.

One of the most popular relational database management systems (RDBMS), SQL Server, has several strong features. The SELECT statement is one of the most important and commonly used commands among them. It is the foundation of the majority of SQL operations since it enables users to get data from a database. This article will examine SQL Server's SELECT capability, including its fundamental syntax, more complex applications, and some best practices for maximizing its use.

We demonstrate the results of these claims using a sample Employee table to help put this idea into concrete form.

SELECT Statement
To fetch all records from the Employees table.
SELECT * FROM Employees;

In this query

• SELECT * tells SQL Server to return all columns.
• FROM Employees specifies the table from which to retrieve the data.

While using * to select all columns can be convenient, it is generally better practice to specify the exact columns you need to retrieve. This reduces unnecessary data retrieval and improves performance, especially when dealing with large tables. At its core, the SELECT statement allows you to query data from one or more tables. The basic syntax for a SELECT query looks like this.

SELECT FirstName, LastName FROM Employees;

Filtering Data with the WHERE Clause
The real power of SELECT comes when you start using the WHERE clause to filter the data returned. This allows you to specify conditions that must be met for rows to be included in the result set.
SELECT FirstName, LastName FROM Employees
WHERE Department = 'Sales';

The query fetches only the employees working in the "Sales" department.

You can also use operators like =, >, <, BETWEEN, IN, LIKE, and IS NULL to create complex conditions. For example.
SELECT FirstName, LastName, Salary FROM Employees
WHERE Salary > 50000 AND Department IN ('HR', 'Sales');

This retrieves the employees in the HR or Sales departments with a salary greater than $50,000.

Sorting Data with ORDER BY
To arrange the result set in a specific order, you can use the ORDER BY clause. By default, it sorts in ascending order (A to Z, lowest to highest), but you can also use DESC to sort in descending order.
SELECT FirstName, LastName, Salary FROM Employees
ORDER BY Salary DESC;

This query returns the employees sorted by salary, from the highest to the lowest.

Aggregating Data with GROUP BY
In many cases, you'll want to group rows together based on certain columns and then perform aggregate functions like COUNT, SUM, AVG, MIN, or MAX on those groups. The GROUP BY clause allows you to do this.
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

This query returns the number of employees in each department. The COUNT(*) function counts the number of rows in each group formed by the Department column.

Using DISTINCT to Remove Duplicates
If you want to eliminate duplicate rows from your result set, you can use the DISTINCT keyword. This is particularly useful when querying data that might contain repeated values.

SELECT DISTINCT Department FROM Employees;

This query returns only the unique departments from the Employees table.

Limiting the Number of Results with TOP
When working with large datasets, it’s often useful to limit the number of rows returned by a query. SQL Server allows you to do this using the TOP keyword.
SELECT TOP 5 FirstName, LastName FROM Employees;

This query returns only the first 5 rows from the Employees table. You can also use PERCENT to return a percentage of the rows.
SELECT TOP 10 PERCENT FirstName, LastName FROM Employees;

Subqueries and Nested Queries
SQL Server allows you to nest queries within other queries. A subquery is a query that is embedded inside a larger query. Subqueries can be used in the SELECT, FROM, and WHERE clauses.
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

This query returns the names and salaries of employees who earn more than the average salary.

Best Practices for Using SELECT in SQL Server

• Be Specific with Columns: Avoid using SELECT * in production code, as it can lead to unnecessary data being returned and can negatively impact performance.
• Use Indexes Efficiently: Proper indexing on tables can significantly speed up SELECT queries, especially those with WHERE conditions or JOIN operations.
• Avoid N+1 Query Problem: When using JOIN or subqueries, ensure your queries are optimized to prevent fetching data multiple times unnecessarily.
• Limit Data Retrieval: When testing queries or working with large datasets, always limit the number of rows returned using TOP, especially if you don’t need the entire dataset.

Conclusion
The SELECT statement is a versatile and powerful feature in SQL Server that allows developers to retrieve and manipulate data effectively. By mastering its basic and advanced features, you can write efficient queries that meet your application’s needs. Whether you're filtering data, aggregating results, or joining multiple tables, understanding how to use SELECT to its full potential is crucial for any SQL Server user. By following best practices and leveraging the various clauses and functions available, you can optimize the performance of your queries and ensure that your database operations are both effective and efficient.

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.

The terms "distributed SQL" and "monolithic SQL" refer to different architectures for SQL databases, each with unique characteristics in terms of performance, scalability, resilience, and operational complexity. Let's dive into both in detail.

Monolithic SQL Databases

Monolithic SQL databases, like SQL Server and Oracle, are traditional relational databases typically designed to run on a single server or a single cluster (in a primary replica configuration).

Characteristics of Monolithic SQL Databases

• Single Node or Primary-Replica Architecture
  • The core database instance often runs on a single node (server), which handles most of the read and write operations.
  • Some monolithic databases can be configured with a primary-replica setup, where the primary server handles all writes and propagates these changes to one or more replicas. However, replicas are primarily read-only and require special configurations to handle writes.
• Vertical Scalability (Scale-Up)
  • These databases rely on scaling vertically, meaning that performance is improved by upgrading the existing server with more powerful hardware (CPU, memory, storage).
  • Vertical scaling has limits; there’s only so much you can upgrade a single machine, and it can be very costly.
• Centralized Storage
  • Data is stored centrally on a single machine or cluster. This centralized approach can make the system easier to manage and maintain, but it also introduces a single point of failure unless high availability mechanisms like clustering are in place.
• Replication and Failover
  • High availability is achieved through replication and clustering, where replicas can take over if the primary instance fails. However, failover to replicas isn’t instant and often requires downtime.
• Latency and Geographic Constraints
  • Since the database runs on a single server or data center, latency is often low for local applications but can be high for users located far from the data center. This can make monolithic databases less ideal for applications needing global reach and low latency in multiple regions.

Pros and Cons of Monolithic SQL Databases
Pros

• Simplicity: Centralized management is often easier to configure, secure, and monitor.
• ACID Transactions: Strong ACID compliance makes it reliable for applications that need consistent transactions.
• Reliability: Mature ecosystem and extensive support for enterprise use cases.

Cons

• Limited Scalability: Scaling up has physical and cost limitations.
• Single Point of Failure: Even with replication, failover can introduce downtime.
• Performance Bottlenecks: Since all operations go through a single machine, heavy workloads can create bottlenecks.
• Latency for Global Applications: Users outside the data center’s region may experience high latency, affecting the user experience.

Distributed SQL Databases
Distributed SQL databases, such as CockroachDB, Google Spanner, and YugabyteDB, are designed to operate across multiple nodes, often located in different geographic regions, in a single logical database system.

Characteristics of Distributed SQL Databases

• Distributed Nodes (Multi-Node Architecture)
  • The database consists of multiple nodes across various locations (data centers or regions). Each node can handle read and write requests, spreading the workload evenly across the cluster.
  • Nodes communicate through consensus algorithms (like Raft or Paxos) to ensure data consistency and reliability.
• Horizontal Scalability (Scale-Out)
  • Distributed SQL databases can scale horizontally by adding more nodes to the cluster, which automatically increases the capacity for reads and writes.
  • This “scale-out” architecture allows the database to handle very large workloads and datasets with minimal operational overhead.
• Data Replication and Sharding
  • Data is automatically sharded (split) across multiple nodes and replicated for high availability. If a node fails, replicas on other nodes take over, providing seamless continuity with minimal downtime.
  • Some distributed databases allow data placement across specific regions or data centers to minimize latency for specific user bases, an approach sometimes called “multi-region awareness.”
• Geographic Flexibility and Low Latency
  • By placing nodes close to where data is being accessed, distributed SQL databases can reduce latency for global applications. This is especially beneficial for applications with users in multiple regions who require quick access to data.
  • Distributed SQL databases support “geo-partitioning,” where data can be stored close to users, reducing latency and ensuring that laws and regulations (such as GDPR) about data residency are adhered to.
• High Availability and Fault Tolerance
  • Built-in mechanisms provide fault tolerance so the database continues to operate even if some nodes go offline. Automatic failover and load balancing ensure that the system is highly available.
  • Distributed SQL databases are resilient by design, as they can lose nodes and still maintain consistent data availability.

Pros and Cons of Distributed SQL Databases

Pros

• Scalability: Easily scale out by adding more nodes without downtime or costly hardware upgrades.
• High Availability: Fault tolerance and automatic failover make them resilient to failures.
• Global Low Latency: Geographically distributed nodes help reduce latency for users around the world.
• Consistency: Strong consistency models are built-in, often matching the ACID compliance found in monolithic databases.

Cons

• Complexity: Distributed databases are inherently more complex to manage and troubleshoot than centralized ones.
• Networking Costs: Communication between nodes in different locations can incur network costs and add some latency.
• Consistency Trade-offs: Though they aim to be ACID-compliant, certain configurations may need to sacrifice some consistency for availability, depending on the specific CAP requirements.

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 handle massive volumes of data in a single call, a Table-Valued Parameter (TVP) in SQL Server allows an entire table to be passed as a parameter to a stored procedure or function. When sending many rows of data from an application to SQL Server, TVPs are commonly used.

Advantages of TVPs

• Increased effectiveness: Decreases the requirement for looping and multiple calls when passing numerous rows.
• Streamlined Code: Enhances code clarity by combining multiple inserts/updates into a single batch operation.
• Enhanced Performance: Reduces trips to the server, leading to substantial performance improvements with large datasets.

To achieve this Functionality Follow the Following Steps

Step 1. In SQL Server, a User-Defined Table Type (UDTT) needs to be created to define the structure (columns and data types) of the table parameter before using a TVP.

CREATE TYPE TVPUsersDetail AS TABLE (
    UserID INT,
    UserName NVARCHAR(50) NOT NULL,
    UserAge INT NOT NULL,
    UserAddress NVARCHAR(255)
);

Step 2. Create a physical table to store information like the one below.

CREATE TABLE UsersPrimaryTable (
    UserID INT,
    UserName NVARCHAR(50) NOT NULL,
    UserAge INT NOT NULL,
    UserAddress NVARCHAR(255)
);

Step 3. After defining the UDTT, create a stored procedure that takes a parameter of this table type.
CREATE PROCEDURE prcInsertDataIntoTVPTableData
    @MTVPUsersDetailsParam TVPUsersDetail READONLY
AS
BEGIN
    INSERT INTO TVPUsersDetail (UserID, UserName, UserAge, UserAddress)
    SELECT UserID, UserName, UserAge, UserAddress
    FROM @MTVPUsersDetailsParam;
END;

Step 4. Develop a sample application similar to the one provided below. I am utilizing WPF, but you may choose your own framework based on your specific needs.

UI View
<Window x:Class="TVPExample.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:TVPExample"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800">

    <StackPanel Margin="10">
        <!-- User ID Field -->
        <StackPanel Orientation="Horizontal" Margin="5">
            <TextBlock Text="User ID:" Width="100" VerticalAlignment="Center"/>
            <TextBox Name="UserIDTextBox" Width="200" Margin="5,0,0,0"/>
        </StackPanel>

        <!-- User Name Field -->
        <StackPanel Orientation="Horizontal" Margin="5">
            <TextBlock Text="User Name:" Width="100" VerticalAlignment="Center"/>
            <TextBox Name="UserNameTextBox" Width="200" Margin="5,0,0,0"/>
        </StackPanel>

        <!-- User Age Field -->
        <StackPanel Orientation="Horizontal" Margin="5">
            <TextBlock Text="User Age:" Width="100" VerticalAlignment="Center"/>
            <TextBox Name="UserAgeTextBox" Width="200" Margin="5,0,0,0"/>
        </StackPanel>

        <!-- User Address Field -->
        <StackPanel Orientation="Horizontal" Margin="5">
            <TextBlock Text="User Address:" Width="100" VerticalAlignment="Center"/>
            <TextBox Name="UserAddressTextBox" Width="200" Margin="5,0,0,0"/>
        </StackPanel>

        <!-- Buttons -->
        <Button Content="Add to List" Click="AddToList_Click" Margin="5"/>
        <Button Content="Submit to Database" Click="SubmitToDatabase_Click" Margin="5"/>

        <!-- DataGrid to Display Entries -->
        <DataGrid Name="UserDataGridView" Margin="5" Height="200"/>
    </StackPanel>
</Window>

Code behind Implementation
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Windows;

namespace TVPExample
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private ObservableCollection<UserDetailsModel> userCollection = new ObservableCollection<UserDetailsModel>();

        public MainWindow()
        {
            InitializeComponent();
            UserDataGridView.ItemsSource = userCollection;
        }

        string connectionString = @"Server=DESKTOP-JNM9BF1\SANJAYSERVER;Database=Demo;User Id=sa;Password=1234;"; // Change this connection string with your own

        private void AddToList_Click(object sender, RoutedEventArgs e)
        {
            if (int.TryParse(UserIDTextBox.Text, out int userId) &&
                int.TryParse(UserAgeTextBox.Text, out int userAge))
            {
                userCollection.Add(new UserDetailsModel
                {
                    UserID = userId,
                    UserName = UserNameTextBox.Text,
                    UserAge = userAge,
                    UserAddress = UserAddressTextBox.Text
                });

                // Clear input fields
                UserIDTextBox.Clear();
                UserNameTextBox.Clear();
                UserAgeTextBox.Clear();
                UserAddressTextBox.Clear();
            }
        }

        private void SubmitToDatabase_Click(object sender, RoutedEventArgs e)
        {
            var dataTable = new DataTable();
            dataTable.Columns.Add("UserID", typeof(int));
            dataTable.Columns.Add("UserName", typeof(string));
            dataTable.Columns.Add("UserAge", typeof(int));
            dataTable.Columns.Add("UserAddress", typeof(string));

            foreach (var user in userCollection)
            {
                dataTable.Rows.Add(user.UserID, user.UserName, user.UserAge, user.UserAddress);
            }

            InsertDataToDatabase(dataTable);
        }

        private void InsertDataToDatabase(DataTable dataTable)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                using (var command = new SqlCommand("prcInsertDataIntoTVPTableData", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    var parameter = command.Parameters.AddWithValue("@MTVPUsersDetailsParam", dataTable);
                    parameter.SqlDbType = SqlDbType.Structured;

                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}

Model class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TVPExample
{
    internal class UserDetailsModel
    {
        public int UserID { get; set; }
        public string UserName { get; set; }
        public int UserAge { get; set; }
        public string UserAddress { get; set; }
    }
}

Application View

Explanation of the above Code

• Data Binding: UserDataGridView.ItemsSource = userCollection; binds the ObservableCollection<UserDetailsModel> to the DataGrid, making it easy to visualize added entries.
• Add to List: The AddToList_Click event reads values from TextBox inputs, converts them to the appropriate types, and adds a new UserDetailsModel instance to userCollection.After adding, it clears the TextBox fields.
• Submit to Database: The SubmitToDatabase_Click event creates a DataTable that matches the structure of the UserDetailsModel.It populates the DataTable from userCollection and sends it to SQL Server using a Table-Valued Parameter.
• Stored Procedure in SQL Server: The SQL Server stored procedure prcInsertDataIntoTVPTableData should be configured to accept a TVP with the structure of UserDetailsModel.

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.

We will discuss the definition of STRING_SPLIT(), its operation, and the new ordinal feature in SQL Server 2022 in this post.

String_Split()
In SQL Server 2016, STRING_SPLIT() was initially offered as a method for dividing a delimited string into rows. When working with data stored in a delimited format, such comma-separated values (CSV), this function is really useful. In the past, developers had to divide strings manually or using user-defined functions, which were frequently laborious and inefficient.

syntax
STRING_SPLIT (string , separator);
--string: The string to be split.
--separator: The character used to separate the string into parts

Example
SELECT value
FROM STRING_SPLIT('STRING_SPLIT,New,SQL,Server,2022,Enhancement', ',')

Output

While this functionality has been useful, one limitation was the lack of an ordinal position for the split elements. In SQL Server 2016–2019, the function returned only the values from the split operation but did not indicate the position (or index) of each value within the original string.

New Ordinal Enhancement in SQL Server 2022
In SQL Server 2022, the STRING_SPLIT() function now includes an optional ordinal parameter, which allows you to retrieve not only the split values but also the original position (or index) of each value within the string. This enhancement is particularly useful when the order of the elements matters, such as when splitting CSV data that maps to specific columns or when processing structured text data.

Syntax
STRING_SPLIT (string , separator [,enable_ordinal]);
--enable_ordinal: A bit flag (0 or 1) that specifies whether to include the ordinal column in the output.
--When set to 1, the result set includes both the value and the ordinal position of each element in the string.

Example
SELECT value, ordinal
FROM STRING_SPLIT('STRING_SPLIT,New,SQL,Server,2022,Enhancement', ',', 1)

Output

In this example, the addition of the ordinal column shows the position of each split element in the original string. This enhancement simplifies working with ordered data, where the sequence is important for further processing.

Why the Ordinal Enhancement Matters?
For SQL Developers, data engineers, and DBAs, the ordinal enhancement in STRING_SPLIT() offers several key benefits.

• Improved Data Integrity and Validation: When working with structured data in delimited formats, such as CSV files, maintaining the correct sequence is crucial. By using the ordinal feature, you can ensure that the split data retains its original order, which is particularly useful when validating the contents of a file against expected formats.
• Enhanced Data Parsing and Mapping: Many applications require data parsing and mapping to different columns or formats. For example, a CSV line might represent a row in a database table, where each column has a specific order. The ordinal feature makes it easier to split the string and map the resulting values to the correct columns.
• Simplification of Existing Queries: Before SQL Server 2022, developers had to write additional code to maintain the order of split data. This often involved creating complex logic or using additional functions like ROW_NUMBER() or CROSS APPLY to simulate the ordinal behavior. Now, with the built-in ordinal feature, such queries are more straightforward, reducing code complexity and improving performance.
• Improved Performance: The native implementation of STRING_SPLIT() with an ordinal column is optimized for performance. By reducing the need for additional functions or joins to maintain the order of split elements, the overall query execution is faster, especially when working with large datasets.

Conclusion
The introduction of the ordinal enhancement in STRING_SPLIT() in SQL Server 2022 is a significant improvement that simplifies and optimizes many common string manipulation tasks. By providing the ability to retain the original order of split elements, SQL developers, data engineers, and DBAs can now write cleaner, more efficient code when dealing with delimited data. The new ordinal feature can be particularly useful in scenarios such as data parsing, CSV file processing, and structured data validation. With this enhancement, SQL Server 2022 continues to build on its reputation as a powerful and flexible platform for handling a wide variety of data management tasks.

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.

I'll go over several techniques in this post for removing duplicate rows from a SQL Server table. The issues listed below will be covered in detail in this article.

• Introduction
• Various methods to remove duplicate rows from a table in SQL Server
• Points to Remember
• Conclusion

First of all, we are going to create a new database using the SQL Server. You can still do this step if you already have an existing database.

Create a new Database
The following SQL query creates a new database and a table. Copy this query and execute it in Query Explorer or the command line.
-- Execute the following query to create the database...
IF (DB_ID('Hostforlife_DeleteDuplicateRows') IS NOT NULL)
BEGIN
    USE master
    PRINT 'Database exists'
    DROP DATABASE Hostforlife_DeleteDuplicateRows
    PRINT 'Database Dropped...'
END
GO
CREATE DATABASE Hostforlife_DeleteDuplicateRows
PRINT 'New Database ''Hostforlife_DeleteDuplicateRows'' Created'
GO
USE [Hostforlife_DeleteDuplicateRows]
GO
-- Employee Table
CREATE TABLE [dbo].[Employee] (
    EmployeeID INT IDENTITY(31100, 1),
    EmployerID BIGINT NOT NULL DEFAULT 228866,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    DepartmentID VARCHAR(100) NOT NULL,
    Age INT NOT NULL,
    GrossSalary BIGINT NOT NULL,
    PerformanceBonus BIGINT,
    ContactNo VARCHAR(25),
    PRIMARY KEY (EmployeeID)
);

Next, you can insert data to the tables using the SQL INSERT statement or by adding data directly to the tables in SSMS.

Let's check our table using the following query.

To get the data from the "Employee" table, use the following query.
SELECT * FROM Hostforlife_DeleteDuplicateRows..Employee

3 Ways to Delete Duplicate Rows From A Table In SQL Server
Here are 3 common methods that you can use to delete duplicate records from a table In SQL Server.

Method 1. Using GROUP BY and Having Clause
In this method, the SQL GROUP BY clause is used to identify and remove duplicate rows from a table.

Syntax
DELETE FROM <Table_Name>
WHERE ID NOT IN
(
    SELECT MAX(ID) AS MaxRecordID
    FROM <Table_Name>
    GROUP BY column1, columns2, ...
);

Example
DELETE FROM [Employee]
    WHERE EmployeeID NOT IN
    (
        SELECT MAX(EmployeeID) AS MaxRecordID
        FROM [Employee]
        GROUP BY [EmployerID], [FirstName], [LastName], [Email], [DepartmentID], [Age], [GrossSalary], [PerformanceBonus], [ContactNo]
    );

To verify the deletion, use the following query.

SELECT * FROM Hostforlife_DeleteDuplicateRows..Employee

Method 2. Using CTE (Common Table Expression)
CTE (Common Table Expression) can also be used to remove duplicate rows from a table in SQL Server.

Syntax
WITH CTE AS (
    SELECT
        column1,
        column2,
        ...
        ROW_NUMBER() OVER (
            PARTITION BY column1, column2, ...
            ORDER BY column1, column2, ...
        ) AS RowNumber
    FROM
        <Table_Name>
)
DELETE FROM CTE
WHERE RowNumber > 1;

Example
WITH CTE AS
(
    SELECT
        [EmployeeID],
        [EmployerID],
        [FirstName],
        [LastName],
        [Email],
        [DepartmentID],
        [Age],
        [GrossSalary],
        [PerformanceBonus],
        [ContactNo],
        ROW_NUMBER() OVER (
            PARTITION BY [EmployerID], [FirstName], [LastName], [Email], [DepartmentID], [Age], [GrossSalary], [PerformanceBonus], [ContactNo]
            ORDER BY [EmployerID], [FirstName], [LastName], [Email], [DepartmentID], [Age], [GrossSalary], [PerformanceBonus], [ContactNo]
        ) AS RowNumber
    FROM
        Employee
)
DELETE FROM CTE
WHERE RowNumber > 1;

To verify the deletion, use the following query.
SELECT * FROM Hostforlife_DeleteDuplicateRows..Employee

Method 3. Using Rank Function
The RANK function with PARTITION BY can also be used to remove duplicate rows from a table in SQL Server.

Syntax
DELETE E
    FROM <Table_Name> E
      INNER JOIN
    (
     SELECT *,
            RANK() OVER(PARTITION BY column1, column2, ...
            ORDER BY ID) rank
     FROM <Table_Name>
    ) T ON E.ID = t.ID
    WHERE rank > 1;

Example
DELETE E
FROM [Employee] E
INNER JOIN
(
    SELECT *,
           RANK() OVER (
               PARTITION BY [EmployerID], [FirstName], [LastName], [Email], [DepartmentID], [Age], [GrossSalary], [PerformanceBonus], [ContactNo]
               ORDER BY [EmployeeID]
           ) AS rank
    FROM [Employee]
) T ON E.[EmployeeID] = T.[EmployeeID]
WHERE rank > 1;

To verify the deletion, use the following query.
SELECT *
FROM Hostforlife_DeleteDuplicateRows..Employee;

Points to Remember
I would recommend you follow the points given below before deleting any type of record.

• Back up your data.
• Be sure to test your DELETE query with a SELECT statement.
• Choose an effective method as per the requirement to remove duplicate rows.

See you in the next article, till then, take care and be happy learning.

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.