SQL Server Series: physical database mode design

1. Naming Conventions are often used for database development.

1> use a singular table name that does not contain numbers. You can add a module name prefix if necessary.

2> for primary key naming, the table name + id is used.

3> the names of the columns that serve as foreign keys should be the same as the names of their corresponding primary keys, unless the foreign keys maintain a self-inverse/recursive relationship.

4> avoid abbreviations.

2. Primary Key design principles

1> Primary Key Usage: uniquely identifies a row as an object that can be effectively referenced by an external key.

2> Primary keys should be meaningless to users.

3> Primary keys should be single columns to improve the efficiency of connection and filter operations. The composite primary key is only used to describe multiple-to-multiple links. Two foreign keys are used as the primary key.

4> never update the primary key.

5> Primary keys should not contain dynamically changed data, such as timestamps, creation time columns, and modification time columns.

6> the primary key should be automatically generated by the computer.

7> Primary keys are implemented through unique constraints (non-empty constraints) and clustered or non-clustered unique indexes. The primary key should be non-practical, single-column, computer-generated, non-editable, and invisible to users.

2.1 create a primary key

Creating indexes using clustered indexes in table structure design is not the best because each table can only create one clustered index, this will lead to the loss of the opportunity to create other more effective clustered indexes for each table.

Create a non-clustered index primary key using an SQL statement:

Create TableDBO. Product (productidInt Identity(1,1)Primary Key Nonclustered, ProductnameVarchar(100)Null)
 

2.2 primary key use ID column

Advantages of the logo column: it is easy to recognize manually and occupies a small amount of space and is fast.

Set the ID column to the primary key using the clustered index (this is a common but not good method). When a single user retrieves a row of records, the speed is extremely fast. However, such a design method will form a hot spot of lock contention in the database.

2.3 use guid for primary keys

A guid is a hexadecimal number of 16 bytes. duplicate numbers are not displayed. Compared with the ID column, guid can ensure uniqueness in a larger range.

Guid advantage: you do not need to perform a comprehensive check when copying a database that uses the guid primary key. the randomness of the guid value can reduce the hotspot of the database. You can use multiple methods to generate the guid value.

Create a guid primary key table using an SQL statement:

 
Create TableDBO. Product (productidUniqueidentifier Default(Newid())Primary Key Nonclustered, ProductnameVarchar(100)Null)
 

3. Foreign key design principles

Foreign keys can reference primary keys, unique constraints, or columns of tables other than temporary tables.

4. Index

SQL Server uses two basic types of indexes: clustered index and non-clustered index. Generally, the primary key, foreign key, and order by columns are used to create an index.

Index design principles:

The larger the number of indexes, the better. If a table has a large number of indexes, it not only occupies a large amount of disk space, but also affects the performance of insert, delete, update, and other statements. When the table data is updated, the index is also adjusted and updated.

◊ Avoid too many indexes on frequently updated tables, and minimize the number of indexed columns. You should create indexes for fields that are frequently used for queries, but do not add unnecessary fields.

It is recommended that you do not use indexes for tables with small data volumes. Because there is little data, the query time may be shorter than the index Traversal Time, And the index may not produce optimization results.

Indexes are usually used in conditional expressions to create indexes for columns with different values. Do not create indexes for columns with fewer values. For example, the [gender] field only has two different values: [male] and [Female], so no index is required. If an index is created, it will not improve the efficiency, but will seriously reduce the update speed.

When uniqueness is a feature of a data, it specifies a unique index. Using a unique index can ensure the data integrity of the defined columns and improve the query speed.

Sort creates an index on columns that are frequently sorted or grouped (order by or group by). If there are multiple columns to be sorted, you can create a composite index on these columns.

4.1 Non-clustered Index

Create a non-clustered index SQL statement:

Create Nonclustered IndexIx_productnoOnDBO. Product (productno)
 

4.2 clustered Index

Clustered indexes store record rows and index key values in the same physical order. Since data can only have one physical storage order, each table can have only one clustered index. Retrieving data through clustered indexes requires less logical reads. Any non-clustered index points to the ID of the clustered index instead of the data page.

Clustered indexes centralize record rows with the same index key value into as few data pages as possible, thus reducing the number of data pages to be read when retrieving a group of records. Therefore, it is a good choice to create clustered indexes for columns that often serve as conditions to query a group of records, such as foreign keys such as orderdetail. orderid. For the same reason, using clustered indexes for queries that return only one row of records does not significantly improve performance.

 

Common Mistakes in clustered indexes include:

1> clustered indexes reduce the insert operation speed, because half of the data must be moved backward to free up space for the inserted rows. This recognition is incorrect because the fill factor can be used to control the percentage of the fill to reserve space for the newly inserted data on the index page. If the index page is full, SQL Server splits the page. In this case, only the first page is affected.

2> it is a good design method to create a clustered index on the primary key of the identified column, which can achieve the fastest operation speed on the table. This recognition is incorrect. It wastes the opportunity to create other more effective clustered indexes. In addition, this method is used to store each newly inserted record row to the same data page at the end of the table, which leads to database hotspot and lock contention. Although row locks can partially alleviate this hot issue, based on the number of rows to be locked, the number of rows on the page, and the number of users, the lock manager may upgrade the row lock to a page lock. In this case, a hot spot occurs.

3> clustered indexes are magical. If the query speed is not fast enough, you can create a clustered index on the column for a long time, and the operation speed of the table will be improved. This is wrong. Clustered indexes are only a little faster than non-clustered indexes. Because only one clustered index can be created on each table, indexing is also a valuable performance resource. Clustered indexes should be created only on columns that frequently query a group of record rows as conditions.

Create a clustered index using SQL statements:

 
Create Clustered IndexIx_orderidOnDBO. orderdetail (orderid)
 

Delete An index using an SQL statement:

 
Drop IndexOrderdetail. ix_orderid
 

4.3 Composite Index

A composite index is a clustered or non-clustered index with multiple index columns. Because the composite index contains multiple index columns, you can use the create index DDL command to create a table only after creating the table.

Create a composite index using SQL statements:

Create Nonclustered IndexIx_productnoproductnameOnDBO. Product (productno, productname)
 

In composite indexes, the order of index sorting is very important. Because only such queries can use composite indexes: the index columns contained in the query conditions are arranged from the first index column to the right in the composite index order. In the composite index of the preceding example, the order of the index columns is productno and productname. This index is not used for queries that only use productname as the condition, this index can be used only for queries with productno, productno, and productname conditions.

4.4 index options

1> unique index

The SQL Server Index has several options, including uniqueness, space allocation, and performance options.

The unique index option is not just an index with unique constraints. You can optimize the unique index. When you create a primary key or unique constraint, the system automatically creates a unique index for them.

 
Create unique index ix_orderno on DBO. Order. orderno
 

2> fillfactor and pad_index)

Indexes need to keep a little space on the index tree, so that indexes do not need to be reconstructed when new records are inserted. The fill factor specifies the percentage of data space filled in the index.

The fill factor should be set to the maximum value, depending on the purpose of the database. If the database is mainly used for data retrieval, set the fill factor to a large value to fill up the space of an index page as much as possible. If you need to insert a large number of tables, you can retain some space on the index page to provide the performance of the update operation. If the number of table insert operations changes significantly, set the fill factor to an intermediate value and select the fill Index option.

 
Create Unique IndexIx_ordernoOnDBO.Order. OrdernoWith Fillfactor=85Pad_index
 

3> index sorting options (ASC | DESC)

Although SQL server can create a descending index, it is not recommended to change the default ascending index order. This does not improve performance, but may cause some confusion in the future. Unless the DESC option is explicitly used in the order by clause, all queries using the order by clause will be sorted in ascending order.

To create an index in ascending or descending order, use the ASC or DESC option after the column name in the create index DDL command.

Create Unique IndexIx_ordernoOnDBO.Order. OrdernoDesc With Fillfactor=85Pad_index
 

4> ignore duplicate key option (ignore_dup_key)

The ignore duplicate key option does not apply to the index. It specifies the way the index will be processed for future data modification operations.

Generally, transactions are atomic, which means that the operations in the entire transaction are either all successful or all failed as a logical unit. If the ignore duplicate key option is set, the insert transaction is allowed to commit only the record rows received by the unique index, and ignore the unique record rows that violate the unique index.

This option does not damage the unique index. Duplicate values are still not inserted in the table, so Database Consistency is intact, but the atomicity of transactions is violated.

This option can be used to easily import a large number of problematic record rows to the database, but the acid attribute of the database is weakened.

  Create   unique   index  ix_orderno  On  DBO.  order . orderno  with  ignore_dup_key