SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Indexing related issues
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/19/2013 :  08:34:30  Show Profile  Reply with Quote
Somebody please tell me
What a bookmark lookup is?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/19/2013 :  09:13:23  Show Profile  Reply with Quote
You find bookmark lookup usualy in execution plan when you've to retrive lot of information from tables after seraching based on set of column values. the column values may be obtained from indexes but for the associated other information it requires a lookup using these index column values. Thats when it goes for bookmark lookup.
http://technet.microsoft.com/en-us/library/ms180920(v=sql.105).aspx

some cases you may be able to avoid lookup by create covering non clustered index which covers all columns used by the query or by including additional columns as included columns if they cant be directly added to index due to thier types.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/19/2013 :  11:25:15  Show Profile  Reply with Quote
Thanks for the reply.

I will look at that site.

I have one more doubt.

you mentioned in your post that some columns cannot be added directly to indexes due to their types.

is this due to the limit of index column length i.e 900 bytes?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/20/2013 :  02:09:41  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

Thanks for the reply.

I will look at that site.to indexes due to their types.


I have one more doubt.

you mentioned in your post that some columns cannot be added directly
is this due to the limit of index column length i.e 900 bytes?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


yes and also due to datatype of columns like image,text etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/20/2013 :  03:53:00  Show Profile  Reply with Quote
Ya thank you sir.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/20/2013 :  03:59:55  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/20/2013 :  09:52:42  Show Profile  Reply with Quote
When a clustered index is created on a table, then the table is organized into a b-tree.

what are the contents of the root node of that b-tree?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

Edited by - sgondesi on 11/20/2013 11:24:24
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/22/2013 :  03:50:11  Show Profile  Reply with Quote
Somebody please help me..

When a clustered index is created on a table, then the table is organized into a b-tree.

what are the contents of the root node of that b-tree?


--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/22/2013 :  06:47:08  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

Somebody please help me..

When a clustered index is created on a table, then the table is organized into a b-tree.

what are the contents of the root node of that b-tree?


--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


all except leaf nodes will have index information. leaf nodes will contain actual data pages.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/22/2013 :  08:08:36  Show Profile  Reply with Quote
ya..
I have read that leaf level will consist of the data pages.
Also read that the intermediate level nodes will contain the (key,value) pairs.
Does this key and value is the index key and the column value on which the clustered index is created?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/25/2013 :  04:36:11  Show Profile  Reply with Quote
I request somebody to respond to my post.
Thank you.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/25/2013 :  05:19:58  Show Profile  Reply with Quote
see

https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/25/2013 :  07:32:25  Show Profile  Reply with Quote
thank you sir.
i will read from that website and i will get back to you.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 11/25/2013 :  10:08:52  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

I request somebody to respond to my post.
Thank you.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.



Please don't write posts like this. It is optional for members to respond.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/25/2013 :  10:17:09  Show Profile  Reply with Quote
I am Sorry.
I will not repeat here after.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/27/2013 :  10:28:07  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

see

https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks for the link sir. It helped me a lot in understanding indexing.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/29/2013 :  06:49:00  Show Profile  Reply with Quote
hello everyone..
I have an issue..
If we have a primary key column in our table, will the table still be organized into a heap without creation of an index on that table.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/29/2013 :  07:28:31  Show Profile  Reply with Quote
one more doubt..

Non clustered Index with clustered index is effective or non clustered index with heap is effective?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 11/29/2013 :  11:38:07  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

hello everyone..
I have an issue..
If we have a primary key column in our table, will the table still be organized into a heap without creation of an index on that table.





- A Primary Key is an index
- By default a Primary Key is a Clustered Index
- You can choose to have a table with indexes an a Primary Key as a heap - as long as none of the indexes are Clustered and the Primary Key would have to declared as nonclustered
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/30/2013 :  02:20:35  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

one more doubt..

Non clustered Index with clustered index is effective or non clustered index with heap is effective?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


see this white paper which compares both scenarios

http://technet.microsoft.com/en-us/library/cc917672.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/02/2013 :  05:09:22  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

quote:
Originally posted by sgondesi

hello everyone..
I have an issue..
If we have a primary key column in our table, will the table still be organized into a heap without creation of an index on that table.





- A Primary Key is an index
- By default a Primary Key is a Clustered Index
- You can choose to have a table with indexes an a Primary Key as a heap - as long as none of the indexes are Clustered and the Primary Key would have to declared as nonclustered


Thanks for the response.
So with having a primary key column in the table, can we create a clustered index on any other column in the same table?

this is one query.
and i did not understand the third point in your post.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000