Bad practice is bad practice ;(
If you have got table
Customers
without any
identity[
^] field (CustId), you're in trouble. Literally!
So, as soon as possible,
alter table[
^] and add column with identity. Any other solutions are partial.
Temporary, you can use
ROW_NUMBER()[
^], but this can't solve your problem.
SELECT 'Cust' + CONVERT(NVARCHAR(10),ID) AS CustID, [CustFName], [CustLName]
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY [CustLName]) AS ID, [CustFName], [CustLName]
FROM Customers
) AS t1
Another, temporary solution, when you add new customer to your database, is to get the count of customers plus one.
DECLARE @id INT
SELECT @id = COUNT(*) + 1
FROM @cust
SELECT 'Cust' + CONVERT(NVARCHAR(10), @id ) AS LastCustomer
This solution is charged with errors too. Why? Remove one record to become convinced...
Summary
Alter table with identity field!