SQL 2016 – It Just Runs Faster – BULK INSERT Uses Vector Instructions (SSE/AVX)


Moved from: bobsql.com

 

SQL Server 2016 enhanced the bulk insert activities (BULK INSERT), leveraging the CPUs vector instructions.   The vector based approach allows the bulk data to be processed faster.

 

For example, when inserting data from a text file the integer conversion leverages SSE instructions when the character length is more than 4 characters and trace flag 4809 is disabled. 

 

Take the value of ‘12345678’ needing to be converted to the integer value 1,2345,678.  A common algorithm used to determine the integer value of each position one might loop over the character array subtracting the value of character ‘0’