SQL Server SELECT MAX and Add 1-CodePudding

The following C# code was working with MySQL server correctly to get the MAX value of a column from a table and at the same time this query adds 1 to the value like this:

    SqlDataReader dr = new SqlCommand("SELECT (MAX(Consec)  1) AS NextSampleID FROM Samples", Connection).ExecuteReader();  
    while (dr.Read())
    { //in case of Maximum value of Consec = 555, the expected result: A556
      txtSampleID.Text = "A"   dr["NextSampleID"].ToString();
    }

However this code does not work anymore after migrating the DB from MySQL to SQL Server, the result is the same if MAX(Consec) = 555 the result after running the query is A555, it does not add 1 like before when using MySQL server.

Question: What is the correct query to get the MAX value of Consec and how to add "1" to the result of MAX in the same query?

CodePudding user response：

The MySQL query is wrong and won't work except in trivial applications, with only a single user, no deletions and no relations :

Concurrent calls will produce the same MAX value so result in the same, duplicate next value
Deleting records will reduce the MAX value, resulting in previous ID values getting assigned to new rows. If that ID value is used in another table, the new record will end up associated with rows it has no real relation. This can be very bad. Imagine one patient's test samples getting mixed with another's.
Calculating the MAX requires locking the entire table or index, thus blocking or getting blocked by others. Given MySQL's MVCC isolation though, that wouldn't prevent duplicates as concurrent SELECT MAX queries wouldn't block each other.

It's possible MAX 1 would work in a POS application with only one terminal generating invoice numbers, but as soon as you added two POS terminals you'd risk generating duplicate invoices.

In an e-Commerce application on the other hand, it's almost guaranteed that even if only two orders are placed per month, they'll happen at the exact same moment, resulting in duplicates.

Correct MySQL solution and equivalent

The correct solution in MySQL is to use the AUTO_INCREMENT attribute :

CREATE TABLE Samples (
     Consec INT NOT NULL AUTO_INCREMENT,
     ...
);

If you want the invoice number to contain other data, use a calculated column to combine the incrementing number and that other data.

The equivalent in SQL Server is the IDENTITY property :

CREATE TABLE Samples (
     Consec INT NOT NULL IDENTITY,
     ...
);

Sequences

Another option available in SQL Server and other databases is the SEQUENCE object. A SEQUENCE can be used to generate incrementing numbers that aren't tied to a table. It can also be reset, making it ideal for accounting applications where invoice numbers are reset after a specific period (eg every year).

Since a SEQUENCE is an independent object, you can increment and receive the new value before inserting any data in the database with NEXT VALUE FOR eg :

SELECT NEXT VALUE FOR seq_InvoiceNumber;

NEXT VALUE FOR can be uses as a default constraint for a table column the same way IDENTITY or AUTO_INCREMENT are used:

Create MyTable (
...
Consec INT NOT NULL DEFAULT (NEXT VALUE FOR seq_ThatSequence)
)

Multi-table sequences

The same sequence can be used in multiple tables. One case where that's useful is assigning a Document ID to data imported from multiple sources, stored in different tables, eg payments.

Payment providers (credit cards, banks etc) send statements using different formats. Obviously you can't lose any information there so you need to use different tables per provider, but still be able to handle payments the same way no matter where they came from.

If you used an IDENTITY on each table you'd end up with conflicting IDs for payments coming from different providers. On eg the OrderPayments table you'd have to record both the provider name and ID. Generating a single view of payments would end up with ID values that can't be used by themselves.

By using a single SEQUENCE though, each record would get its own ID, no matter the table.