Database

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!

DerekTP123 wrote:

rather than just a later version. It's a real shame, because 15 years ago when I first started using them

SELECT a.*
FROM [MyDB].[dbo].[MyTable] AS a

SELECT [MyReallyLongColumnName] AS ShorterName
FROM [MyDB].[dbo].[MyTable]

SELECT TOP(10) * FROM TABLE ORDER BY NEWID();

create table test (d varchar(10))
insert into test (d) values
('Test 1'), ('Test 2'), ('Test 3'), ('Test 4'), 
('Test 5'), ('Test 6'), ('Test 7'), ('Test 8')
DELETE from test WHERE Id = 3

Id	d
1	Test 1
2	Test 2
4	Test 4
5	Test 5
6	Test 6
7	Test 7
8	Test 8

SELECT * FROM test where id % 2 = 1

create table test2 (Id int, d varchar(10))
insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4'), 
(2,'Test 5'), (2,'Test 6'), (3,'Test 7'), (3,'Test 8')

Id	d
1	Test 1
1	Test 2
1	Test 3
2	Test 4
2	Test 5
2	Test 6
3	Test 7
3	Test 8

;with CTE AS
(
	select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
	FROM Test2
)
SELECT * FROM CTE WHERE r % 2 = 1

SELECT * FROM CTE WHERE rn % 2 = 1

insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4')

select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
	FROM Test2

Id	d	rn	r
1	Test 1	1	1
1	Test 1	2	1
1	Test 2	3	3
1	Test 2	4	3
1	Test 3	5	5
1	Test 3	6	5
2	Test 4	7	7
2	Test 4	8	7
2	Test 5	9	9
2	Test 6	10	10
3	Test 7	11	11
3	Test 8	12	12

;with CTE AS
(
	select *, ROW_NUMBER() OVER (ORDER BY d) as rn
	FROM Test2
)
SELECT * FROM CTE WHERE rn % 2 = 1

CHill60 wrote:

You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?