none
Limit of number of rows in a table ?

    Question

  • Is there a limit of how many rows a table can have in SQL compact Edition? I didn't find anything in the documentation, but I get regularly a funny error message "Expression evaluation caused an overflow. [ Name of function (if known) =  ]" when I try to create record number 32768 (is equal to 2 to the power of 15).

    Where is this limit documented ?


    Code Snippet

    const string connectionString = "Data Source='" + dbFileName + "'; Max Database Size = 4091; temp file max size = 4091";


     using (SqlCeEngine testEngine = new SqlCeEngine(connectionString)) {
       testEngine.CreateDatabase();
     }


    using (SqlCeCommand addMValuesTableComand = testDbConnection.CreateCommand()) {
      addMValuesTableComand.CommandText = "CREATE TABLE MValues (MSerieId int, TimeStamp smallint, Value real, PRIMARY KEY(MSerieId, TimeStamp))";
      addMValuesTableComand.ExecuteNonQuery();
    }

    //fill table
    StatusLabel.Text = "fill MValues Table";
    using (SqlCeCommand fillTableComand = testDbConnection.CreateCommand()) {
      int i = 0;
      try {
        int iterationCount = (int)RecordsCountNumericUpDown.Value;

        ProgressBar.Value = 0;
        ProgressBar.Maximum = iterationCount;
        for (i = 0;i < iterationCount;i++) {
          fillTableComand.CommandText = "INSERT MValues VALUES (1, " + i.ToString() + ", " + (i/100.0).ToString() + ")";
          fillTableComand.ExecuteNonQuery();
          ProgressBar.Value = i+1;
        }
      } catch (Exception ex) {
        ErrorTextBox.Text = "Error occured" + Environment.NewLine +
            "Iterations: " + i.ToString() + Environment.NewLine +
             "Error Message: " + ex.ToString();
      }
    }



    Thursday, June 28, 2007 1:32 PM

Answers

  • You are inserting an int value into a smallint (timestamp column). When you reach 32768, the smallint (Int16) type overflows. That has nothing to do with SQL Compact Edition.
    Thursday, June 28, 2007 2:47 PM

All replies

  • I've got a table in CE with 650,000 records, so whatever it is, it's not CE.  Must be something else.
    Thursday, June 28, 2007 2:44 PM
  • In fact, I would say it's the fact that you're using 'int' which is a signed 16 bit value, with a top value of 32767, so that makes sense.  Change your code to use at least a 32 bit integer.
    Thursday, June 28, 2007 2:45 PM
  • You are inserting an int value into a smallint (timestamp column). When you reach 32768, the smallint (Int16) type overflows. That has nothing to do with SQL Compact Edition.
    Thursday, June 28, 2007 2:47 PM