Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    33

    Unanswered: Primary key clustered simple question

    Hello

    I have two tables. This tables include primary key clustered. My question is: How better?

    http://i43.tinypic.com/3589vma.jpg

    In case 1 foreign key in table TEST2 is both primary key ( Is it best practices? )

    In case 2 are two field ConstValueT and ConstValueT2 ( warning: in every table ConstValueT = ConstValueT2 ).

    I greet

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've attached your photo for people that can't access it via the link that you provided.

    Primary Key and Clustering are very casually related.

    A table should have one or more AKs (Alternate Keys). These AKs are groups of 1 or more columns that will always uniquely identify exactly one row. The database architect chooses one of these AKs to be designated as the PK (Primary Key). For most purposes, the PK then becomes the key used to find/join/identify rows.

    Clustering is a table/index attribute that determines the physical order of the rows on disk within that table. Depending on how the table is used, clustering can be used to put the rows into the physical order that generates the least disk I/O. For tables used primarily via JOIN opeartions, clustering on the PK is usually your best choice. For tables used primarily via date scans (looking for ranges of dates like the most current N rows), that date often makes the best clustering key.

    One item of note on PK versus clustering key lengths. The PK is repeated for every index and every declared foreign key, so this means that making the PK smaller can save ENORMOUS amounts of disk space and disk I/O for your system. The clustering key is simply stored as part of the row and isn't necessary to duplicate it at all (at least for clustering purposes) so there is no practical reason to limit the size of the clustering key and there might well be benefits to extending it.

    -PatP
    Attached Thumbnails Attached Thumbnails Dexter90.jpg  
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2012
    Posts
    33
    I using with replication database ( central subscriber ):

    Central Subscriber Model Explained - SQLServerCentral

    How to solve the problem of repeated records on the central subscriber? ( Two tables with related )

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The location_id that Brandon describes in that article identifies which publisher originally created the row. In the example that he posted, the ID values can (and are) repeated at each location, and the combination of the location_id and the ID columns are unique across all of the locations.

    If you look at the image for the central table, it has three rows where the ID is 1 but no two rows anywhere with the same ID and location_id. By making the PK two columns (id and location_id) at the central server, uniqueness is preserved.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2012
    Posts
    33
    "uniqueness is preserved."

    Excatly. Now I wouldlike replication my two tables ( up page ), my idea is good? ( Case 1 on image or Case 2 )

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If your records from one Publisher database, they can replicate to as many subscriber databases as you like.

    If your records come from many publisher databases, as long as only one of those publishers uses any given location_id (your diagram calls it ConstValue), then that will work nicely too.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •