When MSSQL has a large amount of data, the solution to save change prompt timeout after establishing index or adding field

Time:2019-9-14

Generally, we like to use the UI of the database manager to change the structure of the data table, and then naturally place the “save” button to save, but when the amount of data is large, it often appears that “the index”IX_index name”can not be created. The time-out has arrived. The timeout time has elapsed or the server has not responded before the operation is completed. “Such a mistake. At a loss, Wax Man Zhang’s article

Copy codeThe code is as follows:
After modifying table attributes, use the Index/Key dialog box to create an index for a large table (number of records 13,239,473), prompting:

– The index “IX_TableName” could not be created.
The time-out has arrived. The timeout time has elapsed or the server has not responded before the operation is completed.

It took 12 minutes to execute the change script in Database Engine Query after it was generated.

How is the timeout in the Index/Key dialog determined? What are the differences between the two ways in which operations are performed locally? Wondering…

Later comments gave me guidance. Thank!

It’s very simple to sort out the concrete methods, but when no one teaches me to do so, I just stare at them.

When changing the data structure of a table with large amount of data, do not click the “Save” button, but right-click ==”on the left side of the table structure editing box to select”Generate Change Script”. Then a text dialog box comes out, which selects and replicates all the contents of the table, closes the table, chooses not to save, and right-clicks the database to which the table belongs. Select “New Query” and then Ctrl + V pastes in the newly copied script and runs it. This will no longer pop up the timeout prompt, if the amount of data is quite large, we just need to wait patiently!