Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexing


Indexing

Author
Message
pchirags
pchirags
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2249 Visits: 615
Comments posted to this topic are about the item Indexing
PRAMANA.DBA
PRAMANA.DBA
SSC Eights!
SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)

Group: General Forum Members
Points: 860 Visits: 1029
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 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
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: 1910 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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3106 Visits: 5053
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
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2565 Visits: 2764
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
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: 4710 Visits: 4599
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 question

There 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 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
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3717 Visits: 8082
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