need cursor help

hi
i want to create the sp to recreate the indexes of all the table. here
is what i need:
CREATE UNIQUE CLUSTERED INDEX
[table_name$32$0_idx] ON
[dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
WITH DROP_EXISTING
in order to get all the <table name> (columns)
i use sp_pkeys:
exec sp_pkeys [table_name$32$0]
here is the table where i insertet sp_pkeys data.
create table keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
i think i need to create a temp table and insert the data into it and
then fetch all i need into cursor ant exec CREATE INDEX WITH
DROP_EXISTING statment, but
i don't know how to convert column into a row (as sp_pkeys returns all
the index's keys into 1 column) .
thanxHi,
You could use DBCC DBREINDEX command to Reindex all indexes.
Use the below script to reindex all tables in a database:-
EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
Thanks
HARI
SQL Server MVP
"benamis" <nera@.meilo.lt> wrote in message
news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi
> i want to create the sp to recreate the indexes of all the table. here is
> what i need:
> CREATE UNIQUE CLUSTERED INDEX
> [table_name$32$0_idx] ON
> [dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
> WITH DROP_EXISTING
> in order to get all the <table name> (columns)
> i use sp_pkeys:
> exec sp_pkeys [table_name$32$0]
> here is the table where i insertet sp_pkeys data.
> create table keys (
> table_qualifier sysname,
> table_owner sysname,
> table_name sysname,
> column_name sysname,
> key_seq smallint,
> pk_name sysname
> )
> i think i need to create a temp table and insert the data into it and
> then fetch all i need into cursor ant exec CREATE INDEX WITH DROP_EXISTING
> statment, but
> i don't know how to convert column into a row (as sp_pkeys returns all the
> index's keys into 1 column) .
> thanx|||Maybe this might help:
http://milambda.blogspot.com/2005/0...in-current.html
ML|||yes i know about DBCC DBREINDEX , but CREATE INDEX WITH DROP_EXISTING
is more efective [~2x times] (at least for navision db).
Hari Pra wrote:
> Hi,
> You could use DBCC DBREINDEX command to Reindex all indexes.
> Use the below script to reindex all tables in a database:-
> EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
> Thanks
> HARI
> SQL Server MVP
> "benamis" <nera@.meilo.lt> wrote in message
> news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
>
>|||If you want to rebuild indexes without using the DBCC command, you can scrip
t
your the indexes and maybe create a job using this script.
You can script any object easily in the Enterprise Manager.
ML

NEED COMMENTS: Why SQL Server Allow so

this is not a question i need nice comments on this

"Why did SQL Server can have same name of indexes on different tables, but Oracle do not"

(i maen SQL Server allow same Index name, for any number of time on different tables, but Oracle dont allow to do so)

my personal Opinion is , Oracel is Right.(if so then why SQL Server allow this)

what about you?

Regards,

Thanks.

Gurpreet S. Gill

? Why not? Indexes are scoped per table -- so there is no clash by having multiple tables with similarly-named indexes. It's the same with schemas and table names. Both Oracle and SQL Server allow you to create tables of the same name that exist in different schemas. Do you think that's a problem? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Gurpreet Singh Gill@.discussions.microsoft..com> wrote in message news:c07ec808-144e-4499-8d5e-bcaf7edeffd5_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com this is not a question i need nice comments on this "Why did SQL Server can have same name of indexes on different tables, but Oracle do not" (i maen SQL Server allow same Index name, for any number of time on different tables, but Oracle dont allow to do so) my personal Opinion is , Oracel is Right.(if so then why SQL Server allow this) what about you? Regards, Thanks. Gurpreet S. Gill|||

hi Adam Machanic

I think you dont understand what my statment is:

As example for SQL Server(2005)

TABLE NAME INDEX NAME

--

ABC ABC_IND

DEF ABC_IND

GHI ABC_IND

JKL ABC_IND

SQL Server allow us to have same index name for different table

But in ORACLE

this thing is not possible, the oracle says, if you create an oblect(ABC_IND) its name shold be unique for the whold schema, if i try to create the other index with same name as ABC_IND, this throws an error.( this is right).

As example for ORACLE(10g)

TABLE NAME INDEX NAME

--

ABC ABC_IND

DEF ABC_IND (this throws error,as the name ABC_IND already used)

GHI ABC_IND(this throws error,as the name ABC_IND already used)

JKL ABC_IND(this throws error,as the name ABC_IND already used)

Why did SQL Server allow to do so?

Any comment?

Regards,

Thanks.

Gurpreet S. Gill

|||

I thin kthe question should be the other way round: why does Oracle NOT allow indices with the same name on different object? The answer could be that their dictionary and architecture have a different basic/philosphy than SQL Server. An example for those differences might be that you have only one database in one Oracle instance vs. multiple databases in one SQL Server instance.

It looks like that indexes are treated in Oracle as a separate object and not dependent on other objects, but I am not sure.

Ragrds

Norbert

|||

Not sure if this is still the case but this discussion extends to teh size of names.

Why does Oracle only allow short names, SQL Server allows really long names like.

create myTableThatHasAVeryLongNameBecauseSQLServerIsBetterThanOracle_SoThere

:)

|||? Yes, I do understand your statement, and I don't agree that Oracle is "right" in this regard. Indexes are not directly queryable or manipulable objects, and are therefore it does not make sense to have them be schema or database-scoped. They are table-scoped, and only make sense in the context of the table on which they're created. Name resolution can, therefore, act at the table level, and uniqueness across tables does not need to be enforced. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Gurpreet Singh Gill@.discussions.microsoft..com> wrote in message news:37f35485-81a0-4f41-beb7-aca9ffe2866c@.discussions.microsoft.com... hi Adam Machanic I think you dont understand what my statment is: As example for SQL Server(2005) TABLE NAME INDEX NAME -- ABC ABC_IND DEF ABC_IND GHI ABC_IND JKL ABC_IND SQL Server allow us to have same index name for different table But in ORACLE this thing is not possible, the oracle says, if you create an oblect(ABC_IND) its name shold be unique for the whold schema, if i try to create the other index with same name as ABC_IND, this throws an error.( this is right). As example for ORACLE(10g) TABLE NAME INDEX NAME -- ABC ABC_IND DEF ABC_IND (this throws error,as the name ABC_IND already used) GHI ABC_IND(this throws error,as the name ABC_IND already used) JKL ABC_IND(this throws error,as the name ABC_IND already used) Why did SQL Server allow to do so? Any comment? Regards, Thanks. Gurpreet S. Gill|||

hi thanks to all who send such a nice comments.

i think this is a question of long debate, but i find two nice comments

i) Indexes having scope only at table level(means, no table can have any relation with index which is on other table), so why to have unique name, for whole database.

ii) Oracel having only one database in one Oracle instance vs. multiple databases in one SQL Server instance.

I think due to these comments, in my opinion SQL-Server wins the race.

Any other comments are welcomed.

Regards,

Thanks.

Gurpreet S. Gill

Need a little help please

I have a simple table:
MyTable
id int null
user varchar(50) null
desc varchar(150) null
No PK or indexes of any kind.
Does the following cause any performance issue:
insert into MyTable (desc, user, id) values ('Bob from Account
Temps','Bob','777')
Does not having indexes or PK hinder the insert? And does the order of the
columns in the insert make a difference?
Thanks all
JD
Every table should have a primary key. Why do you have a table without
one?

> Does not having indexes or PK hinder the insert?
A lack of indexes and constraints doesn't affect the insert per se.
Potentially it affects the *validity* of the insert since you won't be
prevented from inserting redundant data.

> And does the order of the
> columns in the insert make a difference?
If all the column names are listed then logically the order makes no
difference.
David Portas
SQL Server MVP
|||This is a 3rd party application. I'm trying to gather information what will
be presented at a meeting next week.
thanks for the feedback.
JD
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegr oups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>
|||>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
It can, but the effect will probably only surface under more extreme conditions.
If there is a clustered index, SQL Server will immediately know where to put this row. If the cl ix
key is always increasining (which isn't necessarily the optimal cl ix key for *searches*), then you
won't get page splits either.
If there isn't a cl ix, then SQL Server need to go by IAMs and PFS pages (I believe, it is still
early in the morning for me) to determine where to put the rows. I imagine that this can be more
work, but I doubt we see the effect unless large databases and heavy load.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegr oups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>

Need a little help please

I have a simple table:
MyTable
id int null
user varchar(50) null
desc varchar(150) null
No PK or indexes of any kind.
Does the following cause any performance issue:
insert into MyTable (desc, user, id) values ('Bob from Account
Temps','Bob','777')
Does not having indexes or PK hinder the insert? And does the order of the
columns in the insert make a difference?
Thanks all
JDEvery table should have a primary key. Why do you have a table without
one?
> Does not having indexes or PK hinder the insert?
A lack of indexes and constraints doesn't affect the insert per se.
Potentially it affects the *validity* of the insert since you won't be
prevented from inserting redundant data.
> And does the order of the
> columns in the insert make a difference?
If all the column names are listed then logically the order makes no
difference.
--
David Portas
SQL Server MVP
--|||This is a 3rd party application. I'm trying to gather information what will
be presented at a meeting next week.
thanks for the feedback.
JD
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
> > Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
> > And does the order of the
> > columns in the insert make a difference?
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>|||>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
It can, but the effect will probably only surface under more extreme conditions.
If there is a clustered index, SQL Server will immediately know where to put this row. If the cl ix
key is always increasining (which isn't necessarily the optimal cl ix key for *searches*), then you
won't get page splits either.
If there isn't a cl ix, then SQL Server need to go by IAMs and PFS pages (I believe, it is still
early in the morning for me) to determine where to put the rows. I imagine that this can be more
work, but I doubt we see the effect unless large databases and heavy load.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>> And does the order of the
>> columns in the insert make a difference?
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>

Need a little help please

I have a simple table:
MyTable
id int null
user varchar(50) null
desc varchar(150) null
No PK or indexes of any kind.
Does the following cause any performance issue:
insert into MyTable (desc, user, id) values ('Bob from Account
Temps','Bob','777')
Does not having indexes or PK hinder the insert? And does the order of the
columns in the insert make a difference?
Thanks all
JDEvery table should have a primary key. Why do you have a table without
one?

> Does not having indexes or PK hinder the insert?
A lack of indexes and constraints doesn't affect the insert per se.
Potentially it affects the *validity* of the insert since you won't be
prevented from inserting redundant data.

> And does the order of the
> columns in the insert make a difference?
If all the column names are listed then logically the order makes no
difference.
David Portas
SQL Server MVP
--|||This is a 3rd party application. I'm trying to gather information what will
be presented at a meeting next week.
thanks for the feedback.
JD
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>|||>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
It can, but the effect will probably only surface under more extreme conditi
ons.
If there is a clustered index, SQL Server will immediately know where to put
this row. If the cl ix
key is always increasining (which isn't necessarily the optimal cl ix key fo
r *searches*), then you
won't get page splits either.
If there isn't a cl ix, then SQL Server need to go by IAMs and PFS pages (I
believe, it is still
early in the morning for me) to determine where to put the rows. I imagine t
hat this can be more
work, but I doubt we see the effect unless large databases and heavy load.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>