00000010 - Random SQL Server Databases "Facts"

Remember your database courses in which the professor explained how databases can hold large amounts of data so the design should make several considerations to enable proper querying, maintenance, and handling of data?

Well, welcome to the real world.

Back in the university practices, courses, heck even investigations, the concept or handling of a large database was distant. Even then large was a rather ambiguous concept.

Here in the real world I've had some curious experiences with large databases... say.. tables with more than 20 million registers?

Here are some considerations you must take and facts you probably had not heard of before... hey, at least I know I hadn't prior to this.

**Please note the considerations below apply to C# and SQL Server implementations

---

Error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

At first you may not run into this problem as probably you may not start working in a already highly populated database.

However, time will only enable database growth and it's just a matter of time for your SQL queries to present this issue everywhere, as long as the database growth is exponential or completely random.

Luckily, this error is well documented on the web. The solution I've found to be the easiest to implement is as follows.

If you, like me are using SqlCommand to perform SQL operations from C#, just modify the SqlCommand.timeout attribute.

As indicated in the link this refers to "the time in seconds to wait for the command to execute, the default is 30 seconds", which in the example explained above (20 million registers and increasing...) just doesn't cut it.

---

There is a max size for SQL Server Primary Keys

This may be a non-brainer if thought through, as it's a well known best practice to keep primary keys and database indexing as simple as possible.

But it is worth mentioning as a curiosity as... oh well, it happens.

---

SQLType smalldatetime and C# DateTime are not friends?

To be honest I'm not really sure on this one yet, but somehow it has happened. An explanation is still pending, and even though this is not really a large database issue, I'll mention it in case it happens.

It's a common scenario, there is a table with some date stored as a smalldate, you retrieve the smalldate value from the database and store it in a C# DateTime variable. Maybe you later use the DateTime variable for another query... and BAM!

Error: smalldatetime time type can not be transferred to string

Curious as it is, this error appeared in queries created by appending the parameters to create the string. If this explanation is not clear, instead of using sqlCommand, picture:

DateTime randomDate = "22/10/2012 00:00:00"
String query = "SELECT * FROM TABLEA WHERE DATE = '" + randomDate + "';";

For whatever reason, suddenly the date you just picked up from the database is no longer a valid value for the same database. And no standard "google results" solution solves the issue.

The most common suggestion was to use either a SQL CONVERT(...) or CAST(...), but this ended up with the same error.

A working solution is to change your query implementation to sqlCommand, however this may prove rather tedious if the application is not constructing the queries in this format already.

The solution I found to be both working and simple was to create a method to perform the DateTime to String conversion to a SQL date recognized format. The method is not required, however using a single method through which perform the formatting ensures consistency.

public String dateTimeToString( DateTime date) {
  return String.Format("{0:M/d/yyyy HH:mm:ss}", date);
}

DateTime randomDate = "22/10/2012 00:00:00"
String query = "SELECT * FROM TABLEA WHERE DATE = '" + dateTimeToString(randomDate) + "';";

That's all I wanted to share with you for today.

Entry dismissed.