One of the things I struggle with is not using Hungarian notation.  I don't want to have to go to the variable definition just to see what type it is.  When a project gets extensive, it's nice to be able to look at a variable prefixed by 'bool' and know that it's looking for true/false instead of a 0/1 value.

I also do a lot of work in SQL Server.  I prefix my stored procedures with 'sp' and my tables with 'tbl', not to mention all of my variables in the database respectively.

I see everywhere that nobody really wants to use Hungarian notation, to the point where they avoid it.  My question is, what is the benefit of not using Hungarian notation, and why does the majority of developers avoid it like the plague?

Because its original intention (see http://www.joelonsoftware.com/articles/Wrong.html and http://fplanque.net/Blog/devblog/2005/05/11/hungarian_notation_on_steroids) has been misunderstood and it has been (ab)used to help people remember what type a variable is when the language they use is not statically typed. In any statically typed language you do not need the added ballast of prefixes to tell you what type a variable is. In many untyped script languages it can help, but it has often been abused to the point of becoming totally unwieldy. Unfortunately, instead of going back to the original intent of Hungarian notation, people have just made it into one of those "evil" things you should avoid.
Hungarian notation in short was intended to prefix variables with some semantics. For example if you have screen coordinates (left, top, right, bottom), you would prefix variables with absolute screen positions with "abs" and variables with positions relative to a window with "rel". That way it would be obvious to any reader when you passed a relative coordinate to a method requiring absolute positions.
update (in response to comment by delnan)
IMHO the abused version should be avoided like the plague because:

it complicates naming. When (ab)using Hungarian notation there will always be discussions on how specific the prefixes need to be. For example: listboxXYZ or MyParticularFlavourListBoxXYZ.
it makes variable names longer without aiding the understanding of what the variable is for.
it sort of defeats the object of the exercise when in order to avoid long prefixes these get shortened to abbreviations and you need a dictionary to know what each abbreviation means. Is a ui an unsigned integer? an unreferenced counted interface? something to do with user interfaces? And those things can get long. I have seen prefixes of more than 15 seemingly random characters that are supposed to convey the exact type of the var but really only mystify.
it gets out of date fast. When you change the type of a variable people invariably ( lol ) forget to update the prefix to reflect the change, or deliberately don't update it because that would trigger code changes everywhere the var is used...
it complicates talking about code because as "@g ." said: Variable names with Hungarian notation are typically difficult-to-pronounce alphabet soup. This inhibits readability and discussing code, because you can't 'say' any of the names.
... plenty more that I can't recall at the moment. Maybe because I have had the pleasure of not having to deal with the abused Hungarian notation for a long while...

