Version Table in SQL

Question

Hi All, I Have a 2 questions, 1. Is it possible to Version Table in SQL Server and Mark that table(s) as Open for modification or closed for modification when presented in UI using C# ? i.e Versioned Table will need to have a Version Status to show if table in DB is Open/Closed for Modification 2. Is it possible to have Version-ID to primary key of the table?

Amit Gupta · Answer

Read this https://softwareengineering.stackexchange.com/a/395681 It is always a good practice to untouch the existing SP and redirect to the new one

Sachin Singh · Answer

suppose you have a UI for inserting data into table now when users enter data and click on submit you execute a stored procedure on submit click. now the StoredProcedure which is responsible for inserting or modifying data can be set to user level, means if you want that only admin can execute this SP then you can allow execute permission to a specific user called as Admin using below syntax GRANT EXECUTE ON SpInsert TO [UserName] https://www.sqlshack.com/restricting-and-monitoring-data-access-with-views-and-procedures/

Alpheus Lekgothoane · Answer

@Amit Gupta, kindly drop an example if possible please

Amit Gupta · Answer

Tables is used to store records, the logic or fetching/storing can be implemented via queries passed via Inline queries or storrProcedure. If you want to modify/inprove the query then you can version your storeProcedure, so you have all the old SP as per versions.