Keys

Question 1

What is the difference between an Intelligent or Natural key and a surrogate key?

Answer 1

An Intelligent or Natural key is a key made from a data attribute that already exists. For example social security number.

A surrogate key has no meaning associated with the value.

SQL Server 70-461 02-02a

Question 2

What are the typical options used to create Surrogate Keys?

Answer 2

1. The identity column property
2. The sequence object
3. Nonsequential GUIDs
4. Sequential GUI Ds
5. Custom solutions

SQL Server 70-461 02-02a

Question 3

What does the Identity column property do?

Answer 3

It automatically generates keys in one of the following data types:
TINYINT
SMALLINT
INT
BIGINT
NUMERIC with scale of 0
DECIMAL with scale of 0

SQL Server 70-461 02-02a

Question 4

What is the Sequence Object?

Answer 4

It’s an independent object in the database that you can get a new sequence of values from.

SQL Server 70-461 02-02a

Question 5

What data types are supported when using the Sequence Object?

Answer 5

Just like the Identity Property, any numeric data type is supported. Any numeric data type that uses scale must have a scale of 0.

TINYINT
SMALLINT
INT
BIGINT
NUMERIC with scale of 0
DECIMAL with scale of 0

SQL Server 70-461 02-02a

Question 6

How is the Sequence Object different from the Identity Property?

Answer 6

It is not tied to a particular column, it is independent.

SQL Server 70-461 02-02a

Question 7

What is the difference between UNIQUEIDENTIFIER and GUID?

Answer 7

There is none, they are the same thing. The UNIQUEIDENTIFIER data type stores non–sequential GUIDs.

SQL Server 70-461 02-02a

Question 8

How do you generate a new non–sequential GUID?

Answer 8

You use the function NEWID.

SQL Server 70-461 02-02a

Question 9

What is a GUID?

Answer 9

It is a globally unique identifier. They are guaranteed to be unique across space and time.

SQL Server 70-461 02-02a

Question 10

How do you generate a sequential GUID?

Answer 10

You use the function NEWSEQUENTIALID.

SQL Server 70-461 02-02a

Question 11

What is a natural key?

Answer 11

• A column or combination of columns that uniquely identify each row.
• An example would be category name in production.categories table

SQL Server 70-461 08-02

Question 12

What is one good way to name your PK column?

Answer 12

• Simple TableNameID
• Example: categoryID
• When used as a FK in another table it will be clear what table it comes from and that it is an id column.

SQL Server 70-461 08-02

Question 13

When creating a foreign key, what must be unique?

Answer 13

• The value you are looking up in the other table with the FK
• In other words if the FK value is 2 there must be only one value of 2 in the table it is looking it up in

SQL Server 70-461 08-02

Question 14

What is a surrogate key?

Answer 14

A column that has a unique value that has no meaning in the form of a numeric data type (such as integer)

SQL Server 70-461 08-02

Question 15

Should you use a natural key or surrogate key as a primary key?

Answer 15

A surrogate key is better in the long run. What would happen if the business decided to change the data in the natural key column and it was the PK column? Much more painful. Use surrogate key as PK and enforce uniqueness of natural key using a unique constraint.

SQL Server 70-461 08-02

Question 16

How do data types affect FK?

Answer 16

The FK must have the same data types as the column(s) it is referencing

SQL Server 70-461 08-02