Answers

Question and Answer:

  Home  MS SQL Server

⟩ Does Index Slows Down INSERT Statements?

If you want to see the impact of indexes on INSERT statements, you can repeat the same insert script on the table "ggl_links" of the same structure with two indexes: one non-clustered index on column "url" and one non-clustered index on column "counts". See the tutorial exercise below:

USE GlobalGuideLineDatabase

GO

-- Drop the old table, if needed

DROP TABLE ggl_links_indexed;

GO

-- Create a table

CREATE TABLE ggl_links_indexed (

id INT,

url VARCHAR(80) NOT NULL,

notes VARCHAR(1024),

counts INT,

created DATETIME NOT NULL DEFAULT(getdate())

);

GO

-- Create two indexes

CREATE INDEX ggl_links_url ON ggl_links_indexed (url);

CREATE INDEX ggl_links_counts ON ggl_links_indexed (counts);

GO

-- Empty the table if needed

DELETE FROM ggl_links_indexed;

GO

-- Performance test of INSERT

DECLARE @start_time DATETIME, @end_time DATETIME;

SET @start_time = GETDATE();

INSERT INTO ggl_links_indexed

SELECT id, rand_string, REVERSE(rand_string),

rand_integer, rand_datetime

FROM ggl_random

SET @end_time = GETDATE();

PRINT 'Milliseconds used: '+CONVERT(VARCHAR(20),

DATEDIFF(MILLISECOND,@start_time,@end_time));

GO

 154 views

More Questions for you: