此处将为大家介绍关于sqlserver改变自增Id索引值的详细内容，并且为您解答有关sqlserver修改自增id值的相关问题，此外，我们还将为您介绍关于C#简单的学籍管理系统(数据库变更由原来的SQ
此处将为大家介绍关于sqlserver改变自增Id索引值的详细内容，并且为您解答有关sqlserver修改自增id值的相关问题，此外，我们还将为您介绍关于C#简单的学籍管理系统(数据库变更由原来的SQLserver改为SqLite)、MySQL使用自增ID索引和UUID索引的对比、SQL SERVER 从其它数据库中复制带自增ID主键的表数据、SQL Server 的 主键 解决方案 NEWID() ， 自增ID的有用信息。
本文目录一览：- sqlserver改变自增Id索引值（sqlserver修改自增id值）
- C#简单的学籍管理系统(数据库变更由原来的SQLserver改为SqLite)
- MySQL使用自增ID索引和UUID索引的对比
- SQL SERVER 从其它数据库中复制带自增ID主键的表数据
- SQL Server 的 主键 解决方案 NEWID() ， 自增ID
sqlserver改变自增Id索引值（sqlserver修改自增id值）
DBCC CHECKIDENT ( '表名',RESEED,0 )
C#简单的学籍管理系统(数据库变更由原来的SQLserver改为SqLite)
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace 简单的学籍管理系统.App_Code
{
public class SqLiteManager//数据库管理类
{
private SqLiteManager()
{
CreateDataBase();
}
private static SqLiteManager instance;
private SQLiteConnection m_dbConnection;
public static SqLiteManager GetInstance
{
get
{
if (instance == null)
{
instance = new SqLiteManager();
}
return instance;
}
}
private void CreateTable(string tableName, List<KeyValuePair<string, Type>> members)
{
ConnectToDataBase();
string sql = "create table " + tableName + " ";
sql += "(";
for (int i = 0; i < members.Count; i++)
{
sql += members[i].Key + " ";
if (members[i].Value == typeof(string))
{
sql += "VARCHAR(200)";
}
else if (members[i].Value == typeof(int))
{
sql += "INTERGER";
}
else if (members[i].Value == typeof(long))
{
sql += "BIGINT";
}
else if (members[i].Value == typeof(decimal))
{
sql += "DECIMAL";
}
else if (members[i].Value == typeof(DateTime))
{
sql += "DATETIME";
}
else
{
sql += "VARVHAR(200)";
}
if (i + 1 != members.Count)
sql += " ,";
}
sql += ")";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
CloseDataBase();
}
private void FillTabel(string tableName, List<KeyValuePair<string, object>> members)
{
ConnectToDataBase();
string sql = "insert into " + tableName + " (";
string values = "values (";
for (int i = 0; i < members.Count; i++)
{
sql += members[i].Key;
Type type = members[i].Value.GetType();
if (type == typeof(string))
{
values += "''" + members[i].Value + "''";
}
else if (type == typeof(int))
{
values += (int)members[i].Value;
}
else if (type == typeof(long))
{
values += (long)members[i].Value;
}
else if (type == typeof(decimal))
{
values += (decimal)members[i].Value;
}
else if (type == typeof(DateTime))
{
values += "''" + ((DateTime)members[i].Value).ToString("yyyy-MM-dd") + "''";
}
else
{
values += "''" + members[i].Value + "''";
}
if (i + 1 != members.Count)
{
sql += " ,";
values += " ,";
}
}
values += ")";
sql += ") ";
sql += values;
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
CloseDataBase();
}
private void DelTabel(string tableName, List<KeyValuePair<string, object>> members)
{
ConnectToDataBase();
string sql = "delete from " + tableName + " where";
string values = "(";
for (int i = 0; i < members.Count; i++)
{
Type type = members[i].Value.GetType();
if (type == typeof(string))
{
values += members[i].Key + "=" + "''" + members[i].Value + "''";
}
else if (type == typeof(int))
{
values += members[i].Key + "=" + (int)members[i].Value;
}
else if (type == typeof(long))
{
values += members[i].Key + "=" + (long)members[i].Value;
}
else if (type == typeof(decimal))
{
values += members[i].Key + "=" + (decimal)members[i].Value;
}
else if (type == typeof(DateTime))
{
values += members[i].Key + "=" + "''" + ((DateTime)members[i].Value).ToString("yyyy-MM-dd") + "''";
}
else
{
values += members[i].Key + "=" + "''" + members[i].Value + "''";
}
if (i + 1 != members.Count)
{
values += " and";
}
}
values += ")";
sql += values;
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
CloseDataBase();
}
private void UpdateTabel(string tableName, List<KeyValuePair<string, object>> members, string key, object value)
{
ConnectToDataBase();
string sql = "update " + tableName + " set";
string values = "(";
for (int i = 0; i < members.Count; i++)
{
Type type = members[i].Value.GetType();
if (type == typeof(string))
{
values += members[i].Key + "=" + "''" + members[i].Value + "''";
}
else if (type == typeof(int))
{
values += members[i].Key + "=" + (int)members[i].Value;
}
else if (type == typeof(long))
{
values += members[i].Key + "=" + (long)members[i].Value;
}
else if (type == typeof(decimal))
{
values += members[i].Key + "=" + (decimal)members[i].Value;
}
else if (type == typeof(DateTime))
{
values += members[i].Key + "=" + "''" + ((DateTime)members[i].Value).ToString("yyyy-MM-dd") + "''";
}
else
{
values += members[i].Key + "=" + "''" + members[i].Value + "''";
}
if (i + 1 != members.Count)
{
values += " and";
}
}
values += ")";
sql += values;
if (!string.IsNullOrEmpty(key))
{
string valuestr = "";
if (value.GetType() == typeof(string))
{
valuestr = "''" + value.ToString() + "''";
}
else if (value.GetType() == typeof(int))
{
valuestr = value.ToString();
}
else if (value.GetType() == typeof(long))
{
valuestr = value.ToString();
}
else if (value.GetType() == typeof(decimal))
{
valuestr = value.ToString();
}
else if (value.GetType() == typeof(DateTime))
{
valuestr = "''" + ((DateTime)value).ToString("yyyy-MM-dd") + "''";
}
else
{
valuestr = "''" + value.ToString() + "''";
}
sql += "where " + key + "=" + valuestr;
}
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
CloseDataBase();
}
private void CreateDataBase()
{
try
{
ConnectToDataBase();
CloseDataBase();
}
catch
{
SQLiteConnection.CreateFile("MyDatabase.sqlite");
}
}
private void ConnectToDataBase()
{
if (m_dbConnection == null)
m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();
}
private void CloseDataBase()
{
if (m_dbConnection == null)
m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Close();
}
public void createAllTables()
{
/*创建用户表*/
List<KeyValuePair<string, Type>> members = new List<KeyValuePair<string, Type>>();
members.Add(new KeyValuePair<string, Type>("UserName", typeof(string)));
members.Add(new KeyValuePair<string, Type>("PassWord", typeof(string)));
members.Add(new KeyValuePair<string, Type>("NickName", typeof(string)));
members.Add(new KeyValuePair<string, Type>("Sex", typeof(int)));
members.Add(new KeyValuePair<string, Type>("Birthday", typeof(DateTime)));
members.Add(new KeyValuePair<string, Type>("Nation", typeof(string)));
members.Add(new KeyValuePair<string, Type>("Area", typeof(string)));
CreateTable("Users", members);
/*创建民族表*/
members.Clear();
members.Add(new KeyValuePair<string, Type>("NationCode", typeof(string)));
members.Add(new KeyValuePair<string, Type>("NationName", typeof(string)));
CreateTable("Nation", members);
/*创建区域表*/
members.Clear();
members.Add(new KeyValuePair<string, Type>("AreaCode", typeof(string)));
members.Add(new KeyValuePair<string, Type>("AreaName", typeof(string)));
members.Add(new KeyValuePair<string, Type>("ParentAreaCode", typeof(string)));
CreateTable("Chinastates", members);
/*创建uspd表*/
members.Clear();
members.Add(new KeyValuePair<string, Type>("Name", typeof(string)));
members.Add(new KeyValuePair<string, Type>("Pwd", typeof(string)));
members.Add(new KeyValuePair<string, Type>("Lastlogon", typeof(int)));
CreateTable("uspd", members);
/*创建jl表*/
members.Clear();
members.Add(new KeyValuePair<string, Type>("Jmima", typeof(int)));
members.Add(new KeyValuePair<string, Type>("Jdenglu", typeof(int)));
CreateTable("jl", members);
}
public List<object> SelectAllData(Type type)
{
List<object> resultList = new List<object>();
string sql = "select *from ";
if (type == typeof(Users))
{
ConnectToDataBase();
sql += "Users";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
var dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Users u = new Users()
{
UserName = dr["UserName"].ToString(),
PassWord = dr["PassWord"].ToString(),
NickName = dr["NickName"].ToString(),
Sex = Convert.ToBoolean(dr["Sex"]),
Birthday = Convert.ToDateTime(dr["Birthday"].ToString()),
Nation = dr["Nation"].ToString(),
Area = dr["Area"].ToString()
};
resultList.Add(u);
}
}
CloseDataBase();
}
else if (type == typeof(Nation))
{
ConnectToDataBase();
sql += "Nation";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
var dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Nation n = new Nation()
{
NationCode = dr[0].ToString(),
NationName = dr[1].ToString()
};
resultList.Add(n);
}
}
CloseDataBase();
}
else if (type == typeof(Chinastates))
{
ConnectToDataBase();
sql += "Chinastates";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
var dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Chinastates c = new Chinastates()
{
AreaCode = dr[0].ToString(),
AreaName = dr[1].ToString(),
ParentAreaCode = dr[2].ToString()
};
resultList.Add(c);
}
}
CloseDataBase();
}
else if (type == typeof(uspd))
{
ConnectToDataBase();
sql += "uspd";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
var dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
uspd u = new uspd()
{
Name = dr[0].ToString(),
Pwd = dr[1].ToString(),
Lastlogon = int.Parse(dr[2].ToString()),
};
resultList.Add(u);
}
}
CloseDataBase();
}
else if (type == typeof(jl))
{
ConnectToDataBase();
sql += "jl";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
var dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
jl u = new jl
{
Jmima = (int)dr[0] == 1 ? true : false,
Jdenglu = (int)dr[0] == 1 ? true : false
};
resultList.Add(u);
}
}
CloseDataBase();
}
return resultList;
}
public bool AddData(Type type, object data)
{
bool ok = false;
List<KeyValuePair<string, object>> members = new List<KeyValuePair<string, object>>();
try
{
if (type == typeof(Users))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("UserName", ((Users)data).UserName));
members.Add(new KeyValuePair<string, object>("PassWord", ((Users)data).PassWord));
members.Add(new KeyValuePair<string, object>("NickName", ((Users)data).NickName));
members.Add(new KeyValuePair<string, object>("Sex", ((Users)data).Sex));
members.Add(new KeyValuePair<string, object>("Birthday", ((Users)data).Birthday));
members.Add(new KeyValuePair<string, object>("Nation", ((Users)data).Nation));
members.Add(new KeyValuePair<string, object>("Area", ((Users)data).Area));
FillTabel("Users", members);
}
else if (type == typeof(Nation))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("NationCode", ((Nation)data).NationCode));
members.Add(new KeyValuePair<string, object>("NationName", ((Nation)data).NationName));
FillTabel("Nation", members);
}
else if (type == typeof(Chinastates))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("AreaCode", ((Chinastates)data).AreaCode));
members.Add(new KeyValuePair<string, object>("AreaName", ((Chinastates)data).AreaName));
members.Add(new KeyValuePair<string, object>("ParentAreaCode", ((Chinastates)data).ParentAreaCode));
FillTabel("Chinastates", members);
}
else if (type == typeof(uspd))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("Name", ((uspd)data).Name));
members.Add(new KeyValuePair<string, object>("Pwd", ((uspd)data).Pwd));
members.Add(new KeyValuePair<string, object>("Lastlogon", ((uspd)data).Lastlogon));
FillTabel("uspd", members);
}
else if (type == typeof(jl))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("UserName", ((jl)data).Jdenglu));
members.Add(new KeyValuePair<string, object>("PassWord", ((jl)data).Jmima));
FillTabel("jl", members);
}
ok = true;
}
catch
{
}
return ok;
}
public bool DelData(Type type, object data)
{
bool ok = false;
List<KeyValuePair<string, object>> members = new List<KeyValuePair<string, object>>();
try
{
if (type == typeof(Users))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("UserName", ((Users)data).UserName));
members.Add(new KeyValuePair<string, object>("PassWord", ((Users)data).PassWord));
members.Add(new KeyValuePair<string, object>("NickName", ((Users)data).NickName));
members.Add(new KeyValuePair<string, object>("Sex", ((Users)data).Sex));
members.Add(new KeyValuePair<string, object>("Birthday", ((Users)data).Birthday));
members.Add(new KeyValuePair<string, object>("Nation", ((Users)data).Nation));
members.Add(new KeyValuePair<string, object>("Area", ((Users)data).Area));
DelTabel("Users", members);
}
else if (type == typeof(Nation))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("NationCode", ((Nation)data).NationCode));
members.Add(new KeyValuePair<string, object>("NationName", ((Nation)data).NationName));
DelTabel("Nation", members);
}
else if (type == typeof(Chinastates))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("AreaCode", ((Chinastates)data).AreaCode));
members.Add(new KeyValuePair<string, object>("AreaName", ((Chinastates)data).AreaName));
members.Add(new KeyValuePair<string, object>("ParentAreaCode", ((Chinastates)data).ParentAreaCode));
DelTabel("Chinastates", members);
}
else if (type == typeof(uspd))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("Name", ((uspd)data).Name));
members.Add(new KeyValuePair<string, object>("Pwd", ((uspd)data).Pwd));
members.Add(new KeyValuePair<string, object>("Lastlogon", ((uspd)data).Lastlogon));
DelTabel("uspd", members);
}
else if (type == typeof(jl))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("UserName", ((jl)data).Jdenglu));
members.Add(new KeyValuePair<string, object>("PassWord", ((jl)data).Jmima));
DelTabel("jl", members);
}
ok = true;
}
catch
{
}
return ok;
}
public bool UpdateData(Type type, string name, object value, object data)
{
bool ok = false;
List<KeyValuePair<string, object>> members = new List<KeyValuePair<string, object>>();
try
{
if (type == typeof(Users))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("UserName", ((Users)data).UserName));
members.Add(new KeyValuePair<string, object>("PassWord", ((Users)data).PassWord));
members.Add(new KeyValuePair<string, object>("NickName", ((Users)data).NickName));
members.Add(new KeyValuePair<string, object>("Sex", ((Users)data).Sex));
members.Add(new KeyValuePair<string, object>("Birthday", ((Users)data).Birthday));
members.Add(new KeyValuePair<string, object>("Nation", ((Users)data).Nation));
members.Add(new KeyValuePair<string, object>("Area", ((Users)data).Area));
UpdateTabel("Users", members, name, value);
}
else if (type == typeof(Nation))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("NationCode", ((Nation)data).NationCode));
members.Add(new KeyValuePair<string, object>("NationName", ((Nation)data).NationName));
UpdateTabel("Nation", members, name, value);
}
else if (type == typeof(Chinastates))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("AreaCode", ((Chinastates)data).AreaCode));
members.Add(new KeyValuePair<string, object>("AreaName", ((Chinastates)data).AreaName));
members.Add(new KeyValuePair<string, object>("ParentAreaCode", ((Chinastates)data).ParentAreaCode));
UpdateTabel("Chinastates", members, name, value);
}
else if (type == typeof(uspd))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("Name", ((uspd)data).Name));
members.Add(new KeyValuePair<string, object>("Pwd", ((uspd)data).Pwd));
members.Add(new KeyValuePair<string, object>("Lastlogon", ((uspd)data).Lastlogon));
UpdateTabel("uspd", members, name, value);
}
else if (type == typeof(jl))
{
members.Clear();
members.Add(new KeyValuePair<string, object>("UserName", ((jl)data).Jdenglu));
members.Add(new KeyValuePair<string, object>("PassWord", ((jl)data).Jmima));
UpdateTabel("jl", members, name, value);
}
ok = true;
}
catch
{
}
return ok;
}
}
}
MySQL使用自增ID索引和UUID索引的对比
数据库自增主键ID
自增的主键的值是顺序的，所以存储引擎（MyISAM、InnoDB）把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16，会留出1/16的空间留作以后的修改)
优点：
- 下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费；
- 新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗；
- 减少了页分裂和碎片的产生。
缺点：
- 因为自增ID步进值是固定的，如果别人爬取数据库，会获取到业务增长信息，从而分析出经营情况；
- 如果在并发情况下写入，自增主键的上界会造成明显的锁挣用，并发插入会导致间隙锁竞争；
- Auto_Increment锁机制会造成自增锁的抢夺，有一定的性能损失。
UUID的索引
- 数据生成没有规律，无法做到按顺序存储；
- 写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，在插入之前存储引擎不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO；
- 因为写入是乱序的，存储引擎不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上；
- 由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片
SQL SERVER 从其它数据库中复制带自增ID主键的表数据
SQL SERVER两个结构相同（或不同）的表，互相导入数据，方法有两种：
1、使用SQL SERVER 自带的导出、导入功能，在库名上右击，“任务”，导出数据、导入数据，这个操作具体不就不多讲了。
2、使用语句直接复制，从XXX表复制数据到YYY表
步骤1、首先，需要设置下插入允许自增ID。
SET IDENTITY_INSERT 表名YYY ON
如果提示 表 ''XXX'' 的 IDENTITY_INSERT 已经为 ON。无法为表 ''YYY'' 执行 SET 操作。
那么需要先将XXX表设置为OFF，再执行上面的代码将YYY设置为ON。
SET IDENTITY_INSERT 表名XXX OFF
步骤2、清空原表数据
如果原表主键ID不会重复，或者你的原表数据需要保留，那么这一步可以不需要，只要保证不重复即可。
truncate table 表YYY
步骤3、复制数据
本地表复制
INSERT INTO 表YYY (
[Id] ,
[字段1],
[字段2])
SELECT
[Id] ,
[字段1],
[字段2]
FROM 表XXX
跨机器跨网络复制：
INSERT INTO 表YYY (
[Id] ,
[字段1],
[字段2])
SELECT
[Id] ,
[字段1],
[字段2]
FROM opendatasource( ''SQLOLEDB'',''Data Source=172.0.0.2;User ID=user;Password=pass'').数据库.表XXX
需要注意的是，必须要两边都指定具体的字段名称，不能用*号，否则可能会提示以下错误：
仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表''YYY''中的标识
或者会因为字段不对应而提示一些奇怪的错误，例如：
在将 varchar 值 ''AAAA'' 转换成数据类型 int 时失败
SQL Server 的 主键 解决方案 NEWID() ， 自增ID
SQL Server 的 主键 解决方案 NEWID() ， 自增ID
参考文章：
（1）SQL Server 的 主键 解决方案 NEWID() ， 自增ID
（2）https://www.cnblogs.com/wangzhanbo/p/8807125.html
备忘一下。
我们今天的关于sqlserver改变自增Id索引值和sqlserver修改自增id值的分享就到这里，谢谢您的阅读，如果想了解更多关于C#简单的学籍管理系统(数据库变更由原来的SQLserver改为SqLite)、MySQL使用自增ID索引和UUID索引的对比、SQL SERVER 从其它数据库中复制带自增ID主键的表数据、SQL Server 的 主键 解决方案 NEWID() ， 自增ID的相关信息，可以在本站进行搜索。
本文标签：
