So, for all you NOLOCKers out there, you can now save yourselves oodles of time by only using the hint in outer references to your CTEs and Views.
Congratulations, I suppose.
(Please stop using NOLOCK.)
Agreed, whenever possible.
Crazy enough, the last two are correct. It seems that unlike every other language I’ve worked with, all variables are scoped to the same local scope regardless of where in the script they are defined. Demo the first
Wanna see something even more crazy? Check this version out
I should note that I wanted a submit button.
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’ ;
PRINT ‘Got Here 2’ ;
PRINT ‘Got Here 3’ ;
PRINT ‘Got Here 4’ ;
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.
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 BUCKETand 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!
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
If you’re writing U-SQL code, you’ll want to read up on the ramifications and alternatives here.
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.
But then I saw Mike use the following syntax (sans PROFILE and XML):
SET STATISTICS IO, TIME, PROFILE, XML ON;
SET STATISTICS IO, TIME, PROFILE, XML OFF;
Wow, that is a lot easier! (And yeah, using SQL Prompt I can set a snippet to take away typing, but this saves space in any case.)
Read on for more examples.
Alternative syntaxes: NATURAL JOIN
An more extreme and much less useful form of
"EQUI" JOINis the
NATURAL JOINclause. The previous example could be further “improved” by replacing
NATURAL JOINlike this:SELECT * FROM actor NATURAL JOIN film_actor NATURAL JOIN film
Notice how we no longer need to specify any
JOINcriteria, because a
NATURAL JOINwill automatically take all the columns that share the same name from both tables that it joins and place them in a “hidden”
USINGclause. 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).
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.
The concept is very similar to a DEFAULT constraint, with two differences:
1. Will work on an UPDATE operation, without specifying DEFAULT
2. Could be configured to disallow the user from entering a value. My proposed syntax was pretty simple:
AUTO [WITH OVERRIDE] (scalar expression)
Now I realize that 10 years ago, I didn’t take terribly long to consider that WITH was a terrible thing to add to the syntax, and AUTO is a keyword already, so I am going to rename it: AUTO_DEFAULT (scalar expression, [option]). Since I have thought a bit more about this in the years since writing it, I realized there were a few more options that would be nice. I was terrible in college doing syntax parsing, but the syntax itself is not important. Temporal in SQL Server 2016 has syntax that is similar to this for the new temporal columns which I got really excited about the first time I saw it: SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL. Maybe in vNext?
Read the whole thing. Then check out the related Connect item Adam Machanic submitted. I’d love to see that functionality, given how frequently I create these metadata columns.
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.