| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-06-11 : 08:33:13
|
| I have an ASP feedback form which supplies about 15 entries which range from zero input to maybe 2000 characters. I'm considering a simple table structure.CREATE TABLE MyTable (Ilovesurrogatekeys INT PRIMARY KEY IDENTITY (1,1) ,FeedbackID INT NOT NULL,Feedback VARCHAR (2000) NOT NULL)I've read in BOL that there are data storage efficencies designed into SQL which ensure that 2000 bytes are not allocated for 1 byte entries in VARCHAR fields.I'm curious about how SQL databases achieve storage efficency without sacrificing performance. I imagine fixed-width columns are a relatively simple matter, but variable columns? I leave these BOL passages pondering possible DB layout solutions.. "let's see, the VARCHAR column could be a pointer to a string heap, that would make comparing strings relatively efficent". Is there any documentation on the techniques used to layout tables in SQL DB pages?Sam |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-11 : 09:25:50
|
| Sam,You lost me...BOL:varchar[(n)]Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.What's the issue? Image and text are stored as pointers (to my knowledge...the thimble is full now)..not varchar or char...Brett8-) |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-11 : 10:31:38
|
| I'm left wondering how SQL stores VARCHAR in the database and can move row-to-row quickly. Gotta be pointers doesn't it?Sam |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-11 : 15:47:46
|
| In syscolumns there is an offset field for each column. Fixed length columns will be positive and variable length negative.In the data page for the row the fixed length data is all at the start. Part of this is a pointer to the variable length data number of columns.The columns are all accessed via the offset field in order.After the number of variable length columns there is an array of pointers to the end of the variable length column data which is used to calculate the data for that column.This array is part of the fixed length data as it is the same for every row in the table.The rows are accessed via the rowid and slot number on the page.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 06/11/2003 15:48:59 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-11 : 16:09:31
|
| Thanks Nigel.How can I learn more about this ?? BOL?Sam |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-11 : 16:15:53
|
| Inside sql server, dbcc page, dumping an mdf, searches.Don't think there's too much about it in bol.Maybe I'll write an article (or you can).I was writing a VB app to get table data out of an mdf - unfinished like most things I don't get paid for (and not really very useful).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 06/11/2003 16:18:33 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-11 : 16:20:12
|
| Not much use, but it's a curiousity thing I guess.Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-13 : 07:59:51
|
| There are some entries in BOL under "pages", check all of them out. They do only cover it briefly, if you want more detail, Nigel's suggestions are right on (Inside SQL Server is probably the best source) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-23 : 16:33:13
|
| I posted some stuff on this.Might get round to doing more but I doubt it.http://www.nigelrivett.net/PageStructure.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-23 : 16:41:36
|
I haven't read a good memory dump since DOS! I can remember when memory dumps were standard procedure for debugging IBM's 370. I once wrote an abort event handler that would traceback dumping the code in both hex and assembly through the call stack.Life is good. Thanks for the sample code. I had no idea this was possible with SQL alone. Sam |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-23 : 17:11:19
|
| Now if you know how to read a binary file in t-sql that woudl be good - maybe the stream object.Especially one that is locked by the os (the .mdf for instance).Guess where that's going.Yep - the days when you expect a few lines of output and get a 100 page core dump.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 06/23/2003 17:15:26 |
|
|
|
|
VARCHAR storage efficency