|
How to find out the size of data in MS SQL databases |
||||||||
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.
|
Returning values from mySQL in a select query as Yes/No rather than 1/0 |
||||||||
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:
2SELECT intid,varuserfname,varusersname, IF(intactive<>0, "Yes", "No")
3FROM 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.
2SELECT intid,varuserfname,varusersname, IF(intactive<>0, 'Yes', 'No') as intactive
3FROM table.users
|
Emulating the mySql limit function in MS SQL |
||||||||
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.
2<cfset rowsperpage = 15>
3
4DECLARE @rowsperpage INT
5
6 DECLARE @start INT
7
8 SET @start = #startpos#
9 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.
2
3 (SELECT row_number() OVER (ORDER BY [intId]) AS rownum, *
4
5 FROM [table]
6 Where [table].intId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.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.
2Order by rownum
The whole script looks like this:
2<cfset rowsperpage = 15>
3
4DECLARE @rowsperpage INT
5
6 DECLARE @start INT
7
8 SET @start = #startpos#
9 SET @rowsperpage = #rows#
10
11SELECT * FROM
12
13 (SELECT row_number() OVER (ORDER BY [intId]) AS rownum, *
14
15 FROM [table]
16 Where [table].intId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.intId#">) AS tempQuery
17
18Where tempQuery.rownum BETWEEN (@start) AND (@start + @rowsperpage)
19Order 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:
2From table
3Limit 0,100
|
Quotes being changed to question marks using cfsavecontent and MySql |
||||||||
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:
2<cfcontent type="text/html; charset=iso-8859-1">
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.
2Html template code
3<body> etc </body>
4</cfsavecontent>
5<cfquery datasource="#application.dsn#">
6INSERT INTO table (fields)
7VALUES ('#htmlContent#');
8</cfquery>
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.
