Showing posts with label explain. Show all posts
Showing posts with label explain. Show all posts

Friday, March 30, 2012

Index SQL Server

How do i use Index's in SQL Server to sort coulmn values....can someone give me some insight or any good links that would explain this for me please!!

You don't, exactly. It is true that clustered indexes do sort data in a given order, but indexes are basically used to look up values in a SQL Server. The keys are in fact sorted in a given order (ascending or descending) for easy access to data via a B-Tree structure, so they can have some value in sorting, but you wouldn't really sort using an index.

Data is sorted for output by the means of an ORDER BY clause of a SELECT statement. In some cases, when you are sorting in the same order as the clustered index, or when you only need the columns that make up an indexes keys, the index can be useful. But, indexes are also bad for the performance of writes, so you have to be careful to balance the use of indexes with write performance.

sql

Monday, March 26, 2012

Index Question

Can anyone explain me why there are (_wa) indexes in my
databases when "real" indexes exists. Meaning: I have an
automatic index "showing" an index that has already been
created... any explanation?
The '_wa' indexes are statistics. They are essential for the SQL optimizer
to decide on a good, efficient query plan.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"" <anonymous@.discussions.microsoft.com> wrote in message
news:82ae01c431e6$21bd4df0$a301280a@.phx.gbl...
> Can anyone explain me why there are (_wa) indexes in my
> databases when "real" indexes exists. Meaning: I have an
> automatic index "showing" an index that has already been
> created... any explanation?
sql

Index Question

Can anyone explain me why there are (_wa) indexes in my
databases when "real" indexes exists. Meaning: I have an
automatic index "showing" an index that has already been
created... any explanation?The '_wa' indexes are statistics. They are essential for the SQL optimizer
to decide on a good, efficient query plan.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"" <anonymous@.discussions.microsoft.com> wrote in message
news:82ae01c431e6$21bd4df0$a301280a@.phx.gbl...
> Can anyone explain me why there are (_wa) indexes in my
> databases when "real" indexes exists. Meaning: I have an
> automatic index "showing" an index that has already been
> created... any explanation?

Friday, March 23, 2012

Index Question

Can anyone explain me why there are (_wa) indexes in my
databases when "real" indexes exists. Meaning: I have an
automatic index "showing" an index that has already been
created... any explanation?The '_wa' indexes are statistics. They are essential for the SQL optimizer
to decide on a good, efficient query plan.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:82ae01c431e6$21bd4df0$a301280a@.phx.gbl...
> Can anyone explain me why there are (_wa) indexes in my
> databases when "real" indexes exists. Meaning: I have an
> automatic index "showing" an index that has already been
> created... any explanation?sql

Monday, March 12, 2012

Index Help

Hi,
Can any please explain to me the diff.?
Use Northwind
I created a Non-Clustered index on "LastName' and "FirstName" (Combined)
Select * from customers where LastName = 'Hello' and FirstName = 'World'
Select * from customers where FirstName = 'World' and LastName = 'Hello'
Does that make the difference?
I really would like to know the Clustered Index part as well...!!!!!
I created a Clustered index on "LastName' and "FirstName" (Combined)
Select * from customers where LastName = 'Hello' and FirstName = 'World'
Select * from customers where FirstName = 'World' and LastName = 'Hello'
Does that make any difference as well if I filter that FirstName first?
Thanks for the answer on both clustered and Non clustered
EdIt doesn't make any difference. The query optimiser is smart enough to
rearrange the order of the parts of the WHERE clause in order to best
use the indexes it has available. See example below:
create table dbo.mikejunk
(
LastName varchar(50) not null,
FirstName varchar(50) not null
)
go
create nonclustered index IX_mikejunk on dbo.mikejunk (LastName,
FirstName)
go
insert into dbo.mikejunk (LastName, FirstName) values ('Ben-Gan',
'Itzik')
insert into dbo.mikejunk (LastName, FirstName) values ('Delaney',
'Kalen')
insert into dbo.mikejunk (LastName, FirstName) values ('Tripp',
'Kimberly')
insert into dbo.mikejunk (LastName, FirstName) values ('Waymire',
'Richard')
insert into dbo.mikejunk (LastName, FirstName) values ('Kelly',
'Andrew')
insert into dbo.mikejunk (LastName, FirstName) values ('Bertrand',
'Aaron')
insert into dbo.mikejunk (LastName, FirstName) values ('Sommarskog',
'Erland')
insert into dbo.mikejunk (LastName, FirstName) values ('Epprecht',
'Mike')
insert into dbo.mikejunk (LastName, FirstName) values ('Kass', 'Steve')
insert into dbo.mikejunk (LastName, FirstName) values ('Karaszi',
'Tibor')
go
set showplan_text on
go
select * from dbo.mikejunk where LastName = 'Delaney' and FirstName
= 'Kalen'
select * from dbo.mikejunk where FirstName = 'Kalen' and LastName =
'Delaney'
go
set showplan_text off
go
drop table dbo.mikejunk
go
If you have a look at the execution plans you'll see they are both the same:
StmtText
----
--
select * from dbo.mikejunk where LastName = 'Delaney' and FirstName
= 'Kalen'
StmtText
----
----
|--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
WHERE:([tempdb].[dbo].[mikejunk].[LastName]=[@.1] AND
[tempdb].[dbo].[mikejunk].[FirstName]=[@.2]))
StmtText
----
--
select * from dbo.mikejunk where FirstName = 'Kalen' and LastName =
'Delaney'
StmtText
----
----
|--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
WHERE:([tempdb].[dbo].[mikejunk].[FirstName]=[@.1] AND
[tempdb].[dbo].[mikejunk].[LastName]=[@.2]))
Same with the clustered index (try changing the "nonclustered" to
"clustered" in the test script above and look at the results).
However, if you have more columns than just LastName & FirstName in your
table then it probably becomes a moot point, since at the very least the
query engine will need to do a lookup into the clustered index to get
the other columns for the SELECT list or it may just decide that it's
more efficient to do a table/clustered index scan rather than look at
the nonclustered index at all.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Ed wrote:

>Hi,
> Can any please explain to me the diff.?
>Use Northwind
>I created a Non-Clustered index on "LastName' and "FirstName" (Combined)
>Select * from customers where LastName = 'Hello' and FirstName = 'World'
>Select * from customers where FirstName = 'World' and LastName = 'Hello'
>Does that make the difference?
>I really would like to know the Clustered Index part as well...!!!!!
>I created a Clustered index on "LastName' and "FirstName" (Combined)
>Select * from customers where LastName = 'Hello' and FirstName = 'World'
>Select * from customers where FirstName = 'World' and LastName = 'Hello'
>Does that make any difference as well if I filter that FirstName first?
>Thanks for the answer on both clustered and Non clustered
>Ed
>
>|||Oops, sorry - I just noticed that the showplan results I posted were
from a run I did with no indexes. <blush> Here are the showplan
results I meant to post (using the nonclustered index):
StmtText
----
--
select * from dbo.mikejunk where LastName = 'Delaney' and FirstName
= 'Kalen'
StmtText
----
----
--
|--Index S(OBJECT:([tempdb].[dbo].[mikejunk].[IX_mikejunk]),
SEEK:([tempdb].[dbo].[mikejunk].[LastName]=[@.1] AND
[tempdb].[dbo].[mikejunk].[FirstName]=[@.2]) ORDERED FORWARD)
StmtText
----
--
select * from dbo.mikejunk where FirstName = 'Kalen' and LastName =
'Delaney'
StmtText
----
----
--
|--Index S(OBJECT:([tempdb].[dbo].[mikejunk].[IX_mikejunk]),
SEEK:([tempdb].[dbo].[mikejunk].[LastName]=[@.2] AND
[tempdb].[dbo].[mikejunk].[FirstName]=[@.1]) ORDERED FORWARD)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
> It doesn't make any difference. The query optimiser is smart enough
> to rearrange the order of the parts of the WHERE clause in order to
> best use the indexes it has available. See example below:
> create table dbo.mikejunk
> (
> LastName varchar(50) not null,
> FirstName varchar(50) not null
> )
> go
> create nonclustered index IX_mikejunk on dbo.mikejunk (LastName,
> FirstName)
> go
> insert into dbo.mikejunk (LastName, FirstName) values ('Ben-Gan',
> 'Itzik')
> insert into dbo.mikejunk (LastName, FirstName) values ('Delaney',
> 'Kalen')
> insert into dbo.mikejunk (LastName, FirstName) values ('Tripp',
> 'Kimberly')
> insert into dbo.mikejunk (LastName, FirstName) values ('Waymire',
> 'Richard')
> insert into dbo.mikejunk (LastName, FirstName) values ('Kelly',
> 'Andrew')
> insert into dbo.mikejunk (LastName, FirstName) values ('Bertrand',
> 'Aaron')
> insert into dbo.mikejunk (LastName, FirstName) values
> ('Sommarskog', 'Erland')
> insert into dbo.mikejunk (LastName, FirstName) values ('Epprecht',
> 'Mike')
> insert into dbo.mikejunk (LastName, FirstName) values ('Kass',
> 'Steve')
> insert into dbo.mikejunk (LastName, FirstName) values ('Karaszi',
> 'Tibor')
> go
> set showplan_text on
> go
> select * from dbo.mikejunk where LastName = 'Delaney' and
> FirstName = 'Kalen'
> select * from dbo.mikejunk where FirstName = 'Kalen' and LastName
> = 'Delaney'
> go
> set showplan_text off
> go
> drop table dbo.mikejunk
> go
>
> If you have a look at the execution plans you'll see they are both the
> same:
> StmtText
> ---
--
> select * from dbo.mikejunk where LastName = 'Delaney' and
> FirstName = 'Kalen'
> StmtText
> ---
----
--
> |--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
> WHERE:([tempdb].[dbo].[mikejunk].[LastName]=[@.1] AND
> [tempdb].[dbo].[mikejunk].[FirstName]=[@.2]))
>
> StmtText
> ---
--
> select * from dbo.mikejunk where FirstName = 'Kalen' and LastName
> = 'Delaney'
> StmtText
> ---
----
--
> |--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
> WHERE:([tempdb].[dbo].[mikejunk].[FirstName]=[@.1] AND
> [tempdb].[dbo].[mikejunk].[LastName]=[@.2]))
> Same with the clustered index (try changing the "nonclustered" to
> "clustered" in the test script above and look at the results).
> However, if you have more columns than just LastName & FirstName in
> your table then it probably becomes a moot point, since at the very
> least the query engine will need to do a lookup into the clustered
> index to get the other columns for the SELECT list or it may just
> decide that it's more efficient to do a table/clustered index scan
> rather than look at the nonclustered index at all.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Ed wrote:
>

Sunday, February 19, 2012

index cluster

Can u explain it to me pls?

2/ also what s 4th normal form ?

Thanks

hi,
i think u want tp know abt clustered index....its the way of physically arranging data on the disk for faster access of data...its arranged in the form of B-tree structure, where the leaf nodes contain the data (best way to explain in real life is how the data is arranged in telephone directory)...its explained well in sql server books online...

ao for 4th normal form...i'll assume that u know till 3rd normal... in 3rd normal form we try to keep only the fully functional fields rematin in the stable...still we may be left with multivalued dependencies...in the 4th NF we try to make these multivalued dependencies to functional dependency...
multivalued dependencies are of the type a-> b ->c , so we need to make them a->b and a->c
this is a gud link for normalization..
http://www.cs.jcu.edu.au/Subjects/cp1500/1998/Lecture_Notes/normalisation/1nf.html|||just a normal index then, right