How to run an input operation test with INSERT statements on BIGINT data type?

If you want to see the impact of larger data type like 8-byte BIGINT, comparing to the 4-byte INT, you can follow this tutorial.

Create the following SQL script, fyi_bigint_insert.sql:

-- fyi_bigint_insert.sql
-- Copyright (c) FYIcenter.com 

DECLARE @Count INT
SET @Count = 100

DECLARE @I INT
DECLARE @STime DATETIME
DECLARE @ETime DATETIME
DECLARE @Duration INT

CREATE TABLE FYI_BIGINT (QUANTITY BIGINT) 
SET @STime = GETDATE()

SET @I = 0
WHILE @I < @Count BEGIN
   INSERT INTO FYI_BIGINT VALUES (@I)
   SET @I = @I+1
END

SET @ETime = GETDATE()
DROP TABLE FYI_BIGINT

SET @Duration = DATEDIFF(millisecond,@STime,@ETime)
SELECT @Count AS Count, @Duration AS Duration, 
  1000*@Count/@Duration AS Throughput, @Duration/@Count AS Latency

Each record now has a single field of BIGINT, which uses 8 bytes.

Now run the script with different @Count values on the Azure SQL Database. You will get something like:

Count   Duration   Throughput   Latency
  100        560          178         5
 1000       5737          174         5
10000      59834          167         5
25000     150890          165         6

The actual input data throughput is 165 * 8 bytes/sec = 1,320 bytes/sec = 1.3 KB/sec, using the last test result.

Comparing to 4-byte INT records, inserting 8-byte BIGIN records is more efficient. The throughput increased from 0.9 KB/sec to 1.3 KB/sec.

⇒ Input Operation Test with UUID Data Type

⇐ Verify SQL Database DTU Usage

⇑ IOPS Tests on Azure SQL Database with INSERT

⇑⇑ SQL Server Storage Tutorials