What makes a "friendly URL"?
Tim Berners-Lee (the architect of the WWW) wrote a great article about this subject about 10 years ago.
Your example is a bad URL -- but not just because it has both an id and a "slug" (the abbreviated, hyphenated form of the page title). Putting the page title into your URL is problematic in the long term. Content will change over time. If you ever change the title of that blog post, you'll be forced to choose between keeping the old URL, or changing the URL to match the new title. Changing the URL will break any previous links to that page; and not changing it means you'll have a URL that doesn't match the page. Neither is good for the user. Better to just go with www.myblog.com/posts/123.
Users often do need to type a URL, but more importantly, sometimes they'll also edit existing URLs to find other pages in your site. Thus, it's often good to have discoverable URLs. For example, if I want to see post #124, I could easily look at the current URL and figure that the URL for the page I want to see is www.myblog.com/posts/124. That's a level of user-friendliness that can be a big help to people trying to find what they're looking for. Including other information (like the subject of the post) can make this impossible -- so it reduces my exploration options.
Forget about SEO. Search engine technology has been reducing the effectiveness of SEO hacks for some time. Good content is still king -- and in the long run, you won't be able to game the system.
To me, friendly URL means there's been some attempt to include semantic information in the URL to make it more fit for human consumption. It's an interesting example of a computer-computer interface being augmented and built upon to make a better human-computer interface.
So, in your two examples:
www.myblog.com/posts/123/this-is-the-name-of-my-blog-post
is friendly, because you've included the title in the URL - it tells you something about the page.www.myblog.com/posts.aspx?id=123
is unfriendly because it's cryptic and obscure: it makes perfect sense to a database, but none to you or me.
Friendly URLs are fantastic in some situations and useless in others. Basically, if a user is ever going to be exposed to it, I'd make friendly URL creation a priority, and it's not just a matter of aesthetics. It makes it much easier to get back to URLs from the address bar if you can quickly see and understand what the various options are, plus it makes it more obvious where you're about to go if you're following a link from a web page.
Combine all that with the awesome bar in Firefox 3+ (surely coming in other browsers too), and auto-complete in the address bar becomes incredibly powerful when you're dealing with friendly URLs.