Database

Jörgen Andersson wrote:

The other problem is that the ID sequence is having gaps in it.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

DECLARE
        @LastID int = 0,
        @NextID int,
        @RC int = 1;
    
WHILE (@RC > 0)
BEGIN
    SELECT  TOP 5000
            @NextID = s.id
    FROM    Source s WITH(NOLOCK)
    WHERE   s.id> @LastID
    ORDER BY s.id
    ;
    UPDATE  t
        SET  ------
    FROM    Source s
    JOIN    Target t WITH(NOLOCK) ON t.id = s.id
    WHERE   s.id > @LastID 
        AND s.id <= @NextID 
    ;
    SET @RC = @@ROWCOUNT;
    SET @LastID = @NextID ;
END

Quote:

SQL

UPDATE  t
...
FROM    Source s
JOIN    Target t WITH(NOLOCK) ON t.id = s.id
...

Avoid using NOLOCK on SQL Server UPDATE and DELETE statements[^]:

SQL

UPDATE t1 
SET t1.x = something
FROM dbo.t1 WITH (NOLOCK)
INNER JOIN ...;

jschell wrote:

Because the key is generated on the client not the server.

public int GetNextPrimaryKey(string collectionName)
{
    lock(_lockObj)
    {
        // Get the PrimaryKeys collection from the DB
        IMongoCollection<PrimaryKeyEntity> pkCol = GetCollection<PrimaryKeyEntity>("PrimaryKeys");

        // Attempt to find the row for the collection 
        PrimaryKeyEntity entity = pkCol.Find(x => x.CollectionName == collectionName).FirstOrDefault();

        // If it doesn't exist...
        if (entity == null)
        {
            // Create and insert the row for the desired collection
            entity = new PrimaryKeyEntity
            {
                PrimaryKey = 1,
                CollectionName = collectionName
            };
            pkCol.InsertOne(entity);
        }
        else
        {
            // Increment the PK
            entity.PrimaryKey = entity.PrimaryKey + 1;

            var filter = Builders<PrimaryKeyEntity>.Filter.Eq(x => x.CollectionName, collectionName);
            var update = Builders<PrimaryKeyEntity>.Update.Set(x => x.PrimaryKey, entity.PrimaryKey);
            var result = pkCol.UpdateOneAsync(filter, update).Result;
        }

        return entity.PrimaryKey;
    }
}

Kevin Marois wrote:

So what if I created a PK table on the sever:

DerekTP123 wrote:

Please, tell me my hosting provider is talking rubbish!