How to Restrict Database for One User in SQL Server 2008 R2?

Auto-generated primary key Versus user defined primary key?

• i am designing a database using sql server 2008 r2. In around 90% of the tables i have added an identifier column (i set the Identity specification to Yes) that will contain an auto-generated number that can uniquely indentify the table rows. But in some tables i have a unique value that the users can enter which can also uniquely indentify the table rows without that need to have an auto-generated ID column. for example i have a table named Document_Status which have a Description column that include values such as (new, draft, published, canceled,etc); now in the Document_status table no two rows can have the same Description value. so should I in this case set the Description column as the primary key and there will be no need to set an auto-generated ID field in the Document_status table.? OR it is always better to have an auto-generated column as the primary key? BR

• Answer:

  Varchar columns make lousy targets for indexes and primary keys in particular. Remember that a primary key is indexed by default and this means every entry in Description would be duplicated in the index, thus wasting tablespace. In addition, comparisons between varchars is REALLY slow as opposed to those between numbers, so queries are likey to be much slower to process. Keep your primary key something else and just have an UNIQUE constraint on the Description column if you really must enforce uniqueness for the column.