SQL Server Performance

What is the purpose of symmetric key?

Discussion in 'SQL Server 2005 General Developer Questions' started by pcsql, Sep 12, 2007.

  1. pcsql New Member

    Is symmetric key used for security purpose? If I just want a unique identifier for a table, what are the possible options and their pros and cons? The table will be accessed thru dataset created in Visual Basic 2005.
  2. ghemant Moderator

  3. satya Moderator

  4. pcsql New Member

    Hi Satya,
    I just simply want a unique column and its value is generated automatically by SQL Server. It seems that Identity column is the one that I should use. I have also read about uniqueidentifier and I think it does not mean that it is unique within the column unless it has unique constraint or primary key constraint. I'm little bit confused when one should use uniqueidentifier. Also, when a uniqueidentifier column has the unique constraint, how does sql server generate the value and whether it will have performance issue?
  5. satya Moderator

    Go with IDENTITY column...
  6. DilliGrg Member

    Uniqueidentifier is a GUID (Globally Unique IDentifier) which consumes more space than typical identity column (an example of a formatted GUID:B85E62C3-DC56-40C0-852A-49F759AC68FB). Generally, you need to try avoiding this key as your primary key on the table. I am already having hardtime spelling this word. [;)] Unlike an Identity column, a uniqueidentifier column doesn't automatically get an assigned value when a row is inserted into a table. You either need to place a default on the uniqueidentifier column (DEFAULT NEWID()) or place some logic in your insert stored procedure. You can find many advantages and disadvantages of UniqueIdentifier in BOL or online.
  7. pcsql New Member

    Hi DilliGrg,
    If I understand correctly, uniquenidentifier does not mean that it is unique within the column unless it has unique constraint or primary key constraint. If this is true, can I say that its uniqueness only applies among machines but not within same machine?
  8. ghemant Moderator

    Excerpts from BOL
  9. DilliGrg Member

    There you go!!!
  10. pcsql New Member

    Hi Hemantgiri and Dilli,
    I have also read that section of BOL. The following part is the one I don't quite understand:
    uniqueidentifier columns may contain multiple occurrences of an individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY constraints are also specified for the column. A foreign key column that references a uniqueidentifier primary key in another table will have multiple occurrences of individual uniqueidentifier values when multiple rows reference the same primary key in the source table.
    It sounds like the uniqueidentifier won't be unique "WITHIN" a column unless UNIQUE or PRIMARY KEY constraint is also specified for the column. Is my interpretation correct?
    Peter
  11. satya Moderator

    http://www.sql-server-performance.com/faq/unique_identifier_column_p1.aspx FAQ fyi.
    Unless you need a globally unique identifier, you're probably better off sticking with an IDENTITY. Now, unlike an IDENTITY column, a uniqueidentifier column doesn't automagically get an assigned value when a row is inserted into a table. You either need to place a default on the uniqueidentifier column (DEFAULT NEWID()). The major advantage of using GUIDs is that they are unique across all space and time.
  12. pcsql New Member

    Hi Satya,
    Thanks for the link. For my current issue, using IDENTITY column seems to be the better choice. However, I will still like to get a better understanding of uniqueidentifier since I'm sure that I also need to deal with transferring rows from one table of a database to a corresponding table of another database. I hope you can explain the following paragraph:
    uniqueidentifier columns may contain multiple occurrences of an individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY constraints are also specified for the column. A foreign key column that references a uniqueidentifier primary key in another table will have multiple occurrences of individual uniqueidentifier values when multiple rows reference the same primary key in the source table.
  13. DilliGrg Member

    [quote user="pcsql"]
    uniqueidentifier columns may contain multiple occurrences of an individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY constraints are also specified for the column. A foreign key column that references a uniqueidentifier primary key in another table will have multiple occurrences of individual uniqueidentifier values when multiple rows reference the same primary key in the source table.
    [/quote]
    I am assuming that you know what is One-to-Many relationship means. Of course, you have to define this column as PK which itself is unique if you want to refer to other column as FK. In this case, you will have One source table PK value and multiple references to this value from other tables. I think it's better to read more on BOL or google it if you need more info.

Share This Page