# Best Practice for Using `AUTO_INCREMENT`
+ Attribute **`AUTO_RANDOM`** requires **`BIGINT`** data type
+ Use **`AUTO_INCREMENT`** attribute, **`SHARD_ROW_ID_BITS`** and **non-clustered** index together
  + The value of **`PRE_SPLIT_REGIONS`** must be less than or equal to that of **`SHARD_ROW_ID_BITS`** or **`AUTO_RANDOM`**.
```7
tidb> CREATE TABLE clustered_order (
  ~!@id INT PRIMARY KEY AUTO_RANDOM@!~, 
  code VARCHAR(30) NOT NULL,
  order_no VARCHAR(200) NOT NULL DEFAULT '',
  status INT NOT NULL,
  cancel_flag INT DEFAULT NULL,
  create_user VARCHAR(50) DEFAULT NULL,
  update_user VARCHAR(50) DEFAULT NULL,
  create_time DATETIME DEFAULT NULL,
  update_time DATETIME DEFAULT NULL
  );
~!@ERROR 8216 (HY000): Invalid auto random: auto_random option must be@!~ 
~!@defined on `bigint` column, but not on `int` column@!~

tidb> CREATE TABLE clustered_order (
  ~!@id BIGINT PRIMARY KEY AUTO_RANDOM@!~, 
  code VARCHAR(30) NOT NULL,
  order_no VARCHAR(200) NOT NULL DEFAULT '',
  status INT NOT NULL,
  cancel_flag INT DEFAULT NULL,
  create_user VARCHAR(50) DEFAULT NULL,
  update_user VARCHAR(50) DEFAULT NULL,
  create_time DATETIME DEFAULT NULL,
  update_time DATETIME DEFAULT NULL
  );
Query OK, 0 rows affected, 1 warning (0.63 sec)
```
```7
tidb> CREATE TABLE clustered_order (
  ~!@id INT AUTO_INCREMENT PRIMARY KEY@!~, 
  code VARCHAR(30) NOT NULL,
  order_no VARCHAR(200) NOT NULL DEFAULT '',
  status INT NOT NULL,
  cancel_flag INT DEFAULT NULL,
  create_user VARCHAR(50) DEFAULT NULL,
  update_user VARCHAR(50) DEFAULT NULL,
  create_time DATETIME DEFAULT NULL,
  update_time DATETIME DEFAULT NULL
  ) ~!@SHARD_ROW_ID_BITS=3 PRE_SPLIT_REGIONS=3@!~;
~!@ERROR 8200 (HY000): Unsupported shard_row_id_bits@!~
~!@for table with primary key as row id@!~

tidb> CREATE TABLE nonclustered_order (
  ~!@id INT AUTO_INCREMENT PRIMARY KEY NONCLUSTERED@!~, 
  code VARCHAR(30) NOT NULL,
  order_no VARCHAR(200) NOT NULL DEFAULT '',
  status INT NOT NULL,
  cancel_flag INT DEFAULT NULL,
  create_user VARCHAR(50) DEFAULT NULL,
  update_user VARCHAR(50) DEFAULT NULL,
  create_time DATETIME DEFAULT NULL,
  update_time DATETIME DEFAULT NULL
  ) ~!@SHARD_ROW_ID_BITS=3 PRE_SPLIT_REGIONS=3@!~;
Query OK, 0 rows affected (0.50 sec)
```

------------------------------------------------------------------------------
`AUTO_RANDOM` is useful, however it must be used with `BIGINT` data type together. If your primary key column is not of `BIGINT` data type you cannot leverage `AUTO_RANDOM` to mitigate the insertion hotspot performance issue. Furthermore, if you have to use `AUTO_INCREMENT` for MySQL compatibility reasons, you can create a table with a non-clustered index for the primary key using `NONCLUSTERED` and `SHARD_ROW_ID_BITS`. This will ensure that the row values are assigned evenly distributed keys.

In the SQL command on the slide, `SHARD_ROW_ID_BITS` and `PRE_SPLIT_REGIONS` are two additional properties for the table. 
- `SHARD_ROW_ID_BITS=3` means that 3 bits in `_tidb_rowid` are randomly generated, resulting in 2 to the power of 3 (8) random values (also known as 8 shards). 
- `PRE_SPLIT_REGIONS=8` means that during table creation, 2 to the power of 3 (8) empty regions are pre-allocated. 

As a result, the table has 8 TiKV Regions and the unique primary key values are generated among 8 shards, the hotspot issue is mitigated similar to the `AUTO_RANDOM` case.