| Author |
Topic |
|
sgondesi
Posting Yak Master
200 Posts |
 Posted - 2013-11-19 : 08:34:30
|
| Somebody please tell meWhat a bookmark lookup is?-- Thanks and RegardsSrikar 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).aspxsome 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
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 RegardsSrikar 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-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 RegardsSrikar 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-11-20 : 03:53:00
|
| Ya thank you sir.-- Thanks and RegardsSrikar 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-20 : 03:59:55
|
| you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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 RegardsSrikar 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-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 RegardsSrikar 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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 RegardsSrikar 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 |
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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 RegardsSrikar 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. |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-11-25 : 10:17:09
|
| I am Sorry.I will not repeat here after.-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-11-27 : 10:28:07
|
quote:
Originally posted by visakh16
seehttps://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Thanks for the link sir. It helped me a lot in understanding indexing.-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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 |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc.
see this white paper which compares both scenarioshttp://technet.microsoft.com/en-us/library/cc917672.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
Next Page
|
Indexing related issues