pf » Tag: sql on Pete Freitag's Blog

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.

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.

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).

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.

I am working on a management dashboard for a client, which uses some AJAX here and there.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Andy Jarrett posted this technique for insterting multiple rows (bulk insert) in one SQL statement a few months ago.

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.

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.

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.

Here's a handy query for finding duplicates in a table.

I was curious to know how much overhead client variables incur in an application.

I recently switched a database server from Microsoft SQL Server over to PostgreSQL.

Returning only the first N records in a SQL query differs quite a bit between database platforms.