CODEWITHSUNDEEP
sqlsql-server
Kuhlii
Kuhlii

Reputation: 1

SQL temporary table and sparse columns issue

I have asked this on another forum as well, not sure where the best to post this. Thought I would try here as well.

I have recently converted a table with many columns that are now using sparse columns. However I have now found that a lot of my queries are not working.

It seems to be due to the fact that I am using a lot of #Temp tables in my queries along with the SELECT... INTO... method to get data into the temporary table.

This worked previously fine, however the sparse columns are not coming across to the #Temp table.

Here is an example of what previously working fine:-

Select *  
INTO #temptable1
from Client

(This obviously just grabbed everything in the Client table)

I have tried the following without any success (Knowing I now need to specify the column names)

Select ClientId, Val1, Val2, Val3, Val4, Val5, Val6
INTO #temptable1
from Client

Val3, Val4, Val6 and Val6 are sparse columns. The query runs fine, however when I try and do a select of any of the sparse columns from #temptable1, it just says they do not exist.

After reading up. It would seem I can not use the SELECT... INTO... however I have not found any other alternatives that I can convert my code to?

I have a lot of quite complex queries that use this method and I am looking for something that I can convert them all over to.

Any help or suggestions would be greatly appreciated.

Thanks in advance.

Upvotes: 0

Views: 387

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

The SQL Server documentation is clear that sparse columns are not presented in select into:

Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.

So, your columns are being converted to regular columns. I would expect problems because there are too many columns or the records are too big for a SQL Server page.

Perhaps common table expressions could solve your problems. In my experience, temporary tables are useful, but very over-used.

Upvotes: 3

Related Questions