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 (...

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...

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 the partitions

DROP TABLE IF EXISTS #test;
CREATE TABLE #test ( ValueId varchar(30));

INSERT  #test ( ValueId )
VALUES ( '1' )
     , ( '99'...