SQL Question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » SQL Question

SQL Question

Rate Topic

Display Mode

Topic Options

Author

Message

rocky_498

Posted Friday, October 19, 2012 10:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:46 PM
Points: 149, Visits: 546

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

Sean Lange

Posted Friday, October 19, 2012 11:06 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 8,620, Visits: 8,261

rocky_498 (10/19/2012)

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

Post #1374941

Grant Fritchey

Posted Friday, October 19, 2012 11:10 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:14 PM
Points: 13,381, Visits: 25,174

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1374942

ScottPletcher

Posted Monday, October 22, 2012 2:27 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1375726

« Prev Topic | Next Topic »

Permissions