October 1, 2015 at 12:23 pm
kslentz (10/1/2015)
Not a good question for 2008 R2 users with no FileTable experience. Researching on MSDN (curious about the FileTable and the structure) gives an incorrect answer. Bummer!!
I'm in the same situation with SQL 2008. I researched it and came up with the wrong answer. If nothing else, this provided a perfect example of how MSDN is wrong sometimes. This certainly isn't the first time.
October 1, 2015 at 2:01 pm
timwell (10/1/2015)
With regard to last_access_time, there is a note from a Michael Quinlan on the version of the FileTable Schema page that comes up on the search in technet with some corrections:https://technet.microsoft.com/en-us/library/gg492084(v=sql.110).aspx
He also mentions that Name should say "not null"
But...is Michael correct? Note that MSDN for 2016 still say's it's not null
Gerald Britton, Pluralsight courses
October 1, 2015 at 2:08 pm
Exactly
October 1, 2015 at 2:25 pm
g.britton (10/1/2015)
timwell (10/1/2015)
With regard to last_access_time, there is a note from a Michael Quinlan on the version of the FileTable Schema page that comes up on the search in technet with some corrections:https://technet.microsoft.com/en-us/library/gg492084(v=sql.110).aspx
He also mentions that Name should say "not null"
But...is Michael correct? Note that MSDN for 2016 still say's it's not null
OK I just set up a new filetable and use sp_help to tell me about the schema. I found (a bit messy, sorry):
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
stream_id uniqueidentifierno16 no(n/a)(n/a)NULL
file_stream varbinaryno-1 yesnoyesNULL
name nvarcharno510 no(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
path_locator hierarchyidno892 no(n/a)noNULL
parent_path_locatorhierarchyidyes892 yes(n/a)yesNULL
file_type nvarcharyes510 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
cached_file_size bigintyes819 0 yes(n/a)(n/a)NULL
creation_time datetimeoffsetno1034 7 no(n/a)(n/a)NULL
last_write_time datetimeoffsetno1034 7 no(n/a)(n/a)NULL
last_access_time datetimeoffsetno1034 7 yes(n/a)(n/a)NULL
is_directory bit no1 no(n/a)(n/a)NULL
is_offline bitno1 no(n/a)(n/a)NULL
is_hidden bitno1 no(n/a)(n/a)NULL
is_readonly bitno1 no(n/a)(n/a)NULL
is_archive bitno1 no(n/a)(n/a)NULL
is_system bitno1 no(n/a)(n/a)NULL
is_temporary bitno1 no(n/a)(n/a)NULL
Michael is mostly correct, if sp_help is correct. THe date columns are reported as datetimeoffset, not datetime2
Gerald Britton, Pluralsight courses
October 1, 2015 at 3:19 pm
g.britton (10/1/2015)
timwell (10/1/2015)
With regard to last_access_time, there is a note from a Michael Quinlan on the version of the FileTable Schema page that comes up on the search in technet with some corrections:https://technet.microsoft.com/en-us/library/gg492084(v=sql.110).aspx
He also mentions that Name should say "not null"
But...is Michael correct? Note that MSDN for 2016 still say's it's not null
Close enough for me, I use 2008 R2 and got the QOTD correct... 😎
October 1, 2015 at 7:31 pm
Well darn. I trusted Books Online instead of testing it out for myself. Good to know. Thanks Andy.
October 2, 2015 at 12:30 am
It looks simple, but eventually a lot discussion.
That's when you learn the most !
October 2, 2015 at 1:17 am
Curiously despite being allowed NULL, the last_access_time column has a default value of (sysdatetimeoffset()) 😉
...
October 3, 2015 at 7:27 am
rabih_karam (10/1/2015)
Is really this is the correct answer ?:w00t::w00t::cool::cool:
I'm pretty sure ieither it isn't or BOL is wrong, possibly both. The FileTable schema BOL page shows last_accesstime as datetime2(4) not null with default current time, in the 2012, 2014, and 2016 versions of the page (all of which are wrong, as the type and length are not as shown) and it seems that the column is set to the currrent datetimeoffset when teh filetable is created (as is the last write column). There's a correction suggested on the 2012 page, but that was added 3 years ago and BOL is still giving the same story today. I guess I could try overwriting that column with NULL if I cared enough to find out, but it's definitely a column that shouldn't be nullable (for the same reason the last write time isn't nullable) although it's not too harmful if it is.
Tom
October 5, 2015 at 1:02 pm
Thanks for the question.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply