estimate table spaces

hi guys,
sp_spaceused 'table1'
I have two ntext columns in 'table1',
by default, is sp_spaceused calculating space for ntext too?Hi Britney
All columns are included. You can see this for yourself:
use pubs
go
select * into newtitles from titles
go
exec sp_spaceused newtitles, @.updateusage= true
go
alter table newtitles add info ntext
go
update newtitles set info = replicate(title, 100)
go
exec sp_spaceused newtitles, @.updateusage= true
go
HTH
Kalen Delaney
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:emryCsOpFHA.1044@.tk2msftngp13.phx.gbl...
> hi guys,
>
> sp_spaceused 'table1'
> I have two ntext columns in 'table1',
> by default, is sp_spaceused calculating space for ntext too?
>
>|||When NTEXT column is NULL, how come it still takes some spaces?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODCxkYQpFHA.3828@.TK2MSFTNGP12.phx.gbl...
> Hi Britney
> All columns are included. You can see this for yourself:
> use pubs
> go
> select * into newtitles from titles
> go
> exec sp_spaceused newtitles, @.updateusage= true
> go
> alter table newtitles add info ntext
> go
> update newtitles set info = replicate(title, 100)
> go
> exec sp_spaceused newtitles, @.updateusage= true
> go
> HTH
> Kalen Delaney
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:emryCsOpFHA.1044@.tk2msftngp13.phx.gbl...
>
>|||Kevin
LOB data (type text, ntext and image) is by default stored on separate pages
outside the data rows. As soon as you update any rows with LOB data to
anything, even null, SQL Server will allocate at least 2 additional pages to
start keeping track of that data.
FYI, for ANY fixed length data column, NULLs will take space. So a char(100)
that contains NULL will take the full 100 bytes.
HTH
Kalen Delaney
www.solidqualitylearning.com
"kevin" <pearl_77@.hotmail.com> wrote in message
news:%23OP4VFYpFHA.3380@.TK2MSFTNGP12.phx.gbl...
> When NTEXT column is NULL, how come it still takes some spaces?
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ODCxkYQpFHA.3828@.TK2MSFTNGP12.phx.gbl...
>
>

Escaping [characters with text lengths over 4000 characters

In a previous post I mentioned you will get into problems when trying to
update DataSets containing rows with [ characters in ntext or text field
columns. Andrew Conrad (thanks) mentioned that you should escape these, like
so [[]. This will help you, except when the length of the value is over 4000
characters (for ntext) in length. After that the problem of zero affected
rows arises again, despite escaping. How come? How to solve?
Thanks.
AlexThe REPLACE fuction (and other SQL Server string functions) will not operate
on data larger than 8000 bytes -- for an NVARCHAR datatype, that means 4000
characters (2 bytes per character). Can you perform the replace on the
client, before passing the data to SQL Server?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Alex Thissen" <athissen A T killer-apps.nl> wrote in message
news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> In a previous post I mentioned you will get into problems when trying to
> update DataSets containing rows with [ characters in ntext or text field
> columns. Andrew Conrad (thanks) mentioned that you should escape these,
like
> so [[]. This will help you, except when the length of the value is over
4000
> characters (for ntext) in length. After that the problem of zero affected
> rows arises again, despite escaping. How come? How to solve?
> Thanks.
> Alex
>|||Hi Adam,
Thanks for thinking with me on this one. I don't use the REPLACE functions.
Instead, I make sure that the OriginalVersion of my DataRow in the DataSet
has the replaced value. Then the SqlXmlAdapter builds the UPDATE statement
for me, but it uses an optimistic locking scheme by comparing all columns
with the original values.For the (n)text fields the LIKE operator is used,
but as I mentioned this one breaks with values over 8000 bytes and [ chars
in it. I also wrote a (teasing) weblog entry on it, that you can find here:
http://www.alexthissen.nl/weblog/Pe...br />
8c42070.
You might want to read up on it, since this problem is hardly related to
SQLXML. It could be circumvented if there was a possibility to influence the
SQL that is generated. An option to exclude fields from the optimistic
locking would solve it directly.
So, my question remains, given that the LIKE operator breaks for lengths
over 8000 bytes WITH escaped [ characters in it ( as [[] ) in it, how do I
get my DataSets that contain such values to get updated through SQLXML?
Thanks, Alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvUE$$VFFHA.1348@.TK2MSFTNGP14.phx.gbl...
> The REPLACE fuction (and other SQL Server string functions) will not
> operate
> on data larger than 8000 bytes -- for an NVARCHAR datatype, that means
> 4000
> characters (2 bytes per character). Can you perform the replace on the
> client, before passing the data to SQL Server?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Alex Thissen" <athissen A T killer-apps.nl> wrote in message
> news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> like
> 4000
>sql

Escaping [ characters with text lengths over 4000 characters

In a previous post I mentioned you will get into problems when trying to
update DataSets containing rows with [ characters in ntext or text field
columns. Andrew Conrad (thanks) mentioned that you should escape these, like
so [[]. This will help you, except when the length of the value is over 4000
characters (for ntext) in length. After that the problem of zero affected
rows arises again, despite escaping. How come? How to solve?
Thanks.
Alex
The REPLACE fuction (and other SQL Server string functions) will not operate
on data larger than 8000 bytes -- for an NVARCHAR datatype, that means 4000
characters (2 bytes per character). Can you perform the replace on the
client, before passing the data to SQL Server?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Alex Thissen" <athissen A T killer-apps.nl> wrote in message
news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> In a previous post I mentioned you will get into problems when trying to
> update DataSets containing rows with [ characters in ntext or text field
> columns. Andrew Conrad (thanks) mentioned that you should escape these,
like
> so [[]. This will help you, except when the length of the value is over
4000
> characters (for ntext) in length. After that the problem of zero affected
> rows arises again, despite escaping. How come? How to solve?
> Thanks.
> Alex
>
|||Hi Adam,
Thanks for thinking with me on this one. I don't use the REPLACE functions.
Instead, I make sure that the OriginalVersion of my DataRow in the DataSet
has the replaced value. Then the SqlXmlAdapter builds the UPDATE statement
for me, but it uses an optimistic locking scheme by comparing all columns
with the original values.For the (n)text fields the LIKE operator is used,
but as I mentioned this one breaks with values over 8000 bytes and [ chars
in it. I also wrote a (teasing) weblog entry on it, that you can find here:
http://www.alexthissen.nl/weblog/Per...-8feda8c42070.
You might want to read up on it, since this problem is hardly related to
SQLXML. It could be circumvented if there was a possibility to influence the
SQL that is generated. An option to exclude fields from the optimistic
locking would solve it directly.
So, my question remains, given that the LIKE operator breaks for lengths
over 8000 bytes WITH escaped [ characters in it ( as [[] ) in it, how do I
get my DataSets that contain such values to get updated through SQLXML?
Thanks, Alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvUE$$VFFHA.1348@.TK2MSFTNGP14.phx.gbl...
> The REPLACE fuction (and other SQL Server string functions) will not
> operate
> on data larger than 8000 bytes -- for an NVARCHAR datatype, that means
> 4000
> characters (2 bytes per character). Can you perform the replace on the
> client, before passing the data to SQL Server?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Alex Thissen" <athissen A T killer-apps.nl> wrote in message
> news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> like
> 4000
>