A very common way to do this is to have an auto-generated column in your table:
CREATE SEQUENCE UserID_Sequence AS int START WITH 1
go
CREATE TABLE Users (UserID int NOT NULL
CONSTRAINT default_User_UserID DEFAULT NEXT VALUE FOR UserID_Sequence,
Username nvarchar(50) NOT NULL,
CONSTRAINT pk_Users PRIMARY KEY (UserID)
)
go
INSERT Users (Username)
OUTPUT inserted.UserID, inserted.UserName
VALUES('Nisse Nilsson'), ('Jacques LeMacque'), ('Flavio Tavio')
go
-- Cleanup
DROP TABLE Users
DROP SEQUENCE UserID_Sequence
Beware that the id may not always be contiguous, but there can be gaps. That should not be an issue in most context.
An similar technique is to use IDENTITY, which is very popular. However, IDENTITY gives you problems if you would need to insert rows with explicit IDs. With sequences you don't have that problem.