⟩ 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