quote:

---

New to SQL Server so would appreciate some help.

I want a column to default to a unique integer UserID

I've looked at SUSER_NAME()which returns 'ServerName\LoginName'

But apart from being more verbose has the disadvantage that (in our organisation we use a firstname+lastname logins, and therefore) when our females marry their logins often change.

So I then looked at doing a lookup on the returned SUSER_NAME() value on our STAFF table and getting the integer UserId that way. This is fine for 1 or 100 or 1000 records, but I guess there is an overhead to doing this if the system is asked to write to a 'CreatedBy' or a 'ModifiedBy' (or both) column a few million times in a batch update or create process?

So I then looked at SUSER_(ID)which does appear to be returning a unique integer, but it is not clear from the documentation and reference material I have wether in msSQL2008 this number remains unique against that particular login (and does a new number get created if on change of name at marriage the login changes)

Can an SQL guru come to my aid on this and tell me if SUSER_ID() will do the trick or alternatively what is the best way to achieve my goal of using a unique numeric integer Id for each member of staff?

Thanks

SQL2008 and Visual Studio 2008 (VB Preferred)

---

ALTER TRIGGER [dbo].[trStaffModified] 
ON [dbo].[Staff] 
FOR UPDATE
AS 
DECLARE @userId int
SELECT @userId = id FROM Staff WHERE Staff.Login = SUSER_NAME()
IF NOT @userId IS NULL
BEGIN
UPDATE Staff 
SET Staff.MDT = GetUTCDate(),
	 Staff.MBI = @userId
FROM Staff INNER JOIN Inserted ON Staff.Id = Inserted.Id
END