I think the inclusion of the HD will have a visual effect, but in a rather indirect way. When in comes to using high rez textures load times are a huge consideration. If you keep lots of little textures in memory you have a library of textures often large enough to create an entire level with. As you move to larger textures you have less textures in memory thus less variety. One option is you simply make your level smaller; this is why tech demos can use huge textures because they only need enough textures in memory for a small level. If you build you game out of these small levels you free yourself to use larger textures or more textures for a given amount of space.
The trade off is frequent loads, and a larger disk footprint. HL2 is a good example of this philosophy the biggest thing that makes that game look so good is the extremely high rez textures. Yet it has to frequently load in order to erase what is currently in memory and replace it with new stuff. When you look at some of the ps3 demos you can see the really high rez textures being used, without the HD load times on these games could increase to crazy levels.
Streaming may eliminate the loading, but their is a finite rate at which things can be streamed. The faster this rate the more texture info can be used per a given area. The HD will stream faster than cd. Effective streaming is a really big deal having the most dramatic effect on texture detail next to adding more memory.
You will also need the HD for someplace to write to. If games allow for lots of destruction and environment interaction the game saves may grow exponentially so that all the changes to the environment may be persisted. This will make more sense in some game types than others. There are a few games on the pc that have allowed their saves to grow to the 100mb+ range, the amount of information persisted in these types of saves allows for the players past to have a stronger impact on game play.
Another thing, you may write the frame buffer to the HD. This would allow you to persist the frame buffer. This may be good to create procedural textures that require non real-time processing. For example you could take 10 seconds or more to generate some super detailed texture and store it on the drive for later use in the game. It would save BR disk space and may be dependent on some type of player interaction in order to be generated (like scanning the players face through eye toy) for example.