You should only need to drop and recreate the affected NC index.
Saying that, on a test server, you can see the differences in doing this compared to your strategy of dropping all indexes above.
I reckon dropping/creating the single index would be quicker overall because the data will be shifted around twice otherwise: once char to varchar, another to build the clustered index. Then you have the NC creation overhead.
After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:
CREATE TRIGGER [ddl_log]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;
DECLARE @data XML
SET @data = EVENTDATA()
EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
GETDATE(),
CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
CONVERT(NVARCHAR(100), HOST_NAME()),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
) ;
REVERT
END
As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.
Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.
Best Answer
I doubt Microsoft has anywhere published the logic that is used to determine how SSMS will script a table change. It should be noted that the way SSMS has handled these has evolved over the years - I swear in earlier versions every single change would drop & re-create. Now it definitely tries to make much targeted adjustments.
I can't think of many instances where the only option is to DROP and CREATE. There are many scenarios where it would be the easiest for a human or SSMS to script, but most any situation can be solved by judicious uses of
ALTERor dropping and re-creating constraints only. The one absolute exception to this is changing the order of columns - that does require dropping and re-creating the table.One way to always be sure of what your change is going to do is, in the table designer, once you've made your changes, but before you've saved them, go to the "Table Designer" menu, and select "Generate Change Script." This will tell you exactly what SSMS is going to execute and can allow you to decide before you hit "Save" whether or not that's how you want it done.