SQL SERVER 2017 | Data Management Tool – SQL Operations Studio

In this post we shall check out the details about the new data management tool called SQL Operations Studio. SQL Operations Studio is a data management tool that enables working with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux.

Notes:-

1. You can download and install SQL Operations Studio (preview) from https://docs.microsoft.com/en-us/sql/sql-operations-studio/download

2. Both SSMS and SQL Operations Studio (preview) can work simultaneously. Also note that SSMS has been still the default tool for SQL Server.

3. SQL Operations Studio (SQLOPS) is open source. It is orginally called Project Carbon.

4. SQL Operations Studio is in beta. Meaning – You may encounter bugs in the product or may be get few incomplete things. Use this at your own risk

5. As of now please do not use this on production system.

6. You can get the project details of SQL Operations Studio from https://github.com/Microsoft/sqlopsstudio

Few Screen Shots of the SQL Operations Studio (SQLOPS):-

Refer Microsoft Link below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | Get data based on Index from a column

In this puzzle you will give a value from FromValue. From that FromValue we need to find out GetCode(code) rows with where you get that FromValue. For more details please check the sample input and expected output.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE SplitDataByChar
(
	 FromValue varchar(20)
	,GetCode varchar(200)
)
GO

INSERT INTO SplitDataByChar VALUES
('0.1','A.B'),
('0.1.2','A.B.C'),
('0.1','A.T'),
('0.1','T.K')
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

DECLARE @Find AS VARCHAR(10) = '1'
;WITH CTE0 AS
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY ( SELECT NULL ) ) rnk  FROM SplitDataByChar
)
,CTE AS 
(	 
	SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM 
	(
		SELECT *,CAST('<A>'+ REPLACE(FromValue,'.','</A><A>')+ '</A>' AS XML) po
		FROM CTE0
	)rt
	CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o
)
,CTE1 AS
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM 
	(
		SELECT *,CAST('<A>'+ REPLACE(GetCode,'.','</A><A>')+ '</A>' AS XML) po
		FROM CTE0
	)rt
	CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o
)
SELECT s.*,ISNULL(p.value,'') Value FROM SplitDataByChar s
LEFT JOIN 
(
	SELECT a.FromValue, a.GetCode , b.value , a.rnk FROM CTE a 
	INNER JOIN CTE1 b ON a.GetCode = b.GetCode
	AND a.FromValue = b.FromValue AND a.rnk = b.rnk AND a.rnk1 = b.rnk1
	WHERE a.value = @Find
)p ON p.FromValue = s.FromValue AND p.GetCode = s.GetCode
ORDER BY rnk

--

Output-1

--                               
  
FromValue            GetCode                Value
-------------------- ---------------------- ----------
0.1                  A.B                    B
0.1.2                A.B.C                  B
0.1                  A.T                    T
0.1                  T.K                    K

(4 rows affected)

                                             
                                    
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | Split String by .(Dot) using XML

In this puzzle you will have to split a string based on dot(.). For more details please check the sample input and expected output.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE Splitter
(
	 Id INT
	,Vals VARCHAR(100)
)
GO

INSERT INTO Splitter VALUES
(1,'P.K'),
(2,'a.b'),
(3,'c.d'),
(4,'e.J'),
(5,'t.u.b')
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

SELECT Id,Vals,Value FROM 
(
	SELECT *,CAST('<A>'+ REPLACE(Vals,'.','</A><A>')+ '</A>' AS XML) po
	FROM Splitter
)rt
CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o


--

Output-1

--                               
  
Id          Vals              Value
----------- ----------------- ----------
1           P.K               P
1           P.K               K
2           a.b               a
2           a.b               b
3           c.d               c
3           c.d               d
4           e.J               e
4           e.J               J
5           t.u.b             t
5           t.u.b             u
5           t.u.b             b

(11 rows affected)

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com