{"id":923,"date":"2010-06-23T00:00:00","date_gmt":"2010-06-23T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/oracle-to-sql-server-crossing-the-great-divide-part-3\/"},"modified":"2021-08-16T15:02:14","modified_gmt":"2021-08-16T15:02:14","slug":"oracle-to-sql-server-crossing-the-great-divide-part-3","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/oracle-to-sql-server-crossing-the-great-divide-part-3\/","title":{"rendered":"Oracle to SQL Server, Crossing the Great Divide, Part 3"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">At the start of the <a href=\"http:\/\/www.simple-talk.com\/sql\/learn-sql-server\/oracle-to-sql-server-crossing-the-great-divide,-part-2\/\">previous installment<\/a> of this series, I noted that good, scalable database performance is largely dependent on putting the data in the right place, being able to access it efficiently, and avoiding unnecessary overheads. Having looked at ways of generating data of various types and patterns at reasonable volumes, the time has come to investigate how SQL Server handles the placing of data.<\/p>\n<h1>Pages and Extents<\/h1>\n<p>In Oracle, we talk about <span class=\"STBold\">data segments<\/span>. A simple object corresponds to a single data segment, each partition of a partitioned object (or sub-partition of a composite partitioned object) is a separate data segment; segments, in turn, comprise a collection of extents; and an extent is a contiguous set of blocks (pages) in a file.<\/p>\n<p>I started my research into similar data structures in SQL Server. There is no such thing as a &#8220;segment&#8221; in SQL Server, but a search in Books Online for the word &#8220;Extents&#8221; proved to be a good choice. The first hit was an article on <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190969.aspx\">Understanding Pages and Extents<\/a>, which was an excellent starting point for learning how SQL Server allocates space and stores data.<\/p>\n<p>I won&#8217;t repeat the details of my journey through the manuals, but instead will summarize some of the highlights (with comments about Oracle in <span class=\"STItalic\">italics<\/span>):<\/p>\n<ul>\n<li><b>A page is the smallest unit of storage and is a fixed 8KB<\/b> <br \/>\n<i>Oracle can use blocks of 2KB, 4KB, 8KB, 16KB or even, on some platforms, 32KB. A single database can use multiple block sizes. In most cases the default, and best choice, for a platform is 8KB, and mixing block sizes is rarely a good idea.<\/i><\/li>\n<li><b>An extent is a collection of eight consecutive pages<\/b><br \/>\n<i>Oracle allows for variable extent sizes, and has various strategies for specifying the extent sizes that should be used for an object or collection of objects.<\/i><\/li>\n<li><b>Certain &#8220;mapping&#8221; pages are used to track the allocation status of other pages<\/b>, providing information such as which extents have been allocated, the objects to which they have been allocated, which pages in which extents are used, and which pages are free. This leads to references to pages such as Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages and Index Allocation Maps (IAM) pages.<br \/>\n<i>Oracle has two dramatically different ways for dealing with extent allocation (&#8220;dictionary managed&#8221; and locally managed&#8221;, and two ways of dealing with block usage (&#8220;freelist managed&#8221; and &#8220;bitmap managed&#8221;), but I won&#8217;t go into details.<\/i><\/li>\n<li><b>A single extent may hold pages from several objects<\/b>. This is a mechanism to avoid wasting space but is only used when an object is very small.<br \/>\n<i>In Oracle, all the blocks in an extent belong to a single object, but a single extent can be as small as two blocks, and in the most recent version of Oracle an object doesn&#8217;t allocate an extent until you insert some data into it<\/i>.<\/li>\n<\/ul>\n<p>A couple of thoughts that follow on from the above observations: there is an &#8220;allocation unit&#8221; for space at the operating system level in Windows, which defaults to 4KB on my little laptop. Clearly it would make sense to match the size of the allocation unit to the size to the database page. In fact, since I also noticed that SQL Server does a <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms191475.aspx\">read-ahead<\/a> for an entire extent when possible, there is a case for creating an allocation unit of 16KB, 32KB, or 64KB (if these are possible) and making sure you align the database page boundaries with the O\/S allocation unit boundaries.\u00a0 (On the down side, perhaps a larger extent size would result in a &#8220;read \/ fill \/ write&#8221; operation when SQL Server wanted to write a single 8KB block into a 32KB extent.)<\/p>\n<h1>Data Storage in Heaps and B-Trees<\/h1>\n<p>Moving on from extents to objects, just two clicks away at <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189051.aspx\">Table and Index Organization<\/a>, we find that:<\/p>\n<ul>\n<li><b>A table is always partitioned<\/b>, although it may (and probably usually does) have only one partition.<\/li>\n<li><b>Each partition consists of many logical components which may be Heaps or (clustered) B-trees<\/b> &#8211; the &#8220;HoBT&#8221;. In other words, someone using SQL Server is likely to think of a &#8220;table&#8221; as something which includes its indexes.<br \/>\n<i>In Oracle, the separation of tables and indexes is more clear-cut, to the extent that a partitioned table may have local or global indexes. In other words, a &#8220;single partition&#8221; of an Oracle index may cover every partition of a partitioned table, or be partitioned using a different strategy from the table partitioning.<\/i><\/li>\n<li><b>Each HoBT may hold three types of pages &#8211; &#8220;row data&#8221;, &#8220;LOB&#8221; or &#8220;row overflow&#8221;<\/b><br \/>\n<i>In Oracle, overflow data may be &#8220;chained&#8221;, holding rows too long for a single block, or &#8220;migrated&#8221;, holding rows that have been updated and have to move because there is not enough space in the current block for the new length of the row. Nevertheless, &#8220;overflow&#8221; is not treated differently from any other row data. The LOB columns for a table are, like indexes, given their own dedicated segments.<\/i><\/li>\n<\/ul>\n<p>With a little background, then, it&#8217;s time for some technical investigation. How well can I track where the data goes? In the previous article, I made a few comments about the differences in the amount of space used by Oracle and SQL Server for storing the same data; for the same million rows of data, a heap in Oracle used a about 8% more pages, while the non-clustered primary key index used about 7% less. Since data location is key to performance, I&#8217;m going to try and find out what they do differently.<\/p>\n<p>We&#8217;ll start with the data creation script shown in Listing 1 (see the previous article for the reasons behind the structure of this script). The script is based on a template that I use for generating all sorts of volumes and patterns of data, but in this case creates only 5,000 rows in a heap table with a non-clustered non-unique index:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE test_table\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 id INT ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 random_data INT ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 update_date DATE ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 vc_small VARCHAR(10) ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 vc_padding VARCHAR(100)\r\n\u00a0\u00a0\u00a0 ) ;\r\ngo\r\n\u00a0\r\nCREATE INDEX bt_i_rand ON test_table(random_data) ;\r\ngo\r\n\u00a0\r\nDECLARE @div INT = 50 ;\r\nDECLARE @mod INT = 100 ;\r\nDECLARE @limit INT = @div * @mod ;\r\nDECLARE @driver INT = 1000 ;\r\n\u00a0\r\nWITH\u00a0\u00a0\u00a0 generator\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS ( SELECT\u00a0\u00a0 1 AS id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0\u00a0 id + 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0\u00a0 generator\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0\u00a0 id &lt; @driver\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0 INSERT\u00a0 INTO test_table\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ABS(xx % @mod) ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REPLICATE('x', 100)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 ( SELECT TOP ( @limit )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @driver * ( g1.id - 1 ) + g2.id id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(NEWID() AS VARBINARY) xx\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0\u00a0\u00a0 generator g1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS JOIN generator g2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) iv\r\n\u00a0\u00a0\u00a0 OPTION\u00a0 ( MAXRECURSION 0, FORCE ORDER ) ;\r\n<\/pre>\n<p class=\"caption\">Listing 1: Creating <span class=\"STCodeinTextChar\">test_table<\/span> and populating it with data<\/p>\n<p>During my research into space allocation in SQL Server, I found a script that reported the space used by a table (and its indexes), by querying the <span class=\"STCodeinTextChar\">sys.allocation_units<\/span> view. I adapted this query for my own requirements, as shown in Listing 2 (the <span class=\"STCodeinTextChar\">STR<\/span> commands are there simply to make the output tidy in SQLCMD).<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 SUBSTRING(tab.name, 1, 16) table_name ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tab.object_id object_id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 prt.index_id index_id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUBSTRING(alu.type_desc, 1, 12) alloc_type ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 alu.data_space_id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STR(alu.total_pages, 8, 0) tot_pages ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STR(alu.used_pages, 8, 0) used_pages ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STR(alu.data_pages, 8, 0) data_pages\r\nFROM\u00a0\u00a0\u00a0 sys.schemas sch\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.tables tab ON tab.schema_id = sch.schema_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.partitions prt ON prt.object_id = tab.object_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.allocation_units alu\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON alu.container_id = prt.partition_id\r\nWHERE\u00a0\u00a0 sch.name = 'DBO'\r\nORDER BY tab.name ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 prt.partition_id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 prt.index_id ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 alu.allocation_unit_id\r\ngo\r\n<\/pre>\n<p class=\"caption\">Listing 2: Reporting on space allocation in <span class=\"STCodeinTextChar\">test_table<\/span><\/p>\n<p>After creating a new database (called <span class=\"STCodeinTextChar\">testdata<\/span>), and creating the one (heap) table and index shown in Listing 1, the output of the query was as follows:<\/p>\n<pre>table_name\u00a0\u00a0\u00a0 object_id\u00a0\u00a0 index_id\u00a0\u00a0 alloc_type \u00a0\u00a0data_space_id\u00a0 tot_pages\u00a0 used_pages\u00a0 data_pages\r\n---------------- ----------- ----------- ------------ ------------- --------- ---------- ---------\r\ntest_table\u00a0\u00a0\u00a0 2105058535\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN_ROW_DATA\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 81\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 80\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 79\r\ntest_table\u00a0\u00a0\u00a0 2105058535\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN_ROW_DATA\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 25\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 19\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 17\r\n<\/pre>\n<p>The <span class=\"STCodeinTextChar\">index_id<\/span> is zero for the heap table and 2 for the index. If I had created the index as a clustered index then there would be just one line in this report, with an <span class=\"STCodeinTextChar\">index_id<\/span> of 1.<\/p>\n<p>We have 81 pages allocated to the heap table, of which 79 are used for data, and 25 pages allocated to its index, of which 17 are used for data.<\/p>\n<p>In the previous article, I hinted that there appears to be no equivalent in heap tables for the &#8220;fill factor&#8221; that is an option for indexes (in Oracle, the <span class=\"STCodeinTextChar\">PCTFREE<\/span> parameter, which applies to tables and indexes, is effectively the same as &#8220;100 &#8211; fill factor&#8221;). Unfortunately, no-one took the hint so it&#8217;s time to find out the hard way how well filled are the table blocks, which means dumping the actual pages&#8230;<\/p>\n<h1>Investigating Table Blocks using DBCC IND and DBCC PAGE<\/h1>\n<p>I had to find a way of listing the pages allocated to the table, and then dump them. Tricky, but I had noticed that whenever you want to do anything subtle in SQL Server you need to use the DBCC command, so I tried a Google search for &#8220;DBCC PAGE&#8221;, and soon found a collection of interesting articles written by Paul Randall, the first of which gave me <a href=\"http:\/\/blogs.msdn.com\/b\/sqlserverstorageengine\/archive\/2006\/06\/10\/625659.aspx\">the syntax of both DBCC PAGE command<\/a>, following which I soon found the &#8220;DBCC IND&#8221; command.<\/p>\n<ul>\n<li>DBCC IND( database, table, index_id )<\/li>\n<li>DBCC PAGE ( database, file_id, block_id, level)<\/li>\n<\/ul>\n<p>I started by investigating the heap, with a call to <span class=\"STCodeinTextChar\">DBCC IND<\/span>, shown in Listing 3.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DBCC ind('testdata', 'test_table',0)\r\n<\/pre>\n<p class=\"caption\">Listing 3: Calling <span class=\"STCodeinTextChar\">DBCC IND<\/span><\/p>\n<p>Despite what may be suggested by the &#8220;ind&#8221;, this produces a list of the blocks in the table:<\/p>\n<pre>PageFID\u00a0\u00a0 PagePID\u00a0\u00a0\u00a0\u00a0 IAMFID \u00a0\u00a0IAMPID\u00a0\u00a0 \u00a0ObjectID\u00a0\u00a0\u00a0 IndexID\u00a0\u00a0 PartitionNumber \u00a0PartitionID\r\n------- ----------- ------ ----------- ----------- ----------- --------------- -----------------\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 154\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a072057594038779904\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 153\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 154\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594038779904\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 157\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 154\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594038779904\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 158\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 154\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594038779904\r\n\u00a0\r\niam_chain_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID\r\n-------------------- -------- ---------- ----------- ----------- ----------- -----------\r\nIn-row data \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\nIn-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\nIn-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\nIn-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n<\/pre>\n<p>This tells us that file 1, block 153 is the first data block in the table. So let&#8217;s dump it, as shown in Listing 4, and see what&#8217;s in it. Note that in SSMS, you will first need to turn on a trace flag (3604), using <span class=\"STCodeinTextChar\">DBCC<\/span> <span class=\"STCodeinTextChar\">TRACEON<\/span> <span class=\"STCodeinTextChar\">(3604)<\/span>.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DBCC PAGE (testdata,1,153,3)\r\n<\/pre>\n<p class=\"caption\">Listing 4: Dumping page 153<\/p>\n<p>The level 3 dump gives us a full symbolic dump, from which I&#8217;ve extracted a few lines:<\/p>\n<pre>PAGE: (1:153)\r\n\u00a0\r\n...{page header was here}\r\n\u00a0\r\nSlot 0 Offset 0x60 Length 124\r\n\u00a0\r\nRecord Type = PRIMARY_RECORD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Record Attributes =\u00a0 NULL_BITMAP VARIABLE_COLUMNS\r\nRecord Size = 124\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\nSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4\r\n\u00a0\r\nid = 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\nSlot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4\r\n\u00a0\r\nrandom_data = 32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\nSlot 0 Column 3 Offset 0x0 Length 0 Length (physical) 0\r\n\u00a0\r\nupdate_date = [NULL]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\nSlot 0 Column 4 Offset 0x0 Length 0 Length (physical) 0\r\n\u00a0\r\nvc_small = [NULL]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\nSlot 0 Column 5 Offset 0x18 Length 100 Length (physical) 100\r\n\u00a0\r\nvc_padding = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\r\n\u00a0\r\nSlot 1 Offset 0xdc Length 124\r\n\u00a0\r\nRecord Type = PRIMARY_RECORD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Record Attributes =\u00a0 NULL_BITMAP VARIABLE_COLUMNS\r\nRecord Size = 124\u00a0\u00a0 \r\n\u00a0... \r\nSlot 63 Offset 0x1ee4 Length 124\r\n\u00a0\r\nRecord Type = PRIMARY_RECORD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Record Attributes =\u00a0 NULL_BITMAP VARIABLE_COLUMNS\r\nRecord Size = 124\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n<\/pre>\n<p>The block held 64 rows (slots 0 to 63) and each one was 124 bytes, for a total of 7,936. Add a couple of bytes for each pointer in the table of &#8220;Offsets&#8221;, and the total gets to 8,064. Add the block header (which I believe is 96 bytes) and there are only 32 bytes of free space in the block.<\/p>\n<p>This is rather bad news if you&#8217;re hoping to update any of the data in the table. Unless there&#8217;s a &#8220;fill factor&#8221; for heap tables, this default 100% fill gives anyone using SQL Server a fairly compelling reason for using nothing but clustered indexes, unless there are some tables that aren&#8217;t going to need any updates.<\/p>\n<p>To demonstrate the problem of updates, let&#8217;s perform the following simple update of <span class=\"STCodeinTextChar\">test_table<\/span>, as shown in Listing 5.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">UPDATE\u00a0 test_table\r\nSET\u00a0\u00a0\u00a0\u00a0 vc_small = 'xxxxxxxxxx' ;\r\n<\/pre>\n<p class=\"caption\">Listing 5: Updating the test_table table<\/p>\n<p>This is what slot 3 (amongst others) looks like after I&#8217;ve executed the update and then re-run the level 3 dump command.<\/p>\n<pre>Slot 3 Offset 0x1f2 Length 9\r\n\u00a0\r\nRecord Type = FORWARDING_STUB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Record Attributes =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Record Size = 9\r\n\u00a0\r\nMemory Dump @0x4176C1F2\r\n\u00a0\r\n00000000:\u00a0\u00a0 040e0100 00010008 00\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0\u00e2\u00a0........\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nForwarding to\u00a0 =\u00a0 file 1 page 270 slot 8\u00a0\u00a0 \r\n<\/pre>\n<p>Slots 0, 1, and 2 show <span class=\"STCodeinTextChar\">vc_small = xxxxxxxxxx<\/span>, but between them the three rows have taken up 30 of the 32 bytes that I had estimated as free space, so there&#8217;s no room in the block for the update to slot 3 and the row has been copied to another block, leaving nothing but a pointer behind. The space freed up by this &#8220;row migration&#8221; allowed several more updates to take place in the block before it was full again, and I ended up with a total of 5 forwarding stubs in the block.<\/p>\n<p>To an Oracle database designer, if there is no specific need to use a clustered index, to group data in a pattern that&#8217;s different from the order that reflects the natural order of arrival of the data, then a heap table is the obvious structure for the table. However, when you implement a heap table in SQL Server, any updates to the data will have a negative impact on performance because of the way that rows will move to different blocks. This, presumably, is one reason why there seems to be such enthusiasm for creating a clustered index on an identity column, as it keeps the data collected in order of arrival but allows you to leave some space in each block for updates by specifying an appropriate fill factor.<\/p>\n<p>What about non-clustered indexes on heap tables? How do they cope with row movement? Here are the first few lines of output from using the <span class=\"STCodeinTextChar\">DBCC IND<\/span> command on our index:<\/p>\n<pre>PageFID PagePID\u00a0\u00a0\u00a0\u00a0 IAMFID IAMPID\u00a0\u00a0\u00a0\u00a0\u00a0 ObjectID\u00a0\u00a0\u00a0 IndexID\u00a0\u00a0\u00a0\u00a0 PartitionNumber PartitionID\r\n------- ----------- ------ ----------- ----------- ----------- --------------- ------------------\r\n1\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0156\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594038845440 \r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 156\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594038845440 \r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 172\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 156\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594038845440 \r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 173\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 156\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594038845440 \r\n\u00a0\r\niam_chain_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID\r\n------------------- -------- ---------- ----------- ----------- ----------- -----------\r\nIn-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\nIn-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 248\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\nIn-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\nIn-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 215\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 210\r\n<\/pre>\n<p>You&#8217;ll notice from the <span class=\"STCodeinTextChar\">PagePID<\/span> that the index and the table start off sharing the same extent (the table uses blocks 153, 154, 157, 158 and 159 and the index is using blocks 155 and 156).<\/p>\n<p>Block 156 is the IAM page (<span class=\"STCodeinTextChar\">PageType = 10<\/span>) for the index, and block 172 is the root block (<span class=\"STCodeinTextChar\">IndexLevel = 1<\/span>, so it&#8217;s not a leaf block). Lets&#8217; dump block 155, the first leaf block and take a look at the index entries:<\/p>\n<pre>FileId \u00a0\u00a0\u00a0\u00a0\u00a0PageId\u00a0\u00a0\u00a0 Row\u00a0 Level random_data (key) HEAP RID (key) KeyHashValue\u00a0\u00a0\u00a0 \r\n------ ----------- ------ ------ ----------------- -------------- ----------------\r\n\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 0x020100000100 (03003c52c4d8)\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 0x050100000100 (0600e2cd409d)\u00a0 \r\n...\r\n\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155\u00a0\u00a0\u00a0 143\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 0x0D0100000100 (1100bb6857c6)\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155\u00a0\u00a0\u00a0 144\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 0x990000000100 (9d00b21bdb69)\u00a0 \r\n\u00a0 \u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155\u00a0\u00a0\u00a0 145\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 0x9F0000000100 (a300191bb3a4)\u00a0 \r\n...\r\n\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155\u00a0\u00a0\u00a0 307\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6 0xBE0000000100 (c50036fde6b2)\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155\u00a0\u00a0\u00a0 308\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6 0xBF0000000100 (c600ed093b03)\u00a0 \r\n\u00a0\r\n(309 rows affected)\r\n<\/pre>\n<p>I&#8217;ve shown the start, the end, and a patch in the middle, from the list of leaf entries. You&#8217;ll notice that each entry consists of key value (my <span class=\"STCodeinTextChar\">random_data<\/span> column), a <span class=\"STCodeinTextChar\">HEAP<\/span> <span class=\"STCodeinTextChar\">RID<\/span> (row identifier), which seems to be the block address for the key. Since the <span class=\"STCodeinTextChar\">HEAP RID<\/span> is also labeled as &#8220;key&#8221;, I assume that it has been appended to the real key value as an aid to ordering the appearance of duplicates; certainly the RIDs appear to be sorted within key. Interestingly, the byte-ordering of the <span class=\"STCodeinTextChar\">HEAP RID<\/span> means that it could, in principle, introduce a greater degree of random I\/O than necessary &#8211; it looks as if rows with the same key in adjacent blocks may appear at non-adjacent positions in the index.<\/p>\n<p>I&#8217;ve printed a section from the index which shows a few occurrences of the <span class=\"STCodeinTextChar\">random_data<\/span> value of 3 because it&#8217;s reporting one of the rows in the table block I dumped: the <span class=\"STCodeinTextChar\">HEAP RID = 0x990000000100<\/span> matches file 1, block 153 (it looks like first 4 bytes is the block id, last two is the file id).<\/p>\n<p>There&#8217;s a problem though: slot 47 of the table block is the slot that holds the value 3, and I can&#8217;t see the reference to slot 47 anywhere in the block dump of the leaf block. Does this mean that index look-ups are only accurate to the table block, leaving SQL Server to search the whole table block for the matching key? It seems unlikely, especially when you think of the possible consequences this would have for finding migrated rows.<\/p>\n<p>Fortunately, by switching the dump level to 2 (raw dump row details) we can find the following in the file:<\/p>\n<pre>Slot 144, Offset 0x60, Length 16, DumpStyle BYTE\r\nRecord Type = INDEX_RECORD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Record Attributes =\u00a0 NULL_BITMAP\u00a0\u00a0\u00a0\u00a0 Record Size = 16\r\nMemory Dump @0x4146C060\r\n00000000:\u00a0\u00a0 16030000 00990000 0001002f 00020000 \u00e2\u00a0...........\/....\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\r\n<\/pre>\n<p>Note the &#8220;2f&#8221;; converted to decimal, that&#8217;s the number 47 that we needed for the slot identifier. You might also note that this version of the dump doesn&#8217;t include anything that looks like the <span class=\"STCodeinTextChar\">KeyHashValue<\/span>. Just as in Oracle, some of the dump files report information that is derived at the time of the dump and some of the real information is missing from the dump.<\/p>\n<p>Having tracked down the link from an index leaf to the table row, it takes just a few minutes to check that when a row is migrated from the heap table, the index entry is not updated; it points to the forwarding stub, rather than pointing to the new location of the row (Oracle adopts the same strategy).<\/p>\n<p>Tackling one structure at a time is probably enough. I shall take a look at clustered indexes (unique and non-unique) in the next article.<\/p>\n<h1>Conclusions<\/h1>\n<p>Unless I&#8217;ve missed something obvious, the main conclusion that I&#8217;ve reached about SQL Server and heap tables is that you cannot specify the equivalent of a fill factor for a heap table. This means that updates to heap tables will probably result in an unreasonable amount of row movement and so leading to an unreasonable performance overhead when you query the data, because you will have to follow a forwarding link to find the row. The absence of a fill factor is (by itself) enough of a threat to make heap tables in SQL Server fairly undesirable.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>We soon learn, in SQL Server, that heaps are a bad thing, without necessarily understanding how or why. Jonathan Lewis is an Oracle expert who doesn&#8217;t  like to take  such strictures for granted, especially when they don&#8217;t apply to Oracle. Jonathan discovers much about how SQL Server places data, and concludes from his experiments that heaps perform badly in SQL Server because you cannot specify a fill factor for them.&hellip;<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[5176,4149,4459,4150,4151,5230],"coauthors":[39048],"class_list":["post-923","post","type-post","status-publish","format-standard","hentry","category-learn","tag-jonathan-lewis","tag-learn-sql-server","tag-oracle","tag-sql","tag-sql-server","tag-the-great-divide"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/923","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=923"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/923\/revisions"}],"predecessor-version":[{"id":77203,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/923\/revisions\/77203"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=923"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}