TSQL - Create Table Design

I have a -

Table X  
------------------------------------
TaskId (bigint-identity-primary key)  
Data1 (varchar), Data2(varchar)

Now, I am creating a new Table -

Table Y 
------------------------------------
Id (identity, Primary Key)
TaskId(FK to Table X)
Data3(varchar)

Now, since Table X and Table Y will have a 开发者_如何学Goone-one relationship based on the TaskId column, so what and how should do I define the Primary Key, Index, etc on Table Y ?

Also, is it worth having an identity column at all as Primary Key if it will never be used in where clause ? I have just added it by convention.

Since this is 1:1 relationship, and every Y is a Task you can use this approach:

Table Y 
----------
TaskId (bigint),
Data3 (varchar),
PRIMARY KEY TaskId, 
FOREIGN KEY TaskId REFERENCES Task(TaskId)

I guess this is useful if only a few Tasks are Y-Tasks and not all of them. And you prefer not to have NULLs in your tables.

If it's a one-one relationship, why create a whole new table Y? Just add the new Data3 column to Table X.

Assuming you actually have a 1:Many relationship...

Keep your PK and Clustered index key on the ID field.

Create a non-clustered index on TaskID, Data3 or, if you never filter on Data3 and only SELECT it, just INCLUDE (Data3).

If you must separate it and you are sure that it will always remain 1-1, then use taskID as the PK. If it could be 1-many inteh future, add an identity and put a unique index on taskid. Then if you go 1-many all you have to do is drop the unique index.