You're asking the right questions about n-tier designs. It is certainly easier with small-to-medium systems to put all of the business logic in code-behind files, or closely-coupled utility classes.
However, long term maintenance of the site is much easier if the tiers are separated, or if you have a very large site.
Advantages of n-tier:
1) scalability - you can add data caching at several possible places without disrupting code in other tiers
2) maintainablity - you can modify code in one tier without breaking code in other tiers. It is understood that this doesn't fully apply to cases where you modify the underlying data elements. However, if your business logic is not tightly coupled to your DB schema, it is often possible to make schema changes that don't break lots of code. Things like "select *" is an invitation to disaster if the columns change, by the way.
3) security - you can implement security checks in several tiers to help stop hackers.
4) portability - it's easier to change DB platforms, or to re-use business components in other applications.
5) de-coupled tiers can allow developers to specialize. For example, many larger companies have developers who only do presentation level coding, or some that only do data tier coding.
6) you can modify the architecture to re-implement some functionality in new ways: maybe a web service, or remoting could replace a middle tier component. This is what Service Oriented Architecture is all about.
There are other advantages, and some disadvantages you didn't mention. Most companies strongly perfer the n-tier models for medium to large scale systems. Small systems are often on a short timeline and it's hard to develop and implement an n-tier design in only a few days.