DATABASE level selection settings help

Question

DATABASE level selection settings help

Sana4u

SSC-Addicted

Points: 464
More actions
April 23, 2015 at 4:01 am

#297347

Hi All,
I have following table structure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FAS_LEDGER](
[TID] [INT] IDENTITY(1,1) NOT NULL,
[TDATE] [DATETIME] NOT NULL,
[SOURCE] [NVARCHAR](50) NOT NULL,
[CHEQUE_NO] [NVARCHAR](50) NOT NULL,
[NARRATION] [NVARCHAR](MAX) NOT NULL,
[MASTER_CODE] [NVARCHAR](50) NOT NULL,
[DR] [FLOAT] NOT NULL,
[CR] [FLOAT] NOT NULL,
[AUDIT_FLAG] [BIT] NOT NULL,
[AUDIT_DATE] [DATETIME] NOT NULL,
[DOMAIN] [NVARCHAR](50) NULL,
CONSTRAINT [PK_FAS_LEDGER_HO] PRIMARY KEY CLUSTERED
(
[TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[FAS_LEDGER] WITH CHECK ADD CONSTRAINT [FK_FAS_LEDGER_FAS_MASTER_ACCOUNT] FOREIGN KEY([MASTER_CODE])
REFERENCES [dbo].[FAS_MASTER_ACCOUNT] ([MASTER_CODE])
GO
ALTER TABLE [dbo].[FAS_LEDGER] CHECK CONSTRAINT [FK_FAS_LEDGER_FAS_MASTER_ACCOUNT]
GO
in this table I have 1571182 rows
the problem is someone did some changes in this LEDGER table
I can insert new rows in this table but when I try to fetch latest rows on the basis of following query then I did not get the latest row.
means
following query gives all the rows of this table
SELECT * FROM dbo.FAS_LEDGER ORDER BY TID DESC
and when I try to filter Master_code = '02-07-01-008-0001' and apply oder by TDATE I do not get latest rows
SELECT * FROM dbo.FAS_LEDGER WHERE MASTER_CODE = '02-07-01-008-0001'
ORDER BY TDATE DESC
can anyone please help me?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

since there's an identity in the table, if you change hte order by to that column, do you get the data you want? it sounds like someone updated your date field, ?

SELECT * FROM dbo.FAS_LEDGER WHERE MASTER_CODE = '02-07-01-008-0001'

ORDER BY TID DESC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sana4u SSC-Addicted Points: 464 More actions · Answer 2

Lowell (4/23/2015)
since there's an identity in the table, if you change hte order by to that column, do you get the data you want? it sounds like someone updated your date field, ?
SELECT * FROM dbo.FAS_LEDGER WHERE MASTER_CODE = '02-07-01-008-0001'
ORDER BY TID DESC

Yes, I tried TID and I get the data

but why Can't I see latest rows when I use TDATE in order by?? any idea?

Lowell SSC Guru Points: 323546 More actions · Answer 3

the order by will not eliminate any rows with the query you gave;

only the WHERE condition does; if you are using a different query than the one you posted, show us that; any joins or additional where conditions would affect results.

how many rows are returned? maybe since some rows have a TDATE of NULL , they show up at the top or bottom of your list, instead of where you expect them?

maybe you need to order by SOURCE,TDATE or something to see data grouped the way you expect?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sana4u SSC-Addicted Points: 464 More actions · Answer 4

Lowell (4/23/2015)
the order by will not eliminate any rows with the query you gave;
only the WHERE condition does; if you are using a different query than the one you posted, show us that; any joins or additional where conditions would affect results.
how many rows are returned? maybe since some rows have a TDATE of NULL , they show up at the top or bottom of your list, instead of where you expect them?
maybe you need to order by SOURCE,TDATE or something to see data grouped the way you expect?

Thanks for the response

I executed DBCC CHECKTABLE on my table and I am getting following

DBCC results for 'FAS_LEDGER'.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1106102981, index ID 1, partition ID 72057594045464576, alloc unit ID 72057594048741376 (type In-row data). Page (1:93827) is missing a reference from previous page (1:94591). Possible chain linkage problem.

There are 1577152 rows in 49077 pages for object "FAS_LEDGER".

CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'FAS_LEDGER' (object ID 1106102981).

repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (ASML_ERP_2013.dbo.FAS_LEDGER).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

can you please help me in fixing this issue as this is the reason why I am not getting latest rows from table.