My previous answer does not cater for scenarios where ALL Name values are "NONE".

Updated code - Please test thoroughly.

WITH cteBase AS (
  SELECT p.PatID, x.pos, x.Namex
...

For more info on using the CROSS APPLY method, please read this article

https://www.sqlservercentral.com/articles/an-alternative-better-method-to-unpivot-sql-spackle

Another option

WITH cteBase AS (
  SELECT p.*, x.pos, x.Namex
       , rn = ROW_NUMBER()  OVER (PARTITION BY p.PatID ORDER BY...

Thanks DesNorton,

Inner join will give me ~40 odd GB used by various table. The intent is to find allocation units that are not part of actual usage hence the...

I dont normally work with DB admin.

However, according to the example this page, your joins appear to be incorrect.

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql?view=sql-server-ver16

On my SQL 2019 box, this def returns the default values.

The default values for parameters are not stored anywhere in the DMVs.

This statement is incorrect

SELECT ProcName = pr.name
     , ParamName = p.name
 ...

As far as I understand it, SQL compiles the plan before it executes.

When you use a variable, SQL compiles a plan for an unknown value.

When you use a fixed value,...

Take a look at the CONVERT function

Starting with SQL 2017, you can use the TRANSLATE function

You have to create a table to store it in

CREATE TABLE foo (col1, col2, etc)

Then insert the results into it

INSERT INTO foo (col1, col2,...

Try the following

Add a computed column

ALTER TABLE C_B_PARTY_COMM
  ADD emailDoman AS (CASE WHEN COMM_TYP_CD = 'EMAIL' THEN SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val)) ELSE NULL END)...

You can find a good splitter function here

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Then you can apply it as follows to split, and rebuild in reverse order

DECLARE @TBL AS table ( COLUMN1 nvarchar(100));

INSERT...

SET NOCOUNT ON;

DECLARE @SAMPLE_SIZE BIGINT = 10000;

DECLARE
      @MinInt       INT = 10000
    , @MaxInt...

Also, if the %of duplicates is higher than the % of singles, then it may be better to copy the singles into a new table and switch out...