Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Question Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 10:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 1:40 PM
Points: 219, Visits: 838
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.
Post #1374923
Posted Friday, October 19, 2012 11:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:34 PM
Points: 12,995, Visits: 12,413
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 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.


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1374941
Posted Friday, October 19, 2012 11:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 13,776, Visits: 28,178
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1374942
Posted Monday, October 22, 2012 2:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 2,121, Visits: 3,206
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1375726
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse