Advertisements

Archive

Archive for the ‘SQL Server Versions’ Category

Parsing nested JSON in customized SQL Tabular format – MSDN TSQL forum


 
–> Question:

Basically I have a JSON file output that I want to put into a SQL table, but no matter what syntax I try it doesn’t seem to be working.

This is my sample JSON file contents:

{
   "destination_addresses":[
      "Orlando, FL, USA"
   ],
   "origin_addresses":[
      "New York, NY, USA"
   ],
   "rows":[
      {
         "elements":[
            {
               "distance":{
                  "text":"1,072 mi",
                  "value":1725756
               },
               "duration":{
                  "text":"15 hours 40 mins",
                  "value":56405
               },
               "status":"OK"
            }
         ]
      }
   ],
   "status":"OK"
}

What I want is a SQL table that has 2 columns, column 1 will be item names (Destination Address,Origin Address, Distance Text, Distance Value, Duration Text & Duration Value) and column 1 will be the actual data.

I can get OPENJSON to do what I want for destination address & origin address but when I try and using OPENJSON to read the nested items that exist in ‘rows – > elements’ I can’t seem to pick them up. Can anyone help with the right syntax?
 

–> Answer:

declare @str varchar(4000) = 
N'{
   "destination_addresses":[
      "Orlando, FL, USA"
   ],
   "origin_addresses":[
      "New York, NY, USA"
   ],
   "rows":[
      {
         "elements":[
            {
               "distance":{
                  "text":"1,072 mi",
                  "value":1725756
               },
               "duration":{
                  "text":"15 hours 40 mins",
                  "value":56405
               },
               "status":"OK"
            }
         ]
      }
   ],
   "status":"OK"
}'

SELECT 
	CONCAT_WS(' ',Destination_Addresses, Origin_Addresses,Distance_Text,
		Distance_Value,Duration_Text,Duration_Value) as Col1
	,@str as Col2
FROM OPENJSON (@str)
WITH (
    Destination_Addresses    VARCHAR(1000) '$.destination_addresses[0]',
    Origin_Addresses   VARCHAR(1000) '$.origin_addresses[0]',
    Distance_Text    VARCHAR(1000) '$.rows[0].elements[0].distance.text',
    Distance_Value   VARCHAR(1000) '$.rows[0].elements[0].distance.value',
    Duration_Text    VARCHAR(1000) '$.rows[0].elements[0].duration.text',
    Duration_Value   VARCHAR(1000) '$.rows[0].elements[0].duration.value'
) AS Orders

Advertisements

Preparing for Exam 70-761, Querying Data with Transact-SQL | Certification


 
This exam (70-761) will earn you MCP in SQL Server 2016 Querying Data with Transact-SQL. It is 1 out of the 2 exams to earn the “MCSA: SQL 2016 Database Development” certification. I will discuss about the other “Exam 70-762, Developing SQL Databases” in my next post.
 

So, let’s go and understand this exam, links to study material and how to prepare for this.
 

–> This exam is targeted for students or professionals who want to learn about Transact-SQL or simply T-SQL, which includes:
1. Know about SQL Server and its components
2. Write single/multi table SELECT statements, with SET and Predicate logic
3. Apply Filtering, Sorting, JOIN, etc with SQL Queries
4. Write DDL, DML SQL Statements to Store and Retrieve data to & from Tables
5. Create Views, Stored Procedures, Functions, etc
6. Use inbuilt Functions and Transform data
7. Work with SQL Datatypes, Variables, Conditions, Loops and T-SQL code/scripts
 

–> The exam is divided into multiple modules:

1. Introduction to Microsoft SQL Server 2016
    – The Basic Architecture of SQL Server
    – SQL Server on-premise and cloud-based Editions and Versions
    – Getting Started with SSMS

2. Introduction to T-SQL Querying
    – Introducing T-SQL with basic SELECT Statements
    – Understanding Sets and SET based Queries in relational DBs
    – Understanding Predicate Logic to Filter Data
    – Understanding the Logical Order of Operations in SELECT statements

3. Writing SELECT Queries
    – Writing Simple SELECT Statements, its structure and format
    – Eliminating Duplicates with DISTINCT clause
    – Using Column and Table Aliases
    – Writing Simple CASE Expressions

4. Querying Multiple Tables
    – Understanding Joins
    – Querying with Inner Joins, Outer Joins, Cross Joins and Self Joins

5. Sorting and Filtering Data
    – Sorting Data with ORDER BY clause
    – Filtering Data with WHERE clause Predicates
    – Filtering Data with TOP and OFFSET-FETCH
    – Working with Unknown/Missing Values or three-valued logic (NULL)

6. Working with SQL Server 2016 Data Types
    – Introducing SQL Server 2016 Data Types
    – Working with Character Data
    – Working with Date and Time Data
    – Working with data types conversion

7. Using DML to Modify Data
    – Inserting Data with INSERT and SELECT INTO statements
    – Modifying and Deleting Data with UPDATE, MERGE, DELETE, and TRUNCATE statements

8. Using Built-In Functions
    – Writing Queries with Built-In Functions
    – Using Conversion Functions (CAST & CONVERT)
    – Using Logical Functions
    – Using Functions to Work with NULL

9. Grouping and Aggregating Data
    – Using built-in Aggregate Functions
    – Using the GROUP BY Clause
    – Filtering Groups with HAVING

10. Using Subqueries
    – Writing Self-Contained Subqueries
    – Writing Correlated Subqueries
    – Using the EXISTS Predicate with Subqueries

11. Using Table Expressions
    – Using Views
    – Using Inline Table-Valued Functions (TVFs)
    – Using Derived Tables
    – Using Common Table Expressions (CTEs)

12. Using Set Operators
    – Writing Queries with the UNION operator (and UNION ALL)
    – Using EXCEPT and INTERSECT operators
    – Using APPLY operators (CROSS APPLY and OUTER APPLY)

13. Using Windows Ranking, Offset, and Aggregate Functions
    – Creating Windows with OVER clause with partitioning, ordering, and framing
    – Exploring Window Aggregate and Ranking Functions

14. Pivoting and Grouping Sets
    – Writing Queries with PIVOT and UNPIVOT
    – Working with Grouping Sets, CUBE and ROLLUP Subclauses
    – Using the GROUPING_ID function

15. Executing Stored Procedures
    – Querying Data with Stored Procedures
    – Creating Simple Stored Procedures
    – Passing Parameters to Stored procedures
    – Stored Procedures with output parameters
    – Working with Dynamic SQL

16. Programming with T-SQL
    – T-SQL Programming Elements (Variables, Batches, )
    – Using variables in a Dynamic SQL Statement
    – Controlling Program Flow (IF-ELSE condition, WHILE loop)
    – Working with Synonyms

17. Implementing Error Handling
    – Implementing T-SQL error handling with TRY/CATCH block
    – Implementing structured exception handling with THROW keyword

18. Implementing Transactions
    – Transactions and the database engines
    – Differences between batches and transactions
    – Controlling transactions with BEGIN, COMMIT, and ROLLBACK
    – Error handling with the CATCH block
    – Use of SET XACT_ABORT while handling Transactions


 
Microsoft official link to this exam: https://www.microsoft.com/en-in/learning/course.aspx?cid=20761

Book available as a Training Kit on Amazon, buy it.

SQL Server 2016 articles on my blog: https://sqlwithmanoj.com/sql-server-2016-articles/


SQL Server 2016 Certification Path


 
Microsoft has made few changes to its Certification path for the new version of SQL Server i.e. SQL Server 2016.

And with this post I’m trying to collate and put all exams and certifications in concise and clear manner. Would be happy to accept any comments, changes and suggestions !!!
 

–> The MCSA i.e. “Microsoft Certified Solution Associate” level now contains the new “MCSA: SQL Server 2016” certification, and this replaces the older “MCSA: SQL Server 2012/14” one.
 

–> And the top MCSE i.e. “Microsoft Certified Solutions Expert” level now contains the new “MCSE: Data Management and Analytics” certification. This also replaces the following 2 existing certifications (but retains the underlying exams to earn this MCSE level, which I’ve explained below):
– MCSE: Data Platform
– MCSE: Business Intelligence


 

–> Here are the details of exams at both the levels.

Level 1. “MCSA: SQL Server 2016”: Now to earn this level you need to give either of the following certifications:

   1. MCSA: SQL 2016 Database Development certification
       – Exam [70-761], Querying Data with Transact-SQL
       – Exam [70-762], Developing SQL Databases

   2. MCSA: SQL 2016 Database Administration certification
       – Exam [70-764], Administering a SQL Database Infrastructure
       – Exam [70-765], Provisioning SQL Databases

   3. MCSA: SQL 2016 Business Intelligence Development certification
       – Exam [70-767], Implementing an SQL Data Warehouse
       – Exam [70-768], Developing SQL Data Models

 

Level 2. “MCSE: Data Management and Analytics”: Now to earn this level you need to first get an MCSA on either of the above 3 certifications, or “MCSA: Data Science”. Then need to pass anyone of the below exam:

   – Exam [70-473], Designing and Implementing Cloud Data Platform Solutions
   – Exam [70-475], Designing and Implementing Big Data Analytics Solutions
   – Exam [70-464], Developing Microsoft SQL Server Databases
   – Exam [70-465], Designing Database Solutions for Microsoft SQL Server
   – Exam [70-466], Implementing Data Models and Reports with Microsoft SQL Server
   – Exam [70-467], Designing Business Intelligence Solutions with Microsoft SQL Server


Microsoft released SQL Server 2017 CTP 2.0 – new features, enhancements & what’s new for Linux

April 19, 2017 1 comment

Today on 19th April 2017 Microsoft released the CTP 2.0 version of SQL Server 2017.

As announced earlier with the first CTP release, the new SQL Server 2017 will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.
 

–> Download SQL Server 2017 bits:

To download the SQL Server 2017 you can Register and Download the Full version or Free evaluation version (180 days).

Or, directly download the ISO (~1.7 GB): SQLServerVnextCTP2.0-x64-ENU.iso
 

–> Check version and SQL build:

select @@version

Microsoft SQL Server vNext (CTP2.0) – 14.0.500.272 (X64)
Apr 13 2017 11:44:40
Copyright (C) 2017 Microsoft Corporation. All rights reserved.
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 14393: ) (Hypervisor)

 

–> New Features & Enhancements:

1. Support for Graph Data to model many-to-many relationship, with Nodes and Edges Table types.

2. Resumable online index rebuild, allows you to resume an online index rebuild operation from where it stopped after a failure. You can also Pause and later Resume an Online index rebuild operation.

3. IDENTITY_CACHE option for ALTER DATABASE SCOPED CONFIGURATION, to avoid gaps in the values of identity columns in case a server restarts unexpectedly or fails over to a secondary server.

4. Batch Mode Adaptive Join to improve plan quality.

5. Interleaved Execution for multi-statement T-SQL TVFs to improve plan quality.

6. Adaptive Query Processing, for automatically running database queries efficiently.

7. SQL Server Machine Learning Services (till now, SQL Server R Services), added support for the Python language with existing ‘R’.

8. Run the Python language in-database to scale and accelerate machine learning, predictive analytics and data science scripts

9. And for SQL Server with Linux:
    – Additional SQL Server Agent capabilities
    – Listener for Always On availability groups

… I’ll discuss about all these features in my coming posts.
 

–> Feature Selection Page:


 

–> You can check other features released in CTP 1.x here.
 

–> References:

>> SQL Server 2017 official Page

>> MSDN Blog announcement

>> Docs for SQL Server 2017


New built-in function CONCAT_WS() in SQL Server 2017

January 20, 2017 1 comment

 
In my previous posts I discussed new Functions introduced in SQL Server vNext (or 2018), like STRING_AGG(), TRIM(), TRANSLATE().
 

Here in this post I’ll discuss about one more new function i.e. CONCAT_WS(), here “_WS” means “With Separator”.

This is very similar to the existing CONCAT() function introduced back in SQL Server 2012, which concatenates a variable number of arguments or string values.

The difference is the new function CONCAT_WS() accepts a delimiter specified as the 1st argument, and thus there is no need to repeat the delimiter after very String value like in CONCAT() function.

Also the new CONCAT_WS() function takes care of NULL values and do not repeat the delimiter, which you can see in 2nd example below.
 

Syntax:

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )

 

–> Example #1:

With CONCAT_WS() we will use the delimiter just once and it concatenates the names separated by ‘-‘, and do not repeat the hyphen where the middle name is NULL.

USE [AdventureWorks2014]
GO

SELECT TOP 10 
	CONCAT_WS(' - ', FirstName, MiddleName, LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]

concat_ws-01
 

–> Example #2:

With CONCAT() the delimiter needs to be used after every argument, it concatenates the names separated by ‘-‘, do repeats the hyphen where the middle name is NULL.

SELECT TOP 10 
	CONCAT(FirstName, ' - ', MiddleName, ' - ', LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]

concat_ws-02