I'm using a MS SQL Server db and use plenty of views (for use with an O/R mapper). A little annoyance is that I'd like to
but run into the issue that whenever I want to e.g. add a column to a table, I have to first drop all views that reference that table, update the table, and then recreate the views, even if the views wouldn't need to be updated otherwise. This makes my update scripts a lot longer and also, looking the diffs in the source control system, it is harder to see what the actual relevant change was.
Is there a better way to handle this?
I need to still be able to use simple and source-controllable sql updates. A code generator like is included in SQL Server Management Studio would be helpful, but I had issues with SQL Server Management Studio in that it tends to create code that does not specify the names for some indices or (default) constraints. But I want to have identical dbs when I run my scripts on different systems, including the names of all contraints etc, so that I don't have to jump through loops when updating those constraints later.
So perhaps a smarter SQL code generator would a solution?
My workflow now is:
alter table
statement in query editorcannot ALTER 'XXX' because it is being referenced by object 'YYY'
."create
code for the referenced objectdrop
statement before the alter statement and create statement afterdrop
statement creates error and repeatthis annoys me, but perhaps I simply have to live with it if I want to continue using schemabinding and script updates...
To change the schema of a table by using SQL Server Management Studio, in Object Explorer, right-click on the table and then click Design. Press F4 to open the Properties window. In the Schema box, select a new schema. ALTER SCHEMA uses a schema level lock.
You can use the Update Database Schema command in iBase Designer to manage this process, making the changes and then applying them to the other databases by applying a new database template. This process is only suitable for compatible databases.
Answer: SCHEMABINDING keywords prevent tables used in the views to make any such modifications that can affect the view's definition. When this keyword is used in the view it binds the view to the schema of the underlying tables.
SCHEMABINDING. Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
Updating a database schema is pretty easy if you can take your application offline. You shutdown the application, create a backup of the current database schema, perform all required update operations using tools like Flyway or Liquibase, restart the application and hope that everything works fine.
And while not having to worry about database schema might sound great, in most cases, it just kicks the can down the road. Because most applications require some or all of the data they use to be structured, you still end up having a data schema. It’s just defined and enforced in your application rather than in your database.
Doubling your database costs just to prevent downtime during schema changes is a tough pill to swallow. Schema updates on legacy RDBMS get even more complicated if you’ve sharded your database to scale it horizontally. In that case, you’ve created an ad-hoc distributed database, so building a replica of it is no longer straightforward.
The idea is that an application is backward-compatible with multiple versions of the database schema and is the first to upgrade at all times. An application can discover database schema at any point in time and adjust behavior.
You can at least eliminate the "check if I get an error" step by querying a few dynamic managment functions and system views to find your dependencies. This article gives a decent explanation of how to do that. Beyond that, I think you're right, you can't have your cake and eat it too with schema-binding.
Also keep in mind that dropping/creating views will cause you to lose any permissions that were granted on those objects, so those permissions should be included in your scripts as well.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With