SQL Server Performance

turn tables/cascading tables

Discussion in 'SQL Server 2005 General Developer Questions' started by v1rt, Jan 1, 2010.

  1. v1rt New Member

    Good afternoon folks,
    I chatted with a friend last night. He's also good in sql but not sure which one. He is also the person who does the technical interviews. Anyways, we talked about few sql and I froze after he started telling me to brush up on cascading tables and turn tables. I've never heard about them. I'm very willing to learn and would like to seek some advice here. He also game me an example on how cascading tables can be use say for a class-students-subjects tables. I remember he mentioned about 1 to many or many to many and as well as how to design it ideally especially if you delete a subject, how can the other be udpated. I also remember he said that triggers will work but not ideal.
    I'm very willing to learn them. I also like to learn proper designing.
    Any help would be greatly appreciated.

  2. v1rt New Member

    maybe, he really said cascade delete. Is this possible in 2000 or 2005?
  3. arunyadav Member

    Hello,One-to-One relationship can be implemented as a single table or two tables with primary and foreign key relationships.One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.Many-to-Many relationships are implemented by using a junction table with the keys from both the tables forming the composite primary key of the junction table.Yes, cascade is possible in SQL Server 200x. This option is available for ON DELETE and ON UPDATE. While "cascade" could be an option over triggers, the choice depends on the database design and business requirements. For example when deleting an employee's record from employee table you may want to delete the related records from emp_details table but not from emp_history OR, instead of delete you may just want to update the name with Name_deleted_datetime. Kindly refer to the below links for more details. http://www.visual-paradigm.com/VPGallery/datamodeling/OneToOneRelationship.htmlhttp://msdn.microsoft.com/en-us/library/ms186973(SQL.90).aspx
  4. vambati New Member

    Basically these are useful if you have cascades to be done between 2 tables, but when many tables are involved...say on update of a table u want to cascade the same to 10 other tables - triggers are best even though there might a slight performance hit. But as arun said it all depends on business requirment and how simple do you want to keep the colution.
  5. moh_hassan20 New Member

    use Cascade with care , otherwise you will delete /update all date in the cascade chain by mistake

Share This Page