Disk performance issues

Question

Post reply

Disk performance issues

Larsi1971

Ten Centuries

Points: 1081
More actions
February 2, 2004 at 6:45 am

#107041

Is it performance problems when I should have 100 fields in a table with varchar 255 in almost each field?

-Lars

Please only reply to this newsgroup. All mails would be bounced back.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Looks like an Access design

http://qa.sqlservercentral.com/columnists/sjones/pagesize.asp

You should try to stay within the limit mentioned in the article or rethink your design.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Larsi1971 Ten Centuries Points: 1081 More actions · Answer 2

Does it really look like Access Design - it is created in Access - and ported to SQL Server.

The link you referred to was a limitation in CHAR field and not VARCHAR! I tried it myself. Two 8000 varchar fields worked fine, but not when the datatype is changed to char.

-Lars

Please only reply to this newsgroup. All mails would be bounced back.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

if object_id('myvarchar') is not null

drop table myvarchar

go

create table myvarchar(col1 varchar(8000), col2 varchar(8000))

go

insert into myvarchar(col1, col2) values(replicate('A', 8000), replicate('B', 8000))

select * from myvarchar

Achtung: myvarchar-Tabelle wurde erstellt, maximale Zeilenlänge (16025) übersteigt jedoch die maximale Byteanzahl pro Zeile (8060). INSERT oder UPDATE führt für eine Zeile in dieser Tabelle zu einem Fehler, wenn die entstehende Zeilenlänge 8060 Byte übersteigt.

Server: Nachr.-Nr. 511, Schweregrad 16, Status 1, Zeile 1

Eine Zeile der Größe 16013 kann nicht erstellt werden, da sie länger als das zulässige Maximum von 8060 wäre.

Die Anweisung wurde beendet.

(0 row(s) affected)

Well, the create table statement translates to what you have read in the article. The table will be created, but when you try to insert a row that exceeds the maximum size, the operation will be terminated. You see that the INSERT fails and no row has been inserted.

What information will you store in 100 column of that size?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]