Aristocratic Elfin Share

Sunday, December 22, 2013

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.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.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

Thursday, December 19, 2013

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

Tuesday, December 17, 2013

Getting the current Time from any Time Zone

Your application might be hosted in a particular location but accessed from many location, and when user of your application ask to retrieve date specific transaction that time the time zone will be considered by default as the time zone of Hosted server so user will be getting wrong date in this case.

This is a common problem which is faced by Developer to there are many way to tackle this issue. With the advent of SQL Server 2008 and the new data type TIMEOFFSET feature, this is bit of more easy to tackle this problem. Lets see how we can arrive to a solution with the new feature of SQL Server 2008

Creating Table TimeOffsets which will store Location Name and the TimeOffset (GMT) value for corresponding location.

create table TimeOffsets

(Location varchar(100) primary key,

TimeOffset varchar(10))

Find the insert script at the end of this article.

Now here we are creating a function which will return a table with the current time of your location and the time for asked location.

This function is taking two argument @myplace and @targetplace for which it will give the current date for @myplace location and @targetplace location. It uses

Todatetimeoffset which will changes the current offset only offset which we defined
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.

--create the function

create function getgeographicaltime

(@myplace as varchar(100),@targetplace as varchar(100)

)

returns @timeatbothplace table(yourtime datetimeoffset,targettime datetimeoffset)

begin

declare @targetplaceoffset varchar(100)

declare @myplaceoffset varchar(100)

declare @todaysdatetime datetime2

declare @todaysdatetimeoffset datetimeoffset

declare @returndatetimeoffset datetimeoffset

select @todaysdatetime = getdate()

select @myplaceoffset =

[timeoffset] from timeoffsets where [location] = @myplace

select @targetplaceoffset =

[timeoffset] from timeoffsets where [location] = @targetplace

select @todaysdatetimeoffset =

todatetimeoffset (@todaysdatetime, @myplaceoffset)

select @returndatetimeoffset =

switchoffset(@todaysdatetimeoffset,@targetplaceoffset)

insert into @timeatbothplace(yourtime,targettime)

select @todaysdatetimeoffset,@returndatetimeoffset;

return

end

Lets now try to call this function to see our desired result, here we are passing @myplace as ‘DELHI’ and @targetplace as ‘LAS VEGAS’. Let’s fire this query to see the result.

Select * from GetGeographicalTime('Delhi','Las Vegas')

YourTime TargetTime

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

2013-12-18 13:11:00.3300000 +05:30 2013-12-17 23:41:00.3300000 -08:00

Here we can see we got time for LAS Vegas corresponds to DELHI time zone. So when it is 2013-12-18 13:11:00 at Delhi, Vegas evidence 2013-12-17 23:41.

INSERT Script for TimeOffset table