我们如何在SQL Server中创建身份 [英] How we create the identiy in sql server

问题描述

您好，先生，
我想启动ID为M001，并与M009自动递增，如何在sqlserver中实现；
请给我解决方法.
谢谢先生

arvind kumar singh

Hello Sir ,
I want to start the ID is M001 and is auto increment with M009 how it is possible in sqlserver;
Plz give me solution .
Thanks sir

arvind kumar singh

推荐答案

切勿在单个字段中组合单独的数据.

对于这种情况，请使用两个不同的字段，标识和前缀字段.如果要使用一个字段来显示所提供的数据，则可以创建视图或使用计算的"列，请参见:

Never combine separate data in a single field.

For this case, use two different fields, identity and the prefix field. If you want to have a field that shows the data as you presented, you can create a view or use Computed columns, see: http://msdn.microsoft.com/en-us/library/ms191250.aspx[^].

However if the M is a constant, I wouldn''t put it into the database at all. At max I would include it to the computed column, but I see no reason for that either.

这是不可能的.

SQL Server标识字段只能是数字.如果需要，唯一的解决方案是丢弃"M"部分，并在以后每次使用该字段时将其添加. MSDN [

在SQL Server中自动增加数字的唯一方法是使用身份.唯一的解决方案是，如果需要自动更改的标识字段，则不将True ID存储在表中.

如果您需要具有"Mnnn"的ID，其中"nnn"以1开头并每次递增9，那么我建议在表中使用一个标识字段，其增量为1，并使用一对转换方法:

It isn''t possible.

SQL Server Identity fields can only be numeric. If you want this, then the only solution is to discard the "M" part and add that on later whenever you use the field. MSDN[^]

"sir I know this is not possible through Identity.But how can we do this please give me solution ;
Thanks Sir"

The only way to autoincrement numbers in SQL Server is to use identity. The only solution is to not store the True ID in the table if you need an automatically changing identity field.

If you need to have IDs which are "Mnnn" where "nnn" starts with 1 and increments by 9 each time, then I would suggest use an identity field in the table with an increment of one, and have a pair of translation methods:

private static string ToExternalID(int id)
    {
    return string.Format("M{0:d3}", id * 9);
    }
private static int FromExternalID(string id)
    {
    return int.Parse(id.Substring(1)) / 9;
    }

您可能需要对它们进行一些错误检查，然后才能真正使用它们！

You will probably want to add some error checking to these before you use them for real!

这篇关于我们如何在SQL Server中创建身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！