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
OK, enough brain cells are dead.
SQL Fiddle
WITH cte AS
(
SELECT
[ICFilterID],
[ParentID],
[FilterDesc],
[Active],
CAST(0 AS varbinary(max)) AS Level
FROM [dbo].[ICFilters]
WHERE [ParentID] = 0
UNION ALL
SELECT
i.[ICFilterID],
i.[ParentID],
i.[FilterDesc],
i.[Active],
Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
FROM [dbo].[ICFilters] i
INNER JOIN cte c
ON c.[ICFilterID] = i.[ParentID]
)
SELECT
[ICFilterID],
[ParentID],
[FilterDesc],
[Active]
FROM cte
ORDER BY [Level];
Best Answer
It is not possible to convert the TimeStamp colunm into a Date & Time format. Microsoft has renamed the TimeStamp data type to RowVersion to avoid the confusion.
The timestamp/rowversion data type is really just a Database Transaction ID, and it can also be stored in a column of the Binary(8) data type.
But here is a work-around to implement the ModifiedAt, UpdatedDate column type. This will translate (or "convert") a RowVersion column into a SmallDateTime datatype, that can be formatted according to your needs.
But you need to do a bit of work;
1. Create a table "UpdateTimeStamp" with three columns
(CreatedDate Smalldatetime, NewRowVersion, OldRowVersion) like this:
Insert the first row manually
2. Create a SQL Agent job
Create a standard SQL Agent job which inserts one row in the table every one minute.
Make step 1 run this code:
Set the schedule to run every 1 minute.
3. Query the data
You need to write your query with a join to the UpdateTimeStamp table to your table with a between join clause like this:
Caveat, buts...
1) Microsoft SQL Server is not very good at "date range join" clauses, so if you do not need minute resolution, but 10 minute accuracy is acceptable, then you can improve the query speed a bit with lowering the frequency of the SQL Agent job to 10 minute intervals.
2) This will only work for time periods where the SQL Agent job has been running, as it is not easy to create the dbo.UpdateTimeStamp table retrospectively. Unless you have an old table with a RowVersion/TimeStamp datatype, and this table also never sees updates, and the table has a CreatedDate column. And the table has to be in the same database on the same server.
Bonus features
The RowVersion column was already added to many tables already in our Data Warehouse, because I use it as a watermark for loading data into our Data Marts. So now I have the this feature as a debug option, when I need to investigate when things went wrong in our facts and dimensions. Furthermore I do not need a trigger, which may have performance issues and/or stability issues, if not coded correctly.