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
cursors.
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
DECIMAL(5,2)) AS VARCHAR) END FROM Product ORDER BY ProductName
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
|
|
