I recently worked on a project to assess the schema of a third-party vendor. Our organization has an internal support ticket tracking tool. The program used a SQL database, and after calculating its cost, we opted not to extend the contract. The objective was to create an in-house platform for managing internal support tickets.

My responsibility was to review the schema of the internal support database. We couldn't figure out what data was in which table because the structure was intricate and the table names were tough. Eventually, I was able to determine the relationship between tables and what data was contained in each. I've also given the columns proper names so that we can quickly identify what data is contained in which column. I used the sp_rename method to rename tables.

This article explains how to rename a column using the sp_rename stored procedure. I also demonstrated how to rename a column in SQL Server Management Studio. First, let us look at the fundamentals of renaming a column.

The Basics of Renaming Columns
Renaming a table's column is a simple task. We can use a system-stored process called sp_rename. Additionally, we can utilize SQL Server Management Studio to rename a column. The sp_rename stored procedure can rename the following:

• Database objects like tables, Stored procedures, and functions.
• Indexes and statistics
• User-defined datatypes.

In this article, we will learn how to rename any column of an SQL Server table using the sp_rename stored procedure.

How can you rename a column in SQL Server?
In SQL Server, we may rename any column or object using the sp_rename stored procedure. In this post, we'll look at how to rename columns using the sp_rename function.

The syntax for the sp_rename stored procedure is as follows.

Exec sp_rename 'original_schema_name.original_table_name.original_column_name', 'new_column_name' ,'object_type'

In the syntax

• original_schema_name.original_table_name.original_column_name: Specify the table name whose column you want to rename. If you are renaming a column of a table that exists in the user-defined schema, you must specify the table name in three three-part names.
• new_column_name: Specify the new name of the column.
• object_type: Specify the object type.

Let us understand the process with simple examples. Suppose you want to rename a column of the patient table. The original column name is Address, and we want to change it to patient_address. The sp_rename command to rename the column is as follows.

USE HospitalManagementSystem
GO
EXEC sys.sp_rename 'patients.address','patient_address','COLUMN'

Once the column is renamed, let us verify that the column has been renamed successfully. You can run the below query to view the columns of the patient table.
use HospitalManagementSystem
go
select table_name,column_name from information_schema.columns where table_name='Patients'

Output

As you can see in the above image, the column Address has been changed to patient_address.

Let us take another example. Suppose you want to rename the column of Sales. invoice table which exists in the Wideworldimportors database. The current name of the column is InvoiceDate, and the new name will be InvoiceCreateDate. The query to rename the column is the following.
EXEC sys.sp_rename 'Sales.Invoices.InvoiceDate','InvoiceCreateDate','COLUMN'

Here you can see, that we are changing the column name of the invoice table which is in the Sales schema. Therefore, we have used three-part naming. Once the column is renamed, execute the following T-SQL query to verify that the column has been renamed.
select table_name,column_name from information_schema.columns where table_name='Invoices'

Output

Renaming other objects in SQL Server
The sp_rename stored procedure can be used to rename other database objects, such as indexes, constraints, and stored procedures. The syntax of the sp_rename operation stays unchanged. The object_type argument for the sp_rename column will change. Let us consider a basic example.

Assume we wish to rename the index of the sales invoice table. The index's present name is 'IX_Sales_Invoices_ConfirmedDeliveryTime', which we would like to modify to 'IX_Sales_Invoices_ConfirmedDeliveryTime_New'. In the query, the object_type argument in the sp_rename method will be set to INDEX. The query to rename the index is shown below.

EXEC sys.sp_rename 'Sales.Invoices.IX_Sales_Invoices_ConfirmedDeliveryTime','IX_Sales_Invoices_ConfirmedDeliveryTime_New','INDEX'

Once the index is renamed, you can query sys. indexes dynamic management view to verify that the index has been renamed successfully. Note that whenever we rename any index, the statistics associated with the index will be renamed as well. Here is the query to verify both changes.
SELECT object_name(object_id)[TableName], name [IndexName], Type_desc [Index Type]
FROM sys.indexes where object_id=object_id('Sales.Invoices')

Output

Using SQL Server Management Studio to Rename
We can use SQL Server Management Studio to rename the database object. In the first section, we learnt how to rename columns and indexes using the sp_rename stored procedure.

In this example, we'll see how to rename a constraint in SQL Server Management Studio. For demonstration, I'll rename the constraint in the Sales.invoice table. The present constraint name is DF_Sales_Invoices_InvoiceID, which we will rename to Default_Sales_Invoices_InvoiceID. As the name implies, this constraint is a default constraint.

First, launch SQL Server Management Studio and connect to your database server. Expand databases. Expand the Wideworldimportors database.

A database contains many tables. Expand the Sales, Invoice, and Constraint tables. Press F2 or right-click on DF_Sales_Invoices_InvoiceID and choose Rename.

The name will be editable. Change the name to Default_Sales_Invoices_InvoiceID and hit enter. The name will be changed. The SQL Server management studio prompts a confirmation message that looks like the following image.

Click OK to change the name. Once changes are made, execute the following T-SQL query to verify that the constraint has been renamed successfully.
SELECT
  [constraint].name AS constraint_name,
  OBJECT_NAME([constraint].parent_object_id) AS table_name,
  [column].name AS column_name from
  sys.default_constraints [constraint]
JOIN
  sys.columns [column] ON [constraint].parent_object_id = [column].object_id
    AND [constraint].parent_column_id = [column].column_id
    where  OBJECT_NAME([constraint].parent_object_id)='Invoices'

Output

Let us take a look at some limitations and things to be considered before renaming any column.

Limitations and Considerations
If you are renaming any column in a table or renaming any object in a database, you must consider the following limitations and possible issues that might break the application.

• ALTER permission is needed on the object that you want to rename. Suppose you want to rename a column name; you must have ALTER object permission on the table whose column you are renaming.
• Renaming a column name always breaks the stored procedure or other objects (View, function, etc.) that are referencing that column. For example, you are renaming a column that is being used in a view. Therefore, make sure you modify all the stored procedures, functions, and triggers that reference the column that was renamed. You can use sys.sql_expression_dependencies to find all dependencies of the column.
• When you rename a stored procedure, the object's name in sys.sql_modules will not change. Hence Microsoft recommends dropping and recreating an object instead of just renaming it.
• When you rename a column of a table that is part of replication, the replication might break so if we want to rename the column of the replicated table, first, we must pause the replication, then rename the column using sp_rename or SQL Server management studio, update all database objects that are referencing the column, and finally, reinitialize replication with the new snapshot.

Conclusion
In this tutorial, we learned how to rename any column in a table. I demonstrated how to rename a column using a system-stored process called sp_rename, complete with syntax and easy examples. We also learned how to rename a column using SQL Server Management Studio. We can also use other tools, such as dbForge Studio for SQL Server, to run the stored procedure to rename a column. We also reviewed the limits and other difficulties that must be addressed before to renaming any object or column.

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.

While UNION ALL combines them without removing duplicates, yielding faster results but keeping all rows, including duplicates, UNION merges and filters out duplicates from different SELECT queries.

Now let's look at the example.

I have started by making two tables, Employee and Employee_One.

CREATE TABLE Employee
(
 employeeId INT IDENTITY(1,1) PRIMARY KEY,
 employeeName VARCHAR(50),
 City VARCHAR(100)
)

The Employee table has records as follows.

CREATE TABLE Employee_One
(
 employeeId INT IDENTITY(1,1) PRIMARY KEY,
 employeeName VARCHAR(50),
 City VARCHAR(100)
)

The Employee_One table has records as follows.

Let's explore how the UNION operation works.

SELECT City
FROM Employee
UNION
SELECT City FROM Employee_One

Employee table records

Employee_One table records

After using UNION between these two tables, we will get results as follows (removing duplicates).

In conclusion, the UNION function in SQL Server automatically eliminates duplicate rows while combining the output of several SELECT queries to create a single, cohesive result set. Let's examine the operation of UNION ALL.

SELECT City

FROM Employee

UNION ALL

SELECT City

Employee_One

Employee table records

Employee_One table records

Employee_One table records

After using UNION ALL between these two tables, we will get results as follows (including all records - without removing duplicate records).
UNION ALL

In summary
In SQL Server, the UNION ALL method is used to aggregate the output of several SELECT queries without removing duplicate rows. Because duplicate elimination processing is not present in UNION, UNION ALL offers faster performance than UNION and contains all records from the combined queries, making it a good option when maintaining duplicate entries is required.

I hope this post has given you useful knowledge on using UNION and UNION ALL in SQL Server. Best wishes for the future.

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.

Overview
Microsoft SQL Server is a well-known and potent relational database solution in the field of database management. During their exploration of the complex world of SQL, developers and database administrators often find themselves returning to the same questions in both interviews and practical situations.

We'll explore the most commonly asked SQL questions for MS SQL Server in this blog, offering tips and code samples to help novices and experts alike.

Retrieving Data. The SELECT Statement
The foundation of any database interaction lies in retrieving data. The SELECT statement is your go-to tool for this task.
-- Retrieve all columns from a table
SELECT * FROM TableName;

-- Retrieve specific columns
SELECT Column1, Column2 FROM TableName;

-- Filter data with WHERE clause
SELECT * FROM TableName WHERE Condition;

Filtering and Sorting Data
Refining data based on conditions and sorting results is a common requirement.
-- Filtering with WHERE
SELECT * FROM TableName WHERE Column = 'Value';

-- Sorting with ORDER BY
SELECT * FROM TableName ORDER BY Column ASC/DESC;

Aggregating Data. GROUP BY and Aggregate Functions
Aggregating data provides valuable insights into summaries and statistics.
-- Grouping data with GROUP BY
SELECT Column, COUNT(*)
FROM TableName
GROUP BY Column;

-- Using Aggregate Functions
SELECT AVG(Column), SUM(Column), MAX(Column)
FROM TableName
WHERE Condition;

Joining Tables. INNER JOIN, LEFT JOIN
When data resides in multiple tables, joining becomes essential.
-- Inner Join
SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID;

-- Left Join
SELECT *
FROM Table1
LEFT JOIN Table2 ON Table1.ID = Table2.ID;

Subqueries. Nesting Queries for Precision
Subqueries enable embedding one query within another, offering precision and flexibility.
-- Using Subqueries
SELECT *
FROM TableName
WHERE Column IN (SELECT Column FROM AnotherTable WHERE Condition);

Modifying Data. UPDATE and DELETE Statements
Ensuring data accuracy involves updating and deleting records.
-- Update Statement
UPDATE TableName SET Column = 'NewValue' WHERE Condition;

-- Delete Statement
DELETE FROM TableName WHERE Condition;

Transaction Control. BEGIN, COMMIT, ROLLBACK
Transactions maintain data integrity by grouping operations.
-- Begin Transaction
BEGIN TRANSACTION;

-- Commit Transaction
COMMIT;

-- Rollback Transaction
ROLLBACK;

Working with Dates. Date Functions
Manipulating dates is a common task, and SQL Server provides robust date functions.
-- Get Current Date
SELECT GETDATE();

-- Extract Part of a Date
SELECT YEAR(DateColumn), MONTH(DateColumn), DAY(DateColumn);

Common Table Expressions (CTEs). Enhancing Readability
CTEs simplify complex queries and enhance code readability.
-- Using CTE
WITH MyCTE AS (
    SELECT Column FROM TableName WHERE Condition
)
SELECT * FROM MyCTE;

Window Functions. Analytical Insights
Window functions offer advanced analytical capabilities.
-- Using Window Function
SELECT Column, ROW_NUMBER() OVER (ORDER BY Column) AS RowNum
FROM TableName;

These SQL queries encapsulate the essence of database interactions and form the backbone of database-related interviews and projects. Mastering these queries empowers professionals to navigate diverse scenarios efficiently.
Conclusion

The world of SQL Server queries is vast, and continuous learning is key.

As you embark on your SQL journey, these fundamental queries will serve as the stepping stones to deeper insights and mastery. Happy querying!

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 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.