SQL Server: Index and Key

560 pts.
Tags:
Index and Key
SELECT statement
What is the difference between a key and an index? Whe I issue a SELECT statement, and the table has an index, do I still have to use my key?

Answer Wiki

Thanks. We'll let you know when a new response is added.

That would be i-series talk — missed the keywords

Do your select on the physical file or the view.
The view’s select should be on the physical file.

Don’t do your select on a logical.
Include the where clause to define which records you want.
Include the order by clause to indicate the delivery order

The query engine will decide how to get and sort your request
- we hope it picks an index where performance is critical
because it’s a little faster than a logical or creating an index on the fly

– try navigator SQL analyze

Phil

————————-

By key I assume that you are talking about the Primary Key of the table. The Primary Key is a logical object. By that I mean that is simply defines a set of properties on one column or a set of columns to require that the columns which make up the primary key are unique and that none of them are null. Because they are unique and null, these values (or value if your primary key is a single column) can then be used to identify a single row in the table every time. In most if not all database platforms the Primary Key will have an index created on it.

An index on the other hand doesn’t define uniqueness. An index is used to more quickly find rows in the table based on the values which are

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Could you please be a little more specific about your doubt ? What exactly you mean by "key", and what exactly you mean by "use my key" ?
    68,635 pointsBadges:
    report
  • bogeybetsy
    I'm sorry, I shouldn't have written SQL server. I'm using an iSeries. I just found out that the index does not arrange the records according to itseld, the index. If I use indexes and SQL DDL, then I would have to use SQL DML. Otherwise, I would have to build logical files over the tables created by SQL DDL. Is that correct Phil, Luke and Mr Denny? Thanks!
    560 pointsBadges:
    report
  • bogeybetsy
    Hi Carlos, In the iSeries, if you have a logical file that has a key, then when yuo access the records one by one from the top, you will find that the records you get are in the order of the key. I was trying to use a SQL DDL table with an index in an RPG program. In other words, I was using it as if it was a logical file. That's because I thought it would be like one. Terrible mistake. I have used SELECT statements on the iSeries (concept of) files, physical or logical, so many times before that I didn't think SQL DDL would be very different. Regards, Allan
    560 pointsBadges:
    report
  • philpl1jb
    Hi again Bogeybetsy I'm not sure if you still have a question. The physical file is basically the same regardless of how you make it. You can add both indexes for SQL and logical files for native I/O. When you use SQL selects specify the physical and the where and order by clauses. Phil
    49,590 pointsBadges:
    report
  • bogeybetsy
    Hi Phil, Yes you are right Phil, but I read an article entitled "Modernizing Database Access, The Madness Behind the Methods" by Dan Cruikshank. It says that I could still use RPG with SQL DDL. And I did. I created a table with a primary key. I even added an index on it. but when you use native I/O (like READ), it doesn't give me the records sorted according to the key - very unllike logical files. In this case, I would have to create a MQT with a ORDER BY clause. Or build a logical file over the SQL DDL table I created. Any other ideas? Allan
    560 pointsBadges:
    report
  • philpl1jb
    You're absolutely right - what you will get is the data in enttry sequence. You will need a traditional: Logical file or OpnQryf to get the sequence you want for your program to use it in "keyed" sequence, these would be created the same as for any other file. Even though this file was created in SQL it is just like any other file on the as/400. Remeber to specify K in the F spec. Or, of course, you could use and SQL select command with the where and order by clauses and fetch from that. Phil
    49,590 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following