One of the many reservoirs that I have worked around.
The effects of submersion vary due both to the type of site in question, and where in the reservoir it ends up. For example, I have just finished assessing the condition of a historic-era site, the remains of an old building now long-since fallen over, and it's doing alright. The reservoir water cover part of the site for a couple of months every year, which results in sediments being brought to and taken from the site because of the changing reservoir levels (reservoirs fill and are emptied at various time throughout the year) and because of the impacts from waves. In this location, a species of grass has grown that manages to survive the occasional inundation, and it is helping to hold more of the sediment in place than would otherwise be the case. The building remains are concrete, and while the reservoir is wearing them down, it is doing so slowly, and most of the material appears to be more-or-less intact.
By contrast, a prehistoric site nearby is located just a few feet lower on it's slope, and as a result the reservoir spends a bit more time inundating it, and all of the vegetation has been removed, which results in the ground being bare, which results in site soils being leached of some organic materials and generally eroded away as the reservoir rises, lowers, and as waves hit the site. An area that was once midden* is now barren sand, fragments of flaked stone debitage** as well as some of the obsidian tools whose manufacture created the debitage has been moved around (and likely some of it removed) by the water - although it should be noted that in some cases the reservoir actually deposits sand on sites and prevents this kind of damage. A combination of soaking and drying out and pounding action from the waves have caused the bedrock mortars to erode, to the point that some of the mortar cups are barely recognizable anymore. Materials that can be important to figuring out how old a site is or what plants were used, such as charcoal, tend to be moved and/or destroyed by the water.
Another view of the same reservoir.
There are other sites in the vicinity, which are always submerged below the reservoir. The effects of the reservoir on these sites is open to question, but based on examples from other places it is likely that much of their organic material (midden, pollens, animal bone, etc.) has been damaged, but that materials such as flaked stone and bedrock features may have been preserved under layers of deposited sediments (though it is also possible that these have been damaged by subsurface water movement). Regardless of the site's preservation or lack thereof, the submersion effectively removes it from both archaeological study and use by Native Americans descended from the original site occupants.
Impacts don't just come from the reservoirs themselves, though. The presence of a reservoir often results in recreation as swimmers, fishers, and boaters flock to the area. The effects of this can include damage to sites due to boat propellers (I was recently on a site where the bedrock mortars had marks from where boat propellers had hit them during high-water levels), artifacts being inadvertently destroyed by people walking on them (or in some cases doing things like building campfires on top of site features), and looting due to more people being aware of the site's presence because they are around it more often. All of this gets intensified when, as often happens, communities appear around reservoirs due to people wanting lakefront houses.
So, reservoirs have detrimental effects on archaeological sites, to be certain. However, this is a classic example of the trade-offs that one sees when working in environmental consulting. Reservoirs are expensive to create and maintain, and they are always built with a reason, the supply of water and the creation of hydroelectric power being two big reasons. The need for water - for drinking and for agriculture - is obvious. Hydroelectric facilities have their own set of environmental problems, but also help to reduce the need/desire for the construction of coal and petroleum burning power plants. So, reservoirs are typically a case where archaeology has had to suffer for what the society at large has deemed a greater good. It's this sort of trade-off that has caused many of my academic colleagues to reject cultural resource management as a career choice, but it's also these sorts of trade-offs that point to the need for someone to speak for these resources so that they aren't forgotten in the constellation of other issues surrounding land modification, even if that simply means that sites are excavated prior to being inundated.
*Middens are dark, nutrient-rich soils that contain artifacts, the remains of prehistoric trash dumps and a treasure-houses of archaeological information.
**The rock that is chipped away when making flaked stone tools.