Category: SQL

Handling Display Weight/Priority Using SQL

Posted on Jun 16, 2006

I was presented with an issue the other day while I was building our updated promo maintenance tool that handles internal banner ads. The issue was that the frequency of the display was weighted (I called it priority) like most banner ads are. In our case we had determined to limit the priority to 3 options, but the solution I will provide can work for more (but it has to be a limited set and likely small). Anyway, I was going to use the random records in T-SQL solution I previously discussed to send me back a single random record, therefore I needed to handle the priority within my query so that records with a higher priority were more likely to be randomly chosen. The solution I came up with feels a bit contrived, and I would love to hear a better one, but it works and is really easy to do. Nonetheless, I present this solution to both offer it to someone who may find it useful, but also to solicit feedback from someone who might have a better solution (kinda like Ray's Friday Puzzlers).

 Continue Reading →

Free SQL Code Completion Tool (Limited Time)

Posted on Jun 01, 2006

"Dave the Disruptor" mentioned this on CF-Talk (sorry Dave - don't know your real name), but Red Gate Software is offering their SQL Prompt software for free until September 1st 2006 with "no time-bombs, no restrictions". SQL Prompt offers code completion and insight directly within SQL Query Analyzer. Here is there description:

SQL Prompt provides Intellisense® style auto-completion for Microsoft SQL Server editors. It will tell you the exact formatting you should use for your SQL commands, while you are writing them and will therefore help you write fast, perfectly formatted SQL statements.

I downloaded and installed it, and I have to say was nicely impressed on my initial tests. Once you establish SQL Prompt's database connection credentials via a prompt, the code completion and insight works rather seamlessly. Download the software by going here. Thanks for the tip Dave!

 Continue Reading →

Random Records in T-SQL

Posted on Jan 30, 2006

Recently, I needed to write a simple query that would randomly grab a single product record from the database. In the past, I have used QueryRandomRows() to do this, but this can be very inefficient if you want to pull a random record from a large data set. I happened upon a solution for MS SQL that is extremely simple (this is an oversimplified version of my query):

<cfquery name="qryProducts" datasource="#application.dsn#">    SELECT   TOP 1 id, productName    FROM   products    ORDER BY NEWID() </cfquery>

Obviously, you can alter how many rows you return by changing the TOP 1. Also note that using the RAND() function will not work even though it may seem more logical. This is because "repetitive calls of RAND() with the same seed value return the same results." This means that within a single query, RAND() will return the same result for every row.

I am sure I have seen this blogged elsewhere, but I cannot remember where exactly (so forgive me if this is not new).

 Continue Reading →

Create Your Own Code Generator

Posted on Dec 16, 2005

Lately there has been a lot of talk about tools like Reactor, Arf, ObjectBreeze and Transfer with regards to how they can help reduce repetitive coding tasks and and speed up development time. While, as you may already know, I am a huge fan of these tools, there may be times when you are not able to or may choose not to (for whatever reason) use these tools. This leaves tools like Roobios (and even SQL Surveyor though it is not free) that include some basic code generation to speed up your component development. However, these tools only offer a few basics, and may not write code in the manner you wish to have it. Well, as it turns out, it isn't all that complicated to build your own tool to generate components your way. I put together a basic example and will walk through how I built it (and share the code via the download).

 Continue Reading →

Queryparam Issue and Arf

Posted on Dec 02, 2005

I have been testing out Arf and it is a great tool. I plan to cover my experiences in more detail soon, but I ran into an issue that isn't specific to Arf but came up while using it having to do with cfqueryparam and prepared statements.

 Continue Reading →



My name is Brian Rinaldi and I am the Web Community Manager for Flash Platform at Adobe. I am a regular blogger, speaker and author. I also founded RIA Unleashed conference in Boston. The views expressed on this site are my own & not those of my employer.