Link to home
Create AccountLog in
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,
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of johnson1
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.
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
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:-)
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]?