Nikolay Pavlov

One thing you are actually not supposed to do is to format your data in the database.  Databases are to store and manage data, and presentation is for the UI layer.

Life has its funny ways to make you do things which you should not under normal cirumstances, things which you have sworn by yourself not to.  I came to a situation where I needed to format a money number following “#,.####” within the body of a T-SQL stored  procedure.  I simply had to do it there.  The requirement came in a very late moment of the testing phase, when moving this part out was definitely not feasible, considering the approaching deadline.

T-SQL has some limited functions to format data into string.  In reference to numbers - float and money, you can use CONVERT.  For float, there is no sensible option for ‘#,.####‘.  For money, CONVERT gives three options:

As you can see, ‘#,.####’ is a mix of 1 and 2.

Poor man is devil, Bulgarians say.  What I did was to get the string, result of CONVERT with style 1, and concatenate it with the last two characters of the string, result of CONVERT with style 2:

There is a bug in the above code, which produces wrong result in case the last two digits are greater than 49.  The correct solution would be:

Here is another approach to shrink an extensively large MS SQL Server log file:

Thanks to  Stilgar for helping me out with this one.

In Microsoft SQL Server sometimes you can observe the LOG file growing uncontrollably and unexpectedly to a size times the size of the DATA file.  I’ve witnessed  a database with data file of 200 MB and a log file of 4 GB.  Not only harddisk space is wasted, but the backup file becomes too inconvenient to drag around even heavily compressed.

Why does the LOG file grow that much is a different story, which I am not prepared to tell now.  The question is how to make the LOG file small again within the “normal” boundaries.  Using the “Shrink database” command does no good.  Backup and restore of the database gives you the same old large log file.

The only solution I know so far involves the following steps:

1. Create a new, empty database.
2. Script all the db structure, and use it to create an identical copy of your old one into the new database you just created.
3. Use bulk copy to transfer all data from the original database to the new one.

Voila, the LOG file is back to normal.

Note that you might need to disable certain triggers and foreign keys before the copy operations - that depends completely on your database.

An excerpt from MySQL Reference Manual:

Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type (’0000-00-00 00:00:00′ or ‘0000-00-00′).

What the fuck.  Really, come on.  The purpose of a database is to store valid and integral data.  Again.  Valid.  And.  Integral.  Data.  It is valid, integral and meaningful that counts.  Not just any rubbish.  If I want rubbish, I can just read back random bytes from the harddisk.  If someone tries to write crap into a database, which the database cannot handle, the database must refuse the operation, and give error. 

Really, I wonder what the hell designers of MySQL were thinking when they implemented this specific feature.  Pardon me, the word “feature” is not appropriate.  The correct word would be “disaster”.  It is a disaster indeed, because it silently causes data loss.  One might argue that it is developers’ responsibility to validate their data, and make sure they provide the database with valid input.  While this is definitely true, it is no excuse for the database to accept graciously any garbage, and on top of that convert it to something which has nothing to do with what it is supposed to be.  That is a major fail in design, and major fail to achieve a primary goal.

The behavior above is true for MySQL versions from 4.1 to 6.0.

This little story only proves that MySQL is only good as a backend for small and simple websites.  If you mean serious business, please reconsider.  There are a bunch of free databases out there, all of them much, much better and more mature.  Some of them even open source.

And yes, I know there is a general setting in MySQL 5.02 and greater to make it give errors.  And yes, I don’t think it makes the fail lesser.

A friend of mine asked this question:

I have two tables: table T1 with primary key field ID, and table T2 with field ID which is a foreign key to ID from T1, and field VAL.  How do I select all records from T1, for which there are records from T2 with values X and Y? 

For example, considering the following data, I want all records from T1, where exist records records from T2 with values 1 and 2.

T1 has records
1
2
3

T2 has records

1

1

1

2

2

1

3

1

3

2

Desired output is records 1 and 3 from table 1.

The question is actually interesing only if we want to select records from table T1 for which there are an arbitrary number of records with respective values in T2.

There are a few ways to implement this.  Here is my choice number one for an arbitrary number of values:

If we are considering a few number of values - like four or five, I would suggest an alternative, which might prove even more efficient:

Providing we have a foreign key on T2.ID to T1.ID, the JOIN-approach can be really efficient.