varchar actual size

Question

varchar actual size

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 1

Grant Fritchey (12/31/2008)
Even though SQL Server 2005/2008 will store excess values past the 8060 limit when dealing with varchar,etc., you will get performance overhead to go and do that extra retrieve. I'd be sure I needed it rather than toss the idea of database design out the window & store everything in flipping huge, unformed, strings.

What? You don't think that putting everything in an entire database into a single EAV table with a VARCHAR(MAX) for the "V" part wouldn't be better? :hehe: Just think how easy it would be to index... and DRI would be a breeze... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 2

I presented this as a database design at a meeting once.

CREATE TABLE [dbo].[AppTable](

[Id] [int] IDENTITY(1,1) NOT NULL,

[RefId] [int] NOT NULL,

[AppValue] [nvarchar](max) NOT NULL,

CONSTRAINT [PK_AppTable] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AppTable] WITH CHECK ADD CONSTRAINT [FK_AppTable_AppTable] FOREIGN KEY([RefId])

REFERENCES [dbo].[AppTable] ([Id])

GO

I was the only one laughing. One of the developers asked me how hard it would be to write queries against it.

"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

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 3

Grant Fritchey (12/31/2008)
I presented this as a database design at a meeting once.
CREATE TABLE [dbo].[AppTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RefId] [int] NOT NULL,
[AppValue] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_AppTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AppTable] WITH CHECK ADD CONSTRAINT [FK_AppTable_AppTable] FOREIGN KEY([RefId])
REFERENCES [dbo].[AppTable] ([Id])
GO
I was the only one laughing. One of the developers asked me how hard it would be to write queries against it.

Now THAT's funny... I've gotta try that at the next design meeting... :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

klini (12/30/2008)
RBarry says "Varchars only store that space that you are actually using"
but
Jeffrey says "whatever size you allow is the size that is going to be used"
Could I have some clarification?

Sure, I was talking about how SQL Server works. I believe that Jeffery was talking about how users (and applications) behave. That is, if you allow them to store 8000 characters, then they will try to store 8000 characters.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeffrey Williams SSC Guru Points: 90301 More actions · Answer 5

Yes, I was referring to users and applications. If you create a Person table - with a LastName column and declare it as VARCHAR(8000), then you will get a lastname that is 8000 characters long.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Jeff Moden (12/31/2008)
Grant Fritchey (12/31/2008)
Even though SQL Server 2005/2008 will store excess values past the 8060 limit when dealing with varchar,etc., you will get performance overhead to go and do that extra retrieve. I'd be sure I needed it rather than toss the idea of database design out the window & store everything in flipping huge, unformed, strings.
What? You don't think that putting everything in an entire database into a single EAV table with a VARCHAR(MAX) for the "V" part wouldn't be better? :hehe: Just think how easy it would be to index... and DRI would be a breeze... 😛

Actually, Jeff, you should be using SQL_VARIANTS for the V part. That's what they're there for.

😀

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jeff Moden (12/31/2008)
Grant Fritchey (12/31/2008)
I presented this as a database design at a meeting once.
CREATE TABLE [dbo].[AppTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RefId] [int] NOT NULL,
[AppValue] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_AppTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AppTable] WITH CHECK ADD CONSTRAINT [FK_AppTable_AppTable] FOREIGN KEY([RefId])
REFERENCES [dbo].[AppTable] ([Id])
GO
I was the only one laughing. One of the developers asked me how hard it would be to write queries against it.
Now THAT's funny... I've gotta try that at the next design meeting... :hehe:

Careful - it's only funny if you can convince them how BAD an idea that is. Like Grant mentioned, this tends to be very popular with "straight dev types" who don't much care to deal with "effing databases" to paraphrase the drones I had to battle.

It's not at ALL funny when an attempt at humor desparately tries to make it into Production.....:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 8

Grant Fritchey (12/31/2008)
Even though SQL Server 2005/2008 will store excess values past the 8060 limit when dealing with varchar,etc., you will get performance overhead to go and do that extra retrieve. I'd be sure I needed it rather than toss the idea of database design out the window & store everything in flipping huge, unformed, strings.

True... but then, if actually need to store more than 8060 bytes as a value then you are going to get that overhead anyway. However, I definitely agree that you should design your database to meet your needs, rather than whack in VARCHAR(MAX) statements everywhere.

Perhaps I should have been more clear when I posted that varchar(max) statement above.

Random Technical Stuff[/url]

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 9

RBarryYoung (12/31/2008)

Actually, Jeff, you should be using SQL_VARIANTS for the V part. That's what they're there for.:D

Uh huh... and someday they'll make all our dreams come true by making it capable of holding the TEXT and VARCHAR(MAX) datatypes... that's when it'll really become useful. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)