March 12, 2015 at 11:40 am
if I have a table with these columns:
ID (uniqueidentifier)
Create_date (Nvarchar 100)
Created (nvarchar (max))
how much space does a row takes? ----this is just a sample.
March 12, 2015 at 12:55 pm
Dan121 (3/12/2015)
if I have a table with these columns:ID (uniqueidentifier)
Create_date (Nvarchar 100)
Created (nvarchar (max))
how much space does a row takes? ----this is just a sample.
It depends. The UNIQUEIDENTIFIER is easy, it's 16 bytes. Then the NVARCHAR(100) is also easy. It's up to 200 bytes, but it depends on the data being stored in it. Then the hard one. NVARCHAR(MAX). MAX values are stored partially in the row and partially outside of it, depending on the size. It can store up to 8000 bytes in the row. But... Here's the kicker, there's a row limit of 8060. So that's going to be the most you can get anyway. So, up to 8060 in the row and anything else in the MAX, up to 2gb, out on disk.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2015 at 1:26 pm
Let me point out that using a char-based column for a date is a very bad idea. Not only does it take more space but it will get garbage in it that is not a valid date.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 12, 2015 at 3:13 pm
ScottPletcher (3/12/2015)
Let me point out that using a char-based column for a date is a very bad idea. Not only does it take more space but it will get garbage in it that is not a valid date.
Good catch. I didn't even look at the column names.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2015 at 12:22 pm
Scott, my concern is a size of a row. As Grant said UNIQUEIDENTIFIER is 16 bytes. if I have 503 columns with datatype 'UNIQUEIDENTIFIER' and rows are empty, will all they fit in a row ? (8016/16) = 503.75
what if those all columns have texts like '4u009433fldjfalaldj33948' still they all fit in one page either empty or with data? please make me clear.
March 16, 2015 at 12:28 pm
Does the column really have to be (nvarchar (max))?
Created date is unicode?
Assuming this is all hypothetical and no one is really trying to create a unicode character date field or NVARCHAR(MAX) created, then
March 16, 2015 at 12:39 pm
Dan121 (3/16/2015)
Scott, my concern is a size of a row. As Grant said UNIQUEIDENTIFIER is 16 bytes. if I have 503 columns with datatype 'UNIQUEIDENTIFIER' and rows are empty, will all they fit in a row ? (8016/16) = 503.75what if those all columns have texts like '4u009433fldjfalaldj33948' still they all fit in one page either empty or with data? please make me clear.
If the columns as defined in the CREATE TABLE don't fit within one page, SQL won't create the table. When you go to INSERT data, if the row won't fit within one page, SQL won't insert the row. Given that, what is your concern with the row size?
If you want, you can force all [n]varchar(max) columns out of row, so that they only take 16 bytes in the main table no matter how long they are. That may help the columns fit in the row.
Define the columns you need and see if they fit. If not, you'll have to move some columns to a second table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 16, 2015 at 1:22 pm
Dan121 (3/16/2015)
Scott, my concern is a size of a row. As Grant said UNIQUEIDENTIFIER is 16 bytes. if I have 503 columns with datatype 'UNIQUEIDENTIFIER' and rows are empty, will all they fit in a row ? (8016/16) = 503.75what if those all columns have texts like '4u009433fldjfalaldj33948' still they all fit in one page either empty or with data? please make me clear.
That begs the question a little, are you looking at relational storage here? That's what SQL Server does. If that's absolutely not what's needed, you might be better off with a different data management engine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply