window.open opens a new independent window. It works in most browsers in some way (although pop-up blockers often block them or require additional user confirmation). It is possible to access the document that opened the window using the window.opener property.

showModalDialog opens a dialog that is tied to the current page. It is impossible to do anything on the page until the dialog gets closed. (MSDN docs)

The most important distinction between the two is that showModalDialog halts the execution of the JavaScript until the dialog box is closed, and can return a return value (at least in Internet Explorer). In that, it works similar e.g. to the confirm() dialog. In contrast, window.open opens a window asynchronously: script execution will continue immediately, even while the new window loads.

It is possible to access the parent document from the dialog with some property whose name I can't remember right now, but it is different from window.open.

One more thing to note is that in my experience, modal dialogs are awfully difficult to refresh, as they seem to be subject to different caching rules than normal pages. The F5 key won't work to refresh the page. One workaround is to use a random addition to the loaded URL every time (pagename.htm?random=1203402920)

In general, seeing as showModalDialog is a proprietary function and its functionality can't be easily ported to other browsers, it's usually best not to use it.