#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    8
    Rep Power
    0

    Question How to select records using Index key in Firebird?


    MS Compact SQL
    ----------------
    1. Table creation

    CREATE TABLE ProductsWIndex(ProductID int IDENTITY(1,1), ProductName nvarchar(50))

    2. Create Index

    CREATE INDEX index_productID ON ProductsWIndex (ProductID)

    3. Select records using Index key

    SELECT * FROM ProductsWIndex WITH (INDEX(index_productID)) WHERE ProductID < @ProductID



    So please help me....how to select records in faster way using the Index 'index_productID' in Firebird SQL?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Code:
    SELECT * FROM ProductsWIndex 
    WHERE ProductID < :ProductID
    Firbird will use the PK without your help.

    At the purest level, SQL is a declarative language.
    That means you 'declare' what you want and the SQL server implementation decides how to fulfill it.
    Last edited by clivew; February 7th, 2011 at 01:48 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    8
    Rep Power
    0

    Question


    Thanks for your reply.

    I have one small doubt.

    Here while table creation i didn't set ProductID as Primary key.

    So while executing SELECT * FROM ProductsWIndex
    WHERE ProductID < :ProductID refers index_productID index table??

    Please tell me how indexing is working in the above query?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    As long as the index exists it will be used provided the optimizer thinks it will be beneficial.

    All SQL tables should have a PK defined. Either a natural PK or a surrogate one.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    8
    Rep Power
    0

    Question


    Thanks for your reply.

    I tried with

    SELECT * FROM ProductsWIndex WHERE ProductID < @ProductID PLAN (ProductsWIndex INDEX (index_productID))

    Is the correct way of selecting records faster using Index key??
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I give up.
    Perhaps someone more articulate than I can explain better.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    8
    Rep Power
    0
    Thanks for your reply.

IMN logo majestic logo threadwatch logo seochat tools logo