October 23, 2013 at 9:40 am
I would like to try and get the Rowversion change snuck into the schedule sometime soon.
An overview of this plan:
If we use the following command we can add a rowversion column to every table in both our main and settings databases.
EXEC sp_MSforeachtable '
if not exists (select * from sys.columns
where object_id = object_id(''?'')
and name = ''RowVersion'')
begin
ALTER TABLE ? ADD [RowVersion] Rowversion NOT NULL;
end';
With the rowversion column in place we can quickly check that data we are about to update is not stale, without having to read entire row into memory.
This is targeted more at disconnected/web service application contexts.
Entity Framework can be configured to take advantage of the column as well.
The plan was to add a one-time entry into the each of the update scripts to initialize this on all of the tables. And then add code to always run this command after main data or setting data updates are triggered (actually do something).
Any tips how can I implement it??
October 23, 2013 at 10:10 am
Is what you have not working? Are you getting an error message?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 23, 2013 at 10:41 am
I have not done the changes in Database so far but I was looking for any better option
October 23, 2013 at 10:45 am
sumair.haider (10/23/2013)
I have not done the changes in Database so far but I was looking for any better option
What you posted worked fine on a dev database I have. If you want to add this column to every single table in the database this is pretty much how I would do it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 23, 2013 at 10:50 am
The only thing I would suggest is give the column a better name. You shouldn't use reserved words or datatype names as column names
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2013 at 12:03 pm
GilaMonster (10/23/2013)
The only thing I would suggest is give the column a better name. You shouldn't use reserved words or datatype names as column names
+100000000000
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply