Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.
For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.
For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.
Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.
Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.
The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.
Taking all of this into consideration, I would recommend Option A.
I hope this helps,
Matt
The behaviors you're curious about are explained in the documentation:
When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
...
- The identity column is part of an expression.
...
If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property.
And in this thread from UC:
Metadata is determined based on the source column and expressions used
in the SELECT list. Below are the rules:
Any expression that uses a built-in function like SUBSTRING, LEFT, RIGHT etc (except ISNULL) for example is considered as NULLable by the
engine. So if you use CAST(somecol as char(8)) then the expression is NULLable.
...
So to make an expression or column in the SELECT list not null then use ISNULL around the column or expression.
Best Answer
Information turned into a Community Wiki answer from this question on Stack Overflow: What is the data type of a computed column?
It's determined by the value with the highest datatype precedence.
You can use
sql_variant_property
to determine what datatype a literal expression is as per my answer here. e.g.2147483648
is interpreted asnumeric(10,0)
rather thanbigint
.In addition, if you want to force a specific data type instead of relying on data type precedence, you can use
CONVERT
orCAST
in the computation to force it (assuming all potential outcomes are compatible with the type you choose). This can be very useful in cases where, by default, you end up with a wider data type than you intended; the most common use case I've seen is when you end up with an INT instead of, say, a BIT:In this case
Active1
is anINT
(4 bytes) whileActive2
is aBIT
(1 byte - or less, potentially, if it is adjacent to otherBIT
columns).