Post reply

Filestream Tables

  • December 10, 2015 at 8:26 pm

    #72566

    Comments posted to this topic are about the item Filestream Tables

  • December 10, 2015 at 11:47 pm

    This was removed by the editor as SPAM

  • December 11, 2015 at 1:24 am

    #1845413

    Thanks for the QOTD Steve.

  • December 11, 2015 at 1:28 am

    #1845415

    Good question thanks Steve

    ...

  • December 11, 2015 at 2:38 am

    #1845430

    Well I guessed right, but the correct answer is surely that ID is not a valid data type for myID.

  • December 11, 2015 at 3:04 am

    #1845433

    The question would have been better if the author had added a note that we can assume that "ID" is already created as a userdefined datatype.

    I still got it wrong. All the documentation I found (including the one referenced in the explanation) says that a ROWGUID column "is required to use FILESTREAM data with Win32 APIs" - so I assumed that the table would be created but the filestream data would be excluded from Win32 API access.

    Does anyone know why the documentation doesn't simply list this as a requirement without that distracting extra note?

    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/

  • December 11, 2015 at 4:03 am

    #1845438

    Hugo Kornelis (12/11/2015)

    The question would have been better if the author had added a note that we can assume that "ID" is already created as a userdefined datatype.

    A user defined datatype can't specify the ROWGUIDCOL property, nor can that property be applied to a user-defined type, so the column with user defined type ID can't have that property.

    Also, a user-defined datatype can't specify that the coulmn's values are unique or that the column is the primary key, so the column with type ID can't have such a constraint unless it is explicitly stated in the table definition (adding that constraint later, after the table has been created, won't allow the FILESTREAM column to be declared in the table definition at create time). So even if it could have the ROWGUIDCOL property it still couldn't satisfy the requirement for a ROWGUIDCOL property which also has either a NOT NULL UNIQUE constraint or a PRIMARY KEY constraint.

    So I don't think that stating that "ID" is a user defined type would improve the question, it wouldn't really change anything. But maybe I'm biased because I've seen several databases where dbo.ID was defined as an alias type for uniqueidentifier.

    All the documentation I found (including the one referenced in the explanation) says that a ROWGUID column "is required to use FILESTREAM data with Win32 APIs" - so I assumed that the table would be created but the filestream data would be excluded from Win32 API access.

    Does anyone know why the documentation doesn't simply list this as a requirement without that distracting extra note?

    The CREATE TABLE BoL page doesn't have that distracting extra note. It says (in its explanation of the FILESTREAM column attribute)

    wrote:

    Books Online[/b]

    FILESTREAM

    Valid only for varbinary(max) columns. Specifies FILESTREAM storage for the varbinary(max) BLOB data.

    The table must also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute. This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint. The GUID value for the column must be supplied either by an application when inserting data, or by a DEFAULT constraint that uses the NEWID () function.

    The ROWGUIDCOL column cannot be dropped and the related constraints cannot be changed while there is a FILESTREAM column defined for the table. The ROWGUIDCOL column can be dropped only after the last FILESTREAM column is dropped.

    When the FILESTREAM storage attribute is specified for a column, all values for that column are stored in a FILESTREAM data container on the file system.

    Tom

  • December 11, 2015 at 5:06 am

    #1845445

    TomThomson (12/11/2015)

    So I don't think that stating that "ID" is a user defined type would improve the question, it wouldn't really change anything. But maybe I'm biased because I've seen several databases where dbo.ID was defined as an alias type for uniqueidentifier.

    It would remove the possible confusion that the table definition is in error because "ID" is not a valid data type. By stating that a user-defined type of that name exists, that part of the table definition would no longer be an error, and instead the answer marked as correct would be the only correct answer. Now, that depends on an assumption we need to make. And you know what they say about assumptions: they make an *** out of u and mptions.

    Thanks for findinf that additional documentation reference.

    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/

  • December 11, 2015 at 6:02 am

    #1845452

    Toreador (12/11/2015)

    Well I guessed right, but the correct answer is surely that ID is not a valid data type for myID.

    I also guessed (wrong) when the answer I was looking for (ID not valid data type) wasn't listed. Don't know anything about filestreams as that is not the part of the database I work with.

  • December 11, 2015 at 8:07 am

    #1845505

    The ID data type definitely made me take a second look at it. It got the grey matter moving, so it did its job. Thanks.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • December 11, 2015 at 8:34 am

    #1845518

    ID is probably a bad idea. I was thinking a UDDT, but I should have left is as an INT.

    Changed.

  • December 11, 2015 at 11:29 am

    #1845552

    Thanks, Tom for the clarifications. There are several references out there that simply state 'When a table contains a FILESTREAM column, each row must have a nonnull unique row ID." That sounds like a PK to me, hence my (wrong) answer. I would love to see MS update its other references (such as https://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx) to be clear like BOL.

    Thanks!

  • December 13, 2015 at 9:11 pm

    #1845715

    Hi Steve,

    Which is your right piece of code:

    A./

    CREATE TABLE MyTable

    ( myid ID

    , myFS varbinary(max) FILESTREAM

    );

    go FROM THE SQLServerCentral.com <subscriptions@sqlservercentral.com>

    OR

    CREATE TABLE MyTable

    ( myid INT

    , myFS varbinary(max) FILESTREAM

    );

    go

    from THE http://www.sqlservercentral.com/ WEBSITE. SOME CONSISTECY of which you are so committed would be nice if you follow yourself as well. It is really frustrating the reading one version of a code in the email's question of the day and finding another version in the website.

  • December 14, 2015 at 4:16 am

    #1845760

    julienchappel 38298 (12/13/2015)

    SOME CONSISTECY of which you are so committed would be nice if you follow yourself as well. It is really frustrating the reading one version of a code in the email's question of the day and finding another version in the website.

    If you read the forum discussion you'll see that Steve changed the question in response to various comments. Obviously he couldn't access your email system to change it there as well.

  • December 14, 2015 at 6:41 am

    #1845793

    Thanks for the question.

Viewing 15 posts - 1 through 15 (of 16 total)

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