Salakhetdinov Shamil
mcp2004 at mail.ru
Tue Oct 9 06:39:40 CDT 2012
Hi John -- But Varchar() could behave as/be handled as BLOB if you'd use Varchar(MAX)? - do you use such a T-SQL DDL construct? http://msdn.microsoft.com/en-us/library/ms187752.aspx Anyway AFAIU if you have a clustered index and this clustered index field's value isn't changed (as in your case) then the record can't be moved to the new page, or I'd better write record can be moved to new page only if record's page get split and record still doesn't fit the first part of the split page - then it gets moved to the second part of that split page and some page's records could be moved to the third part. If a record's total length exceeds 8KB then its tail go moved to Overflow extent. And BLOBs are always stored on LOB pages/extents - http://msdn.microsoft.com/en-us/library/ms189051(v=sql.105).aspx ? Please correct me if I'm wrong and I'm missing something obvious. Thank you. -- Shamil Tue, 09 Oct 2012 07:08:00 -0400 от jwcolby <jwcolby at colbyconsulting.com>: > > > > >True, no BLOBs, though many of the fields are Varchar(). > > John W. Colby > Colby Consulting > > Reality is what refuses to go away > when you do not believe in it > > On 10/9/2012 2:45 AM, Salakhetdinov Shamil wrote: > > Hi Jim -- > > > > But JC's records have a clustered index and do not have(?) BLOBs. > > > > http://msdn.microsoft.com/en-us/library/ms177443(v=sql.105).aspx > > > > So they should be updated in place if there exists enough free space on a record page to adapt to its increased total record length or record's page should be split... > > > > That could be ROW_OVERFLOW_DATA Allocation Unit where part of records go in the case of updated record length exceed 8KB? > > > > http://msdn.microsoft.com/en-us/library/ms189051(v=sql.105).aspx > > > > http://msdn.microsoft.com/en-us/library/ms190969(v=sql.105).aspx > > > > The speed of update (insert/update/delete) is important but secondary - the speed of retrieval "is a king"... > > > > Please correct me if I'm wrong and I'm missing something obvious. > > > > Thank you. > > > > -- Shamil > > > > > > Mon, 8 Oct 2012 07:46:05 -0700 от "Jim Lawrence" <jlawrenc1 at shaw.ca>: > >> > >> > >> > > > > > >> > > > > > > > >> I believe MS SQL just sticks the records on the end of the table. > >> > >> > > This is of course done for speed. It is much easier to just add a record at > >> > > the end of the table than try to insert it in the middle, replacing a > >> > > deleted record's position. (This is of course how old FoxPro and MS Access > >> > > works and I am assuming MS SQL works basically, just the same...) > >> > >> > > Jim > >> > >> > > -----Original Message----- > >> > > From: dba-sqlserver-bounces at databaseadvisors.com > >> > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby > >> > > Sent: Monday, October 08, 2012 3:37 AM > >> > > To: Sqlserver-Dba; Access Developers discussion and problem solving > >> > > Subject: [dba-SQLServer] How does SQL Server do updates > >> > >> > > Every month I perform a process where name / address records are extracted > >> > > from SQL Server, updated > >> > > by a third party app and then only changed data is updated back into SQL > >> > > Server. I take changed > >> > > addresses and write them to an 'old address' table and then literally update > >> > > the existing record > >> > > with the changes. > >> > >> > > The address table has an integer PK (which never changes) which came from > >> > > and is related to another > >> > > table 1 to 1, and that PK is used by itself in a clustered index. There are > >> > > other indexes for > >> > > FName, LName Addr ect, my hash fields and so forth. > >> > >> > > So, I pull the updated address info into a custom SQL Server DB created > >> > > on-the-fly for this purpose, > >> > > and then only the changes are updated back into the live database. > >> > >> > > The PK and Name fields never change but the address fields do change and > >> > > other fields which capture > >> > > information about the address changes also change. The hash fields are > >> > > updated (in the temp db) and > >> > > written back into live etc. > >> > >> > > I am trying to visualize what goes on behind the scenes in SQL Server in the > >> > > live database. Most of > >> > > the data fields are varchar(), the hash fields are varbinary(200). I assume > >> > > that the data is moved > >> > > around inside of the dbf file, i.e. moved out to new space on the end if it > >> > > can no longer fit in the > >> > > originally allocated space. IOW of the town changed from 'Yuma' to 'Los > >> > > Angeles', something has to > >> > > give. > >> > >> > > So as things move around, does SQL Server actually go back and reuse the > >> > > pieces and parts of empty > >> > > space inside of the file? Or does it just keep expanding the file and doing > >> > > everything out at the > >> > > end of the file. > >> > >> > >> > > So > >> > >> > > _______________________________________________ > dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > >