{"id":4399,"date":"2015-03-12T08:21:41","date_gmt":"2015-03-12T07:21:41","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/"},"modified":"2015-03-12T08:21:41","modified_gmt":"2015-03-12T07:21:41","slug":"venturing-into-bulk-insert-a-sql-server-error-log-and-data-order","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/","title":{"rendered":"Venturing into bulk insert a SQL Server error log and data order"},"content":{"rendered":"

Have you ever attempted to bulk import a SQL Server error log in order to use the information inside a report for example? If yes, you have probably wondered how to keep data in the correct order in a query because you cannot refer to any column from the table. In such case you can notice that you may have many records with the same date. Of course, there exists some workarounds but this is not the subject of this blog. Instead, I would like to share with you an interesting discussion I had with a forum member about the guarantee to get the SQL Server error log data in order with a simple SELECT statement without an ORDER BY clause.<\/p>\n

Let\u2019s begin with the following script which bulk import data from a SQL Server error log file inside a table:<\/p>\n

CREATE TABLE ErrLog<\/span><\/span><\/span><\/div>\n
(<\/span><\/div>\n
LogCol NVARCHAR(max) NULL<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
)<\/span><\/div>\n

 <\/p>\n

BULK INSERT ErrLog <\/span><\/span><\/span><\/div>\n
FROM ‘C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLLogERRORLOG.1’ <\/span><\/span><\/span><\/div>\n
WITH<\/span><\/div>\n
(<\/span><\/div>\n
FIRSTROW = 6, <\/span><\/span><\/span><\/span><\/div>\n
DATAFILETYPE = ‘widechar’<\/span><\/span><\/span><\/span><\/div>\n
)<\/span><\/div>\n

\u00a0<\/span><\/p>\n

You may notice that we use FIRSTROW hint to begin from the 6th line and skip information as follows:<\/p>\n

2015-03-07 13:57:41.43 Server\u00a0\u00a0\u00a0\u00a0 Microsoft SQL Server 2014 – 12.0.2370.0 (X64) <\/span><\/div>\n
Jun 21 2014 15:21:00 <\/span><\/div>\n
Copyright (c) Microsoft Corporation<\/span><\/div>\n
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)<\/span><\/div>\n

 <\/p>\n

In addition, using DATAFILETYPE = ‘widechar’ is mandatory in order to bulk import Unicode data.<\/p>\n

Let\u2019s continue and after bulking import data let\u2019s take a look at the data itself inside the table. You will probably get the same kind of sample information as follows:<\/p>\n

SELECT<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LogCol<\/span><\/div>\n
FROM ErrLog;<\/span><\/span><\/span><\/div>\n

 <\/p>\n

\"blog_34_-_1_-_bulk_result\"<\/p>\n

Comparing records order between the SQL Server error log file and the table tends to state that the order is the same. At this point, we may wonder how to number the records inside the table without affecting the table order. Indeed, numbering records in the table will allow to control the order of data by using the ORDER BY clause. So go ahead and let\u2019s using the ROW_NUMBER() function in order to meet our requirement. You may notice that I use an \u201cartificial\u201d ORDER BY clause inside the windows function to avoid to interfere with the original order of getting data in my table.<\/p>\n

SELECT<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS numline,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LogCol<\/span><\/span><\/div>\n
FROM ErrLog;<\/span><\/span><\/span><\/div>\n

 <\/p>\n

\"blog_34_-_2_-_bulk_result_with_row_number\"<\/p>\n

At this point, the forum member tells me that we cannot guarantee the order of data without using an order by clause but once again, it seems that we get the same order that the previous query but can I trust it? I completely agree with this forum member and I tend to advice the same thing. However in this specific case the order seems to be correct but why?<\/p>\n

If you take a look at the first script, the first operation consisted in creating a heap table. This detail is very important. Then, the bulk insert operation reads sequentially the file and insert the data in the allocation order of pages. It means that data insertion order is tied to the allocation order of the pages specifying in the IAM page. So when we perform a table scan operation to number each row in the table (see the execution plan below), in fact this operation will be performed by scanning the IAM page to find the extents that are holding pages.<\/p>\n

\"blog_34_-_21_-_execution_plan\"<\/p>\n

As you know, IAM represents extents in the same order that they exist in the data files, so in our case table scan is performed by using the same allocation page order. We can check it by catching the call stack of our query. We may see that the query optimizer uses a particular function called AllocationOrderPageScanner for our heap table.<\/p>\n

\"blog_34_-_3_-_allocation_order_scan\"<\/p>\n

So, that we are agreed, I don\u2019t claim that in all cases we may trust blindly the data order without specifying the ORDER BY clause. In fact, I\u2019m sure you will have to process differently most of the time depending on your context and different factors that will not guarantee the order of data without specifying the ORDER BY CLAUSE (bulking import data from multiple files in parallel, reading data from table with b-tree structure in particular condition etc\u2026)<\/p>\n

Enjoy!<\/p>\n

By David Barbarin<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Have you ever attempted to bulk import a SQL Server error log in order to use the information inside a report for example? If yes, you have probably wondered how to keep data in the correct order in a query because you cannot refer to any column from the table. In such case you can […]<\/p>\n","protected":false},"author":26,"featured_media":4400,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[543,544,51],"type_dbi":[],"class_list":["post-4399","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-allocation-order-scan","tag-bulk-insert","tag-sql-server"],"acf":[],"yoast_head":"\nVenturing into bulk insert a SQL Server error log and data order - dbi Blog<\/title>\n<meta name=\"description\" content=\"Venturing into bulk insert a SQL Server error log and data order\" \/>\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.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Venturing into bulk insert a SQL Server error log and data order\" \/>\n<meta property=\"og:description\" content=\"Venturing into bulk insert a SQL Server error log and data order\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-03-12T07:21:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"772\" \/>\n\t<meta property=\"og:image:height\" content=\"231\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"Venturing into bulk insert a SQL Server error log and data order\",\"datePublished\":\"2015-03-12T07:21:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/\"},\"wordCount\":706,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg\",\"keywords\":[\"allocation order scan\",\"bulk insert\",\"SQL Server\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/\",\"name\":\"Venturing into bulk insert a SQL Server error log and data order - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg\",\"datePublished\":\"2015-03-12T07:21:41+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"description\":\"Venturing into bulk insert a SQL Server error log and data order\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg\",\"width\":772,\"height\":231},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Venturing into bulk insert a SQL Server error log and data order\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Venturing into bulk insert a SQL Server error log and data order - dbi Blog","description":"Venturing into bulk insert a SQL Server error log and data order","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.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/","og_locale":"en_US","og_type":"article","og_title":"Venturing into bulk insert a SQL Server error log and data order","og_description":"Venturing into bulk insert a SQL Server error log and data order","og_url":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/","og_site_name":"dbi Blog","article_published_time":"2015-03-12T07:21:41+00:00","og_image":[{"width":772,"height":231,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"Venturing into bulk insert a SQL Server error log and data order","datePublished":"2015-03-12T07:21:41+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/"},"wordCount":706,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg","keywords":["allocation order scan","bulk insert","SQL Server"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/","url":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/","name":"Venturing into bulk insert a SQL Server error log and data order - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg","datePublished":"2015-03-12T07:21:41+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"description":"Venturing into bulk insert a SQL Server error log and data order","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_34_-_1_-_bulk_result.jpg","width":772,"height":231},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/venturing-into-bulk-insert-a-sql-server-error-log-and-data-order\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Venturing into bulk insert a SQL Server error log and data order"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4399","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4399"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4399\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/4400"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4399"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4399"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4399"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4399"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}