Performance gain with Subquery instead of group
May 12, 2008
I've often used SubQueries in SQL for filtering data, but I've not tried using one in a select statement. I recently had a query that was running slowly due to the number of fields referenced in the group by clause. By changing it to use a SubQuery in the select it has actually reduced the execution time. Read more »
ClearCode Standards in practice
April 26, 2008
I've just watched Ben Nadel's thought provoking presentation on Code Standards. I've always been a fan of standards, Ben takes this further and talks about how the eye and brain process the code on the screen. As a result I thought I'd try comparing … Read more »
Adding a Not Null column to a table with data
April 14, 2008
If you want to add a column to your database table that already has data in it, then you would normally have to make the new field nullable. -- add new column ALTER TABLE dbo.Products ADD product_grams int NULL GO -- set default value U… Read more »
Import from MS SQL 2005 to 2000
April 08, 2008
Believe it or not you can not restore an MS SQL 2005 Server backup to an MS SQL 2000 Server. This is a major pain and I've spent all morning trying to figure out how to get data on a live 2005 box to my local 2000 box. To be honest the easiest soluti… Read more »
Getting MS SQL Database size using ColdFusion
March 25, 2008
Someone asked how to retrieve the size of all MS SQL Server databases using Coldfusion. There is a handy built in stored procedure called sp_spaceused which will do the job for you. Read more »
SQL Server Express 2005 and ColdFusion 8
March 19, 2008
ColdFusion does not connect to the default install of SQL Server Express 2005. After much swearing I discovered that you need to enable TCP/IP (which is disabled by default). To enable it go to Start -> Programs -> Microsoft SQL Server 2005 -&… Read more »
Using CharIndex to get a SubString
March 19, 2008
If you need to extract part of a string based on the index of a character in SQL, then you can use the CharIndex function and the Substring function. This works the same as the Find & Mid functions in ColdFusion and IndexOf in JavaScript. For ex… Read more »
Get tables in a database
March 17, 2008
To return all the tables in a database use this SQL statement SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Type = 'BASE TABLE' AND Table_Name <> 'dtproperties' Read more »
Sorting alpha and numeric data in the same field
February 18, 2008
The problem I had was the client was entering some room names as numbers and others as a number with a character at the end. When MS SQL server sorts the column it does it by the field data type (in this case nvarchar), which meant you would get this… Read more »
Finding table size in a database
August 20, 2007
I found this bit of code on http://www.databasejournal.com/img/BigTables.sql for finding the size a tables in a database on MS SQL Server. Really handy for finding out which tables are using up all the disk space! /********************************… Read more »