Is there any reason to worry about the column order in a table?

Column order had a big performance impact on some of the databases I’ve tuned, spanning Sql Server, Oracle, and MySQL. This post has good rules of thumb: Primary key columns first Foreign key columns next. Frequently searched columns next Frequently updated columns later Nullable columns last. Least used nullable columns after more frequently used nullable … Read more

PostgreSQL create table if not exists

This feature has been implemented in Postgres 9.1: CREATE TABLE IF NOT EXISTS myschema.mytable (i integer); For older versions, here is a function to work around it: CREATE OR REPLACE FUNCTION create_mytable() RETURNS void LANGUAGE plpgsql AS $func$ BEGIN IF EXISTS (SELECT FROM pg_catalog.pg_tables WHERE schemaname=”myschema” AND tablename=”mytable”) THEN RAISE NOTICE ‘Table myschema.mytable already exists.’; … Read more

T-SQL Pivot? Possibility of creating table columns from row values

Itzik Ben-Gan’s example on how to build dynamic PIVOT, I highly recommend his Inside Microsoft SQL Server 2008: T-SQL Programming book — Creating and Populating the Orders Table USE tempdb; GO IF OBJECT_ID(‘dbo.Orders’) IS NOT NULL DROP TABLE dbo.Orders; GO CREATE TABLE dbo.Orders ( orderid int NOT NULL PRIMARY KEY NONCLUSTERED, orderdate datetime NOT NULL, … Read more