January 25, 2011
HTML5 SQL DB vs localStorage
I've now built some mobile applications using both HTML5's localStorage and the HTML5 Embedded SQLite Database (aka Web SQL Database).
December 09, 2009
Use varchar(max) instead of text in SQL Server
As you may know on SQL Server a varchar column can hold up to 8000 characters (each row can hold up to 8K, so it depends on the size of other columns), when you need to store more it is common to use the text or ntext datatypes.
August 05, 2009
Creating a Derby Datasource with ColdFusion Admin API
I am working on some example code for some CFUG managers who are demoing our ColdFusion WAF product at their groups. I wanted the demo to be very easy to setup, so I decided to use Apache Derby for the database, since it is embedded with CF8.
April 20, 2009
Cheat Sheet for SQL Server
I have been writing a lot of T-SQL Scripts for SQL Server for a client to migrate to a new database schema. Whenever I get deeply involved in something like that I find that it is handy to create a cheatsheet for all of the things you have to lookup in the docs.
December 05, 2008
DateFormat for SQL Server
Have you ever tried to find a DateFormat or date_format function in SQL Server? Chances are you probably ended up concatenating a bunch of DatePart strings to get the format you are looking for.
October 28, 2008
Top 10 Reserved SQL Keywords
You may know that I created a SQL Reserved Keywords Checker a few years ago. I was just noticing today that a lot of people are searching for the same keywords. Here's a list of the top 10 keywords people searched for using the tool in the past year:.
October 15, 2008
SQL Case Statement
Here's a simple example of a SQL CASE statement for Microsoft SQL Server:
July 31, 2008
Dear SQL Server Enterprise Manager Developer
Please add a button to make a column an identity.You have a button to set as primary key, why do we have to scroll down through 3/4 of the properties, click the plus under "Identity", and then choose "Yes" for such a common operation?
Thank You
July 24, 2008
Mastering CFQUERYPARAM
If you haven't been using the cfqueryparam tag, chances are you had a baptism by fire this week. As you may have heard, lots of ColdFusion powered sites were targeted by hackers using SQL Injection this week.
June 16, 2008
Getting ColdFusion SQL Statements from SQL Server Trace
Running a SQL Trace in the SQL Server Profiler can be a great way to track down performance problems in your ColdFusion application.
April 07, 2008
Try Catch for SQLServer T-SQL
Error handling in SQL Server T-SQL scripts is not always the most graceful thing to deal with. With the release of Microsoft SQL Server 2005 you can now use TRY / CATCH statements.
February 18, 2008
INFORMATION_SCHEMA Support in MySQL, PostgreSQL
I've known about the INFORMATION_SCHEMA views (or system tables) in SQL Server for a while, but I just leared recently that they are actually part of the SQL-92 standard and supported on other database platforms.
February 16, 2007
Use Char instead of Varchar to Store UUID's
I know a lot of developers like using UUID's for primary keys instead of integers. But one thing I see alot is the use of the varchar datatype to store these strings.
January 18, 2007
Calculating Distance in Miles from Latitude and Longitude
The amount of data out there via API's is increadible these days. For instance you can take an address, and get the latitude and longitude using Google's GeoCoding API.I am using this API along with some others to build a pretty some interesting stuff (more on that when its public).
January 10, 2007
Backwards LIKE Statements
Sometimes you need to think backwards.Here was the problem. I needed to match up some IP address ranges to the company that owns them.
January 04, 2007
Toggle a Bit with SQL
I am working on a management dashboard for a client, which uses some AJAX here and there.
December 04, 2006
Naming Tables
I am working on a project that has a database table called people. Now I am pretty sure that I was the one to name this table so I will take the blame, but it has been kind of hard to work with that name.
November 01, 2006
Sphinx - Open Source SQL Full Text Search Engine
I came across Sphinx today via the MySQL Performance Blog (which has some good entries you might want to check out). It is an Open Source Full Text SQL Search Engine.
March 28, 2006
Updated SQL Reserved Words Checker
I just updated my SQL reserved words checker to include a few new words from MySQL 5 that it wasn't picking up.I use the tool when I'm designing a database table, or column. It can tell you if a value is a reserved word in MySQL, PostgreSQL, Oracle, or SQL Server.
September 29, 2005
MySQL FULLTEXT Indexing and Searching
MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.To get started you need to define the FULLTEXT index on some columns.
September 14, 2005
SQL to Select a random row from a database table
There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.
September 07, 2005
Cheat Sheet for PostgreSQL
When I created my list of cheat sheets for web development last week, I left out PostgreSQL. Frank Reiser, asked why it was left out - and the reason was simply that I couldn't find one.Being a PostgreSQL fan, I decided to create a cheat sheet for postgresql myself.
September 07, 2005
Temporary Stored Procedures on SQL Server
I posted about doing fast bulk inserts with PostgreSQL last week, and with MySQL a while back. Now its time for Microsoft SQL Server.I'm using a technique similar to what I used for PostgreSQL for SQL server.
September 01, 2005
Cheat Sheet Roundup - Over 30 Cheatsheets for developers
Lets face it, unless you have a photographic memory, no developer can remember all the different functions, options, tags, etc. that exist. Documentation can be cumbersome at times, thats why I like cheat sheets.
August 31, 2005
Faster Inserts with PostgreSQL
As I mentioned yesterday, I'm working on optimizing lots of inserts into a database, and I need solutions for different DB servers. Today I have been working with PostgreSQL. Although the PostgreSQL COPY command is recommended, I can't seam to get it to work with ColdFusion, and cfquery.
August 30, 2005
Multiple Dynamic Inserts with SQL
If you have ever had to do lots of SQL INSERT statements you will know that they can get pretty slow. When looking for ways to speed up some inserts, I noticed that you can use a SELECT subquery to provide the values.
August 29, 2005
LIMIT and OFFSET SQL Pagination
Both MySQL and PostgreSQL support a really cool feature called OFFSET that is usually used with a LIMIT clause.The LIMIT clause is used to limit the number of results returned in a SQL statement.
August 02, 2005
Insert Delayed with MySQL
I just discovered that MySQL has some handy optimization hints, and commands that you can pass in your SQL statements. Since there is a lot to cover with each tip, I will be posting them in separate blog entries. I have already written about the MySQL Query Cache optimization hints.
June 20, 2005
Performance of database tag schemas
Philipp Keller has done some performance test on 4 different database schemas for storing tagged items. Although his article refers to tagging bookmarks, you can tag pretty much anything.
June 10, 2005
Multiple Inserts with MySQL
Andy Jarrett posted this technique for insterting multiple rows (bulk insert) in one SQL statement a few months ago.
May 16, 2005
SQL: Distinct values from two tables
Today must be database day for me...A question on my local CFUG mailing list asks how to remove duplicate values from two different tables:
I have 2 tables that store email addresses. One table is for newsletters and
the other is for registration to our site.
April 08, 2005
Related Posts
As you may have noticed I recently added tags to my blog. The main reason I implemented tags was so that I could find related posts based on mutual tags. So if two posts have similar tags, they will show up in as related.
March 28, 2005
SQL Reserved Key Words Checker Tool
I just whipped together a tool for checking for reserved sql words. The tool checks against SQL Server 2000, MySQL 5.0, PostgreSQL 8.0, Oracle 10g, ODBC, and SQL Server Future Keywords list.
October 06, 2004
Finding Duplicates with SQL
Here's a handy query for finding duplicates in a table.
December 04, 2003
Client Variables unnecessary overhead?
I was curious to know how much overhead client variables incur in an application.
October 23, 2003
Top 3 differences between PostgreSQL and MS SQL
I recently switched a database server from Microsoft SQL Server over to PostgreSQL.
April 19, 2003
Returning TOP N Records
Returning only the first N records in a SQL query differs quite a bit between database platforms.
