SELECT INTO FILESTREAM

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

  • 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

  • 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..

  • 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/

  • 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/

  • 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"

  • 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

  • This was removed by the editor as SPAM

  • 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

  • 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

  • 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.

  • Good question ..though my answer was wrong .. nice to know about filestream attribute

  • Though I didn't knew but guessed it correct. 🙂

  • a bit of luck was not really sure !

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

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