Special Storage Series – III Unrestricted-Length Object Data I

Read Previous Article:

• Special Storage Series – I (Checking the 8K (8192) size of page)
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.B One row can span many row-overflow pages.
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.C Update statement on row-overflow pages data.

There are mainly three kind of LOB type

• Text
• Ntext
• Image

And if your table consists of any of these LOB type for your field, then by default the actual data is not stored on regular data pages, because for LOB data type SQL Server has separate pages for storing the data. Like row-overflow data, LOB data are stored in their own set of pages.

The Pages where the data of LOB data get stored are

PageType 3, LOB or row-overflow page, TEXT_MIXED

PageType4, LOB or row-overflow page, TEXT_DATA

For LOB column, SQL Server stores a 16 byte pointer in the data row that indicates where the actual data can be found i.e either PageType3 or PageType4. Although default behavior of SQL Server is to store the LOB data off the data row but SQL Server also allows you to change the default behavior of LOB data storage mechanism and allow LOB data to get stored in the data row itself if it is small enough.  

The collection of 8K pages that form the LOB data, not necessary located next to each other, they might be scattered but logically they are organized in a B-Tree Structure, so operation on LOB data type is very efficient.

Let’s take a look at how data of text type i.e. LOB type get stored in separate LOB Page, for this we are creating a table Tab_VKM_LOBTable with col1 as text data type.

create table Tab_VKM_LOBTable

(col1 text)

Command(s) completed successfully.

Let’s insert a record to the table.

insert into Tab_VKM_LOBTable

select REPLICATE('a',3000)

(1 row(s) affected)

Now we will introspect the table storage detail, ie in what all kind of pages, data of table are getting stored by querying System Catalog   sys.partitions and sys.allocation_units.

select OBJECT_NAME(p.object_id) as TableName,

p.partition_id,p.partition_number,p.rows,

au.total_pages as Pages,

au.type_desc as PageType

from sys.partitions p, sys.allocation_units au

where p.partition_id=au.container_id

and p.object_id=OBJECT_ID('Tab_VKM_LOBTable')

TableName        partition_id      partition_number rows Pages PageType

--------------------------------------------------- ----------------------

Tab_VKM_LOBTable 72057594041008128 1                1    2     IN_ROW_DATA

Tab_VKM_LOBTable 72057594041008128 1                1    2     LOB_DATA

Now from the output we can see there are two pages for each Page Type. We can fire DBCC IND query to and see the detail of each pages. Here we are using our custom table which we created in our last post (Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A) and store the output from DBCC IND to the custom table.

Here we are executing DBCC IND for ‘Tab_VKM_LOBTable' and storing the output in out custom made TAB_VKM_TablePages table.

insert into TAB_VKM_TablePages

exec ('dbcc ind (''master'',''Tab_VKM_LOBTable'',-1)')

select PageFID,PagePID,OBJECT_NAME(objectId) as [Table],

iam_chain_type,PageType from TAB_VKM_TablePages

PageFID PagePID Table            iam_chain_type PageType

------- ------------------------ -------------- --------

1       184     Tab_VKM_LOBTable In-row data    10

1       183     Tab_VKM_LOBTable In-row data    1

1       182     Tab_VKM_LOBTable LOB data       10

1       181     Tab_VKM_LOBTable LOB data       3

(4 row(s) affected)

From the output we can observe two pages for LOB Data and two pages for In-row data. The Data page hold a 16byte pointer to LOB Page. The descriptions for page type are

PageType =  1, Data Page

PageType =  2, Index Page

PageType =  3, LOB, Row-Overflow page, TEXT_MIXED

PageType =  4, LOB, Row-Overflow page, TEXT_DATA

PageType =  10, IAM Page

Here we can make out there are one data page and one IAM page for both in-row-data and LOB-data.

It is also possible to store LOB data in in-row data pages; we will see this in coming article from this series.

Code confidently in the course of your logic and other will bow their head to the direction of your code triumph.

Post Reference: Vikram Aristocratic Elfin Share

Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.C Update statement on row-overflow pages data.

Read Previous Article:

• Special Storage Series – I (Checking the 8K (8192) size of page)
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.B One row can span many row-overflow pages.

Here in this article we will take on with the previous article to see the affect of Update statement on data rows which are spanning in many row-overflow pages.

Let’s take a look at a table with each variable length data type column size of more than 8K bytes.

create table Tab_VKM_BigTable_III

(col1 varchar(3000),

col2 varchar(8000),

col3 varchar(8000),

col4 varchar(8000))

Command(s) completed successfully.

Our table is ready whose maximum record size to store is greater than 27000 byte. Let’s insert data with size more than 27K byte.

insert into Tab_VKM_BigTable_III

select REPLICATE('a',3000),REPLICATE('b',8000),

       REPLICATE('c',8000),REPLICATE('d',8000)

(1 row(s) affected)

Now we have record whose length is more than 27000 bytes. You can query the table to see the record

select * from  Tab_VKM_BigTable

Now we will introspect the table storage detail, i.e. In what all kind of page, data of table are getting stored by querying System Catalog   sys.partitions and sys.allocation_units.

sys.partitions will hold a single entry irrespective of whether you apply partitioning on table or not. It will give Partition Id and Partition Number and number of rows in the table. Partiton number is 1 for Non Partitioned   Table.

sys.allocation_units will hold information about how data are allocated on various pages like page Type, number of Pages etc.

select OBJECT_NAME(p.object_id) as TableName,

p.partition_id,p.partition_number,p.rows,

au.total_pages as Pages,

au.type_desc as PageType

from sys.partitions p, sys.allocation_units au

where p.partition_id=au.container_id

and p.object_id=OBJECT_ID('Tab_VKM_BigTable_III')

TableName           partition_id         parti_no rows Pages PageType

------------------- -------------------- -------- ---- ----- -----------------

Tab_VKM_BigTable_II 72057594042449920    1         1   2     IN_ROW_DATA

Tab_VKM_BigTable_II 72057594042449920    1         1   4     ROW_OVERFLOW_DATA

(2 row(s) affected)

Now from the output we can see there are four pages for row-overflow, one for row-overflow IAM and three for data that doesn’t fit in regular row.

Now we will fire Update statement on col2 of the table Tab_VKM_BigTable_III and reduce the size of the column data to 3000 from 8000 byte.

update Tab_VKM_BigTable_III

set col2 = REPLICATE('b',3000)

(1 row(s) affected)

Let’s query now sys.partitions and sys.allocation_units to see the effect of Update Query

select OBJECT_NAME(p.object_id) as TableName,

p.partition_id,p.partition_number,p.rows,

au.total_pages as Pages,

au.type_desc as PageType

from sys.partitions p, sys.allocation_units au

where p.partition_id=au.container_id

and p.object_id=OBJECT_ID('Tab_VKM_BigTable_III')

TableName           partition_id         parti_no rows Pages PageType

------------------- -------------------- -------- ---- ----- -----------------

Tab_VKM_BigTable_II 72057594042449920    1         1   2     IN_ROW_DATA

Tab_VKM_BigTable_II 72057594042449920    1         1   3     ROW_OVERFLOW_DATA

(2 row(s) affected)

Here we can see one ROW_OVERFLOW_DATA is removed so now it 3 row-overflow data pages where one is IAM and other two are used to store data.

Lets now again update the col2 and col3 to 1 byte data and check the data span over Pages.

update Tab_VKM_BigTable_II

set col2 = REPLICATE('b',1)

,col3 = REPLICATE('c',1)

Let’s query now sys.partitions and sys.allocation_units to see the effect of Update Query

select OBJECT_NAME(p.object_id) as TableName,

p.partition_id,p.partition_number,p.rows,

au.total_pages as Pages,

au.type_desc as PageType

from sys.partitions p, sys.allocation_units au

where p.partition_id=au.container_id

and p.object_id=OBJECT_ID('Tab_VKM_BigTable_III')

TableName           partition_id         parti_no rows Pages PageType

------------------- -------------------- -------- ---- ----- -----------------

Tab_VKM_BigTable_II 72057594042449920    1         1   2     IN_ROW_DATA

Tab_VKM_BigTable_II 72057594042449920    1         1   2     ROW_OVERFLOW_DATA

Here we can see one ROW_OVERFLOW_DATA is removed so now it 2 row-overflow data pages where one is IAM and other one is used to store data

Conclusion: Update statement on row-overflow pages data cause the number of pages reduced or increased.

Programmer just wanted to play with code; this is the highest honor that can ever be paid and its mind blowing.

Post Reference: Vikram Aristocratic Elfin Share

Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.B One row can span many row-overflow pages.

Read Previous Article:

Special Storage Series – I (Checking the 8K (8192) size of page)

Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A

Here in this article we will take on with the previous article to see how one row can span many row-overflow pages if it contains many large variable length fields in the table. Lets take a look at a table with each variable length data type column size of more than 8K bytes.

create table Tab_VKM_BigTable_II

(col1 varchar(3000),

col2 varchar(8000),

col3 varchar(8000),

col4 varchar(8000))

Command(s) completed successfully.

Our table is ready whose maximum record size to store is greater than 27000 byte. Let’s insert data with size more than 27K byte.

insert into Tab_VKM_BigTable_II

select REPLICATE('a',3000),REPLICATE('b',8000),

       REPLICATE('c',8000),REPLICATE('d',8000)

(1 row(s) affected)

Now we have record whose length is more than 27000 bytes. You can query the table to see the record

select * from  Tab_VKM_BigTable

Now we will introspect the table storage detail, i.e. In what all kind of page, data of table are getting stored by querying System Catalog   sys.partitions and sys.allocation_units.

sys.partitions will hold a single entry irrespective of whether you apply partitioning on table or not. It will give Partition Id and Partition Number and number of rows in the table. Partiton number is 1 for Non Partitioned   Table.

sys.allocation_units will hold information about how data are allocated on various pages like page Type, number of Pages etc.

select OBJECT_NAME(p.object_id) as TableName,

p.partition_id,p.partition_number,p.rows,

au.total_pages as Pages,

au.type_desc as PageType

from sys.partitions p, sys.allocation_units au

where p.partition_id=au.container_id

and p.object_id=OBJECT_ID('Tab_VKM_BigTable_II')

TableName           partition_id         parti_no rows Pages PageType

------------------- -------------------- -------- ---- ----- -----------------

Tab_VKM_BigTable_II 72057594042449920    1         1   2     IN_ROW_DATA

Tab_VKM_BigTable_II 72057594042449920    1         1   4     ROW_OVERFLOW_DATA

(2 row(s) affected)

Now from the output we can see there are four pages for row-overflow, one for row-overflow IAM and three for data that doesn’t fit in regular row. We can fire DBCC IND query to and see the detail of each pages.

Here we will be using the table TAB_VKM_TablePages which we have created in our last post (Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A) to store the result of DBCC IND.

insert into TAB_VKM_TablePages

exec ('dbcc ind (''Sparsh'',''Tab_VKM_BigTable_II'',-1)')

(6 row(s) affected)

Now let’s query the TAB_VKM_TablePages to see the DBCC IND data for the table Tab_VKM_BigTable_II

select PageFID,PagePID,OBJECT_NAME(objectId) as [Table],

iam_chain_type,PageType from TAB_VKM_TablePages

PageFID PagePID Table               iam_chain_type     PageType

------- ---------------------------------------------- --------

1       369     Tab_VKM_BigTable_II In-row data        10

1       368     Tab_VKM_BigTable_II In-row data        1

1       357     Tab_VKM_BigTable_II Row-overflow data  10

1       356     Tab_VKM_BigTable_II Row-overflow data  3

1       358     Tab_VKM_BigTable_II Row-overflow data  3

1       359     Tab_VKM_BigTable_II Row-overflow data  3

(6 row(s) affected)

From the output we can observe there are two pages for In-Row Data, one is IAM and another is data page. And there are four pages for Row-Overflow Data, one is IAM page and other three are used to store the data.

Conclusion: One row can span many row-overflow pages.

It’s fun to be around Code that has wonderful potency to bring life to tranquil object.

Post Reference: Vikram Aristocratic Elfin Share

Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A

Read Previous Article: Special Storage Series – I (Checking the 8K (8192) size of page)

There are two special formats to store data that doesn’t fit on 8k data page that is if row size increases to more than 8KB.

Resticted-Length Object Data

UnResticted-Length Object Data

Here in this article we will see Restricted-Length Object Data, this is one way to store the data in variable length column when the size of data crosses the limit of 8060 bytes. SQL Server stores these over exceeded data in special Row-Overflow pages.

Let’s take a look at a table with variable length data type and size of more than 8k; here we are creating a table with rows that have a maximum defined length that is much longer than 8060 byte.

create table Tab_VKM_BigTable

(col1 varchar(3000),

col2 varchar(3000),

col3 varchar(3000),

col4 varchar(3000))

Command(s) completed successfully.

Our table is ready whose maximum record size to store is greater than 8060 byte. Lets insert data with size more than 8K byte.

insert into Tab_VKM_BigTable

select REPLICATE('a',2250),REPLICATE('b',2250),

       REPLICATE('c',2250),REPLICATE('d',2250)

(1 row(s) affected)

Now we have record whose length is more than 8060 bytes. You can query the table to see the record

select * from  Tab_VKM_BigTable

Now we will introspect the table storage detail, ie in what all kind of pages data of table are getting stored by querying System Catalog   sys.partitions and sys.allocation_units.

sys.partitions will hold a single entry irrespective of whether you apply partitioning on table or not. It will give Partition Id and Partition Number and number of rows in the table. Partiton number is 1 for Non Partitioned   Table.

sys.allocation_units will hold information about how data are allocated on various pages like page Type, number of Pages etc.

select OBJECT_NAME(p.object_id) as TableName,

p.partition_id,p.partition_number,p.rows,

au.total_pages as Pages,

au.type_desc as PageType

from sys.partitions p, sys.allocation_units au

where p.partition_id=au.container_id

and p.object_id=OBJECT_ID('Tab_VKM_BigTable')

TableName        partition_id      parti_no rows Pages PageType

------------------------------------------- ---- ----- ----------------------

Tab_VKM_BigTable 72057594042056704    1      1    2    IN_ROW_DATA

Tab_VKM_BigTable 72057594042056704    1      1    2    ROW_OVERFLOW_DATA

(2 row(s) affected)

Now from the output we can see there are two pages for each Page Type. We can fire DBCC IND query to and see the detail of each pages.

Let’s create a table which will hold the data from DBCC IND command.

create table TAB_VKM_TablePages

(PageFID tinyint,

PagePID int,

IAMDID tinyint,

IAMPID int,

ObjectID int,

IndexID tinyint,

PartitionNumber tinyint,

PartitionID bigint,

iam_chain_type varchar(30),

PageType tinyint,

IndexLevel tinyint,

NextPageFID tinyint,

NextPagePID int,

PrevPageFID tinyint,

PrevPagePID int)

Command(s) completed successfully.

Here we are executing DBCC IND for ‘Tab_VKM_BigTable' and storing the output in out custom made TAB_VKM_TablePages table.

insert into TAB_VKM_TablePages

exec ('dbcc ind (''Sparsh'',''Tab_VKM_BigTable'',-1)')

select PageFID,PagePID,OBJECT_NAME(objectId) as [Table],

iam_chain_type,PageType from TAB_VKM_TablePages

 PageFID PagePID Table            iam_chain_type     PageType

------- ------- ----------------------------------- --------

1       353     Tab_VKM_BigTable In-row data        10

1       352     Tab_VKM_BigTable In-row data        1

1       343     Tab_VKM_BigTable Row-overflow data  10

1       342     Tab_VKM_BigTable Row-overflow data  3

(4 row(s) affected)

From the output we can observe two pages for In-row data and two pages for  Row-overflow data. The description for page type are

PageType =  1, Data Page

PageType =  2, Index Page

PageType =  3, LOB, Row-Overflow page, TEXT_MIXED

PageType =  4, LOB, Row-Overflow page, TEXT_DATA

PageType =  10, IAM Page

Here we can make out there are one data page and one IAM page for both in-row-data and Row-overflow-data.

People can’t position Programmer exclusive of logic, brainy their created codes are; brainy they are ;)

Post Reference: Vikram Aristocratic Elfin Share

SQL Server 2008 new Datetime function TODATETIMEOFFSET

Change the time zone offset of the current date and time but unlike Switchoffset, it wont change the time, just it change the offset value ie GMT it changes. Lets take some example to understand this.

 TODATETIMEOFFSET ( expression , time_zone )

Lets do some query to understand this

select sysdatetimeoffset() as Current_Time_With_OffsetValue

Current_Time_With_OffsetValue

----------------------------------

2013-12-19 17:32:48.2740254 +05:30

(1 row(s) affected)

So the current time with offset is 2013-12-19 17:32:48.2740254 +05:30

Let’s apply TODATETIMEOFFSET as well as SWITCHOFFSET to check what we will get

TODATETIMEOFFSET

select todatetimeoffset(sysdatetimeoffset(),'-04:00') as Changed_Time

Changed_Time

----------------------------------

2013-12-19 17:35:32.3950254 -04:00

SWITCHOFFSET

select switchoffset(sysdatetimeoffset(),'-04:00') as Changed_Time

Changed_Time

----------------------------------

2013-12-19 08:05:32.3950254 -04:00

Here we can see the with TODATETIMEOFFSET only the offset (GMT) gets changed to -04:00 where as in case of SWITCHOFFSET time and  offset (GMT) both gets changed.

Let’s take one few more example

TODATETIMEOFFSET

select todatetimeoffset(sysdatetimeoffset(),'+12:00') as Changed_Time

Changed_Time

----------------------------------

2013-12-19 17:40:26.3130254 +12:00

SWITCHOFFSET

select switchoffset(sysdatetimeoffset(),'+12:00') as Changed_Time

Changed_Time

----------------------------------

2013-12-20 00:10:26.3130254 +12:00

Again we can see the with TODATETIMEOFFSET only the offset (GMT) gets changed to +12:00 where as in case of SWITCHOFFSET date, time and  offset (GMT) all gets changed.

NOTE: So with TODATETIMEOFFSET you can change the offset only without affecting your date and time where as in case of SWITCHOFFSET you can change date time and offset according to the GMT passed as an argument to SWITCHOFFSET.

 Every value code is an artist.  But to remain an artist revise required.

Post Reference: Vikram Aristocratic Elfin Share

SQL Server 2008 new Datatime function SWITCHOFFSET

Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. It also change the given time to the offset passed as parameter unlike TODATETIMEOFFSET

 SWITCHOFFSET ( DATETIMEOFFSET, time_zone )

Lets do some query to understand this

select sysdatetimeoffset() as Current_Time_With_OffsetValue

Current_Time_With_OffsetValue

----------------------------------

2013-12-18 23:16:46.0192745 +05:30

So the current time with offset is 2013-12-18 23:16:46.0192745 +05:30

Let’s apply SWITCHOFFSET to check what we will get

select switchoffset(sysdatetimeoffset(),'-04:00') as Changed_Time

Changed_Time

----------------------------------

2013-12-18 13:53:16.7063393 -04:00

Here we can see the current time and zone changed to 13:53:16.7063393 -04:00

Let’s take one few more example

select switchoffset(sysdatetimeoffset(),'+04:00') as Changed_Time

Changed_Time

----------------------------------

2013-12-18 21:55:38.6735346 +04:00

The current time and zone changed to 21:55:38.6735346 +04:00

Switchoffset :It does not only change the offset. It also changes the time with the appropriate time at Time zone specified in offset. I will take both this in deeper view in coming article.

Set off jogging with your logic and carry out an elegant and community will call it mastery code.

Post Reference: Vikram Aristocratic Elfin Share