Index question 1

[babeo]

Hi

- I want to drop an old index and create a new index, also a new constrainst on a table of a production system, is there anything should I pay attention to it or should be done before making these actions? (I believe last time I am able to create an index, but not so sure the affect on dropping a current index).
- Also I want to add/drop a new field to a table, what should be done?
- What is a different between an index and a constrainst

Thanks
bb

 

[babeo]

Also
- If I only have a priviledge of oper_role, am I able to create/drop index, field... ?
Tx
bb

 

[dickiebird]

Here's some answers to your questions :

First a sample to drop an index ( slprdsales_idx ) and create a new index (slprdsales_idx2)

/*
* DROP INDEX slprdsales.slprdsales_idx
*/
IF EXISTS ( SELECT * FROM sysindexes WHERE id = OBJECT_ID( 'slprdsales' ) AND name = 'slprdsales_idx' )
BEGIN
DROP INDEX slprdsales.slprdsales_idx
PRINT '<<< DROPPED INDEX slprdsales.slprdsales_idx >>>'
END
go

/*
* CREATE INDEX slprdsales.slprdsales_idx2
*/
CREATE INDEX slprdsales_idx2
ON slprdsales( sales_level, sales_level_type, currency_code )
ON indexsegment
go

IF EXISTS ( SELECT * FROM sysindexes WHERE id = OBJECT_ID( 'slprdsales' ) AND name = 'slprdsales_idx2' )
PRINT '<<< CREATED INDEX slprdsales.slprdsales_idx2 >>>'
ELSE
PRINT '<<< FAILED TO CREATE INDEX slprdsales.slprdsales_idx2 >>>'
go

Indexes are created after the tables and are used for quick access to your rows without searchimg through the entire table.

There are the following types of constraints:
Unique and primary key constraints require that no two rows in a table have the same values in the
specified columns. In addition,a primary key constraint requires that there cannot be a null value
in any row of the column.

Referential integrity (references) constraints require that data being inserted in specific columns
already have matching data in the specified table and columns.

Check constraints limit the values of data inserted into columns.
You can also enforce data integrity by restricting the use of null values in a column
(the null or not null keywords) and by providing default values for columns (the default clause).

To add a column -
alter table slprdsales
add sales_value2 money null

Hope this helps
;-) Dickie Bird
db@dickiebird.freeserve.co.uk

 

[babeo]

Thanks

I give it a shot.