February 7, 2007 at 2:11 pm
I have a question about the how data is stored in a table. Basically I have a table which has developed over time to include several fields which are searched on frequently. These fields were added at the end of the table structure.
I believe it is best to have frequently searched fields of a static size early in the table therefore I want to move these fields to closer to the start of the table.
I can 'pick up' the fields in Enterprise Manager and physically move them to wherever I want so that the table structure is modified, howver, does this move the data itself or is it still stored in the original same place?
The only other way I can think of doing what I want to do is to make sure there is no activity on the db, create a new table with the structure I would like to have, copy the data from the old table into the new table, rename the old table and finally rename the new table with the original tables name. Obviously I'd have to appy any indexes etc to the new table afterwards.
Thanks in advance for any thoughts.
Windows 2008 Server | SQL Server 2008
February 8, 2007 at 12:23 am
I believe it is best to have frequently searched fields of a static size early in the table
Why do you think that?
You can change the order of columns in EntMan, but there's really little point. The order that columns are stored within the records in the data file are not necessarily the same as the order specified in the table creation.
Also, when SQL needs to read data, it reads pages at a time, not portions of rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2007 at 2:09 am
To be homest it's just something I've heard from several different sources as I've learned about sql over the last couple of years. I was told it would aid performance.
Windows 2008 Server | SQL Server 2008
February 8, 2007 at 2:54 am
I've never heard that myself. Got a source? I'd like to check it out.
afaik, the position of columns within the data page has no impact on speed of searching. If it does, it's likely to be near unmeasurable.
The order of columns in an index however makes a very large difference, as statistics are only maintained on the 1st column of an index (unless you manually create multi-column stats) and indexes can only be used for seeks if the query filters on a left-based subset of the index keys.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2007 at 2:58 am
I think you've been missled.
I thought about it and how one might prove this or otherwise, sql server will always read the row ( page ) and anything oft used would be in cache so on-disk structures don't count , if it was crititcal you'd probably use a covered index which wouldn't use the table and would also be in cache. Bookmarks, scans, indexes ? - possibly another urban myth ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 8, 2007 at 3:11 am
Thanks guys, I'm sure you're right. I'll see if I can find the articles I read and post links to them.
Windows 2008 Server | SQL Server 2008
February 8, 2007 at 3:11 am
Well, the on-disk page structure and the in memory page structure are identical (afaik) so it should be enough to take 2 very large tables, with lots of columns in different orders and do searches.
Searches would have to be on unindexed columns and all the columns would have to be fixed length and not nullable or (variable length or nullable) to ensure that the order on page is as you expect.
Not sure if there's any value to be gained, other than academic interest.
If I want a search on a column faster, I'll index it. It's easier and will have a much bigger impact than shuffling columns around.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2007 at 6:22 am
"I believe it is best to have frequently searched fields of a static size early in the table therefore I want to move these fields to closer to the start of the table."
This was true of DB2 at one time but has never been true for SQL Server.
"The order that columns are stored within the records in the data file are not necessarily the same as the order specified in the table creation."
This was true for SQL Server 7 and earlier versions where fixed length not null columns were always first. This is not true for SQL Server 2000.
If you are interested in the physical storage models used by Database Management Systems, search the internet for:
NSM - N-ary Storage Model also called the slotted page
DSM - Decomposition Storage Model
PAX - Partition Attributes Across
MBSM - Multiresolution Block Storage Model
SQL = Scarcely Qualifies as a Language
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply