2

If a table has one column of variable length, will there be any gain in other columns being of fixed length, or will their advantage be cancelled because rows will not have a fixed length and therefore the SQL application won't be able to calculate the location of a row easily?

If it matters, I'm asking about SQL Server.

EDIT: Duplicate?? Wow. Just wow...

ispiro
  • 26,556
  • 38
  • 136
  • 291

3 Answers3

3

It makes no difference at all from the perspective of addressing records. Do not worry about SQL Server finding the position of a row. Databases do not assume that rows are arrays with items of fixed length.

Instead, rows are stored on data pages. The data page has a header. The header has the offset of rows on the page. There is no multiplication by a fixed length for finding where a record starts.

Well, technically, the row offsets are not part of the header, but part of the footer. That's a nuance. Here is a good description of the page layout. The topic is also covered in the documentation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! So _the_ reason for using char (or nchar) is for space only (in those cases where it does in fact save space) and not indexing, I guess. – ispiro May 24 '18 at 20:50
  • 1
    @ispiro . . . The primary concern should be which type better represents the data you want to represent. – Gordon Linoff May 24 '18 at 20:50
  • Yes. But I can now decide whether to use a simple string representation or some kind of code to conserve space (and I thought, speed as well). Kind of an _enum_ ("enumeration". I don't know if it's used by everyone or only in C#). And it seems that there is no simple way of saving enums. – ispiro May 24 '18 at 20:56
  • @ispiro . . . The SQL equivalent of an "enum" is a foreign key into a reference table. Not exactly the same. I would say that your efforts at optimization are premature. Represent the data as best fits your needs. If your data grows really big or you expect other performance issues, then you can revisit the decisions based on something that works. – Gordon Linoff May 24 '18 at 20:56
  • Thanks. `The SQL equivalent of an "enum"...` - Yes. That's why I wrote "there is no _simple_ way". I don't want to deal with that. – ispiro May 24 '18 at 20:58
  • Incorrect. Using fixed lengths, the engine can calculate and jump ahead on the *pages*. – l33t May 24 '18 at 21:18
  • @l33t . . . Please provide a reference for your comment. – Gordon Linoff May 24 '18 at 21:21
  • https://social.msdn.microsoft.com/Forums/en-US/47bfe0ed-daf0-4cd3-bfbd-ca0c17044853/which-is-best-in-performance-char-or-varchar?forum=sqlgetstarted&forum=sqlgetstarted – l33t May 24 '18 at 21:22
  • @l33t . . . I have no idea what that answer is referring to. I am not aware that SQL Server does such calculations differently for "fixed length" rows as opposed to "variable length" rows. The suggestion of a breakpoint of 20 is also completely arbitrary. Another answer that mentions indexes is more *a propos*, but this is not related to this question. You should read Microsoft's documentation on pages (referred to in the answer) if you are actually interested in this subject. – Gordon Linoff May 24 '18 at 21:26
  • One difference is that the column width will always be in RAM whereas the variable length may or may not be in RAM (e.g. if the index grows very large). – l33t May 24 '18 at 21:45
  • @ispiro . . . The length of a variable column will always be on the original data page, in the first two or four bytes of the column value. You can try again. – Gordon Linoff May 25 '18 at 02:59
  • My latest comment is accurate. I'm not referring to one single data page, but *all* available data pages (which may or may not be in RAM). – l33t May 31 '18 at 12:34
0

The implementation details are taken care of by the database itself. You need to specify what you want, and SQL Server will stored and retrieve them appropriately.

Don't worry about that.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
0

Yes, there will be a gain if you stick to CHAR columns. Consider page splits and fragmentation. I have seen primary keys with 99% fragmentation because of page splits on VARCHAR columns.

l33t
  • 18,692
  • 16
  • 103
  • 180