Blog of Shaun McCran - Architecting robust, elegant technical and business solutions - SQL

How to find out the size of data in MS SQL databases

Sun, 03 Oct 2010 22:15:00 -0000

If you are running Microsoft SQL server you may already be familiar with how the database file system can enlarge itself to astronomical proportions. There are 'Auto shrink' and 'Auto grow' options, but these are really not recommended for production environments. So how do you go about determining how much space is being used by actual data IE records, and how much is being used by log files etc? This article will show you how to run a query that will return the size of all the tables in your database, and highlight a TSQL command that will clean up the file storage of a database. [More]

Returning values from mySQL in a select query as Yes/No rather than 1/0

Fri, 05 Mar 2010 11:48:00 -0000

Whilst writing a ColdFusion based query to create a JSON response I thought I'd look up returning data values as a "Yes/No" string rather than "1/0". Then I wouldn't have to transform it in any way to use in the JSON build script. The mySQL version allows you to do this by evaluating the value, and matching a string if the condition is met, like this:


SQL version: 
SELECT intid,varuserfname,varusersname, IF(intactive<>0, "Yes", "No")
FROM 	table.users

This does not work in ColdFusion at all. An error is thrown: After a little tweaking it seems that if you alias the field it does work. In the example code below I've simply aliased the field with its own name. I'm not exactly clear why, as the error message above isn't all that helpful.


CF version:
SELECT intid,varuserfname,varusersname, IF(intactive<>0, 'Yes', 'No') as intactive
FROM 	table.users

Emulating the mySql limit function in MS SQL

Mon, 15 Feb 2010 16:37:00 -0000

There are pro's and con's to both mySql and MS SQL, one of the pro's of mySql is the limit function. This allows you to set a starting record number, and select N number of records from there. But how can we do that in MS SQL? This is usually used for pagination functions, IE SELECT the next N records from the current count. Firstly declare two variables, a "rows per page" and a start record.






DECLARE @rowsperpage INT

            DECLARE @start INT

            SET @start = #startpos#
            SET @rowsperpage = #rows#

Next we need to write the query we want, but wrap it in a parent select. Remember at this point to do all your conditional processing on the query inside the parenthesis.


SELECT * FROM

            (SELECT row_number() OVER (ORDER BY [intId]) AS rownum, *
                               
            FROM   [table]
                    Where [table].intId = ) AS tempQuery

Next we can apply a where clause that filters down the results based on the two variables declared at the top of the script. This means we will only get back the required number of records, starting at our defined record number.



Where tempQuery.rownum BETWEEN (@start) AND (@start + @rowsperpage) 
Order by rownum

The whole script looks like this:






DECLARE @rowsperpage INT

            DECLARE @start INT

            SET @start = #startpos#
            SET @rowsperpage = #rows#

SELECT * FROM

            (SELECT row_number() OVER (ORDER BY [intId]) AS rownum, *
                               
            FROM   [table]
                    Where [table].intId = ) AS tempQuery

Where tempQuery.rownum BETWEEN (@start) AND (@start + @rowsperpage) 
Order by rownum

I am selecting *, but I recommend that you actually list your fields here, I tweaked it for this entry. Not quite as nice as the mySql version tho is it:



Select *
From table
Limit 0,100

Quotes being changed to question marks using cfsavecontent and MySql

Mon, 12 Oct 2009 12:32:00 -0000

One of the tables in an online application stores entire html templates in a blob field, this data is pulled out and used to build emails, replacing specific keywords. I recently needed to update some of this data, but there is not an administration system for it, so a manual SQL script would have to do. The problem I encountered was that creating a html template as a variable using cfsavecontent and inserting it into MySql was causing all the ' to appear as ?. I read a blog entry based on Coldfusion MX and setting the character encoding in your coldfusion administrator, for each datasource. Or you could do it in the template, like this:

This didn't seem to work at all, in fact I found that it would error whilst trying to insert the cfsavecontent variable using cfqueryparam. There seemed to be some sort of encoding issue between the two tags.



Html template code
  etc 


INSERT INTO table (fields)
VALUES ('#htmlContent#');

This didn't work either with Cfqueryparam, or as a non param input. It turns out that you simply need to escape the characters (double them, or use character representations). IE don't use a single quote, use two . In this way the MySql encoding treats the character as a character, rather than a potential command. I still think that this is something that can be fixed by setting the encoding type, but in this case there was a far simpler solution.

Example of inserting a Struct() into a database using keys

Wed, 26 Aug 2009 16:42:00 -0000

A while ago a colleague and I were working on a timesheet application in Flex. The idea was that you could commit a custom timebar object, generated in flex, and it would update the dataset in the back end using the ColdFusion flex gateway. I came across the code recently, and decided to tidy it up a bit, and make the query dynamic, based on the Struct contents. The obvious limitation to this is that your Struct and your database schema have to match exactly. I won't go into the Flex application here, but I've emulated its input arguments here with a pre-populated structure.



      timesheetTask = StructNew();
      StructInsert(timesheetTask, "employeeid", '36');
      StructInsert(timesheetTask, "timesheetDT", '0');
      StructInsert(timesheetTask, "projectid", '6');
      StructInsert(timesheetTask, "weekid", '25');
      StructInsert(timesheetTask, "taskid", '39');
      StructInsert(timesheetTask, "hours", '8');
      StructInsert(timesheetTask, "comment", 'Comments for this task live here');
      StructInsert(timesheetTask, "szStatus", '1');
      StructInsert(timesheetTask, "iFirstLineApproval", '23');
      StructInsert(timesheetTask, "iSecondLineApproval", '34');
      StructInsert(timesheetTask, "iCurrentApprover", '');
      StructInsert(timesheetTask, "szRejectReason", '');
      StructInsert(timesheetTask, "szDescription", '');
 
      updateTimesheet = createObject("component", "timesheet");
      updateTimesheet.updateTask(timesheetTask);

Notice that this code also calls the CFC object at the end. The data itself isn't massively important, it's a time object for recording tasks. Next we have the function, which accepts a Struct() argument called 'taskStruct'. I then loop through the structure, and populate a SQL query using the keys from a collection. The only logic is a check to see if it is the last structure element, as this controls the ',' placement.


      
            
      

            
      
 
      
            INSERT INTO [dbo].[timesheet]
           (
                        [#key#]
                        
                        ,
                  )
    
       
      
      VALUES(
                  '#arguments.taskStruct[key]#'
               
               ,
               )

That will insert your Struct into a database, in small and tidy manner. It was somewhere around here that we started using cfproperty tags, and creating strongly typed objects for Flex.

AIR Phone Book application - Part 1 (CFC and GUI)

Fri, 03 Apr 2009 14:46:00 -0000

I'm always asking what peoples phone numbers are in the office, we currently don't have any internal communications (like an intranet) so I thought I'd create a handy phone book application in AIR. With FLEX of AIR applications I often wireframe them up with the data model in mind first. If you know what data you are going to display, and the format and delivery mechanism of that data, it can often have a large impact on the design and layout of your application. In this instance I was just returning a simple query object of users and their phone numbers and a thumbnail image.

The CFC

My preferred server language is ColdFusion, so my service is a CFC object.




	
	
		
			select 	id AS ID, 
					name AS Name,
					number AS No,
					image As Image
			from phonebook 
			Order by name

In my example I'm using an MS SQL database, so I have included the creation script here:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[phonebook](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](20) NULL DEFAULT (NULL),
	[number] [int] NULL DEFAULT (NULL),
	[image] [varchar](55) NULL DEFAULT (NULL)
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Now that we know what the data will look like we can build the GUI front end. My display layer is going to be a canvas, with another canvas inside it, to create a bordered effect. Then I have a DataGrid, with a click event that will call an AS function. This will control the displaying of an image that corresponds to the user being clicked. Its always nice to see who you want to call!

My 'userImage'has a showEffect attribute, that uses an image fadeIn method. It fades in the first image called, but not any others, I've had a play around with this, and I can't get it to fade in subsequent images, so if anyone has any ideas let me know! Lastly I have added some chrome controls, as I will be removing the standard chrome, and building my own. Now, on to the functions.

SQL Stored Procedures, UPDATE Template script

Tue, 17 Mar 2009 12:46:00 -0000

This article deals with creating a SQL stored procedure for Updating a record. In each of these stored procedure templates I am declaring a variety of documentation parameters in the header. I've found these handy in the past when you are working in a team environment, or when you go back to a procedure at a later date. Its much easier to read a simple description in the header, than trawl through the SQL code looking for what it is doing. So, this declares the procedure name, any parameters and return codes, and also details what it does, and who made it. In a modified version of this I also hold the SVN revision number here.



/********************************************************************************/
/*		              Company Name                   							*/
/********************************************************************************/
/*      Procedure Name  : dbo.ssp_stored_procname                               */
/*      Parameters      :                                                       */
/*      Return Codes    :                                                       */
/*                                                                              */
/*      Description     : Description of what it does, params etc               */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*      Author          : Authorname                                            */
/*      Date written    : Date                                                  */
/*      History         : version number                                        */
/*                                                                              */
/********************************************************************************/

The next block of code performs a select on the sysObjects table (part of the Master database). It is checking for the existence of itself. If it finds itself, it will drop the procedure. Note that throughout all of these scripts we are telling the user at each stage what is going on, by printing useful english output back to the screen.



IF EXISTS (SELECT 1 FROM sysobjects where id = object_id('dbo.ssp_stored_procname') and sysstat & 0xf = 4)
BEGIN
	PRINT 'Dropping old version of dbo.ssp_stored_procname'
	DROP PROCEDURE dbo.ssp_stored_procname
END
GO

By now we have identified whether or not the procedure previously existed, and if it did, we have dropped it, so we know that we are all good to go. So to create our Update procedure, we print out a message to the user, then using the "CREATE PROCEDURE" command we create our procedure. At this point you substitute the "@field" value with your field name, and the [datatype] and (datasize) with the correct values. Just list your fields one after another, seperating with a comma. As this is creating an Update stored procedure I will list any of the values to update in the query here.



PRINT 'Creating procedure dbo.ssp_stored_procname - START'
GO
 
CREATE PROCEDURE dbo.ssp_stored_procname
	(@field 		[datatype](datasize),
	 @field 		[datatype],
	 @field		[datatype](datasize),
	 @field		[datatype](datasize),
	 @field		[datatype],
	 @field		[datatype])

After that we create the SQL code, as per usual. We have an Update statement, using the variables declared above in the SQL variable declaration (@var). Just write out your update like you normally would here. Then we check for any errors, and return a success message if it all worked ok!


AS UPDATE tablename 
SET  [field]	 = @field, 
     [field]	 = @field,
     [field]	 = @field,
     [field]	 = @field,
     [field]	 = @field
WHERE 
	( [field] = @conditions)
RETURN @@ERROR
GO
PRINT 'Creating procedure dbo.ssp_stored_procname - END'
GO

By using a script like this I've found that its really simple to have a repeatable standard process that is easy to implement across a team of developers, ensuring you get the same results, no matter who writes the query. It is also very useful if you have a seperate implementation team, as these scripts are re-runnable, they clear up after themselves. Download the full template here.

SQL Stored Procedures, DELETE Template script

Wed, 11 Mar 2009 15:59:00 -0000

This article deals with creating a SQL stored procedure for deleting a record. In each of these stored procedure templates I am declaring a variety of documentation parameters in the header. I've found these handy in the past when you are working in a team environment, or when you go back to a procedure at a later date. Its much easier to read a simple description in the header, than trawl through the SQL code looking for what it is doing. So, this declares the procedure name, any parameters and return codes, and also details what it does, and who made it. In a modified version of this I also hold the SVN revision number here.



/********************************************************************************/
/*		              Company Name                   							*/
/********************************************************************************/
/*      Procedure Name  : dbo.ssp_stored_procname                               */
/*      Parameters      :                                                       */
/*      Return Codes    :                                                       */
/*                                                                              */
/*      Description     : Description of what it does, params etc               */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*      Author          : Authorname                                            */
/*      Date written    : Date                                                  */
/*      History         : version number                                        */
/*                                                                              */
/********************************************************************************/



IF EXISTS (SELECT 1 FROM sysobjects where id = object_id('dbo.ssp_stored_procname') and sysstat & 0xf = 4)
BEGIN
	PRINT 'Dropping old version of dbo.ssp_stored_procname'
	DROP PROCEDURE dbo.ssp_stored_procname
END
GO

By now we have identified wether or not the procedure previously existed, and if it did, we have dropped it, so we know that we are all good to go. So to create our Insert procedure, we print out a message to the user, then using the "CREATE PROCEDURE" command we create our procedure. At this point you substitute the "@field" value with your field name, and the [datatype] and (datasize) with the correct values. Just list your fields one after another, seperating with a comma. As this is creating a delete stored procedure I will only be inserting one variable into the query.



PRINT 'Creating procedure dbo.ssp_stored_procname - START'
GO
 
CREATE PROCEDURE dbo.ssp_stored_procname
	(@field			[datatype](datasize))

After that we create the SQL code, as per usual. We have a Delete statement, using the variable declared above in the SQL variable declaration (@var). Just write out your delete like you normally would here. Then we check for any errors, and return a success message if it all worked ok!


DELETE FROM table
WHERE (conditions = @var)
RETURN @@ERROR
GO

PRINT 'Creating procedure dbo.ssp_stored_procname - END'
GO

SQL Stored Procedures, SELECT Template script

Tue, 10 Mar 2009 14:29:00 -0000

This article deals with creating a SQL stored procedure for selecting a record. In each of these stored procedure templates I am declaring a variety of documentation parameters in the header. I've found these handy in the past when you are working in a team environment, or when you go back to a procedure at a later date. Its much easier to read a simple description in the header, than trawl through the SQL code looking for what it is doing. So, this declares the procedure name, any parameters and return codes, and also details what it does, and who made it. In a modified version of this I also hold the SVN revision number here.



/********************************************************************************/
/*		              Company Name                   							*/
/********************************************************************************/
/*      Procedure Name  : dbo.ssp_stored_procname                               */
/*      Parameters      :                                                       */
/*      Return Codes    :                                                       */
/*                                                                              */
/*      Description     : Description of what it does, params etc               */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*      Author          : Authorname                                            */
/*      Date written    : Date                                                  */
/*      History         : version number                                        */
/*                                                                              */
/********************************************************************************/



IF EXISTS (SELECT 1 FROM sysobjects where id = object_id('dbo.ssp_stored_procname') and sysstat & 0xf = 4)
BEGIN
	PRINT 'Dropping old version of dbo.ssp_stored_procname'
	DROP PROCEDURE dbo.ssp_stored_procname
END
GO

By now we have identified wether or not the procedure previously existed, and if it did, we have dropped it, so we know that we are all good to go. So to create our Insert procedure, we print out a message to the user, then using the "CREATE PROCEDURE" command we create our procedure. At this point you substitute the "@field" value with your field name, and the [datatype] and (datasize) with the correct values. As an example of this the first line in this proc will create a field named 'varName' with a data type of varchar with a character limit of 100. Just list your fields one after another, seperating with a comma.



PRINT 'Creating procedure dbo.ssp_stored_procname - START'
GO
 
CREATE PROCEDURE dbo.ssp_stored_procname
	(varName		[varchar](100),
	 @field	 		[int],
	 @field			[datatype](datasize),
	 @field			[datatype],
	 @field			[datatype],
	 @field			[datatype])

After that we create the SQL code, as per usual. We have a Select statement, listing the fields, and the SQL variable declaration (@var). Then we check for any errors, and return a success message if it all worked ok!


Select Fields
from   Table
Where  (Conditions)
RETURN @@ERROR
GO

PRINT 'Creating procedure dbo.ssp_stored_procname - END'
GO

SQL Stored Procedures, INSERT Template script

Wed, 04 Mar 2009 11:08:00 -0000

This article deals with creating a SQL stored procedure for inserting a record. In each of these stored procedure templates I am declaring a variety of documentation parameters in the header. I've found these handy in the past when you are working in a team environment, or when you go back to a procedure at a later date. Its much easier to read a simple description in the header, than trawl through the SQL code looking for what it is doing. So, this declares the procedure name, any parameters and return codes, and also details what it does, and who made it. In a modified version of this I also hold the SVN revision number here.



/********************************************************************************/
/*		              Company Name                   							*/
/********************************************************************************/
/*      Procedure Name  : dbo.ssp_stored_procname                               */
/*      Parameters      :                                                       */
/*      Return Codes    :                                                       */
/*                                                                              */
/*      Description     : Description of what it does, params etc               */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*                                                                              */
/*      Author          : Authorname                                            */
/*      Date written    : Date                                                  */
/*      History         : version number                                        */
/*                                                                              */
/********************************************************************************/



IF EXISTS (SELECT 1 FROM sysobjects where id = object_id('dbo.ssp_stored_procname') and sysstat & 0xf = 4)
BEGIN
	PRINT 'Dropping old version of dbo.ssp_stored_procname'
	DROP PROCEDURE dbo.ssp_stored_procname
END
GO

By now we have identified wether or not the procedure previously existed, and if it did, we have dropped it, so we know that we are all good to go. So to create our Insert procedure, we print out a message to the user, then using the "CREATE PROCEDURE" command we create our procedure. At this point you substitute the "@field" value with your field name, and the [datatype] and (datasize) with the correct values. As an example of this the first line in this proc will create a field named 'varName' with a data type of varchar with a character limit of 100. Just list your fields one after another, seperating with a comma.



PRINT 'Creating procedure dbo.ssp_stored_procname - START'
GO
 
CREATE PROCEDURE dbo.ssp_stored_procname
	(varName		[varchar](100),
	 @field	 		[int],
	 @field			[datatype](datasize),
	 @field			[datatype],
	 @field			[datatype],
	 @field			[datatype])

After that we create the SQL code, as per usual. We have an Insert statement, listing the fields, and the SQL variable declaration (@var). Then we check for any errors, and return a success message if it all worked ok!



AS INSERT INTO table
	 ([field],
	  [field],
	  [field],
	  [field],
	  [field],
	  [field])
VALUES
	(@field,
	 @field,
	 @field,
	 @field,
	 @field,
	 @field)

RETURN @@ERROR
GO

PRINT 'Creating procedure dbo.ssp_stored_procname - END'
GO

MS SQL Stored procedure templates

Mon, 02 Mar 2009 12:10:00 -0000

Over the course of working with one employer, I headed up an investigation into our current server performance, and how would could potentially aim for at least a 25% to 30% increase on our current user thresholds before our capacity maxed out. After looking through a series of server logs, and data transactions it was easy to see that the server technology being used, Coldfusion, is not the most efficient data handler in the world. So after stripping back our application layer, taking a peek under the hood, and changing around 600 or so instances of data connectivity from inline SQL code, to Stored Procedures, we had more than exceeded our target gain. In fact under stress testing we had achieved a consistent increase of around 65% threshold. As a quick set of figures, the server loads had changed from CF, running at 75%-80% dropping to 20%-25%, and SQL server running at 6% increased to around 15%. Based on these figures, and architectural methodologies I've learnt since, I would always recommend stored procedures over inline SQL code. This has lead me to develop a standard set of SQL templates for developers to use. They are self checking, repeatable SQL templates that will action the desired changes, and check for any existing conditions up front. The idea is that a non technical resource could run them, and receive a useable English response. 1. Select statement
2. Update Statement
3. Insert statement
4. Delete statement

Differences between cfQueryParam "cf_sql_date" and "cf_sql_timestamp"

Thu, 26 Feb 2009 18:10:00 -0000

I was recently looking at a report generator, and came across a conflict in the mySql query dealing with the date objects. The report returns a set of records between two dates, using cfqueryparam, but there seemed to be a problem in running the query using certain dates. Running it from a browser provided differing results from running it directly in a sql interface. So where was the error? The original code was using the "cf_sql_date" type to pass the date time off to the sql engine. It appears that this data type is not transferred as a date time value, but as an incompatible numeric value, that is not comparable to the mySql datetime I am querying against.


AND table.ap_startdate >= 

AND table.ap_enddate <=

There are a wealth of articles online about passing date values into a variety of database technologies, but this Adobe post was very helpful. This article shows a handy table of cfsqltypes and how they map to different database management systems. Using something like sql profiler you can clearly see the difference between cf_sql_date and cf_sql_timestamp, where cf_sql_date is being truncated to just a date value (actually evaluates as a numeric value) and a full date time stamp that cf_sql_timestamp generates.


AND table.ap_startdate >= 

AND table.ap_enddate <=

Changing the code to the above example completely resolves this issue.

Mysql left and right functions

Wed, 04 Feb 2009 12:18:00 -0000

When you need to extract specific elements from a column, MySQL has a few functions that can help. I've always found it much easier to provide the data to your application layer in the corrct format in the first place, rather than excessive processing in GUI layer. Suppose I needed to check that column in the table always has a trailing '/' on the column data.


select 	id, 
	name, 
	RIGHT(url, 1) as slash
	from table
	where RIGHT(url, 1) != "/"

In the LEFT() function, using the column telephone along with the number of characters to extract, starting from the first character on the left in the column.

The RIGHT() function is similar, but it starts from the last character on the right, counting left to encapsulate the last seven characters.
In the SQL statement below, areaCode is reused to order the results set. To reformat the telephone number, it will be necessary to use the SUBSTRING() function. SELECT LEFT(telephone, 3) AS areaCode, RIGHT(telephone, 7) AS telNo FROM contacts ORDER BY areaCode; SELECT CONCAT('(', LEFT(telephone, 3), ') ', SUBSTRING(telephone, 4, 3), '-', MID(telephone, 7)) AS 'Telephone Number' FROM contacts ORDER BY LEFT(telephone, 3);

Coldfusion Server connectiong to SQL Server Express 2005

Mon, 02 Feb 2009 17:01:00 -0000

I've been using SQL Server Express for quite a while noa, and still prefer it to almost every other database application. I was rebuilding a development environment the other day, and discovered that I was using an ODBC "trusted connection" to connect to a SQL Server instance in Coldfusion Server. Why was this I thought? It turns out that I'd had some problems getting Coldfusion to connect SQL Server datasource connection, so I'd used a custom ODBC connection. Looking into the connection error and having a dig around I found out a few things its worth checking when installing SQL Server. 1. First thing is to enable TCP/IP connectivity, as its not on by default. Go to Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. Under SQL Server 2005 Network Configuration, click on Protocols for SQLEXPRESS, click on TCP/IP, and click enable. Then restart the service, which you can do from the Configuration Manager under the SQL Server 2005 Services area, or in windows services. 2. In my experience SQL Server always used to use port 1433 by default after installation. For some reason it hadn't used this port, it was using 1523. I was quite puzzled about this as I just used accepted the installation defaults. The port is a crucial setting when you're setting up your datasources for Coldfusion, otherwise CF wont even find the datasource, let alone attempt to connect. To check your port number, go to the SQL Server Configuration Manager, go to Network Configuration/Protocols again, right click on TCP/IP, choose Properties, then click on the IP Addresses tab. Look under IPAll in the TCP Dynamic Ports section. I personally wouldn't change this as I'm not really sure why SQL Server is using a different port now. I just used it in my Coldfusion datasource setup, and it connected fine.

Using LIMIT in mySql Update script

Wed, 14 Jan 2009 14:03:00 -0000

I've recently switched to MySql, as my present employer uses it. Its taken a bit of getting used to, but I'm coming around to it. One piece of functionality we have is a 'queue-ing' system. There is an image queue that users have to go through to 'vet' photos. In this instance of work I needed to update a collection of records. It didn't matter which ones, just enough to fill the queue.


UPDATE tableName
SET active = 0
LIMIT 500;

So rather than using a where clause it seems that you can use a mySql LIMIT value, to just return a set number of records. Simple but effective.