Concatenate a string value from table A to a text field in table B, in a scalable way On a daily basis, I am adding values from the following table (table1):

Id    Name      Value
1     'Name 1'  789
2     'Name 2'  789
3     'Name 3'  789


To the text field (Values) in the following table (table2), with a comma:

Id    Name      Values
1     'Name 1'  '123,456,789'
2     'Name 2'  '123,456,789'
3     'Name 3'  '123,456,789'


(So on day one, '123' was added. On day two, '456' was added et.c.)

This is to have a daily history of values, over the last year. The idea of having a "flat" history field (rather than a table with an entry per day) is to have fast reads:


I'm reading a lot from that table
I need all 360 days for every read
I'm writing once a day
I don't need to query the values


I currently have ~32 million rows in each of the two tables above.

Question #1: is this a good way at all of solving my problem of having to display daily values over a year, with fast reads and slower writes?

Question #2: assuming this is an acceptable way of solving the problem - what is the most efficient way of executing the above concatenation? My current solution is to do a MERGE that selects all entries from table1 as a source:

MERGE table2 AS [Target]
USING(SELECT Name, Value FROM table1) AS [Source] ON [Target].Name = [Source].Name
WHEN MATCHED THEN UPDATE SET [Target].Values = [Target].Values + CONVERT(varchar(12), [Source].Value) + ',' + ','
WHEN NOT MATCHED THEN INSERT(Name, Value) VALUES([Source].Name, CONVERT(varchar(12), [Source].Value) + ',');


The reason I am asking these questions is that this takes several hours to run and I'm hitting cannot obtain a LOCK resource at this time errors.

UPDATE #1:
With the very useful comments I got on this question, I learned that the "flat" model I'm using will probably not have any effect on performance as SQL Server reads per page and not per row. Based on that, I'll re-model everything into a more traditional relational structure: one row per entry.

I had to get things working ASAP though, so as a band aid I wrote a stored procedure that updates the flat model without locking. In stead of using a MERGE, it iterates over each row in table1, and for each row, upserts (UPDATE followed by a IF (@@ROWCOUNT = 0) with an INSERT) the corresponding table2 entry. It takes the same amount of time to run, but uses half as much CPU (interesting) and doesn't lock.

Thanks everyone for your inputs.

The stored procedure (Name, Value and Values will have to be renamed):

-- row ID for iteration
declare @id int
select @id = min(id) from table1

-- field variables
declare @name varchar(128),
        @value int

-- iterate through all table1 entries
while @id is not null
begin

    -- select next table1 entry
    select @name = Name, @value = Value
    from table1
    where id = @id;

    -- update corresponding table2 entry
    update table2 
    set Updated = getdate(),
        Values = Values + CONVERT(varchar(12), @value) + ','
    where Name = @name

    -- if no rows were updated, there is entry for this name; insert it instead
    if (@@rowcount = 0)
        begin
            insert into table2 (Created, Updated, Name, Values)
             values (
                getdate(),
                getdate(),
                @name,
                CONVERT(varchar(12), @value) + ',')
        end

    -- update @id to select next row
    select @id = min(id) from table1 where id &gt; @id
end
The suggestion to have one row per value is good advice. It should be the starting point for any relational database design. To ensure all rows are close together (on the same or at least adjacent pages) you will have to have the clustered index on (ID, Date) (or on (Name, Date) depending on which you've chosen for the PK). Next you'll need to make sure there's room for newly-arriving rows beside their siblings. You can do this in one of two ways. One - by setting PAD_INDEX and FILLFACTOR to reserve that space when the index is created. Two - define the index with little or no free space reserved and rebuild it regularly.

That said, what you're proposing is not silly. As long as VALUES is treated as a single attribute and no query tries to dissect it the design is first normal form.

Why would you write to table1 then try to re-write into table2? I'd upsert (i.e. MERGE) into table2 directly, as each value comes into the system.

It seems your primary concern is that the target table is being locked, preventing concurrent work from proceeding. This is caused by lock escalation. The solution is to process the writes in smaller batches. The optimal batch size can only be found by experimentation, but usually a few thousand at a time is about right. I've an answer on this here.

The MERGE statement has had some problems. I haven't checked if they've been fixed recently (&amp; I'm not sure what version you're running) so be aware and read up on the work-around. I propose the following alternative using newer functions which may simplify syntax:

declare @Target table (ID int, x varchar(99));
declare @Source table (ID int, x varchar(99));

insert @Target values (1, 'a'), (2, 'b,c'), (3, 'd');
insert @Source values (1, 'w'), (2, 'x'), (4, 'y');

-- UPDATE @Target with these rows:
select
    t.ID,
    CONCAT_WS (',', t.x, s.x)
from @Target as t
inner join @Source as s    -- INNER JOIN ensures there's a row in both tables
    on s.ID = t.ID

-- INSERT @Target with these rows:
select
    s.*
from @Source as s
where not exists     -- ensures only new rows are attempted
(
    select 1
    from @Target as t
    where t.ID = s.ID
);


Wrap this in the batching technique I suggested above. The downside is a second scan of the data. As the INSERT and UPDATE branches are disjoint sets they could be run in separate, concurrent jobs.