Interview for Advanced SQL
      "Advanced SQL" interview with Selene Bainum
Michael Smith: This time we are talking with Selene Bainum about her CFUNITED-05 
talk "Advanced SQL". So why should a developer come to your session Selene ?

Selene Bainum: Most ColdFusion developers get to a point where their 
applications need to run more efficiently than they have before. The first thing 
anyone should try to improve is interactions with their database. By moving 
logic from ColdFusion to your database, such as SQL Server, you can greatly 
increase the efficiency and speed with which your applications run. This session 
will teach you some of the ins and outs of Transact-SQL, SQL Server's specific 
flavor or SQL, which will allow you to create complex queries and stored 
procedures that include concepts such as variables, conditional logic, loops and 

MS: Is T-SQL hard to learn?

SB: Not really. T-SQL is just a superset of SQL, so if you are familiar with 
writing standard queries (SELECT, INSERT, UPDATE, etc...) you are well on your 
way. T-SQL does have a lot of functions, but since they are based on basic 
functions, many of them are very similar to ColdFusion functions. There is also 
very good help in Microsoft's SQL Query Analyzer tool. I use the T-SQL Reference 
all the time. Other items, such as cursors, are more difficult to learn, but 
should not be beyond a mid-level developer's grasp.

MS: Are there any other advantages to T-SQL over SQL?

SB: The main advantage of using T-SQL over standard, cross-database compatible 
SQL is that you can utilize all the functions and processes it contains, thus 
pushing more of your processing to your database. You can also create stored 
procedures, functions and views that run faster than standard SQL in a CFQUERY 
tag because they are precompiled. You can also restrict viewing and editing of 
these objects so that not everyone on your development team can see them. You 
can allow them to use them, but not see their content, which helps hide and 
secure your business logic.

MS: Are there any disadvantages to using T-SQL?

SB: The main disadvantage of using T-SQL, as with any proprietary database 
language - is that the code will not port to another database without a lot of 
rewrites. If your company migrates from SQL Server to Oracle, for instance, any 
query that does not use standard SQL will have to be rewritten.

MS: Can you show us a simple example of some T-SQL code and briefly explain what 
it does?

SB:  Often in ColdFusion you may not be able to manipulate query data when it is 
displayed, which is especially true if you are using ColdFusion components and 
your data is being accessed by another system.  In these instances it is very 
useful to have SQL format the data for you.  One of the most useful statements 
in a query is CASE.  Like in most languages, CASE is used to perform conditional 
logic. Consider the following query:

SELECT ProductID, ProductName, ProductPrice, DisplayPrice = CASE WHEN 
ProductPrice IS NULL THEN 'No Cost' ELSE '$' + CAST(CAST(ProductPrice AS 

This query will return four columns: ProductID, ProductName, ProductPrice and 
DisplayPrice.  The first three columns will return data exactly as it is in the 
database, but DisplayPrice will be formatted based on the value of ProductPrice. 
When ProductPrice is null, a string literal of 'No Cost' will be returned. 
Otherwise, the existing ProductPrice will be cast to a decimal format (it is 
stored as FLOAT in the database) and then will be cast to a VARCHAR so that it 
can be concatinated with the dollar sign.

MS: Wow that is pretty niffty logic inside a SQL statement - Does it run much 
faster tahn doing the same logic in CF?

SB: The speed difference can depened on a lot of different factors, including 
hardware.  If you are running ColdFusion on a multi-processor server with a gig 
of memory and have SQL Server on a P3 desktop, you won't notice much different. 
The big speed benefit you get is when you perform complex processing within a 
stored procedure, such as looping over recordsets as opposed to having one 
CFQUERY tag for each loop iteration.  One benefit of the code above is data 
sorting.  Because you are returning the formatted column from your database, you 
can sort by it.  If you do the data formatting in ColdFusion, you may not be 
able to recreate the sort results.

MS: Hmm, I am going to have to check out your session at CFUNITED
© Copyright TeraTech Inc 2004. All rights Reserved.