Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
california6
Starting Member
38 Posts |
 Posted - 2008-08-11 : 21:45:45
|
| I have 4 indexes (1 clustered and 3 non-clustered) on a table which is 105 GB. I want to run the following code to rebuild all the indexes on this table:ALTER INDEX ALL ON Product REBUILDQuestion is - During this operation will other users be able to access the product table? I know SQL 2005 supports online index operations. But wasn't sure if it supports the following data types:Table consist of 7 columns and the data types are as follows:3 - uniqueidentifier1 - varbinary(16)1 - int1 - image1 - tinyintCan index be created with online option for this table? My SQL version is 2005-64 bit.Appreciated any inputCali |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 22:13:47
|
| You can't rebuild index online if your column datatype consist of LOB(image,varbinary,text,ntext) |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-11 : 22:47:05
|
| You can't rebuild clustered index online on that table. Can rebuild non-clustered index online if blob column is not part of the index. |
|
|
|
california6
Starting Member
38 Posts |
Posted - 2008-08-12 : 16:18:30
|
| Thanks for the update sodeep and rmiao.But i am not specifying "online" option for my index rebuild. I believe to rebuild an index online you must Explicit say about it in the rebuild. I.E ALTER INDEX ALL ON Product REBUILD ONLINE. With my command - thats not true. so how is my index rebuild online?Cali |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
california6
Starting Member
38 Posts |
Posted - 2008-08-13 : 13:09:10
|
| tkizer in order to use your script - do i need to modify any parameters? If no, then all what i need to do is just copy it on my server and run it? How will it run against the user database (Product)? Also separate to this - i would like to understand why cant i run ALTER INDEX ALL ON Product REBUILD against my product table. well it runs sucesfully but i see it did not rebuild the indexes on the table which are image data type. Is there any special way to run this?Cali |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-13 : 13:44:35
|
| You create my stored procedure, then execute it providing the parameters you want. This script acts on all tables that match the input parameters.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
california6
Starting Member
38 Posts |
Posted - 2008-08-13 : 14:42:47
|
| Tkizer thanks a bunch for all your help. Its working fine now.Cali |
|
|
|
|
|
|
Index Rebuild