[

Correct... but it didn't convert to FLOAT in other versions either (thank goodness for that!)... It converts numeric literals (constants) that have a value larger than the bounds of...

• This reply was modified 3 years, 8 months ago by Sergiy.

From the data provided the latest newdate for Status = 'Newtwo' should be 10-07-2020, not 10-09-2020, as per required result.

Assuming that's right, this should do:

select Max(NewDate) LatestNewDate,...

CREATE UNIQUE INDEX IX_ID2 ON  #IndexTEST(ID2) INCLUDE (ID);

creates unique constraint, exactly as you requested.

Second statement is not needed at all.

The whole idea is to create a data set for each individual users + their access level. The output of these joins will bring an addition column as a...

Add another row to the sample table:

insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\DB_4\DIFF\ServerName$SQLEXPRESS_DB_4_DIFF_20220327_032554.bak')

Does the query produce the expected result now?

to produce 355753582 rows

Producing 335 mil rows cannot be fast, no matter what indexes are used in a query.

And any suggestions regarding COALESCE replacement might be generally correct but totally...

Glad you got it working.

This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'

That kind of wild card search can't make use of...

Glad you got it working.

This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'

That kind of wild card search can't make use of...

Try this query:

declare @Taxonomie nvarchar(max) = '%rupicapra%'

SELECT TOP 1
--replacing P.AkcesAutoID with identical A.AkcesAutoID, so only values from dbo.Akces are left in SELECT
A.AkcesAutoID, 
Isnull(A.AkcesitPred,'') AkcesitPred, A.Akcesit, A.Rok 
FROM...

• This reply was modified 4 years, 2 months ago by Sergiy.

The problem is here:

COALESCE( R.VendorName, T.ContractorName, 0)

What is the idea behind that zero in there?

i make what you say above and remove index not needed

this is my estimated execution plan after remove indexes no needed

https://www.brentozar.com/pastetheplan/?id=HkBWmbWZ9

and this is actual execution plan after remove indexes...

update s 
set s.PriorityLevel='I1' 
FROM Z2DataCore.parts.SourcingNotMappedParts s 
WHERE (s.PriorityLevel <> 'I1' OR s.PriorityLevel IS NULL)
AND EXISTS (select * from extractreports.dbo.SourcingNotMappedPartsIDI1 g 
     where...