How to Acquire Every SQL An essential part of Microsoft SQL Server is SQL Server Agent, which lets database administrators utilize jobs to automate a variety of processes like data processing, backup, and maintenance. You can arrange for these jobs to execute at predetermined times, which will simplify the management and upkeep of your SQL Server system. This post will demonstrate how to use SQL Server Management Studio (SSMS) to get a list of all SQL Server Agent jobs.

Finding Jobs for SQL Server Agents
To obtain a list of every SQL Server Agent job in SSMS, take the following actions:

Step 1: Establish a connection with SQL ServerWorks with Server Agents?
To access your SQL Server instance, open SQL Server Management Studio.

Step 2: Launch the SQL Server Agent
Open SSMS and select the "Object Explorer" window from the menu on the left. The "SQL Server Agent" node can be seen by expanding the server node. Click "SQL Server Agent" with a right-click, then choose "Jobs."

Step 3. View Job List
Once you've selected "Jobs," the right-hand pane will display a list of all the SQL Server Agent jobs configured on the SQL Server instance. The list includes the following columns:
    Job ID
    Job Name
    Owner
    Enabled (whether the job is currently enabled or not)
    Last Run Date
    Next Run Date
    Last Run Outcome

You can see an overview of the jobs, their statuses, and when they were last run, making it easier to manage your SQL Server Agent jobs.

Step 4: Job Specifics
To see additional information about a particular job, right-click on the job name and choose "Properties." This will cause a new window to open, displaying a plethora of job-related information such as the timetable, steps, and notifications.
Using Query, Retrieve SQL Server Agent Jobs

The msdb database contains information on SQL Server Agent jobs. SQL queries can be used to obtain job details, their steps, and other pertinent data. The two main tables that we will be utilizing are sysjobs and sysjobsteps. This is an extensive SQL query that retrieves different information on SQL Server Agent jobs.

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName],
    case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
     CASE
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':')
        AS [LastRunDuration]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [NextRunDateTime],
      isnull (convert (varchar,d.Date_Created), 'None') CreatedDate
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]

    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id]
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
        left outer join msdb.dbo.sysjobschedules e on e.job_id = [sJOB].job_id
        left outer join msdb.dbo.sysschedules d on e.schedule_id = d.schedule_id

The msdb.dbo.sysjobs table contains information about SQL Server Agent jobs.
The msdb.dbo.sysjobsteps table contains information about the steps within those jobs.
We use the inner join clause to link the two tables based on the job_id column.

In summary
Monitoring and controlling automated processes in a SQL Server environment requires retrieving information about SQL Server Agent jobs. With the SQL query that is provided, you may get comprehensive details on jobs and the steps that go along with them. Database administrators can maintain the functionality and health of their SQL Server instances by routinely accessing and evaluating this data. Furthermore, by utilizing this data, job-related problems can be resolved and work schedules can be optimized for increased productivity.

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 logical read
When the query engine needs to access data, it performs a logical read. It first verifies SQL Server's in-memory storage. If the relevant page is already present in SQL Server's memory, it is used. If the data page is not discovered in memory, a physical read is initiated, which results in the retrieval of the data page from the disk. A "cache hit" is a logical read without a following physical read.

For query resolution, the buffer cache, also known as the buffer pool, acts as SQL Server's primary working memory. The amount of RAM allocated to SQL Server has a direct impact on the size of the accessible buffer cache.

It is nearly impossible to provide particular recommendations without first reviewing the query, comprehending the table content, data structure, and indexing.

While a large number of logical readings is not necessarily harmful, an excessive number of logical reads is. For example, if a query returns only three rows of data but requires scanning through 200 million rows of data, the process becomes inefficient and can be improved by query optimization or the insertion of an appropriate index.

Example of a query

select *
from
(
  select *
  from Employees
  where empId = 9
)
where deptId = 1;

You can combine both criteria in a single step to improve the query and reduce logical reads. Rather of selecting all employees with empid 9 and then filtering for deptid 1, you may combine both requirements in the initial SELECT statement. This method seeks to reduce the size of the intermediate result set while increasing query efficiency.

Here's an example of how you might change the query:

SELECT *
FROM employees
WHERE empid = 9 AND deptid = 1;

When both conditions are combined in a single WHERE clause, the query engine is more likely to execute a more efficient execution plan, potentially resulting in fewer logical reads than in the two-step procedure. The efficiency of this optimization, however, is dependent on the specific database schema, indexes, and SQL Server's query optimizer.

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 maintenance of identity columns is critical in SQL databases for maintaining data integrity. After an INSERT operation, two often used functions, @@IDENTITY and SCOPE_IDENTITY(), aid in recovering recently produced identity data. Understanding the distinctions between these two is critical for database administrators and developers.

What exactly is @@IDENTITY?
@@IDENTITY is a global SQL Server variable that stores the most recent identity value generated during the current session across all tables with identity columns. While it may appear to be useful, it is crucial to realize that @@IDENTITY may not always yield the correct value. @@IDENTITY may capture the most recent identity value from any table in scenarios involving triggers or additional operations that create new tables with identity columns, potentially resulting in inaccurate results.

What exactly is SCOPE_IDENTITY()?
To alleviate the restrictions of @@IDENTITY, SQL Server provides SCOPE_IDENTITY(), a method built expressly to address identity column retrieval difficulties. SCOPE_IDENTITY() returns the most recently generated identity value within the current scope or session, making it a more dependable and accurate method of retrieving the recently inserted identity value.

Recommended Practices
When working with identity columns and needing to capture newly generated identity values after an INSERT operation, SCOPE_IDENTITY() is highly recommended. Unlike @@IDENTITY, SCOPE_IDENTITY() verifies the accuracy of the obtained identity value, avoiding potential conflicts caused by triggers or concurrent actions.

Conclusion
In conclusion, while @@IDENTITY may appear to be a convenient way to acquire the most recent identity value, its instability in certain contexts can result in unexpected results. SCOPE_IDENTITY(), on the other hand, provides a more secure and dependable method of retrieving the most recently created identity value within the current scope or session. Choosing between @@IDENTITY and SCOPE_IDENTITY() is critical for proper identity column retrieval and maintaining data consistency in SQL databases.

Understanding the difference between @@IDENTITY and SCOPE_IDENTITY() enables developers to manage identity columns with confidence and get accurate identity values, contributing to a more robust and dependable database environment.

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.

What exactly is Dynamic Data Masking?
Dynamic Data Masking (DDM) is a SQL Server security feature that helps secure sensitive data in real time by obscuring it from users who do not have the necessary access rights. It enables users to selectively mask sensitive data without changing the underlying data. This aids in data privacy and confidentiality protection in contexts where sensitive data is accessed by different people with varying levels of clearance.

This post will go over the concept of Dynamic Data Masking in SQL Server, as well as its benefits and examples.

How may Dynamic Data Masking be used?

• DDM can be used with one of four masking functions: default, email, random, or custom. These functions enable you to modify how the data is masked based on the data type and sensitivity level.
• Default Masking: Use the default masking function to mask sensitive data with a predetermined mask. For example, you can utilize the default masking mechanism to hide all but the final four digits of a credit card number.
• Email Masking: When you wish to mask an email address but keep it recognizable as an email address, you utilize the email masking function. For example, you can utilize the email masking tool to hide an email address's domain name.
• When you want to mask data with a random value, you use the random masking function. You can, for example, use the random masking function to replace a year in Date of Birth with a random value.
• Custom Masking: Use the custom masking function to build a bespoke mask for sensitive data. You can, for example, utilize the custom masking function to mask a phone number in a specific format.

How to create Masking functions?
Let's see, with an example, create a table with default, email, random, and custom masking functions and insert some records by running the following command.

--Create table with masking functions
CREATE TABLE dbo.DDM_TestTable(
    Id        INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'PARTIAL(1, "xxx", 1)') NULL,
    LastName  VARCHAR(100) NOT NULL,
    Phone     VARCHAR(10) MASKED WITH (FUNCTION = 'DEFAULT()') NULL,
    Email     VARCHAR(100) MASKED WITH (FUNCTION = 'EMAIL()') NOT NULL,
    BirthYear SMALLINT MASKED WITH (FUNCTION = 'RANDOM(1000, 9999)') NULL
    );

--Inser sample records
INSERT INTO dbo.DDM_TestTable (FirstName, LastName, Phone, Email, BirthYear)
VALUES ('Peter', 'Black', '9876543210', '[email protected]', 1982),
('Scott', 'Cassidy', '9128374650', '[email protected]', 1991),
('John', 'Lee', '9021873456', '[email protected]', 1989),
('Laura', 'M', '8907654321', '@hostforlife.eu">[email protected]', 1985);

Create a User and Grant the SELECT permission on the schema where the table resides by execution the following command.
CREATE USER DDMUser WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::dbo TO DDMUser;

Execute the query as the DDMUser to view masked data from the table.
EXECUTE AS USER = 'DDMUser'
SELECT * FROM dbo.DDM_TestTable

Benefits of Dynamic Data Masking

Organizations that need to protect sensitive data might benefit from Dynamic Data Masking in a variety of ways. Some of these advantages.

• Enhanced Security: Dynamic Data Masking shields sensitive data from unauthorized users by obscuring it. This aids in the prevention of data breaches and illegal access to critical information.
• Improved Compliance: Many firms must adhere to data privacy standards such as GDPR and HIPAA. Dynamic Data Masking assists enterprises in complying with these rules by preventing unauthorized access to sensitive data.
• Reduced danger: Dynamic Data concealing decreases the danger of data theft and other security breaches by concealing sensitive data. This helps to defend the organization's reputation and sustain customer trust.
• Increased Flexibility: Dynamic Data Masking can be used to mask data selectively based on user roles and permissions. This allows for more control over access to sensitive data.

What new DDM feature was introduced in SQL Server 2022(16.x)?
Datetime has a new masking function.
With the introduction of granular permission, we may grant or revoke UNMASK permission to a user or database role at the database, schema, table, or column level.

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 is an essential tool for managing and searching databases in today's data-driven environment. However, as our data and queries become more complicated, so does the difficulty of writing and maintaining SQL code. CTEs (Common Table Expressions) provide an elegant answer to this problem. In this blog post, we'll look at CTEs, what they are, how to use them, and why database professionals need them.

What exactly is CTE?
A named temporary result set within a SQL query is referred to as a Common Table Expression (CTE). Consider it a method of breaking down complex searches into smaller chunks. CTEs can be utilized in statements such as SELECT, INSERT, UPDATE, and DELETE. They improve your SQL code's readability and maintainability, making it easier to understand and modify.

WITH CTE_Name (Column1, Column2, ...) AS (
    -- SQL query that defines the CTE
    SELECT ...
)

The CTE can then be referenced in the next section of the query.

The Benefits of Using CTEs
There are various benefits to using CTEs in your SQL code:

• CTEs improve query readability by breaking complex queries down into more intelligible components.
• CTEs enable you to construct reusable sections of code, which simplifies maintenance and debugging.
• Potential performance gains: Some database systems have the ability to optimize queries that employ CTEs more effectively.

Let's look at how CTEs can improve code readability and maintainability.

CTEs that are not recursive
Non-recursive CTEs are used in non-recursive queries to create temporary result sets. They are excellent for simplifying complex queries in the absence of hierarchical data structures.

The CTE can then be referenced in the next section of the query.

The Benefits of Using CTEs
There are various benefits to using CTEs in your SQL code:

• CTEs improve query readability by breaking complex queries down into more intelligible components.
• CTEs enable you to construct reusable sections of code, which simplifies maintenance and debugging.
• Potential performance gains: Some database systems have the ability to optimize queries that employ CTEs more effectively.

Let's look at how CTEs can improve code readability and maintainability.

CTEs that are not recursive
Non-recursive CTEs are used in non-recursive queries to create temporary result sets. They are excellent for simplifying complex queries in the absence of hierarchical data structures.

Consider this example: you have a database of products and want to find all products with a price above the average price. A non-recursive CTE can help make this query more readable.
WITH AveragePrice AS (
    SELECT AVG(Price) AS AvgPrice
    FROM Products
)

SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AvgPrice FROM AveragePrice);

In this case, the AveragePrice CTE calculates the average price, which is then used in the main query to filter products.

CTEs that repeat themselves
While non-recursive CTEs are beneficial in a variety of situations, recursive CTEs excel when working with hierarchical data. These are data structures in which one item is related to another, such as employees and their managers in a hierarchical organization or individuals in a family tree.

A recursive CTE's structure is made up of two parts: the anchor member and the recursive member.

• Anchor Member: This is the first part of the CTE and serves as the starting point for recursion. The anchor member picks employees who do not have supervisors (i.e., top-level employees) in an organizational structure.
• This section is executed after the anchor member and may refer back to the CTE itself. It chooses employees with bosses and adds them to the anchor members. This operation is repeated until no more rows are generated.

Consider how a recursive CTE can be used to navigate an organizational structure.

Best Practices and Case Studies
There are various important practices to remember when working with CTEs.

• CTEs for recursive data: Consider utilizing a recursive CTE if your data has a hierarchical or recursive structure.
• Avoid utilizing CTEs excessively: While CTEs can improve code readability, utilizing them in excess might cause performance concerns.
• Indexing: For optimal CTE performance, ensure that your tables have proper indexes.

CTEs are quite adaptable in terms of use cases. They can be used for the following purposes:

• Handling hierarchical data: As we've seen in earlier examples, recursive CTEs are ideal for dealing with organizational hierarchies, family trees, and other nested data structures.
• Data validation: CTEs can be used to check for data consistency and validate information.
• Complex queries: When writing complex queries, splitting them down into smaller, modular CTEs can make your code more maintainable.

Subqueries and Comparisons
Subqueries and CTEs are both techniques for breaking down complex SQL queries. There are, nevertheless, significant distinctions.

• CTEs: Allow you to divide the query into named modular components. They have the potential to increase query readability and maintainability.
• Subqueries are inline queries that are a subset of a bigger query. They might make queries more difficult to read and comprehend.

Let's look at both approaches in the context of a real-world case.
WITH EmployeeRanks AS (
    SELECT
        Department,
        EmployeeName,
        Salary,
        ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
    FROM Employees
)

SELECT Department, EmployeeName, Salary
FROM EmployeeRanks
WHERE Rank = 1;

This query uses the ROW_NUMBER() function within a CTE to rank employees within each department based on their salary. It then selects only those with a rank of 1, which are the highest-paid employees in their respective departments.

Calculating Running Totals for Sales
Consider a scenario where you have a table of daily sales, and you want to calculate the running total of sales over time. CTEs can simplify this calculation.
WITH Sales AS (
    SELECT
        SaleDate,
        Amount,
        SUM(Amount) OVER (ORDER BY SaleDate) AS RunningTotal
    FROM DailySales
)

SELECT SaleDate, Amount, RunningTotal
FROM Sales;

In this example, the CTE computes the running total of sales by using the SUM() window function within the OVER clause. It provides a straightforward way to calculate running totals in your 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.

A business rule in Master Data Services is a rule that you employ to ensure the quality and accuracy of your master data.
Is it feasible to create a business rule to verify that the value for a combination of columns (Composite key) is unique?

Solution
Navigate to Administrative Tasks on the Master Data Service (MDS) site.

Click on Manage >> Business Rules

Select the Model and Entity within which you need to add the Business Rule. And Click on Add.

Provide the Name and Description for the Business Key and Click on Add section within Then

Select the Attribute ( one column ) and Operator as : Must be Unique in Combination with the following attributes.

Post that select the other Columns which are part of Composite key (other columns that needs to be added as part of uniqueness) and Click Add.

Finally, Click on Save.
Click on Save to Create the Business Rule.

The Business Rule would be in Activation Pending State

To Activate it, Click on Publish All.

Post this, the Business Rule would in Active state:

Output
Composite Key is Unique

Composite Key is Duplicate

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

In this article, I will go over the notion of the IIF Function in SQL Server. First, let's establish a database with some dummy data in it. I am supplying you with the database, as well as the tables holding the records, on which I am demonstrating the various examples. Let's see what happens.

CREATE DATABASE Peter_OFS
PRINT 'New Database ''Peter_OFS'' Created'
GO

USE [Peter_OFS]
GO

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 UNIQUE,
    DepartmentID VARCHAR(100) NOT NULL,
    Age INT  NOT NULL,
    GrossSalary BIGINT NOT NULL,
    PerformanceBonus BIGINT,
    ContactNo VARCHAR(25),
    PRIMARY KEY (EmployeeID)
);

CREATE TABLE [dbo].[tbl_Orders] (
    OrderId INT IDENTITY (108, 1) PRIMARY KEY,
    FoodieID INT,
    OrderStatus TINYINT NOT NULL, -- ==>> OrderStatus: 4: Cancelled; 3: Pending; 2: Processing; 1: Completed
    OrderDate DATE NOT NULL,
    ShippedDate DATE,
    RestaurantId INT NOT NULL,
);

Let's check our following tables by using the following queries.

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

2) To get the data from the "tbl_Orders" table, use the following query.
SELECT * FROM Peter_OFS..tbl_Orders

The IIF Function
IIF is a logical function that returns one of two values based on whether the boolean expression is true or false. In other words, the IIF() method returns "true_value" if a condition is TRUE and "false_value" if it is FALSE.

Important PointsIn SQL Server, IIF is a logical function.

• SQL Server 2012 introduces IIF.
• IIF is an abbreviation for CASE Expression.
• IIFs can only be nested to a maximum of ten levels.
• The IIF function returns the data type with the highest precedence from the types "true value" and "false value."

Syntax
IIF(boolean_expression, true_value, false_value) is an IIF function.

• boolean_expression: A syntax error will be thrown if the argument is not a boolean expression.
• true_value: If boolean_expression evaluates to "TRUE", it returns the value supplied in the "true_value" parameter.
• false_value: If boolean_expression evaluates to "FALSE," the value specified in the "false_value" parameter is returned.

Examples
The examples in this section demonstrate the IIF Function's capability. Let's see what happens.
1) The IIF function compares integer values.
Because boolean_expression is true, the next example will return true_value.

SELECT IIF( 25 * 10 = 250, 'TRUE', 'FALSE' ) AS 'Result'

2) IIF Function with variables
In the following example, variables are used to calculate two integer values.
DECLARE @a INT = 25, @b INT = 12;
SELECT IIF( @a * @b = 300, 'TRUE', 'FALSE' ) AS 'Result'

3) IIF with String Functions
A) The following example accepts a string with a length greater than 10.
SELECT IIF(LEN('Hello! Scott') > 10, 'StringAccepted', 'StringRejected') AS [Result]

B) The following example checks the ASCII value.

SELECT IIF(ASCII('A') = 65, 'ASCIIAccepted', 'ASCIIRejected') AS [Result]

C) The following example compares string data using the IIF Function.
DECLARE @Person VARCHAR (25) = 'Peter'
SELECT @Person + ' likes ' + IIF(@Person = 'Peter', 'Mercedes-Benz Maybach', 'Audi A8') AS [Result]

4) IIF Function with data type precedence
SELECT IIF(21 < 11, 551.50, 551) Result

5) IIF Function with NULL
A) With NULL Constants
If we specify "NULL" in true_value and false_value, this statement will result in an error.
SELECT IIF( 25 * 12 = 300, NULL, NULL ) Result

B) With NULL Parameters
DECLARE @aa INT = NULL, @bb INT = NULL
SELECT IIF( 25 * 12 = 300, @aa, @bb ) Result

6) IIF Function With Aggregate Function
SUM()

The following example summarizes the total orders along with the order status.
SELECT
   SUM(IIF(OrderStatus = 1, 1, 0)) AS 'Completed',
   SUM(IIF(OrderStatus = 2, 1, 0)) AS 'Processing',
   SUM(IIF(OrderStatus = 3, 1, 0)) AS 'Pending',
   SUM(IIF(OrderStatus = 4, 1, 0)) AS 'Cancelled',
   COUNT(OrderId) AS 'Total Orders'
FROM tbl_Orders
WHERE YEAR(OrderDate) = 2021

7) Nested IIF Function (with GROUP BY Clause)

The following example summarizes the total orders along with the order status.
SELECT
   IIF(OrderStatus = 1, 'Completed',
      IIF(OrderStatus=2, 'Processing',
         IIF(OrderStatus=3, 'Pending',
            IIF(OrderStatus=4, 'Cancelled', '')
            )
         )
      ) AS [Order Status],
   COUNT(OrderId) AS 'Total Orders'
FROM tbl_Orders
GROUP BY OrderStatus

Points To Remember
In the key points, I have already mentioned that the IIF function is the shorthand form of the CASE Expression. And, yes, it's true. Internally, SQL Server converts IIF to CASE Expression and executes it.

Step 1
To check this, execute the following query with the "Actual Execution Plan" (Alternatively, press the "Ctrl + M" to include the Actual Execution Plan).
SELECT EmployeeID, CONCAT(FirstName , ' ' , LastName) AS [Full Name],
      Email, DepartmentID, GrossSalary,
      IIF(ContactNo IS NULL, 'Not Available', ContactNo) AS [Contact Number]
FROM Peter_OFS..Employee

Step 2
Now, right-click on "Compute Scalar" and click on the "Properties" option to proceed.

Step 3
And, you can see that SQL Server converts IIF to CASE expression internally.

Difference Between IIF Function and CASE Expression In SQL Server

Now, let's look at the quick difference between IIF Function and CASE Expression in SQL Server.

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

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

In a SQL Server SELECT, INSERT, UPDATE, or DELETE query, you can refer to a temporary named result set known as a common table expression (CTE). CTEs, which were first made available in SQL Server 2005, are comparable to derived tables and views. Following a WITH clause, a CTE name and query expression are specified to define CTEs. The main query can refer to the CTE as if it were a table or view after the CTE query finishes and fills the CTE with data.

Some important benefits of CTEs

• CTE queries can be modularized into individual CTE blocks that can then be readily reused in different parts of the query. This increases readability and consistency.
• CTEs allow you to design recursive searches in which a CTE refers itself. This is beneficial for data that is hierarchical or tree-like.
• Simpler semantics: CTEs are easier to understand and write since they employ less sophisticated SQL syntax than derived tables.
• CTEs perform better than nested views and subqueries when it comes to optimization. The optimizer can use CTE results to improve performance in tempdb.

When Should CTEs Be Used?

• Here are some examples of frequent applications for CTEs:
• As previously stated, CTEs let you to split down complex logic into simpler modular parts, boosting readability.
• Reusable query logic - Once a CTE is defined, it can be referenced several times in the query. This reduces the need for repetitious logic.
• CTEs can recursively reference themselves to query hierarchical data such as org charts, folders, and so on.
• Replace views - In some circumstances, a CTE can accomplish the same thing as a view but with higher efficiency since the optimizer can better tune the CTE query.
• Replace derived tables - CTEs can be used to replace derived tables in order to simplify query syntax.
• Data exploration/investigation - Because CTE definitions are localized to a single statement, they might be beneficial for ad hoc data exploration prior to permanent table storage.

Now let's look at some examples to demonstrate how to write and use CTEs.

Syntax
WITH CTE_Name (Column1, Column2)

AS
(
    -- CTE Definition using SELECT
)

SELECT *
FROM CTE_Name

A WITH clause is used before the primary SELECT statement to define the CTE. Following the WITH keyword, you supply the CTE name and optional column list. The AS keyword denotes the beginning of the CTE definition query.

After you've defined the CTE, you may use it in the primary SELECT query just like any other table or view.

Consider the following example.

WITH Sales_CTE (SalesPerson, SalesAmount)

AS
(
   SELECT SalesPerson, SUM(SalesAmount)
   FROM Sales
   GROUP BY SalesPerson
)

SELECT *
FROM Sales_CTE

This CTE summarizes sales per person into a temporary result set named Sales_CTE. The main query simply selects from the CTE to display the sales summary.

Multiple CTEs
You can define multiple CTEs in a single query by listing them sequentially after the WITH clause:
WITH Sales_CTE (SalesPerson, SalesAmount)

AS
(
SELECT SalesPerson, SUM(SalesAmount)
FROM Sales
GROUP BY SalesPerson
),

TopSales_CTE (TopSalesPerson, TopSalesAmount)

AS
(
SELECT TOP 1 SalesPerson, SalesAmount
FROM Sales_CTE
ORDER BY SalesAmount DESC
)

SELECT *
FROM TopSales_CTE

Here we define two CTEs - Sales_CTE and TopSales_CTE. The second CTE references the first CTE. The main query selects the top salesperson from the second CTE.

Recursive CTE Example
One of the key benefits of CTEs is the ability to write recursive queries. Here is an example to find all managers and employees in a hierarchy.
WITH Managers_CTE (EmployeeID, ManagerID, EmployeeName, ManagerName, Level)

AS
(
-- Anchor member
SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, m.EmployeeName,
       0 AS Level

FROM Employees e
INNER JOIN Employees m
ON e.ManagerID = m.EmployeeID

UNION ALL

-- Recursive member that references CTE name
SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, m.EmployeeName,
       Level + 1
FROM Employees e
INNER JOIN Managers_CTE m
ON e.ManagerID = m.EmployeeID
)

-- Outer query

SELECT *
FROM Managers_CTE

The anchor member defines the root level of the hierarchy.
The recursive member joins back to the CTE name to get to the next level.
UNION ALL combines each round of recursion.
Outer query returns the final resultset.

This builds the org hierarchy iteratively until all levels are retrieved.
CTE with INSERT Example

In addition to SELECT, CTEs can be used with data modification statements like INSERT.
WITH Sales_CTE (SalesID, SalesPersonID, SalesAmount)

AS
(

SELECT SalesID, SalesPersonID, SalesAmount
FROM Sales
WHERE SalesDate = '20180901'
)

INSERT INTO SalesByDay (SalesDate, SalesPersonID, SalesAmount)
SELECT '20180901', SalesPersonID, SalesAmount

FROM Sales_CTE

This inserts sales for a specific date into a separate SalesByDay table using a CTE as the data source.

CTE with UPDATE Example
You can also leverage CTEs with updated statements.

WITH Sales_CTE (SalesID, SalesAmount)

AS
(

SELECT SalesID, SalesAmount
FROM Sales
WHERE SalesDate = '20180901'

)

UPDATE SalesByDay
SET SalesAmount = Sales_CTE.SalesAmount

FROM SalesByDay
INNER JOIN Sales_CTE
ON SalesByDay.SalesID = Sales_CTE.SalesID
WHERE SalesByDay.SalesDate = '20180901'

Here we populate matching rows in another table using values from the CTE.
CTE with DELETE Example

Similarly, CTEs can be utilized with DELETE statements.

WITH InactiveSales_CTE (SalesID, SalesDate)
AS
(
SELECT SalesID, SalesDate
FROM Sales
WHERE SalesDate < '20180101'
)

DELETE SalesByDay
FROM SalesByDay
WHERE SalesID IN (SELECT SalesID
             FROM InactiveSales_CTE)

This deletes related rows in another table based on inactive sales data from the CTE.

Temporary CTE Benefits
A key benefit of CTEs is that they are temporary named result sets that only exist during query execution. This provides several advantages.

• No need to persist CTEs in the database, unlike views or permanent tables. This reduces storage overhead.
• Can reference CTEs multiple times in a statement without repetitive subqueries or joins. Improves maintainability.
• Optimizer can tailor a temporary CTE query plan, unlike a persisted view which has a fixed query plan.
• Can replace inline derived tables and views to simplify and improve query semantics.
• Great for ad hoc data investigation before determining permanent tables.

In summary, CTEs are very useful in SQL Server for simplifying complex logic, improving query readability, handling recursive queries, and temporarily staging data transformations for business reporting and analysis. As you gain more experience with SQL Server, be sure to add CTEs to your development toolbox.

Features of CTEs

• Improved Readability and Maintainability: CTEs enhance the readability of complex queries by breaking them into smaller logical sections. This is especially useful when dealing with queries involving multiple joins, subqueries, or complex calculations. The segmented structure makes it easier to understand and troubleshoot the query.
• Modularity and Reusability: CTEs enable the creation of modular SQL code. You can define CTEs for specific tasks or calculations and then reuse them across different parts of the main query. This promotes code reusability, reduces redundancy, and simplifies the modification of specific parts of the query.
• Recursive Queries: CTEs are ideal for building recursive queries, where a query references itself to traverse hierarchical or recursive data structures. This is commonly used for tasks like navigating organizational charts, product categories, or tree-like data.
• Self-Joins and Window Functions Simplification: When dealing with self-joins or complex calculations involving window functions, CTEs provide a clearer and more organized way to express the logic. They break down intricate operations into manageable steps, leading to more concise and readable code.
• Code Organization and Reusability: Complex subqueries can be defined within CTEs, allowing for cleaner code organization. This organization makes it easier to understand the purpose of each part of the query, leading to improved maintainability.
• Optimization Opportunities: In some cases, SQL Server's query optimizer can optimize CTEs more effectively than equivalent subqueries. This optimization can lead to better execution plans and potentially improved performance.

Limitations of CTEs

• Single-Statement Scope: CTEs are scoped to a single SQL statement. They cannot be referenced across different statements in the same batch. This limitation can restrict their use in complex scenarios that involve multiple related statements.
• Performance Considerations: While CTEs enhance query organization, they may not always result in the most optimal execution plans. In certain cases, complex CTEs can lead to performance issues, especially when dealing with large datasets or intricate queries.
• Memory Usage: Recursive CTEs, which are used for hierarchical or recursive queries, can consume significant memory, particularly when dealing with deep hierarchies. This can lead to performance degradation if memory usage is not managed effectively.
• Lack of Indexing Support: CTEs do not support indexing. Unlike temporary tables, CTEs do not allow you to create indexes to improve query performance. This can be a limitation when working with large datasets that require efficient access patterns.
• Nested CTEs and Complexity: Nesting multiple CTEs within each other can lead to complex and challenging-to-maintain code. Overuse of nesting can make the query difficult to understand, debug, and optimize.
• Limited Use in Stored Procedures: CTEs are more commonly used in ad-hoc queries. While they can be used within stored procedures, their single-statement scope can sometimes be restrictive when working with multiple statements in a procedure.
• Complexity Management: While CTEs enhance query readability, they can also introduce complexity, especially when dealing with deeply nested or highly recursive queries. Overusing CTEs might lead to code that is harder to understand and maintain.

Conclusion
Common Table Expressions (CTEs) in SQL Server offer valuable features that enhance the readability, modularity, and organization of complex queries. Their ability to handle recursive operations and simplify self-joins and window functions makes them a versatile tool for developers. However, it's crucial to be aware of the limitations, such as single-statement scope, performance considerations, and lack of indexing support. By understanding both the features and limitations of CTEs, developers can leverage them effectively to create optimized and maintainable SQL code. Properly using CTEs requires a balance between leveraging their advantages and mitigating potential drawbacks.

HostForLIFE.eu SQL Server 2019 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 is a widespread database administration system utilized by organizations of all sizes. In order to effectively manage and maintain a database as a database administrator or developer, it is necessary to have a thorough understanding of SQL. This post will cover some useful SQL Server queries that will assist you in performing a variety of duties.
1. List all databases supported by SQL Server

To view an inventory of all databases on a server, use the query below.

SELECT name FROM sys.databases

This will return a list of all databases on the server, including system databases such as 'master', 'model', and 'tempdb'.

2. Viewing the schema of a SQL table
To see the structure of a table, including column names and data types, you can use the following query:
EXEC sp_help 'table_name'

This will return a list of all columns in the table, along with information such as the data type, length, and whether or not the column is nullable.

3. Checking the size of a SQL Server database
To see the size of a database, including the amount of used and unused space, you can use the following query:

EXEC sp_spaceused

This will return the number of rows in the database, the amount of reserved space, and the amount of used and unused space.

4. Retrieving the current user
To see the current user that is connected to the database, you can use the following query:

SELECT SUSER_NAME()

This can be useful for auditing purposes, or for determining which user is making changes to the database.
5. Viewing the current date and time

To see the current date and time on the server, you can use the following query:
SELECT GETDATE()

This can be useful for storing timestamps in your database, or for checking the current time on the server.

6. Finding the Total Space of the tables in a database
To see the total space of all the tables in a database, you can use the following query:
SELECT t.NAME
       AS
       TableName,
       s.NAME
       AS SchemaName,
       p.rows,
       Sum(a.total_pages) * 8
       AS TotalSpaceKB,
       Cast(Round(( ( Sum(a.total_pages) * 8 ) / 1024.00 ), 2) AS NUMERIC(36, 2)
       ) AS
       TotalSpaceMB,
       Sum(a.used_pages) * 8
       AS UsedSpaceKB,
       Cast(Round(( ( Sum(a.used_pages) * 8 ) / 1024.00 ), 2) AS NUMERIC(36, 2))
       AS
       UsedSpaceMB,
       ( Sum(a.total_pages) - Sum(a.used_pages) ) * 8
       AS UnusedSpaceKB,
       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 totalspacemb DESC,
          t.NAME

This can be useful for identifying tables that may be consuming a large amount of space, and determining if any optimization is necessary.

7. Connect two Database with Different Servers in SQL Server
To connect two databases on different servers in a SQL Server query, you can use a linked server. A linked server allows you to connect to another instance of an SQL Server and execute queries against it.
exec sp_addlinkedsrvlogin  'Servername', 'false', null, 'userid', 'password';

This can be connected to two databases.

8. Execute the query with the connected server database
To see the query where you use one server database for another server database, you can use the following query:
select  *  from [Servername].[Databasename].[dbo].[tablename]

This can be used from one server database to another database.

9. Disconnect two Database with Different Servers in SQL Server
To disconnect a linked server in SQL Server, you can use the sp_dropserver system stored procedure. Here's the syntax:
drop server exec sp_dropserver    @server='Servername'

This can be disconnected from one server database to another database.

10. Top 20 Costliest Stored Procedures - High CPU
To see the query where you can find the SP which takes a High CPU, you can use the following query:
SELECT TOP (20)
    p.name AS [SP Name],
    qs.total_worker_time AS [TotalWorkerTime],
    qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
    qs.execution_count,
    ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
    qs.total_elapsed_time,
    qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
    qs.cached_time
FROM    sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

Output
SP Name: Stored Procedure Name

TotalWorkerTime: Total Worker Time since the last compile time

AvgWorkerTime: Average Worker Time since last compile time

execution_count: Total number of execution since last compile time

Calls/Second: Number of calls/executions per second

total_elapsed_time: total elapsed time

avg_elapsed_time: Average elapsed time

cached_time: Procedure Cached time

10. How to identify DUPLICATE indexes in SQL Server
To see the query where you can find duplicate indexes, you can use the following query:
;WITH myduplicate
     AS (SELECT Sch.[name]                                                 AS
                SchemaName
                ,
                Obj.[name]
                AS TableName,
                Idx.[name]                                                 AS
                IndexName,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1)  AS
                Col1,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2)  AS
                Col2,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3)  AS
                Col3,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4)  AS
                Col4,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5)  AS
                Col5,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6)  AS
                Col6,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7)  AS
                Col7,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8)  AS
                Col8,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9)  AS
                Col9,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 10) AS
                Col10,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 11) AS
                Col11,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 12) AS
                Col12,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 13) AS
                Col13,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 14) AS
                Col14,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 15) AS
                Col15,
                Index_col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 16) AS
                Col16
         FROM   sys.indexes Idx
                INNER JOIN sys.objects Obj
                        ON Idx.[object_id] = Obj.[object_id]
                INNER JOIN sys.schemas Sch
                        ON Sch.[schema_id] = Obj.[schema_id]
         WHERE  index_id > 0)
SELECT MD1.schemaname,
       MD1.tablename,
       MD1.indexname,
       MD2.indexname AS OverLappingIndex,
       MD1.col1,
       MD1.col2,
       MD1.col3,
       MD1.col4,
       MD1.col5,
       MD1.col6,
       MD1.col7,
       MD1.col8,
       MD1.col9,
       MD1.col10,
       MD1.col11,
       MD1.col12,
       MD1.col13,
       MD1.col14,
       MD1.col15,
       MD1.col16
FROM   myduplicate MD1
       INNER JOIN myduplicate MD2
               ON MD1.tablename = MD2.tablename
                  AND MD1.indexname <> MD2.indexname
                  AND MD1.col1 = MD2.col1
                  AND ( MD1.col2 IS NULL
                         OR MD2.col2 IS NULL
                         OR MD1.col2 = MD2.col2 )
                  AND ( MD1.col3 IS NULL
                         OR MD2.col3 IS NULL
                         OR MD1.col3 = MD2.col3 )
                  AND ( MD1.col4 IS NULL
                         OR MD2.col4 IS NULL
                         OR MD1.col4 = MD2.col4 )
                  AND ( MD1.col5 IS NULL
                         OR MD2.col5 IS NULL
                         OR MD1.col5 = MD2.col5 )
                  AND ( MD1.col6 IS NULL
                         OR MD2.col6 IS NULL
                         OR MD1.col6 = MD2.col6 )
                  AND ( MD1.col7 IS NULL
                         OR MD2.col7 IS NULL
                         OR MD1.col7 = MD2.col7 )
                  AND ( MD1.col8 IS NULL
                         OR MD2.col8 IS NULL
                         OR MD1.col8 = MD2.col8 )
                  AND ( MD1.col9 IS NULL
                         OR MD2.col9 IS NULL
                         OR MD1.col9 = MD2.col9 )
                  AND ( MD1.col10 IS NULL
                         OR MD2.col10 IS NULL
                         OR MD1.col10 = MD2.col10 )
                  AND ( MD1.col11 IS NULL
                         OR MD2.col11 IS NULL
                         OR MD1.col11 = MD2.col11 )
                  AND ( MD1.col12 IS NULL
                         OR MD2.col12 IS NULL
                         OR MD1.col12 = MD2.col12 )
                  AND ( MD1.col13 IS NULL
                         OR MD2.col13 IS NULL
                         OR MD1.col13 = MD2.col13 )
                  AND ( MD1.col14 IS NULL
                         OR MD2.col14 IS NULL
                         OR MD1.col14 = MD2.col14 )
                  AND ( MD1.col15 IS NULL
                         OR MD2.col15 IS NULL
                         OR MD1.col15 = MD2.col15 )
                  AND ( MD1.col16 IS NULL
                         OR MD2.col16 IS NULL
                         OR MD1.col16 = MD2.col16 )
ORDER  BY MD1.schemaname,
          MD1.tablename,
          MD1.indexname

This can be Find the Duplicate Indexes, So you can remove the duplicate Indexes.

In this post, we covered some useful queries for working with Microsoft SQL Server. These queries can help you perform tasks such as listing all databases on a server, viewing the schema of a table, checking the size of a database, seeing the current user and date and time, linking to another server database, get Duplicate indexes.

I hope these queries are useful for you!

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

In this article, we will learn about SPARSE Column in SQL Server. The SPARSE column is a good feature of SQL Server. It helps us to reduce the space requirements for null values. Using a SPARSE column, we may save up to 20 to 40 percent of space.

SPARSE Column in SQL ServerA SPARSE column is a common column with optimized storage for NULL values. It also reduces the space requirements for null values at the cost of more overhead to retrieve non-null values. In other words, a SPARSE column is better at managing NULL and ZERO values in SQL Server. It does not occupy any space in the database. Using a SPARSE column, we may save up to 20 to 40 percent of the area. We can define a column as a SPARSE column using the CREATE TABLE or ALTER TABLE statements.
CREATE TABLE TableName
(
      .....
      Col1 INT SPARSE,
      Col2 VARCHAR(100) SPARSE,
      Col3 DateTime SPARSE
      .....
)

We may also add/change a column from the graphical view.

Example
In this example, I have created two tables with the same number of columns and the same data type, but one table's columns are created as a SPARSE column. Each table contains 500+ rows.
CREATE TABLE TableName
(
      Col1 INT SPARSE,
      Col2 VARCHAR(100) SPARSE,
      Col3 DateTime SPARSE
)

CREATE TABLE TableName1
(
      Col1 INT ,
      Col2 VARCHAR(100) ,
      Col3 DateTime
)

Using the sp_spaceused stored procedure, we can determine the space occupied by the table data.
sp_spaceused 'TableName'
GO
sp_spaceused 'TableName1'

Advantages of a SPARSE column
    A SPARSE column saves database space when there are zero or null values.
    INSERT, UPDATE, and DELETE statements can reference the SPARSE columns by name.
    We can get more benefits from Filtered indexes on a SPARSE column.
    We can use SPARSE columns with change tracking and change data capture.

Limitations of a SPARSE column
    A SPARSE column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
    A SPARSE column cannot be data types like text, ntext, image, timestamp, user-defined data type, geometry, or geography.
    It cannot have a default value and bounded-to rule.
    A SPARSE column cannot be part of a clustered index or a unique primary key index and partition key of a clustered index or heap.
    Merge replication does not support SPARSE columns.
    The SPARSE property of a column is not preserved when the table is copied.

HostForLIFE SQL Server 2019 Hosting
HostForLIFE 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.