Loading...

Follow {coding}Sight | Awesome blog for SQL Server and.. on Feedspot

Continue with Google
Continue with Facebook
or

Valid

In this article, I will demonstrate several ways to split the delimited string and insert it in into a column of a table in SQL Server. You can do it using the following methods:

  1. Convert delimited string into XML, use XQuery to split the string, and save it into the table.
  2. Create a user-defined table-valued function to split the string and insert it into the table.
  3. Split the string using STRING_SPLIT function and insert the output into a table.

To demonstrate the above methods, let me prepare a demo setup. First, let us create a table named Employee on DemoDatabase. To do that, we need to execute the following query:

USE DEMODATABASE 
GO 

CREATE TABLE EMPLOYEE 
  ( 
     ID            INT IDENTITY (1, 1), 
     EMPLOYEE_NAME VARCHAR(MAX) 
  )

For this demo, we will insert the names of all employees in one row and the names of employees will be separated by a comma. To do that, we need to execute the following query:

INSERT INTO EMPLOYEE 
            (EMPLOYEE_NAME) 
VALUES      ('DULCE , MARA , PHILIP , KATHLEEN, NEREIDA , GASTON , ETTA , EARLEAN , VINCENZA')

Execute the following query to verify that data has been inserted into the column.

SELECT * 
FROM   EMPLOYEE

The following is the output:

As I mentioned above, we are going split the delimited string and insert it into a table. So, we will create a table named Employee_Detail to store the delimited string split by any of the above methods.

To create a table, execute the following code:

USE DEMODATABASE 
GO 
CREATE TABLE EMPLOYEE_DETAIL 
  ( 
     ID      INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, 
     EMPNAME VARCHAR(MAX) NOT NULL 
  )

Method 1: Use STRING_SPLIT function to split the delimited string

We will use the STRING_SPLIT function to split the string in a column and insert it into a table. Before we do that, let me explain about the STRING_SPLIT function.

What is STRING_SPLIT Function

STRING_SPLIT is a table-valued function, introduced in SQL Server 2016. This function splits the string based on the special character within the row and returns the output in a separate table. We can use this function on the databases that have compatibility level equal to or higher than 130.

The STRING_SPLIT function accepts two parameters and returns a table with the separated values. The following is the syntax of the STRING_SPLIT function.

SELECT STRING_SPLIT (STRING, SPECIALCHARACTER)

In the above syntax, SPECIALCHARACTER is one character which will be used to separate the input string.

The following is a simple example of the STRING_SPLIT function.

DECLARE @STRING VARCHAR(MAX) 
DECLARE @SPECIALCHARACTER CHAR(1) 
SET @STRING='NISARG,NIRALI,RAMESH,SURESH' 
SELECT * 
FROM   STRING_SPLIT (@STRING, ',')

The following is an output of the query:

As you can see in the above example, the name of the output column returned by STRING_SPLIT is “value.” We can filter the output returned by the function using the WHERE clause on the “value” column and also, we can sort the order of output using the ORDER BY clause on the “value” column.

The following is an example.

Now to insert a delimited string into a table, we will perform the following tasks:

  1. Create a variable named @EmployeeName, which holds the output of the Employee table. To do that, execute the following code:
    DECLARE @EMPLOYEENAME VARCHAR(MAX) 
    SET @EMPLOYEENAME =(SELECT EMPLOYEE_NAME 
                        FROM   EMPLOYEE)
  2. Create another variable called @Separator of the char data type. This variable holds the value of the separator, which will be used to split the strings into multiple values. To create the variable and assign the value to the separator, execute the following code:
    DECLARE @SEPARATOR CHAR(1) 
    SET @SEPARATOR=','
  3. Now use the “STRING_SPLIT” function to split the values of the employee_name column of the Employee table and insert the values into the EMPLOYEENAME table. To do that, execute the following code:
    INSERT INTO EMPLOYEE_DETAIL 
                (EMPNAME) 
    SELECT * 
    FROM   STRING_SPLIT(@EMPLOYEENAME, @SEPARATOR)

The following is the entire script:

DECLARE @EMPLOYEENAME VARCHAR(MAX) 

SET @EMPLOYEENAME =(SELECT EMPLOYEE_NAME 
                    FROM   EMPLOYEE) 
DECLARE @SEPARATOR CHAR(1) 
SET @SEPARATOR=',' 
INSERT INTO EMPLOYEE_DETAIL 
            (EMPNAME) 
SELECT * 
FROM   STRING_SPLIT(@EMPLOYEENAME, @SEPARATOR)

Execute the above script. The script will insert nine rows into the table. Once you execute it, make sure the data has been inserted into the EMPLOYEENAME table. For this, execute the following query:

SELECT * 
FROM   EMPLOYEE_DETAIL

The following is the output:

Method 2: Split string using XML and insert the output in the table

When we want to split the delimited string, we can do it using table-valued functions. As we know, the user-defined table-valued functions are resource-intensive and should be avoided. In such cases, we do not have many options available. As I mentioned, the STRING_SPLIT function can be used for the databases which have compatibility level greater than or equal to 130. In such circumstances, it is difficult to find a way to split a delimited string. We have created a simple and efficient solution for this task. We can split the string using XML.

So, in this section, I am going to explain the code of XML which can be used to insert the split delimited string in different rows of a column.

I have split the entire code into three steps.

Step 1: Convert the delimited string into the XML Format. To do that, execute the following code:

USE demodatabase 
go 

DECLARE @xml       AS XML, 
        @QueryData AS VARCHAR(max), 
        @delimiter AS VARCHAR(10) 

SET @QueryData=(SELECT employee_name 
                FROM   employee) 
SET @delimiter =',' 
SET @xml = Cast(( '<EMPNAME>' 
                  + Replace(@QueryData, @delimiter, '</EMPNAME><EMPNAME>') 
                  + '</EMPNAME>' ) AS XML) 

SELECT @XML

The following is the output:

To view the entire XML string, click the cell as shown on the image above. Once you click the cell, the XML file should look like following:

<EMPNAME>DULCE </EMPNAME>
<EMPNAME> MARA </EMPNAME>
<EMPNAME> PHILIP </EMPNAME>
<EMPNAME> KATHLEEN</EMPNAME>
<EMPNAME> NEREIDA </EMPNAME>
<EMPNAME> GASTON </EMPNAME>
<EMPNAME> ETTA </EMPNAME>
<EMPNAME> EARLEAN </EMPNAME>
<EMPNAME> VINCENZA</EMPNAME>

Step 2: Once the string is converted into XML, use X-Query to query the XML file. To do that, execute the following code:

USE DEMODATABASE 
GO 

DECLARE @XML       AS XML, 
        @STR       AS VARCHAR(MAX), 
        @DELIMITER AS VARCHAR(10) 

SET @STR=(SELECT EMPLOYEE_NAME 
          FROM   EMPLOYEE) 
SET @DELIMITER =',' 
SET @XML = CAST(( '<EMPNAME>' 
                  + REPLACE(@STR, @DELIMITER, '</EMPNAME><EMPNAME>') 
                  + '</EMPNAME>' ) AS XML) 

SELECT N.VALUE('.', 'VARCHAR(10)') AS VALUE 
FROM   @XML.NODES('EMPNAME') AS T(N)

The following is the output:

Step 3: Insert the output generated by the query executed above into the Employee_Detail table. To do that, execute the following code:

USE DEMODATABASE
GO
DECLARE @XML AS XML,@STR AS VARCHAR(MAX),@DELIMITER AS VARCHAR(10)
SET @STR=(SELECT EMPLOYEE_NAME FROM EMPLOYEE)
SET @DELIMITER =','
SET @XML = CAST(('<EMPNAME>'+REPLACE(@STR,@DELIMITER ,'</EMPNAME><EMPNAME>')+'</EMPNAME>') AS XML)
INSERT INTO EMPLOYEE_DETAIL (EMPNAME)
SELECT N.VALUE('.', 'VARCHAR(10)') AS VALUE FROM @XML.NODES('EMPNAME') AS T(N)
/*Output
 (9 rows affected)
 */

Once data is inserted, execute the following script to verify that the data has been inserted. Execute the following query:

USE DEMODATABASE 
GO 
SELECT * 
FROM   EMPLOYEE_DETAIL

The following is the output.

Method 3: Split string using table-valued function and insert the output of the function in the table

This approach is traditional, and is supported in all versions and editions of SQL Server. In this approach, we will create a user-defined table-valued function which will use while loop and CHARINDEX and SUBSTRING function.

The following is the code to create a function:

REATE FUNCTION [DBO].SPLIT_DELIMITED_STRING (@SQLQUERY  VARCHAR(MAX), 
                                              @DELIMITOR CHAR(1)) 
RETURNS @RESULT TABLE( 
  VALUE VARCHAR(MAX)) 
AS 
  BEGIN 
      DECLARE @DELIMITORPOSITION INT = CHARINDEX(@DELIMITOR, @SQLQUERY), 
              @VALUE             VARCHAR(MAX), 
              @STARTPOSITION     INT = 1 

      IF @DELIMITORPOSITION = 0 
        BEGIN 
            INSERT INTO @RESULT 
            VALUES     (@SQLQUERY) 

            RETURN 
        END 

      SET @SQLQUERY = @SQLQUERY + @DELIMITOR 

      WHILE @DELIMITORPOSITION > 0 
        BEGIN 
            SET @VALUE = SUBSTRING(@SQLQUERY, @STARTPOSITION, 
                         @DELIMITORPOSITION - @STARTPOSITION) 

            IF( @VALUE <> '' ) 
              INSERT INTO @RESULT 
              VALUES     (@VALUE) 

            SET @STARTPOSITION = @DELIMITORPOSITION + 1 
            SET @DELIMITORPOSITION = CHARINDEX(@DELIMITOR, @SQLQUERY, 
                                     @STARTPOSITION) 
        END 

      RETURN 
  END

Once the function is created, execute the following query to split the query and insert the output into the Employee_Detail table.

DECLARE @SQLQUERY NVARCHAR(MAX) 
SET @SQLQUERY=(SELECT EMPLOYEE_NAME 
               FROM   EMPLOYEE) 
INSERT INTO EMPLOYEE_DETAIL 
SELECT * 
FROM   SPLIT_DELIMITED_STRING(@SQLQUERY, ',')

Once data is inserted into the table, execute the following query to verify that data has been inserted properly

Summary

In this article, I have covered:

  1. Different approach to split and insert the delimited string in table.
  2. High level is summary of STRING_SPLIT function.
  3. Split and insert a delimited string using XML and XQuery.
  4. Split and insert delimited string using a user-defined table-valued function.

The post Several Ways to Insert Split Delimited Strings in a Column appeared first on {coding}Sight.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Are you a .NET developer who is stuck in great confusion when it comes to choosing Azure Active Directory or Microsoft Graph?

We all have look at those multiple posts on which you can choose for better web development. In this article, we will be providing some guidance along with a bit of roadmap to clarify things for all of the existing and new developers who want to access directory-based features. Let us begin.

What makes a Difference?

We will begin by looking at some of the different functionalities which makes both of them unique at their own pace. Both of them are defined as two Application Programming Interface (API) with different functionalities.

Microsoft Graph is mostly supporting all of the Azure AD Graph features. It also supports some additional feature like $select projection queries which is not implemented in Azure AD Graph.

Indeed, Microsoft is working harder to close the feature gap between the Microsoft Graph and Azure AD Graph hence making it easier for the developers to use any of them. In present times, all the new applications or integrating an existing app with the cloud is implemented using the Microsoft Graph. The applications which are already running on Azure Active Directory Graph does not get affected as it remains fully functional for these applications.

Make sure that Azure AD Graph client library is only made available for .NET applications while Microsoft Graph client libraries are supporting multiple platforms and languages by giving more choices to use directory data in your application.

What is a Microsoft Graph?

Microsoft Graph is an Application Programming Interface that provides a programming model in order to connect Office 365, Azure Active Directory, Enterprise security services and Windows 10. The API is used to build applications for the users to make them interact with the millions of data to access resources with just a single endpoint. Being a RESTful web API, you can also enjoy the cloud services. All you need to do is register your application with Azure AD and then request Microsoft Graph API for accessing authentication tokens for the user.

This API uses relationships to connect the resources under its services. For instance – By using the manager relationship, you can connect a member from a relationship to the user or the group. With the help of Microsoft Graph, you can also get some important insights. For instance – you can access all the files that are trending around a user.

Components for Microsoft Graph API request

To make a request in Microsoft Graph, you need to use HTTP methods. You simply do not require to get any request body for the GET and DELETE methods. But, you require the request body for PATCH, PUT and POST methods that will return in JSON format which contains some extra information on values and properties.

MethodDescription
GETGet data from the resource
POSTCreate a new resource
PATCHModify resource with the latest values
PUTReplace resource with a new one
DELETEDelete a resource
Quick Features of Microsoft Graph

To add value to your application and build some friendly user context experiences, you can use the Microsoft Graph.

  • Simplify the user onboarding by automation tasks which include assigning a role to users, provide permissions to documents, and assign product licenses, change users role and much more.
  • Retrieve your Excel workbook data to build some powerful workflow in order to integrate data collection with cloud services like SharePoint.
  • It allows searching meeting times by providing profile information for one or more attendees.
  • You also get recommendations on meeting times by scanning your calendar that contains attendees and their preferred timeslots.
  • It supports file format conversion to PDF whereas other file types like Excel, Word, PowerPoint, CSV, and RTF can also be converted.
  • Allows you to manage employee profile with the help of Azure directory up-to-date and you can also modify user profile information which is stored in SharePoint via API.
  • Receive notifications if any changes occur in your calendar if you spend too much time in meetings based on the relevancy of your attendees.
  • Sync and Sort your personal data from the mobile phone.
RequestEndpoint URL
Get my profilehttps://graph.microsoft.com/v1.0/me
Get my photohttps://graph.microsoft.com/v1.0/me/photo/$value
Get my fileshttps://graph.microsoft.com/v1.0/me/drive/root/children
Get my emailhttps://graph.microsoft.com/v1.0/me/messages
Get my calendar eventsttps://graph.microsoft.com/v1.0/me/events
Get my managerhttps://graph.microsoft.com/v1.0/me/manager
Azure Active Directory Graph API

This Application Programming Interface is enabled to access the objects of Azure AD by making use of REST API endpoint. Moreover, you can also use Azure AD API to perform CRUD operations and also supports some common operations on the Azure AD data and objects by creating a new user in Azure AD and get the properties of a user such as where does the group user belong to along with their email address, location, updated details, phone number and account status.

Quick Features of Azure AD Graph

You must register your mobile application with the Azure AD to get consent for your app before calling out the Azure AD Graph API on a directory.

  • The API is comprising of RESTful APIs which can be accessed with the help of HTTP requests and also supports XML and JSON formats.
  •  The API request appends a JSON Web Token (JWT) in its authentication header by acquiring the token to request for Azure AD’s token endpoint.
  • Makes use of security groups to perform Role-Based Authorization. For instance – You can call Check Group Membership Boolean Operation in your app in order to check the user’s access to the specific resource.
  • Differential query tracks the changes in a directory between two durations without any need of an independent query to API which will return modifications that are made between the previous and active requests.
  • You can make use of custom properties with Azure directory objects to access properties from the external data source like Skype.
  • The API lets you use permission scopes to enable secure access to directory data by using OAuth 2.0 that supports two types of permissions.
  • Users get the delegated access to data through authorization from the signed-in user whereas role-based access control is defined by the application without authorization from the signed-in user.
What’s the Difference between Azure AD and Microsoft Graph?

We already know that Microsoft Graph API supports all the features but Azure AD Graph API supports less than the other. Let us see them both.

FeatureAzure AD Graph capability
Microsoft Graph capability
Endpointhttps://graph.windows.nethttps://graph.microsoft.com
Delta SyncDifferential query/delta sync available for users and groups• General Availability with Delta query
• Organizational contacts delta sync is not supported.
• Sync from now is still not supported
Organizational Contact Resource TypeSupportedIn the preview stage
Applications ManagementApplications management such as
• Application and service principal entity types
• Assigning applications to users and groups
• Assigning OAuth permissions to applications
Available in preview
Partner Admin supportYesGeneral Availability
Domain resource typeYesGeneral Availability
Contracts resource typeYesGeneral Availability
Directory schema extension definition registrationSupportedGeneral availability.
Supports extending resources with application data.
Does not support application or service principal resource type
BatchingYesAvailable in preview
PropertiesProperties are missing on user
Resources such as sipProxyAddress,
otherEmails, licenseDetails
General availability.
otherEmails still not supported, but planned for future
Get ObjectGetObjectsByObjectsIds methodGeneral availability. getByIds method
Check MembershipIsMemberOf methodUnplanned. Need to use the checkMemberGroups method
Users ManagementUsers management in B2C tenantComing soon in preview
Wrapping Up!

Microsoft is recommending to use Microsoft Graph in place of the Azure Active Directory Graph API in order to access Azure AD’s resources. The overall Microsoft development is focused on Microsoft Graph as there is no further extension planned for Azure AD Graph API for the future purpose. Keep Learning!

The post Microsoft Graph or Azure Active Directory Graph API: Which is better? appeared first on {coding}Sight.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This article is about T-SQL (Transact-SQL) Window functions and their basic use in day-to-day data analysis tasks.

There are many alternatives to T-SQL when it comes to data analysis. However, when improvements over time and introduction of Window functions are considered, T-SQL is capable of performing data analysis on a basic level and, in some cases, even beyond that.

About SQL Window Functions

First, let’s first get familiar with SQL Window functions in the context of the Microsoft documentation.

Microsoft Definition

A window function computes a value for each row in the window.

Simple Definition

A window function helps us to focus on a particular portion (window) of the result set so that we can perform data analysis on that specific part (window) only, rather than on the entire result set.

In other words, SQL window functions turn a result set into several smaller sets for data analysis purposes.

What is a result set

Simply put, a result set consists of all records retrieved by running a SQL query.

For example, we can create a table named Product and insert the following data into it:

-- (1) Create the Product table
CREATE TABLE [dbo].[Product]
(
	[ProductId] INT NOT NULL PRIMARY KEY,
	[Name] VARCHAR(40) NOT NULL,
	[Region] VARCHAR(40) NOT NULL
)

-- (2) Populate the Product table
INSERT INTO Product
(ProductId,Name,Region)
VALUES
(1,'Laptop','UK'),(2,'PC','UAE'),(3,'iPad','UK')

Now, the result set retrieved by using the script below will contain all rows from the Product table:

-- (3) Result set
SELECT [ProductId], [Name],[Region] FROM Product

What is a Window

It is important to first understand the concept of a window as it relates to SQL window functions. In this context, a window is just a way of narrowing down your scope by targeting a specific part of the result set (as we already mentioned above).

You may be wondering now – what does ‘targeting a specific part of the result set’ actually means?

Returning to the example we looked at, we can create a SQL window based on the product region by dividing the result set into two windows.

Understanding Row_Number()

To proceed, we will need to use the Row_Number() function which temporarily gives a sequence number to the output rows.

For example, if we want to add a row numbers to the result set based on ProductID, we’ll need to use ROW_NUMBER() to order it by Product ID as follows:

--Using the row_number() function to order the result set by ProductID
SELECT ProductID,ROW_NUMBER() OVER (ORDER BY ProductID) AS SrNo,Name,Region FROM Product

Now, if we want the Row_Number() function to order the result set by ProductID descending, then the sequence of output rows based on ProductID will change as follows:

--Using the row_number() function to order the result set by ProductID descending
SELECT ProductID,ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SrNo,Name,Region FROM Product

There are no SQL windows yet since the only thing we’ve done is ordering the set by specific criteria. As discussed earlier, windowing means breaking the result set into several smaller sets to analyze each one of them separately.

Creating a Window with Row_Number()

To create a SQL window in our result set, we will need to to partition it based on any of the columns it contains.

We can now partition the result set by region as follows:

--Creating a SQL window based on Region
SELECT ROW_NUMBER() OVER (Partition by region ORDER BY Region) as Region_Serial_Number
, Name, Region FROM dbo.Product

Select – Over Clause

In other words, Select with the Over clause paves the way for SQL window functions by partitioning a result set into smaller windows.

According to the Microsoft documentation, Select with the Over clause defines a window which can then be used by any window function.

Now, let’s create a table called KitchenProduct as follows:

CREATE TABLE [dbo].[KitchenProduct]
(
	[KitchenProductId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	[Name] VARCHAR(40) NOT NULL,
	[Country] VARCHAR(40) NOT NULL,
	[Quantity] INT NOT NULL,
	[Price] DECIMAL(10,2) NOT NULL
);
GO

INSERT INTO dbo.KitchenProduct
(Name, Country, Quantity, Price)
VALUES
('Kettle','Germany',10,15.00)
,('Kettle','UK',20,12.00)
,('Toaster', 'France',10,10.00)
,('Toaster','UAE',10,12.00)
,('Kitchen Clock','UK',50,20.00)
,('Kitchen Clock','UAE',35,15.00)

Now, let’s view the table:

SELECT [KitchenProductId], [Name], [Country], [Quantity], [Price] FROM dbo.KitchenProduct

If you want to see each product with its own serial number rather than a number based on the generalized product ID, then you would have to use a SQL window function to partition the result set by product as follows:

-- Viewing each product in its own series
SELECT ROW_NUMBER() OVER (Partition by Name order by Name) Product_SrNo,Name,Country,Quantity
FROM dbo.KitchenProduct

Compatibility (Select – Over Clause)

According to Microsoft documentation, Select – Over Clause is compatible with the following SQL database versions:

  1. SQL Server 2008 and upward
  2. Azure SQL Database
  3. Azure SQL Data Warehouse
  4. Parallel Data Warehouse
Syntax

SELECT – OVER (Partition by <column> Order by <column>)

Please note that I have simplified the syntax to make it easy to understand; please refer to the Microsoft documentation to see the full syntax.

Pre-requisites

This article is basically written for beginners, but there are still some pre-requisites which must be kept in mind.

Familiarity with T-SQL

This article assumes that the readers have a basic knowledge of T-SQL and are capable of writing and running basic SQL scripts.

Setup the Sales sample table

This article requires the following sample table so that we can run our SQL window function examples:

-- (1) Create the Sales sample table
CREATE TABLE [dbo].[Sales]
(
	[SalesId] INT NOT NULL IDENTITY(1,1), 
    [Product] VARCHAR(40) NOT NULL,
	[Date] DATETIME2,
	[Revenue] DECIMAL(10,2), 
    CONSTRAINT [PK_Sales] PRIMARY KEY ([SalesId])
);
GO

-- (2) Populating the Sales sample table
SET IDENTITY_INSERT [dbo].[Sales] ON
INSERT INTO [dbo].[Sales] ([SalesId], [Product], [Date], [Revenue]) VALUES (1, N'Laptop', N'2017-01-01 00:00:00', CAST(200.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sales] ([SalesId], [Product], [Date], [Revenue]) VALUES (2, N'PC', N'2017-01-01 00:00:00', CAST(100.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sales] ([SalesId], [Product], [Date], [Revenue]) VALUES (3, N'Mobile Phone', N'2018-01-01 00:00:00', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sales] ([SalesId], [Product], [Date], [Revenue]) VALUES (4, N'Accessories', N'2018-01-01 00:00:00', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sales] ([SalesId], [Product], [Date], [Revenue]) VALUES (5, N'iPad', N'2019-01-01 00:00:00', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sales] ([SalesId], [Product], [Date], [Revenue]) VALUES (6, N'PC', N'2019-01-01 00:00:00', CAST(200.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sales] ([SalesId], [Product], [Date], [Revenue]) VALUES (7, N'Laptop', N'2019-01-01 00:00:00', CAST(300.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT [dbo].[Sales] OFF

View all sales by running the following script:

-- View sales
SELECT
  [SalesId],[Product],[Date],[Revenue]
FROM dbo.Sales

Group By vs SQL Window Functions

One can wonder – what is the difference between using the Group By clause and SQL window functions?

Well, the answer lies in the examples below.

Group By Example

In order to see total sales by product, we can use Group By as follows:

-- Total sales by product using Group By
SELECT
  Product
 ,SUM(REVENUE) AS Total_Sales
FROM dbo.Sales
GROUP BY Product
ORDER BY Product

So, the Group By clause helps us see the total sales. The total sales value is the sum of revenue for all similar products in the same row with no Group By clause used. What if we are interested in seeing the revenue (sale) of each individual product along with total sales?

This is where SQL window functions come into action.

SQL Window Function Example

In order to see the product, revenue and total revenue by all similar products, we have to partition the data on a by-product basis using OVER() as follows:

-- Total sales by product using an SQL window function
SELECT
  Product
 ,REVENUE
 ,SUM(REVENUE) OVER (PARTITION BY PRODUCT) AS Total_Sales
FROM dbo.Sales

The output should be as follows:

So, we can now easily see the sales for each individual product along with total sales for that product. For example, the revenue for PC is 100.00 but total sales (sum of revenue for the PC product) is 300.00 because two different PC models were being sold.

Basic Analysis with the Aggregate Functions

Aggregate functions return a single value after performing calculations on a set of data.

In this section, we are going to further explore SQL window functions – specifically, by using them along with aggregate functions to perform basic data analysis.

Common Aggregate Functions

The most common aggregate functions are:

  1. Sum
  2. Count
  3. Min
  4. Max
  5. Avg (Average)
Aggregate Data Analysis by Product

In order to analyse the result set on a by-product basis with the help of aggregate functions, we simply have to use an aggregate function with a by-product partition inside of the OVER() statement:

-- Data analysis by product using aggregate functions
SELECT Product,Revenue
,SUM(REVENUE) OVER (PARTITION BY PRODUCT) as Total_Sales 
,MIN(REVENUE) OVER (PARTITION BY PRODUCT) as Minimum_Sales 
,MAX(REVENUE) OVER (PARTITION BY PRODUCT) as Maximum_Sales 
,AVG(REVENUE) OVER (PARTITION BY PRODUCT) as Average_Sales 
FROM dbo.Sales

If you take a closer look at the PC or Laptop products, you will see how aggregate functions are working together alongside the SQL window function.

In the example above, we can see that the Revenue value for PC is 100.00 first time and 200.00 next time, but Total Sales amount to 300.00. The similar information can be seen for the rest of the aggregate functions.

Aggregate Data Analysis by Date

Now, let’s perform some data analysis of the products on a by-date basis using SQL window functions in combination with aggregate functions.

This time, we are going to partition the result set by date rather than by product as follows:

-- Data analysis by date using aggregate functions
SELECT Product,date,Revenue
,SUM(REVENUE) OVER (PARTITION BY DATE) as Total_Sales 
,MIN(REVENUE) OVER (PARTITION BY DATE) as Minimum_Sales 
,MAX(REVENUE) OVER (PARTITION BY DATE) as Maximum_Sales 
,AVG(REVENUE) OVER (PARTITION BY DATE) as Average_Sales 
FROM dbo.Sales

With this, we have learned basic data analysis techniques using the SQL window functions approach.

Things to do

Now that you are familiar with SQL window functions, please try the following:

  1. Keeping in mind the examples we looked at, perform basic data analysis using SQL window functions on the sample database mentioned in this article.
  2. Adding a Customer column to the Sales sample table and see how rich your data analysis can become when another column (customer) is added to it.
  3. Adding a Region column to the Sales sample table and perform basic data analysis using aggregate functions by region.

The post Learn Basic Data Analysis with SQL Window Functions appeared first on {coding}Sight.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In my previous article, I explained the basics of set operators, their types, and prerequisites for their use. I also talked about UNION and UNION ALL operators, their usage and differences.

In this article, we’re going to learn the following:

  1. EXCEPT and INTERSECT operators.
  2. Difference between INTERSECT and INNER JOIN.
  3. The detailed explanation of INTERSECT and EXCEPT with an example.

EXCEPT and INTERSECT operators were introduced in SQL Server 2005. Both are set operators used to combine the result sets generated by two queries and retrieve the desired output.

What is the INTERSECT operator

INTERSECT is used to get records common to all data sets retrieved from multiple queries or tables. Here’s a visualization of this:

The syntax of the INTERSECT operator is a follows:

SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3, 
       COLUMN4..FROM TABLE1 
INTERSECT
SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3, 
       COLUMN4..FROM TABLE2

What is the EXCEPT operator

EXCEPT is used to retrieve records which are found in one query but not in another query. In other words, it returns records which are unique to one result set. This is what it looks like visualized:

The syntax of the EXCEPT operator is as follows:

SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3, 
       COLUMN4..FROM TABLE1 
EXCEPT
SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3, 
       COLUMN4..FROM TABLE2

Let’s create a demo setup to demonstrate how these operators can be used.

Demo Setup

To demonstrate INTERSECT and EXCEPT, I created two tables named Employee and Trainee.

Execute the following query to create these tables:

CREATE TABLE [DBO].[EMPLOYEE] 
  ( 
     [NAME]             [NVARCHAR](250) NOT NULL, 
     [BUSINESSENTITYID] [INT] NOT NULL, 
     [NATIONALIDNUMBER] [NVARCHAR](15) NOT NULL, 
     [LOGINID]          [NVARCHAR](256) NOT NULL, 
     [BIRTHDATE]        [DATE] NOT NULL, 
     [MARITALSTATUS]    [NCHAR](1) NOT NULL, 
     [GENDER]           [NCHAR](1) NOT NULL 
  ) 
ON [PRIMARY] 

CREATE TABLE [DBO].[TRAINEE] 
  ( 
     [NAME]             [NVARCHAR](250) NOT NULL, 
     [BUSINESSENTITYID] [INT] NOT NULL, 
     [NATIONALIDNUMBER] [NVARCHAR](15) NOT NULL, 
     [BIRTHDATE]        [DATE] NOT NULL, 
     [GENDER]           [NCHAR](1) NOT NULL 
  ) 
ON [PRIMARY]
Now, let’s insert some dummy data into the Employee table by executing the following query:
INSERT [DBO].[EMPLOYEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [LOGINID], [BIRTHDATE], [MARITALSTATUS], [GENDER]) VALUES (N'KEN SÁNCHEZ', 1, N'295847284', N'ADVENTURE-WORKS\KEN0', CAST(N'1969-01-29' AS DATE), N'S', N'M')
GO
INSERT [DBO].[EMPLOYEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [LOGINID], [BIRTHDATE], [MARITALSTATUS], [GENDER]) VALUES (N'TERRI DUFFY', 2, N'245797967', N'ADVENTURE-WORKS\TERRI0', CAST(N'1971-08-01' AS DATE), N'S', N'F')
GO
INSERT [DBO].[EMPLOYEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [LOGINID], [BIRTHDATE], [MARITALSTATUS], [GENDER]) VALUES (N'ROBERTO TAMBURELLO', 3, N'509647174', N'ADVENTURE-WORKS\ROBERTO0', CAST(N'1974-11-12' AS DATE), N'M', N'M')
GO
INSERT [DBO].[EMPLOYEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [LOGINID], [BIRTHDATE], [MARITALSTATUS], [GENDER]) VALUES (N'ROB WALTERS', 4, N'112457891', N'ADVENTURE-WORKS\ROB0', CAST(N'1974-12-23' AS DATE), N'S', N'M')
GO
INSERT [DBO].[EMPLOYEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [LOGINID], [BIRTHDATE], [MARITALSTATUS], [GENDER]) VALUES (N'GAIL ERICKSON', 5, N'695256908', N'ADVENTURE-WORKS\GAIL0', CAST(N'1952-09-27' AS DATE), N'M', N'F')
GO
INSERT [DBO].[EMPLOYEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [LOGINID], [BIRTHDATE], [MARITALSTATUS], [GENDER]) VALUES (N'JOSSEF GOLDBERG', 6, N'998320692', N'ADVENTURE-WORKS\JOSSEF0', CAST(N'1959-03-11' AS DATE), N'M', N'M')
Next, we’ll do the same thing for the Trainee table:
INSERT [DBO].[TRAINEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [BIRTHDATE], [GENDER]) VALUES (N'JOHN WOOD', 18, N'222969461', CAST(N'1978-03-06' AS DATE), N'M')
GO
INSERT [DBO].[TRAINEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [BIRTHDATE], [GENDER]) VALUES (N'MARY DEMPSEY', 19, N'52541318', CAST(N'1978-01-29' AS DATE), N'F')
GO
INSERT [DBO].[TRAINEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [BIRTHDATE], [GENDER]) VALUES (N'WANIDA BENSHOOF', 20, N'323403273', CAST(N'1975-03-17' AS DATE), N'F')
GO
INSERT [DBO].[TRAINEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [BIRTHDATE], [GENDER]) VALUES (N'KEN SÁNCHEZ', 1, N'295847284', CAST(N'1969-01-29' AS DATE), N'M')
GO
INSERT [DBO].[TRAINEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [BIRTHDATE], [GENDER]) VALUES (N'TERRI DUFFY', 2, N'245797967', CAST(N'1971-08-01' AS DATE),  N'F')
GO
INSERT [DBO].[TRAINEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [BIRTHDATE], [GENDER]) VALUES (N'ROBERTO TAMBURELLO', 3, N'509647174', CAST(N'1974-11-12' AS DATE), N'M')
GO
Now, let’s use INTERSECT to retrieve the list of employees which are common to both tables. To do that, run the following query:
SELECT NAME, 
       BUSINESSENTITYID, 
       NATIONALIDNUMBER, 
       BIRTHDATE, 
       GENDER 
FROM   EMPLOYEE 
INTERSECT 
SELECT NAME, 
       BUSINESSENTITYID, 
       NATIONALIDNUMBER, 
       BIRTHDATE, 
       GENDER 
FROM   TRAINEE
The output of this query should be as follows:

As you can see in the screenshot above, the query has only returned records which are common to both tables.

INNER JOIN vs. INTERSECT

In most cases, INTERSECT and INNER JOIN return the same output, but there are some exceptions. A simple example will help us understand this.

Let’s add some duplicate records to the Trainee table. Execute the following query:

INSERT [DBO].[TRAINEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [BIRTHDATE], [GENDER]) VALUES (N'TERRI DUFFY', 2, N'245797967', CAST(N'1971-08-01' AS DATE),  N'F')
GO
INSERT [DBO].[TRAINEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [BIRTHDATE], [GENDER]) VALUES (N'ROBERTO TAMBURELLO', 3, N'509647174', CAST(N'1974-11-12' AS DATE), N'M')
GO

Now, we’ll try to generate the desired output using INTERSECT.

SELECT NAME,BUSINESSENTITYID,NATIONALIDNUMBER,BIRTHDATE,GENDER FROM EMPLOYEE
INTERSECT
SELECT NAME,BUSINESSENTITYID,NATIONALIDNUMBER,BIRTHDATE,GENDER FROM TRAINEE

This is the output we get:

Now, let’s try using INNER JOIN.

SELECT A.NAME, 
       A.BUSINESSENTITYID, 
       A.NATIONALIDNUMBER, 
       A.BIRTHDATE, 
       A.GENDER 
FROM   EMPLOYEE A 
       INNER JOIN TRAINEE B 
               ON A.NAME = B.NAME

The output we get in this case is as follows:

Now, as you can see on the screenshot above, INNER JOIN retrieves records which are common to both tables. It populates all records from the right table. Therefore, you can see duplicate records.

Now, let’s add the DISTINCT keyword to the INNER JOIN query and look at what this does:

SELECT DISTINCT A.NAME, 
                A.BUSINESSENTITYID, 
                A.NATIONALIDNUMBER, 
                A.BIRTHDATE, 
                A.GENDER 
FROM   EMPLOYEE A 
       INNER JOIN TRAINEE B 
               ON A.NAME = B.NAME

The output should look like this:

As you can see on the screenshot above, duplicate records have been eliminated.

INTERSECT and INNER JOIN treat NULL values differently. For INNER JOIN, two NULL values are different, so there are chances that it will skip them while joining two tables.

On the other hand, INTERSECT treats two NULL values as being the same, so records that have NULL values won’t be eliminated. To understand it better, let’s look at an example.

First, let’s add some NULL values to the Trainee and Employee tables by executing the following query:

INSERT [DBO].[TRAINEE] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER], [BIRTHDATE], [GENDER]) VALUES (NULL, 3, N'509647174', CAST(N'1974-11-12' AS DATE), N'M')
GO

INSERT [DBO].[Employee] ([NAME], [BUSINESSENTITYID], [NATIONALIDNUMBER],[LOGINID], [BIRTHDATE],[MARITALSTATUS], [GENDER]) VALUES (NULL, 3, N'509647174','ADVENTURE-WORKS\TERRI0', CAST(N'1974-11-12' AS DATE),  N'M',N'M')
GO

Now let’s try to retrieve records common to the two tables using INTERSECT and INNER JOIN. You will need to execute the following query:

/*QUERY WITH INTERSECT*/ 
SELECT NAME, 
       BUSINESSENTITYID, 
       NATIONALIDNUMBER, 
       BIRTHDATE, 
       GENDER 
FROM   EMPLOYEE 
INTERSECT 
SELECT NAME, 
       BUSINESSENTITYID, 
       NATIONALIDNUMBER, 
       BIRTHDATE, 
       GENDER 
FROM   TRAINEE 

/*QUERY WITH INNER JOIN*/ 
SELECT A.NAME, 
       A.BUSINESSENTITYID, 
       A.NATIONALIDNUMBER, 
       A.BIRTHDATE, 
       A.GENDER 
FROM   EMPLOYEE A 
       INNER JOIN TRAINEE B 
               ON A.NAME = B.NAME

This is the output we should get as a result:

As you can see above, the result set generated by INTERSECT contains NULL values, while INNER JOIN skipped the records that have NULL values.

The EXCEPT Operator

To demonstrate the EXCEPT operator in action, let’s look at a use case. For example, I want to populate the details of female employees from the Employee table. The following query will help us do just that:

SELECT NAME, 
       BUSINESSENTITYID, 
       NATIONALIDNUMBER, 
       BIRTHDATE, 
       GENDER 
FROM   EMPLOYEE 
WHERE  GENDER = 'F' 
EXCEPT 
SELECT NAME, 
       BUSINESSENTITYID, 
       NATIONALIDNUMBER, 
       BIRTHDATE, 
       GENDER 
FROM   EMPLOYEE 
WHERE  GENDER = 'M'

This is the output we get:

As you can see above, the query populated only the female employees’ details.

You can also populate the result set using a sub-query:

SELECT NAME, 
       BUSINESSENTITYID, 
       NATIONALIDNUMBER, 
       BIRTHDATE, 
       GENDER 
FROM   EMPLOYEE AS M 
WHERE  GENDER = 'F' 
       AND GENDER NOT IN (SELECT GENDER 
                          FROM   EMPLOYEE AS F 
                          WHERE  GENDER = 'M')

Limitations of INTERSECT and EXCEPT

  1. We cannot use EXCEPT and INTERSECT in distributed partitioned view definitions with COMPUTE and COMPUTE BY clauses.
  2. EXCEPT and INTERSECT can be used in Fast forward-only and static cursors.
  3. EXCEPT and INTERSECT can be used in distributed queries, but can only be executed on the local server. You cannot run them on a remote server.
Summary

In this article, I have covered:

  1. The EXCEPT and INTERSECT operators.
  2. The difference between INTERSECT and INNER JOIN.
  3. A detailed explanation of the INTERSECT and EXCEPT operators with an example.

The post T-SQL SET Operators Part 2: INTERSECT and EXCEPT appeared first on {coding}Sight.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This article is focused on the T-SQL (Transact-SQL) IsNumeric function and its proper use in day-to-day SQL scripting tasks.

We will also see why it is important to understand how and why IsNumeric can be used – both incorrectly and correctly.

There may be some better alternatives to IsNumeric depending on the context. However, in the cases we’re going to cover in this article, I see this function as the best possible choice.

About IsNumeric

First of all, let’s get familiar with IsNumeric. Here’s what we can say about it after reading the related information in the Microsoft documentation:

Microsoft Definition

The IsNumeric function determines whether an expression can be evaluated as a number. Here, the expression can consist of symbols and operators which are evaluated as a single data value by SQL Server Database Engine.

Please refer to my article Basic to Complex Uses of Not Equal in T-SQL to get more information about expressions and operators.

Simple Definition

The IsNumeric function tells us if something we passed to it (a variable, constant or a column) is a number or not.

How Does IsNumeric Work

It is quite obvious what the function does based on the definition. However, it may not be clear how to get the result of this function.

In reality, it’s also rather simple – IsNumeric returns 1 if the expression we passed to it is numeric or 0 if it’s not.

Dry Run Example

One of the simplest examples we can come up with is passing the number 10 to the function to check how it works:

--Dry Run ISNUMERIC Function
SELECT ISNUMERIC(10) AS ISNUMERIC_10_Result

When we run the script, the following output is generated:

Since IsNumeric returns ‘1’ when 10 is passed to it as an expression, we can be sure that 10 is a numeric value. Of course, we already know it. But, in this way, we can get a basic understanding of how this function works.

Compatibility

According to Microsoft documentation, the IsNumeric function is compatible with the following SQL database versions:

  1. SQL Server 2008 and upward
  2. Azure SQL Database
  3. Azure SQL Data Warehouse
  4. Parallel Data Warehouse
Syntax

ISNUMERIC expression (constant, variable, column etc.)

Pre-requisites

This article is mostly written for beginners, but there are still some pre-requisites which must be kept in mind if you want to understand it better.

Familiarity with T-SQL

This article assumes that the readers have a basic T-SQL know-how and are capable of writing and running basic SQL scripts.

Setup Sample Database (SQLDevBlogV3)

This article requires the SQLDevBlogV3 sample database to be set. This database consists of three tables:

  1. Article
  2. Author
  3. Category

You can setup the SQLDevBlogV3 database by running the following code:

Code
-- Create sample database (SQLDevBlogV3)
CREATE DATABASE SQLDevBlogV3;
GO


USE SQLDevBlogV3;

-- (1) Create the Author table in the sample database
CREATE TABLE Author (
  AuthorId INT PRIMARY KEY IDENTITY (1, 1)
 ,Name VARCHAR(40)
 ,RegistrationDate DATETIME2
 ,TotalArticles INT
 ,Notes VARCHAR(400)
)

-- (2) Create the Category table in the sample database
CREATE TABLE Category (
  CategoryId INT PRIMARY KEY IDENTITY (1, 1)
 ,Name VARCHAR(50)
 ,Notes VARCHAR(400)
)

-- (3) Create the Article table in the sample database
CREATE TABLE Article (
  ArticleId INT PRIMARY KEY IDENTITY (1, 1)
 ,CategoryId INT
 ,AuthorId INT
 ,Title VARCHAR(150)
 ,Published DATETIME2
 ,Notes VARCHAR(400)  
)

-- Adding foreign keys for the Author and Article categories
ALTER TABLE Article ADD CONSTRAINT FK_Category_CategoryId FOREIGN KEY (CategoryId) REFERENCES Category (CategoryId)
ALTER TABLE Article ADD CONSTRAINT FK_Author_AuthorId FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId)

GO


-- (5) Populating the Author table
INSERT INTO Author (Name, RegistrationDate, TotalArticles,Notes)
  VALUES ('Sam', '2017-01-01', 20,'Database Analyst'),
  ('Asif', '2017-01-02',15, 'Database and Business Intelligence Developer'),
  ('Sadaf', '2018-01-01', 30,'Database Analyst Programmer')

-- (6) Populating the Category table
INSERT INTO Category (Name, Notes)
  VALUES ('Development', 'Articles about database development'),
  ('Testing', 'Database testing related articles'),
  ('DLM', 'Database lifecycle management')

-- (7) Populating the Article table
INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes)
  VALUES (1, 1, 'Fundamentals of SQL Database Development', '02-01-2018', ''),
  (1, 2, 'Advanced Database Development', '02-01-2018', ''),
  (2, 3, 'All About Database Testing', '03-01-2018', '');
GO

When this is done, you can view all authors by running the following script:

-- View all authors
SELECT [AuthorId]
      ,[Name]
      ,[RegistrationDate]
      ,[TotalArticles]
      ,[Notes]	
  FROM [dbo].[Author]

Improper Use of the IsNumeric function

In this section, we are going to explore the improper use of IsNumeric.

Please remember that the upcoming examples should be ran against the SQLDevBlogV3 database we created earlier.

In the sample database, we have the Author table which contains the list of all registered authors.

Adding Authors

Let’s suppose that a business requirement to add new authors was sent to the development team.

Creating the AddAuthor Stored procedure

Attempting to complete the business requirement, we will create a stored procedure which is capable of adding new authors to the Author table.

The AddAuthor stored procedure can be created as follows:

-- Creating a stored procedure to add new authors to the Author table
CREATE PROCEDURE AddAuthor
@Name VARCHAR(40),
@RegistrationDate DATETIME2,
@TotalArticles INT,
@Notes VARCHAR(400)
AS
SET NOCOUNT ON
INSERT INTO dbo.Author
(
  Name
 ,RegistrationDate
 ,TotalArticles
 ,Notes
)
VALUES
(
  @Name 
 ,@RegistrationDate 
 ,@TotalArticles 
 ,@Notes 
);
GO

Adding a New Author by Using the AddAuthor Procedure

Now. let’s add a new author named Adil by using the stored procedure we have just written:

-- Adding a new author by using the AddAuthor stored procedure
EXEC dbo.AddAuthor "Adil","2019-01-03",18,"SQL Developer"

View all authors to see the newly-added one:

-- View all authors
SELECT [AuthorId]
      ,[Name]
      ,[RegistrationDate]
      ,[TotalArticles]
      ,[Notes]
  FROM [dbo].[Author]

The output is shown below:

As you can see, the newly-added author is now dsiplayed in the complete list of authors.

Adding A New Author by Supplying TotalArticles in Words

Now, let’s add another author – but this time we are purposely inserting a Total Articles value expressed in words rather than as a number:

-- Adding a new author while expressing TotalArticles in words rather than as a number
EXEC dbo.AddAuthor "Peter","2019-01-03",'Three','Business Intelligence Developer'

Running the script results in the following error:

This error was caused because we passed a string value (‘Three’) as an argument to the stored procedure. A value of numeric type (INT) is expected for Total Articles since we know that the TotalArticle column in our table has the numeric type.

It might be possible to use the IsNumeric function to avoid this conversion error.

Should We Use IsNumeric to Check the TotalArticles Value?

One might think, this means we have to modify our AddAuthor stored procedure. Specifically, making it check if the value supplied for TotalArticles is numeric or not and, if it is, process the request to be added to the Author table.

However, the IsNumeric function will not really help in this scenario due to the following:

  1. The argument of incorrect data type will cause the AddAuthor stored procedure to instantly throw an error, so trying to check the supplied argument within the stored procedure is useless
  2. The IsNumeric function is not really meant to check data types of arguments passed to stored procedures – rather, it has a different objective

We can handle this scenario by using the TRY…CATCH construct which is capable of catching the error before it is thrown by the system (further explanation is beyond the scope of this article).

Proper Use of the IsNumeric function

Now we know that IsNumeric should not be used to validate the data type of parameters passed to stored procedures.

The proper use of IsNumeric function would be to make it check if an expression (e.g., a variable) is numeric or not.

Now, we will implement IsNumeric in a proper and suitable scenario.

Importing Inconsistent Age Column Values

Let’s suppose that this time around we are importing less consistent Age data about authors from an Excel sheet.

The Age column has mixed value types:

Creating and Populating the AuthorAge Table

This inconsistent table is imported into the sample database:

--Creating the AuthorAge table
CREATE TABLE AuthorAge (
  AuthorId INT 
 ,Age VARCHAR(50) NOT NULL
 ,CONSTRAINT PK_AuthorAge_AuthorId PRIMARY KEY CLUSTERED (AuthorId)
) ON [PRIMARY]
GO

-- Populating AuthorAge with data from our Excel file
INSERT INTO [dbo].[AuthorAge]
           ([AuthorId]
           ,[Age])
     VALUES
           (1,'32'),
		   (2,'Twenty Nine'),
		   (3,'27'),
		   (4,'34');

GO

Now, let’s view the contents of this table:

--View the AuthorAge table
SELECT AuthorId,Age FROM dbo.AuthorAge

Adding the Age column to the Author table

Since we are planning to merge the AuthorAge table with the Author table, we will need to add the Age column to the Author table by running the following script:

-- Adding the Age column to the Author table
ALTER TABLE dbo.Author
  ADD Age INT NULL;
GO

Viewing authors with numeric Age values by using ISNUMERIC

We require the IsNumeric function to only choose those authors whose age is expressed in numbers. This can be achieved by the following script:

--View authors with age expressed in numbers using ISNUMERIC
SELECT A.AuthorId,A.Name,A.RegistrationDate, AA.Age,A.TotalArticles
FROM Author A INNER JOIN AuthorAge AA
ON A.AuthorId=AA.AuthorId
WHERE ISNUMERIC(AA.Age)=1

The results are as follows:

Using ISNUMERIC to Merge AuthorAge with Author table

We are going to merge the AuthorAge table with the Author table, but some work should be done in order to get only numeric age values from AuthorAge and join it with the Age column in the Author table based on Author Id.

IsNumeric comes into action as soon as you run the following SQL script against the sample database:

--Merging the AuthorAge and Author tables; only records with numeric age values are included
UPDATE Author
SET Age=AA.Age
FROM Author A INNER JOIN AuthorAge AA
ON A.AuthorId=AA.AuthorId
WHERE ISNUMERIC(AA.Age)=1

Now, let’s view all authors whose age is expressed in numbers and who were successfully copied from another table by executing the following code:

-- View all authors with numeric age values
SELECT A.AuthorId,A.Name,A.RegistrationDate, A.Age,A.TotalArticles FROM Author A

With this, you now know how to use the IsNumeric function to both manage tables with inconsistent data types and join them with another tables by filtering out the inconsistent data.

Things to do

Now that you are familiar with the proper use of IsNumeric, it is worth trying the following things to improve your knowledge further:

  1. Add four more records to the original Author table without the age value and then try to join two tables AuthorAge and AuthorAge2 by filtering out the records with age expressed in words and only allowing age expressed in numbers.
  2. Create a table named Data_For_Review and put all records with numeric Age into this table to be reviewed by using IsNumeric.
  3. Create a stored procedure that will use the IsNumeric function to filter out inconsistent data and put this data into the Data_For_Review table.

The post How to Properly Use the T-SQL IsNumeric Function appeared first on {coding}Sight.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Introduction

Data types are attributes that specify the kind of data that objects such as columns, local variables, expressions, and parameters can hold. Across the RDBMS world, data types are typically grouped into string, numeric, and date data types.

T-SQL supports 6 date and time data types namely:

  1. Datetime
  2. Smalldatetime
  3. Date
  4. Time
  5. Datetime2
  6. Datetimeoffset

The first two data types are considered as legacy versions of the newer ones. In this article, we focus on the date data types and, specifically, on the datetime and datetime2 data types available in SQL Server. Table 1 gives details of the various date and time data types available in SQL Server.

S/NoData TypeStorage (bytes)Date RangeAccuracyEntry Format
1DATETIME8
01-Jan-1753 to 31-Dec-99993.333 msecs‘YYYYMMDD hh:mm:ss.nnn’
2SMALLDATETIME401-Jan-1900 to 06-Jun-2079
1 min‘YYYYMMDD hh:mm’
3DATE301-Jan-0001 to 31-Dec-9999
100 nsecs‘YYYY-MM-DD’
4TIME3 to 5N/A100 nsecs‘hh:mm:ss.nnnnnnn’
5DATETIME26 to 801-Jan-0001 to 31-Dec-9999100 nsecs‘YYYYMMDD hh:mm:ss.nnnnnnn’
6DATETIMEOFFSET8 to 1001-Jan-0001 to 31-Dec-9999100 nsecs‘YYYYMMDD hh:mm:ss.nnnnnnn[+|-] hh:mm’

Tab 1 Date and Time Data Types

Datetime and Datetime2

Datatime is a datatype that combines date with time in a 24-hour clock format. The date range supported in the datetime data type is as shown in Tab 1 and it has an accuracy of about 3 milliseconds.

Datetime2 is an extension of the datetime data type. It accommodates a wider range of possible values and has an accuracy of 100 nanoseconds which is much better than its predecessor. Another key aspect of the dattime2 data type is that the storage required ranges from 6 to 8 bytes depending on the precision you choose.

  • You can achieve a precision of 1 millisecond by allowing three decimal places on the seconds component. Each value will thus consume six bytes.
  • You can achieve a precision of 100 nanoseconds by allowing seven decimal places on the seconds component. Each value will thus consume eight bytes.
Demonstrations Insert Wrong Date Values

We create a table with the details shown in Listing 1 to carry out a few demonstrations which illustrate how to manipulate the datetime and datetime2 data types.

-- Listing 1 Create Table and insert Rows
-- Create Table with Data Types
use Practice2017
go
create table staffers (
fname varchar(50),
lname varchar(50),
JobTitle varchar(100),
DOB datetime,
PreciseDOB datetime2,
LastLoginTime time)
go

Then we try to populate the table with one row as shown in Listing 2 but we get the error shown in Fig. 1. The key word in the error message is “out-of-range” values. What it is saying is that her value we are trying to insert is either lower than 01-Jan-1753 or higher than 31-Dec-9999. In this case, the problem is that we have not used the recommended entry format of ‘YYYYMMDD hh:mm:ss.nnn’ (see Table 1). Reading the value ‘06101979‘, SQL Server assumes 0610 to be the year (matching YYYY). This error is not thrown for the datetime2 data type because the range for datetime2 is wider starting in the year 0001.

-- Listing 2 Insert Rows with Wrong Entry Format
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'06101979'
,'06101979'
,'8:00 AM'
)

Fig. 1 Error Returned for Datetime Column Insert Correct Date Values

We try to correct the problem by entering the correct entry format for the datetime column as shown in Listing 3. When we run the statement again, we get the error shown in Fig. 2. This error essentially is caused by the same failure to follow the Entry format specifications. However, the problem lies with the other part of the date ‘06101979‘ which matches with the entry format ‘YYYYMMDD hh:mm:ss.nnn’. In this case, SQL Server assumed 19 Is a month and 79 is a day of the month. Attempting this implicit conversion fails since neither of the preceding assertions is true.

-- Listing 3 Insert Rows with One Correct Entry Format
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'19791006'
,'01061979'
,'8:00 AM'
)

Fig. 2 Error Returned for Datetime2 Column

Listing 4 allows us to demonstrate the last assertion. The value 01101201 fits into the range for datetime2 and we are able to insert the row. This value translates to 1st December 0110 as we see in fig 3.

-- Listing 4: Insert Rows with Correct Date Format
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'19791006'
,'01101201'
,'8:00 AM')

-- Listing 5: Insert Rows with All Correct Entry Format
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'19791006'
,'19791006'
,'8:00 AM'
)

Verifying the Data Fig. 3 Querying the Data Set

When we query the staffers table we see clearly the precision of the datetime data type when compared to the datetime2 alternative. Let’s move on to something a little more sinister: Language settings. Take a look at Listing 6. We are inserting the exact same records using the date format 06/10/1979. This format is NOT language neutral thus when we set language to British in the first statement and then to us_english in the second we find that we have inserted two different dates actually though our raw values are the same. This is why it is so important to always use the recommended entry format when dealing with datetime and datetime2.

-- Listing 6: Impact of Language Settings

set language british
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'06/10/1979'
,'06/10/1979'
,'8:00 AM'
)


set language us_english
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'06/10/1979'
,'06/10/1979'
,'8:00 AM'
)

Fig. 4 Querying Staffers

With the language setting as British, SQL Server interprets the first two figures as the day but with the language setting as us_english, SQL Server interprets the first two figures as a month. One last thing that we need to mention here is that in inserting our records, we did not specify the time component thus SQL Server automatically assumes were mean midnight of the specified date.

Conclusion

In this article, we have learned what the datetime and datetime2 data types look like, their key differences and how to make sure you are entering the correct date when using these data types. In the course of it, we have also examined two errors which a developer could run into when working with these data types.

References
  • SQL Data Types
  • Ben-Gan, I. (2016) T-SQL Fundamentals. pp74-78. Microsoft Press.

The post T-SQL Datetime Data Type appeared first on {coding}Sight.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In my previous article, I demonstrated:

  1. Insert the output of the table-valued function in the SQL table.
  2. Insert the output of the table-valued function that is created on the remote database server.

In this article, I am going to demonstrate:

  1. Copy data between two tables, created in a different schema.
  2. Copy data between two tables, created in different databases on the same server.
  3. Copy data between two tables created, in different databases resides the different server (Cross server query)

Transfer data between two tables created in a different schema.

To copy data between two tables created in a different schema, we must use the following syntax:

INSERT INTO <DESTINATIONSCHEMANAME>.<DESTINATIONTABLENAME> 
            (                         COLUMN1, 
                        COLUMN2, 
                        COLUMN3                  .. 
            ) 
SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3 
FROM   <SOURCESCHEMANAME>.<SOURCETABLENAME>

For demonstration, I am going to use the WideWorldImportors database. I want to copy all the records from the [Application].[People] table to the [dbo].[EmployeesWithoutLogonName] table that has LOGONNAME equal to ”NO LOGON.”

First, Let’s review data from the [Application].[People] table. To do that, execute the following query:

SELECT [PERSONID], 
       [FULLNAME], 
       [PREFERREDNAME], 
       [SEARCHNAME], 
       [LOGONNAME], 
       [PHONENUMBER], 
       [FAXNUMBER], 
       [EMAILADDRESS] 
FROM   APPLICATION.PEOPLE 
WHERE  LOGONNAME = 'NO LOGON'

The following is a screenshot of the output:

Now, let’s create the [dbo].[EmployeesWithoutLogonName] table. To do that, execute the following query:

CREATE TABLE EMPLOYEESWITHOUTLOGONNAME 
  ( 
     [ID]             INT IDENTITY(1, 1), 
     [FULL_NAME]      VARCHAR(500), 
     [PREFERRED_NAME] VARCHAR(500), 
     [SEARCH_NAME]    NVARCHAR(MAX), 
     [LOGON_NAME]     VARCHAR(250), 
     [PHONE_NUMBER]   VARCHAR(50), 
     [FAX_NUMBER]     VARCHAR(100), 
     [EMAIL_ADDRESS]  NVARCHAR(250) 
  )

Now let us copy records from [Application].[People] to [dbo].[EmployeesWithoutLogonName]. To do that, execute the following query:

INSERT INTO [DBO].[EMPLOYEESWITHOUTLOGONNAME] 
            ([FULL_NAME], 
             [PREFERRED_NAME], 
             [SEARCH_NAME], 
             [LOGON_NAME], 
             [PHONE_NUMBER], 
             [FAX_NUMBER], 
             [EMAIL_ADDRESS]) 
SELECT [FULLNAME], 
       [PREFERREDNAME], 
       [SEARCHNAME], 
       [LOGONNAME], 
       [PHONENUMBER], 
       [FAXNUMBER], 
       [EMAILADDRESS] 
FROM   APPLICATION.PEOPLE 
WHERE  LOGONNAME = 'NO LOGON'

Once data is inserted, execute the following query to verify that data has been copied.

SELECT * 
FROM   EMPLOYEESWITHOUTLOGONNAME

The following is the output:

Copy data between two tables, created in a different database

As I explained above, we can copy data between tables created in two different schemas. Similarly, we can copy data between two tables created in two different databases. To copy data between two databases, the user must have the “db_datareader” permission on the source database and “db_datawriter” on the destination database.

Following is the syntax:

INSERT INTO <DESTINATIONDATABASENAME>.<DESTINATIONSCHEMANAME>.<DESTINATIONTABLENAME> 
            ( 
                        COLUMN1, 
                        COLUMN2, 
                        COLUMN3 .. 
            ) 
SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3 
FROM   <SOURCEDATABASENAME>.<SOURCESCHEMANAME>.<SOURCETABLENAME>

Now to demonstrate, I have created a new database named “HR.” To create a database, execute the following command.

CREATE DATABASE HR

Now I want to copy data of the EmployeesWithoutLogonName table, created in WideWorldImportors database to the “Employees” table created in the HR database.

First, let’s create a table named “Employees” in the HR database. To do that, execute the following query:

USE HR 
GO 
CREATE TABLE EMPLOYEES 
  ( 
     [ID]             INT IDENTITY(1, 1), 
     [FULL_NAME]      VARCHAR(500), 
     [PREFERRED_NAME] VARCHAR(500), 
     [SEARCH_NAME]    NVARCHAR(MAX), 
     [LOGON_NAME]     VARCHAR(250), 
     [PHONE_NUMBER]   VARCHAR(50), 
     [FAX_NUMBER]     VARCHAR(100), 
     [EMAIL_ADDRESS]  NVARCHAR(250) 
  )

Now to copy data from the “EmployeesWithoutLogonName” table to the “EMPLOYEES” table, let us execute the following query:

INSERT INTO HR.DBO.EMPLOYEES 
            ([FULL_NAME], 
             [PREFERRED_NAME], 
             [SEARCH_NAME], 
             [LOGON_NAME], 
             [PHONE_NUMBER], 
             [FAX_NUMBER], 
             [EMAIL_ADDRESS]) 
SELECT FULL_NAME, 
       PREFERRED_NAME, 
       SEARCH_NAME, 
       LOGON_NAME, 
       PHONE_NUMBER, 
       FAX_NUMBER, 
       EMAIL_ADDRESS 
FROM   WIDEWORLDIMPORTERS.DBO.EMPLOYEESWITHOUTLOGONNAME

Once data is inserted, execute the following query to verify that data has been copied.

SELECT * 
FROM   HR.DBO.EMPLOYEES

The following is the output:

Copy data between two tables, created in the different databases on different servers

Now, similarly, we can copy data between two tables created on two separate databases created on two different servers. This can be performed using Linked Server or the OPENROWSET keyword.

The following is the syntax to connect SQL database created on the remote server using Linked Server.

INSERT INTO <LINKEDSERVERNAME>.<DESTINATIONDATABASENAME>.<DESTINATIONSCHEMANAME>.<DESTINATIONTABLENAME> 
            ( 
                        COLUMN1, 
                        COLUMN2, 
                        COLUMN3 .. 
            ) 
SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3 
FROM   <SOURCEDATABASENAME>.<SOURCESCHEMANAME>.<SOURCETABLENAME>

In this demo, I am going to copy data of Employee table created on the Employees database to SQL database created on Azure cloud. To do that, firstly create a database on Azure SQL Instance. I have created a SQL server resource pool named “companyemployees.database.windows.net.

Now to create an HR database on Azure database instance, open SQL Server management studio. In the server name text box, select “companyemployees.database.windows.net.” We will use SQL Server authentication to connect SQL instance. To do that, select “SQL Server authentication” in the authentication type drop-down box. Provide appropriate user name and password and click on connect. See the following screenshot.

Now to create the “Cloud_HR_DB” database, Press Ctrl+N to open query editor window and execute the following command.

CREATE DATABASE CLOUD_HR_DB 
GO

See the following image.

Once the database is created, execute the following query to create the “cEmployees” table on the Cloud_HR_DB database. To do that, execute the following query in the HR database.

CREATE TABLE cEMPLOYEES 
  ( 
     [ID]             INT IDENTITY(1, 1), 
     [FULL_NAME]      VARCHAR(500), 
     [PREFERRED_NAME] VARCHAR(500), 
     [SEARCH_NAME]    NVARCHAR(MAX), 
     [LOGON_NAME]     VARCHAR(250), 
     [PHONE_NUMBER]   VARCHAR(50), 
     [FAX_NUMBER]     VARCHAR(100), 
     [EMAIL_ADDRESS]  NVARCHAR(250) 
  )

Once the database and table are created, we need to create a linked server to perform a cross-server query. To create a linked server between Local SQL server instance and Azure SQL server instance, execute following command on Local SQL Server instance.

USE [MASTER] 
GO 

EXEC MASTER.DBO.SP_ADDLINKEDSERVER 
  @SERVER = N'AZURE_SQL_SERVER', 
  @SRVPRODUCT=N'', 
  @PROVIDER=N'SQLNCLI', 
  @DATASRC=N'COMPANYEMPLOYEES.DATABASE.WINDOWS.NET', 
  @CATALOG=N'CLOUD_HR_DB' 

/* FOR SECURITY REASONS THE LINKED SERVER REMOTE LOGINS PASSWORD IS CHANGED WITH ######## */ 
EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN 
  @RMTSRVNAME=N'AZURE_SQL_SERVER', 
  @USESELF=N'FALSE', 
  @LOCALLOGIN=NULL, 
  @RMTUSER=N'NISARGUPADHYAY', 
  @RMTPASSWORD='########' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'DATA ACCESS', 
  @OPTVALUE=N'TRUE' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'RPC', 
  @OPTVALUE=N'TRUE' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'RPC OUT', 
  @OPTVALUE=N'TRUE' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'CONNECT TIMEOUT', 
  @OPTVALUE=N'0' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'LAZY SCHEMA VALIDATION', 
  @OPTVALUE=N'FALSE' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'QUERY TIMEOUT', 
  @OPTVALUE=N'0' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'REMOTE PROC TRANSACTION PROMOTION', 
  @OPTVALUE=N'TRUE' 
GO

Once linked server is created, lets export data of the Employees table, created on the local instance of SQL server to the cEmployees table created on the Azure SQL Server Instance. To do that, execute the following query on local SQL Server instance:

INSERT INTO [AZURE_SQL_SERVER].Cloud_HR_DB.DBO.cEMPLOYEES 
            ([FULL_NAME], 
             [PREFERRED_NAME], 
             [SEARCH_NAME], 
             [LOGON_NAME], 
             [PHONE_NUMBER], 
             [FAX_NUMBER], 
             [EMAIL_ADDRESS]) 
SELECT FULL_NAME, 
       PREFERRED_NAME, 
       SEARCH_NAME, 
       LOGON_NAME, 
       PHONE_NUMBER, 
       FAX_NUMBER, 
       EMAIL_ADDRESS 
FROM   HR..EMPLOYEES

Once data is exported from a local instance to Azure Instance, execute the following query to verify that data has been copied.

SELECT * 
FROM [AZURE_SQL_SERVER].Cloud_HR_DB.DBO.cEMPLOYEES

The following is the output:

We can also copy data to a remote server without using Linked Server. To do that, we must use the OPENROWSET keyword. OPENROWSET is an ad-hoc method to connect and access remote data source using OLEDB. For further reading of OPENROWSET, refer to Microsoft documentation of OPENROWSET.

Now in the following example, I will copy data from the cEmployees table created on Cloud_HR_DB to the Employees database created on the local instance. We will copy only those records which have Email_Address like” contoso.com.

Now to copy data, create a table named “ContosoEmployees” in the “HR” database. To do that, execute the following query:

CREATE TABLE [DBO].[CONTOSOEMPLOYEES] 
  ( 
     [ID]             [INT] IDENTITY(1, 1) NOT NULL, 
     [FULL_NAME]      [VARCHAR](500) NULL, 
     [PREFERRED_NAME] [VARCHAR](500) NULL, 
     [SEARCH_NAME]    [NVARCHAR](MAX) NULL, 
     [LOGON_NAME]     [VARCHAR](250) NULL, 
     [PHONE_NUMBER]   [VARCHAR](50) NULL, 
     [FAX_NUMBER]     [VARCHAR](100) NULL, 
     [EMAIL_ADDRESS]  [NVARCHAR](250) NULL 
  ) 
GO

Now to insert data using OPENROWSET, we need to enable the “Ad Hoc distributed queries” advanced option. To do that, execute the following command.

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE WITH OVERRIDE

EXEC SP_CONFIGURE 'AD HOC DISTRIBUTED QUERIES',1
RECONFIGURE WITH OVERRIDE

Now to copy data from the cEmployees table of the Cloud_HR_DB (Azure Instance) database to “ContosoEmployees” in the “Employees” database (Local Instance), execute the following query on the local server:

USE HR 
GO 

INSERT INTO CONTOSOEMPLOYEES 
            ([FULL_NAME], 
             [PREFERRED_NAME], 
             [SEARCH_NAME], 
             [LOGON_NAME], 
             [PHONE_NUMBER], 
             [FAX_NUMBER], 
             [EMAIL_ADDRESS]) 
SELECT * 
FROM   OPENROWSET('SQLNCLI11', 
'SERVER=COMPANYEMPLOYEES.DATABASE.WINDOWS.NET,1433;DATABASE=CLOUD_HR_DB;UID=NISARGUPADHYAY;PWD=NISARG@8987'
, 
'SELECT FULL_NAME, PREFERRED_NAME, SEARCH_NAME, LOGON_NAME, PHONE_NUMBER, FAX_NUMBER, EMAIL_ADDRESS FROM   CEMPLOYEES WHERE  EMAIL_ADDRESS LIKE ''%CONTOSO.COM%''')A

Once data is exported from a local instance to Azure Instance, execute the following query to verify that data has been copied.

SELECT FULL_NAME, 
       PREFERRED_NAME, 
       SEARCH_NAME, 
       LOGON_NAME, 
       PHONE_NUMBER, 
       FAX_NUMBER, 
       EMAIL_ADDRESS 
FROM   CONTOSOEMPLOYEES

The following is the output:

In this article I have explained how to:

  1. Copy data between two tables created in a different schema.
  2. Copy data between two tables created in different databases on the same server.
  3. Copy data between two tables created in different databases of the different server (Cross server query).

The post Advanced SQL: Variations and Different Use cases of T-SQL Insert Statement appeared first on {coding}Sight.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Jenkins Plugin implementation generally consists of the following stages:

  • Environment preparation;

  • Implementing the plugin’s UI;

  • Implementing the plugin’s business logic;

  • Implementing validation;

  • JUnit test coverage;

In this part of the article, we’ll look at the first two stages.

Environment preparation

Before starting the development of a Jenkins plugin, you would need to make sure that you have an environment prepared along the following guidelines:

  • JDK 8 is installed on your machine and the JAVA_HOME environment variable is set (this variable contains the path to JDK);

  • Apache Maven is installed;

  • An IDE that allows developing Jenkins plugins on Java is installed (e.g., IntelliJ IDEA).

Implemeting the plugin’s UI (front-end)

The plugin’s UI is configured through a Jelly file. Jelly is a specialized markup language that visually resembles xml and is widely used for writing plugins in Java.

The Jelly file is created within the project’s resources and is usually named config.jelly. To make sure Java will properly link this file to the plugin’s implementation, you will need to make sure that the UI folder you create in the project’s resources will have the same name as the Java file containing the plugin’s business logic. For example, if the plugin’s logic is implemented in the src/main/java/org/myorganization/MyAction.java class, the UI should be then implemented in the src/main/resources/org/myorganization/MyAction/config.jelly file.

As Jelly files are directly connected with the Java classes, you can call the methods from these classes in Jelly. To reference the Java files they’re connected with, Jelly files use the it or instance keyword – “${it.[member of classes]}”.

Suppose there is a method defined in a Java class:

public String getMyString() 
{
    return "Hello Jenkins!";
}

To call this method in Jelly, we will need to do the following:

<j:jelly xmlns:j="jelly:core" xmlns:st="jelly:stapler" 
xmlns:d="jelly:define" xmlns:l="/lib/layout"
xmlns:t="/lib/hudson" xmlns:f="/lib/form">
${it.getMyString()}
</j:jelly>

Depending on the plugin type, other objects can also be predefined:

  • app – the Jenkins instance.

  • instance – the Java class object that is currently being configured in the configuration page section.

  • descriptor – the descriptor object that corresponds to the instance class.

  • h – the hudson.Functions instance containing various useful functions.

Also, when writing UI in Jelly files, you can use resource constants. To do this, create a config.properties resource file next to the Jelly file. When this is done, the resource constants will be available for use in the ${%resourceName} format. For example:

<?jelly escape-by-default='true'?>
<j:jelly xmlns:j="jelly:core" xmlns:st="jelly:stapler" 
xmlns:d="jelly:define" xmlns:l="/lib/layout" xmlns:t="/lib/hudson" 
xmlns:f="/lib/form">
  <f:section title="${%MySection}">
    <f:block>${%MySectionDesc}</f:block>
    <f:textbox />
  </f:section>
</j:jelly>

The help functionality in Jenkins is realized in the following way. An html file with a ‘help-’ prefix (e.g., help-FIELD.html) is formed in the webapp folder of our plugin’s project. To connect the help file with a specific field, the help=”path to help file” statement is used. For example:

<?jelly escape-by-default='true'?>
<j:jelly xmlns:j="jelly:core" xmlns:st="jelly:stapler" 
xmlns:d="jelly:define" xmlns:l="/lib/layout" xmlns:t="/lib/hudson" 
xmlns:f="/lib/form">
  <f:section title="${%MySection}">
    <f:entry title="${%MyField}" field="myField" help="/plugin/MyJenkinsPlugin/resources/io/jenkins/plugins/sample/MyBuilder/help-myField.html">
<f:textbox />
    </f:entry>
  </f:section>
</j:jelly>

Also, when you’re designing UI, it is important to keep in mind that you won’t need to constantly restart the Jenkins server in most of the cases – instead, just change the *.jelly file in your IDE and request the page again.

Links you may find useful when designing UI in Jelly:

Apache Jelly Tutorial

Basic guide to Jelly usage in Jenkins

Jelly form controls

We’ll look at the remaining stages of Jenkins plugin implementation in the second part of this article.

The post Jenkins Plugin Implementation – Part 1 appeared first on {coding}Sight.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The need to do things in an asynchronous way – that is, dividing big tasks between multiple working units – was present long before the appearance of computers. However, when they did appear, this need became even more obvious. It is now 2019, and I’m writing this article on a laptop powered by an 8-core Intel Core CPU which, in addition to this, is simultaneously working on hundreds of processes, with the number of threads being even larger. Next to me, there lies a slightly outdated smartphone which I bought a couple of years ago – and it also houses an 8-core processor. Specialized web resources contain a wide variety of articles praising this year’s flagship smartphones equipped with 16-core CPUs. For less then $20 per hour, MS Azure can give you access to a 128-core virtual machine with 2 TB RAM. But, unfortunately, you cannot get the most out of this power unless you know how to control interaction between threads.

Terminology

Process – an OS object which represents an isolated address space containing threads.

Thread – an OS object which represents the smallest execution unit. Threads are constituent parts of processes, they divide memory and other resources between each other in the scope of a process.

Multitasking – an OS feature which represents the capability of executing multiple processes simultaneously.

Multi-core – a CPU feature which represents the ability to use multiple cores for data processing

Multiprocessing – the feature of a computer which represents the capability to physically work with multiple CPUs.

Multi-threading – the feature of a process which represents the capability of dividing and spreading the data processing between multiple threads.

Parallelism – simultaneous physical execution of multiple actions in a unit of time

Asynchrony – executing an operation without waiting for it to be fully processed, leaving the calculation of the result for later time.

A Metaphor

Not all definitions are effective and some of them require elaboration, so let me provide a cooking metaphor for the terminology I just introduced.

Making breakfast represents a process in this metaphor.

When making breakfast in the morning, I(CPU) go to the kitchen(Computer). I have two hands(Cores). On the kitchen, there is an assortment of devices(IO): stove, kettle, toaster, fridge. I turn on the stove, put a frying pan on it and pour some vegetable oil in it. Without waiting for the oil to heat (asynchronously, Non-Blocking-IO-Wait), I get some eggs from the fridge, crack them over a bowl and then whip them with one hand(Thread#1). Meanwhile, the second hand(Thread#2) is holding the bowl in place (Shared Resource). I would like to turn on the kettle, but I don’t have enough free hands at the moment (Thread Starvation). While I was whipping the eggs, the frying pan got hot enough (Result processing), so I pour the whipped eggs into it. I reach over to the kettle, turn it on and look at the water being boiled (Blocking-IO-Wait) – but I could have used this time to wash the bowl.

I only used 2 hands while making the omelet (because I don’t have more), but there were 3 simultaneous operations being executed: whipping the eggs, holding the bowl, heating the frying pan. CPU is the fastest part of the computer and IO is the part which requires waiting the most often, so it is quite effective to load the CPU with some work while it’s waiting for the data from IO.

To extend the metaphor:

  • If I was also trying to change my clothes while making the breakfast, then I would have been multitasking. Computers are way better at this than humans are.
  • A kitchen with multiple cooks – for example, in a restaurant – is a multi-core computer.
  • A mall food court with many restaurants would represent a data center.
.NET Tools

.NET is really good when it comes to working with threads – as well as at many other things. With each new version, it provides more tools for working with threads and new OS thread abstraction layers. When working with abstractions, the developers working with the framework are using an approach that allows them to go one or more layers down while using high-level abstractions. In most cases, there is no real need to do this (and doing this may introduce a possibility of shooting yourself in the foot), but sometimes this may be the only way to resolve an issue that cannot be solved on the current abstraction level.

When I said tools earlier, I meant both program interfaces (API) provided by the framework or third-party packages and full-fledged software solutions that simplify the process of searching for issues related to multi-threaded code.

Starting a Thread

The Thread class is the most basic .NET class for working with threads. Its constructor accepts one of these two delegates:

  • ThreadStart – no parameters
  • ParametrizedThreadStart – one object-type parameter.

The delegate will be executed in a newly-created thread after calling the Start method. If the ParametrizedThreadStart delegate was passed to the constructor, then an object should be passed to the Start method. This process is needed to pass any local information to the thread. I should point out that it takes a lot of resources to create a thread and the thread itself is a heavy object – at least because it requires interaction with the OS API and 1MB of memory is allocated to the stack.

new Thread(…).Start(…);

The ThreadPool class represents the concept of a pool. In .NET, the thread pool is a piece of engineering art and the Microsoft developers invested much effort to make it work optimally in all sorts of scenarios.

The general concept:

When started, the app creates a few threads in the background, allowing to access them when needed. If threads are used frequently and in great numbers, the pool is expanded to satisfy the needs of the calling code. If the pool doesn’t have enough free threads at the right time, it will either wait for one of the active threads to become unoccupied or create a new one. Based on this, it follows that the thread pool is perfect for short actions and does not work that well for processes that work as services for the whole duration of the application’s operation.

The QueueUserWorkItem method allows to use threads from the pool. This method takes the WaitCallback-type delegate. Its signature coincides with the signature of ParametrizedThreadStart, and the parameter that is passed to it serves the same role.

ThreadPool.QueueUserWorkItem(…)

The less-commonly-known RegisterWaitForSingleObject thread pool method is used to organize non-blocking IO operations. The delegate which is passed to this method will be called when the WaitHandle is released after being passed to the method.

ThreadPool.RegisterWaitForSingleObject(…)

There is a thread timer in .NET, and it differs from the WinForms/WPF timers in that its handler is called in the thread taken from the pool.

System.Threading.Timer

There is also a rather unusual way of sending the delegate to a thread from the pool – the BeginInvoke method.

DelegateInstance.BeginInvoke

I would also like to take a look at the function which many of the methods I mentioned earlier come down to – CreateThread from the Kernel32.dll Win32 API. There is a way to call this function with the help of the methods’ extern mechanism. I have only seen this being used once in a particularly bad case of legacy code – and I still don’t understand what its author’s reasons were.

Kernel32.dll CreateThread

Viewing and Debugging Threads

All threads – whether created by you, third-party components or the .NET pool – can be viewed in the Visual Studio’s Threads window. This window will only display the information about threads when the application is being debugged in the Break mode. Here, you can view the names and priorities of each thread and focus the debug mode on specific threads. The Priority property of the Thread class allows you to set the thread’s priority. This priority will be then taken into consideration when the OS and CLR are dividing processor time between threads.

Task Parallel Library

Task Parallel Library (TPL) has first appeared in .NET 4.0. Currently, it’s the main tool for working with asynchrony. Any code utilizing older approaches will be considered legacy code. TPL’s main unit is the Task class from the System.Threading.Tasks namespace. Tasks represent thread abstraction. With the latest version of C#, we acquired a new elegant way of working with Tasks – the async/await operators. These allow for asynchronous code to be written as if it were simple and synchronous, so those who are not well-versed in the theory of threads can now write apps that won’t struggle with long operations. Using async/await is really a topic for a separate article (or even a few articles), but I’ll try to outline the basics in a few sentences:

  • async is a modificator of a method which returns a Task or void
  • await is an operator of a non-blocking wait Task.

Once again: the await operator will usually (there are exceptions) let the current thread go and, when the task will be executed and the thread (actually, the context, but we’ll get back to it later) will be free as a result, it will continue executing the method. In .NET, this mechanism is implemented in the same way as yield return – a method is turned into a finite state machine class that can be executed in separate pieces based on its state. If this sound interesting, I would recommend writing any simple piece of code based on async/await, compiling it and looking at its compilation with the help of JetBrains dotPeek with Compiler Generated Code enabled.

Let’s look at the options we have when it comes to starting and using a task. In the example below, we create a new task which doesn’t actually do anything productive(Thread.Sleep(10000)). However, in real cases we should substitute it with some complex work that utilizes CPU resources.

using TCO = System.Threading.Tasks.TaskCreationOptions;

public static async void AsyncMethod() {
   var cancellationSource = new CancellationTokenSource();

   await Task.Factory.StartNew(
       // Code of action will be executed on other context
       () => Thread.Sleep(10000),
       cancellationSource.Token,
       TCO.LongRunning | TCO.AttachedToParent | TCO.PreferFairness,
       scheduler
   );

   // Code after await will be executed on captured context
}

A task is created with the following options:

LongRunning – this option hints at the fact that the task can not be performed quickly. Therefore, it is possibly better to create a separate thread for this task rather than taking an existing one from the pool to minimize harm to other tasks.

AttachedToParent – Tasks can be arranged hierarchically. If this option is used, the task will be waiting for its children tasks to be executed after being executed itself.

PreferFairness – this option specifies that the task should better be executed before the tasks that were created later. However, it’s more of a suggestion, so the result is not always guaranteed.

The second parameter that was passed to the method is CancellationToken. For the operation to be properly cancelled after it was already started, the executable code should contain CancellationToken state checks. If there are no such checks, then the Cancel method called on the CancellationTokenSource object would only be able to stop the task execution before the task is actually started.

For the last parameter, we sent a TaskScheduler-type object called scheduler. This class, along with its children classes, is used to control how tasks are distributed between threads. By default, a task will be executed on a randomly-selected thread from the pool

The await operator is applied to the created task. This means that the code written after it (if there is such code) will be executed in the same context (often, this means ‘on the same thread’) as the code written before await.

This method is labelled as async void, which means that the await operator can be used in it, but the calling code would no be able to wait for execution. If such possibility is needed, the method should return a Task. Methods labelled as async void can be seen quite often: they are usually event handlers or other methods operating under the fire and forget principle. If it’s necessary to wait for the execution to be finished and return the result, then you should use Task<T>.

For tasks that return the StartNew method, we can call ConfigureAwait with the false parameter – then, the execution after await will be continued on a random context instead of a captured one. This should always be done if the code written after await does not require a specific execution context. This is also a recommendation from MS when it comes to writing code provided as a library.

Let’s look at how we can wait for a Task to be finished. Below, you can see an example piece of code with comments denoting when the waiting is implemented in a relatively good or bad manner.

public static async void AnotherMethod() {

   int result = await AsyncMethod(); // good

   result = AsyncMethod().Result; // bad

   AsyncMethod().Wait(); // bad

   IEnumerable<Task> tasks = new Task[] {
       AsyncMethod(), OtherAsyncMethod()
   };

   await Task.WhenAll(tasks); // good
   await Task.WhenAny(tasks); // good

   Task.WaitAll(tasks.ToArray()); // bad
}

In the first example, we are waiting for the Task to be executed without blocking the calling thread, so we’ll come back to processing the result when it’s ready. Before that happens, the calling thread is left on its own.

In the second attempt, we are blocking the calling thread until the method’s result is calculated. This is a bad approach for two reasons. First of all, we are wasting a thread – a very valuable resource – on simple waiting. Additionally, if the method we’re calling contains an await while a return to the calling thread after await is intended by the synchronization context, we’ll get a deadlock. This happens because the calling thread will be waiting for the result of an asynchronous method, and the asynchronous method itself will be fruitlessly trying to continue its execution in the calling thread.

Another disadvantage of this approach is the increased complexity of error handling. The errors can actually be handled rather easily in asynchronous code if async/await is used – the process in this case is identical to that in synchronous code. However, when a synchronous wait is applied to a Task, the initial exception is wrapped in AggregateException. In other words, to handle the exception, we would need to explore the InnerException type and manually write an if chain in a catch block or, alternatively, use the catch when structure instead of the more usual chain of catch blocks.

The two last examples are also labelled as relatively bad for the same reasons and both contain the same issues.

The WhenAny and WhenAll methods are very useful when it comes to waiting for a group of Tasks – they wrap these tasks into one, and it will be executed either when one Task from the group is started or when all of these tasks are successfully executed.

Stopping Threads

For various reasons, there may be a need to stop a thread after it has been started. There are a few ways to do this. The Thread class has two methods with appropriate names — Abort and Interrupt. I would strongly discourage using the first one as, after it’s called, there would be a ThreadAbortedException thrown at any random moment while processing any arbitrarily chosen instruction. You’re not expecting such an exception to be encountered when an integer variable is incremented, right? Well, when using the Abort method, this becomes a real possibility. In case you need to deny the CLR’s ability of creating such exceptions in a specific part of the code, you can wrap it in the Thread.BeginCriticalRegion and Thread.EndCriticalRegion calls. Any code written in the finally block is wrapped in these calls. This is why you can find blocks with an empty try and a non-empty finally in the depths of the framework code. Microsoft dislike this method to the extent of not including it in the .NET core.

The Interrrupt method works in a much more predictable way. It can interrupt a thread with a ThreadInterruptedException only when the thread is in the waiting mode. It moves to this state when suspended while waiting for WaitHandle, a lock or after Thread.Sleep is called.

Both of these ways have a disadvantage of unpredictability. To escape this issue, we should use the CancellationToken structure and the CancellationTokenSource class. The general idea is this: an instance of the CancellationTokenSource class is created, and only those who own it can stop the operation by calling the Cancel method. Only CancellationToken is passed to the operation. CancellationToken’s owners cannot cancel the operation themselves – they can only check whether the operation has been cancelled. This can be achieved by using a Boolean property IsCancellationRequested and the ThrowIfCancelRequested method. The last one will generate a TaskCancelledException if the Cancel method has been called on the CancellationTokenSource instance which created the CancellationToken. This is the method I recommend using. It’s advantage over the previously-described methods lies in the fact that it provides full control over the exact exception cases in which an operation can be cancelled.

The most brutal way to stop a thread would be to call a Win32 API function called TerminateThread. After this function is called, the CLR’s behavior can be quite unpredictable. In MSDN, the following is written about this function: “TerminateThread is a dangerous function that should only be used in the most extreme cases. “

Turning a Legacy API Into a Task-Based One by Using FromAsync

If you were fortunate enough to work on a project which was started after the Tasks have been introduced (and when they are no longer inciting existential horror in most of developers), you will not have to deal with old APIs – both the third-party ones and those your team toiled on in the past. Fortunately, the .NET Framework development team made it easier for us – but this could have been self-care, for all we know. In any case, .NET has a few tools which help with seamlessly bringing the code written with old aprroaches to asynchrony in mind to an up-to-date form. One of these is the TaskFactory method called FromAsync. In the example below, I’m wrapping the old asynchronous methods of the WebRequest class into a Task by using FromAsync.

object state = null;
WebRequest wr = WebRequest.CreateHttp("http://github.com");
await Task.Factory.FromAsync(
   wr.BeginGetResponse,
   we.EndGetResponse
);

It’s only an example, and you probably won’t be doing something of this sort with built-in types. However, old projects teem with BeginDoSomething methods that return IAsyncResult and EndDoSomething methods that receive them.

Turning a Legacy API Into a Task-Based One by Using TaskCompletionSource

Another tool worth exploring is the TaskCompletionSource class. In its functionality, purpose and operation principle, it resembles the RegisterWaitForSingleObject method from the ThreadPool class I mentioned earlier. This class allows us to easily wrap old asynchronous APIs into Tasks.

You may want to say that I already told about the FromAsync method from the TaskFactory class which served these purposes. Here, we would need to remember the full history of asynchronous models Microsoft provided in the last 15 years: before Task-Based Asynchronous Patterns (TAP), there were Asynchronous Programming Patterns (APP). APPs were all about BeginDoSomething returning IAsyncResult and the EndDoSomething method which accepts it – and the FromAsync method is perfect for these years’ legacy. However, as time passed, this was replaced with Event Based Asynchronous Patterns(EAP) which specified that an event is called when an asynchronous operation is successfully executed.

TaskCompletionSource are perfect for wrapping legacy APIs built around the event model into Tasks. This is how it works: objects of this class have a public property called Task<T>, the state of which can be controlled by various methods of the TaskCompletionSource class (SetResult, SetException etc.). In places where the await operator was applied to this Task, it will be executed or crashed with an exception depending on the method applied to TaskCompletionSource. To understand it better, let’s look at this example piece of code. Here, some old API from the EAP era is wrapped in a Task with the help of TaskCompletionSource: when an event is triggered, the Task will be switched to the Completed state while the method that applied the await operator to this Task will continue its execution after receiving a result object.

public static Task<Result> DoAsync(this SomeApiInstance someApiObj) {

   var completionSource = new TaskCompletionSource<Result>();
   someApiObj.Done +=
       result => completionSource.SetResult(result);
   someApiObj.Do();

   result completionSource.Task;

TaskCompletionSource Tips & Tricks

TaskCompletionSource can do more than just wrapping obsolete APIs. This class opens an interesting possibility of designing various APIs based on Tasks that don’t occupy threads. A thread, as we remember, is a expensive resource limited mostly by RAM. We can easily reach this limit when developing a robust web application with complex business logic. Let’s look at the capabilities I mentioned in action by implementing a neat trick known as Long Polling.

In short, this is how Long Polling works:

You need to get some information from an API about events occurring on its side, but the API, for some reason, can only return a state rather than telling you about the event. An example of such would be any API built over HTTP before WebSocket appeared or in circumstances under which this technology can’t be used. The client can ask the HTTP server. The HTTP server, on the other hand, cannot initiate contact with the client by itself. The simplest solution would be to ask the server periodically using a timer, but this would create additional load for the server and a general delay which approximately equals to TimerInterval / 2. To bypass this, Long Polling was invented. It entails delaying the server response until the Timeout expires or an event happens. If an event occurs, it will be handled; if not – the request will..

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The goal

Keeping two databases in sync is a task we often encounter when working on SQL database development and maintenance. One particular case is this – the versions of a database present on testing and development environments need to be consistently synchronized to make sure that tests are running on the most recent version. This should be done by detecting schema changes in the development version of a database and then automatically synchronizing them with the version from the testing environment on a scheduled basis.

How to achieve it

We’ll look into how automated synchronization of two SQL databases can be achieved by combining a schema comparison tool with PowerShell scripts and scheduling.

In the example we’re going to see, the databases will be synchronized every 15 minutes. As we’re using a schema comparison tool, we can be sure that the database will only be updated if there are some actual changes between the development and testing versions of the database – this prevents the execution of unnecessary resource-heavy operations.

We’ll use Devart Schema Compare as the utility of choice to diff and synchronize SQL Server databases. It’s a tool that allows you to compare and sync live SQL Server databases, snapshots, and backups. Let’s look at how we can implement our project below.

Setup

First of all, we’ll need to set some things up:

  1. Run Schema Compare
  2. Click the ‘New Schema Comparison‘ button on the toolbar or, alternatively, in the top right part of the start page:
  3. In the Source and Target tab of the New Schema Comparison window, choose the desired source and target databases:
  4. In the Options, Schema Mapping, Table Mapping and Object Filter tabs, you can set up the necessary comparison and synchronization options:
  5. When everything is set up, you can press the ‘Compare‘ button in the bottom right corner to start the comparison process.
  6. When the comparison is finished, you can see all objects that have been compared and their respective difference status in the top part of the window, while the detailed script for each of those objects will be located at the bottom:
  7. Check all objects to include them into the synchronization process and click the ‘Save‘ button or press File > Save:This will save the project with all the information we need to start the synchronization process.
  8. Once the project is saved, click the ‘Synchronize objects‘ button:
  9. Schema Synchronization Wizard will be opened. First, select ‘Execute the script directly against the target database‘ in the Output tab:
  10. You can choose the necessary settings in the Options tab:
  11. You can check all objects that are going to be synchronized in the Summary tab:
  12. Click ‘Synchronize‘ in the bottom-right corner to test the synchronization process.
  13. You will see the results displayed in the bottom pane of the window:
Automating the process

As schema synchronization was successful and now that we have a project file with all the necessary info, let’s automate the synchronization process by using a PowerShell script.

In the following example, it is assumed that you use Integrated security but the stored and encrypted database can still be easily accessed from the project file, along with repository credentials.

We’ll look at some parts of the script creation process that may be particularly interesting, but feel free to skip this section to download and test the finished script right away.

Creating a script

First, we’ll need to create a function that checks if the Outputs folder exists. This folder’s purpose is to store data-stamped commit summaries. The function should look like this:

#check if the Outputs folder exists
function CheckAndCreateFolder($rootFolder, [switch]$Outputs)
{
$location = $rootFolder

    #setting up its location 
    if($Outputs -eq $true)
{
$location += "\Outputs"
}
   
    #creating the folder if it doesn't currently exist
    if(-not (Test-Path $location))
{ mkdir $location -Force:$true -Confirm:$false | Out-Null }

return $location
}

Next, we’ll define the root folder and the location of schema output summaries:

#defining the root folder for the synchronization process
$rootFolder = "d:\temp\SchemaSync"

#defining the location of schema output summaries 
$outsLoc = CheckAndCreateFolder $rootFolder -Outputs

Now, we’ll need to define Schema Compare’s location, the date stamp variable and parameters of the application:

#Schema Compare location and the date stamp variable are defined, as well as  the tool’s parameters 
$diffLoc   = "C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com"
$stamp = (Get-Date -Format "Mmddyyyy_HHMMss")

With this in place, we can set the path to the output log file:

#output log file path
$logPath = "$outsLoc\SchemaOutput_$stamp.txt"
$Params = "/schemacompare /compfile:""D:\temp\SchemaSync\Project\ALLA1vsALLA2.scomp"" /log:""$logPath"""
$sync = " /sync"

Next, we’ll call Schema Compare and let it execute its synchronization parameters:

#initiate the schema comparison and synchronization process
(Invoke-Expression ("&amp; `"" + $diffLoc +"`" " +$Params))
$returnCode = $LASTEXITCODE

$message = ""

The final part of the script will serve to define all possible outcomes:

  1. Schema changes are detected, return code 0 – Success
  2. No schema changes detected, return code 100– No schema changes detected
  3. An error has been encountered and the output summary will be opened.
if ($returnCode -notin (100, 101))
{ #an error is encountered
$logPath = "$outsLoc\SchemaOutput_error.txt"
       
$message &gt;&gt; $logPath
clear-content $logPath
$message = "`r`n $returnCode - An error is encountered"
       
#output file is opened when an error is encountered
Invoke-Item "$logPath"
}
else{
if ($returnCode -eq 101)
{
clear-content $logPath
(Invoke-Expression ("&amp; `"" + $diffLoc +"`" " +$Params+$sync))
$returnCode = $LASTEXITCODE
       
#schema changes are detected
}
if($returnCode -eq 0)
{
 $message = "`r`n $returnCode - Schema changes were successfully synchronized"
}
else
{
 #there are no schema changes
 if($returnCode -eq 100)
{
$message = "`r`n $returnCode - There are no schema changes. Job aborted"
}
}
}
$message &gt;&gt; $logPath
Scheduling

With the PowerShell script ready and the process automated, we can schedule this in a few different ways, e.g. via the Windows Scheduler.

Viewing results

Now that the scheduled job is up and running, you can view schema output summaries anytime you need. In the example we just seen, $outsLoc variable defined the location of schema output summaries. As a result, such summaries will be saved to $rootFolder\$outsLoc – in this particular case, SchemaSync\Outputs:

If any error is encountered, e.g. a project file’s extension is entered incorrectly, the corresponding error message will be displayed in the output summary.

The list of return error codes will help us to better understand the specific error we encountered.

FULL SCRIPT
#check if the Outputs folder exists
function CheckAndCreateFolder($rootFolder, [switch]$Outputs)
{
$location = $rootFolder

    #setting up its location 
    if($Outputs -eq $true)
{
$location += "\Outputs"
}
   
    #creating the folder if it doesn't currently exist
    if(-not (Test-Path $location))
{ mkdir $location -Force:$true -Confirm:$false | Out-Null }

return $location
}

#defining the root folder for the synchronization process
$rootFolder = "d:\temp\SchemaSync"

#defining the location of schema output summaries 
$outsLoc = CheckAndCreateFolder $rootFolder -Outputs 

#Schema Compare location and the date stamp variable are defined, as well as  the tool’s parameters 
$diffLoc = "C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com"
$stamp = (Get-Date -Format "Mmddyyyy_HHMMss")

#output log file path
$logPath = "$outsLoc\SchemaOutput_$stamp.txt"
$Params = "/schemacompare /compfile:""D:\temp\SchemaSync\Project\ALLA1vsALLA2.scomp"" /log:""$logPath"""
$sync = " /sync"

#initiate the schema comparison and synchronization process
(Invoke-Expression ("&amp; `"" + $diffLoc +"`" " +$Params))
$returnCode = $LASTEXITCODE

$message = ""

if ($returnCode -notin (100, 101))
{ #an error is encountered
$logPath = "$outsLoc\SchemaOutput_error.txt"

$message &gt;&gt; $logPath
clear-content $logPath
$message = "`r`n $returnCode - An error is encountered"
       
#output file is opened when an error is encountered
Invoke-Item "$logPath"
}
else{
if ($returnCode -eq 101)
{
clear-content $logPath
(Invoke-Expression ("&amp; `"" + $diffLoc +"`" " +$Params+$sync))
$returnCode = $LASTEXITCODE
       
#schema changes are detected
}
if($returnCode -eq 0)
{
$message = "`r`n $returnCode - Schema changes were successfully synchronized"
}
else
{
#there are no schema changes
if($returnCode -eq 100)
{
$message = "`r`n $returnCode - There are no schema changes. Job aborted"
}
}
}
$message &gt;&gt; $logPath

If any questions or issues arise during the process of setting this up, feel free to contact us anytime at support@devart.com

The post How to Automate the Process of SQL Server Database Schema Synchronization appeared first on {coding}Sight.

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview