This is a simple trick when you need to pull a query and order the records randomly.
This code gets one random product from my database.
When designing SQL-server databases I like to use fields starting with an "N".
The "N" stands for NATIONAL, which overhere in Europe we have a lot of, umlauts, trema's, accents... you name it, we love it!
We use a lot of unicode data, things that VARCHAR has difficulties handling, but that's not the main reason I like to use the "N" fields.
The reason itself is that fields without the "N" are padded with spaces to meet their maximum when you output them. This causes a lot of problems when trying to create relations or output them to fields.
For example, an NVARCHAR at length 20 with content "BH 90210" outputs this way:
where VARCHAR at length 20
Where the value is padded with 12 more spaces to be used with VARCHAR...
Although we in Europe are aware of this when designing SQL databases, I still get a lot of SQL-databases that use the VARCHAR, TEXT or CHAR fields which are a nightmare to use in CF, because of the way you creating lookups and relations sometimes. I have "TRIM()" on speed dial here to create good content.
Another SQL trick I use quite a lot, which does solve most problems is:
It's good design to not use the "N", since an NCHAR takes twice the byte-space of a CHAR, but considering the above, please use "N" when creating SQL databases.
It's my first time developing an application where the client needs to add and delete table-fields. It's a database-table that stores a few hundred fields, with different types of values, integers, floats and money.
I never needed this until today, so I though I just share it with you. It's really simple, but I like to use my blog as a personal documentation as well (lazy me...)
Although I prevent using binary data inside database tables, I know have a project where a company logo for each account in the database is stored inside the database as a binary object.
I use this code to get it out. Read on...
Whenever you run into a query where you have to do an average - AVG()-, or some other aggregation based on it's value it might come in handy to have a value there, even if the field is empty.
For example, the SQL-average of...
Item1 = 2.50 euro
Item2 = 5.00 euro
Item3 = empty (null)
Will be 3.75 euro ((2.5+5)/2) since empty fields won't be taken into account. In some cases I want an empty value to be considered in the average, in this case it may be 0.00 euro, so the outcome will be 2.50 euro ((2.5+5+0)/3).
The SQL statement IsNull comes in quite handy in this context. Read on...
