auto increment in sql

What You can do is Concat Id column with Name

select cast(id  as Varchar(30))+ Name as Name1 from Test5 

while retrieving

Hello,

You can do this by either creating a trigger during insert, or modify the default value for your identity column. Take this as an example.

CREATE FUNCTION CompanyBasedId (@id int, @company varchar(6))
RETURNS varchar(10)
AS
BEGIN
RETURN @company + Right('0000' + Convert(varchar(10), @id), 4)
END


ALTER TABLE YOURTABLENAME ADD CustomId as dbo.CompanyBasedId(id, company)

The script above should have a table structure like this one.

YOURTABLENAME
id (autoincrement int column)
company (company code varchar(6))
customId (your customized autoincrement column thats based on id and company column)


Regards.

Do not concatenate separate data into a single field. If you must have company name + autoincrement information, use separate columns. For example:

- CustomerName varchar(...)
- Id int  IDENTITY(1,1)

Now if you want to combine these, you can do it in a query:

SELECT CustomerName + CONVERT(varchar, id) ...

Or if you like, you can create a view that does this operation so the querying would be easier.

And one option is to use computed colummns [^].