pf » Tag: sql on Pete Freitag's Blog
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
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
December 04, 2003
Client Variables unnecessary overhead?
October 23, 2003
Top 3 differences between PostgreSQL and MS SQL
April 18, 2003
Returning TOP N Records
Subscribe to my RSS Feed:
RSS
RSS
Pete Freitag is a software engineer, and web developer located in




