MSSQL table sharding query (01)

In the MSSQL Stored Procedure, table sharding is implemented to store table sharding queries and improve query efficiency.

Table sharding and table sharding for some fixed data can greatly improve the query performance. This is already accepted. The following describes how to implement it in the storage process.

If you want to query the region of a mobile phone number, the database of the mobile phone number is saved to the 10 tables of M0 to M9 respectively. Note: only the first seven digits of the mobile phone number are saved in the table, the first seven digits can be used to determine the region!

Alter proc [DBO]. [p-searchmobile]

@ Mobile bigint,

@ Type int output,

@ Province varchar (6) output,

@ City varchar (24) output,

@ Info varchar (30) Output

As

Declare @ SQL nvarchar (200)

Declare @ number int

Set @ number = cast (left (@ mobile, 7) as INT) -- retrieve the first seven digits

-- Note that table store uses the 7th-bit number of the mobile phone number to determine which table exists.

Set @ SQL = 'select top 1 @ type = [type], @ city = city, @ province = province, @ info = info

From [m' + Cast (@ Number % 10 as varchar (10) + ']

Where number = '+ Cast (@ number as varchar (10 ))

 

Exec sp_executesql @ SQL, n' @ Type int output,

@ City varchar (24) output,

@ Province varchar (6) output,

@ Info varchar (30) output', -- the four output variables here replace the corresponding variables in the @ SQL string

-- The following four values are used to receive the values of four variables.

@ Type output,

@ City output,

@ Province output,

@ Info output