DIR Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
HTML https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
DIR Return to: General
*****************************************************
#Post#: 146--------------------------------------------------
Update Statement
DIR By: Balachandar_kt_exceldbp
Date: April 14, 2015, 3:55 am
---------------------------------------------------------
If I update the top record of a table with around million
records(For Example, I will give a large value for a column of
datatype VARCHAR(MAX) which was NULL earlier.)
What actually happens internally?
As I understood rows/records are stored in continuous pages.
Does this affect all the pages as the content of the pages have
to be moved/replaced?
Does it cause any performance related issues?
#Post#: 147--------------------------------------------------
Re: Update Statement
DIR By: Yasub_exceldbp
Date: April 14, 2015, 4:33 am
---------------------------------------------------------
What do you mean by "Top Record"?
Can you please share an example query of what exactly you want
to do?
-Yasub
#Post#: 149--------------------------------------------------
Re: Update Statement
DIR By: Balachandar_kt_exceldbp
Date: April 14, 2015, 6:58 am
---------------------------------------------------------
Yasub,
Please take it as a 'first row' instead of 'Top record'.
Lets say I have the following Table
UserRecord
(
Userid BIGINT IDENTITY,
Name NVarchar(100),
Address NVarchar(Max),
.......
.......
......
ColN Nvarchar(100)
)
And imagine this table has one million rows.
In that case, All the rows/records are saved inside 'pages' in
contiguous manner.
Now, If I alter the first row, what actually happens
internally(inside pages)?
#Post#: 150--------------------------------------------------
Re: Update Statement
DIR By: Yasub_exceldbp
Date: April 14, 2015, 7:39 am
---------------------------------------------------------
If i understand correctly, You are asking the following: "What
happens to internal storage, when an update statement, changes
existing value to a very large one, that cannot be accommodated
on the current page."
Answer: Page Split, i.e. Another page is allocated to
accommodate the spilled over row and connected back to the
current page via a doubly linked list. This change does not
cause all other pages to be re arranged. The newly allocated
page, gets the header and footer information and points to the
next and previous page.
Contrarily , if your question, revolves around, updating the
Clustering Key (Around which the rest of the table is physically
ordered), Then the answer is: The pages are re-arranged to
accommodate the new clustering key order and all non clustered
indexes are re arranged as well. Hence it is ill advised to
Update a Clustering Key.
Please confirm if this answers your question.
-Yasub
#Post#: 152--------------------------------------------------
Re: Update Statement
DIR By: Balachandar_kt_exceldbp
Date: April 14, 2015, 7:46 am
---------------------------------------------------------
I got my answer. Thanks:-)
*****************************************************
Page 1 of 1