In sybase I created a table EMP has emp_id,ssn,lname,fname attributes.emp_id is primary key and ssn is unique key.
By default primary key creates clustered index and unique key creates non-clustered index.
1.Does it creates seperate DATA PAGES for primary and unique key?
2.While I insert a record how does works?
1.primary key will be stored in the order of the emp_id on a DATA page. the row will contain all the columns that make up the table definition.
a separate index b-tree structure will be created for the unique key constraint. it will contain only the ssn value; and it too will be stored in order.
it will contain an address of where the value can be found on the DATA page
2 insert emp values (100,'abc-123','smith','adam')
Primary key: the DATA page will be access to put the row in it's correct place
in the table
Unique: looselfy speaking the leaf level of the index structure will be accessed to insert 'abc-123'. it will write the address of where 'abc-123' actually sits in the table.