Shulou(Shulou.com)05/31 Report--

How to write Sqlserver data into the table test, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1. Create tables:

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Table_3

(

Id int NOT NULL

Company_id nvarchar (MAX) NULL

Number nvarchar (MAX) NULL

Code nvarchar (MAX) NULL

Input_tele bigint NULL

Type nvarchar (MAX) NULL

) ON [PRIMARY]

TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE dbo.Table_3 SET (LOCK_ESCALATION = TABLE)

GO

COMMIT

2. Test table insertion

Insert 200000 records into the table:

Set nocount on

Declare @ I int

Set @ I = 0

While @ I

< 200000 begin insert into Table_3 values (@i,'abcd','123','234','345','567') set @i = @i +1 end go 耗时3分11秒。 dbcc showcontig('Table_3') go DBCC SHOWCONTIG 正在扫描 'Table_3' 表... 表: 'Table_3' (370100359)；索引 ID: 0，数据库 ID: 5 已执行 TABLE 级别的扫描。 - 扫描页数................................: 1482 - 扫描区数..............................: 188 - 区切换次数..............................: 187 - 每个区的平均页数........................: 7.9 - 扫描密度 [最佳计数:实际计数].......: 98.94% [186:188] - 区扫描碎片 ..................: 1.60% - 每页的平均可用字节数.....................: 403.7 - 平均页密度(满).....................: 95.01% DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 未加索引表大小： 加索引后表大小： dbcc showcontig('Table_3') go DBCC SHOWCONTIG 正在扫描 'Table_3' 表... 表: 'Table_3' (386100416)；索引 ID: 1，数据库 ID: 5 已执行 TABLE 级别的扫描。 - 扫描页数................................: 1087 - 扫描区数..............................: 136 - 区切换次数..............................: 135 - 每个区的平均页数........................: 8.0 - 扫描密度 [最佳计数:实际计数].......: 100.00% [136:136] - 逻辑扫描碎片 ..................: 0.00% - 区扫描碎片 ..................: 3.68% - 每页的平均可用字节数.....................: 0.3 - 平均页密度(满).....................: 100.00% DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

Clear the table data.

Modify the table structure:

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_Table_3

(

Id int NOT NULL

Company_id varchar (50) NULL

Number varchar (50) NULL

Code varchar (50) NULL

Input_tele bigint NULL

Type varchar (50) NULL

) ON [PRIMARY]

GO

ALTER TABLE dbo.Tmp_Table_3 SET (LOCK_ESCALATION = TABLE)

GO

IF EXISTS (SELECT * FROM dbo.Table_3)

EXEC ('INSERT INTO dbo.Tmp_Table_3 (id, company_id, number, code, input_tele, type)

SELECT id, CONVERT (varchar (50), company_id), CONVERT (varchar (50), number), CONVERT (varchar (50), code), input_tele, CONVERT (varchar (50), type) FROM dbo.Table_3 WITH (HOLDLOCK TABLOCKX)

GO

DROP TABLE dbo.Table_3

GO

EXECUTE sp_rename Numbdbo.Tmpspeak Table3, Noble3, 'OBJECT'

GO

COMMIT

Insert 200000 records into the table:

Set nocount on

Declare @ I int

Set @ I = 0

While @ I < 200000

Begin

Insert into Table_3 values (@ iMagneur ABCDM 123pr 234pr 345pr 567')

Set @ I = @ I + 1

End

Go

It took 3 minutes and 07 seconds.

Dbcc showcontig ('Table_3')

Go

DBCC SHOWCONTIG is scanning the 'Table_3' table.

Table: 'Table_3' (386100416); index ID: 0, database ID: 5

Performed a TABLE level scan.

-number of scanned pages.: 1143

-number of scan areas.: 145

-number of zone switches.: 144

-average number of pages per district.: 7.9

-scan density [Best count: actual count].: 98.62% [143VR 145]

-Zone scan fragments.: 2.07%

-average number of available bytes per page.: 397.0

-average page density (full).: 95.10%

DBCC execution completed. If DBCC outputs an error message, contact your system administrator.

After truncate table Table_ 3:

Dbcc showcontig ('Table_3')

Go

DBCC SHOWCONTIG is scanning the 'Table_3' table.

Table: 'Table_3' (386100416); index ID: 0, database ID: 5

Performed a TABLE level scan.

-number of scanned pages.: 0

-number of scan areas.: 0

-number of zone switches.: 0

-average number of pages per zone.: 0.0

-scan density [best count: actual count].: 100.00% [0:0]

-Zone scan fragments.: 0.005%

-average number of available bytes per page.: 0.0

-average page density (full).: 0.005%

DBCC execution completed. If DBCC outputs an error message, contact your system administrator.

Index the table:

Add a primary key clustered index to the id field:

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

ALTER TABLE dbo.Table_3 ADD CONSTRAINT

PK_Table_3 PRIMARY KEY CLUSTERED

(

Id

) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE dbo.Table_3 SET (LOCK_ESCALATION = TABLE)

GO

COMMIT

Insert 200000 records into the table:

Set nocount on

Declare @ I int

Set @ I = 0

While @ I < 200000

Begin

Insert into Table_3 values (@ iMagneur ABCDM 123pr 234pr 345pr 567')

Set @ I = @ I + 1

End

Go

Dbcc showcontig ('Table_3')

Go

DBCC SHOWCONTIG is scanning the 'Table_3' table.

Table: 'Table_3' (386100416); index ID: 1, database ID: 5

Performed a TABLE level scan.

-number of scanned pages.: 1087

-number of scanning areas.: 139

-number of zone switches.: 138

-average number of pages per district.: 7.8

-scan density [Best count: actual count].: 97.84% [136VR 139]

-logical scan fragments.: 0.55%

-Zone scan fragments.: 2.16%

-average number of available bytes per page.: 0.3

-average page density (full).: 100.005%

DBCC execution completed. If DBCC outputs an error message, contact your system administrator.

Table size after indexing and inserting data:

For 20W of data:

Build the table, insert the data, and index it.

Build the table, index, and insert the data.

It feels like it takes about the same amount of time and space.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.