|
Hi,
I can add two different columns to an index. What is the difference if I make two separate indexes for two columns instead of creating one index and adding two columns to it.
Thanks
_
|
|
|
|
|
This depends very much on the database you are using (SQL Server, Oracle, MySQL etc).
But basically if you create two separate indexes, they can be used independently. When you have one index, the tree traversal must use the first column even though you are not referring to it in a query. Think of it like an index in a book. If you have two levels on chapters like:
- 1.1
- 1.2
- 1.3...
- 2.1
- 2.2
- 2.3...
and you want to find all chapters that are numbered ???.3 you must scan through the whole index to find what you are looking for.
Disclaimer: The description is really simplified and is not accurate for all databases so the situation is actually much more complex. But this should give you the idea.
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Thanks for your response. To be more specific, I'm talking about SQL Server database. Suppose that I've got a query like this:
select id, fname, lname, age from customers where lname = @lname order by age asc
Besides having a default clustered index on id column, I can have an index containing two columns, lname and age. But I can also have an index for lname and an index for age column separately. Does it matter which one is better in this case?
I may also have another query like this:
select id, lname from customers order by age asc
for the sake of this query, according to what I learned from your explanation, having an index containing only age column would be better than an index containing lname and age. right?
Can I have both of aforementioned indexes together? Will SQL Server determine which index is better to use based on my query?
Thanks for your help
_
|
|
|
|
|
Having both indexes (lname, age and age) is ok. However this will have some performance penalty for insert, update and delete statements.
You could try having only lname + age or lname and age separately. The optimizer will pick up the index / indexes it will consider most benefitial. You can verify optimizer behaviour using execution plan.
If you have only lname + age, it is possible that the optimizer makes horizontal scan on the index tree for the second query.
If you have lname and age separately, the optimizer can choose to make an index join for the first query.
So try all variations and use execution plan to see what is reasonable in your case (especially consider that those are hardly the only queries, so you should consider all query needs for this table if possible).
It's like playing chess 
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Yea it's like playing a chess!
From what I've seen in execution plan, it tells me that how much percent of time has been spent on which phase of query. Do you have any recommendation for me on how to make best use of execution plan to fine tune my indexes? I'm sorry if this question is very general and might bother you.
Thanks again
_
|
|
|
|
|
I found an article here: Execution Plans[^]
I think it can help me a lot.
Thanks again for your help
_
|
|
|
|
|
Just noticed your message.
That article is a good one. Especially concentrate on the total cost (= estimated seconds) and how it's distributed. Then observe logical I/O amounts (don't mind so much about physical I/O).
Your question is not bothering me at all. Actually this area is one of my special interests
Happy coding! 
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Hello,
I have one datetime column named Hours in SQL Server Table1, which gives the output as 09/26/2008 01:00:00, 09/26/2008 02:00:00, 09/26/2008 03:00:00 till 09/26/2008 12:00:00 format.
I wanted to get just the year in int datatype from this format and I got it using (SELECT CONVERT(int, YEAR (Hours)) AS Year FROM Table1) and I am getting the correct Year as I wanted 2008. Same way I am getting month and date also. But the issue is with hour.
I want the hour also in the integer format. I mean if there is 01:00:00, then the output should just be 1 and same way the next should be 2.
Can anyone please help me to achieve this output?
I want a proper syntax for the same.
Meanwhile I will carry on the research on my part.
Thank you.
|
|
|
|
|
|
try this
<br />
select CAST(DATEDIFF(minute, 0, '01:00:00') AS int) / 60
Hope it will help.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi
My Stored Procedure output is as below given.
Interval Avg1 Avg2
NULL NULL NULL
04/08 NULL NULL
05/08 2000 NULL
NULL 1000 NULL
NULL NULL NULL
NULL NULL 4000
NULL NULL 3000
I want result in two rows as below given.
Interval Avg1 Avg2
04/2008 2000 4000
05/2008 1000 3000
Please let me know how i can do this using temm table ?
Thanks
Care Career
|
|
|
|
|
Based on the info you posted, the only thing I can imagine is that you should modify the output from sp to give you what want.
So, you need to include to the post, how the result is made in sp and what do you want to change
or
if you cannot change the sp, what is the logic you want to achieve with temp tables (why 04/2008 has 4000 in avg2, since there is nothing in the data combining the row "NULL NULL 4000" to the row "04/08 NULL NULL"
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Hi
I do not want to change my existing SP I just want output as below given.I want to remove NULL Please help..
Sr No Interval Avg1 Avg2
1 04/2008 2000 4000
2 05/2008 1000 3000
|
|
|
|
|
This simply isn't possible unless there is some kind of logic why the result set should look like you described.
If your result set in the start is:
Interval Avg1 Avg2
NULL NULL NULL
04/08 NULL NULL
05/08 2000 NULL
NULL 1000 NULL
NULL NULL NULL
NULL NULL 4000
NULL NULL 3000 and you want:
Interval Avg1 Avg2
04/2008 2000 4000
05/2008 1000 3000
Please explain for example why the last row having 3000 in Avg2 should be combined with the row having 05/08 as interval. Also why the value 2000 on the same row is moved to the row 04/08 and so on...
One crucial thing is that you think this as result sets having rows with conditions since this is the way the DBMS does it.
The need to optimize rises from a bad design
|
|
|
|
|
I want to remove NULL from below given resultset.If I will remove NULL then data will come in two rows.SO how Can i do this using temp table?
please help its urgent.
Interval Avg1 Avg2
NULL NULL NULL
04/08 NULL NULL
05/08 2000 NULL
NULL 1000 NULL
NULL NULL NULL
NULL NULL 4000
NULL NULL 3000
Thanks
Care Career
|
|
|
|
|
listen,
first i assume columns are varchar,int,int type
if column is varchar type you can use isnull() funtion like this:
select,...,isnull(avg1,'') FROM mytable
that way you will get an empty slot.
if you want to erase all row with null values type
select * from mytable where (interval <> null) and (avg1 <> null) and (avg2 <> null)
what you type is wrong, you can´t get that, columns not from the same row!!!
about temp table forget it, you don´t need that ... for now 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Is it possible that two different SQL servers generate the same
UniqueIdentifier
thanks
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
|
Jeopardy answer: What is the easiest question in the world to google?
"While each generated GUID is not guaranteed to be unique, the total number of unique keys (2128 or 3.4×1038) is so large that the probability of the same number being generated twice is very small. For example, consider the observable universe, which contains about 5×1022 stars; every star could then have 6.8×1015 universally unique GUIDs."
from the first google result: http://en.wikipedia.org/wiki/Globally_Unique_Identifier[^]
|
|
|
|
|
hi
how can i backup and restore a table?
thanks in advance
|
|
|
|
|
Restore the database as a copy with another name and get the data needed from the desired table.
|
|
|
|
|
You can use export/import or bcp utility for a specific table.
Mika
The need to optimize rises from a bad design
|
|
|
|
|
hi
how can i connect from an application to sql server 2005
i build up an application_role in sql server
but still cannot create trusted login and username
|
|
|
|
|
reza assar wrote: trusted login and username
Trusted Connection uses WINDOWS AUTHENTICATION (i.e. your windows credentials), otherwise you need to create a login in SQL Server.
Have a look at www.connectionstrings.com to learn more about connecting to sql server.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all
SELECT @vSQL = 'INSERT INTO #rapor(Id) VALUES('' '+ @X + ' '')'
i want below
FOR EX
INSERT INTO #rapor(Id) VALUES('5')
BUT SQL SAYS
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' VALUES('.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ')'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' VALUES('.
like that pls help
Thanks ...
Sampiyon FENERBAHCE
|
|
|
|
|
Odd, it works for me - although you get null in @vSQL if @X is null.
You will get a space each side of you value though.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
