Timestamp timestamp in SQL Server with uniqueidentifier data type

TimeStamp

The SQL Server timestamp data type is independent of time and date. SQL Server Timestamp is a binary number that indicates the relative order in which data modifications occur in the database. The implementation of the timestamp data type was originally intended to support the SQL Server recovery algorithm. Each time the page is modified, it is marked with the current @ @DBTS value and then @ @DBTS plus 1. This is sufficient to help the recovery process determine the relative order of page modifications, but the timestamp value has no relation to time.

Each database has a counter that is incremented when an INSERT or update operation is performed on a table that contains timestamp columns in the database. The counter is a database timestamp. This can track the relative time within the database, not the actual time associated with the clock.

Using the timestamp column in a row makes it easy to determine whether any value in the row has changed since the last time it was read. If a change is made to the row, the timestamp value is updated. If no changes are made to the row, the timestamp value is consistent with the timestamp value of the row that was previously read. To return the current timestamp value for the database, use @ @DBTS.

(Transferred from Http://blog.sina.com.cn/s/blog_43eb83b90102e1ae.html)

uniqueidentifier

What is a uniqueidentifier?

Uniqqueidentifier is a globally unique identity


Two how do I assign a value to a column of the uniqueidentifier data type?

1 using the NewID () function to implement
2 converts the constants of a string directly into such a format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Example: 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier data
3 16-bit data directly assigned to 32-bit
Example 0xffffffff00000000ffffffff00000000

three uniqueidentifier How does the data type data actually be stored in the database?

The uniqueidentifier data type stores the actual data as 16 bytes of binary value,
uniqueidentifier can be converted into actual string and binary data types


How does the four NewID () function generate a unique uniqueidentifier value?

The NewID () function is a unique number from the identity number and CPU clock on their network card to generate new uniqueidentifier data, and the GUID is the same as each computer can generate globally unique values so that unique identifiers are generated between multiple computers and multiple networks


five key benefits of using the uniqueidentifier data type

The main advantage of the uniqueidentifier data type is that when generating values using the NEWID function, it is possible to guarantee the global uniqueness of the value, to uniquely identify a single row of records, to be more effective than the identity for Docu (especially for multi-machine, multi-segment database replication)
Secondly, in the case of using identity, we cannot modify the automatically generated value, and the uniqueidentifier data type can be modified at any time.


Six disadvantages of using the uniqueidentifier data type

1 is unordered for the value of the generated uniqueidentifier type, and the information returned is unordered when the relevant data information is displayed normally.
When the identity-identified data is displayed, by default it is displayed in the order in which the records were added. This will require a field that is sorted by default identity for uniqueidentifier, which is the primary key.

2 for the uniqueidentifier field the actual information of the data is 16 bytes, relative to the identity is much larger, relative to the efficiency of storage space and query will be reduced a lot.



Seven in the design of the system database, we make a choice between the uniqueidentifier,identity, and the identifiable record attribute (which has the actual meaning of the information) as the primary key, these three ways

System design with property as the primary key
In the system design process, a single piece of information contains attributes that can represent uniqueness (typically not too much less than 3) and such attributes are required fields. In the record life cycle is generally not changed, the table is generally more than 50 such a level of system with the property as the primary key, this way is best

Example: The student's management information system is based on the student's school number key

The case of a uniqueidentifier column as the primary key
When there is a need for a database replication between multiple networks between multiple databases, we need to differentiate each single record in each unique identity, and use the uniqueidentifier column to generate the primary key without the proper attributes to the master key

Case with identity as the primary key
There is no need for database replication, and the system is relatively small (within 50 tables) can be used to generate a primary key with identity column, suitable for rapid development.

(Transferred from Http://www.cnblogs.com/yuanyuan/archive/2009/09/09/1563438.html)

Timestamp timestamp in SQL Server with uniqueidentifier data type