I am Sandeep Chalke from Microsoft Data Access Support Team. My team deals with issues that arise while accessing data from SQL Server.
I hope many of you should be acquainted with SSDT (SQL Server Data Tools). If not, please visit http://msdn.microsoft.com/en-us/data/tools.aspx for details.
Two weeks back I faced an intriguing issue related to SSDT. One of my customer opened a support ticket in regards to poor performance with SSDT while deploying the database to SQL Server using the SSDT generated deployment script.
The customer was using SSDT from Visual Studio 2013. When SSDT created the deployment script, a lot of temporary tables with suffix "tmp_ms_XXX_" were seen.
CREATE TABLE [dbo].[tmp_ms_xx_DBP_Table]
INSERT INTO [dbo].[tmp_ms_xx_DBP_Table]
DROP TABLE [dbo].[DBP_Table]
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_DBP_Table]', N'DBP_Table';
The above script was seen for every table in the database. Now, this behavior is expected, if the schema of the underlying table is changed. But, in this scenario that was not the case. Even though the underlying table was unchanged, SSDT would create script with tmp tables.
After spending lot of time experimenting with the properties of SSDT project, I could nail down the issue.
Every SQL file included in the SSDT project has few properties, like "ANSI Null", "Quoted Identifier" etc. which are set to "Project Default" by default.
File Properties in the SSDT project for the SQL file
When we try to "Genearte Script", the same properties are seen in the "Advanced Properties". If the value of the properties set for the SQL file is different than the one in the "Advanced Properties", SSDT will create tmp tables.
Database in SQL Server
Advanced Properties while Publish/Generate Script of SSDT project.
Author : Sandeep(MSFT), Support escalation engg, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft