SQL server exams - Index problems.....70-228

Author

Index problems.....70-228

silversamurai

2003-06-16, 10:37 am

Okay I didn't pass and I think that test was completely unfair. I'm not being bitter but I used the Microsoft Press Kit and still couldn't pass.

Way too many questions on indexing and that's a database design issue.

I can't understand the difference between clustered and non-clustered. Somebody look this over and tell me if I'm right.

Clustered indexes are ordered by the hierarchy of the data like an alphabetical structure or high-low number, right ?

Non-clustered indexes do not affect the physical order of the data at all but simply point to where the data is.

Sounds to me like clustered would be the way to go unless your looking for just one field in a table, right ?

Well anyway, bone up on Indexing and query execution.

Also, I didn't get the old format showing the areas that I did better or worse in. Just got a simple piece of paper that said I failed with the Microsoft header at the top. Can I get a copy of the other document so I'll know what to study or am I just floating around in the ether and have to study it all over again. Thanks.

sjouken

2003-06-22, 4:28 am

A clustered index is a sorted index on alphabetical order, just as you wrote.
A non-clustered index is a index where the clustered index is being used to point out where SQL have to search. Microsoft Press is calling them bookmarks.
As far as I can see you have to use at least one clustered index and with that information SQL is using to point out where to find the subjects in his non-clustered index.

I hope this will help you

John

netmankc

2003-06-22, 12:44 pm

Unless Microsoft has changed things in the past few years, you no longer get the page showing which areas you did well or poorly in. When I took my NT exams I never saw what areas I needed to study more. It does suck. What a shame if you actually knew what you needed to work harder on!!! Leave it to MS!! Keep hitting the books. You'll get it.

lucasponzo

2003-06-29, 8:31 pm

In both cases the data is sorted by the index keys.

The diference is that in a non clustered index, the data is copied to another area and a bookmark is necessary to identify the row in the table.

In the clustered index, the index is the table itself, and no bookmark is necessary to lookup the data. But, when to choose one or another ???

Due to the nature of the clustered indexes, they are better to use on columns that normaly are acessed in range queries, like in dates and numerical values. Clustered works better in this way ( less seletive ).

For more selective queries, like primary and foreign keys searchs, a non clustered index is the better choise.

Really, I still don't understant why the MSSQL default is to create the primary key as clustered. Doing this, the less experienced DBA/Developer loose the real power of the clustered indexes.

Lucas Ponzo.

Sponsored Links