SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexing


Indexing

Author
Message
pchirags
pchirags
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3233 Visits: 616
Comments posted to this topic are about the item Indexing
PRAMANA.DBA
PRAMANA.DBA
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3013 Visits: 1039
Good Question.... but ,before select statement, he is dropping the table so select statement returns error but there is no option in answer list.
Lokesh Vij
Lokesh Vij
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5428 Visits: 1599
My eyes winked when I looked at this code

DROP TABLE [dbo].[IndexTable]
SELECT * FROM indextable



Qotd aks about "the output of select statement", but as per the code this will give "error" :-)

Though I understood the intention and answered accordingly. A nice question otherwise. Thanks Chirag!

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Ford Fairlane
Ford Fairlane
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4714 Visits: 836
Was a good question - but if I had to follow the script it would have resulted in an error as previously pointed out.

Drop was before select ...

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Kingston Dhasian
Kingston Dhasian
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10434 Visits: 5324
Excellent question. Keep it up.


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/
kapil_kk
kapil_kk
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9663 Visits: 2778
Lokesh Vij (6/13/2013)
My eyes winked when I looked at this code

DROP TABLE [dbo].[IndexTable]
SELECT * FROM indextable



Qotd aks about "the output of select statement", but as per the code this will give "error" :-)

Though I understood the intention and answered accordingly. A nice question otherwise. Thanks Chirag!

same here
+1 Smile

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Stuart Davies
Stuart Davies
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10534 Visits: 4861
I wonder what version of SQL this was tested against - running it against my Test box (2008 R2) all goes well until
INSERT INTO [dbo].[IndexTable]
VALUES(3, 'Value3')


Then I get the error "Msg 8655, Level 16, State 1, Line 1
The query processor is unable to produce a plan because the index 'PK_IndexTable' on table or view 'IndexTable' is disabled."

Any ideas?

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
Lokesh Vij
Lokesh Vij
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5428 Visits: 1599
Stuart Davies (6/14/2013)
I wonder what version of SQL this was tested against - running it against my Test box (2008 R2) all goes well until
INSERT INTO [dbo].[IndexTable]
VALUES(3, 'Value3')


Then I get the error "Msg 8655, Level 16, State 1, Line 1
The query processor is unable to produce a plan because the index 'PK_IndexTable' on table or view 'IndexTable' is disabled."

Any ideas?


Since table "IndexTable" is a clustered table and before this insert statement, we have disabled the clustered index. This prohibits us to read/write rows into the table. And hence this error message.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


WWDMark
WWDMark
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1863 Visits: 530
kapil_kk (6/14/2013)
Lokesh Vij (6/13/2013)
My eyes winked when I looked at this code

DROP TABLE [dbo].[IndexTable]
SELECT * FROM indextable



Qotd aks about "the output of select statement", but as per the code this will give "error" :-)

Though I understood the intention and answered accordingly. A nice question otherwise. Thanks Chirag!

same here
+1 Smile


+1. A tad misleading given that the table would be dropped before the select statement is executed! Answered correctly though when put into correct context.


MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: www.markallen-online.co.uk
Business Website: www.weekendwebdesign.co.uk
rodjkidd
rodjkidd
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9264 Visits: 8872
Ha.
I missed the DROP TABLE due to the scrolling code window. Otherwise I would have been very confused. But I don't think this distracts from the point the QotD was making.
I had to check whether reorg would re enable the index. And it's only the REBUILD. Which does make sence.

Good question, even with the SELECT and DROP in the wrong order.

Rodders...



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search