Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
Reorganizing a specified clustered index will compact all LOB columns that
are contained in the leaf level (data rows) of the clustered index" .
According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
" In SQL Server 2000, the only way you can compact LOBs in a table is to
unload and reload the LOB data"
This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
Is this correct?Mladen Andrijasevic wrote:
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> Is this correct?
from BOL:
ALTER INDEX
...
...
WITH ( LOB_COMPACTION = { ON | OFF } )
Specifies that all pages that contain large object (LOB) data are
compacted. The LOB data types are image, text, ntext, varchar(max),
nvarchar(max), varbinary(max), and xml. Compacting this data can improve
disk space use. The default is ON.|||Hi, according BOL (Jul 2007), ALTER INDEX ... REORGANIZE is Equivalent to
DBCC INDEXDEFRAG. is the same.
Regards,
"Mladen Andrijasevic" wrote:
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> Is this correct?|||I believe the following white has the info you need. It covers SQL2000, but
most materials should still be relevant.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Linchi
"Mladen Andrijasevic" wrote:
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> Is this correct?|||Zarko,
I am aware that LOB_COMPACTION default is ON . It was the point of my
question. From this it would fiollow that saying that "ALTER INDEX ...
REORGANIZE is Equivalent to DBCC INDEXDEFRAG " is wrong . But I have not come
across a statement saying so , or saying that ALTER INDEX ... REORGANIZE
should be immediately implemented on sql 2005 instead of DBCC INDEXDEFRAG
because it does more than DBCC INDEXDEFRAG , i.e. it can compact LOBs
Thanks
Mladen
"Zarko Jovanovic" wrote:
> Mladen Andrijasevic wrote:
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> > Is this correct?
> from BOL:
> ALTER INDEX
> ...
> ...
> WITH ( LOB_COMPACTION = { ON | OFF } )
> Specifies that all pages that contain large object (LOB) data are
> compacted. The LOB data types are image, text, ntext, varchar(max),
> nvarchar(max), varbinary(max), and xml. Compacting this data can improve
> disk space use. The default is ON.
>|||Carlos ,
Well, the purpose of my question was precisely to clarify how to reconcile
what is written in BOL in one place , i.e. that ALTER INDEX ...
REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
another place in SQL Server 2005 Books Online (September 2007) i.e. that
ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent if
ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
Thanks
Mladen
"Carlos A." wrote:
> Hi, according BOL (Jul 2007), ALTER INDEX ... REORGANIZE is Equivalent to
> DBCC INDEXDEFRAG. is the same.
> Regards,
>
> "Mladen Andrijasevic" wrote:
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> > Is this correct?|||Linchi,
I 've read Microsoft SQL Server 2000 Index Defragmentation Best Practices
and implemented its recommendations a few years ago . However, it cannot
provide an answer to my question since my question has todo with the
comparsion of DBCC INDEXDEFRAG of sql 2000 . to ALTER INDEX ... REORGANIZE of
sql 2005, which is not mentioned in the Server 2000 Index Defragmentation
Best Practices document since it appeared only in SQL 2005
Thanks
Mladen
"Linchi Shea" wrote:
> I believe the following white has the info you need. It covers SQL2000, but
> most materials should still be relevant.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Linchi
> "Mladen Andrijasevic" wrote:
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> > Is this correct?|||> Well, the purpose of my question was precisely to clarify how to
> reconcile
> what is written in BOL in one place , i.e. that ALTER INDEX ...
> REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> another place in SQL Server 2005 Books Online (September 2007) i.e. that
> ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent
> if
> ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
I think using the default behavior they are equivalent. Just because one
has some different *optional* commands does not make them completely
different animals.
Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
and COALESCE()?
In any case, I do agree that perhaps the wording could be a little less
ambiguous. Maybe you should click on the feedback item on that page in
Books Online, and voice your concerns? That feedback will make its way
directly to the writer of the topic.
A|||Aaron ,
I am not such a stickler over the wording at all. I just need to find out
whether it makes sense to implement ALTER INDEX . REORGANIZE over DBCC
INDEXDEFRAG on our SQL 2005 systems. If they were equivalent I would not
bother to do it right away . So would you say that ALTER INDEX . REORGANIZE
would better defrgarment than DBCC INDEXDEFRAG since ALTER INDEX .
REORGANIZE would compact LOBs and DBCC INDEXDEFRAG would not?
Thanks
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Well, the purpose of my question was precisely to clarify how to
> > reconcile
> > what is written in BOL in one place , i.e. that ALTER INDEX ...
> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> > another place in SQL Server 2005 Books Online (September 2007) i.e. that
> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent
> > if
> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
> I think using the default behavior they are equivalent. Just because one
> has some different *optional* commands does not make them completely
> different animals.
> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
> and COALESCE()?
> In any case, I do agree that perhaps the wording could be a little less
> ambiguous. Maybe you should click on the feedback item on that page in
> Books Online, and voice your concerns? That feedback will make its way
> directly to the writer of the topic.
> A
>
>|||Aaron,
Just noticed the " I think using the default behavior they are equivalent"
I do not think this is true either since LOB_COMPACTION = ON is the default
and that is precisely where they differ!
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Well, the purpose of my question was precisely to clarify how to
> > reconcile
> > what is written in BOL in one place , i.e. that ALTER INDEX ...
> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> > another place in SQL Server 2005 Books Online (September 2007) i.e. that
> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent
> > if
> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
> I think using the default behavior they are equivalent. Just because one
> has some different *optional* commands does not make them completely
> different animals.
> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
> and COALESCE()?
> In any case, I do agree that perhaps the wording could be a little less
> ambiguous. Maybe you should click on the feedback item on that page in
> Books Online, and voice your concerns? That feedback will make its way
> directly to the writer of the topic.
> A
>
>|||Sorry, my (clearly wrong) recollection was that LOB_COMPACTION defaulted to
OFF.
But still, I think it is weird for you to be asking us whether you should
use ALTER INDEX instead of DBCC. Isn't that really your call? Do you want
your LOBs compacted, or not? If not, then you can use ALTER INDEX with that
setting to OFF, no? Do you want your code to be forward compatible? I
envision that someday they will deprecate the DBCC command completely.
In any case, not really our decision.
"Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
in message news:BA1DD97B-3DB5-43A2-AAE0-07C8AC1674D2@.microsoft.com...
> Aaron,
> Just noticed the " I think using the default behavior they are equivalent"
> I do not think this is true either since LOB_COMPACTION = ON is the
> default
> and that is precisely where they differ!
> Mladen
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > Well, the purpose of my question was precisely to clarify how to
>> > reconcile
>> > what is written in BOL in one place , i.e. that ALTER INDEX ...
>> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
>> > another place in SQL Server 2005 Books Online (September 2007) i.e.
>> > that
>> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be
>> > equivalent
>> > if
>> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
>> I think using the default behavior they are equivalent. Just because one
>> has some different *optional* commands does not make them completely
>> different animals.
>> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
>> and COALESCE()?
>> In any case, I do agree that perhaps the wording could be a little less
>> ambiguous. Maybe you should click on the feedback item on that page in
>> Books Online, and voice your concerns? That feedback will make its way
>> directly to the writer of the topic.
>> A
>>|||Hi Mladen
Internally DBCC INDEXDEFRAG and ALTER INDEX REORGANIZE use the same
algorithm, so the only 'improvements' in the REORGANIZE option are that
there are additional features you can control, such as the LOB compaction.
As Aaron states, whether or not you see the new syntax as an improvement
depends on whether you want to control LOB compaction or not. Going
forward, it's a good idea to use the ALTER INDEX because the DBCC option
will be eventually going away, but there is no word yet on when that will
be.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
in message news:91BF82DD-6C5A-4702-972A-EFD6A841A8FF@.microsoft.com...
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p
> 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to
> DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG
> cannot.
> Is this correct?|||Apparently there is a misunderstanding here. First the documentation
stated that the two (ALTER INDEX REORGANIZE and DBCC INDEXDEFRAG) were
equivalent, which they obviously are not. Next, you mistook the default
value for LOB_COMPACTION. This gives me the impression that not many have
been using ALTER INDEX REORGANIZE yet, else things would have been clarified
by now.
I do not expect you to make a decision for me, but to possibly point to a
study, white paper, where the performance benefits of LOB_COMPACTION
through ALTER INDEX REORGANIZE are quantified. Something on the line of a
sequel to Server 2000 Index Defragmentation Best Practices document . Is
there a Server 2005 Index Defragmentation Best Practices document planned?
I have not done LOB compaction through unload and reload of LOB data in SQL
2000 that Kalen Delaney mentioned in her book. Are the benefits of compaction
comparable to compacting of non LOB data? Any idiosyncrasies? If there are
documents discussing this topic in somewhat more detail I would definitely
switch to ALTER INDEX, once convinced of its benefits, even if it is not
backward compatible.
tks
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> Sorry, my (clearly wrong) recollection was that LOB_COMPACTION defaulted to
> OFF.
> But still, I think it is weird for you to be asking us whether you should
> use ALTER INDEX instead of DBCC. Isn't that really your call? Do you want
> your LOBs compacted, or not? If not, then you can use ALTER INDEX with that
> setting to OFF, no? Do you want your code to be forward compatible? I
> envision that someday they will deprecate the DBCC command completely.
> In any case, not really our decision.
>
> "Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
> in message news:BA1DD97B-3DB5-43A2-AAE0-07C8AC1674D2@.microsoft.com...
> >
> > Aaron,
> >
> > Just noticed the " I think using the default behavior they are equivalent"
> >
> > I do not think this is true either since LOB_COMPACTION = ON is the
> > default
> > and that is precisely where they differ!
> >
> > Mladen
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> > Well, the purpose of my question was precisely to clarify how to
> >> > reconcile
> >> > what is written in BOL in one place , i.e. that ALTER INDEX ...
> >> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> >> > another place in SQL Server 2005 Books Online (September 2007) i.e.
> >> > that
> >> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be
> >> > equivalent
> >> > if
> >> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
> >>
> >> I think using the default behavior they are equivalent. Just because one
> >> has some different *optional* commands does not make them completely
> >> different animals.
> >>
> >> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
> >> and COALESCE()?
> >>
> >> In any case, I do agree that perhaps the wording could be a little less
> >> ambiguous. Maybe you should click on the feedback item on that page in
> >> Books Online, and voice your concerns? That feedback will make its way
> >> directly to the writer of the topic.
> >>
> >> A
> >>
> >>
> >>
>
>|||> Apparently there is a misunderstanding here. First the documentation
> stated that the two (ALTER INDEX REORGANIZE and DBCC INDEXDEFRAG) were
> equivalent, which they obviously are not. Next, you mistook the default
> value for LOB_COMPACTION. This gives me the impression that not many have
> been using ALTER INDEX REORGANIZE yet, else things would have been
> clarified
> by now.
Or maybe we are using it in places where we don't have LOBs?
> I do not expect you to make a decision for me, but to possibly point to a
> study, white paper, where the performance benefits of LOB_COMPACTION
> through ALTER INDEX REORGANIZE are quantified.
<shrug>
I don't know of any. I could Google, but of course, so could you.
A|||Thanks Kalen,
I was responding to Aaronâ's post and did not notice your answer.
Is there a white paper, where the performance benefits of LOB_COMPACTION
through ALTER INDEX REORGANIZE are quantified? Something on the line of a
sequel to Server 2000 Index Defragmentation Best Practices document . Is
there a Server 2005 Index Defragmentation Best Practices document planned?
I have not used LOB compaction through unload and reload of LOB data that
you mentioned in your book. I do not think we could have done it (even if I
had known about it) for the same reason that we used DBCC INDEXDEFRAG in the
first place â' we need the system to be up 24/7 and the defragmenting
operation must be an online one. But I would definitely switch to ALTER
INDEX REORGANIZE if I could find some more documentation on LOB_COMPACTION .
Thank you for posting the new_helpindex command. Will definitely use it.
Mladen
"Kalen Delaney" wrote:
> Hi Mladen
> Internally DBCC INDEXDEFRAG and ALTER INDEX REORGANIZE use the same
> algorithm, so the only 'improvements' in the REORGANIZE option are that
> there are additional features you can control, such as the LOB compaction.
> As Aaron states, whether or not you see the new syntax as an improvement
> depends on whether you want to control LOB compaction or not. Going
> forward, it's a good idea to use the ALTER INDEX because the DBCC option
> will be eventually going away, but there is no word yet on when that will
> be.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
> in message news:91BF82DD-6C5A-4702-972A-EFD6A841A8FF@.microsoft.com...
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p
> > 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to
> > DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG
> > cannot.
> > Is this correct?
>
>|||>>Or maybe we are using it in places where we don't have LOBs?
Indeed. Sorry. Should have been more precise: â'This gives me the
impression that not many have been using ALTER INDEX REORGANIZE , in the
context of knowingly, deliberately compacting LOBs yet, else things would
have been clarified by nowâ'. I assumed the LOBs context from my initial
post.
Thanks for the answers.
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Apparently there is a misunderstanding here. First the documentation
> > stated that the two (ALTER INDEX REORGANIZE and DBCC INDEXDEFRAG) were
> > equivalent, which they obviously are not. Next, you mistook the default
> > value for LOB_COMPACTION. This gives me the impression that not many have
> > been using ALTER INDEX REORGANIZE yet, else things would have been
> > clarified
> > by now.
> Or maybe we are using it in places where we don't have LOBs?
> > I do not expect you to make a decision for me, but to possibly point to a
> > study, white paper, where the performance benefits of LOB_COMPACTION
> > through ALTER INDEX REORGANIZE are quantified.
> <shrug>
> I don't know of any. I could Google, but of course, so could you.
> A
>|||I'm not aware of any paper, but as Aaron suggests, you can use google as
well as any of us. My guess is that the impact would completely depend on
your application, and what you were doing with the lob data. It should be
very straightforward for you to run your own tests with and without lob
compaction, and check the performance difference.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
in message news:1017B786-D603-4252-A0FC-63E4AAC94E9A@.microsoft.com...
> Thanks Kalen,
> I was responding to Aaron's post and did not notice your answer.
> Is there a white paper, where the performance benefits of LOB_COMPACTION
> through ALTER INDEX REORGANIZE are quantified? Something on the line of a
> sequel to Server 2000 Index Defragmentation Best Practices document . Is
> there a Server 2005 Index Defragmentation Best Practices document planned?
> I have not used LOB compaction through unload and reload of LOB data that
> you mentioned in your book. I do not think we could have done it (even if
> I
> had known about it) for the same reason that we used DBCC INDEXDEFRAG in
> the
> first place - we need the system to be up 24/7 and the defragmenting
> operation must be an online one. But I would definitely switch to ALTER
> INDEX REORGANIZE if I could find some more documentation on LOB_COMPACTION
> .
> Thank you for posting the new_helpindex command. Will definitely use it.
> Mladen
>
> "Kalen Delaney" wrote:
>> Hi Mladen
>> Internally DBCC INDEXDEFRAG and ALTER INDEX REORGANIZE use the same
>> algorithm, so the only 'improvements' in the REORGANIZE option are that
>> there are additional features you can control, such as the LOB
>> compaction.
>> As Aaron states, whether or not you see the new syntax as an improvement
>> depends on whether you want to control LOB compaction or not. Going
>> forward, it's a good idea to use the ALTER INDEX because the DBCC option
>> will be eventually going away, but there is no word yet on when that will
>> be.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com>
>> wrote
>> in message news:91BF82DD-6C5A-4702-972A-EFD6A841A8FF@.microsoft.com...
>> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
>> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
>> > Reorganizing a specified clustered index will compact all LOB columns
>> > that
>> > are contained in the leaf level (data rows) of the clustered index" .
>> >
>> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine
>> > p
>> > 324
>> > " In SQL Server 2000, the only way you can compact LOBs in a table is
>> > to
>> > unload and reload the LOB data"
>> >
>> > This would make ALTER INDEX REORGANIZE not equivalent but superior to
>> > DBCC
>> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG
>> > cannot.
>> > Is this correct?
>>
Showing posts with label http. Show all posts
Showing posts with label http. Show all posts
Thursday, March 8, 2012
Monday, February 13, 2012
Allow access to IUSR_MachineName
In SQL 2000 SP3.
I was trying to follow this page:
http://support.microsoft.com/default.aspx?kbid=3D299987
It's not that I cannot follow it but that I'd prefer to have the accound = that will access the database be IUSR_MachineName. So I gave = IUSR_MachineName the ability to logon on to this database in SQL 2000 = and no other.
I get to the point in the article where it has this for the connection = string:
StrConnect =3D "Provider=3DSQLOLEDB.1;User = ID=3DIUSR_MachineName;Password=3D<strong password>;Initial = Catalog=3Dhesterlogin;" & _
"Network Library=3Ddbmssocn;Data Source=3DMyServer"
and now I am stuck. What do I assign for the password here? The = IUSR_MachineName password is automatically generated by my Internet = Informaion Services and I really do not know how to access it. All = attempts I have made in the past have been well less then satisfatory. = I suppose I could try it again (requires some Windows Scripting) but I'm = hoping there is a better or easier way. Any suggestions? Thanks.
-- George Hester
__________________________________George,
If I understand your question, you need to specify in the connection string
that you want to do a Windows login. There are examples of connection
strings at www.connectionstrings.com.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:ece5WeTwDHA.2308@.TK2MSFTNGP11.phx.gbl...
In SQL 2000 SP3.
I was trying to follow this page:
http://support.microsoft.com/default.aspx?kbid=299987
It's not that I cannot follow it but that I'd prefer to have the accound
that will access the database be IUSR_MachineName. So I gave
IUSR_MachineName the ability to logon on to this database in SQL 2000 and no
other.
I get to the point in the article where it has this for the connection
string:
StrConnect = "Provider=SQLOLEDB.1;User
ID=IUSR_MachineName;Password=<strong password>;Initial Catalog=hesterlogin;"
& _
"Network Library=dbmssocn;Data Source=MyServer"
and now I am stuck. What do I assign for the password here? The
IUSR_MachineName password is automatically generated by my Internet
Informaion Services and I really do not know how to access it. All attempts
I have made in the past have been well less then satisfatory. I suppose I
could try it again (requires some Windows Scripting) but I'm hoping there is
a better or easier way. Any suggestions? Thanks.
--
George Hester
__________________________________|||Ah maybe trusted? Let me check. Thanks.
-- George Hester
__________________________________
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:Oy659LawDHA.1788@.tk2msftngp13.phx.gbl...
> George,
> > If I understand your question, you need to specify in the connection =string
> that you want to do a Windows login. There are examples of connection
> strings at www.connectionstrings.com.
> > -- > Tibor Karaszi, SQL Server MVP
> Archive at:
> =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ece5WeTwDHA.2308@.TK2MSFTNGP11.phx.gbl...
> In SQL 2000 SP3.
> > I was trying to follow this page:
> > http://support.microsoft.com/default.aspx?kbid=3D299987
> > It's not that I cannot follow it but that I'd prefer to have the =accound
> that will access the database be IUSR_MachineName. So I gave
> IUSR_MachineName the ability to logon on to this database in SQL 2000 =and no
> other.
> > I get to the point in the article where it has this for the connection
> string:
> > > StrConnect =3D "Provider=3DSQLOLEDB.1;User
> ID=3DIUSR_MachineName;Password=3D<strong password>;Initial =Catalog=3Dhesterlogin;"
> & _
> "Network Library=3Ddbmssocn;Data Source=3DMyServer"
> > and now I am stuck. What do I assign for the password here? The
> IUSR_MachineName password is automatically generated by my Internet
> Informaion Services and I really do not know how to access it. All =attempts
> I have made in the past have been well less then satisfatory. I =suppose I
> could try it again (requires some Windows Scripting) but I'm hoping =there is
> a better or easier way. Any suggestions? Thanks.
> > -- > George Hester
> __________________________________
> >|||Actually I was hoping to use IUSR_MachineName for the Windows Logon =account. But I see I need to put a password in for the connection =string. As my IIS controls the IUSR_MachineName password I cannot =hardcode that into the connection string. That is my issue. What do I =put in for the password so that IIS can continue to automatically =generate the IUSR_MachineName account's password and use that in my =connection string? Thanks.
-- George Hester
__________________________________
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:Oy659LawDHA.1788@.tk2msftngp13.phx.gbl...
> George,
> > If I understand your question, you need to specify in the connection =string
> that you want to do a Windows login. There are examples of connection
> strings at www.connectionstrings.com.
> > -- > Tibor Karaszi, SQL Server MVP
> Archive at:
> =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ece5WeTwDHA.2308@.TK2MSFTNGP11.phx.gbl...
> In SQL 2000 SP3.
> > I was trying to follow this page:
> > http://support.microsoft.com/default.aspx?kbid=3D299987
> > It's not that I cannot follow it but that I'd prefer to have the =accound
> that will access the database be IUSR_MachineName. So I gave
> IUSR_MachineName the ability to logon on to this database in SQL 2000 =and no
> other.
> > I get to the point in the article where it has this for the connection
> string:
> > > StrConnect =3D "Provider=3DSQLOLEDB.1;User
> ID=3DIUSR_MachineName;Password=3D<strong password>;Initial =Catalog=3Dhesterlogin;"
> & _
> "Network Library=3Ddbmssocn;Data Source=3DMyServer"
> > and now I am stuck. What do I assign for the password here? The
> IUSR_MachineName password is automatically generated by my Internet
> Informaion Services and I really do not know how to access it. All =attempts
> I have made in the past have been well less then satisfatory. I =suppose I
> could try it again (requires some Windows Scripting) but I'm hoping =there is
> a better or easier way. Any suggestions? Thanks.
> > -- > George Hester
> __________________________________
> >
I was trying to follow this page:
http://support.microsoft.com/default.aspx?kbid=3D299987
It's not that I cannot follow it but that I'd prefer to have the accound = that will access the database be IUSR_MachineName. So I gave = IUSR_MachineName the ability to logon on to this database in SQL 2000 = and no other.
I get to the point in the article where it has this for the connection = string:
StrConnect =3D "Provider=3DSQLOLEDB.1;User = ID=3DIUSR_MachineName;Password=3D<strong password>;Initial = Catalog=3Dhesterlogin;" & _
"Network Library=3Ddbmssocn;Data Source=3DMyServer"
and now I am stuck. What do I assign for the password here? The = IUSR_MachineName password is automatically generated by my Internet = Informaion Services and I really do not know how to access it. All = attempts I have made in the past have been well less then satisfatory. = I suppose I could try it again (requires some Windows Scripting) but I'm = hoping there is a better or easier way. Any suggestions? Thanks.
-- George Hester
__________________________________George,
If I understand your question, you need to specify in the connection string
that you want to do a Windows login. There are examples of connection
strings at www.connectionstrings.com.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:ece5WeTwDHA.2308@.TK2MSFTNGP11.phx.gbl...
In SQL 2000 SP3.
I was trying to follow this page:
http://support.microsoft.com/default.aspx?kbid=299987
It's not that I cannot follow it but that I'd prefer to have the accound
that will access the database be IUSR_MachineName. So I gave
IUSR_MachineName the ability to logon on to this database in SQL 2000 and no
other.
I get to the point in the article where it has this for the connection
string:
StrConnect = "Provider=SQLOLEDB.1;User
ID=IUSR_MachineName;Password=<strong password>;Initial Catalog=hesterlogin;"
& _
"Network Library=dbmssocn;Data Source=MyServer"
and now I am stuck. What do I assign for the password here? The
IUSR_MachineName password is automatically generated by my Internet
Informaion Services and I really do not know how to access it. All attempts
I have made in the past have been well less then satisfatory. I suppose I
could try it again (requires some Windows Scripting) but I'm hoping there is
a better or easier way. Any suggestions? Thanks.
--
George Hester
__________________________________|||Ah maybe trusted? Let me check. Thanks.
-- George Hester
__________________________________
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:Oy659LawDHA.1788@.tk2msftngp13.phx.gbl...
> George,
> > If I understand your question, you need to specify in the connection =string
> that you want to do a Windows login. There are examples of connection
> strings at www.connectionstrings.com.
> > -- > Tibor Karaszi, SQL Server MVP
> Archive at:
> =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ece5WeTwDHA.2308@.TK2MSFTNGP11.phx.gbl...
> In SQL 2000 SP3.
> > I was trying to follow this page:
> > http://support.microsoft.com/default.aspx?kbid=3D299987
> > It's not that I cannot follow it but that I'd prefer to have the =accound
> that will access the database be IUSR_MachineName. So I gave
> IUSR_MachineName the ability to logon on to this database in SQL 2000 =and no
> other.
> > I get to the point in the article where it has this for the connection
> string:
> > > StrConnect =3D "Provider=3DSQLOLEDB.1;User
> ID=3DIUSR_MachineName;Password=3D<strong password>;Initial =Catalog=3Dhesterlogin;"
> & _
> "Network Library=3Ddbmssocn;Data Source=3DMyServer"
> > and now I am stuck. What do I assign for the password here? The
> IUSR_MachineName password is automatically generated by my Internet
> Informaion Services and I really do not know how to access it. All =attempts
> I have made in the past have been well less then satisfatory. I =suppose I
> could try it again (requires some Windows Scripting) but I'm hoping =there is
> a better or easier way. Any suggestions? Thanks.
> > -- > George Hester
> __________________________________
> >|||Actually I was hoping to use IUSR_MachineName for the Windows Logon =account. But I see I need to put a password in for the connection =string. As my IIS controls the IUSR_MachineName password I cannot =hardcode that into the connection string. That is my issue. What do I =put in for the password so that IIS can continue to automatically =generate the IUSR_MachineName account's password and use that in my =connection string? Thanks.
-- George Hester
__________________________________
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:Oy659LawDHA.1788@.tk2msftngp13.phx.gbl...
> George,
> > If I understand your question, you need to specify in the connection =string
> that you want to do a Windows login. There are examples of connection
> strings at www.connectionstrings.com.
> > -- > Tibor Karaszi, SQL Server MVP
> Archive at:
> =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ece5WeTwDHA.2308@.TK2MSFTNGP11.phx.gbl...
> In SQL 2000 SP3.
> > I was trying to follow this page:
> > http://support.microsoft.com/default.aspx?kbid=3D299987
> > It's not that I cannot follow it but that I'd prefer to have the =accound
> that will access the database be IUSR_MachineName. So I gave
> IUSR_MachineName the ability to logon on to this database in SQL 2000 =and no
> other.
> > I get to the point in the article where it has this for the connection
> string:
> > > StrConnect =3D "Provider=3DSQLOLEDB.1;User
> ID=3DIUSR_MachineName;Password=3D<strong password>;Initial =Catalog=3Dhesterlogin;"
> & _
> "Network Library=3Ddbmssocn;Data Source=3DMyServer"
> > and now I am stuck. What do I assign for the password here? The
> IUSR_MachineName password is automatically generated by my Internet
> Informaion Services and I really do not know how to access it. All =attempts
> I have made in the past have been well less then satisfatory. I =suppose I
> could try it again (requires some Windows Scripting) but I'm hoping =there is
> a better or easier way. Any suggestions? Thanks.
> > -- > George Hester
> __________________________________
> >
all the data coming from sql unreadable like this one (CC?E C?CI?)
all the data coming from sql unreadable like this one (C?C?E C'CI')
If you go to this page http://72.41.53.93/new_index.asp
you can see the Arabic character which is typed in the page coming
correctly.
while , on the other hand , the one comes form database comes
unreadable ?EEC? OE?E
Please I need your help , I do not want to lose my data in sql.Hi
How do you store arabic characters in the database?
I mean have you defined NAVARCHAR(n) daratype forther column/s?
<malbar99@.gmail.com> wrote in message
news:1144919506.830104.45910@.t31g2000cwb.googlegroups.com...
> all the data coming from sql unreadable like this one (C?C?E C'CI')
> If you go to this page http://72.41.53.93/new_index.asp
> you can see the Arabic character which is typed in the page coming
> correctly.
> while , on the other hand , the one comes form database comes
> unreadable ?EEC? OE?E
> Please I need your help , I do not want to lose my data in sql.
>
If you go to this page http://72.41.53.93/new_index.asp
you can see the Arabic character which is typed in the page coming
correctly.
while , on the other hand , the one comes form database comes
unreadable ?EEC? OE?E
Please I need your help , I do not want to lose my data in sql.Hi
How do you store arabic characters in the database?
I mean have you defined NAVARCHAR(n) daratype forther column/s?
<malbar99@.gmail.com> wrote in message
news:1144919506.830104.45910@.t31g2000cwb.googlegroups.com...
> all the data coming from sql unreadable like this one (C?C?E C'CI')
> If you go to this page http://72.41.53.93/new_index.asp
> you can see the Arabic character which is typed in the page coming
> correctly.
> while , on the other hand , the one comes form database comes
> unreadable ?EEC? OE?E
> Please I need your help , I do not want to lose my data in sql.
>
all the data coming from sql unreadable like this one (CC?E C?CI?)
all the data coming from sql unreadable like this one (C?C?E C'CI')
If you go to this page http://72.41.53.93/new_index.asp
you can see the Arabic character which is typed in the page coming
correctly.
while , on the other hand , the one comes form database comes
unreadable ?EEC? OE?E
Please I need your help , I do not want to lose my data in sql.Hi
How do you store arabic characters in the database?
I mean have you defined NAVARCHAR(n) daratype forther column/s?
<malbar99@.gmail.com> wrote in message
news:1144919506.830104.45910@.t31g2000cwb.googlegroups.com...
> all the data coming from sql unreadable like this one (C?C?E C'CI')
> If you go to this page http://72.41.53.93/new_index.asp
> you can see the Arabic character which is typed in the page coming
> correctly.
> while , on the other hand , the one comes form database comes
> unreadable ?EEC? OE?E
> Please I need your help , I do not want to lose my data in sql.
>
If you go to this page http://72.41.53.93/new_index.asp
you can see the Arabic character which is typed in the page coming
correctly.
while , on the other hand , the one comes form database comes
unreadable ?EEC? OE?E
Please I need your help , I do not want to lose my data in sql.Hi
How do you store arabic characters in the database?
I mean have you defined NAVARCHAR(n) daratype forther column/s?
<malbar99@.gmail.com> wrote in message
news:1144919506.830104.45910@.t31g2000cwb.googlegroups.com...
> all the data coming from sql unreadable like this one (C?C?E C'CI')
> If you go to this page http://72.41.53.93/new_index.asp
> you can see the Arabic character which is typed in the page coming
> correctly.
> while , on the other hand , the one comes form database comes
> unreadable ?EEC? OE?E
> Please I need your help , I do not want to lose my data in sql.
>
Subscribe to:
Posts (Atom)