Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Index Help

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.

Comments

  • BarkeeperBarkeeper Member Posts: 335
    : 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

  • seanpennseanpenn Member Posts: 5
    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
    :
    :

  • BarkeeperBarkeeper Member Posts: 335
    : 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

Sign In or Register to comment.