SELECT INTO FILESTREAM

Question

SELECT INTO FILESTREAM

Steve Jones - SSC Editor

SSC Guru

Points: 738140
More actions
May 28, 2013 at 9:13 pm

#74176

Comments posted to this topic are about the item SELECT INTO FILESTREAM

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Interesting question Steve. I had no idea about this, except the connection of VARBINARY and FILESTREAM. Luckily was able to guess the correct answer

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 2

I don't have idea about filestream but i'm sure other than IDENTITY property no other attributes will be copied to new table. So i guessed it should be varchar(max) but it was wrong , could have gone through the BOL before answering.

--
Dineshbabu
Desire to learn new things..

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Dineshbabu (5/29/2013)
I don't have idea about filestream but i'm sure other than IDENTITY property no other attributes will be copied to new table. So i guessed it should be varchar(max) but it was wrong , could have gone through the BOL before answering.

Same thing happened with me :doze:

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

I am not surprised that SELECT ... INTO loses the FILESTREAM attribute. However, I was surprised to find that varchar(max) with FILESTREAM would be converted to varbinary(max).

So then I did a bit more research, and found that FILESTREAM is not even permitted with varchar(max) - so not really a surprise that all FILESTREAM columns get converted to varbinary(max), since that already was their data type! :hehe:

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

seatedElephant Say Hey Kid Points: 674 More actions · Answer 5

Interesting question Steve. I had no idea about this, except the connection of VARBINARY and FILESTREAM. Luckily was able to guess the correct answer
~ Lokesh Vij

+1, except I guessed wrong. Thanks for the question.

Dave Morris :alien:

"Measure twice, saw once"

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 6

Easy one, thanks Steve!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Hugo Kornelis (5/29/2013)
I am not surprised that SELECT ... INTO loses the FILESTREAM attribute. However, I was surprised to find that varchar(max) with FILESTREAM would be converted to varbinary(max).
So then I did a bit more research, and found that FILESTREAM is not even permitted with varchar(max) - so not really a surprise that all FILESTREAM columns get converted to varbinary(max), since that already was their data type! :hehe:

Thanks Hugo.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Nice question.

Dineshbabu (5/29/2013)
I don't have idea about filestream but i'm sure other than IDENTITY property no other attributes will be copied to new table. So i guessed it should be varchar(max) but it was wrong , could have gone through the BOL before answering.

But why did you think the source type was varchar(max) filestream? varchar(max) isn't a BLOB type any more that text was it's LOB but not BLOB, so varchar(max) filestream doesn't exist.

Tom

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 9

Very Interesting, Steve. Thank you for posting.

I have read about filestream but never tried it practically. For testing this code, which came with good amount of learning and couple of hours of BOL exploring , finally I was able to create the tables with the FILESTREAM attribute which only can be achieved by script creation and not using with GUI for table design. Also I didn't know this needs a default filestream filegroup and then one file with the filestream type needs to be created before creating any columns with the filestream attribute.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.