SQL Server: Index and Key
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 31, 2009 5:08 PM
UPDATED:
August 3, 2009 9:30 AM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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” ?
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!
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
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
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
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