Two strong SQL tools—GROUP BY and the OVER clause—play critical roles in data management and analysis. Understanding these tools is critical for unlocking the full power of SQL queries. Let's investigate their subtleties and see how they help with data aggregation and window operations.

GROUP BY: Data Aggregation
The GROUP BY clause is essential for data aggregation in SQL. It enables you to group rows with similar values in one or more columns and then use aggregate methods like COUNT, SUM, AVG, MAX, and MIN to get summary results.

Consider the following scenario.
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

In this example, the query computes the average pay for each department in the 'employees' dataset, using the 'department' field to group the data. The category BY clause categorizes the output and computes the average income within each category.

GROUP BY isn't limited to one column; it can group data more precisely by using many columns, allowing extensive insights into diverse combinations of those columns.
The OVER clause is as follows: Activating Window Functions

The OVER clause adds a powerful feature known as window functions. It only works on a subset of rows defined by a window. These methods compute across a set of table rows linked to the current row, rather than condensing the result set into a single output like aggregate functions do.
SELECT employee_id, salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_salary_department
FROM employees;

This query employs the OVER clause with the AVG function to calculate the average salary for each department alongside individual employee data. The PARTITION BY clause divides the rows into partitions based on the 'department', enabling the calculation of the average salary within each partition.

Window functions are versatile, offering numerous functions like ROW_NUMBER, RANK, NTILE, and more. They empower users to perform complex analytical tasks, such as ranking, cumulative sums, moving averages, and identifying top or bottom performers within specific partitions.

Key Differences and Use Cases

While both GROUP BY and the OVER clause perform data aggregation, their functionalities differ significantly. GROUP BY creates a single row per group by collapsing the result set, whereas the OVER clause works with window functions to provide analytical insights while preserving individual rows.

GROUP BY is ideal for summarizing and reducing data and is often used in aggregate queries. Conversely, the OVER clause shines in analytical scenarios where a detailed view of the dataset is required without losing individual records. Mastering GROUP BY and the OVER clause is crucial for leveraging the full potential of SQL in data analysis. Understanding their capabilities and distinctions empowers SQL practitioners to craft sophisticated queries for both aggregating and analyzing data, unlocking deeper insights from databases.

These tools are invaluable for anyone working with SQL, offering a robust arsenal to tackle diverse data analysis and reporting tasks. Harness the power of GROUP BY and the OVER clause to elevate your SQL skills and unearth rich insights from 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.

While connecting to the SQL server, a network-related or instance-specific problem occurred. The server could not be located or was inaccessible. Check to ensure that the instance name is correct and that the SQL server is set to enable remote connections. (provider: named pipes provider, error: 40 - Could not open a connection to SQL server).

I will provide step by step for Troubleshooting with screenshots below. Step 1. Go to the services Page and check the SQL Server (SQL EXPRESS) Status Running.

Step 2. Next SQL Server Management Studio View Tap object explorer.

Expand "SQL server log" and then select the current log to which you want to apply the filter.Click Apply Filter and type "server is listening on" in the Message includes text box to apply the filter. Click apply filter, then OK.

“server is listening on [‘any’ <ipv4> 1433]” should be shown. SQL Server instance is listening on all computers with IP address IPv4 and TCP port is 1433 (default).

Step 3. “All programs”, point to SQL server configuration tools, “SQL server configuration management”

Next Enabling protocols for port 1433 Select “protocols for MSSQL server”. Click on “TCP\IP” in the right panel
In the tab “protocol” set enable as “yes”.
then Choose the “IP Address tab” from the window and set “TCP Port” equal to “1433″ in the “IP All” entry. click Ok

Step 4. Next, Click “Start” and start “Firewall.cpl” in the run box.

Click on “Advanced Settings.

Tap the Inbound Rules from the left pane of the “Windows Firewall with Advanced Security” and click the New Rule from the “Actions” window.

Select “Port” under “Rule Type” and press the “Next” button.

Now select “Specific local ports” and set it to 1433.

Now select “Allow the connection” in the “Action” dialog and press the Next button.

Give the rule a “title” on this stage and press the “Finish” button.

Then finally solved the problem of the migration database.

This article taught us A network-related or instance-specific error occurred while establishing a connection to an SQL server. If you have any questions, please contact me.

Thanks.

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

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.

How Does SQL Server Search Work?
The LIKE operator is used in a WHERE clause in SQL to search for a specific pattern in a column.

The wildcards % and _ are frequently used in the LIKE operator.
% denotes one or more characters, as in Acc% — Account, Accept, Access. _ denotes a single character, as in Labs, Cabs, and Tabs are all abbreviated as _abs.

What exactly is Normal Search?
The search is limited to a single column. Filter the results depending on the single column's search text.
The column Department Name is searched in the sample below.

DECLARE @SearchText VARCHAR(255)

SET @SearchText = 'engineer'

SELECT
    EmployeeKey,
    FirstName,
    LastName,
    Title,
    EmailAddress,
    Phone,
    EmergencyContactName,
    DepartmentName
FROM dbo.DimEmployee WITH(NOLOCK)
WHERE DepartmentName LIKE '%' + @SearchText + '%'

In the below example, search is applied to the column FirstName.
DECLARE @SearchText VARCHAR(255)

SET @SearchText = 'david'

SELECT
    EmployeeKey,
    FirstName,
    LastName,
    MiddleName,
    Title,
    HireDate,
    BirthDate,
    EmailAddress,
    Phone,
    EmergencyContactName,
    EmergencyContactPhone,
    DepartmentName,
    StartDate
FROM dbo.DimEmployee
WHERE FirstName LIKE '%' + @SearchText + '%'

What is an Advanced Search?
Multiple columns are searched. Filter the data depending on the search text in the numerous columns.
Search is used on numerous columns in the example below.

DECLARE @SearchText VARCHAR(255)

SET @SearchText = 'tool'

SELECT EmployeeKey, FirstName, LastName, MiddleName, Title, HireDate, BirthDate, EmailAddress,
Phone, EmergencyContactName, EmergencyContactPhone, DepartmentName, StartDate
FROM dbo.DimEmployee
WHERE (FirstName LIKE '%' + @SearchText + '%'
  OR LastName LIKE '%' + @SearchText + '%'
  OR Title LIKE '%' + @SearchText + '%'
  OR EmailAddress LIKE '%' + @SearchText + '%'
  OR EmergencyContactName LIKE '%' + @SearchText + '%'
  OR DepartmentName LIKE '%' + @SearchText + '%'
  OR HireDate LIKE '%' + @SearchText + '%'
  OR COALESCE(@SearchText,'') = '')

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 Pagination?
Pagination is the process of separating big amounts of data into smaller data sets that are displayed on discrete pages. It's commonly utilized in online applications.

How Does MS SQL Server Pagination Work?

We can accomplish pagination capabilities in MS SQL Server by using OFFSET and FETCH clauses with ORDER BY in a SELECT query.

• OFFSET: The number of rows in the result set that will be skipped. It should be greater than zero.
• FETCH: The number of rows that will be displayed in the result.

Considerations When Using OFFSET and FETCH

• ORDER BY is required when using the OFFSET FETCH Clause.
• FETCH is optional, but OFFSET is required.
• The TOP clause is incompatible with the SELECT statement using OFFSET FETCH.

Let's look at few examples.
In the following example, OFFSET 0 and FETCH NEXT 5 ROWS indicate that no rows are skipped and the next 5 rows in the dataset are returned, rather than the first 5 rows in the dataset.
SELECT EmployeeKey,FirstName,LastName,Title,EmailAddress,
Phone,EmergencyContactName,DepartmentName
FROM dbo.DimEmployee WITH(NOLOCK)
ORDER BY EmployeeKey
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY

OFFSET 3 and FETCH NEXT 5 ROWS in the example below indicate skip the first 3 rows and retrieve the next 5 rows in the dataset.

SELECT EmployeeKey,FirstName,LastName,Title,EmailAddress,
Phone,EmergencyContactName,DepartmentName
FROM dbo.DimEmployee WITH(NOLOCK)
ORDER BY EmployeeKey
OFFSET 3 ROWS
FETCH NEXT 5 ROWS ONLY

Page Number and Rows Of Page

• PageNumber - Represents the page number
• RowsOfPage - Represents the no of rows on a page

Now, to calculate the number of rows to be skipped when we move to the next page the (@PageNumber-1) * @RowsOfPage formula is being used in the OFFSET, and the number of rows will be returned in FETCH.

Here, PageNumber is 2 and RowsOfPage is 5, which means return the dataset for page number 2.

DECLARE @PageNumber AS INT
       ,@RowsOfPage AS INT

SET @PageNumber=2
SET @RowsOfPage=5

SELECT EmployeeKey,FirstName,LastName,Title,EmailAddress,
Phone,EmergencyContactName,DepartmentName
FROM dbo.DimEmployee WITH(NOLOCK)
ORDER BY EmployeeKey
OFFSET (@PageNumber-1) * @RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY

Page Number and Rows Of Page

• PageNumber - Represents the page number
• RowsOfPage - Represents the no of rows on a page

Now, to calculate the number of rows to be skipped when we move to the next page the (@PageNumber-1) * @RowsOfPage formula is being used in the OFFSET, and the number of rows will be returned in FETCH.

Here, PageNumber is 2 and RowsOfPage is 5, which means return the dataset for page number 2.

DECLARE @PageNumber AS INT
       ,@RowsOfPage AS INT

SET @PageNumber=2
SET @RowsOfPage=5

SELECT EmployeeKey,FirstName,LastName,Title,EmailAddress,
Phone,EmergencyContactName,DepartmentName
FROM dbo.DimEmployee WITH(NOLOCK)
ORDER BY EmployeeKey
OFFSET (@PageNumber-1) * @RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY

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 are Registered Servers?
Registered Servers is a feature in SQL Server Management Studio (SSMS) that enables database administrators (DBAs) and developers to manage and arrange connections to many SQL Server instances and other database server types in a centralized manner. It's a useful tool for streamlining server administration, running queries across numerous servers, and doing administrative duties quickly.

What exactly are Registered Servers?

• Registered Servers is a feature in SQL Server Management Studio (SSMS) that enables database administrators (DBAs) and developers to manage and arrange connections to many SQL Server instances and other database server types in a centralized manner. It's a useful tool for streamlining server administration, running queries across numerous servers, and doing administrative duties quickly.
• Grouping and Organization: Registered Servers can be organized into server groups, making it easier to categorize and manage servers based on criteria like development, production, or specific projects.
• Quick Access to Servers: With Registered Servers, you can quickly connect to a server by selecting it from the list without the need to enter connection details each time you want to connect.
• Server-Level Configuration: You can set server-level properties and configurations for multiple servers at once, simplifying tasks like changing compatibility levels, managing security settings, or configuring maintenance plans.

In the "New Server Registration" dialog box, enter the server name, authentication method, and login credentials.
Once the server is added, it will appear in the Registered Servers window, where you can organize and manage your connections.
To run a query against multiple servers, select the servers you want to include and right-click on them. Then, select "New Query" to open a new query window with all the selected servers included.
After running the query, the results will be displayed in a single window, making it easy to compare and analyze the data from multiple servers.

Summary
SQL Server Management Studio's Registered Servers functionality is useful for centralizing server management, enhancing organization, and simplifying administrative operations across several database servers. It is especially useful for DBAs and developers that operate in situations with several SQL Server instances.

SQL Server, mySQL, OracleSQL, PostgreSSQL, IBM DB2, Terada, and other relational database systems rely on Structured Query Language (SQL) to manage and manipulate data.  We frequently utilize the CASE statement, along with its siblings WHEN, THEN, and ELSE, to perform conditional operations within SQL queries. In this blog, we will look at how to use these keywords in SQL Server and how versatile they are. Let's get this party started.

EXAMPLE OF A CASE STATEMENT
The SQL Server CASE statement is a useful tool for applying conditional logic within a query. It enables you to return different values based on the conditions you specify. A CASE statement has the following fundamental structure:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE elseResult
END

condition1, condition2, etc.: These are the conditions you want to evaluate.
result1, result2, etc.: The corresponding values or expressions to return if the conditions are met.
elseResult: The value to return if none of the conditions are met (optional).

In this article, we will look at several usage cases of the CASE statement in real-world business situations. For demonstration purposes, we will use the transactions table and retrieve all of the records using the basic SELECT * FROM transactions shown below.

We have the Number of Store column in the transactions table, and we want to use the CASE to return Yes for Number of Stores greater than or equal to 3 and No for less than 3 stores, and we want to give 5% to each customer who operates three or more stores, which will be calculated by the Sales Amount. In the third case, we want to be fair by offering a 5%, 8%, 10%, and 15% incentive to customers who operate one, two, three, or four locations, respectively. For each customer, the percentage bonus will be multiplied by the sales amount. For each customer, the percentage bonus will be multiplied by the sales amount. To deter consumers from owing us, we have a measure in place to penalize any client whose Previous Balance is larger than 0 and who does not have a credit facility arrangement with us. The CustomerID will be extracted from all columns in the Excel table. To accomplish this, the CASE began:

Run the query below.

SELECT
[Customer Name],
[Previous Balance],
[Credit Facility],
[Sales Amount],
[Number of Store],
CASE
WHEN [Number of Store] >= 3 THEN ‘Yes’
ELSE ‘No’
END AS [3 or more stores],
CASE
WHEN [Number of Store] >= 3 THEN [Sales Amount] * 0.10
ELSE 0
END AS Bonus,
CASE
WHEN [Number of Store] = 1 THEN [Sales Amount] * 0.05
WHEN [Number of Store] = 2 THEN [Sales Amount] * 0.08
WHEN [Number of Store] = 3 THEN [Sales Amount] * 0.010
WHEN [Number of Store] >= 4 THEN [Sales Amount] * 0.15
END AS [Fair Bonus],
CASE
WHEN [Previous Balance] >0 AND [Credit Facility]<>’Yes’ THEN [Previous Balance] * 0.10
ELSE 0
END AS Penalty
FROM
transactions

Click on Execute to run the code

From the screenshot below, for the first scenario, we have Yes for customer with 3 or more stores while No is assigned to customers with less than 3 stores. For the second scenario, we calculated the bonus value for customers operating three or more stores while customers with less than 3 stores received 0. With regards to the third scenario, we gave fair bonus to each of the customers based on the actual number of stores they operate. Finally, customers whose Previous Balance is greater than 0 and who do not have Credit Facility with us received 10% penalty.

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

Statement of the Problem
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 that ensures a constant length of value within an attribute?
Unfortunately, there is no direct feature/condition/operator to accomplish this request; however, it can be handled by combining various features/conditions/operators.

Solution
Navigate to Administrative Tasks on the Master Data Service (MDS) site. Click on Manage >> Business Rules

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

Select the Attribute ( one column ) and Operator as Must have a minimum length of
Post that provides the intended length value and Click Add. Finally, Click on Save.

Similarly, Add one with a Maximum length validation rule, which would finally result in the below aspect.

Click on Save to Create the Business Rule.
The Business Rule would be in Activation Pending State.

Output
The length of the value is 5.

Length of value is other than 5
a) Length < 5

b) Length > 5

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.

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.