Index Help

seanpenn

This will sound stupid, I thought I had it but for some reason it does not work for me. Can somebody please post code to create an index on an sql server database using vb.net.

Very simple request but My way of doing it will not work, SO I hope to adapt a code sample that I can not find online.

Barkeeper

: This will sound stupid, I thought I had it but for some reason it does not work for me. Can somebody please post code to create an index on an sql server database using vb.net.
:
: Very simple request but My way of doing it will not work, SO I hope to adapt a code sample that I can not find online.
:

SQL Server Database?

From the MSDN:

CREATE INDEX Statement
For SQL Server 6.5 information, see CREATE INDEX Statement in What's New for SQL Server 6.5.

Creates an index on a given table that either changes the physical ordering of the table or provides the optimizer with a logical ordering of the table to increase efficiency for queries. When creating an index for the primary key, use the table- and column-level PRIMARY KEY constraint provided with the CREATE TABLE statement.

Syntax
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [[database.]owner.]table_name (column_name [, column_name]...)
[WITH
[FILLFACTOR = x]
[[,] IGNORE_DUP_KEY]
[[,] {SORTED_DATA | SORTED_DATA_REORG}]
[[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]
[ON segment_name]

Examples
A. Simple Index
This example creates an index on the au_id column of the authors table.

CREATE INDEX au_id_ind
ON authors (au_id)
B. Unique Clustered Index
This example creates an index on the au_id column of the authors table that enforces uniqueness. This index will physically order the data on disk because the CLUSTERED option is specified.

CREATE UNIQUE CLUSTERED INDEX au_id_ind
ON authors (au_id)
C. Simple Composite Index
This example creates an index on the au_id and title_id columns of the authors table.

CREATE INDEX ind1
ON titleauthor (au_id, title_id)

D. FILLFACTOR
This example uses the FILLFACTOR option set to 100. A FILLFACTOR of 100 fills every page completely and is useful only when you know that no index values in the table will ever change.

CREATE NONCLUSTERED INDEX zip_ind
ON authors (zip)
WITH FILLFACTOR = 100

E. IGNORE_DUP_KEY
This example creates a unique clustered index on the stores table. If a duplicate key is entered, the INSERT or UPDATE statement will be ignored.

CREATE UNIQUE CLUSTERED INDEX stor_id_ind
ON stores(stor_id)
WITH IGNORE_DUP_KEY



------------------------------------------
Only stupidity of mankind and the universe
are infinite, but i'm not sure concerning
the universe. A. Einstein

seanpenn

i need the vb coding. for some reason mine just does not run. And this is for sql server ce for mobile

: : This will sound stupid, I thought I had it but for some reason it does not work for me. Can somebody please post code to create an index on an sql server database using vb.net.
: :
: : Very simple request but My way of doing it will not work, SO I hope to adapt a code sample that I can not find online.
: :
:
: SQL Server Database?
:
: From the MSDN:
:
: CREATE INDEX Statement
: For SQL Server 6.5 information, see CREATE INDEX Statement in What's New for SQL Server 6.5.
:
: Creates an index on a given table that either changes the physical ordering of the table or provides the optimizer with a logical ordering of the table to increase efficiency for queries. When creating an index for the primary key, use the table- and column-level PRIMARY KEY constraint provided with the CREATE TABLE statement.
:
: Syntax
: CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
: ON [[database.]owner.]table_name (column_name [, column_name]...)
: [WITH
: [FILLFACTOR = x]
: [[,] IGNORE_DUP_KEY]
: [[,] {SORTED_DATA | SORTED_DATA_REORG}]
: [[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]
: [ON segment_name]
:
: Examples
: A. Simple Index
: This example creates an index on the au_id column of the authors table.
:
: CREATE INDEX au_id_ind
: ON authors (au_id)
: B. Unique Clustered Index
: This example creates an index on the au_id column of the authors table that enforces uniqueness. This index will physically order the data on disk because the CLUSTERED option is specified.
:
: CREATE UNIQUE CLUSTERED INDEX au_id_ind
: ON authors (au_id)
: C. Simple Composite Index
: This example creates an index on the au_id and title_id columns of the authors table.
:
: CREATE INDEX ind1
: ON titleauthor (au_id, title_id)
:
: D. FILLFACTOR
: This example uses the FILLFACTOR option set to 100. A FILLFACTOR of 100 fills every page completely and is useful only when you know that no index values in the table will ever change.
:
: CREATE NONCLUSTERED INDEX zip_ind
: ON authors (zip)
: WITH FILLFACTOR = 100
:
: E. IGNORE_DUP_KEY
: This example creates a unique clustered index on the stores table. If a duplicate key is entered, the INSERT or UPDATE statement will be ignored.
:
: CREATE UNIQUE CLUSTERED INDEX stor_id_ind
: ON stores(stor_id)
: WITH IGNORE_DUP_KEY
:
:
:
: ------------------------------------------
: Only stupidity of mankind and the universe
: are infinite, but i'm not sure concerning
: the universe. A. Einstein
:
:

Barkeeper

: i need the vb coding. for some reason mine just does not run. And this is for sql server ce for mobile
:
sorry, but i don't work with vb.net.

but i'm wondering..... any database able to process SQL-Statements should know the CREATE INDEX-Statement.

Zvoni
------------------------------------------
Only stupidity of mankind and the universe
are infinite, but i'm not sure concerning
the universe. A. Einstein