urgent - sql table does not work any more as i tshould

johnson1
johnson1 used Ask the Experts™
on
Hello,
I added a column to a table in sql 2014. After it I am only able to get limited number of rows when I select from it and it takes a long time.
Is there a way to fix the table. I appreciate your help - it is very urgent to fix this.
Greetings,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
There's not enough information here to answer your question.

Explain 'I am only able to get a limited number of rows when I select from it, and do us a favor and right-click: Script out the 'Create Table' for all table(s) involved and paste into this question.

Author

Commented:
I get 1500 rows in 00:00:00 seconds when I select top 1500 rows like this

select top 1500 * from MyTable

If I search for 1540 top rows

select top 1540 * from MyTable it runs for 10 seconds before I stop it.
It returns 1500 rows right away but after that no more.

I have been using this table for a long time and never had a problem with it until now.

Author

Commented:
Here is the table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MyTable](
      [MyId] [int] IDENTITY(1,1) NOT NULL,
      [MyText] [varchar](max) NULL,
      [PId] [int] NULL,
      [YearId] [int] NULL,
      [DateCreated] [datetime] NULL,
      [CreatedBy] [int] NULL,
      [Shortname] [varchar](10) NULL,
 CONSTRAINT [PK_s_Class] PRIMARY KEY CLUSTERED
(
      [MyId] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_s_Payer] FOREIGN KEY([PId])
REFERENCES [dbo].[s_Payer] ([PId])
GO

ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_s_Payer]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Some text' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable'
GO

Author

Commented:
I am not sure why but now it works.
Maybe I was running some query at the table. I closed all sql windows and then it worked:-)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
which column was added?

have you tried to update statistics for that table?

is there any ORDER BY involved in your TOP 1500 or 1540 queries?

& just curious: is it really called [MyTable]?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial