March 20, 2016 at 12:08 pm
hi all
how to sort non clustered indexes ? any suggestion on this
March 20, 2016 at 12:46 pm
mithleshdb8 (3/20/2016)
hi allhow to sort non clustered indexes ? any suggestion on this
your question appears a bit sparse in detail...can you explain , maybe with examples, of what your are trying to do?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 21, 2016 at 7:16 am
Nonclustered indexes are logically sorted by the index key. What are you trying to do?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2016 at 10:24 am
sorry i was asked by someone ,, just how you can sort an nonclustered index..
i told i can use order by clause,,but it is not correct answer..
March 23, 2016 at 11:06 am
I still don't understand the question. A nonclustered index is sorted. It has a key value. It gets sorted into a b+tree by that key value. The keys are ordered (logically) at the page level. An ORDER BY statement can take advantage of the sort in the key to retrieve the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2016 at 11:59 am
db8 (3/23/2016)
sorry i was asked by someone ,, just how you can sort an nonclustered index..i told i can use order by clause,,but it is not correct answer..
Sorry, but that question makes about as much sense as the question on how to feed a blue.
If you want to sort soomething, it better be a collection of more than one element. A single nonclustered index is just one thing. You can sort a list of nonclustered indexes (by whatever criterium you want). Or you can sort all entries in a nonclustered index (allthough SQL Server actually already does that for you, as part of how the index is stored - unless it's a columnstore or a hash index). But you cannot sort "an index".
Who is the "someone" who asked, why did they ask, and what was the context. And if they told you that your answer is incorrect, then what did they say is the correct answer, and why do they bother asking you?
March 23, 2016 at 1:01 pm
Hugo Kornelis (3/23/2016)
criterium
Completely off-topic, but criteria is from Greek, so it uses the Greek singular criterion rather than the Latinate singular criterium
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2016 at 2:34 pm
Thanks, Drew! It does not work that way in Dutch, which is why I used the wrong form.
I guess English is better in this then Dutch, because here we never know what we mean exactly when talking about a criterium - either a standard of judgement, or a biciycle race.
March 23, 2016 at 3:06 pm
If this is a homework question, it's either a trick question or phrased very poorly.
March 23, 2016 at 3:26 pm
preparing T-SQL ,
they told me we can sort by using ,with index clause.
don't know what exactly use of this with index clause I will confirm on this by next week.
March 23, 2016 at 4:34 pm
The WITH INDEX clause is a so-called index hint. "Hint" is the name, but it is actually a directive. You are saying to SQL Server: "I know that thousands of man hours and tons of research by highly respected academics went into the optimizer that tries to find the best possible execution plan, but I spent all of five minutes thinks about this query and I am going to force the product to use this index I think is better."
In almost 20 years of working with SQL Server, I have only once used an index hint in a production query, and that was after I found that this was the only way to work around a bug; I removed the hint as soon as we had upgraded to the next service pack.
The question itself and the given "correct" answer, but also the phrasing of all of this, demonstrates a terrible lack of understanding. If I would get a question such as this in a job interview, I would stand up and leave. I do not mind working for people who don't understand SQL Server, but I do mind working for people who think they understand SQL Server but in reality don't.
March 23, 2016 at 8:57 pm
hello sir,
I am new to this one.. Probably i should posted with clear details.
I will try to reframe my question or will get you back what is the exact question.
Thankyou
March 23, 2016 at 10:43 pm
db8 (3/23/2016)
preparing T-SQL ,they told me we can sort by using ,with index clause.
don't know what exactly use of this with index clause I will confirm on this by next week.
Yep, you can force a the optimizer to use a specific index using the WITH (INDEX()) query hint. This is covered here (See example J). So what? I'm curious what prompted the conversation.
-- Itzik Ben-Gan 2001
March 24, 2016 at 4:28 am
Hugo Kornelis (3/23/2016)
In almost 20 years of working with SQL Server, I have only once used an index hint in a production query, and that was after I found that this was the only way to work around a bug; I removed the hint as soon as we had upgraded to the next service pack.
Same here. The only exception was spatial indexes where we had to hint them, a lot. Other than those, I can't say that I've ever used an index hint anywhere.
I too am very interested to hear what the full story is on this one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 24, 2016 at 2:18 pm
Grant Fritchey (3/24/2016)
I too am very interested to hear what the full story is on this one.
My money at this point is on an interview question. Either taken from the internet and then phrased very poorly because the interviewer doesn't know SQL hirself, or reproduced here poorly because the candidate was nervouw and/or in this over hir head.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply