CREATE TABLE testvisit (
    primarydoc varchar(50),
    client_id int,
    visitdoc varchar(50),
    doc_id int,
   ...

@jonathan-2 AC Roberts or anyone, do you know if there is a chance to ORDER BY in alphabetical order whatever is a column? Right now they are in a...

@jonathan-2 AC Roberts or anyone, do you know if there is a chance to ORDER BY in alphabetical order whatever is a column? Right now they are in a...

Acceptable, perhaps.  But can you name any major RDBMS that uses singular...

reduce the length to

The numbers should begin with 11. The minimum length of the number should be 10 The maximum length of the number should be 15 Need...

• This reply was modified 2 years, 6 months ago by Jonathan AC Roberts. Reason: zeros after 11

Acceptable, perhaps.  But can you name any major RDBMS that uses singular table names...

DECLARE  @RowsToGenerate as int = 10000000
SELECT DISTINCT TOP(@RowsToGenerate)  x.Num
  FROM (SELECT LEFT('11' + CONVERT(varchar,(CONVERT(bigint,ABS(CHECKSUM(NEWID()))))) + CONVERT(varchar,(CONVERT(bigint,ABS(CHECKSUM(NEWID()))))) ,10+T.RandNum) Num
      ...

Hubert Blaine Wolfeschlegelsteinhausenbergerdorff Sr. won't even be getting his abbreviated name on your database.

Definitely remove the word "Table" from the end of your table names. You'll also have Joe Celko coming in and giving you a hard time if you don't!

I believe it's...

I don't understand why you would have indexes (apart from maybe a clustered one) on a table with only a few rows?

I think the first index to try is indexing table4

You could give it a clustered primary key on (PP_key, AG_key)

or

CREATE NONCLUSTERED INDEX [IX_table4_1] ON [dbo].[table4] ([PP_key]) INCLUDE ([AG_Key])

That's a bit of a strange naming convention. You would normally call the column t3_Key not t1_Key.

If t1_key is a primary key to #table1 then when it joins to #table3 on t1_Key it can only return up to 6 rows from #table1 as #table3 only has...

I thought I'd write a test to compare the performance of both methods:

Set up some test data using dbo.fnTally with about 25,000 rows of data:

            
February 24, 2023 at 12:28 am
            
#4153518

            
            
                                                
        

I believe that it's a lot more efficient to use a windowed function here.  Since we don't know whether the idPointsrv is monotonically increasing, I've coded it in a...