In SQL Server, you can use the SET STATISTICS PROFILE statement to display the profile information for a T-SQL statement.
STATISTICS PROFILE works for ad hoc queries, views, and stored procedures.
When STATISTICS PROFILE is set to ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution.
In SQL Server, you can use the SET STATISTICS IO statement to generate detailed information about the amount of disk activity generated by a T-SQL statement.
In graphical tools like SSMS and Azure Data Studio, you can view this information in the Messages tab.
Continue readingIf you encounter error Msg 491, Level 16 “A correlation name must be specified for the bulk rowset in the from clause” in SQL Server, it’s probably because you’re trying to read a file without using a correlation name.
Continue readingIf you’re trying to extract values from a JSON document, but one or more of the values on the same level have duplicate keys, you might run into problems if you try to extract those values using JSON_QUERY() or JSON_VALUE().
Both of those functions will only return the first value that matches the path.
Fortunately, there’s another option.
The OPENJSON() function will return all values from any duplicate keys on the same level.
In SQL Server, you can use the SESSIONPROPERTY() function to return the SET options settings of a session.
These are things such as your ANSI_NULLS setting, ANSI_PADDING, QUOTED_IDENTIFIER, etc.
To do this, you simply pass the desired setting name to the function as a string, while selecting the function.
Continue readingIf you’re trying to run some OPENJSON() code in SQL Server, but you’re getting error Msg 319, Level 15 “Incorrect syntax near the keyword ‘with'”, one possibility is that you really do have a syntax error.
But if you’ve check and double checked, and you’re convinced there’s no syntax error, it could actually be that the error is a side effect of having the wrong database compatibility level.
Normally you’d get error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.” when using a database compatibility level of lower than 130, but in some cases, SQL Server finds a problem with the WITH clause first.
SQL Server has a table-valued function called OPENJSON() that creates a relational view of JSON data.
When you call it, you pass a JSON document as an argument, and OPENJSON() then parses it and returns the JSON document’s objects and properties in a tabular format – as rows and columns.
If you encounter error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.”, you are probably trying to use the OPENJSON() function on a database with a compatibility level of less than 130.
OPENJSON() is only available under compatibility level 130 or higher.
To fix this, either increase the compatibility level of your database to 130 or higher, or change to a database that already has the appropriate compatibility level.
Continue readingIn SQL Server, you can use the ALTER DATABASE statement to change the compatibility level of a database.
This can be useful if you have a database that was created in an earlier version of SQL Server, but you now need to use features that are only available with a later compatibility level.
Continue readingIn SQL Server, you can use the @@ROWCOUNT system function to return the number of rows affected by the last T-SQL statement.
For example, if a query returns 4 rows, @@ROWCOUNT will return 4.