September 6, 2012 at 11:34 pm
SELECT * FROM <Tablename> where Field_ID = 20
Returns no row.
But there has such entry in the table .
But When we give
SELECT Field1,field2 FROM <Tablename> where Field_ID = 20
Its returns the result from the table (This is only for Specific fields in that Table. Not all Fields )
And
SELECT 1 FROM <Tablename> where Field_ID = 20
It also getting the result.
Can you please Give me why this kind of Behaviour. (This is MS SQL Server 2008 and Enterprise Edition.)
September 6, 2012 at 11:48 pm
This seems very unlikely.
Can you post screenshots? (you can add attachments to your post. Look at the post options when you are typing a reply)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 7, 2012 at 12:14 am
Here is the attachment
September 7, 2012 at 12:19 am
Is Publication_Author a view?
Can you post the definition of that table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 7, 2012 at 12:29 am
No. It is Table.
Column_name Type LengthNullable
Publication_Author_IDbigint8 no identity, primary_key
Publication_ID bigint 8no
Author_ID bigint 8no
Email nvarcharnono
Seq_No int4no
IsRegUser bit1no
IsDeleted bit1no default ((0))
Modified_By int4yes
Modified_Date datetime8yes
Created_By int4no
Created_date datetime8no
September 7, 2012 at 1:48 am
Can you please post the execution plans for the statements?
What is the output if you force clustered index scan by adding WITH (INDEX (1)) hint for all the queries? For e.g. something like
SELECT Field1,field2 FROM <Tablename> WITH (INDEX (1)) --USE "WITH (INDEX (0))" if it is a heap
where Field_ID = 20
September 7, 2012 at 2:58 am
If we force clustered index it is display all the columns .
select * from <table_name> with (index (cluster index name>)) where field_id = 2
its gets result.
Execution plan attached
September 7, 2012 at 3:19 am
ASFSDJG (9/7/2012)
If we force clustered index it is display all the columns .
select * from <table_name> with (index (cluster index name>)) where field_id = 2
its gets result.
Execution plan attached
Can you please post the DDL of the IX_FIS_Pub_Author_Publication_ID index. Also if it is possible, try rebuilding this index and then see the results.
September 7, 2012 at 3:30 am
Usman Butt (9/7/2012)
ASFSDJG (9/7/2012)
If we force clustered index it is display all the columns .
select * from <table_name> with (index (cluster index name>)) where field_id = 2
its gets result.
Execution plan attached
Can you please post the DDL of the IX_FIS_Pub_Author_Publication_ID index. Also if it is possible, try rebuilding this index and then see the results.
A corrupted index would be my first thought too - the index seek returns 3 rows, key lookup can't see them. I'd drop and recreate all of the indexes.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2012 at 3:31 am
CREATE NONCLUSTERED INDEX [IX_FIS_Pub_Author_Publication_ID] ON [dbo].[FIS_Publication_Author]
(
[Publication_ID] ASC,
[IsDeleted] ASC
)
INCLUDE ( [Author_ID],
[Seq_No]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
the rows are returns while selecting only these index columns.
Done Rebuild Index . But no change
September 7, 2012 at 3:43 am
ASFSDJG (9/7/2012)
CREATE NONCLUSTERED INDEX [IX_FIS_Pub_Author_Publication_ID] ON [dbo].[FIS_Publication_Author](
[Publication_ID] ASC,
[IsDeleted] ASC
)
INCLUDE ( [Author_ID],
[Seq_No]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
the rows are returns while selecting only these index columns.
Done Rebuild Index . But no change
That is kind of strange. But if it is the corruption problem, it could be anything. So another option would be to try rebuilding the clustered index which would also rebuild all the NC indexes. But I would not recommend it if it is in production and unless feasible at this time. I would also run DBCC against such databases, and if the corruption is the problem then would look into the root cause. But that is only me 🙂
September 7, 2012 at 4:10 am
Usman Butt (9/7/2012)
I would also run DBCC against such databases, and if the corruption is the problem then would look into the root cause. But that is only me 🙂
DBCC CHECKTABLE on the table in question, for sure. (DBCC CHECKDB should be running regularly anyway).
Possibly this question should be moved to the corruption forum.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 7, 2012 at 4:45 am
Yes. When I rebuild the clustered Index ,I am Getting the result and it returns all the rows.
select * from tablename where field_id =2 returns the row.
But Why it is happened? Can you tell.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply