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 ...
- 78k
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 ...
- 386
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 ...
- 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 ...
- 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 ...
- 65.6k
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 ...
- 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 ...
- 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-...
- 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 ...
- 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 ...
- 40.1k
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 ...
- 78k
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 ...
- 65.6k
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 ...
- 176k
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 ...
- 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.
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 172
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 ...
- 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 ...
- 176k
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 ...
- 40.1k
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 ...
- 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 ...
- 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 ...
- 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 ...
- 69.2k
Only top scored, non community-wiki answers of a minimum length are eligible