2022 Developer Survey is open! Take survey.
42 votes
Accepted

SQL Server 2019 executes unreachable code

This is a bug with the inlining of Scalar UDFs (or perhaps a bug with the query optimiser that is being exposed more by scalar UDF inlining). You can use WITH INLINE = OFF to turn off inlining for ...
user avatar
27 votes
Accepted

Where to download SQL Server 2019 Express LocalDB Installer MSI from

The documentation page containing download links for LocalDB 2016 onward is: SQL Server Express LocalDB - Installation media Direct download links are not officially supported. The following LocalDB ...
user avatar
15 votes

Dodgy T-SQL Query Execution driving me crazy

is there any way to get that TOP N to not be so heavy? I think there's a little bit of a misunderstanding here about how execution plans work. That number is just the estimated cost, which is a model ...
user avatar
  • 27.1k
13 votes

When `nvarchar/nchar` is going to be used with SQL Server 2019?

UTF-8 support gives you a new set of options. Potential space savings (without row or page compression) is one consideration, but the choice of type and encoding should probably be primarily made on ...
user avatar
  • 69.2k
13 votes
Accepted

How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file?

To start with, the older xp_delete_file (also undocumented) is quite limited: It can only delete either backup (.bak / .trn) or report (??) files (I believe it actually scans the first part of them ...
user avatar
13 votes
Accepted

Why is a parallel top N sort apparently much more CPU efficient than a serial top N sort?

It's hard to be sure based on an anonymised plan without a repro, but the first mechanism that springs to mind is deferred expression evaluation. In the serial plan, the Sort may be responsible for ...
user avatar
  • 69.2k
12 votes
Accepted

In the following inventory system, is it possible to force row locks to avoid deadlock and ensure it works as intended?

My first question is, will these update statements work as intended? Very likely, but not certain. SQL Server guarantees it will honour the semantics of the query, and the level of ACID compliance ...
user avatar
  • 69.2k
12 votes

MERGE into a view with INSTEAD OF triggers

Option 1 It is possible that the inserted reference is not allowed here because SQL Server does not have access to the value actually inserted by the instead of trigger on the view. SQL Server instead-...
user avatar
  • 69.2k
11 votes
Accepted

After upgrading to SQL Server 2019, function throws "insufficient memory" error

Cause SQL Server is trying to inline the function but failing due to the complexity. Using so much memory while doing so is unexpected and almost certainly a bug. A definition for the nested ...
user avatar
  • 69.2k
11 votes
Accepted

SQL Server 2019 performance worse than 2012... am I missing something?

I believe you have just discovered why the recommended upgrade process is to to upgrade your database, enable the Query Store, and test before increasing the database compatibility level. Change the ...
user avatar
11 votes
Accepted

User-defined function returns first character only although RETURN VARCHAR(10) is defined?

This is a bug with SQL Server inline functions in 2019 RTM. DECLARE @Salary MONEY = 100440; SELECT dbo.ufn_GetSalaryLevel(@Salary) The expression in the constant scan (with CONVERT_IMPLICIT changed ...
user avatar
11 votes
Accepted

What is difference between Arabic_100_CS_AS_KS_WS_SC_UTF8 and Latin1_General_100_CS_AS_KS_WS_SC_UTF8?

Yes, all _UTF8 collations use code page 65001 as that is the code page for UTF-8. You can even use 65001 in a DOS / Command Window via: chcp 65001 though not all programs and fonts will work ...
user avatar
11 votes
Accepted

AppDomain 2 SSISDB is Marked for Unload due to Memory Pressure

About your server This query spills badly in two places, despite a 29 GB memory grant. That's about the high end of what a query can ask for on your system with max server memory set to 115 GB. The ...
11 votes

Add partitioning to unpartitioned table with minimal downtime

create a new partitioned table with enough partitions to happily divide up all the existing and some-reasonable-time-into-the-future future data create a view that union alls the two tables (may be ...
user avatar
11 votes
Accepted

SQL Server / SSMS taking up lots of disk space

You might want to use WinDirStat or TreeSize Free to see which files are actually taking up space. It may not even be what you think. SQL Server doesn't take backups on its own, so it's unlikely those ...
user avatar
  • 33.4k
10 votes

How do you enable SQL Server 2019's result set caching?

Currently, in CTP 2.3, in so far as I can tell - you cannot enable this, even under DAC.
user avatar
10 votes
Accepted

Execution plan doesn't show memory grant

Memory is granted at runtime, so the information you are looking for can only be seen in a post-execution (a.k.a "actual") execution plan, not a pre-execution ("estimated") plan. If you are using SQL ...
user avatar
  • 69.2k
10 votes
Accepted

When should the IGNORE_DUP_KEY option be used on an index?

It's definitely an atypical setting to enable for an index. I don't think you're going to find many people who jump up and talk about using it. Of course, there are a couple helpful posts on the ...
user avatar
  • 33.4k
10 votes
Accepted

Upgrading from SQL Server 2012 SP1 to SQL Server 2019

This handy page from Microsoft should be very useful for you as it explains in detail how to carry out a migration and the tools and methods available: Supported Version and Edition Upgrades for SQL ...
user avatar
  • 546
10 votes

Dodgy T-SQL Query Execution driving me crazy

Try creating the following indexes (I'm using what I think is something similar to your naming convention for index names): CREATE INDEX DomainTable$DomainIP$IDNum ON dbo.DomainTable (DomainIP, IDNum ...
user avatar
  • 67.1k
10 votes
Accepted

Changing to compatibility level 150 makes SQL Server consume all available memory

You're encountering a known bug with scalar UDF inlining. You can disable inlining using one of the methods here (or by using a lower compat level, as you've discovered yourself): Disabling Scalar ...
user avatar
  • 27.1k
10 votes
Accepted

SQL Server Agent fails after installing SQL Server 2019 CU2

Thank you for reporting this. There is a feedback item in https://feedback.azure.com/forums/908035-sql-server/suggestions/39754837-ssms-18-4-sqlserver-2019-cu2-object-explorer-fails. All updates will ...
user avatar
10 votes
Accepted

Clone a database from SQL Server 2019 to 2017

Just succeeded to clone the Database, using on local: In the SSMS, right click on the database to export, then Tasks => Export Data-tier Application it creates a bacpac file. Copy the backpac to ...
user avatar
  • 251
10 votes
Accepted

MSSQL table variable identification

It is not possible to identify a table variable in tempdb's metadata by the variable name, as the table name is created with the hex name there and the mapping to the variable at runtime is done ...
user avatar
10 votes
Accepted

How to enhance update to be faster for small count of updated rows?

Adding to Brendan’s answer, swap this conversion inner join Nop_AcceptedValuesOption AVO with(nolock) ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value to inner join ...
user avatar
10 votes

Is there a deterministic relation between the page life expectancy and disk IO throughput?

It’s pretty far off the mark to suggest such a direct relationship between database data file bytes/s throughput - which can be measured directly over a period of time - and SQL Server page life ...
user avatar
  • 1,158
9 votes
Accepted

SSRS error "something went wrong please try again later" in web portal

After some digging I found plenty of unconnected issues, but finally managed to solve them and wanted to share them all in one place so nobody else waste that much time if hits the same issue (or some ...
user avatar
  • 463
9 votes
Accepted

SQL Server in-memory optmized tempdb metadata memory usage continually growing

Pam Lahoud SQL Enterprise Team Principal Program Manager at Microsoft said on twitter: and contributed via comment here: There are a few known issues that could be coming into play here. It's best ...
9 votes
Accepted

How to tell if an index has OPTIMIZE_FOR_SEQUENTIAL_KEY turned on?

The system view sys.indexes has a bit column named optimize_for_sequential_key. It is not on by default because it is intended for use only where significant last-page contention is observed. See ...
user avatar
  • 69.2k
9 votes

How does SQL Server handle parallel UPDATE transaction that prospectively would result into rows that would match the WHERE clause of 1st transaction?

If you're asking specifically what might happen when T1 has completed but not committed, the general answer is that T1 is unaffected by any changes made by T2*. T2 might or might not block depending ...
user avatar
  • 69.2k

Only top scored, non community-wiki answers of a minimum length are eligible