How It Works: Bulk Insert (BCP.exe) – Injecting a Sort Operation

Published Feb 06 2022 07:29 AM 982 Views
Microsoft

 

Move from: bobsql.com


The customer reported that they could not BCP into a table using “keepidentity” and after more debugging I found that “keepidentity” was not the issue but the issue was the addition of a clustered index.

 

The table is defined with 100s of sparse columns:

CREATE TABLE [dbo].[Mytable](

       [DataId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

       [MainId] [int] NOT NULL,

       [YearId] [char](2) NOT NULL,

       [UserId] [int] NOT NULL,

       [DateAdded] [datetime] NOT NULL,

       [DateLstMod] [datetime] NOT NULL,

       [C1] [varchar](max) SPARSE NULL,

       [C2] [varchar](max) SPARSE NULL,

       [C3] [varchar](max) SPARSE NULL,

[C547] [varchar](max) NULL,

       [C548] [varchar](max) NULL,

       [C549] [varchar](max) NULL);

 

A row was added to the table and bcp.exe used to export the data to a file followed by bcp.exe to import the data.

When the table has no-index the import succeeds but when the following index (it turns out any clustered index is present) the import fails.

 

CONSTRAINT [Pk_[DataId] PRIMARY KEY CLUSTERED

(

       [DataId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SQLState = 37000, NativeError = 511 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server] Cannot create a row of size 8161 which is greater than the allowable maximum row size of 8060.

 

I was able to track the behavior the use of a sort for the bulk operation.  When an index/constraint is present bulk insert may choose to sort the incoming data in a temporary table.  The incoming stream is using a sort and the additional overhead for the sort causes the row to exceed the size limit.

 

BCP SORTS: Bulk Insert/BCP uses a set of heuristics to determine when to inject and sort the incoming data.   In general, bcp.exe and bulk insert are considered unknown sort sources and a sort will be injected to handle index/constraint operations.


You can use the following to get a ballpark idea of the row size.  In this customer’s example the size was just under the row limit.  When the sort is injected the row size exceeds the limit, generating to the error.

 

-- Estimate the row size

--

declare @dynamicsql varchar(max)

set @dynamicsql = 'select [DataId], (0'

 

select @dynamicsql = @dynamicsql + ' + isnull(datalength(' + name + '), 0)' from syscolumns where id = object_id('MyTable')  and xusertype <> 167

 

select @dynamicsql = @dynamicsql + ' + 24'

       from syscolumns where id = object_id('MyTable')  and xusertype = 167

 

set @dynamicsql = @dynamicsql + ') as rowsize from MyTable2 order by DataId'

 

select @dynamicsql

exec (@dynamicsql)

Workaround
There are various workarounds to the issue.  Use non-clustered index, drop the index, import, and create the index, or BCP into a table without an index and then insert into the target table.

 

Stack Proof
The following stack shows the Bulk operation with the additional sort taking place.

    01 sqldk!ex_raise
    02 sqlmin!LongRecord::SetDataInternal

    03 sqlmin!LongRecord::SetData

    04 sqlmin!CreateNewRecordNoCheck

    05 sqlmin!CreateNewRecord

    06 sqlmin!RowsetSorted::InsertRow

    07 sqlmin!CValRowNoHrow::SetDataX

    08 sqlTsEs!CallEsFn

    09 sqlTsEs!CEsExec::GeneralEval

    0a sqlmin!CEsRuntime::Eval

    0b sqlmin!CQScanSortNew::PushRow

    0c sqlmin!CQScanSortNew::BuildSortTable

    0d sqlmin!CQScanSortNew::OpenHelper

    0e sqlmin!CQScanNew::OpenHelper

    0f sqlmin!CQScanUpdateNew::Open

    10 sqlmin!CQueryScan::StartupQuery

    11 sqllang!CXStmtQuery::SetupQueryScanAndExpression

    12 sqllang!CXStmtQuery::InitForExecute

    13 sqllang!CXStmtQuery::ErsqExecuteQuery

    14 sqllang!CXStmtDML::XretDMLExecute

    15 sqllang!CXStmtInsertBulk::XretDoExecute

    16 sqllang!CXStmtInsertBulk::XretExecute

    17 sqllang!CExecStmtLoopVars::ExecuteXStmtAndSetXretReturn

    18 sqllang!CMsqlExecContext::ExecuteStmts<1,0>

    19 sqllang!CMsqlExecContext::FExecute

    1a sqllang!CSQLSource::Execute

-          Bob Dorr

%3CLINGO-SUB%20id%3D%22lingo-sub-3122694%22%20slang%3D%22en-US%22%3EHow%20It%20Works%3A%20Bulk%20Insert%20(BCP.exe)%20%E2%80%93%20Injecting%20a%20Sort%20Operation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122694%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20class%3D%22WordSection1%22%3E%0A%3CH6%20class%3D%22MsoNormal%22%20id%3D%22toc-hId-1762582807%22%20id%3D%22toc-hId-1762583491%22%3EMove%20from%3A%20bobsql.com%3C%2FH6%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CBR%20%2F%3EThe%20customer%20reported%20that%20they%20could%20not%20BCP%20into%20a%20table%20using%20%E2%80%9Ckeepidentity%E2%80%9D%20and%20after%20more%20debugging%20I%20found%20that%20%E2%80%9Ckeepidentity%E2%80%9D%20was%20not%20the%20issue%20but%20the%20issue%20was%20the%20addition%20of%20a%20clustered%20index.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EThe%20table%20is%20defined%20with%20100s%20of%20sparse%20columns%3A%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ECREATE%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ETABLE%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%5Bdbo%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%5BMytable%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BDataId%5D%20%5Bint%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EIDENTITY%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E1%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E1%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENOT%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EFOR%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EREPLICATION%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENOT%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BMainId%5D%20%5Bint%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENOT%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BYearId%5D%20%5Bchar%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E2%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENOT%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BUserId%5D%20%5Bint%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENOT%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BDateAdded%5D%20%5Bdatetime%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENOT%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BDateLstMod%5D%20%5Bdatetime%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENOT%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BC1%5D%20%5Bvarchar%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3Emax%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20SPARSE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BC2%5D%20%5Bvarchar%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3Emax%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20SPARSE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-left%3A%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BC3%5D%20%5Bvarchar%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3Emax%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20SPARSE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3CBR%20%2F%3E%E2%80%A6%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-indent%3A%20.5in%3B%20text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%5BC547%5D%20%5Bvarchar%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3Emax%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BC548%5D%20%5Bvarchar%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3Emax%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-left%3A%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BC549%5D%20%5Bvarchar%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3Emax%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3ENULL)%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-left%3A%20.5in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EA%20row%20was%20added%20to%20the%20table%20and%20bcp.exe%20used%20to%20export%20the%20data%20to%20a%20file%20followed%20by%20bcp.exe%20to%20import%20the%20data.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3EWhen%20the%20table%20has%20no-index%20the%20import%20succeeds%20but%20when%20the%20following%20index%20(it%20turns%20out%20any%20clustered%20index%20is%20present)%20the%20import%20fails.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ECONSTRAINT%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%5BPk_%5BDataId%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EPRIMARY%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EKEY%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ECLUSTERED%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BDataId%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EASC%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EPAD_INDEX%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EOFF%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ESTATISTICS_NORECOMPUTE%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EOFF%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EIGNORE_DUP_KEY%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EOFF%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EALLOW_ROW_LOCKS%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EON%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EALLOW_PAGE_LOCKS%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EON%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EFILLFACTOR%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%2095%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EOPTIMIZE_FOR_SEQUENTIAL_KEY%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EOFF%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EON%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%5BPRIMARY%5D%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EON%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%5BPRIMARY%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ETEXTIMAGE_ON%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%5BPRIMARY%5D%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3ESQLState%20%3D%2037000%2C%20NativeError%20%3D%20511%20Error%20%3D%20%5BMicrosoft%5D%5BODBC%20Driver%2013%20for%20SQL%20Server%5D%5BSQL%20Server%5D%20Cannot%20create%20a%20row%20of%20size%208161%20which%20is%20greater%20than%20the%20allowable%20maximum%20row%20size%20of%208060.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EI%20was%20able%20to%20track%20the%20behavior%20the%20use%20of%20a%20sort%20for%20the%20bulk%20operation.%26nbsp%3B%20When%20an%20index%2Fconstraint%20is%20present%20bulk%20insert%20may%20choose%20to%20sort%20the%20incoming%20data%20in%20a%20temporary%20table.%26nbsp%3B%20The%20incoming%20stream%20is%20using%20a%20sort%20and%20the%20additional%20overhead%20for%20the%20sort%20causes%20the%20row%20to%20exceed%20the%20size%20limit.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20class%3D%22MsoNormalTable%22%20style%3D%22width%3A%20467.5pt%3B%20border-collapse%3A%20collapse%3B%22%20border%3D%220%22%20width%3D%22623%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22623%22%20valign%3D%22top%22%20style%3D%22width%3A%20467.5pt%3B%20background%3A%20%23FFF2CC%3B%20padding%3A%200in%205.4pt%200in%205.4pt%3B%22%3E%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200in%3B%22%3E%3CSTRONG%3EBCP%20SORTS%3A%3C%2FSTRONG%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%20Bulk%20Insert%2FBCP%20uses%20a%20set%20of%20heuristics%20to%20determine%20when%20to%20inject%20and%20sort%20the%20incoming%20data.%26nbsp%3B%26nbsp%3B%20In%20general%2C%20bcp.exe%20and%20bulk%20insert%20are%20considered%20unknown%20sort%20sources%20and%20a%20sort%20will%20be%20injected%20to%20handle%20index%2Fconstraint%20operations.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22color%3A%20white%3B%22%3E%3CBR%20%2F%3E%3C%2FSPAN%3EYou%20can%20use%20the%20following%20to%20get%20a%20ballpark%20idea%20of%20the%20row%20size.%26nbsp%3B%20In%20this%20customer%E2%80%99s%20example%20the%20size%20was%20just%20under%20the%20row%20limit.%26nbsp%3B%20When%20the%20sort%20is%20injected%20the%20row%20size%20exceeds%20the%20limit%2C%20generating%20to%20the%20error.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20green%3B%22%3E--%20Estimate%20the%20row%20size%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20green%3B%22%3E--%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Edeclare%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%40dynamicsql%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Evarchar%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3Emax%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eset%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%40dynamicsql%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20red%3B%22%3E'select%20%5BDataId%5D%2C%20(0'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eselect%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%40dynamicsql%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%40dynamicsql%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2B%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20red%3B%22%3E'%20%2B%20isnull(datalength('%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2B%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Ename%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2B%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20red%3B%22%3E')%2C%200)'%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Efrom%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20lime%3B%22%3Esyscolumns%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Ewhere%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20id%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3Eobject_id%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20red%3B%22%3E'MyTable'%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3Eand%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20xusertype%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%26lt%3B%26gt%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20167%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eselect%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%40dynamicsql%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%40dynamicsql%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2B%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20red%3B%22%3E'%20%2B%2024'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Efrom%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20lime%3B%22%3Esyscolumns%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Ewhere%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20id%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3Eobject_id%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20red%3B%22%3E'MyTable'%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3Eand%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20xusertype%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20167%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eset%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%40dynamicsql%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%40dynamicsql%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%2B%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20red%3B%22%3E')%20as%20rowsize%20from%20MyTable2%20order%20by%20DataId'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eselect%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20%40dynamicsql%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eexec%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%40dynamicsql%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3E%3CU%3EWorkaround%3CBR%20%2F%3E%3C%2FU%3E%3C%2FSTRONG%3EThere%20are%20various%20workarounds%20to%20the%20issue.%26nbsp%3B%20Use%20non-clustered%20index%2C%20drop%20the%20index%2C%20import%2C%20and%20create%20the%20index%2C%20or%20BCP%20into%20a%20table%20without%20an%20index%20and%20then%20insert%20into%20the%20target%20table.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3E%3CU%3EStack%20Proof%3CBR%20%2F%3E%3C%2FU%3E%3C%2FSTRONG%3EThe%20following%20stack%20shows%20the%20Bulk%20operation%20with%20the%20additional%20sort%20taking%20place.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2001%20%3CSPAN%20style%3D%22background%3A%20aqua%3B%22%3Esqldk!ex_raise%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2002%20sqlmin!LongRecord%3A%3ASetDataInternal%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2003%20sqlmin!LongRecord%3A%3ASetData%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2004%20sqlmin!CreateNewRecordNoCheck%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B05%20sqlmin!CreateNewRecord%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2006%20sqlmin!RowsetSorted%3A%3AInsertRow%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2007%20sqlmin!CValRowNoHrow%3A%3ASetDataX%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2008%20sqlTsEs!CallEsFn%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2009%20sqlTsEs!CEsExec%3A%3AGeneralEval%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%200a%20sqlmin!CEsRuntime%3A%3AEval%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CSPAN%20style%3D%22background%3A%20aqua%3B%22%3E0b%20sqlmin!CQScanSortNew%3A%3APushRow%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%200c%20sqlmin!CQScanSortNew%3A%3ABuildSortTable%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%200d%20sqlmin!CQScanSortNew%3A%3AOpenHelper%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%200e%20sqlmin!CQScanNew%3A%3AOpenHelper%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%200f%20sqlmin!CQScanUpdateNew%3A%3AOpen%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2010%20sqlmin!CQueryScan%3A%3AStartupQuery%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2011%20sqllang!CXStmtQuery%3A%3ASetupQueryScanAndExpression%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2012%20sqllang!CXStmtQuery%3A%3AInitForExecute%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2013%20sqllang!CXStmtQuery%3A%3AErsqExecuteQuery%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2014%20sqllang!CXStmtDML%3A%3AXretDMLExecute%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CSPAN%20style%3D%22background%3A%20aqua%3B%22%3E15%20sqllang!CXStmtInsertBulk%3A%3AXretDoExecute%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2016%20sqllang!CXStmtInsertBulk%3A%3AXretExecute%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2017%20sqllang!CExecStmtLoopVars%3A%3AExecuteXStmtAndSetXretReturn%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2018%20sqllang!CMsqlExecContext%3A%3AExecuteStmts%26lt%3B1%2C0%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2019%20sqllang!CMsqlExecContext%3A%3AFExecute%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%20color%3A%20%231f497d%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%201a%20sqllang!CSQLSource%3A%3AExecute%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoListParagraph%22%20style%3D%22text-indent%3A%20-.25in%3B%22%3E-%3CSPAN%20style%3D%22font%3A%207.0pt%20'Times%20New%20Roman'%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20white%3B%22%3EBob%20Dorr%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3122694%22%20slang%3D%22en-US%22%3E%3CP%3EWhy%20is%20Bulk%20Insert%20injecting%20a%20sort%20operation%3F%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3122694%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBobSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Feb 06 2022 07:28 AM
Updated by: