SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Managed Instance Memory per vCore Limits

The Voice of the DBA

Actively Choosing Compatibility

SQL Server databases have had a compatibility level for a long time. This is a setting that enables the database to process code as if it were a particular version. The levels go from 80 (SQL Server 2000) to 160 (SQL Server 2022). Each time (almost) a new version of SQL Server has been released, there has been a new compatibility level.

However, not all versions can support all levels. For example, my SQL 2022 instance can support levels back to 100 (SQL Server 2008). If I wanted to get a database to act like a version older than 2008, for example, 2005, I would need to install a SQL Server 2012 or older instance. There is a table of engine versions and supported compatibility levels on the ALTER DATABASE Docs page.

When you create a new database, by default, it is at the current compatibility level. However, if you upgrade a database, the level might not change. There are some limits to which versions are supported, so an upgrade might change your database.

An administrator might choose to keep an older level for compatibility purposes. Perhaps your code has an identifier that is now a keyword. Perhaps you expect some code to behave a certain way. However, not all changes are protected by the compatibility level. Most of the time, an administrator must manually change this, which is something that can slip through the cracks. If you don't change this right away, likely it isn't going to change.

There was an interesting post from Brent Ozar recently that explained a bit about compatibility levels in a SQL Server database. I wonder how many people actually actively choose a level or they just accept the default level for that instance. Brent gives some advice in the post, and his recommendations vary a bit, depending on whether you are happy with the system or not. He also recommends measuring your system and then evaluating a change. Especially if this is a database for vendor software.

I don't think changing or updating this is a priority, but I also think that being aware of when your level doesn't match the instance and documenting this is important. At some point, through many upgrades, you might find your level isn't supported any longer. Then your database might have immediate issues. A good monitoring system can let you know when you have mismatches that can be evaluated when there is time.

Each version of SQL Server adds new features, like the changes for Intelligent Query Processing. In general, we want to take advantage of these if we can. However, not all workloads respond positively, so as Brent mentions, you need to test and evaluate your workload. Hopefully, you have a clear "things are better" or "things are worse" when changing levels. When you get some queries that perform better and some worse, then you have some choices to make. Often the default is "do nothing," which may or may not be the best decision, but the devil you know is sometimes easier to deal with than the one you don' t know.

That's fine but consciously make that choice. Keep an eye on your system and don't just accept defaults, whether those are from Microsoft or the ones you've left after an upgrade. Actively manage your systems to get the best performance you can for clients.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

Calculating Prime Numbers With One Query

wfw311 from SQLServerCentral

This article looks at calculating prime numbers less than n with one query.

Building a Kimball Data Warehouse

ab5sr from SQLServerCentral

Learn about the Kimball method for data warehouses and how you can get started building one.

Are PostgreSQL memory parameters magic?

Additional Articles from SimpleTalk

Ordinary PostgreSQL users often do not know that PostgreSQL configuration parameters exist, let alone what they are and what they mean. Some parameter tuning can improve the database performance, and we are usually talking about 10%, 20%, and in rare cases, up to 50% performance improvement.

From the SQL Server Central Blogs - Microsoft Announcement: Microsoft Fabric

gana20m from Ganapathi's MSSQLLover

Yesterday at Microsoft Build, a significant announcement took place—the introduction of Microsoft Fabric, which is now available for public preview. Satya Nadella, the CEO of Microsoft, went as far...

From the SQL Server Central Blogs - Introduction to SQL Server Query Store

Matthew McGiffen from Matthew McGiffen DBA

Introduced with SQL 2016, Query Store was, probably without doubt, the most anticipated and talked out new feature. In this post we'll just take a brief look at it,...

SQL Server Execution Plans eBook, Third Edition, by Grant Fritchey

SQL Server Execution Plans, Third Edition, by Grant Fritchey

Grant Fritchey from SQLServerCentral

Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. This book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Managed Instance Memory per vCore Limits
There are three editions of Azure SQL Managed Instance, as of June 2023 (General Purpose, Business Critical, and Memory-Optimized). What are the amounts of RAM that I get per vCore for these editions?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Getting the Winners

I get a JSON string that looks like this:

DECLARE @JSON NVARCHAR(MAX) = N'{
"WinnerIDs": [11,14,15,16,45],
"Race": "Furry Scurry",
"RaceStartDate": "2022-05-20T08:00:00",
"RaceEndDate": "2022-05-20T10:00:00",
"Entries": 100
}';

I want to parse out the values of the winners to show each on a separate line of the result set. I try a couple of things.

First, this:

SELECT 
 oj.Race, oj.WinnerIDs
FROM
  OPENJSON (@JSON)
  WITH
    (Race NVARCHAR(100) '$.Race'
   , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj

Second, this:

SELECT 
 oj.Race, oj2.WinnerIDs
FROM
  OPENJSON (@JSON)
  WITH
    (Race NVARCHAR(100) '$.Race'
   , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj
 INNER JOIN 
  OPENJSON (@JSON)
  WITH
    (Race NVARCHAR(100) '$.Race'
   , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj2
   ON oj.WinnerIDs = oj2.WinnerIDs

Third this:

SELECT 
 oj.Race,
 oj.[value] AS WinnerID
FROM
  OPENJSON (JSON_QUERY(@JSON, '$.WinnerIDs')) AS oj

Fourth and lastly, this:

SELECT 
 oj.Race, oj2.[value] AS WinnerIDs
FROM
  OPENJSON (@JSON)
  WITH
    (Race NVARCHAR(100) '$.Race'
   , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj
     CROSS APPLY OPENJSON(oj.WinnerIDs) AS oj2

Which of these returns these results?

RaceWinnerIDs
Furry Scurry11
Furry Scurry14
Furry Scurry15
Furry Scurry16
Furry Scurry45

Answer: Fourth code

Explanation: The last query with the cross apply will work. The first and second ones return a single row with all winnerIDs in one column. The third one doesn't work, and the last one does. Note, there are other ways to do this. Ref:

Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration

Vulnerability Assessment removed? - In Brent Ozar's list of links today, I learned that Microsoft has removed the Vulnerability Assessment tool from SSMS as of v19.1. I recently started using it in my SQL Server environment and found it a great tool -- I was able to demonstrate to management why some of their security shortcuts were a bad […]

SQL Server 2017 - Development

Need help with a Query logic - I have a table which tracks of activity when users from one specific app login and logout. But I have seen some cases where let's say a user logins at 11:40 on March 24th and then logins again at 11:55 on the same day without logging out. The application doesn't allow that but I am […]

SQL Server 2016 - Administration

Database copy issue through - COPY DB WIZARD - I have a production server with SQL server 2016 version (13.0.5026.0) and a database with 25 GB of Data and 525 tables in it. Now, I want to transfer this production DB to my development environment with SQL server 2016 version (13.0.5026.0). I am using DB copy wizard for DB copy to the new instance […]

SQL Server 2016 - Development and T-SQL

Help with a SQL Unpivot - I am trying to use unpivot to extract some data, however, the results are getting messed up on at least one record. By this I mean it is out of order which it should be CLNUM: MODEL: CLSTATUS: CLSTATDATE: CLSTOP: Here is the query any help or advice would be appreciated. WITH ClientsWithOpenMattersCTE (ClientNo, HowMany) […]

SQL help with partition - With the following data, I need to create a column that will display a counter for each VID, Acc where PrgFld is not 'XXX' I am trying to do this with partition, but it's increasing the counter when PrgFld is XXX. I would prefer to not increase the counter or show 0 for XXX. Is […]

NOT LIKE Alternatives in WHERE clause - Hello, I need help to improve an existing (hence I cannot change table's structure nor the content's logic) WHERE clause, please. The table consists of returned products with their respective reason(s). In case there is more than one reason, they are concatenated. The request as shown in the mock-up below, has to retrieve a list […]

Administration - SQL Server 2014

Database backups going to device - Hi Friends, How can I know the exact device where my backups are going on? I only see {67BAB2D0-A1DC-44F8-BF73-EBAFD5AE3220}16 as one device. However, I do not find out where are my backup files located. Some databases are going to a particular drive, but others are using that expression, Thank you, Best Regards,

SQL Server 2019 - Administration

Upgrading from 2016 to 2019 with Encryption - Hi, I have a few AWS EC2 instances currently running SQL Server 2016. I'd now like to upgrade these to SQL Server 2019 but the problem is that some of the data has been encrypted at the column level. I understand that, after SQL 2016, the encryption algorithm changed from SHA1 to SHA2. Would this […]

SQL Server 2019 - Development

how to de escalte a isolation level, and are there any implications. - hi, I have one nested transaction and want to deescalate to outer transaction's isolation/ or lower isolation. is it the right way? SET TRANSACTION ISOLATION LEVEL read committed; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to do some job in read commited SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to […]

Advanced SQL query help - Hi, I am looking for some help with two queries using the following table/data. It would be great to see the most efficient way to achieve these. Queries 1: Show all homes with rent above the average rent value for their Company 2: Show the number of homes registered 0-3 months, 4-6 months, 6-12 months, […]

MSSQL Service Broker DSQL operation on another database causing Broker failure - I have established a Service Broker configuration based on Eitan Blumin's excellent example. Advanced Service Broker Sample: Asynchronous Triggers I have crafted my own Stored Procedure. I can get the example to work when I'm performing activities within the same database. If I try to operate outside the current DB I get errors. I'm new […]

Extracting multiple fields from one column - Hello, I have a situation. I am trying to show each distinct event category, event action and event label from 'Hits' column and show the number of times each event occurred and the month that the event occurred– for labels containing “GNAV”. I am not sure why but something is wrong. Do you have […]

Analysis Services

How can I show the last opening period for each project in my project dimension - I have two attributes hierarchies in my dimension [Dim Project]: Project Code Opening date Project A with 3 opening date : 2023-01-01 , 2023-01-02 and 2023-01-03 Project B with 2 opening date : 2023-01-02 , 2023-01-04 MDX query : Select [Dim Project].[Project Code] .[Project Code].Allmembers * [Dim Project].[Opening date].[Opening date].Allmembers on 0 [Measures].[Measure1] on 1 […]

SQL Server 2022 - Administration

Allow only encrypted connections. - Hi In SQL22, Is there a way to allow only / force encrypted connections? I have 'force encryption' on and have cert installed: But when I connect via SSMS I can unselect ' Encrypt Connection' and it connects. My understanding is that means that it is allowing non-encrypted connections: How do I make my […]

SQL Server 2022 - Development

SQL Query - Hi, i have a sample table: table ID flag name 1 0 test1 1 0 test2 1 1 test3 2 0 test4 2 0 test5 3 1 test6 4 […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.