Microsoft SQL Server : inserting a list problem; mixes up the list

Question

Situation: I have this method for inserting a list into one of my SQL Server table:

public void InsertDataAtOnce(List<string> values, List<string> millisecs, string table, string[] parameters)
{
        connection = new SqlConnection(connectionString);
        query = $"INSERT INTO {table} ({parameters[0]}, {parameters[1]}) VALUES (@{parameters[0]}, @{parameters[1]})";

        try
        {
            connection.Open();

            command = new SqlCommand(query, connection);
            command.Parameters.Add($"@{parameters[0]}", SqlDbType.NVarChar);
            command.Parameters.Add($"@{parameters[1]}", SqlDbType.NVarChar);

            for (int i = 0; i <millisecs.Count-2; i++)
            {
                //command.Parameters.Clear();
                command.Parameters[$"@{parameters[0]}"].Value = millisecs[i];
                command.Parameters[$"@{parameters[1]}"].Value = values[i];

                command.ExecuteNonQuery();
            }

            command.Dispose();
            connection.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Connection Error");
            Console.WriteLine(ex);
        }
}

Imagine my inserted table information should be like this (my table should hold around 1700 values) and I pass this information tho the method:

 1. 0.5 15
 2. 0.6 24
 3. 0.7 29
 4. 0.8 32
 5. 0.9 45

However, when I open the SQL Server Management Studio and check the inserted data, it mixes up the value order, so with this data, the result would be:

 1. 0.5 15
 2. 0.6 24
 3. 0.9 45
 4. 0.8 32
 5. 0.7 29

And so on. When I have my data, it mixes the data up in chunks, so the first few hundred values would be okay, then it jumps to the values, that should be in the end or the middle, returns back to the first values for a while and so on.

So what could be the problem here? I checked the Lists that I'm passing and they seem to be in order I added the data, just how they should be.

Answer 1

A SQL table represent unordered sets. There is no ordering. However, you can use an identity() column to preserve insertion order:

create table t (
    t_id int identity(1, 1) primary key,
    . . .
);

When you insert into the table, do not include t_id into the column list. It will be set automatically.

Then you can fetch the results "in order" by doing:

select t.*
from t
order by t_id;

Answer 2

The physical order of values getting inserted into a database table is identified based on an index that is called Clustered Index.

If it's important to you to insert the values in a specific order, you can create a clustered index on a specific field (for example an integer field that could also be or be not your primary key) and then provide the clustered index value in your insert statements before inserting them to the table.

Let's assume you have added an integer field with the name Id and created a clustered index on that field. If you insert the current values as:

    Id   p1 p2
 1.  1  0.5 15
 2.  2  0.6 24
 3.  3  0.7 29
 4.  4  0.8 32
 5.  5  0.9 45

Then if you query the table with an order by clause on the Id field the results you receive will be exactly as you have inserted them:

    Id   p1 p2
 1.  1  0.5 15
 2.  2  0.6 24
 3.  3  0.7 29
 4.  4  0.8 32
 5.  5  0.9 45