The N900 is more than capable of playing most non-HD videos. However, the built-in mediaplayer isn’t all that great, so you should go and install mplayer. Then install SiB which is a front-end for mplayer, and you’re set to go.
This is especially nice since you can connect the N900 to your TV with the cable that came with it. Watching movies in the bedroom, hotelroom and so on never was easier 🙂 Enjoy.