I'm building an application which will use some settings and a local SQL Server. My question is, when it comes time to update the application; will the settings or data be overwritten?
What happens if I want to change some tables around in the future?
User-level settings will not be overwritten during an update via ClickOnce, but you can push new application-level settings, because the [YourExeName].exe.config file will be overwritten during an update.
If you need to overwrite user-level settings, you will have to do this programmatically.
Frankly, I've always thought that ClickOnce's way of handling data is dangerous. If you deploy a database with ClickOnce, it puts it in the DataDirectory. Then when you deploy an update to the application, it copies the database forward to the folder where the next version of the app is installed. But if the database changes, it copies it forward to the folder + \pre, and puts a new one in the datadirectory. If you don't realize you changed it, it replaces it anyway. If you so much as open a SQLCE database and check out the data structures, wham it gets deployed. Surprise!
I think storing the data in another folder under the user's profile makes more sense, and is safer. Then YOU can choose when to update your database. This article will show how to move your data so it's safe from ClickOnce updates.
Additionally, when you DO want to make changes to your database, you can use SQL statements to do so, such as "ALTER TABLE" and so on. I've created a script and deployed it as one long string (with carriage returns in it) and had the application split the resource apart by carriage return and execute the statements one by one. You get the general idea.
One comment about user settings -- You can change these programmatically via the UI (i.e. give the user the capability). But note that if you change the certificate of your application and are running a high enough version of .NET (3.5, 4), it won't cause you a problem per se, but it DOES have a different identity as a ClickOnce application, and the user settings are not carried forward when the next update is published. For this reason, I also rolled my own XML file for config data, and I store it in LocalApplicationData as well.