Press "Enter" to skip to content

Category: Syntax

SQL Server Graph Database

Published 2017-04-21 by Kevin Feasel

The SQL Server team announces graph extensions in SQL Server 2017:

Graph extensions are fully integrated in the SQL Server engine. Node and edge tables are just new types of tables in the database. The same storage engine, metadata, query processor, etc., is used to store and query graph data. All security and compliance features are also supported. Other cutting-edge technologies like columnstore, ML using R Services, HA, and more can also be combined with graph capabilities to achieve more. Since graphs are fully integrated in the engine, users can query across their relational and graph data in a single system.

This is interesting.  One concern I have had with graph databases is that graphs are storing the same information as relations but in a manner which requires two distinct constructs (nodes and edges) versus one (relations).  This seems to be a hybrid approach, where the data is stored as a single construct (relations) but additional syntax elements allow you to query the data in a more graph-friendly manner.  I have to wonder how it will perform in a production scenario compared to Neo4j or Giraph.

Comments closed

Using OUTPUT To Get Change Counts

Published 2017-03-31 by Kevin Feasel

Manoj Pandey shows how to use the OUTPUT clause to determine the number of records inserted, updated, or deleted after a DML statement:

–> Question:

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE separately and store it in a variable so I can get it in the application side?

Thanks !
–> My Answer:

You need to use OUTPUT clause with MERGE statement

Click through for a code sample.  The OUTPUT clause also works for non-MERGE statements like INSERT, UPDATE, and DELETE, though the “get changes by type” problem is really limited to the MERGE statement.

Comments closed

SET NOEXEC ON

Published 2017-02-20 by Kevin Feasel

Andy Kelly shows how to prevent part of a script from running:

 

If we were to hit F5 (or however you execute your TSQL statements in SSMS) without highlighting any statement(s) they would all be executed, one batch after the other. Even if one batch were to fail or we had a THROW in that batch it would fail at that point but execution would continue immediately after the next GO until the end. This is where SET NOEXEC ON comes into play. If I add that at the beginning of the script all succeeding code would not be executed. The statements would only be compiled and not actually run. It would look like this:

SET NOEXEC ON;

PRINT ‘Got Here 1’ ;
GO
PRINT ‘Got Here 2’ ;
GO
PRINT ‘Got Here 3’ ;
GO
PRINT ‘Got Here 4’ ;
GO

This is a useful “accidental F5” protection:  you can put it at the top of your long script to keep from running the whole thing at once.

Comments closed

U-SQL Deprecation Notices

Published 2017-01-26 by Kevin Feasel

Michael Rys has a couple pieces of U-SQL syntax which will be deprecated.  First is partition by bucket:

In the upcoming refresh, we are removing the deprecated syntax PARTITION BY BUCKET and will raise an error.

Thus, if you have not yet updated your table definitions with the previously announced new syntax, please do so now or your scripts will fail starting some day in February!

The second post involves credentials:

Back in October, we announced that we simplified the U-SQL Credentials by merging the password secrets that are being created in Powershell and the other parts of the credential object into credentials that are being completely created with a Powershell command. This reduces one statement from the creation process.

During the initial phase, we did provide support for both kinds of credential objects, and still supported the old syntax.

In the upcoming February refresh, we are now automatically migrating the existing old credentials into the new format and remove the CREATE CREDENTIAL, ALTER CREDENTIAL and DROP CREDENTIAL statements.

If you’re writing U-SQL code, you’ll want to read up on the ramifications and alternatives here.

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

Joins Galore

Published 2017-01-13 by Kevin Feasel

Lukas Eder has a comprehensive guide to joining data using SQL:

Alternative syntaxes: NATURAL JOIN

An more extreme and much less useful form of "EQUI" JOIN is the NATURAL JOIN clause. The previous example could be further “improved” by replacing USING by NATURAL JOIN like this:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

Notice how we no longer need to specify any JOIN criteria, because a NATURAL JOIN will automatically take all the columns that share the same name from both tables that it joins and place them in a “hidden” USING clause. As we’ve seen before, as primary keys and foreign keys have the same column name, this appears quite useful.

There is a high likelihood that you will learn at least one new thing here; for example, check out lateral joins (which SQL Server practitioners know as something else).

Comments closed

Wanted: Named Parameters For Functions

Published 2017-01-11 by Kevin Feasel

Riley Major would like to be able to specify parameter names in function calls:

Now try this:

DECLARE
	@OrderID int = NULL,
	@OrderType int = 1,
	@Qty int = 2,
	@ServiceSpeed int = 3;

SET @OrderID = dbo.GetOrderID (@OrderType, @Qty, @ServiceSpeed);

SELECT @OrderID 'Using SET Syntax';

Now you get a NULL back from the final SELECT. What happened? If you are a careful code reviewer, you might have spotted that the function definition has the @Qty and @ServiceSpeed parameters flipped as compared to the table definition and how we’re calling the function.

But this isn’t an error. There’s no obvious indication that anything is wrong. Imagine if instead of NULL, which would probably break something, you got a different order ID back. Your program would silently continue, oblivious to what is essentially data corruption.

And if you build a function with a large number of parameters, it gets that much easier accidentally to swap just two of them.  Click through for the rest of the story, and check out Riley’s Connect item.

Comments closed