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.

Microsoft has included new features and improvements to SQL Server 2022 in order to boost functionality, usability, and speed. The DATE_BUCKET function is one of these new capabilities; it's a useful tool for developers and data professionals working with time-based data.  The DATE_BUCKET function streamlines interval management and date grouping, facilitating the aggregation and analysis of time-based data over predetermined timeframes. For SQL developers, data engineers, and DBAs who regularly handle temporal data analysis, this is especially helpful.

Understanding the DATE_BUCKET Function
What is DATE_BUCKET?
The DATE_BUCKET function in SQL Server 2022 is designed to help group and truncate dates into fixed intervals (or "buckets"). This makes it easier to group data for analysis over consistent time periods such as days, weeks, months, quarters, or years. This is especially useful for reporting and data visualization when you want to group time-based data into periods like hourly or weekly aggregates.

DATE_BUCKET (datepart, number, date [, origin ] )

--datepart: The part of the date you want to group by, such as a day, week, month, etc. This can be
--number: The size of the time bucket, which must be an integer. year, month, week, day, hour, minute, second, millisecond, etc.
--date: The date to be truncated and grouped by the interval and datepart.

Return Type
The function returns a date/time value that is rounded down to the start of the specified bucket. This allows for grouping time-based data into logical intervals.

How is DATE_BUCKET Different from Other T-SQL Functions?
Other T-SQL functions, like DATEADD, DATEDIFF, and DATEPART, are typically used to manipulate dates, extract parts of dates, or compute the difference between dates. However, these functions don't natively support the concept of fixed time intervals (buckets). DATE_BUCKET, on the other hand, allows for grouping dates into regular intervals, which can be critical for generating time-based reports.

Example 1. Month Interval Example
This example groups dates into 2-month intervals, starting from January 1, 2024.
DECLARE @DateOrigin date = '2024-01-01'
SELECT
    '1/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-01-01'), @DateOrigin),
    '1/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-02-01'), @DateOrigin),
    '2/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-03-01'), @DateOrigin),
    '2/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-04-01'), @DateOrigin),
    '1/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-05-01'), @DateOrigin),
    '1/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-06-01'), @DateOrigin),
    '2/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-07-01'), @DateOrigin),
    '2/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-08-01'), @DateOrigin)
GO

Output

Example 2. Week Interval Example
This example groups dates into 2-week intervals, starting from January 1, 2024.
DECLARE @DateOrigin date = '2024-01-01'
SELECT
    '1/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-01'), @DateOrigin),
    '1/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-08'), @DateOrigin),
    '2/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-15'), @DateOrigin),
    '2/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-22'), @DateOrigin),
    '1/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-29'), @DateOrigin),
    '1/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-02-05'), @DateOrigin),
    '2/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-02-12'), @DateOrigin),
    '2/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-02-19'), @DateOrigin)
GO

Output

Example 3. Day Interval Example
This example groups dates into 2-day intervals, starting from January 1, 2022.
DECLARE @DateOrigin date = '2024-01-01'
SELECT
    '1/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-01'), @DateOrigin),
    '2/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-02'), @DateOrigin),
    '1/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-03'), @DateOrigin),
    '2/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-04'), @DateOrigin),
    '1/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-05'), @DateOrigin),
    '2/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-06'), @DateOrigin),
    '1/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-07'), @DateOrigin),
    '2/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-08'), @DateOrigin)
GO

Output

Use Cases
1. Grouping Sales Data by Weekly Buckets
Suppose we want to analyze sales orders in AdventureWorks2022 and group the orders into weekly intervals. Using DATE_BUCKET, we can easily create these weekly buckets based on the OrderDate from the Sales.SalesOrderHeader table.

In this query
DATE_BUCKET(1, WEEK, OrderDate) groups the sales orders into weekly buckets, starting from the earliest OrderDate.
The query aggregates the total number of orders and the total sales (TotalDue) within each week.

USE AdventureWorks2022
GO

SELECT
    DATE_BUCKET(WEEK, 1, OrderDate) AS OrderWeek,
    COUNT(SalesOrderID) AS TotalOrders,
    SUM(TotalDue) AS TotalSales
FROM
    Sales.SalesOrderHeader
GROUP BY
    DATE_BUCKET(WEEK, 1, OrderDate)
ORDER BY
    OrderWeek

2. Monthly Sales Data Analysis
For longer-term trends, we may want to aggregate sales on a monthly basis. DATE_BUCKET makes it simple to group the data into months, just as easily as weeks.

Example: Monthly Sales Performance
This query aggregates the sales orders into monthly intervals using DATE_BUCKET(1, MONTH, OrderDate). You can easily visualize this data in a line graph or bar chart to track monthly sales performance over time.

USE AdventureWorks2022
GO

SELECT
    DATE_BUCKET(MONTH, 1, OrderDate) AS OrderMonth,
    COUNT(SalesOrderID) AS TotalOrders,
    SUM(TotalDue) AS TotalSales
FROM
    Sales.SalesOrderHeader
GROUP BY
    DATE_BUCKET(MONTH, 1, OrderDate)
ORDER BY
    OrderMonth

Output

3. Grouping Data in Custom Intervals (e.g., 10-Day Buckets)
While DATE_BUCKET allows for standard intervals like weeks or months, you can also group dates into custom intervals. For instance, if you want to create a report based on 10-day periods instead of full months or weeks, DATE_BUCKET can handle that too.

Here, we specify an interval of 10 days, and the sales orders are grouped into periods based on that interval. This can be useful in scenarios where typical calendar boundaries like weeks or months are too coarse or too fine.

USE AdventureWorks2022
GO

SELECT
    DATE_BUCKET(DAY, 10, OrderDate) AS OrderPeriod,
    COUNT(SalesOrderID) AS TotalOrders,
    SUM(TotalDue) AS TotalSales
FROM
    Sales.SalesOrderHeader
GROUP BY
    DATE_BUCKET(DAY, 10, OrderDate)
ORDER BY
    OrderPeriod

Output

Comparing DATE_BUCKET to Other Functions
1. DATEADD and DATEDIFF
In the past, SQL developers would use combinations of DATEADD and DATEDIFF to group dates into intervals. For example, you could group sales data by year with these functions:

While this method works, it is less intuitive and more cumbersome than using DATE_BUCKET, which allows for direct and flexible interval grouping.
USE AdventureWorks2022
GO

SELECT
    DATEADD(YEAR, DATEDIFF(YEAR, 0, OrderDate), 0) AS OrderYear,
    COUNT(SalesOrderID) AS TotalOrders,
    SUM(TotalDue) AS TotalSales
FROM
    Sales.SalesOrderHeader
GROUP BY
    DATEADD(YEAR, DATEDIFF(YEAR, 0, OrderDate), 0)

Output

2. FLOOR or CEILING on Date Calculations
Another workaround for bucketing dates was using FLOOR or CEILING in conjunction with date calculations. While effective, this approach was error-prone and harder to maintain.

For example, to group dates into weekly intervals using FLOOR, you might write something like this:

This code is not as readable as using DATE_BUCKET. The DATE_BUCKET function simplifies and abstracts away the complexity, making it easier to reason about your queries.
USE AdventureWorks2022
GO

SELECT
    FLOOR(DATEDIFF(DAY, '1900-01-01', OrderDate) / 7) AS WeekNumber,
    COUNT(SalesOrderID) AS TotalOrders
FROM
    Sales.SalesOrderHeader
GROUP BY
    FLOOR(DATEDIFF(DAY, '1900-01-01', OrderDate) / 7)

Output

Benefits of Using DATE_BUCKET

1. Simplified Code
One of the most apparent benefits of DATE_BUCKET is the simplification of code when compared to older methods of date bucketing. Instead of using complex expressions with DATEADD and DATEDIFF, you can now achieve the same result with a single, readable function.

2. Flexibility and Power
DATE_BUCKET provides a powerful tool for aggregating time-based data in flexible ways. Whether you need to group data by week, month, or even custom intervals like ten days or 15 minutes, DATE_BUCKET makes it easy to express and execute these groupings.

3. Improved Performance
By natively supporting time-based intervals in a straightforward function, DATE_BUCKET improves performance over workarounds that rely on complex date manipulation functions such as combinations of DATEADD, DATEDIFF, and FLOOR. These traditional approaches often require multiple calculations and transformations to achieve similar results, which can increase both complexity and computational overhead.

Conclusion
The introduction of the DATE_BUCKET function in SQL Server 2022 marks a significant enhancement for SQL developers, data engineers, and DBAs who frequently work with time-based data. By simplifying the process of grouping dates into consistent intervals, DATE_BUCKET not only makes queries more readable and easier to maintain but also improves performance by reducing reliance on complex, manual date manipulation functions. With its ability to streamline queries, improve code maintainability, and optimize performance, DATE_BUCKET represents a valuable addition to the SQL Server toolkit, empowering professionals to better manage and analyze their time-series 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.

I'll go over the Rank and Dense_Rank Functions in SQL Servers with examples in this article. In SQL Server, the RANK and DENSE_RANK procedures were introduced. Once more, these two procedures are utilized to return consecutive numbers beginning at 1 in accordance with the ORDER BY clause's ordering of the rows. Before attempting to comprehend how these functions differ from one another, let's first examine these functions in further detail using a few instances.

Note: If there are two records with identical data, both rows will have the same rank.

RANK Function in SQL Server
The following is the syntax for using the RANK function in SQL Server. As you can see, like the Row_Number function, here also the Partition By clause is optional while the Order By Clause is mandatory.

The PARTITION BY clause is basically used to partition the result set into multiple groups. As it is optional, and if you did not specify the PARTITION BY clause, then the RANK function will treat the entire result set as a single partition or group. The ORDER BY clause is required, and this clause is used to define the sequence in which each row is going to assign its RANK i.e. number. If this is not clear at the moment, then don’t worry. We will try to understand this with some examples.

Examples to understand Rank and Dense_Rank function in SQL Server.

We are going to use the following Employees table to understand the RANK and DENSE_RANK functions.

Please use the following SQL Script to create and populate the Employees table with the required test data.
Create Table Employees
(
    Id INT PRIMARY KEY,
    Name VARCHAR(50),
    Department VARCHAR(10),
    Salary INT,
)
Go

Insert Into Employees Values (1, 'James', 'IT', 80000)
Insert Into Employees Values (2, 'Taylor', 'IT', 80000)
Insert Into Employees Values (3, 'Pamela', 'HR', 50000)
Insert Into Employees Values (4, 'Sara', 'HR', 40000)
Insert Into Employees Values (5, 'David', 'IT', 35000)
Insert Into Employees Values (6, 'Smith', 'HR', 65000)
Insert Into Employees Values (7, 'Ben', 'HR', 65000)
Insert Into Employees Values (8, 'Stokes', 'IT', 45000)
Insert Into Employees Values (9, 'Taylor', 'IT', 70000)
Insert Into Employees Values (10, 'John', 'IT', 68000)
Go

RANK Function without PARTITION
Let us see an example of the RANK function in SQL Server without using the PARTITION BY Clause. When we did not specify the PARTITION BY Clause, then the RANK function will treat the entire result set as a single partition and give consecutive numbering starting from 1 except when there is a tie.

The following is an example of the RANK function without using the PARTITION BY clause. Here we use the Order By Clause on the Salary column. So, it will give the rank based on the Salary column.

SELECT Name, Department, Salary,
RANK() OVER (ORDER BY Salary DESC) AS [Rank]
FROM Employees

Once you execute the above query, you will get the following output. As you can see in the below output, there will be no partition, and hence all the rows are assigned with consecutive sequences starting from 1 except when there is a tie i.e. when the salary is 8000 and 65000, it gives the same rank to both the rows.

The Rank function in SQL Server skips the ranking(s) when there is a tie. As you can see in the above output, Ranks 2 and 6 are skipped as there are 2 rows at rank 1 as well as 2 rows at rank 5. The third row gets rank 3 and the 7th row gets rank 7.

RANK Function with PARTITION BY clause in SQL Server

Let us see an example of the RANK function using the PARTITION BY clause in SQL Server. When you specify the PARTITION BY Clause, then the result set is partitioned based on the column that you specify in the PARTITION BY clause. Please have a look at the following image to understand this better. As you can see we have specified Department in the Partition By clause and Salary in the Order By clause.

As in the Employees table, we have two departments (IT and HR). So, the Partition By Clause will divide all the records into two partitions or two groups. One partition is for IT department employees and another partition is for HR department employees. Then in each partition, the data is sorted based on the Salary column. The RANK function then gives an integer sequence number starting from 1 to each record in each partition except when there is a tie. In the case of a tie, it gives the same rank and then skips the ranking.

Now execute the following code and you will get the output as we discussed in the previous image.
SELECT Name, Department, Salary,
               RANK() OVER (
                               PARTITION BY Department
                               ORDER BY Salary DESC) AS [Rank] FROM Employees

So, in short, The RANK function Returns an increasing unique number for each row starting from 1 and for each partition. When there are duplicates, the same rank is assigned to all the duplicate rows, but the next row after the duplicate rows will have the rank it would have been assigned if there had been no duplicates. So the RANK function skips rankings if there are duplicates.

DENSE_RANK Function in SQL Server
The following is the syntax for using the DENSE_RANK function. As you can see, like the RANK function, here also the Partition By clause is optional while the Order By Clause is mandatory.

The PARTITION BY clause is optional and it is used to partition the result set into multiple groups. If you did not specify the PARTITION BY clause, then the DENSE_RANK function will treat the entire result set as a single partition. The ORDER BY clause is mandatory and it is used to define the sequence in which each row is going to assign with their DENSE_RANK i.e. number. Let us understand how to use the DENSE_RANK function in SQL Server with some examples.
DENSE_RANK Function without PARTITION BY clause in SQL Server

Let us see an example of the DENSE_RANK function without using the PARTITION BY Clause. As we already discussed, if we did not specify the PARTITION BY Clause, then the DENSE_RANK function will treat the entire result set as a single partition and give consecutive numbering starting from 1 except when there is a tie.

The following is an example of the DENSE_RANK function without using the PARTITION BY clause. Like the RANK function, here we also apply for the Order By Clause on the Salary column. So, it will give the rank based on the Salary column.

SELECT Name, Department, Salary,
            DENSE_RANK() OVER (ORDER BY Salary DESC) AS [Rank]
FROM Employees

When you execute the above SQL Query, it will give you the following output. As you can see in the output, there will be no partition, and hence all the rows are assigned with consecutive sequences starting from 1 except when there is a tie i.e. when the salary is 8000 and 65000, it gives the same rank to both the rows.

Unlike the Rank function, the DENSE_RANK function will not skip the ranking(s) when there is a tie. As you can see in the above output, we have two rows with rank 1 and the next immediate row rank is 3 and this is the only difference between RANK and DENSE_RANK function in SQL Server.
DENSE_RANK Function with PARTITION BY clause in SQL Server

Let us see an example of the DENSE_RANK function in SQL Server using the PARTITION BY Clause. Like the RANK function, it will also partition the result set based on the column that you specify in the PARTITION BY Clause. In order to understand this better, please have a look at the following diagram. As you can see we have specified the Department column in the Partition By clause and Salary column in the Order By clause.

As we have two departments i.e. IT and HR, so, the Partition By Clause will divide all the data into two partitions. One partition is going to hold the IT department employees while the other partition is going to hold the HR department employees. Then in each partition, the records are sorted based on the Salary column. The DENSE_RANK function is then applied on each record in each partition and provides sequence numbers starting from 1 except when there is a tie. In the case of a tie, it gives the same rank without skipping the ranking.

Now execute the below SQL Script and you should get the output as we discussed in the previous image.

SELECT Name, Department, Salary,
               DENSE_RANK() OVER (
                               PARTITION BY Department
                               ORDER BY Salary DESC) AS [DenseRank]
FROM Employees

What is the difference between Rank and Dense_Rank functions in SQL Server?
As we already discussed the one and only difference is Rank function skips ranking(s) if there is a tie whereas the Dense_Rank will not skip the ranking.

The Real-time examples of RANK and DENSE_RANK Functions in SQL Server:
If you are attending any interview, then one famous question is being asked in almost all interviews i.e. find the nth highest salary. Both the RANK and DENSE_RANK functions can be used to find nth highest salary. However, when to use which function basically depends on what you want to do when there is a tie. Let us understand this with an example.

Suppose, there are 2 employees with the FIRST highest salary, then there might be 2 business cases as follows.
If your business requirement is not to produce any result for the SECOND highest salary then you have to use the RANK function.
If your business requirement is to return the next Salary after the tied rows as the SECOND highest Salary, then you have to use the DENSE_RANK function.

Fetch the 2nd Highest Salary using the RANK function
Since, in our Employees table, we have 2 employees with the FIRST highest salary (80000), the Rank() function will not return any data for the SECOND highest Salary. Please execute the SQL script below and see the output.
-- Fetch the 2nd Hight Salary
WITH EmployeeCTE  AS
(
    SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank_Salry
    FROM Employees
)

SELECT TOP 1 Salary FROM EmployeeCTE WHERE Rank_Salry = 2

Fetch the 2nd Highest Salary using DENSE_RANK Function
As we have 2 Employees with the FIRST highest salary i.e. 80000, the Dense_Rank() function will return the next Salary after the tied rows as the SECOND highest Salary i.e. 70000. Please execute the following SQL Script and see the output.
-- Fetch the 2nd Hight Salary
WITH EmployeeCTE  AS
(
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank_Salry
    FROM Employees
)

SELECT TOP 1 Salary FROM EmployeeCTE WHERE DenseRank_Salry = 2

Example to find the Highest Salary Department
You can also use the RANK and DENSE_RANK functions in SQL Server to find the nth highest Salary department-wise. For example, if someone asks you to find the 3rd highest salary of the IT Department, then you can use the DENSE_RANK function as shown below.
WITH EmployeeCTE  AS
(
    SELECT Salary, Department,
           DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC)
           AS Salary_Rank
    FROM Employees
)

SELECT TOP 1 Salary FROM EmployeeCTE WHERE Salary_Rank = 3
AND Department = 'IT'

To put it briefly, the DENSE_RANK function yields an increasing unique number for each division and row beginning at 1. If there are duplicates, all of the duplicate rows are given the same rank; no levels are skipped. This indicates that the subsequent row in the series will have the next rank after the duplicate rows.

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 will show you how to check disk space utilization in SQL Server in this article, with a particular emphasis on how to find the disk space consumed by different tables. Database administrators must keep an eye on disk space use to guarantee optimal database performance and prevent storage-related problems. Through this approach, you will be able to better plan, optimize, and manage your storage resources by knowing how much disk space each table takes up.

How Can I Check How Much Disk Space Each Table Is Using?
A SQL Server database can be checked for disk space use by table using one of two approaches.

Integrated function located in Reports (SQL Server Management Studio)
We will go over "Disk Usage By Table" in this section, but first, let's take a step-by-step look at the built-in function under reports (the report menu has a lot of reports that we may see). I've downloaded the "NorthWind" database in order to provide an example of disk utilization by table.

The total amount of space allotted to each table in the list will be shown in the report. The "Orders" table takes up the most room in the example below, followed by the "OrderDetails" table, the "Employees" table, and so on.

Using SQL Stored Procedure/Query
If you want to calculate disk space utilization by tables in an SQL Server database using a stored procedure and customize your report, you can do so with a stored procedure. I’ve created one to calculate disk space utilization by tables. Please see the stored procedure below.
CREATE PROCEDURE USP_GetDiskSpaceUsedByTables
AS
BEGIN
    SELECT
        t.name AS TableName,
        s.name AS SchemaName,
        p.rows,
        SUM(a.total_pages) * 8 AS TotalReservedSpaceKB,
        SUM(a.used_pages) * 8 AS UsedSpaceKB,
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalReservedSpaceMB,
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    FROM
        sys.tables t
    INNER JOIN
        sys.indexes i ON t.object_id = i.object_id
    INNER JOIN
        sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    INNER JOIN
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE
        t.name NOT LIKE 'dt%'
        AND t.is_ms_shipped = 0
        AND i.object_id > 255
    GROUP BY
        t.name, s.name, p.rows
    ORDER BY
        TotalReservedSpaceMB DESC, t.name
END

Let's execute this stored procedure and review the results, which are calculated in the same way as the built-in SQL report functionality.

Why Check Disk Space Utilization by Tables in a SQL Server Database?
Here are the key reasons to check disk space utilization by tables in an SQL Server database.

• Performance optimization
• Data management
• Cost management
• Compliance and auditing
• Resource allocation
• Data Archiving
• Post index maintenance

Summary
I've covered efficient ways to keep an eye on and control disk space usage in your SQL Server database in this post. I specifically walk through two methods for monitoring disk space usage at the table level. By employing these techniques, you can obtain important knowledge about how much storage is being used by your database, which will enable you to better effectively schedule your tasks. In your database environment, this proactive management may result in better resource allocation and performance.

Happy reading!

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 talk about a common problem that arises when we try to install SQL Server 2019 Developer Edition in custom mode in this article. SQL Server is typically installed by default and functions perfectly. Occasionally, nevertheless, we may run into some issues when installing the latest version. Upon installing the SQL Server Developer Edition 2019, I encountered a problem that appears to be related to a PolyBase issue.

Issue
When attempting to log in to SQL Server, an error 18456 was encountered.

Reason
Microsoft SQL Server, Error 18456 is the most common error in SQL server login. There are multiple reasons for this error. At least, we can divide them as two types, the error come from

• SQL Server authentication mode
• Windows Authentication Mode

SQL Server authentication mode
"Login failed for user'sa'. (Microsoft SQL Server, Error 18456)" refers to a specific user's login using the SQL Server authentication mode in the first two situations of our demo. One possible explanation for this scenario is that the SQL server instance is set up for Windows Authentication mode, but you are attempting to use SQL Server Authentication. This is the result of installing SQL Server Basic Mode, which is limited to operating in Windows Authentication mode and not in SQL Server Authentication mode by default.We must change the Windows Authentication Mode to the Mixed Mode in this situation.

The fix for this, I have discussed in my SQL Server installation article SQL Server Installation: 2022 Developer Edition --- Basic, or you may see [ref1], [ref2].

SQL Server authentication mode
For this, the main reason is due to the ID is not in the login list of the Server. We can get the login list by Click Login, under the security folder of the given database:

This can be obtain by SQL Command:
exec master.dbo.xp_logininfo

In different SQL Server:

We can get only one specific group with given parameter:
exec master.dbo.xp_logininfo 'NT SERVICE\Winmgmt'

Get the group members by the optional second parameter 'members':

Fix
If we have the person's ID, or the group name, then we can add it into the login list:

The name must be a valid user ID or AD group name:

Server role: by default --- public

OK

 Choose the specific data tables, and give the appreciate rights => OK

OK => Done

Summary
This login failed with SQL Server authentication is mainly due to lack of the individual ID or group the ID is belong to. Find out it and add it. The error is fixed.

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 go over some key SQL ideas in this blog and provide samples of SQL Server code for each. These ideas include index types as well as standard procedures like writing joins, working with views and triggers, and generating and modifying tables.

Index Clustering
The data rows in the table are sorted and stored by a clustered index according to the key values. There can only be one clustered index per table.

-- Create a clustered index on the 'Id' column of the 'Employee' table
CREATE CLUSTERED INDEX IX_Employee_Id
ON Employee(Id);

Non-Clustered Index
A non-clustered index stores the index structure separately from the actual table data, creating pointers to the rows.
-- Create a non-clustered index on the 'Name' column of the 'Employee' table
CREATE NONCLUSTERED INDEX IX_Employee_Name
ON Employee(Name);

Create Table
Creating a table involves defining the columns and their data types.
-- Create an 'Employee' table
CREATE TABLE Employee (
    Id INT PRIMARY KEY,
    Name NVARCHAR(50),
    DepartmentId INT,
    HireDate DATE
);

Insert Multiple Rows
Insert multiple rows into a table in one query.

-- Insert multiple rows into the 'Employee' table
INSERT INTO Employee (Id, Name, DepartmentId, HireDate)
VALUES
(1, 'Alice', 1, '2021-01-01'),
(2, 'Bob', 2, '2021-02-01'),
(3, 'Charlie', 1, '2021-03-01');

Alter Table
Modify an existing table by adding or modifying columns.
-- Add a new 'Salary' column to the 'Employee' table
ALTER TABLE Employee
ADD Salary DECIMAL(10, 2);

Update Row
Updating the data of a row in a table.

-- Update the salary of the employee with Id 1
UPDATE Employee
SET Salary = 70000
WHERE Id = 1;

Rename Table
Renaming an existing table.
-- Rename 'Employee' table to 'Staff'
EXEC sp_rename 'Employee', 'Staff';

Delete Rows
Delete specific rows from a table based on a condition.
-- Delete an employee with Id 2
DELETE FROM Employee
WHERE Id = 2;

Drop Table
Delete the entire table and all of its data.

-- Drop the 'Employee' table
DROP TABLE Employee;

Truncate Table
Remove all rows from a table without logging each row deletion.
-- Truncate the 'Employee' table
TRUNCATE TABLE Employee;

Cursor
A cursor is used to retrieve rows from a result set one at a time.
DECLARE @EmployeeId INT;
DECLARE employee_cursor CURSOR FOR
SELECT Id FROM Employee;

OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @EmployeeId;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Do something with each row
    PRINT @EmployeeId;
    FETCH NEXT FROM employee_cursor INTO @EmployeeId;
END;

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

View
A view is a virtual table based on a query.
-- Create a view to show employee names and hire dates
CREATE VIEW EmployeeView AS
SELECT Name, HireDate
FROM Employee
WHERE HireDate > '2021-01-01';

Trigger
A trigger is a special kind of stored procedure that automatically runs when an event occurs in the database.
-- Create a trigger that prints a message after inserting into the Employee table
CREATE TRIGGER trg_AfterInsertEmployee
ON Employee
AFTER INSERT
AS
BEGIN
    PRINT 'New employee inserted!';
END;

WITH CTE (Common Table Expression)
CTEs are used to create a temporary result set that can be referenced in a SELECT, INSERT, UPDATE, or DELETE statement.
WITH EmployeeCTE AS (
    SELECT Name, Salary
    FROM Employee
    WHERE Salary > 60000
)
SELECT *
FROM EmployeeCTE;

Inner Join
An inner join returns rows when there is at least one match in both tables.
-- Inner join between Employee and Department
SELECT e.Name, d.DepartmentName
FROM Employee e
INNER JOIN Department d
ON e.DepartmentId = d.Id;

Left Join
A left join returns all rows from the left table and the matched rows from the right table. Unmatched rows will return NULL for columns from the right table.
-- Left join between Employee and Department
SELECT e.Name, d.DepartmentName
FROM Employee e
LEFT JOIN Department d
ON e.DepartmentId = d.Id;

Right Join
A right join returns all rows from the right table and the matched rows from the left table. Unmatched rows will return NULL for columns from the left table.
-- Right join between Employee and Department
SELECT e.Name, d.DepartmentName
FROM Employee e
RIGHT JOIN Department d
ON e.DepartmentId = d.Id;

Self Join
A self-join is a regular join but joins the table with itself.
-- Self join on Employee table to find employees and their managers
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerId = e2.Id;

Cross Join
A cross join returns the Cartesian product of two tables, meaning every row in the left table is combined with every row in the right table.
-- Cross join between Employee and Department
SELECT e.Name, d.DepartmentName
FROM Employee e
CROSS JOIN Department d;

Cross Apply
Cross Apply works like an inner join but is used to join a table with a table-valued function.
-- Cross apply example
SELECT e.Name, sub.TopDepartment
FROM Employee e
CROSS APPLY (
    SELECT TOP 1 d.DepartmentName AS TopDepartment
    FROM Department d
    WHERE d.Id = e.DepartmentId
) sub;

ROW_NUMBER()
The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set.

-- Assign row numbers to employees based on salary
SELECT
    Name,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber
FROM
    Employee;

RANK()
The RANK() function assigns a rank to each row within a partition, with gaps in rank when there are ties.

-- Assign ranks to employees based on salary
SELECT Name,
       Salary,
       RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employee;

DENSE_RANK()
The DENSE_RANK() function assigns ranks to rows without gaps between ranks when there are ties.
-- Assign dense ranks to employees based on salary
SELECT
    Name,
    Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM
    Employee;

This concludes our overview of some essential SQL concepts and SQL Server code examples. These queries and operations form the foundation of working with relational databases, making them crucial for both beginners and advanced users alike.

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.

How does Database Mail work?
The SQL Server Database Engine may send emails using Database Mail, an enterprise email solution. Your database applications can send users emails by using Database Mail. The messages may include files from any resource on your network in addition to query results.

Step 1: In order to send mail from SQL Server, we must first install Microsoft SQL Server Management Studio (SSMS) on our PC or laptop. Thus, download and install SSMS first if you haven't already on your PC.

Step 2
Now, Open SSMS and connect it to your SQL Server instance.
Expand Management option, there you can find the Database Mail option. As you can see in the following image.

Now, Right click on Database Mail and select the Configure Database Mail option.
This will open a Database Mail Configuration wizard. Select next to continue.
Now choose the first option, Set up Database Mail, by performing the following tasks, and click next.

Now, give your database mail Profile Name and its description as whatever you want. Here, I am giving "Test Profile" as the profile name. In the SMTP account section, select the Add account option to create a new SMTP account as in the following image.

Now, enter the information shown in the following image in the New Database Mail Account box. I'm sending emails from this account using Gmail. Thus, I set the server name to smtp.gmail.com and the port number to 587.

Use the email address and password from which you wish to send emails when logging into SMTP Authentication and Outgoing Mail Server. For security reasons, confirm that the "The server requires a secure connection" check box is checked as well. When you are finished, click OK. To move on to the next screen, click next.

Step 3. The next screen is about making a Database mail profile, either public or private. You can leave this option and move to the next screen. Again, select next to finish the creation process for the Database Mail profile.

Now you are all set up to send e-mail. Now go to Management, right-click on Database Mail, and select Send Test E-mail... option to send test mail as in the following image.

Choose Test Profile as Database Mail Profile from Dropdown and enter To email address, Subject, and Body, and click Send Test E-Mail. Now your e-mail has been sent successfully from SQL Server.

Step 4. If you want to send mail using SQL query, then you can use the system database 'msdb' and its stored procedure 'sp_send_dbmail' as follows.
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Test Profile',
    @recipients = '[email protected]',
    @subject = 'Test Mail',
    @body = 'This is the test mail.',
    @body_format = 'HTML';

To check the status of the e-mail you sent, you can execute the following query in SQL Server.
USE msdb;
GO

SELECT *
FROM sysmail_allitems;

In this query result, you can find all the details about the mail sent from the SQL Server. You can check the sent_status column to check the status of mail.
So, this is all about using the database mail in the SQL Server to send e-mails. I hope you understood my explanation and liked this article. Thank you!

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.