Press "Enter" to skip to content

Category: T-SQL

JSON Basics In SQL Server

Published 2017-01-25 by Kevin Feasel

Neil Gelder gives an introduction to JSON in SQL Server 2016:

A new feature in SQL Server 2016 (also available in Azure SQL database) is the ability to create and query  JSON (Javascript object notation) documents, which have now become a common alternative to XML.

Lets look at some examples, I’ll be using tables from the new sample database for SQL Server 2016 WorldWideImporters which you can download from this link

I’m of two minds with JSON support:  I think it’s very useful for building output sets for service calls and might be fine for inputs when you can’t use a table-valued parameter for some reason, but if you’re doing a lot of JSON splitting of data in a table, that’s a violation of first normal form.

Comments closed

Getting The Decimal Part Of A Number

Published 2017-01-24 by Kevin Feasel

Madhivanan JR shows two methods for extracting the decimal value of a number:

One of the questions asked in a forum.  “What are the different methods to extract decimal part from a number?”.

There can be many methods. Two simple methods are as shown below

Click through for the two methods.  I knew method #1 but didn’t think about method #2 because I’ve always thought of the modulo with respect to integers, but it makes sense.

Comments closed

CONCAT_WS()

Published 2017-01-23 by Kevin Feasel

Manoj Pandey points out a new string function in SQL Server vNext:

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.

It’s a small change, but I think a rather useful one.  Do think about how you’d want to interpret NULL values, though, as CONCAT_WS() does not include separators for NULL values.

Comments closed

Subset And Apply Problems

Published 2017-01-11 by Kevin Feasel

Tom Martens explains a class of generic data processing problems:

Subset and Apply means that I have a dataset of some rows where due to some conditions all the rows have to be put into a bucket and then a function has to be applied to each bucket.

The simple problem can be solved by a GROUP BY using T-SQL, the not so simple problem requires that all columns and rows of the dataset have to be retained for further processing, even if these columns are not used to subset or bucket the rows in your dataset.

One quick example of this is running totals of orders for each customer, which Tom answers using T-SQL, R, and Power BI.  Click through for those three solutions.

Comments closed

Parsing JSON In R

Published 2017-01-10 by Kevin Feasel

Tomaz Kastrun shows how to feed a JSON data set into R and turn that into a proper data frame:

JSON has very powerful statements for converting to and from JSON for storing into / from SQL Server engine (FOR JSON and JSON VALUE, etc).  And since it is gaining popularity for data exchange, I was curious to give it a try with R combination.

I will simply convert a system table into array using for json clause.

There’s an R library.  There’s always an R library.

Comments closed

Visual Studio Code

Published 2017-01-05 by Kevin Feasel

Rob Sewell shows how to run SQL Server queries using Visual Studio Code:

Reading this blog post by Shawn Melton Introduction of Visual Studio Code for DBAs reminded me that whilst I use Visual Studio Code (which I shall refer to as Code form here on) for writing PowerShell and Markdown and love how easily it interacts with Githuib I hadn’t tried T-SQL. If you are new to Code (or if you are not) go and read Shawns blog post but here are the steps I took to running T-SQL code using Code

I played around with an early version of this and my thought was that there were some nice improvements over Management Studio (like being able to filter and sort the result set grid without going back to the server), but that there are still too many nice things Management Studio does for me to take a serious look at it.  Still, I’m hopeful that Microsoft moves in the direction of having a fully-featured querying tool for Linux so I can finally join the perpetual Year of the Linux Desktop.

Comments closed

Batches And Stored Procedure Creation

Published 2017-01-04 by Kevin Feasel

Steve Jones has a warning for when you create a stored procedure:

Why is my select code in there? That was designed to be a piece of test code. Shouldn’t the BEGIN..END after the AS define my procedure?

Actually it doesn’t. the procedure doesn’t end until the CREATE PROCEDURE statement is terminated. That termination comes by ending the batch. The CREATE PROCEDURE documentation has this limitation:

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

This means that anything else you have in that batch will be considered as part of the procedure, regardless of BEGIN..END.

Judicious usage of the GO statement can help keep you out of trouble.

Comments closed

Nested Sets

Published 2017-01-04 by Kevin Feasel

Nate Johnson explains the nested sets model:

Put another way, the #3 rule is that you should always operate on the tree (CrUD ops) using stored-procedures and/or triggers that encapsulate all the nitty-gritty details of maintaining the correct position values during said insert/update/delete operations.  Of course, somebody is responsible for writing those stored-procs.  Any volunteers?  Easy now, don’t raise your hands all at once!  Generally, this responsibility falls to the DBA(s) or DBDev(s).

The problem at-hand, in my current situation, was that of “moving a sub-tree”, i.e. taking a node and all its descendants, and moving it to place it under another “parent” node.  In some models, and/or in some languages, this is a simple recursive operation.  However, SQL is not spectacular at recursion — after all, we’re working in a relational engine — so let’s try to play to its strengths:

This is a straightforward look at one of the major hierarchical models in relational design.  Well worth a look.

1 Comment

SQL Server Regex

Published 2016-12-30 by Kevin Feasel

Dev Nambi has a new open-source project:

Databases store text, and the best way to manipulate text is to use a regular expression (‘regex’). Using regular expressions in SQL queries has been possible in many database engines for decades.

Now you can use regular expressions in SQL Server queries, too. I’ve created an open-source project, sql-server-regex, that lets you run regular expressions in T-SQL queries using scalar and table-valued functions.

This is a set of CLR functions which use the built-in .NET regular expressions functionality.  That makes it pretty easy to see how the code works.

Comments closed