Into with index

[micang]

Hi all,

Is it possible when creating a table (into) from a table and one one of the columns outputted has an index, to output the index on the new table?

I have noticed that everytime you do a 'into' table, it does not keep indexes etc.

thanks

micanguk

 

[SQLDenis]

No

you have to do something like this

select * into NewAuthors
from authors

create clustered index idx_au_id on NewAuthors (au_id)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[micang]

ta Denis, does this mean though that it wil take longer to create the table?

also, because I am a newbie, i have been creating indexes in Enterprise Manager (SQL 2000), and they take quiet a while to create, so was wondering if it's quicker creating them in Query Analyser.

thanks
micanguk

 

[SQLDenis]

It is always faster to populate the table and to create the index than creating an index and then populating the table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[micang]

thanks denis,

"create clustered index idx_au_id on NewAuthors (au_id)"

can you please let me know wha the "(au_id)" does at the end?

 

[SQLDenis]

au_id is a column name

create clustered index idx_au_id on NewAuthors (au_id)"

is the same as

create clustered index IndexName on TableName (ColumnName)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[micang]

thanks so much denis

 

[micang]

Hi,

Can someone please please explain the difference between a "constraint" and a "index" when setting up a index? BOL seems not to say much about this.

ta

micanguk

 

[jbenson001]

From BOL:

Constraints allow you to define the way Microsoft® SQL Server™ 2000 automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.

Indexes can be used to constrain data also, the differences is that indexes are used to optimise the quering of data, constraints do not.

Jim

 

[micang]

So would choosing index as opposed to constraint generally be a better option?

 

[jbenson001]

It depends on the circumstances. If the columns will used in an order by or join conditions, then use an index. If it is simply to enforce data constraints, then use a constraint.

 

[micang]

thanks jbenson001 for your help.

my logic tells me then, that if its on a column where the values/data will never change, then index makes sense? For example on a unique identifier column?

 

[jbenson001]

Indexes can be used on columns who's data changes.

If the columns will used in an order by or join conditions, then use an index. If it is simply to enforce data constraints, then use a constraint.

 

[SQLSister]

Constraints can also do things that indexes can't, you might need both. For instance you can constrain a field to be within a set group of values. Where they cross over is in terms of a unique constraint vice a unique index. Often if you want uniqueness, it is a field that will need an index as well and then the unique index is the better choice.

Another thing about constraints is that if you can do something with a constraint or a trigger, it is supposed to be more efficient for the system to do it inthe constraint. Triggers should be left for more complex business rule processing than constraints will allow.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief