Sql Server Alignment problem: index

Showing posts with label index. Show all posts

Tuesday, March 27, 2012

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:

> Is there a way to alter a unique clustered index in a table to a primary k
ey
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just t
o
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...[vbcol=seagreen]
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:
> Is there a way to alter a unique clustered index in a table to a primary key
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just to
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>> Is there a way to alter a unique clustered index in a table to a primary
>> key
>> with some magic alter statement?
>> What I want to avoid (if possible) is to run drop/create statement, just
>> to
>> make already unique clustered index to a Primary key.
>> I appreciate your reply. I have sql server 2000 SP4.
>>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

Altering column with index

Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?You'll need to drop the indexes/constraints and recreate after changing the
collation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||Drop the indexes (and any constraints) first, then alter the column and
restore the indexes (and constraints).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?|||Whell Tom
It seems to be very agly to do that.
When i do it with the enterprise Manager i don't do all this stuff
Are you sure i need to do all of that?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Drop the indexes (and any constraints) first, then alter the column and
> restore the indexes (and constraints).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||
Roy Goldhammer wrote:

>Whell Tom
>It seems to be very agly to do that.
>When i do it with the enterprise Manager i don't do all this stuff
>
Enterprise Manager probably does it for you behind the scenes. While
EM doesn't do everything in the best way, you could generate the change
script from EM and use it as a template for creating your own script. If
an index exists on a varchar column, and then the collation is changed,
the index must be rebuilt. There's no way around it, since a change in
collation may change the ordering of the values, which is what the
index is there to implement.
Steve Kass
Drew University

>Are you sure i need to do all of that?
>"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>
>

Sunday, March 25, 2012

Altering a column which has an index defined on it

Hello,

I'm trying the following test (which works like a charm on Oracle)

create table x1(c1 numeric(10), c2 numeric(5,1))
create index x1_c2_idx on x1(c2)
alter table x1 alter column c2 numeric(9,1)

I get the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'x1_c2_idx' is dependent on column 'c2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN c2 failed because one or more objects access this column.

Is there a way to alter the column WITHOUT dropping the index ?

Regards,

Tal Olier
otal@.mercury.co.ilNo, you must drop the index first.|||Originally posted by Paul Young
No, you must drop the index first.

Thanks.

Tuesday, March 20, 2012

ALTER TABLE DEFAULT

/* for the google index */
ALTER TABLE
DEFAULT COLUMN
DEFAULT VALUE

I've worked out several stored procedures for altering the default column
values in a table. They were compiled from books and code snippets found
here. It was a pain to work out so I've decided to share my work and
research here. This post is just my way of saying thanks to several others
here for posting with their wisdom and intelligence.

Michael
simpsonAT(dot)cts(dot)com

This procedure gets the constraint name. If you use the design view to
setup a default value, you won't know the system assigned constraint name.
This proc makes it an non issue. This code was gleened from this news
group.
CREATE PROCEDURE [DBO].[GetConstraintName]
(
@.tablename sysname,
@.columnName sysname,
@.constraintName sysname OUTPUT
)
as
SELECT
@.constraintName = o1.name
FROM
sysobjects o1
INNER JOIN
syscolumns c ON o1.id = c.cdefault
INNER JOIN
sysobjects o2 ON o1.parent_obj = o2.id
WHERE (o2.name = @.tablename) AND (c.name = @.columnName)

This procedure changes the default value for a column that is a numeric. It
uses the previously define stored procedure to get the constraint name. A
text version of this procedure can be created by removing the cast, defining
the input parameter "newConstraint" as varchar(255).

CREATE PROCEDURE [dbo].[ChangeIntConstraint]
(
@.tableName sysname,
@.columnName sysname,
@.newConstraint int
)
AS

Declare @.conName sysname

exec GetConstraintName @.tableName, @.columnName, @.constraintName = @.conName
OUT

declare @.sql nvarchar(1024)

set @.sql = 'ALTER TABLE ' + @.tableName + ' drop constraint ' + @.conName
exec(@.sql)

set @.sql = 'ALTER TABLE ' + @.tableName + ' ADD CONSTRAINT ' + @.conName + '
DEFAULT (' + CAST(@.newConstraint AS varchar(255)) + ') FOR ' + @.columnName
exec(@.sql)Why not make @.newConstraint character.
Then you can get the column type in the SP and cater for numeric and
character columns and dates.|||I am by no means an expert. If you want to post that change here, please do.
I think I know what you mean, but it would take even more time for me to
hack it out.

I did a Google news search on the subject. I saw that it had been asked
numerous times in the past. Some people responded with dropping and
recreating the table. I was just sharing what took me several hours to hash
out.

Michael

"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:7d6610e9.0311021918.19e6c409@.posting.google.c om...
> Why not make @.newConstraint character.
> Then you can get the column type in the SP and cater for numeric and
> character columns and dates.|||Haven't tested it but this sould be quite close. Change the constraint
value parameter to character

The relies on dates being passed in in a good format - e.g. 'yyyymmdd
hh:mm:ss'

declare @.datatype varchar(10)
select @.datatype = case when DATA_TYPE like '%char%' then 'char'
when DATA_TYPE like '%date%' then 'date'
else 'numeric'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.tableName
andCOLUMN_NAME = @.columnName

select @.sql = 'ALTER TABLE ' + @.tableName
select@.sql = @.sql +' ADD CONSTRAINT ' + @.conName
if @.datatype = numeric
select@.sql = @.sql + ' DEFAULT (' + @.newConstraint + ')'
else
select@.sql = @.sql + ' DEFAULT (''' + @.newConstraint + ''')'
select@.sql = @.sql + ' FOR ' + @.columnName

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Alter table changes optimizer query plan

I altered a table that had sever columns defined as float to decimal. Now for some reason instead of using the index for it is using a sequential scan. I have updated the statistics, rebuilt the indexes and about everthing else I can think of. It simply refuses to use the index it did prior to the alter
Anyone have a clue as to what is going on?Is the comparison done against a variable or another column which is of the
float datatype? Float has higher datatype precedence, so the decimal need to
first be converted to float before that comparison can be performed which
prohibits the usage of index. If you code the code, or preferable a
simplified example that displays the behavior we might be able to comment...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||Hi Redmund,
Are you comparing the column with a variable of data type float?
In that case, due to the rules of data type precedence, the decimal will be
implicitly converted into a float, and the implicit convert prevents the use
of an index on the column.
Change the variable to decimal as well.
--
Jacco Schalkwijk
SQL Server MVP
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||The columns that were altered are NOT part of the index nor are they used in the criteria of the query.|||Hi,
Can you posts your table(s), indexes and query, so that we can study that?
--
Jacco Schalkwijk
SQL Server MVP
"RedMud" <anonymous@.discussions.microsoft.com> wrote in message
news:810E5CE2-29CE-490B-BFFD-5A58EA99048B@.microsoft.com...
> The columns that were altered are NOT part of the index nor are they used
in the criteria of the query.
>

Alter table changes optimizer query plan

quote:

> I altered a table that had sever columns defined as float to decimal. Now

for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.

quote:

> Anyone have a clue as to what is going on?

|||Hi Redmund,
Are you comparing the column with a variable of data type float?
In that case, due to the rules of data type precedence, the decimal will be
implicitly converted into a float, and the implicit convert prevents the use
of an index on the column.
Change the variable to decimal as well.
Jacco Schalkwijk
SQL Server MVP
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...

quote:

> I altered a table that had sever columns defined as float to decimal. Now

quote:

> Anyone have a clue as to what is going on?

|||The columns that were altered are NOT part of the index nor are they used in
the criteria of the query.|||Hi,
Can you posts your table(s), indexes and query, so that we can study that?
Jacco Schalkwijk
SQL Server MVP
"RedMud" <anonymous@.discussions.microsoft.com> wrote in message
news:810E5CE2-29CE-490B-BFFD-5A58EA99048B@.microsoft.com...

quote:

> The columns that were altered are NOT part of the index nor are they used

in the criteria of the query.

quote:

>

Thursday, March 8, 2012

Alter Index with REBUILD on master database?

SQL Server 2005:
We plan to use the alter index with rebuild syntax to rebuild our indexes
weekly in a job. Should master and msdb tables be included? I have no
interest in doing it manually a couple times per year if it needs it.
Thanks,
MarkI remember asking the same thing in a SQL 2000 forum a long time ago and the
consensus was you never need to include any of the system databases in
reindexing / update stats. I would gather the same is applicable to SQL
2005.
HTH,
Rubens
"Mark" <mark@.idonotlikespam.com> wrote in message
news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2005:
> We plan to use the alter index with rebuild syntax to rebuild our indexes
> weekly in a job. Should master and msdb tables be included? I have no
> interest in doing it manually a couple times per year if it needs it.
> Thanks,
> Mark
>|||Why not?
"Rubens" <rubensrose@.hotmail.com> wrote in message
news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
>I remember asking the same thing in a SQL 2000 forum a long time ago and
>the consensus was you never need to include any of the system databases in
>reindexing / update stats. I would gather the same is applicable to SQL
>2005.
> HTH,
> Rubens
> "Mark" <mark@.idonotlikespam.com> wrote in message
> news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2005:
>> We plan to use the alter index with rebuild syntax to rebuild our indexes
>> weekly in a job. Should master and msdb tables be included? I have no
>> interest in doing it manually a couple times per year if it needs it.
>> Thanks,
>> Mark|||Because in SQL2005 there shouldn't be any tables (of consequence) in the
master database that you can actually run UPDATE STATISTICS or rebuild
indexes.
Linchi
"Mark" wrote:
> Why not?
> "Rubens" <rubensrose@.hotmail.com> wrote in message
> news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
> >I remember asking the same thing in a SQL 2000 forum a long time ago and
> >the consensus was you never need to include any of the system databases in
> >reindexing / update stats. I would gather the same is applicable to SQL
> >2005.
> >
> > HTH,
> > Rubens
> >
> > "Mark" <mark@.idonotlikespam.com> wrote in message
> > news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> >> SQL Server 2005:
> >>
> >> We plan to use the alter index with rebuild syntax to rebuild our indexes
> >> weekly in a job. Should master and msdb tables be included? I have no
> >> interest in doing it manually a couple times per year if it needs it.
> >>
> >> Thanks,
> >> Mark
> >>
>
>

alter index syntax

I'm using sql server 2005 sp1 on win2003 server
I want to reorganize all indices in my database.
Online-docu says that dbcc indexdefrag should not be used anymore.
Instead, 'alter index' should be used
My syntax for one table is like this:
alter index all on bew reorganize
sql server says:
Meldung 156, Ebene 15, Status 1, Zeile 1
Incorrect syntax near the keyword 'index'.
What's wrong ?
Furthermore I would like to know, what the sql-command looks like for
reorganizing indices for
ALL the tables in the database?My guess database isn't in 90 compatibility mode. See sp_dbcmptlevel. Also, see Books Online for
sample code on how to reorg all indexes for a database:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1154958955.385435.160610@.n13g2000cwa.googlegroups.com...
> I'm using sql server 2005 sp1 on win2003 server
> I want to reorganize all indices in my database.
> Online-docu says that dbcc indexdefrag should not be used anymore.
> Instead, 'alter index' should be used
> My syntax for one table is like this:
> alter index all on bew reorganize
> sql server says:
> Meldung 156, Ebene 15, Status 1, Zeile 1
> Incorrect syntax near the keyword 'index'.
> What's wrong ?
> Furthermore I would like to know, what the sql-command looks like for
> reorganizing indices for
> ALL the tables in the database?
>|||Hi Tibor,
that might be the reason.
So I tried to use this command
sp_dbcmptlevel mydb, 90
I get this error:
Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Inside the procedure I can see that only the levels 60, 65, 70, 80 are
allowed.
Also using object explorer (db options) I only can see 70 and 80.
What's the matter?
I'm testing with a new installed 2005 instance,
The database 'comes' from SQL 7.0 and I did a restore of the .bak file
on the 2005 server
Thank you for more help|||This sounds like you have connected to a SQL 2000 instance.
Try this:
SELECT serverproperty('ProductVersion')
--
HTH
Kalen Delaney, SQL Server MVP
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1155022695.236345.244990@.b28g2000cwb.googlegroups.com...
> Hi Tibor,
> that might be the reason.
> So I tried to use this command
> sp_dbcmptlevel mydb, 90
> I get this error:
> Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
> Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
> Inside the procedure I can see that only the levels 60, 65, 70, 80 are
> allowed.
> Also using object explorer (db options) I only can see 70 and 80.
> What's the matter?
> I'm testing with a new installed 2005 instance,
> The database 'comes' from SQL 7.0 and I did a restore of the .bak file
> on the 2005 server
> Thank you for more help
>

alter index syntax

I'm using sql server 2005 sp1 on win2003 server
I want to reorganize all indices in my database.
Online-docu says that dbcc indexdefrag should not be used anymore.
Instead, 'alter index' should be used
My syntax for one table is like this:
alter index all on bew reorganize
sql server says:
Meldung 156, Ebene 15, Status 1, Zeile 1
Incorrect syntax near the keyword 'index'.
What's wrong ?
Furthermore I would like to know, what the sql-command looks like for
reorganizing indices for
ALL the tables in the database?My guess database isn't in 90 compatibility mode. See sp_dbcmptlevel. Also,
see Books Online for
sample code on how to reorg all indexes for a database:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-
e57702230613.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1154958955.385435.160610@.n13g2000cwa.googlegroups.com...
> I'm using sql server 2005 sp1 on win2003 server
> I want to reorganize all indices in my database.
> Online-docu says that dbcc indexdefrag should not be used anymore.
> Instead, 'alter index' should be used
> My syntax for one table is like this:
> alter index all on bew reorganize
> sql server says:
> Meldung 156, Ebene 15, Status 1, Zeile 1
> Incorrect syntax near the keyword 'index'.
> What's wrong ?
> Furthermore I would like to know, what the sql-command looks like for
> reorganizing indices for
> ALL the tables in the database?
>|||Hi Tibor,
that might be the reason.
So I tried to use this command
sp_dbcmptlevel mydb, 90
I get this error:
Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Inside the procedure I can see that only the levels 60, 65, 70, 80 are
allowed.
Also using object explorer (db options) I only can see 70 and 80.
What's the matter?
I'm testing with a new installed 2005 instance,
The database 'comes' from SQL 7.0 and I did a restore of the .bak file
on the 2005 server
Thank you for more help|||This sounds like you have connected to a SQL 2000 instance.
Try this:
SELECT serverproperty('ProductVersion')
HTH
Kalen Delaney, SQL Server MVP
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1155022695.236345.244990@.b28g2000cwb.googlegroups.com...
> Hi Tibor,
> that might be the reason.
> So I tried to use this command
> sp_dbcmptlevel mydb, 90
> I get this error:
> Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
> Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
> Inside the procedure I can see that only the levels 60, 65, 70, 80 are
> allowed.
> Also using object explorer (db options) I only can see 70 and 80.
> What's the matter?
> I'm testing with a new installed 2005 instance,
> The database 'comes' from SQL 7.0 and I did a restore of the .bak file
> on the 2005 server
> Thank you for more help
>

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

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?
>>

Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegr oups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----
--';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegroups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '['
+
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----
--';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegroups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

Tuesday, March 27, 2012

altering unique index to primary key

altering unique index to primary key

Altering column with index

Sunday, March 25, 2012

Altering a column which has an index defined on it

Tuesday, March 20, 2012

ALTER TABLE DEFAULT

Alter table changes optimizer query plan

Alter table changes optimizer query plan

Thursday, March 8, 2012

Alter Index with REBUILD on master database?

alter index syntax

alter index syntax

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

Alter Index issue & Try/Catch question

Alter Index issue & Try/Catch question

Alter Index issue & Try/Catch question

Sql Server Alignment problem

Blog Archive

About Me