MS SQL Server

Python scatter plot in SQL Server 2017

As you know, Microsoft released the latest version of SQL Server which is SQL Server 2017. If you worked with SQL Server 2016 then you could realize that Microsoft SQL Server is not just a relational database anymore because it started to support big data and so many options to handle the non-relational data.
R Language is very popular to work with data science-related projects. It was integrated with SQL Server 2016 and we can run the R Scripts in SQL Server Management Studio itself.
It helps us to avoid the data movement between the relational database to R server and process. The same way, Microsoft now introduced Python integration with SQL Server. These 2 languages are coming from machine learning services in SQL Server.
To run Python scripts in SQL Server management studio, you need to enable the external script stored procedure.
Run the below command in your SSMS and see whether you are getting the “hello world” as an output.
In case, you are getting an error message then the configuration part was not properly done.
execute sp_execute_external_script
@language = N’Python’,
@script = N’
print(“hello world”)’
The below script is to generate the scatter plot.
DECLARE @Query nvarchar(max) = N’SELECT Year, Sales from [dbo].[SalesByYear]’

execute sp_execute_external_script

@language = N’Python’,

@script = N’

import matplotlib.pyplot as myplot

X = myplot.figure()

myplot.scatter(InputDataSet.Year,InputDataSet.Sales)

myplot.xlabel (“Year”)

myplot.ylabel (“Sales”)

myplot.title (“Sales by Year”)

myplot.savefig (“D:\Win – 8\myfig.png”)

‘,

@input_data_1 = @Query
Check the below step by step procedure to create a scatter plot using python.

Graph database in SQL Server 2017

As you know Graph Database is one of the latest features from SQL Server 2017.
Let us first understand the purpose of Graph database. We have a relational database which handles most of the scenarios but as we are started to handle big data and complex scenario our database also should be capable enough to handle those scenarios.
Yes. Graph database handles those complex scenarios easily which I have explained. As part of graph database, Microsoft team introduced two different tables.
1. Node
2. Edge
Check out the below explanation of those tables and how to work with graph database.
Use the below scripts

Use SQL2017
—Create Main table
CREATE TABLE People (
ID INT PRIMARY KEY,
Name NVARCHAR(25)
) AS NODE;

–Create Edge Table for relationships
CREATE TABLE RELATIONSHIP (
TYPE NVARCHAR(25)
) AS EDGE;

—Insert values to People
INSERT INTO People VALUES (1, ‘David’)
INSERT INTO People VALUES (2, ‘John’)

SELECT * FROM People;

SELECT * FROM RELATIONSHIP;

–Create relationships
INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘David’),
(SELECT $NODE_ID FROM People where Name = ‘John’), ‘Father’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘John’),
(SELECT $NODE_ID FROM People where Name = ‘David’), ‘Son’);

—Cartesian Product result
–No need to use joins since nodes and edges are interconnected in structure
SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP

–Proper Result
SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)

–more Records
INSERT INTO People VALUES (3, ‘Nancy’)

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘Nancy’),
(SELECT $NODE_ID FROM People where Name = ‘David’), ‘Daughter’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘David’),
(SELECT $NODE_ID FROM People where Name = ‘Nancy’), ‘Father’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘John’),
(SELECT $NODE_ID FROM People where Name = ‘Nancy’), ‘Brother’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘Nancy’),
(SELECT $NODE_ID FROM People where Name = ‘John’), ‘Sister’);

SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)

SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)
AND FromName.Name = ‘Nancy’

Share your comments below. Thank you

Windows could not start the SQL Server – Error & Solution

This post explains you very basic error that you get rarely and solution to that error,

Error: “Windows could not start the SQL Server on Local Computer. For more information, review the System Event log”.

The below error message will appear when your SQL Server evaluation period is expired.

Check the information in Event Viewer.

Solution

Buy the server and use the product key.

You can visit Maintenance in SQL Server installation and can upgrade.

Latest Update on SQL Server Management Studio

This simple post highlights the top level updates on components in SQL Server Management Studio.

The latest release of SQL Server Management Studio v17 RC3 has updates on below section compare than the earlier version.

1. Microsoft SQL Server Management Studio
2. Microsoft Analysis Services Client Tools

SQL Server Drop Database User

This blog post explains the issue that we usually face while dropping the users from the database and how can we resolve the issue.

We can create a user for database easily by any of the following user types,

1. SQL User with login
2. SQL User without login
3. User mapped to a certificate
4. User mapped to a asymmetric key
5. Windows User

If we want to drop the user then we can write drop statement but it will throw a below error in case the user owned any of the schemas.

Msg 15138, Level 16, State 1, Line 20

The database principal owns a schema in the database, and cannot be dropped.

To resolve the issue, Follow the below steps.

Step 1: Check the schema that owned by the user.

SELECT *

FROM sys.schemas s

WHERE s.principal_id = USER_ID(‘Username’)

Step 2: Change the schema back to dbo.

ALTER AUTHORIZATION ON SCHEMA::<schemaname> TO dbo;

Step 3: Drop the user

DROP USER <username>

SQL Query – Network Bandwidth Optimization

This article explains that how can we improve the performance when we access the SQL Query via network.

Scenario: I have taken SQL Server installed on Virtual Machine with 7GB of RAM and accessing the SQL Server database from local SQL Server management studio.

Step 1: Up and Run VM with SQL Server installed.

Step 2: Enable the DNS name of the Virtual Machine.

Step 3: Enable the TCP port with 1433 in portal.

Step 4: Create TCP port inside virtual Machine. Type WF.msc in command prompt. Right click on Inbound Rules and choose New.

Step 5: Make sure SQL Server services are up and running.

Step 6: Enable the SQL Server as mixed mode authentication.

Step 7: Create a new user login and check.

Step 8: Access the database from local SSMS.

Performance Improvement with NitroAccelerator

Step 1:

Install the “NitroAccelerator” in local machine, http://nitrosphere.com/trial/?ref=83095

Step 2: Run the query in local SSMS without Nitro Accelerator.

Step 3: Start the accelerator and test the query performance.

Generate SELECT Statement

This article explains that how to generate SELECT statement with all the table columns.

In the Production environment, most of the tables will have more than 100 columns. If those tables are used in a report with all the columns then it will be difficult to type all the columns.

To ease the process, we can generate the SELECT statement and can use that for any purpose.

Use the below code and pass the table which you want to use in the SELECT statement.

In this example, I used FactInternetSales table.

—–Code——-

Declare @Column varchar(500), @tableName varchar(25)

SET @tableName =’FactInternetSales’

SET @Column = ”

select @Column = @Column + Column_Name + ‘, ‘ from [AdventureWorksDW2014].INFORMATION_SCHEMA.COLUMNS

where table_name=@tableName

select ‘SELECT ‘ + SUBSTRING(@Column, 0, LEN(@Column)) + ‘ From ‘+@tableName as SELECTScript

—–Code———-

Copy and paste the result and execute the script.

Avoid Using Dataset Filters in SSRS

If you are Microsoft SQL Server Report Developer, then you have a chance to notice the dataset properties as it is the place where we supply the data subsets to the report.

Let us understand the filters in SSRS first and analyse why dataset filters should be avoided.

We have three different main places to filter the data in SSRS

1. T-SQL Script
2. Dataset Filters
3. Run-time Filter using Parameters

Filter in T-SQL Script

When we understand the business requirements, need to categories what are the filters static and dynamic.

Always use the static filters in T-SQL script. Dynamic filters can be done through parameters but still need to add the filter in T-SQL Script with parameters.

For example,

If you want to filter country values as the US on your country table.

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=’US’

Note: In this case, we are restricting data in database server itself.

Dataset Filters

You can filter in SSRS report dataset.

Go to Dataset -> Filters

Note: In this case, we are taking all the data to report server and filtering the data in the report.

Run-time filter using Parameter

It is a special kind of filter because this will be the requirement from business in most of the cases.

First 2 filters won’t be visible to end users but this filter will be visible and user needs to filter the value while running the report.

This can be achieved by adding the parameter in T-SQL script,

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=@Country

Why should we avoid dataset filters?

The main reason is to improve the performance of the report.

In case 1 (Filter in  T-SQL Script), we are filtering the data in database layer itself. It means we are taking only required data to the reporting layer and showing the result. The performance of the report will be good as it is processing only fewer records.

In case 2 (Dataset filter), we are taking all the records from table database and filtering in the report server. It means report server has to make process and filter the data and show the result. We are giving burden to report server so it will affect the performance.