Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'm
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
> >I have a table that contains text data(XML formatted code). We have
> >decided
> > on a strategy to archive some of this data and null it out. Out of a
220GB
> > database, this history table is 153GB. After setting the text field to
> > null,
> > we haven't realized any space improvements. We reindex often. The
> > previous
> > DBA suggested copying the rows out and renaming the destination table
but
> > I'm
> > sceptical that I can finish that in any conceivable downtime due to the
> > row
> > count and general query performance when accessing text columns. Any
> > ideas
> > on how to reclaim this space?
>

Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'm
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?
On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?
|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
220GB[vbcol=seagreen]
but
>

Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null
,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'
m
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
220GB[vbcol=seagreen]
but[vbcol=seagreen]
>

nullifying value from FOR XML EXPLICIT

In this query (using T-SQL, don't know about SQLXML) hello becomes NULL and an error is returned...
select 2 as tag, null as parent
,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
,null as [aaaaaaaaaaaaaaaaaaaaaaa!19]
union all
select 19 as tag, 2 as parent
,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
,'hello' as [aaaaaaaaaaaaaaaaaaaaaaa!19]
for xml explicit
But change just about anything (make it xml auto, add or remove a few chars, change the 2 to a 12 or the 19 to either a 9 or a 190...) and it will work fine.
Anyone know what gives? Seems to be related to string lengths... Our workaround is to start tag ID's at >100 but does that mean some other combination will flake out?
Cheers...
John
Try changing alias [aaaaaaaaaaaaaaaaaaaaaaa!19] to
[aaaaaaaaaaaaaaaaaaaaaaa!19!] - there's a known bug in FOR XML EXPLICIT
code.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Nowak" <anonymous@.discussions.microsoft.com> wrote in message
news:08168845-CD3E-47DD-A4A9-686AF2B2525F@.microsoft.com...
> In this query (using T-SQL, don't know about SQLXML) hello becomes NULL
and an error is returned...
> select 2 as tag, null as parent
> ,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
> ,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
> ,null as [aaaaaaaaaaaaaaaaaaaaaaa!19]
> union all
> select 19 as tag, 2 as parent
> ,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
> ,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
> ,'hello' as [aaaaaaaaaaaaaaaaaaaaaaa!19]
> for xml explicit
> But change just about anything (make it xml auto, add or remove a few
chars, change the 2 to a 12 or the 19 to either a 9 or a 190...) and it
will work fine.
> Anyone know what gives? Seems to be related to string lengths... Our
workaround is to start tag ID's at >100 but does that mean some other
combination will flake out?
> Cheers...
> John

NULL values in XML string

Can someone tell me how to format my XML string so when I load it into my
table the field actually has a NULL value and not an empty space?
My XML string is formated like this.
<reimbursement>
<tbl_Reimbursement>
<Type10Rec Provider_Type='INSTIT' Provider_SubType='11' ReimDesc=""
Status='PMA' CreateUsr='Test' LastModDate="" LastModUsr="" ApproveDate=""/>
</tbl_Reimbursement><
The problem is that I want the ones with nothing between the doublw quotes
to get entered into the table as NULL values not empty strings. I am having
difficulty doing this. Can someone help me? Thanks in advance.>> The problem is that I want the ones with nothing between the doublw
What is the datatype of the columns in the table? Do you have a default set
for these columns, if the values are missing?Why not post the DDLs here? Are
you using OPENXML to load the data or using some other 3rd party tools?
Anith|||CREATE TABLE [tbl_Reimbursement] (
[ReimID] [int] IDENTITY (1, 1) NOT NULL ,
[RecordType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_Reimbursement_RecordType] DEFAULT (10),
[ActionType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_tbl_Reimbursement_ActionType] DEFAULT ('A'),
[Provider_Type] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Provider_SubType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetworkCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_tbl_Reimbursement_NetworkCode] DEFAULT (' '),
[EffectiveDate] [datetime] NOT NULL CONSTRAINT
[DF_tbl_Reimbursement_EffectiveDate] DEFAULT ('6/1/2004'),
[ExpirationDate] [datetime] NOT NULL CONSTRAINT
[DF_tbl_Reimbursement_ExpirationDate] DEFAULT ('12/31/2099'),
[ReimDesc] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SubmitDate] [datetime] NULL ,
[SubmitUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExportID] [int] NULL ,
[ExportDate] [datetime] NULL ,
[ApproveDate] [datetime] NULL ,
[ApproveUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [datetime] NULL CONSTRAINT [DF_tbl_Reimbursement_CreateDate]
DEFAULT (getdate()),
[CreateUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExportSeq] [int] NULL CONSTRAINT [DF_tbl_Reimbursement_ExportSeq] DEFAULT
(1),
[LastModDate] [datetime] NULL ,
[LastModUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReimLocked] [bit] NULL ,
[ReimLockedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Reimbursement] PRIMARY KEY CLUSTERED
(
[ReimID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO
insert into tbl_Reimbursement (Provider_Type, Provider_SubType, ReimDesc,
Status, CreateUsr, LastModDate, LastModUsr, ApproveDate, ApproveUsr)
select * from OPENXML (@.idoc,'/reimbursement/tbl_Reimbursement/Type10Rec',1)
with (Provider_Type varchar(6),
Provider_SubType varchar(2),
ReimDesc varchar(70),
Status varchar(10),
CreateUsr varchar(50),
LastModDate datetime,
LastModUsr varchar(50),
ApproveDate datetime,
ApproveUsr varchar(50))
"Anith Sen" wrote:

> What is the datatype of the columns in the table? Do you have a default se
t
> for these columns, if the values are missing?Why not post the DDLs here? A
re
> you using OPENXML to load the data or using some other 3rd party tools?
> --
> Anith
>
>|||Your XML, as you posted in your initial post, is not well formed. It has no
closing tags for reimbursement node. The XML string has only eight values,
so it will insert values for only eight corresponding columns mentioned in
the INSERT statement. If the other columns are nullable, NULLs will enter
those columns. Here is the repro:
DECLARE @.x VARCHAR(500), @.idoc INT
SET @.x = '
<reimbursement>
<tbl_Reimbursement>
<Type10Rec Provider_Type="INSTIT" Provider_SubType="11" ReimDesc=""
Status="PMA" CreateUsr="Test" LastModDate="" LastModUsr="" ApproveDate=""/>
</tbl_Reimbursement>
</reimbursement>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.x
INSERT INTO tbl
( Provider_Type, Provider_SubType, ReimDesc, Status,
CreateUsr, LastModDate, LastModUsr, ApproveDate, ApproveUsr )
SELECT * -- use column names
FROM OPENXML ( @.idoc, '/reimbursement/tbl_Reimbursement/Type10Rec', 1 )
WITH ( Provider_Type VARCHAR( 6 ),
Provider_SubType VARCHAR( 2 ),
ReimDesc VARCHAR( 70 ),
Status VARCHAR( 10 ),
CreateUsr VARCHAR( 50 ),
LastModDate DATETIME,
LastModUsr VARCHAR( 50 ),
ApproveDate DATETIME,
ApproveUsr VARCHAR( 50 ) )
EXEC sp_xml_removedocument @.idoc
Anith

Null Field Values not returned - For XML AUTO

Is there a way to get SQL to return NULL field values as 'empty' XML tags,
instead of completely omitting them from the output?
I'm using FOR XML AUTO
Michael L wrote:
> Is there a way to get SQL to return NULL field values as 'empty' XML tags,
> instead of completely omitting them from the output?
> I'm using FOR XML AUTO
Use
FOM XML AUTO, ELEMENTS XSINIL
that way for a null value in a column you get
<Columnname xsi:nil="true"/>
where xsi is bound to http://www.w3.org/2001/XMLSchema-instance.
See "Using the XSINIL directive" in
<http://msdn2.microsoft.com/en-us/library/ms177400.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||THANKS! I assume this only works in SQL Server 2005?
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:%23PvVNP2pHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Michael L wrote:
> Use
> FOM XML AUTO, ELEMENTS XSINIL
> that way for a null value in a column you get
> <Columnname xsi:nil="true"/>
> where xsi is bound to http://www.w3.org/2001/XMLSchema-instance.
> See "Using the XSINIL directive" in
> <http://msdn2.microsoft.com/en-us/library/ms177400.aspx>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
|||Correct. This is only available in 2005. In 2000 you could always use
ISNULL(col, '') in the select clause (with cast adjustments for nonstring
types).
Best regards
Michael
"Michael L" <mlarter@.comcast.net> wrote in message
news:CMGdnckxp900QfjbnZ2dnUVZ_hCdnZ2d@.comcast.com. ..
> THANKS! I assume this only works in SQL Server 2005?
>
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:%23PvVNP2pHHA.4772@.TK2MSFTNGP05.phx.gbl...
>

Null Field Values not returned - For XML AUTO

Is there a way to get SQL to return NULL field values as 'empty' XML tags,
instead of completely omitting them from the output?
I'm using FOR XML AUTOMichael L wrote:
> Is there a way to get SQL to return NULL field values as 'empty' XML tags,
> instead of completely omitting them from the output?
> I'm using FOR XML AUTO
Use
FOM XML AUTO, ELEMENTS XSINIL
that way for a null value in a column you get
<Columnname xsi:nil="true"/>
where xsi is bound to http://www.w3.org/2001/XMLSchema-instance.
See "Using the XSINIL directive" in
<http://msdn2.microsoft.com/en-us/library/ms177400.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||THANKS! I assume this only works in SQL Server 2005?
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:%23PvVNP2pHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Michael L wrote:
> Use
> FOM XML AUTO, ELEMENTS XSINIL
> that way for a null value in a column you get
> <Columnname xsi:nil="true"/>
> where xsi is bound to http://www.w3.org/2001/XMLSchema-instance.
> See "Using the XSINIL directive" in
> <http://msdn2.microsoft.com/en-us/library/ms177400.aspx>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||Correct. This is only available in 2005. In 2000 you could always use
ISNULL(col, '') in the select clause (with cast adjustments for nonstring
types).
Best regards
Michael
"Michael L" <mlarter@.comcast.net> wrote in message
news:CMGdnckxp900QfjbnZ2dnUVZ_hCdnZ2d@.co
mcast.com...
> THANKS! I assume this only works in SQL Server 2005?
>
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:%23PvVNP2pHHA.4772@.TK2MSFTNGP05.phx.gbl...
>