Some useful SQL Server links (for me)

As a fairly well experienced database developer who's new to MS SQL Server, here are some links that have been useful to me so far:

SQLTeam.com: Introduction to Dynamic SQL Part 1
SQLTeam.com: Introduction to Dynamic SQL Part 2 - here sp_executesql is what I was really looking for, as it lets you execute dynamic SQL with bound parameters

Transact-SQL Data types reference (MS)

Translating from Oracle to SQL Server
SQL Server Stored Procedure Basics

The GO command in SQL Server is funny

Well I'm doing serious work in MS SQL Server for the first time, and I'm learning by doing, understanding its quirks as I go. The GO command, which seems analogous to / in Oracle's SQL*Plus, can be confusing!
http://www.sql-server-performance.com/articles/dba/go_command_p1.aspx

A classic database-related cartoon

Although, I was a little disappointed with the comment about sanitizing database inputs. You don't even have to sanitize them if you use bind variables. You don't need to run the input through some function that escapes quotes and such - just bind the input correctly and DONE!

OK maybe that doesn't hold true for ALL databases (I don't know one way or the other). Definitely true with Oracle though.

Remember kids (Oracle, redo logging)...

Borrowed from AskTom (and slightly cleaned up):

Table Mode Insert Mode ArchiveLog Mode  Result
---------- ----------- ---------------  ------
LOGGING    APPEND      ARCHIVELOG       redo generated
NOLOGGING  APPEND      ARCHIVELOG       no redo
LOGGING    no append   ARCHIVELOG       redo generated
NOLOGGING  no append   ARCHIVELOG       redo generated
LOGGING    APPEND      NOARCHIVELOG     no redo
NOLOGGING  APPEND      NOARCHIVELOG     no redo
LOGGING    no append   NOARCHIVELOG     redo generated
NOLOGGING  no append   NOARCHIVELOG     redo generated

Oww...My Brain!

http://thedailywtf.com/Articles/A-Confusing-SELECTion.aspx

No need for further comment.

Why Join Removal Is Cool

Article by Robert Haas about a, well, cool new Postgresql 9.0 feature - http://rhaas.blogspot.com/2010/06/why-join-removal-is-cool.html

Partitioned Aggregates ...or... I love "Distinct On"

Let's begin by creating a table dealing with loan data:

postgres=# create table loan (id serial primary key, type varchar(1), issue_date date, issue_amount numeric);
NOTICE: CREATE TABLE will create implicit sequence "loan_id_seq" for serial column "loan.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "loan_pkey" for table "loan"
CREATE TABLE

Create some test data in an appropriately-named file, testdata:

A|20090405|10000
A|20100101|50000
A|20070605|40000
B|19850815|100000
B|19900430|10000
C|20100304|5555
D|20011122|512591
D|20080517|250259
D|20100206|22222
D|20100204|99999

Load it up...

postgres=# \copy loan (type, issue_date, issue_amount) from 'testdata' delimiter '|'

postgres=# select * from loan;

id | type | issue_date | issue_amount
----+------+------------+--------------
1 | A | 2009-04-05 | 10000
2 | A | 2010-01-01 | 50000
3 | A | 2007-06-05 | 40000
4 | B | 1985-08-15 | 100000
5 | B | 1990-04-30 | 10000
6 | C | 2010-03-04 | 5555
7 | D | 2001-11-22 | 512591
8 | D | 2008-05-17 | 250259
9 | D | 2010-02-06 | 22222
10 | D | 2010-02-04 | 99999
(10 rows)

Now, here's a fairly common query I have to run. I'd like to get the most recent loan per type. That is, I want to tell postgres: For each type value (A,B,C,D), get me the row with the greatest issue_date.

How to do this? We obviously need to use max(issue_date) somewhere. The rest is a little bit more esoteric.

We're going to solve this problem using the unique-to-postgres DISTINCT ON feature. DISTINCT ON (col) gives you one row for each value of col. For example:

postgres=# select distinct on (type) * from loan;

id | type | issue_date | issue_amount
----+------+------------+--------------
1 | A | 2009-04-05 | 10000
4 | B | 1985-08-15 | 100000
6 | C | 2010-03-04 | 5555
7 | D | 2001-11-22 | 512591
(4 rows)

Ta-da! But wait, these aren't the most recent rows per type. An immediately obvious problem is that for type A, the row with id=2 should be returned. Postgres thought: For each type, return the first row I see. How do we get it to return the most recent only?

The answer lies in the good ol' ORDER BY clause. If we sort appropriately, the first row Postgres sees of each type will be the most recent:

postgres=# select distinct on (type) * from loan order by type, issue_date desc;
id | type | issue_date | issue_amount
----+------+------------+--------------
2 | A | 2010-01-01 | 50000
5 | B | 1990-04-30 | 10000
6 | C | 2010-03-04 | 5555
9 | D | 2010-02-06 | 22222
(4 rows)


Now I can really say Ta-da... This was something that was not very intuitive to me, but has proved very useful to know.

How would I solve this problem using Oracle? I would probably use a so-called Analytic query. Like most things Oracle, this feature is powerful but complex. It's more applicable to a wider set of problems, but for this specific problem it seems a little bit complicated.

Unfortunately I don't have an Oracle install handy at the moment, but it should be something like this:

select *
from (select id, type, issue_date, issue_amount,
row_number() over (partition by type order by issue_date desc) rn
from loan)
where rn = 1;

What we're doing above is telling Oracle to make a row number for each row in the table, partitioned by type. Conceptually it's doing something like this:

id|type|...|row_number
--+----+---+----------
1 |A |...|1
2 |A |...|2
3 |A |...|3
4 |B |...|1
5 |B |...|2
6 |C |...|1
7 |D |...|1
8 |D |...|2
9 |D |...|3
10|D |...|4

So above we see a row number assigned to each row, where the row number is reset to 1 for each type.

There is more to it. In the PARTITION BY clause we also have "order by issue_date desc" - this means for each type, row_number 1 is assigned to the row with the greatest issue_date. If we treat this whole query as a subquery, we can simply select * from it where row_number=1. The resultset should be the same as with Postgresql's DISTINCT ON:

 id | type | issue_date | issue_amount
----+------+------------+--------------
  2 | A    | 2010-01-01 |        50000
  5 | B    | 1990-04-30 |        10000
  6 | C    | 2010-03-04 |         5555
  9 | D    | 2010-02-06 |        22222
(4 rows)

DOH - conversions again

I was just running a series of long-running statements...An insert statement bombed because I had hard-coded a null value to insert into a newly added column. (As our data providers feed us the data later, it will be populated.) Use of a subquery seemed to confuse Postgres, breaking the statement. A simple example can illustrate:

core=# create table delete_me (a numeric);
CREATE TABLE

/* This works */
core=# insert into delete_me select null;
INSERT 0 1

/* This does NOT work */
core=# insert into delete_me select a from (select null as a) sub;
ERROR: failed to find conversion function from unknown to numeric

Upgrading from Postgresql 8.2 to 8.3

Postgresql 8.3 is already practically ancient history, but only a month or two ago did I complete upgrading the Postgresql 8.2 cluster on my data processing server. The biggest hurdle was that 8.3's increased strictness about data types. No more can you compare a varchar to an integer without an explicit cast. I wound up having to deal with quite a few of these errors:

ERROR: operator does not exist: character varying = integer at character 33
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT ( $1 = $2 )

Perhaps it's for the best, as I did uncover a bug! In 8.2 I was unwittingly joining two tables by different datatypes, table_a.my_integer against table_b.my_varchar. Even though the two columns were supposed to have the same symantic meaning, the varchar column contained the occasional alpha character, for example "1A"! It's funny the bugs you uncover when dealing with an external data provider's data...I could write a whole blog entry on that one. ;)

Not related to this hurdle, but 8.3 has a bunch of nice features. My favorite is being able to set database parameters on a per-function bases. On my database processing server, most of the time a small value for work_mem is fine, but I have this one function that does a huge amount of database processing, so I crank up the work_mem for just that function, without over-allocating for all the other functions (wasting memory = bad).

Automatic Recommendations

Hmm, I'm wondering if it just made some connection via the keywords "Oracle" and "Tarot", otherwise that's pretty odd. :)

Dear Amazon.com Customer,

We've noticed that customers who have purchased or rated Effective Oracle by Design (Osborne ORACLE Press Series) by Thomas Kyte have also purchased The Language of Tarot by Roberta Lee. For this reason, you might like to know that The Language of Tarot is now available. You can order yours for just $27.99 by following the link below.

Tuning Your PostgreSQL Server

Lately, as I upgrade Postgres to 8.3, I've been reevaluating the configurations on the many search databases I manage. My buddy discovered this basic guide to configuring Postgres (the postgresql.conf file) -
Tuning Your PostgreSQL Server.

This guide does not provide one-size-fits-all magical answers, but it provides a good baseline for settings such as shared_buffers and effective_cache_size. Obviously, your mileage may vary - if your database is OLTP or data warehouse, or you're just loading in lots and lots of data, you'll want to understand and tweak these parameters by hand. But if you follow this guide, at least some of your memory settings will be relative to your total RAM available! Initially all the parameters are set very low - this is so when you're initializing your database on a low-end system, the init script won't conk out due to lack of memory. That would be bad.

A blogger's "Five Months With MySQL Cluster"

"We discovered early on with cluster that we would have to redesign our application. Our DB was highly relational. Almost no data could be put on the site without data from other tables. We used a lot of joins. We learned (later) that joins in the cluster are not a good idea. Neither are sub-selects."
[runs away]

To be fair, he did then say: "So, we wrote some proof of concept scripts for our application. We were very happy. Very few issues were found. Nothing anywhere near show stopping."

I'm just not too keen on working around those little things called foreign keys, joins and such.

http://doughboy.wordpress.com/2007/06/07/five-months-with-mysql-cluster/

Yikes...

NOTE: This post is ancient, no longer applicable, and should be ignored. I say this because somehow I see this post still being accessed!

I am evaluating database clustering solutions. At the moment I am reading the MySQL documentation. I'm very skeptical but hey, I want to give it a fair chance.

The more I read though, the scarier it looks. Here are some of the "unsupported or missing features":

# Foreign key constraints. The foreign key construct is ignored, just as it is in MyISAM tables.
# Savepoints and rollbacks. Savepoints and rollbacks to savepoints are ignored as in MyISAM.
# Durability of commits. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-unsupported-missing.html

On other pages we have such gems as:

# Online adding or dropping of data nodes is not currently possible. In such cases, the entire cluster must be restarted.

But they also claim MySQL gives you "99.999%" uptime, or "five 9s" uptime. How they arrived at this number is beyond me, especially if you can't add nodes (what if one machine dies and you need to replace it?).

Like I said...yikes.