Basically, it escapes any occurrence of the second parameter within the first parameter. So when would we be using it in dynamic SQL? Well, probably the most common way I’ve used it is when I’m building a list of commands I want to run.
Click through for more details, including valid quote characters.
The concatenation of two or more data sets is most commonly expressed in T-SQL using the
UNION ALLclause. Given that the SQL Server optimizer can often reorder things like joins and aggregates to improve performance, it is quite reasonable to expect that SQL Server would also consider reordering concatenation inputs, where this would provide an advantage. For example, the optimizer could consider the benefits of rewriting
A UNION ALL Bas
B UNION ALL A.
In fact, the SQL Server optimizer does not do this. More precisely, there was some limited support for concatenation input reordering in SQL Server releases up to 2008 R2, but this was removed in SQL Server 2012, and has not resurfaced since.
It’s an interesting article about an edge case.
We expect this much from IsNull. However, coalesce is a little different. will take the data type from the first non-null value passed and use that for the table definition. This might not always be what you want because if you pass bits you might get integers. If you pass an array of integers and floats you will get numeric. Be aware if this isn’t what you wanted.
Read the whole thing.
SQL Graph is a similar concept to what is described above, but built in to the core SQL Server engine. This means 2 new table types NODE and EDGE and a few new TSQL functions in particular MATCH(). SQL Graph at the time of writing is only available in SQL 2017 ctp 2.0. You can read more and download ctp2.0 here https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/19/sql-server-2017-community-technology-preview-2-0-now-available/. Once ctp 2.0 is installed there is nothing else you need to do to enable the new graph syntax and storage.
There is an example you can download from Microsoft which is a similar set up to the example in the image above. However I have used some real data shredded from IMDB the internet movie database. This data is available to download from Kaggle https://www.kaggle.com/deepmatrix/imdb-5000-movie-dataset
Click through for a video demonstration as well.
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.
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?
–> 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.
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.