Is it good practice to update (N)VARCHAR cols?

[BlackTulip]

Hi DB masters,

for a forum DB, for example you have declared your PostTitle as NVARCHAR(256), and occasionally your moderators update the titles, would you directly update the PostTitle?

done directly, what happens behinds the scene? how DB actually inserts the new value?

[BlackTulip]

okay, let me rephrase my question:

Does anybody know how Varchar is stored in a DB file?

[physics]

I'm a little confused still about your question. I guess you're using SQL Server? From my understanding NVARCHAR is just a VARCHAR field that can hold multilingual characters. In any case when a VARCHAR field value is changed the database engine just changes the appropriate text within the database file(s). So it should be fine to change the field value, unless the DB is designed such that the post is referenced by title from inbound links and not by some kind of primary key (such as an ID field). In that case you'd be stuck keeping the same title. Again, I'm confused a bit about your question but this is all I got right now.

[BlackTulip]

thanks physics, sorry for the confusion.

I think (!) what i really want to know is how MS SQL server internally manages variable lenght character types?

for example consider this:
- tblPosts has 20 million rows
- tblPosts has a Title column
- you want to run this query

UPDATE tblPosts
SET Title = 'this is a much longer than original title'
WHERE tblPosts.ID = 10057665

- so how does SQL server make room for the new title for that specific row?

- i am concernd about this, because if this is going to take too long to execute, then what happens if another query tries to insert a new post into the DB. it has to wait.

[edited by: BlackTulip at 7:03 pm (utc) on Dec. 12, 2006]

[TheNige]

Changing the text in one nvarchar column is not going to take any amount of time for SQL Server. The part of the query that would take long is actually finding the record to update with your where clause.

I would assume that your tblPosts.ID column is a primary key which means it has an index already which will make it easy and fast to find the record to update.

varchar columns are set to a length, such as 255 (or what ever you set it to), and I would assume that behind the scenes SQL server reserves a certian amount of space so that it can accomadate from 0-255 characters for that column. That space is already reserved so there will be no time penalty there.

If you were to change the schema for the table and change the column length from 255 to 600, then SQL Server may need to move data around to accomadate the larger record....check out Sql Server Performance web site and there is lots of info on how it works there.

[edited by: TheNige at 9:37 pm (utc) on Dec. 12, 2006]

[plumsauce]

Inside Microsoft Sql Server

Kalen Delaney, et al.

or Books on Line

[BlackTulip]

thanks guys for ideas and recommendations.

I haven't had a chance to ask the question at that website; I like to though, it seems very focused.

quite an intresting quote from "Inside Microsoft SQL server":

"Another potential performance issue with variable-length fields is the cost ofincreasing the size of a row on an almost full page. If a row with variable-length columns uses only part of its maximum length and is later updated to a longer length, the enlarged row might no longer fit on the same page. If the table has a clustered index, the row must stay in the same position relative to the other rows, so the solution is to split the page and move some of the rows from the page with the enlarged row onto a newly linked page. This can be an expensive operation. If the table has no clustered index, the row can move to a new location and leave a forwarding pointer in the original location."

so may be one alternative is to have a tblPosts_Changes_to_be_applied and push it into tblPosts when SQL server is not busy.

[TheNige]

When they say exensive...they are not talking about 5 second operations here...they are talking about sub one second times...."expensive" is a relative term to how long an "in-expensive" transaction would take.

How busy is your server going to be? Have you used SQL Server before? Have you done any testing?

You are talking about a simple operation of just doing a column update here...not rocket science.

[BlackTulip]

thanks for the clarification, TheNige.

...not rocket science.

it is easy to get carried away, you know :)