The identity field in the database does not need to be the unique value generated by the system. Performance Optimization Method (new trick)

However, fields with the identity feature do not need to be unique, not the primary key.

Set identity_insert tablename (on | off) can be used to control whether a value can be specified in the identity field during runtime, rather than automatically inserted by the system.

So, what is the use of this. For example, two users can chat for multiple times, which is represented by a chatsession. In the database, we need three tables: user, chatsession (sessionid identity,...), user_chatsession (userid, sessionid,...) to represent this relationship. Here, there is an additional table chatsession, and its primary key sessionid has the identity feature and is referenced in user_chatsession.

However, we know that database write operations are much more costly than read operations. If I want to optimize and remove the entire chatsession, what should I do.

At this time, I will use this point. First, change the sesssionid in user_chatsession to the identity field. When inserting the first user-session (for user a) record in a session, the system generates the sessionid and then records the sessionid, insert a session to the subsequent user-session (for user B, user c ...), run set identity_insert tablename on to insert the sessionid value of the previous record.

Another method is to use select max (sessionid) + 1 from table where userid = @ useridA to obtain the newly inserted sessionid, the performance of this method is obviously much worse.