about 8KB limit

In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off.

For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?

There is no such thing called ROW_OVERFLOW_DATA option. You cannot turn it on or off. It is based on the column type. If you have variable length columns, sql server allows you to store rows larger than 8k by pushing variable length column values off-row.

If your row size is 3KB fixed size, you will waste 2KB in each page. There is no way to work around and re-use those 2KB space.

Thanks

Sherry

about 8KB limit

In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off.

For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?

There is no such thing called ROW_OVERFLOW_DATA option. You cannot turn it on or off. It is based on the column type. If you have variable length columns, sql server allows you to store rows larger than 8k by pushing variable length column values off-row.

If your row size is 3KB fixed size, you will waste 2KB in each page. There is no way to work around and re-use those 2KB space.

Thanks

Sherry

sql

a way to have more than 8060 bytes per row?

Having a table with 5 columns that are varchar(2000).
This exceeds the maximum bytes per row and will fail
whenever someone adds more than 8060bytes in these columns.
Client whines about this and cant believe his eyes cause
he cant understand this since the maximum per column is
set to 8000 and how come the maximum is set to 8060 for
the whole row?
Anyways, do anyone of you guys out there have a workaround
for this problem or should i just tell the client to
rethink his model..
/RisunSorry, you will have to rethink your model - split the big table into two
smaller with one-to-one relation.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Risun" <risun@.wmdata.com> wrote in message
news:032401c37b69$e97cab30$a001280a@.phx.gbl...
> Having a table with 5 columns that are varchar(2000).
> This exceeds the maximum bytes per row and will fail
> whenever someone adds more than 8060bytes in these columns.
> Client whines about this and cant believe his eyes cause
> he cant understand this since the maximum per column is
> set to 8000 and how come the maximum is set to 8060 for
> the whole row?
> Anyways, do anyone of you guys out there have a workaround
> for this problem or should i just tell the client to
> rethink his model..
> /Risunsql

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big
enough to bring more rows, where might the problem mbe?
Declare @.ColList varchar(2000)
Declare @.CrLf varchar(10)
Select @.CrLf=Char(13) + Char(10)
Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
MyName From MyTable
Select @.ColListworks fine on my end.
JIM.H. wrote:
> This does not display more than 10 rows from the able, varchar(2000) is big
> enough to bring more rows, where might the problem mbe?
> Declare @.ColList varchar(2000)
> Declare @.CrLf varchar(10)
> Select @.CrLf=Char(13) + Char(10)
> Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
> MyName From MyTable
> Select @.ColList|||On Mon, 24 Jul 2006 06:44:02 -0700, JIM.H. wrote:
>This does not display more than 10 rows from the able, varchar(2000) is big
>enough to bring more rows, where might the problem mbe?
>Declare @.ColList varchar(2000)
>Declare @.CrLf varchar(10)
>Select @.CrLf=Char(13) + Char(10)
>Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
>MyName From MyTable
>Select @.ColList
Hi Jim,
Since this syntax is not supported, it could be anything. Though I have
to admit that it usually either returns the expected results, or just a
single row. I've just recently had a discussion with Omnibuzz about this
on his blog - check
http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html
The most likely reasons for seeing just 10 rows are forgetting to undo a
previous SET ROWCOUNT 10, or your front-end tool deciding not to show
all the data in long string columns. If you're using Query Analyzer, you
can control this through Tools / Options / Results / Maximum characters
per column (defaults to 256; maximum is 8192). In SQL Server Management
Studio, you can control this through Tools / Options / Query Results /
SQL Server / Result to Text (or Result to Grid). The maximum is 8192 for
Result to Text and 65535 for Result to Grid, but AFAIK, line feeds mess
up the Results to Grid display.
--
Hugo Kornelis, SQL Server MVP

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big enough to bring more rows, where might the problem mbe?

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

Select @.ColList

what is the error message|||

I do not see error, in the query analyzer, I set “Results in text” and run the query, I see first 8 records and (1 row(s) affected) message, it should show at least 50 rows. Is this a query analyzer problem?

|||

how about this

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName as nyfield From MyTable

|||

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

|||

i think QA is displaying it in a very long line

have this a try

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

print @.ColList

|||

JIM.H. wrote:

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

tried to simulate you can only make it until 4000

i think you should make use of cursor

|||

If you are using SQL 2005 please check the following:

Tools -> Options -> Query Results -> Results to Text Maximum number of characters displayed in each column (the default is 256)

Tools -> Options -> Query Results -> Results to Grid Maximum Characters Received Non XML data (the default is 65536)

In SQL 2000 in QA

Tools -> Options -> Results ->Maximum number of characters per column (the default is 256)

You may need to increase these numbers

|||

i tried this one in northwind

use northwind

Declare @.ColList char(8000)
Declare @.CrLf varchar(2)
Select @.CrLf=Char(13) + Char(10)
Select @.coLlist= COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + RTRIM(LTRIM(customerid)) From orders

print @.coLlist
select len(@.collist) as txtlength -<- check this out
select datalength(@.collist) as datalenght <-- and this is

here's the result

4000

8000

|||this worked. Thanks.|||there's a limit of byte per row that can be returned, inserted or updated...it's 8096 if i remember correctly...

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big enough to bring more rows, where might the problem mbe?

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

Select @.ColList

Did you have any SET ROWCOUNT prior to running this SQL> I ran it on my machine and it worked fine for me.|||

If you are using Query Analyzer to execute the query, please go to Tools menu->Options->switch to Results tab->set the 'Maximum characters per column' to max allowed value 8192, then try again.

If you're using Management Studio and you have set to return result as text, please go to Tools->Options->Query Results->SQL Server->Results to Text->set the 'Maximum number of characters displayed in each column' to 8192

|||Thats right. I had changed mine to 1200 sometime back.

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big
enough to bring more rows, where might the problem mbe?
Declare @.ColList varchar(2000)
Declare @.CrLf varchar(10)
Select @.CrLf=Char(13) + Char(10)
Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
MyName From MyTable
Select @.ColListworks fine on my end.
JIM.H. wrote:
> This does not display more than 10 rows from the able, varchar(2000) is bi
g
> enough to bring more rows, where might the problem mbe?
> Declare @.ColList varchar(2000)
> Declare @.CrLf varchar(10)
> Select @.CrLf=Char(13) + Char(10)
> Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
> MyName From MyTable
> Select @.ColList|||On Mon, 24 Jul 2006 06:44:02 -0700, JIM.H. wrote:

>This does not display more than 10 rows from the able, varchar(2000) is big
>enough to bring more rows, where might the problem mbe?
>Declare @.ColList varchar(2000)
>Declare @.CrLf varchar(10)
>Select @.CrLf=Char(13) + Char(10)
>Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
>MyName From MyTable
>Select @.ColList
Hi Jim,
Since this syntax is not supported, it could be anything. Though I have
to admit that it usually either returns the expected results, or just a
single row. I've just recently had a discussion with Omnibuzz about this
on his blog - check
[url]http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html[/u
rl]
The most likely reasons for seeing just 10 rows are forgetting to undo a
previous SET ROWCOUNT 10, or your front-end tool deciding not to show
all the data in long string columns. If you're using Query Analyzer, you
can control this through Tools / Options / Results / Maximum characters
per column (defaults to 256; maximum is 8192). In SQL Server Management
Studio, you can control this through Tools / Options / Query Results /
SQL Server / Result to Text (or Result to Grid). The maximum is 8192 for
Result to Text and 65535 for Result to Grid, but AFAIK, line feeds mess
up the Results to Grid display.
Hugo Kornelis, SQL Server MVP

A simple varchar comparison

First, Hi to all I'm new to this forum.
Second, I don't know if standard SQL involves stored procedures, but anyway I'll post my doubt.
I have chessy little procedure to get a password from a login. login is a varchar

CREATE PROCEDURE getPassw
@.login as varchar
AS
SELECT T_Worker.pass
FROM T_Worker
WHERE T_Worker.login = @.login

If I try " exec getPassw 'abc' ", I never get anything. The data exists in the tables. If I do something like

CREATE PROCEDURE getPassw
/*@.login as varchar*/
AS
SELECT T_Worker.pass
FROM T_Worker
WHERE T_Worker.login = 'abc'

The password shows up : '456' .
If I remove the '@.' from the WHERE query, all columns are returned... ?!?!? :confused:
I running the commands on a Microsoft's SQL server. Thank you for your attention. Any help would be seriously apreciated.This post really belongs in the Microsoft SQL (http://www.dbforums.com/f7/) forum.

I think the only problem is that you didn't cut yourself enough rope... You need to make the parameter longer, like:CREATE PROCEDURE getPassw
@.login as varchar(50)
AS

SELECT T_Worker.pass
FROM T_Worker
WHERE T_Worker.login = @.login

RETURN-PatP|||AH!!! Something that simple... But it did work... Sorry not posting this in the right place :D

a short question ?

how can i convert the varchar value to a column of data type int?

i am trying to do this

declare @.w varchar(50)
select @.w = col1 from myTable
select colname from mtable
where mtableID in (@.w)

but the mtableID is a int

and i always got

Syntax error converting the varchar value to a column of data type int.

thanks for everyone for helpingtry this

where mtableID in CAST(@.w AS int)

not sure if you want to swap your "in" for an "=" or not...

A question about varchar parameters

I've created a stored procedure that takes a varchar(10) as a parameter. However calling this stored procedure from an ASP page, with a string of greater length, generates an error. However this does not happen in Query Analyzer (it simply truncates the string to 10 characters). I was under the previous impression that this truncation was implicit, but now it seems that it is not. Can someone please give me a quick overview of how to work around this issue (is there an SQL setting I can flip on). I know I could pre-truncate every value in my page, but that seems like a design nightmare (seeing as how I would need to know the size of every varchar parameter in every stored procedure old and new, also I'd like to be able to simply increase the size of the data field in the table, at a later point, without having to match it up in every stored procedure and ASP page ).

P.S. I am using SQL Sever 2000

How did you call the stored procedure from your code? I use SqlConnection and SqlCommand to call the sp with a Parameter, it succeeded even I input a string with length greater then the length defined for the stored procedure parameter, as what happened in Query Analyzer.

So I guess your exception came from ASP .NET, not SQL. Did you call the stored procedure using OleDbCommand and specify the length for the parameter on the application side?

A problem in Distributed Transaction in a procedure

-- I made local procedure:

create procedure proc_AccountFrom
@.client varchar(50) = null
,@.account varchar(50) = null
,@.amount money = null
as

set nocount on

update client03.Northwind.dbo.ClientAccount
set AccountAmount = AccountAmount - @.amount
where ClientName = @.client and AccountName = @.account

go

-- I also made a procedure on the server:

create procedure proc_AccountTo
@.client varchar(50) = null
,@.account varchar(50) = null
,@.amount money = null
as

set nocount on

update server01.Northwind.dbo.ClientAccount
set AccountAmount = AccountAmount + @.amount
where ClientName = @.client and AccountName = @.account

set nocount off

-- I made the following local procedure:

create procedure proc_AccountTransfer
@.client varchar(50) = null
,@.account varchar(50) = null
,@.amount money = null
as
set nocount on
set ansi_warnings on
set xact_abort on

begin distributed transaction

exec proc_AccountFrom 'Bishoy','Saving',1000

exec server01.Northwind.dbo.proc_AccountTo 'Bishoy','Saving',1000

commit transaction

go

-- The table code:

use northwind
go
CREATE TABLE [dbo].[ClientAccount]
(ClientID int IDENTITY (1,1) NOT NULL
,ClientName varchar (50) NOT NULL
,AccountName varchar (50) NOT NULL
,AccountAmount money NOT NULL
,constraint PK_ClientID primary key clustered (ClientID)
,constraint CK_Amount check (AccountAmount >= 0)
)
go
insert into dbo.ClientAccount values ('Bishoy','Checking',100000)
insert into dbo.ClientAccount values ('Bishoy','Saving',2000)
go

?

\r\n

-- But I received the following response:

\r\n

Server: Msg 7391, Level 16, State 1, Procedure proc_AccountTransfer, Line 12
The operation could not be performed because the OLE DB provider \'SQLOLEDB\' was unable to begin a distributed transaction. \r\n
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider \'SQLOLEDB\' ITransactionJoin::JoinTransacti on returned 0x8004d00a]. \r\n
?

\r\n

-- Although:

\r\n

The DTS was active on both servers ?-- ?and they was linked?

\r\n

--? and the tables present

\r\n

--
Thank you.
Bishoy

\r\n\r\n",0]);D(["ce"]);D(["ms","1f76"]);//-->

-- But I received the following response:

Server: Msg 7391, Level 16, State 1, Procedure proc_AccountTransfer, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransacti on returned 0x8004d00a].

-- Although:

The DTS was active on both servers -- and they was linked

-- and the tables present

By 'DTS' do you mean 'DTC'? The MSDTC to be precise?
First off, make sure that the DTC process is running on both systems. Second, make sure that Network DTC Access is checked on both systems.
Hope that helps.
|||Can have multiple causes, but first check your firewall, you'll have to insert a rule in it (or turn it of :s)
|||Yes, DTC was running on both servers.
But, how to make sure that Network DTC Access is checked on both systems?

A float data type is recognized as varchar

Hello,
I am on a c# project right now. If I want to insert a float data type (c#)
into the sql server, I am getting the message then, that a varchar cannot be
inserted into a float. I tried it with money, small money and decimal, too.
And the result is ever the same. (Varchar cannot be entered into a
float/decimal, money etc..)
I am using direct sql command, not a SP.
As example: (simplified, without connection opening, etc.)
In c#:
float variable = 20;
sqlcommandobject.commandtext = "insert into products (price) VALUES
('"+variable+"')"Why are you putting the value between apostrophes?

> sqlcommandobject.commandtext = "insert into products (price) VALUES
> ('"+variable+"')"
sqlcommandobject.commandtext = "insert into products (price) VALUES
(" + variable + ")"
AMB
"the friendly display name" wrote:

> Hello,
> I am on a c# project right now. If I want to insert a float data type (c#)
> into the sql server, I am getting the message then, that a varchar cannot
be
> inserted into a float. I tried it with money, small money and decimal, too
.
> And the result is ever the same. (Varchar cannot be entered into a
> float/decimal, money etc..)
> I am using direct sql command, not a SP.
> As example: (simplified, without connection opening, etc.)
> In c#:
> float variable = 20;
> sqlcommandobject.commandtext = "insert into products (price) VALUES
> ('"+variable+"')"
>
>|||Get rid of the single quotes around the value, e.g.
sqlCommandObject.CommandText =
"INSERT INTO PRODUCTS (price) VALUES (" + variable.ToString() + ")"
Better yet, use parameters in your query so that it will automatically do
the formatting for you, this is also more flexible for data types like
binary, etc. and you don't have to worry about escaping text strings::
sqlCommandObject.CommandText =
"INSERT INTO PRODUCTS (price) VALUES (@.var)";
sqlCommandObject.Parameters.Add("@.var", variable);
Mike
"the friendly display name"
<thefriendlydisplayname@.discussions.microsoft.com> wrote in message
news:26A40CD8-45CE-4149-BF00-6064A0741640@.microsoft.com...
> Hello,
> I am on a c# project right now. If I want to insert a float data type (c#)
> into the sql server, I am getting the message then, that a varchar cannot
> be
> inserted into a float. I tried it with money, small money and decimal,
> too.
> And the result is ever the same. (Varchar cannot be entered into a
> float/decimal, money etc..)
> I am using direct sql command, not a SP.
> As example: (simplified, without connection opening, etc.)
> In c#:
> float variable = 20;
> sqlcommandobject.commandtext = "insert into products (price) VALUES
> ('"+variable+"')"
>
>|||Thank you.
The parameters solved the problem.
"Mike Jansen" wrote:

> Get rid of the single quotes around the value, e.g.
> sqlCommandObject.CommandText =
> "INSERT INTO PRODUCTS (price) VALUES (" + variable.ToString() + ")"
> Better yet, use parameters in your query so that it will automatically do
> the formatting for you, this is also more flexible for data types like
> binary, etc. and you don't have to worry about escaping text strings::
> sqlCommandObject.CommandText =
> "INSERT INTO PRODUCTS (price) VALUES (@.var)";
> sqlCommandObject.Parameters.Add("@.var", variable);
> Mike