Sql-server – How to create a relationship to a non-primary key in SQL Server

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

• T1 SPID 93 processec49b8: UPDATE kid SET activityByID=@P0, activityDate=@P1 WHERE kidID=@P2 triggers INSERT INTO ZAT_KID with (PAGLOCK)
• T2 SPID 64 processedb6d8: insert into Image ... triggers INSERT INTO ZAT_Image with (PAGLOCK)

T1 wants X page 295182 (ZAT_KID) and has X page 295211 (ZAT_Image). T2 wants X page 295211 (ZAT_Image) and has X on 295182 (ZAT_KID). This indicates that previous activity of the two spids involved is at play here: T1 had previously locked a page on ZAT_Image, but current stack is not associated with that table. Similarly the T2 had previously locked a page on ZAT_Image but current stack is not associated with it.

The scenario is pretty clear: spurious use of poorly understood hints. T1 has locked the insert point of ZAT_Image while T2 has locked the insert point of ZAT_KID. A deadlock is unavoidable. INSERT WITH (PAGLOCK) is the culprit. You are playing with fire and you got burned. Stop using hints you do not understand. Remove the PAGLOCK hint, let the engine decide the proper strategy. Leave the insert point free in your audit tables free for further inserts. Beat the person that added PAGLOCK to INSERT with a stick.