Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Indexing related issues

Author  Topic 

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-19 : 08:34:30
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

52326 Posts

Posted - 2013-11-19 : 09:13:23
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

200 Posts

Posted - 2013-11-19 : 11:25:15
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

52326 Posts

Posted - 2013-11-20 : 02:09:41
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

200 Posts

Posted - 2013-11-20 : 03:53:00
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

52326 Posts

Posted - 2013-11-20 : 03:59:55
you're welcome

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

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 09:52:42
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

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-22 : 03:50:11
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

52326 Posts

Posted - 2013-11-22 : 06:47:08
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

200 Posts

Posted - 2013-11-22 : 08:08:36
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

200 Posts

Posted - 2013-11-25 : 04:36:11
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

52326 Posts

Posted - 2013-11-25 : 05:19:58
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

200 Posts

Posted - 2013-11-25 : 07:32:25
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

596 Posts

Posted - 2013-11-25 : 10:08:52
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

200 Posts

Posted - 2013-11-25 : 10:17:09
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

200 Posts

Posted - 2013-11-27 : 10:28:07
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

200 Posts

Posted - 2013-11-29 : 06:49:00
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

200 Posts

Posted - 2013-11-29 : 07:28:31
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

596 Posts

Posted - 2013-11-29 : 11:38:07
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

52326 Posts

Posted - 2013-11-30 : 02:20:35
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

200 Posts

Posted - 2013-12-02 : 05:09:22
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
    Next Page

- Advertisement -