CODEWITHSUNDEEP
sqlsql-serversql-server-2016
ispiro
ispiro

Reputation: 27683

Is char useful if another column is varchar?

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...

Upvotes: 2

Views: 86

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

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.

Upvotes: 3

l33t
l33t

Reputation: 19966

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.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48810

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.

Upvotes: 0

Related Questions