There is already an object named '#table in the database.

Question

Comunidad,

tengo este script

	IF OBJECT_ID('tempdb..#mytempo') IS NOT NULL
		BEGIN
			DROP TABLE #mytempo;
		END

	if (select datepart(weekday,getdate())) = 7
		Begin
				SELECT
				B.type,
				B.name as objectname, 
				A.object_id AS objectid,
				A.index_id AS indexid,
				A.partition_number AS partitionnum,
				A.avg_fragmentation_in_percent AS frag
				INTO #mytempo
				FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') A INNER JOIN
				sys.objects B on A.[object_id] = B.[object_id]
				WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND B.name <> 'dtproperties' AND B.type = 'u'
				AND substring(B.name,1,1) between 'A' and 'G' ORDER BY 2;  
		End
	Else  
		Begin
				SELECT
				B.type,
				B.name as objectname, 
				A.object_id AS objectid,
				A.index_id AS indexid,
				A.partition_number AS partitionnum,
				A.avg_fragmentation_in_percent AS frag
				INTO #mytempo
				FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') A INNER JOIN
				sys.objects B on A.[object_id] = B.[object_id]
				WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND B.name <> 'dtproperties' AND B.type = 'u'
				AND substring(B.name,1,1) between 'H' and 'Z' ORDER BY 2; 
		End

y tengo este error: 

There is already an object named '#mytempo' in the database.

Ahora, estoy seguro que cuando corre el script la tabla #mytempo no existe,  de hecho por eso coloque la parte de codigo de la parte superior, para validar esta situación mas bien   creo que  va por el lado del editor de sql el parser :s  , como si estuviera leyendo que se creara dos veces la tabla aunque en la logica eso nunca va a pasar,   alguien me puede explicar que pasa es un bug ???  o la razon,  la solución  creo que seria creando primero la tabla temporal y en vez de hacer el selec into,  hacer un insert con la tabla ya creada, pero   me quedo con la duda de que pasaaaaaaa!!!!!

por cierto la prueba es en sql server 2008 r2

alguien ??

saludos

http://dbasqlserver.wordpress.com/

Answer 1

En realidad no es por el parseador sintáctico, sino por una mejora que se introdujo a partir de la versión 2005 gracias a la cual en ciertas condiciones, la definición de la tabla temporal se cachea para optimizar el proceso de de crear/eliminar/recrear dicha tabla.

Para resolver el problema hay varias opciones, una de ellas es la que apuntas de crear explícitamente la tabla como paso previo a la inserción de los datos, pero otra sería meter esa instrucción dentro de una ejecución dinámica:

	
	IF (SELECT DATEPART(weekday,GETDATE())) = 7
		EXEC('
			SELECT
			B.type,
			B.name as objectname, 
			A.object_id AS objectid,
			A.index_id AS indexid,
			A.partition_number AS partitionnum,
			A.avg_fragmentation_in_percent AS frag
			INTO #mytempo
			FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') A INNER JOIN
			sys.objects B on A.[object_id] = B.[object_id]
			WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND B.name <> ''dtproperties'' AND B.type = ''u''
			AND substring(B.name,1,1) between ''A'' and ''G'' ORDER BY 2; 
			
			SELECT  *
			FROM    #mytempo	
		')
	ELSE  
		EXEC(
		'							
		SELECT
		B.type,
		B.name as objectname, 
		A.object_id AS objectid,
		A.index_id AS indexid,
		A.partition_number AS partitionnum,
		A.avg_fragmentation_in_percent AS frag
		INTO #mytempo
		FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') A INNER JOIN
		sys.objects B on A.[object_id] = B.[object_id]
		WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND B.name <> ''dtproperties'' AND B.type = ''u''
		AND substring(B.name,1,1) between ''H'' and ''Z'' ORDER BY 2; 
		
		SELECT  *
		FROM    #mytempo	
		')
		
	

Para más info, echa un vistazo al artículo "Working with tempdb in SQL Server 2005" [technet.microsoft.com/en-us/library/cc966545.aspx]. En el apartado "Improvements in SQL Server 2005" se detalla lo que te comentaba del cacheo

Answer 2

Gracias por la explicación,   ahora entiendo el porque,  pero aún asi creo que es una especie de bug que no deberia pasar, 

Saludos y gracias.

http://dbasqlserver.wordpress.com/