October 19, 2012 at 10:31 am
Some body ask this question during interview. I didn't the answer i would like to know if someone give me the answer of
this simple or tuff question.
Q) What would be your schema layout to track Update and delete from Table A..
Please let me know if my question is not clear. I will try to provide more information.
Thanks in advance.
October 19, 2012 at 11:06 am
rocky_498 (10/19/2012)
Some body ask this question during interview. I didn't the answer i would like to know if someone give me the answer ofthis simple or tuff question.
Q) What would be your schema layout to track Update and delete from Table A..
Please let me know if my question is not clear. I will try to provide more information.
Thanks in advance.
The first thing for me would be a question. Are they wanting to track all updates and deletes from this table? If that is the answer then I would tell them that CDC is a great option for this.
_______________________________________________________________
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 19, 2012 at 11:10 am
I'm not sure this is a schema question. There are technologies within SQL Server that will allow you to track changes, such as Change Data Capture. I'd suggest those before I'd suggest some other schema.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2012 at 2:27 pm
I would have to know the version of SQL to know how to answer this.
For 2008+, you have CDC.
For SQL prior to that, they probably wouldn't like my answer, but I would make the schema identical to the original table, adding a column(s) to the original schema if needed to support the logging.
This allows me to use all existing code and procs on the logging table, and/or to UNION ALL the two tables in such code/procs.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply