If I purposely lock myself into a Standard User account, am I going to cause problems for myself later?

You will likely not be totally fine, but the issues should be minimal.

I have been running my developer PC this way for almost two years now, and it has been a while since I've seen any developer tools that even complained, let alone refused to work. Visual Studio and SQL Management Studio run fine without administrator rights. Permissions on databases can of course be adjusted so your normal user account can perform whatever tasks you need. [Soap Box] In fact, I would say you should run Visual Studio without administrator rights, even without elevation. The main reason we have this problem you're bringing up (can I run safely without administrator rights) is that developers write their software with administrator rights and never really test the software without. If our fellow developers did what you're doing years ago, you wouldn't be concerned enough to ask this question. You could safely assume things would work. [/Soap Box]

As for those applications that claim to require administrator rights, most of them can be fixed. You simply run Process Monitor (http://technet.microsoft.com/en-us/sysinternals/bb896645) and the application, and then look in Process Monitor's log for Access Denied messages. Change the permissions on the objects in question (registry keys or files), and the application will likely work. I work at a college, and we do not allow students to have admin rights in our labs. We've been doing this Process Monitor fix since the Windows 2000 days, and we've had great success.

One note about Process Monitor if you've never used it for this purpose. Often, programs will complain about not being able to read/write some important system object, such as C:\Windows\Explorer.exe or HKEY_LOCAL_MACHINE\Software. As a developer, you know these objects are critical and should not be wide open for access, as it defeats the point of running as a standard user. Look for other Access Denied messsages, and you'll likely find C:\Program Files\SomeApp or HKLM\Software\SomeApp. Those are likely your trouble spots.

"I'm totally fine with creating a separate "true" Administrator account, and then using a Standard User account"

That's what I do (Win 7) and it tends to work (for the most-part). Linux works like this too; but it's even more extreme, where each application typically has its own user and you NEVER log-in as root. Some distros won't even let you log-in (via the GUI) as root. If you find that you're going to be in Visual Studio or SQL Server Management Studio a lot, you can set them to run as Administrator automatically.

The main thing I would be concerned about (with logging in as Administrator) is your web browser. Your web browser is one of those things that should not be running as admin. It's a simple case of risk vs. reward. There's nothing your web browser should be doing that requires the admin user, so it's best not to risk it.