Rick,
Way back (about 6 years ago) we had ditched using Oracle "long" fields (equivalent of "text" in SQL) in favor of concatenated varchar(2000) fields for a particular app we had - in Oracle 7.2 (not sure about current versions) you could only have 1 long per table and it had to be the last column. What a pain! So we created a generic "notes" table that had 20 or so varchar(2000) fields - when we needed a "long" we used a foreign key and type field combination to create records in "notes" that then became related to that record. We then had a generic form "class" that had an editbox - in the "read" we would concatenate all the varchar(2000) fields and rows. In the save we would break it all apart again. It was klunky as hell but it worked great and gave us tons of flexibility in the data model to add "long" types anywhere. Plus, we could change the model much easier going forward without that last column constraint. The tool we were using was Uniface but I imagine this could easily be done with some classes in VFP pretty easily.

Good luck!

- Randy

Rick,

I know this isn't the best news, but I think SQL Yukon addresses some of these issues.

1. There is a Varchar(max) which I'm not sure how it technically works, but is supposed to be stored inline like varchar() without affecting the record length, or perhaps using whatever is available for the record. I'll have to read read up on it.

2. Yukon will have an XML data type, which allows for storing blob, and you can use the new xquery stuff on it. Not sure if you can pull text out of a specific attribute.

3. Full text search is actually supposed to work in Yukon.

Well, I know this doesn't solve your immediate problem. But, one solution with 2000 is to have a seperate table which you can join to the main table. The secondary table can have varchar(8000) in it so you don't have to worry that it wil make the record excede the record size limit.

Well, even 8000 characters can be a problem. Of course text or blob work, but there are currently at least limitations and convolutions you have to do to do certain searches etc. If Text just performed more like a VarChar then this would really be a moot issue.

Um, a row can only be 8k, because a page of data is 8k, it is a performance thing, it is NOT related to sybase - the engine was completely re-written for 7.0. In order to understand varchar limits you need to understand page sizes, it is all explained pretty well in MSDN online. I like the "note" solution. I have used one like that before with SQL server. Without understanding your reasons for having so much "text" data in your database I can't make too many suggestions as to how you might optimize your storage, but if you'd like you can email me at michael.schmidt*AT*t-mobile.com and I'd be more than happy to see if I can provide a suggestion or two.

I guess I am missing something, this can't be a hard limit as I have a rowsize of 11450 in my master tables and it is perfectly valid. I did run across this thread because of performance issues I am having with this table - but the rowsize certainl;y isnt limited to 8K. That is the SQL page size but it will split the row into multiple pages is all - more I/O of course.

Am I wrong ?
Wouldnt be the first time :)

Well I am finding the estimatedrowsize being considerably skewed in the execution plan... nvarchar and unicode I am sure being contributors. Back to the drawing board - might be an 8K row limit, hard to say with SQL reporting 11450 rowsize back...but there are conflicting notes on the subject as well, and there are settings that seem to be able to oerride this limit. Thanks for the read.

RandyB... 8060b (8K) is the amount of DATA that can be stored in the row. You can create a table definition with 100 fields, all varchar(2000), which is definitely over 8k, and it will accept the creation of the table, but as soon as you try to populate a row with more than 8K of data, you will run into problems.

The row size limit is absolutely 8k. Although you can create longer rows data will be truncated to 8k. Again correct is that this is due to page size limits. Max page size is 8k and rows can NOT span pages. Text, nText, and Image data can be stored as "In Row" data but more frequently and by default it is stored in a seperate structure akn to a BTree. Then a pointer is stored in the data page. There are numerous easy to use functions to access the Text, nText, and Image data and it should not be all that difficult to write T-SQL to query and display this data in applications. Although you may run into a few challenges, once solved solutions run reasonably well. I developed a Contact Management application with "Text" note fields used by over 5000 users (as many as 1000 concurrent users), using from about 34 different countries, and available 24x7. The web based application I developed was done in 2000-2001 and is still in use today without any major performance issues (Other than the client keeping up with Stats and Index rebuilds).

you could try using a table structure like the syscomments field. spliting the text inserted into multiple rows and creating a row indexer for the data to string it back together.

Does anyone have an opinion of using varchar(max) versus varchar(n) in Yukon? Say you had several varchar(2000)columns vs. having several varchar(max) columns instead? Will you hit the rowsize limit using the sized columns, and wouldn't hit the limit using (max) columns?

hi
i need the exact size of one table in sqlserver
if anyone known plz send me imd

Just experimented with SQL Server 2005 Sept CTP. As far I as see, a row can contain over 20,000 nvarchar characters. There is no truncation.

Not sure what the limit is.

Running the same insert query for SQL Server 2000 resulted in the message below. This should be a good improvment in the 2005 version.

Cannot create a row of size 43725 which is greater than the allowable maximum of 8060.
The statement has been terminated.

I've found that if you try to copy rows from syscomments into a table with the identical structure as syscomments (without the computed column info), you are likely to see that syscomments DOES have total row sizes > 8060 bytes that will fail when inserting into your identical table. I have no idea how CREATE PROCEDURE and CREATE FUNCTION get by the row size limit in the first place. I am NOT talking about more than 8060 bytes across multiple syscomments rows, I literally mean a single 12000+ byte syscomments data row that fits into syscomments but can't be insert "insert into/select from" into a table with the same structure.

Scratch that post! Forgot about the computed columns in syscomments that don't actually take up row space.

I want to know "is there a difference in performance between varchar(100) and varchar (500)"
(Assume actual data is always less than 100 )

Why do we have to specify size of the varchar?.
Sql server is going to store only the actual length of the data, so from table size point of view , I/O point of view, size of the varchar should not matter ( as long as total row size is less than 8K)

I want to know "is there a difference in performance between varchar(100) and varchar (500)"
(Assume actual data is always less than 100 )

Why do we have to specify size of the varchar?.
Sql server is going to store only the actual length of the data, so from table size point of view , I/O point of view, size of the varchar should not matter ( as long as total row size is less than 8K)

how many rows added in the sql server 2000 in one table

I have been create one table, have field Abstract vachar(4000), but only 10,23 charator that i can insert. Why i can not insert more than 10,23 charactor in the field abstract?


Thanks

This looks like a pretty old thread, but I'll throw in my 2 cents since I've been struggling with this for many years.

In the beginning, I did the multi-varchar field hack like the one suggested early in this thread. But it's cumbersome and frequently buggy. I tried the convoluted ways of dealing stepping through text/blob types, and that wen't nowhere.

The whole time, there was this Microsoft Full-Text-Search thing sitting the background, I never really knew what it did. On the latest project I finally sat down and play with it and it turned out to be and answer to all the problems I've ever had with this issue.

It is fairly easy to set, it is blazingly fast in all of out applications, and it's more accurate than using the LIKE operator.

In our application, we don't actually index the TEXT fields of our data, we have a secondary joined table that holds TEXT fields that are initially copied in from the master table, but then manipulated specifically for the FTS engine. For example, we have data being combined into a single searchable table comeing from a multitude of sources. It was going to be very difficult to cross-tab all the different sources and add a data point like "this item is hot". Because the table structure was being determined by an external source. So we simply added a special string to the FTS TEXT field like "HOTTOHHOT" and now when we want to get the hot items, we do a FTS query and it comes back almost as fast as our queries that rely on integer lookups.

I highly recommend looking at FTS to solve some of the issues stated above.

When I first got into SQL administration, this is the way it was explained to me:

Remember that TEXT and BLOB fields are really just a pointer to a linked list of 2K pages sitting somewhere else. So in a table that contains multiple varchar(2000) field, the PK sectors will always be 8K apart on the disk (because varchar fields are stored inline in the record). So the heads on the disk need to jump around more.

Conversely, a table with just a PKID and a couple of TEXT fields will be much tighter, because the pointer only takes up (I forget the exact size, but it's tiny).

So in the table with large varchar fields, the space is allocated in every record, even when there is nothing in the varchar field. So that table with 100 "emptry" records will take up 800K on disk (8 x 100k)
Whereas that same table using TEXT fields will only take up the size of the PK + the size of the pointer, since an empty TEXT datatype takes up no space.

I don't know if I got all that right, but once I internalized the basic idea, it really helped me design tables better. Sometimes you want a contiguous space for you data, always allocated and searchable with LIKE. Other times, you just want to store a lot of data infrequently, then the TEXT mechanism is vastly superior to the varchar(really high number) mechanism.

Unfortunately, it looks like they are dropping the TEXT datatype in future versions of SQL server.

You can also get around some of the performance penalty using text by enabling the option for TEXT IN ROW. By default it will allocate 256bytes. Instead of just being a pointer to the separate page, it stores the root structure + data. If your value exceeds the allocated size, it also contains pointers to the rest of the data (out of page) - if it fits within that size, it's read from the same page as the rest of the row.

Hi
For all you guys looking to find available bytes in a table row for SQL Server 2005 (haven't tested it against previous version but should work) here is a scalar function that accepts a table name and returns the remaining bytes left for expansion
Hope it helps !!






-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ruchir T
-- Create date: 01/02/2008
-- Description: returns the number of bytes left to use for creating new columns
-- =============================================
CREATE FUNCTION available_tablerowsize
(
-- Add the parameters for the function here
@tablename char(50)
)
RETURNS int
AS
BEGIN
-- variables to track fixed and variable column sizes
DECLARE @num_columns int
DECLARE @result int
DECLARE @num_fixed_columns int
DECLARE @fixed_data_size int
DECLARE @var_data_size int
DECLARE @num_var_columns int
DECLARE @max_var_size int
DECLARE @null_bitmap_size int
DECLARE @row_size int

-- Find the total number of columns
select @num_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype


-- Find the size occupied by fixed length columns (Note: not possible to exist outside the 8060 bytes limit)
select @num_fixed_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0

select @fixed_data_size = sum(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0

-- Find the size occupied by variable length columns within the 8060 page size limit

-- number of variable length columns
select @num_var_columns=count(*)
from syscolumns, systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- max size of all variable length columns
select @max_var_size =max(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- calculate variable length storage
begin
if @num_var_columns>0
set @var_data_size=2+(@num_var_columns*2)+@max_var_size
--set @var_data_size = @num_var_columns*24
else
set @var_data_size=0
end

-- If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability.
select @null_bitmap_size = 2 + ((@num_columns+7)/8)

-- Calculate total rowsize
select @row_size = @fixed_data_size + @var_data_size + @null_bitmap_size + 4

-- Return the available bytes in the row available for expansion
select @result = 8060 - @row_size

RETURN @result


END
GO

Just shows how cheeky Microsoft is to want to come up with different versions of SQL. I do not see why the row field limitations to 8060 bytes in SQL Server 2000 had to wait for the release of the 2005 version??

It would be nice to "truncate" microsoft from our lives forever, but I am not quite sure how!

In SQL Server 2005 the Row Size Limit is 8060 bytes, but it can be handled by changing varchar(size) to varchar(max) or change the data type to one of the BLOB type.

can you make it clear

VARCHAR(MAX) and NVARCHAR(MAX) are powerful alternatives to TEXT and NTEXT. Whereas your VARCHAR(N) can only store up to 8000, VARCHAR(MAX) can store 2GB. Microsoft introduced these as replacements for TEXT and NTEXT. They have the same capacity but do not suffer the severe functional limits. For example, you can use the usual comparisons and string operations on them. There are some limits on using them in indexes.

Microsoft plans to phase out TEXT and NTEXT in favor of these new alternatives mainly because they perform better. One reason TEXT performs badly is that the contents are stored in other pages than the rows that include them, necessitating extra reads. A VARCHAR(MAX) may be wholly stored on the page, just like a VARCHAR(N). If it is too long to fit on the row page, part or all of it may be stored off-page, like with TEXT. It is significantly more complicated to implement this, but the performance gains can be huge. For example, if you have a "WHERE Contents LIKE 'abc%' and at least the first 3 bytes of text in the Contents column are on the row page, the filtering will be very fast, versus loading a separate extra page for each row you are filtering.