Avatar of johnson1
johnson1
 asked on

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

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,
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
johnson1

ASKER
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.
johnson1

ASKER
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
johnson1

ASKER
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:-)
Your help has saved me hundreds of hours of internet surfing.
fblack61
PortletPaul

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]?