Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: Identity and primary key

    I confuse about Identity and primary key, what is the different between them. One table can have no primary key. Right? Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes a table doesn't need a primary key....but if it's for relational data you should.

    Heap tables wouldn't have a PK.

    And IDENTITY Column is a special property that enables a column to set an incremental value...so you can't have the same value twice.

    Alot of people CONFUSE this as a Primary Key...they even go out of their way to make it one...so would you do?

    Code:
    CREATE TABLE State (
    	StateId int IDENTITY(1,1) PRIMARY KEY
    	, StateCd char(2)
    	, StateName varchar(50))

    And then still have to create a unique contraint on statedCd?


    Doesn't make sense does it.

    Some people will argue that at some point a state will change their code, and then you could just update the state table and be done with it, and not have to update every other table that stores state code.

    I find that argument amusing.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Glad we could entertain you!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    definitely i'm with brett on this one, the surrogate key for state code is insane

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I agree in principle, but as I've said before I have a code library of reusable functions, procedures, and subroutines that work off of GUIDs. So to me, the extra column is worth the time saved in development and maintenance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you use a GUID on a table of state codes and state names?

    WTF OMG LOL!

    no offence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I can just picture the performance on a system built based on PK's made of GUID's! Are they all clustered too?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not to launch the debate for the millionth time....but I've never heard of anyone using a GUID for code tables.....

    Yo, blind dude....what does it buy you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've never seen a performance hit. These are not Terabyte databases. And as far as a GUID on State codes, what does he cost me? There are only 50 records, so how much more disk space or processing time does it require?
    What it buys me is a lot of flexibility importing data and a lot of scalability across the enterprise. I can have multiple copies of the database at separate locations and merge the data without having to worry about key conflicts. I can pre-assign IDs to staging data and load it without ever having to figure out what incremental value was assigned to the record, for later processing. I can set up a single table in the database, for tracking record modification for instance, and have referential integrity between it and every other table in the database based solely on the Primary Key (or unique index, which is what I normally assign to the GUID value). I've developed Rapid Application Development templates in Access that allow me to create a new form based upon a table and have it automatically included in the menu system and synchronized with all the related forms in about fifteen minutes.
    I can't tell you the number of times a client has come to me with a modification requirement that I was able to implement quickly just because my schema was based upon GUID surrogate keys.
    I've use natural keys, identity keys, and GUIDs, and I've just decided that I can do a lot of cool SQL with GUIDs.
    As far as a performance hit, MSSQL's own replication relies upon GUIDs.
    So I'm gonna keep usin' 'em. Nyah!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    And as far as a GUID on State codes, what does he cost me? There are only 50 records, so how much more disk space or processing time does it require?
    well, okay, now, either you use a GIUD on the state code table just for the hell of it (insanity) with the 2-char state code as the primary key, or else you're using the GIUD as the primary key, in which case your 2-million-row employee address database has 2 million 16-byte GUIDs where a 2-char code would normally be

    so how much more disk space and processing does it require? 2 million times 26 bytes -- yeah, i know, that's not much space, but the processing might hurt a lot

    and don't forget you also have to always join to the state code table to find out that

    123 sesame street, hollywood, 3F2434E0-4F76-12F3-9A2C-0305E54C3301

    is in florida and not california

    insane, i tell you, insane
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, it is a disadvantage to have to join to the State code table to look up the state for a GUID. But that is a drawback of any surrogate key. Honestly, I don't always use surrogate keys for lookup tables, but when I do I use a GUID.

    Here is an excellent article on the pros and cons of GUIDs and INTs:
    http://www.informit.com/articles/art...=25862&redir=1

    ...so I ran a test on two tables. One table used INT, the other GUID. 1,000,000 rows in each table. 4,500 searches on each table. No discernable difference. Script is attached.

    For gig and up tables, maybe the size of the GUID would start to affect performance. I don't have time to test that. But I'm not convinced the performance hit would be at all significant. How many times to I have to save 1 millisecond on a search to justify even 15 more minutes writing code?

    Go ahead and laugh, but I chuckle every time I see a post on this forum asking how to retrieve the IDs of a batch of records just inserted.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, read the article....lots of caveats there.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, the article is great, for "Intro to Database Design 101". Either the author does not want to talk about "real" caveats, or he doesn't know about them himself. Here's the SHOWCONTIG results for both tables from Lindman's example, so YOU (Lindman) tell me what's wrong with this picture:

    ...wait a minute, I can't give you a final result because Lindman's script is still running, after 45 minutes...Is it because of those NEWID() function calls or because of IO? Well, it's IO, according to Perfmon. Anyway, based on partial results (250,000 rows or so) here's the output:

    Code:
    
    DBCC SHOWCONTIG scanning 'TestIdentity' table...
    
    Table: 'TestIdentity' (795149878); index ID: 1, database ID: 9
    
    TABLE level scan performed.
    
    - Pages Scanned................................: 1445
    
    - Extents Scanned..............................: 0
    
    - Extent Switches..............................: 0
    
    - Avg. Pages per Extent........................: 0.0
    
    - Scan Density [Best Count:Actual Count].......: 0.00% [0:0]
    
    - Logical Scan Fragmentation ..................: 0.55%
    
    - Extent Scan Fragmentation ...................: 0.00%
    
    - Avg. Bytes Free per Page.....................: 0.0
    
    - Avg. Page Density (full).....................: 99.81%
    
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    
    DBCC SHOWCONTIG scanning 'TestGUID' table...
    
    Table: 'TestGUID' (779149821); index ID: 1, database ID: 9
    
    TABLE level scan performed.
    
    - Pages Scanned................................: 2581
    
    - Extents Scanned..............................: 0
    
    - Extent Switches..............................: 0
    
    - Avg. Pages per Extent........................: 0.0
    
    - Scan Density [Best Count:Actual Count].......: 0.00% [0:0]
    
    - Logical Scan Fragmentation ..................: 99.11%
    
    - Extent Scan Fragmentation ...................: 0.00%
    
    - Avg. Bytes Free per Page.....................: 0.0
    
    - Avg. Page Density (full).....................: 69.56%
    Pay attention to Logical Scan Fragmentation.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pay attention to the results, Czarjabarov. There was no discernable or consistent difference after running 4500 selects.

    Outahere.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by blindman
    Pay attention to the results, Czarjabarov. There was no discernable or consistent difference after running 4500 selects.

    Outahere.
    You wouldn't be showing "online" if you were really "outahere", and please refrain yourself from chauvinistic comments,it's not gonna get you anywhere (at least not where you want to be)

    ...And, for your information, not all apps are written to support the primitivism of the logic that you used in your example, so there, - outahere
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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