0

I have created a SQL database named: DateTimes.

The database is now filled in with this information seen on this image: See the database table here

As seen in the DateTime column, we can see those DateTime values in ascending order:

201005011600
201005011630
201005011645
201005011700

Now comes the problem, I wonder how to do. As we can see the rows are in DateTime ascending order and now I have a new record that have this DateTime that I want to add to the table:
201005011615

The code I have so far below only ADDS a row at the very end of the table.

I do have 2 questions here:

  1. I now want to insert this record in the correct row which will be the second row in the DataTable. Which means that this record will be inserted at the correct index in the Table (DateTime ascending order)
    How is this possible to do?

  2. Looking at the code, I use: comm.Parameters.Add(new SqlParameter... 5 times. What I wonder here is since I could have many thousands of columns later. If there will be a faster way to .Add all info for all columns here in some kind of batch here instead. Which would mean, 1 .Add, instead of 5 .Adds?

     void insertvalue()
     {
         string connectionString = GetConnectionString();
         string cmdString = "INSERT INTO DateTimes (DateTime,F1,F2,G1,G2) VALUES (@val1, @val2, @val3, @val4, @val5)";
         using (SqlConnection conn = new SqlConnection(connectionString))
         {
             using (SqlCommand comm = new SqlCommand())
             {
                 comm.Connection = conn;
                 comm.CommandText = cmdString;
                 comm.Parameters.Add(new SqlParameter("@val1", 201005011615));
                 comm.Parameters.Add(new SqlParameter("@val2", 0.05044));
                 comm.Parameters.Add(new SqlParameter("@val3", 0.05044));
                 comm.Parameters.Add(new SqlParameter("@val4", 0.05044));
                 comm.Parameters.Add(new SqlParameter("@val5", 0.05044));
                 try
                 {
                     conn.Open();
                     int i = comm.ExecuteNonQuery();
                     if (i != 0) { MessageBox.Show(i + "Data Saved"); }
                 }
                 catch (SqlException ex) { MessageBox.Show(ex.ToString()); }
             }
         }
     }
     static private string GetConnectionString()
     {
         return "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\andre\\source\\repos\\TestDatabaseCreation\\DatabaseTest.mdf;Integrated Security=True;Connect Timeout=30";
     }
    
12
  • 3
    Database tables are, and I can't say this strongly enough, NOT ORDERED. Ordering is ONLY a function of the ORDER BY clause you use when selecting. Commented Oct 2, 2020 at 23:14
  • I am not sure if I understand. Does this mean, that one should not insert a row in the middle of 2 other rows? and instead actually add the row at the very end of the table and then in the very end ORDER BY the DateTime column somehow? Commented Oct 2, 2020 at 23:19
  • 1
    You don't have any choice on where a row is inserted... you order the data when you retrieve the information. Thats just how databases work. Commented Oct 2, 2020 at 23:22
  • 3
    Welcome to Indexes Commented Oct 2, 2020 at 23:29
  • 1
    You don't have control over how SQL Server reads (or stores for that matter) data from the disk. You tell it what you want, and it delivers it in the best way it can. It may or may not cache some in RAM and/or temp tables on disk. Thats determined by the resources available to it. And unless you are pulling 1 record at a time, pulling 100GB into a C# app is going to use RAM as well. Anyway the normal way to approach database performance is first to design your system so that its logically correct, then address performance issues if they arrive. Commented Oct 2, 2020 at 23:33

1 Answer 1

0

Think about how you will query the data in that table. If it is the case that you will ALWAYS want to retrieve the data in DATE order - and also that the DATE is always a reflection of the date and time that you inserted the row, then defining a CLUSTERED index on the DATE column would be appropriate.

You will then generally always add rows to the end of the current page (and then get the next contiguous page on disk) and keep adding to the end of that. Your queries by DATE will be efficient as they will be able to find a starting page and scan contiguously from there to the end of your date range.

NOTE - SQL Server will ALWAYS read the page into memory first before sending it to your client application - that's just the way it works. There is no mechanism to stream the pages from disk directly to your client application. But, don't worry about that as it is as efficient a process as you can get.

Sign up to request clarification or add additional context in comments.

9 Comments

Yes, I think the only requirement in this scenario would be a way to iterate through a datetime range and then if accessing each datetime using a query that would actually then work and it is not necessary that the rows are in order. I will look into CLUSTERED index on the DATE column. A Question there. You mention that SQL Server will ALWAYS read the page into memory. What does this mean if my database is 100 GB. Does that mean that I need 100 GB of RAM?
There was another question which should be the same. I will also need to add new columns dynamically in the table now and then. Normally the columns are grouped like: F1, F2, F3 and G1, G2, G3 etc. Here I thought about having all F columns after eachother and all G columns after eachother. But it is the same princip here. It is okay to add them like: G1, G2, G3, F4, F5, G4, G5 like this and use queries here as well? (I will always only add one row at a time to the database or change values on one row at a time in the database)
When the DB is 100GB in size then SQL Server will start at the first pages, read those in and start to send them to the client, then as more pages are needed the last touched pages are removed from memory to make space. You do not need 100GB of memory.
For the columns, again, SQL Server will decide where on the page to store them. There is some structure based on the data types- but SQL Server manages that itself and you do not need to worry.
That sounds good. So I just add columns and for example use a Query to include all columns that .Contains('G') for example since those columns represents one kind of data?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.