how do I alter the column with index on it

hi,
I have a sqlserver 2000 db. I want to alter two columns
from one of the tables to not allow nulls.
but these 2 columns (one if char, and the other is
smalldatetime), one have clustered index on it, and the
other one is part of a combined index of two columns.
is the only way for the column alteration is to drop the
index first, and then re-create it later?
many thanks!
JJ
Hi,
Altering the data type of one column which has participated in an index is
not supported. If the modified column participates in an index, the only
type changes that are allowed are increasing the length of a
variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing
nullability of the column, or both. I believe you have to drop the original
index and rebuild a new index.
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2131001c45a37$30e96600$a501280a@.phx.gbl...
> hi,
> I have a sqlserver 2000 db. I want to alter two columns
> from one of the tables to not allow nulls.
> but these 2 columns (one if char, and the other is
> smalldatetime), one have clustered index on it, and the
> other one is part of a combined index of two columns.
> is the only way for the column alteration is to drop the
> index first, and then re-create it later?
> many thanks!
> JJ
|||You can user table designer shipped with SQL server client tools or
Visuastudio to change the table.
Lishil, VSDATA Team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Subject: how do I alter the column with index on it
>Date: Thu, 24 Jun 2004 15:04:16 -0700
>Lines: 15
>Message-ID: <2131001c45a37$30e96600$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRaNzDpwp7EieNJRQCcEtOkif2wyg==
>Newsgroups:
microsoft.public.sqlserver.programming,microsoft.p ublic.sqlserver.tools
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.tools:24659
microsoft.public.sqlserver.programming:453163
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>hi,
>I have a sqlserver 2000 db. I want to alter two columns
>from one of the tables to not allow nulls.
>but these 2 columns (one if char, and the other is
>smalldatetime), one have clustered index on it, and the
>other one is part of a combined index of two columns.
>is the only way for the column alteration is to drop the
>index first, and then re-create it later?
>many thanks!
>JJ
>
|||thanks Lishil. I know the tool to alter it, I just want
to know whether I have to drop the index first before I
alter the column. please see my privious email for detail.
thanks.
JJ
>--Original Message--
>You can user table designer shipped with SQL server
client tools or
>Visuastudio to change the table.
>Lishil, VSDATA Team
>
>--
>microsoft.public.sqlserver.programming,microsoft. public.sq
lserver.tools[vbcol=seagreen]
microsoft.public.sqlserver.tools:24659
>microsoft.public.sqlserver.programming:453163
>.
>
|||thanks Hari. I fear so too. oh well...
thanks.
JJ
>--Original Message--
>Hi,
>Altering the data type of one column which has
participated in an index is
>not supported. If the modified column participates in an
index, the only
>type changes that are allowed are increasing the length
of a
>variable-length type (for example, VARCHAR(10) to VARCHAR
(20)), changing
>nullability of the column, or both. I believe you have to
drop the original
>index and rebuild a new index.
>
>--
>Thanks
>Hari
>MCDBA
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2131001c45a37$30e96600$a501280a@.phx.gbl...
>
>.
>
|||Ok, you already get answer from hari_prasad_k@.hotmail.com.
You do need to drop index in you case.
Lishi Liu, VSData team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>References: <2131001c45a37$30e96600$a501280a@.phx.gbl>
<$AXF2GuXEHA.2352@.cpmsftngxa06.phx.gbl>
>Subject: RE: how do I alter the column with index on it
>Date: Thu, 8 Jul 2004 18:24:14 -0700
>Lines: 61
>Message-ID: <2962a01c46553$72445bb0$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRlU3JEKPTFyjEUT129z6+ojcBcmg==
>Newsgroups:
microsoft.public.sqlserver.tools,microsoft.public. sqlserver.programming
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:456466
microsoft.public.sqlserver.tools:24467
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>thanks Lishil. I know the tool to alter it, I just want
>to know whether I have to drop the index first before I
>alter the column. please see my privious email for detail.
>thanks.
>JJ
>client tools or
>lserver.tools
>microsoft.public.sqlserver.tools:24659
>

How cursors work internally and what's the most efficient way?

Hello-
The system in question is on SQL Server 7.0/sp4.
I use a cursor to rebuild index (DBCC DBREINDEX). The database has size of around 57+ GB while the fragmentation causes the size to hover around 100 GB Mark (drive where data device exists is around 140 GB while the log device drive has 35 GB empty space as well... so space is not a problem).
What's happening is that previously when there were around 1600+ tables, the scheduled task run without a problem. However, for last two weeks the scheduled task ends in failure. I checked and found 2 tables with errors that I fixed with DBCC CHECKTABLE.
What puzzles me is that there were some (I think 2 tables) that got stuck as I try to run the same script through Query Analyzer that I run scheduled. However, when rebuilt indexes manually for those tables individually, it was done in no time. What kind of problem does this show?
For using the STATIC option, how much space should I have on tempdb? currently we have 10 GB.
Thanks a lot!
MZeeshanHi MZeeshan,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
According on your description, I am not quite clear what the problem is on
your side. I would like you to provide more information so that I can help
you narrow down this issue.
1. What is your accurate concern? As I understand, the job failed and table
occurred on your side, however, after you fix using DBCC CHECKTABLE, the
error always occurs next time on the same table. If I have misunderstood,
please feel free to let me know and describe it in detail.
2. If you DBCC DBREINDEX on the 2 tables in another job, did the same
problem occur again?
Also, I would like you to provide the following detailed information so
that I can perform further research on my side.
1. When the job failed, what is the error message? Please provide the
detailed error message.
2 Please provide the Sqldiag.txt using Sqldiag utility. You can send it to
me at v-yshao@.microsoft.com.
For more in formation regarding sqldiag utility, please refer to the
following article on SQL Server Books Online.
Topic: "Sqldiag Utility"
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Muhammad,
According to your description, I understand that when you performed DBCC
DBREINDEX individually for each table, there is not any problem. However,
when you run the scripts in a job, which performed the DBCC DBREINDEX
command for very small tables in a loop with cursor, the job didn't
complete in expected time. If I have misunderstood, please feel free to let
me know.
It is a hard work reviewing the scripts and Sqldiag.txt. According to the
error log, it seems that there are not any abnormal things occurring.
I would like you to provide the following information so that I can narrow
down this issue.
1. Can you please tell me how long it takes to finish running the SQL
scripts? I also would like to know your expected time.
2. Please try to perform the same script on another database, does the same
problem occur? Does the problem only occur on a specific database having
many small tables?
Please try to use the following script to see if it will decrease the
execute time.
//////////////////
CREATE PROCEDURE REINDEX_ALL
WITH RECOMPILE AS
SET NOCOUNT ON
DECLARE @.TableName varchar(300)
DECLARE @.DisplayString varchar(255)
/*----
--
--
Select all table names for REINDEX
----
--
--*/
DECLARE MainTableNamesCursor CURSOR FOR
SELECT o.name
FROM sysobjects o
WHERE o.type = 'U'
ORDER BY o.name
OPEN MainTableNamesCursor
FETCH NEXT FROM MainTableNamesCursor INTO @.TableName
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
SELECT @.DisplayString = 'REINDEX ' + @.TableName
SELECT @.DisplayString
EXEC ('DBCC DBREINDEX (' + @.TableName + ')')
FETCH NEXT FROM MainTableNamesCursor INTO @.TableName
END
END
DEALLOCATE MainTableNamesCursor
SET NOCOUNT OFF
//////////////////////
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

how check the existing index

Hello,
How can I check an existing index on a table?
I have found this:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'my_ind_name')
DROP INDEX my_table_name.my_ind_name
My Problem is following:
1. What happens when this index is on an other table (e.g 'my_other_table')
and not on the original table ('my_table_name') Will I get an error
message? I would like to avoid this.
2. As far as I know it is not recommended to reffer to system objects
directly.
Instead of it I should use INFORMATION_SCHEMA. But how?
Thanks for any help
Danyi, AttilaTry,
if exists(
select
*
from
sysindexes
where
[id] = object_id('owner.table_name')
and [indid] between 1 and 254
and indexproperty([id], [name], 'IsStatistics') = 0
and indexproperty([id], [name], 'IsHypothetical') = 0
and [name] = 'index_name'
)
...
There is not an information_schema view to query about indexes.
AMB
"Danyi, Attila" wrote:

> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g 'my_other_table
')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>|||> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
You need to also filter on table:
IF EXISTS (SELECT * FROM sysindexes
WHERE name = 'my_ind_name' AND
OBJECT_NAME(id) = N'my_table_name')
DROP INDEX my_table_name.my_ind_name

> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
Indexes are not exposed via the INFORMATION_SCHEMA views because these are
implementation specific. It's ok to use documented system table columns and
functions to query meta data, although I prefer to use the
INFORMATION_SCHEMA views when possible.
Hope this helps.
Dan Guzman
SQL Server MVP
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:4241A175-9CA7-4B83-903D-E2787BE3927C@.microsoft.com...
> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>

How can we list all the tables without any clustered index within a SQL Server 2005 database?

Hi ,All,

Would please anyone here give me any advice and guidance for how to list all the tables without a clustered key within a SQL Server 2005 database?

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Switch to the database you want to analyse and run this script:

select

sys.schemas.nameas [Schema],

sys.tables.nameas [Table]

from sys.tables

leftouterjoinsys.schemasonsys.schemas.schema_id =sys.tables.schema_id

where sys.tables.object_id not in

(

select

sys.indexes.object_id

fromsys.indexes

wheresys.indexes.type= 1

)

|||

Hi, Daniel, thanks a lot.

With best regards,

|||

Take a look at

http://msdn2.microsoft.com/en-us/library/ms345522.aspx#_FAQ1

It answers exactly the question you have asked

|||

Hi, Asvin, thanks a lot for the link. It's very helpful.

With best regards,

Yours sincerely,

How can we list all the tables without any clustered index within a SQL Server 2005 database

Hi ,All,

Would please anyone here give me any advice and guidance for how to list all the tables without a clustered key within a SQL Server 2005 database?

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Switch to the database you want to analyse and run this script:

select

sys.schemas.name as [Schema],

sys.tables.name as [Table]

from sys.tables

left outer join sys.schemas on sys.schemas.schema_id = sys.tables.schema_id

where sys.tables.object_id not in

(

select

sys.indexes.object_id

from sys.indexes

where sys.indexes.type = 1

)

|||

Hi, Daniel, thanks a lot.

With best regards,

|||

Take a look at

http://msdn2.microsoft.com/en-us/library/ms345522.aspx#_FAQ1

It answers exactly the question you have asked

|||

Hi, Asvin, thanks a lot for the link. It's very helpful.

With best regards,

Yours sincerely,