Clustered Indexes Revisited
In my last post I talked about using a clustered index on some column other than the primary key. There are cases where this makes sense and it can have positive impact on performance. Recently however, CF Webtools own Jason Troy pointed out a consequence of altering your clustered index. You may recall that a "clustered" index really means that the actual data in the table will stored in the sort order specified by the index. Consider this example of a table called "emailer":
Read MoreClustered Indexes Mia Culpa
I have for years espoused the benefits of clustered indexes on MS SQL. Unlike a regular index a "clustered" index represents the actual sort order of the table. It is, therefore, the fastest available type of index. It is my view that some thought should be given to which columns are added to the clustered index. Please note, any indexing plan should include performance metrics coupled with experience. Please don't think I am recommending wholesale changes to any given schema. Having said that, a misunderstanding or misuse of indexing is the one of the most common cause of performance related problems. Now back to our discussion of clustered indexing.
Read MoreSSIS and DTS - Each Has Uses
You probably know that Microsoft replaced the venerable DTS with something called "SSIS" - which I gather stands for "SQL Server Integration Services". SSIS is immensely powerful and comes with a full featured scripting language and development environment that uses Visual Studio. Practically any kind of data migration and transformation is possible with SSIS. Unfortunately SSIS is also dizzyingly more complicated than the tried and true "Data Transformatin Services" (DTS). In SSIS I have trouble simply finding the list of tables and columns let alone doing transformations. For simple, one time migration tasks it is like using a 5 horsepower tiller to plow up your house plants.
Recently I was moving large datasets from an MS SQL 2000 (32bit) server to an MS SQL 2005 (64 bit) server and discovered that the SSIS package was importing dates incorrectly. It was somehow transforming them into completely different dates (probably due to a format difference or a difference in the way dates are stored). In addition the SSIS wizard did not automatically check the box for "enable identity insert". You might recall that DTS by default checks this flag for any table using the Identity feature. If you create your tables with the Identity property set ahead of time the DTS import will automatically work correctly without the need to edit the import properties of each table. In SSIS however, I have to go into the properties of each table in the wizard and specifically check the "enable identity insert" checkbox.
My brute force solution to these irritaing issues with SSIS is simple. Instead of "importing" using SSIS I "export" using DTS. This is my rule of thumb (at least until I can get SSIS to sing a new tune) - If you are doing straight forward migrations from SQL 2000 to SQL 2005 I recommend that you stick with DTS and keep SSIS for more complex integration needs. If you are interested in integrating the DTS wizard directly into the Server Management Studio, read on:
Read MoreThe Dreaded Mismatched Column or Data Type Error Revisited
This annoying error that occurs when using JDBC and MS SQL has been around for a while. The JDBC system in Coldfusion caches information about the tables you access. If you change a table (add a column for example) the column ids get out of whack and they no longer match the correct column. For Example, the first time you run a query you might get back the following:
Creating Views With CF Query: DDL Follies
Most queries in your Coldfusion code do one of four things - Select, Insert, Update or Delete. Maybe you did not know that, given the proper permissions, you can do just about anything that can be done on the DB server from within a query. You can backup and restore, drop users, even execute shell commands. That's why you should never create a datasource using the SA user. Instead you should define what you want a datasource to do and create a user for that purpose. Still, sometimes it is useful to be able to do other things using Coldfusion and Cfquery.
For example, I have a generic table with rows that look like "col1, col2" that holds form data. In this particular application the customer creates custom forms to collect data from specific clients. All the forms look different. One might have fullname, address, city, Postal code, and the next one might see first name, last name zip. When the data is submitted it is put in col1, col2, col3. But he has a reporting tool that allows him to query tables from the database and run reports for his customer. What can we do to make it easier for him to report? Surely "select col1, col2" isn't going to do it. The answer is to use T-SQL Data Definition Language (DDL) to create a view for each customer.
Read MoreUsing Rowcount in T-SQL
Here's a tip for limiting the number of rows returned from a query. Now I know you think "that's easy - just use TOP." Ah... but what if TOP is not an option? Consider this example. You have a reporting stored procedure that you want to use to return multiple results sets to your Coldfusion Page. Easy - right?
Read MoreHandling Variable Form Data in a Stored Procedure
Lots of projects have a requirement that interaction with a database must be done using stored procedures only. Stored procedures are generally quite easy to write, but there are some things that are slightly more difficult that using a straight CFQUERY. For example, perhaps you have seen code that handles a search form. You might see a query that looks something like this:
SELECT * FROM USERS
WHERE userID IS NOT NULL
<cfif NOT isEmpty(form.username)>
AND UserName = '#form.username#'
</cfif>
<cfif NOT isEmpty(form.address)>
AND Address = '#form.address#'
</cfif>
</cfquery>
DTS' Other Uses
One of the nicest things about MS SQL is DTS. If you are a reader who has a visceral reaction to anything nice being said about Microsoft you should grab a paper bag so that you don't hyperventilate as we go forward. Shallow breaths... shallow breaths.... ok - ready? Here we go. I suppose that most developers are exposed to DTS (Data Transformation Services) as an import-export mechanism for Microsoft SQL Server. The most common use is during deployment, rehosting or setting up a development environment. There are a host of other things for which you can use DTS that perhaps you hadn't thought of. For example:
- Moving Data from one DB platform or format to another - For example, with 2 ODBC connections to 2 Oracle servers you could import and export data from one Oracle server to another. I know that those of you with experience in Oracle will gasp with horror at this though - but migrating data around in Oracle is unnecessarily difficult (as is just about everything else about Oracle). You could transfer data from a DBase file to an access file. You could move information from a proprietary platform running on Cobol (as long as you had a driver) into a flat file or into MSSQL. It's a very nifty mechanism with all sorts of possibilities.
- File Drop Import - We use DTS to "pickup" a file who's name may not be consistent. It is even possible to FTP a file for import.
- Data Column Validation - With a minimal amount of scripting you can check values prior to importing them. So, for example, you can verify if a field is a number or is parsable into a date, or is populated or not.
- Complex SQL Tasks - You can add SQL task and even COM tasks to a package extending it well beyond just transferring data.
For example, we have a task that imports stock data. It is set to run every weekday. On days that are holidays it would run and sometimes create problems with the Bid and Ask. We needed a way to check and see if it was a holiday. If it was a holiday we wanted to terminate the process. Here is what our DTS Guru came up with. Using the package designer, at the beginning of the package add an ActiveX task that looks like this (this is the generic version).
Dim cn
Dim rs
Dim Flag
Set cn = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")
cn.ActiveConnection = "Provider=sqloledb;Data Source=database;Initial Catalog=database;User Id=username;Password=password;"
cn.CommandType = 1 'adCmdText
cn.ActiveConnection.CursorLocation = 3 'adUseClient
cn.CommandTimeout = 60 'set to 1 min
cn.CommandText = "select * from [table]"
Set rs = cn.Execute()
If rs.RecordCount > 0 Then
Flag = "F"
Else
Flag = "S"
End If
Set rs = Nothing
Set cn = Nothing
'msgbox "Result=" & Flag
If Flag = "S" Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
Sites to Watch
Aggregators
RSS
