I think it may help to look at the division between 'in school' and 'outside school accountability' effects.
In school, the judgement you may have to make is whether the damage to your child is more done by the preparation (which you cannot avoid without taking Y6 out as a whole) or by the actual test week. Schools also differ enormously in how they approach SATs - from intensive year-long preparation with a limited curriculum, to a pattern of lessons that is indistinguishable from YR - Y5, with a few tests in May.
In secondary school, they will use other means to set your child if they do set, so the day to day experience should be unaffected 9though there may be greater uncertainty / moves between sets for your child than for others).
In terms of 'outside school accountability' - even if your child does not take the tests, they are included in the school accountability data. So if they are in a class of 30 and your child does not take the test, then [to a simplistic level) the average results of the class will be reduced by 1/30th as your child is regarded as a 'fail' rather than being removed from the average. This obviously has an impact on comparisons between your school and others, and on future Ofsteds. This is not an issue at all for you, but if you did choose the school on the basis of its results or its Ofsted, or if you like and value the school, it might be something to consider.
Equally, at secondary, the SATs results for your child will be used to measure their progress in a key school accountability measure, Progress8. Without SATs results, that measure - which again is used in comparison between schools, Ofsted etc - will be less accurate. However, tbh most secondaries are much larger, so the impact on school data is significantly lower than the effect on the primary.