for Archival compression it uses Microsoft XPRESS

https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-xca/a8b7cb0a-92a6-4187-a23b-5e14273b96f8

for columnstore compression the engine uses the vertipaq/xvelocity...

Columnstore index is a workaround for denormalised data storage.

follow basic data normalisation rules and there won’t be any place for columnstore indexes in your database.

not to mention - you’re...

I would still consider them good for history/audit tables as these aren't queried often - saving on space is what would really be important here, not speed of processing the...

I work with it on my shop as we have a huge CRM installation - both in users and db size (3 TB) - you have for sure to play...

a simple group by and max would give the desired result without the need to do a sort/numbering with row_number

ALTER PROCEDURE dbo.GetUniqueValueNames
@MessageProcessFolio nvarchar(75),
StartDate DateTime = NULL,
@EndDate DateTime...

good feedback.

If you don't mind do also gives the SSQL function and how its used - we may be able to help with it.

no need for Memory Optimized.

example

create table abc

(id int)

create clustered columnstore index CCI_abc on abc with (data_compression = columnstore)

no issues - but if you install Developer edition that is either a non prod server or you are breaking licensing terms.

Developer is not a downgrade at all - can...

If you license your box for SQL Enterprise and if you have software assurance costs do not increase with virtualization. Without SA or with SQL Standard then yes it does...

great - do get back to us with performance stats - although as mentioned before doing it SQL alone would also work.

with this you should be able to process all...

I'll see if i get time to do that over next few weeks

will be able to test both with SAN based servers and with PC based SSD and SATA

For your example you would be much better off, in terms of performance, by having an update followed by an insert:

UPDATE p 
  ...

it is working as expected - you are matching on product name - "TEA" - no record exists so both are inserted.

Note that the error would happen even with a...

can you post the full code - in pm if required - it does look ok and I dont see an error on its own

this is a example based on your own code

Define the output columns as required, add the required variables as read only variables and within the script add the following code

from...