{"id":505,"date":"2012-01-16T08:00:00","date_gmt":"2012-01-16T08:00:00","guid":{"rendered":"\/blogs\/bobb\/post\/SQL-Server-2012-FileTables-in-T-SQL-part-2-new-rows.aspx"},"modified":"2013-01-03T23:59:27","modified_gmt":"2013-01-04T07:59:27","slug":"sql-server-2012-filetables-in-t-sql-part-2-new-rows","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/","title":{"rendered":"SQL Server 2012 FileTables in T-SQL part 2: new rows"},"content":{"rendered":"

\nSo the functions\/methods that I wrote about in previous post <\/a>are needed because FileTables don't store the UNC path name of the file, they store the path_locator as a hierarchyid data type. Wonder what encoding scheme they're using. Let's see, by doing\n<\/p>\n

\nSELECT path_locator.ToString(), Name
\nFROM dbo.Documents\n<\/p>\n

\nWe get hierarchyid strings that look like this: "\/192992825631153.73945086322524.2119705196\/" Turns out that the encoding scheme involves newid() as you can see by looking at the definition for the default constraint for the path_locator column. It looks like this:\n<\/p>\n

\nconvert(hierarchyid, '\/' +    
\nconvert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.' +
\nconvert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.' +
\nconvert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '\/')\n<\/p>\n

\nIn fact, almost all of the columns in a FileTable have defaults or are computed columns. So, to create a row in a FileTable, let's say, a file named "Testfile1.txt" in the root of the file share, all that's required is:\n<\/p>\n

\nINSERT INTO dbo.Documents(Name, file_stream) VALUES('Testfile1.txt', 0x);\n<\/p>\n

\nBut, if I have a directory named "SQLFiles" at the root? How do I create a file in that directory? You might think parent_path_locator, but that's a computed column. Well, how about…\n<\/p>\n

\nSELECT @pathstring = path_locator.ToString() from documents where name = 'SQLFiles';
\nSET @newpath = @pathstring +  convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.'
\n                           +  convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.'
\n                           +  convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '\/';\n<\/p>\n

\nINSERT INTO dbo.Documents(Name, path_locator, file_stream) VALUES('SQLFilesTest.txt', @newpath, 0x);\n<\/p>\n

\nYou could also just use "@pathstring + '1\/'" but this sticks to the "native" encoding scheme. And no, newid() can't be used in a function, if that's what you're thinking.\n<\/p>\n

\nNow that I've got a zero-length file, can I open it and edit it with Notepad.exe? Well, you can if its empty. But once there is data in the file, attempting to edit with Notepad.exe returns an error, "This request is not supported". This is because Notepad.exe uses memory-mapped files, a win32 feature which isn't supported by FileTable. Paint.exe uses them too. So you'd need to open it from a remote location. Actually, connecting the share as a network drive will work as well.\n<\/p>\n

\nBut what if there are directories named "SQLFiles" at different subdirectory levels? How can we distinguish between them? That's where the hierarchyid comes in. I'll continue with that next.\n<\/p>\n

\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"

So the functions\/methods that I wrote about in previous post are needed because FileTables don't store the UNC path name of the file, they store the path_locator as a hierarchyid data type. Wonder what encoding scheme they're using. Let's see, by doing SELECT path_locator.ToString(), Name FROM dbo.Documents We get hierarchyid strings that look like this: […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,31],"tags":[],"class_list":["post-505","post","type-post","status-publish","format-standard","hentry","category-filestream-storage","category-sql-server-2012"],"yoast_head":"\nSQL Server 2012 FileTables in T-SQL part 2: new rows - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2012 FileTables in T-SQL part 2: new rows - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"So the functions\/methods that I wrote about in previous post are needed because FileTables don't store the UNC path name of the file, they store the path_locator as a hierarchyid data type. Wonder what encoding scheme they're using. Let's see, by doing SELECT path_locator.ToString(), Name FROM dbo.Documents We get hierarchyid strings that look like this: […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2012-01-16T08:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:27+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/\",\"name\":\"SQL Server 2012 FileTables in T-SQL part 2: new rows - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2012-01-16T08:00:00+00:00\",\"dateModified\":\"2013-01-04T07:59:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Filestream Storage\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/filestream-storage\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL Server 2012 FileTables in T-SQL part 2: new rows\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server 2012 FileTables in T-SQL part 2: new rows - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2012 FileTables in T-SQL part 2: new rows - Bob Beauchemin","og_description":"So the functions\/methods that I wrote about in previous post are needed because FileTables don't store the UNC path name of the file, they store the path_locator as a hierarchyid data type. Wonder what encoding scheme they're using. Let's see, by doing SELECT path_locator.ToString(), Name FROM dbo.Documents We get hierarchyid strings that look like this: […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/","og_site_name":"Bob Beauchemin","article_published_time":"2012-01-16T08:00:00+00:00","article_modified_time":"2013-01-04T07:59:27+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/","name":"SQL Server 2012 FileTables in T-SQL part 2: new rows - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2012-01-16T08:00:00+00:00","dateModified":"2013-01-04T07:59:27+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2012-filetables-in-t-sql-part-2-new-rows\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Filestream Storage","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/filestream-storage\/"},{"@type":"ListItem","position":3,"name":"SQL Server 2012 FileTables in T-SQL part 2: new rows"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/505","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=505"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/505\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=505"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=505"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=505"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}