Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to execute Index? Expand / Collapse
Author
Message
Posted Thursday, April 08, 2010 6:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 07, 2010 2:04 AM
Points: 72, Visits: 92
I have gone through this example for index in one site.

create table employee1(
ID int,
name nvarchar (20),
salary int,
city nvarchar (20),
region nvarchar (20)
)

insert into employee1 values ('1', 'Jason', '40420', 'New York', 'W')
insert into employee1 values ('2', 'Robert','14420', 'Vancouver','N')
insert into employee1 values ('3', 'Celia', '24020', 'Toronto', 'W')
insert into employee1 values ('4', 'Linda', '40620', 'New York', 'N')
insert into employee1 values ('5', 'David', '80026', 'Vancouver','W')
insert into employee1 values ('6', 'James', '70060', 'Toronto', 'N')
insert into employee1 values ('7', 'Alison','90620', 'New York', 'W')
insert into employee1 values ('8', 'Chris', '26020', 'Vancouver','N')
insert into employee1 values ('9', 'Mary', '60020', 'Toronto', 'W')

CREATE UNIQUE CLUSTERED INDEX IX_Employee
ON Employee1
([Id], [Name] DESC)
GO

I have done this.

But I am giving

Select * from IX_Employee.

It is not working.

Here What is the use of creating Index?
Post #899464
Posted Thursday, April 08, 2010 6:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
You can never execute an Index. To get the data you will have to always SELECT * from the table.

And one more thing, is you get some script from somewhere make sure you understand it completely and then try it. You can Google for lot of information on Indexes.



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #899492
Posted Thursday, April 08, 2010 6:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
You don't select directly from the index. You select from the table.

If the index is appropriate, SQL Server will use it when you do that.

To really "get" indexing, you need to take a look at the subject of "execution plans". RedGate has a good book on that subject, which is free (to download). Here's the link: http://www.red-gate.com/about/book_store/sql_server_execution_plans.htm

The author is a regular on this site, and very, very knowledgeable about SQL Server.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #899510
Posted Thursday, April 08, 2010 8:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 6:44 PM
Points: 891, Visits: 234
What a question


Post #899654
Posted Thursday, April 08, 2010 12:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
chandrasekaran.ganapathy (4/8/2010)Here What is the use of creating Index?


Allow me to answer you with a question... What is the use of having an index in a book? perhaps to allow you to find the page where some information you are looking for is instead of having to read the whole book from page one?


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #899888
Posted Thursday, April 08, 2010 10:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 07, 2010 2:04 AM
Points: 72, Visits: 92
Nice explanation. But I dint get how it is related to the database. Are you meant that if i can pull the data where index no is 3 I can use that index. is it right? But I dint find any index no. assigned to the data. Can you give example with the small sample data.
Post #900159
Posted Thursday, April 08, 2010 10:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 07, 2010 2:04 AM
Points: 72, Visits: 92
Hi Kingston and all,

Got it. I have read some article. So If i make a query like

Select * from employeeId where employeeId between 'xxxxxx' and 'xxxx'
or
Select * from employeeId where employeeId > 'xxxxx'.


These kind of query will be fetched from the database too fast using index.

Please correct me if i am wrong.
Post #900162
Posted Friday, April 09, 2010 12:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
chandrasekaran.ganapathy (4/8/2010)
Hi Kingston and all,

Got it. I have read some article. So If i make a query like

Select * from employeeId where employeeId between 'xxxxxx' and 'xxxx'
or
Select * from employeeId where employeeId > 'xxxxx'.


These kind of query will be fetched from the database too fast using index.

Please correct me if i am wrong.


Depends. If the optimizer thinks that using the index will be a faster method to fetch the required data it will use your index and fetch the data. But if it decides otherwise, it will not use your index.

And did you download the book from the link provided by GSquared? I don't think you did and if you did you haven't read it. It is a free download and it will have answers to most of your questions.



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #900213
Posted Friday, April 09, 2010 6:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
chandrasekaran.ganapathy (4/8/2010)
Nice explanation. But I dint get how it is related to the database. Are you meant that if i can pull the data where index no is 3 I can use that index. is it right? But I dint find any index no. assigned to the data. Can you give example with the small sample data.


I don't understand your question. Not sure what you mean about index numbers.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #900419
Posted Friday, April 09, 2010 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 09, 2010 3:50 PM
Points: 3, Visits: 3
I got it. Kingston explained well. thanks
Post #900426
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse