There are definitely some benefits to using SQL with ASP.NET, since the two are architecturally synchronized. However, plenty of high-performing applications use Oracle. The productivity gains from sticking with what you know will probably outweigh any sugar for SQL that .NET has baked-in. It supports a maximum database size of 4 gigabytes. It does not support stored procedures, triggers, views, or replication.
SQL Server Compact is the recommended database choice for both development and production in ASP.NET Web Pages applications that you develop by using WebMatrix. The advantages of SQL Server Compact make it an appealing choice in many other .NET web application scenarios also.