June 25, 2009 at 2:02 am
Hi
I'm using SQLS2008 and I need to target SQLS2000. I've generated a change script using SMSS' "Auto generate change script" option and I need to change the syntax.
If I replaceWITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]withWITH PAD_INDEX, FILLFACTOR = 80it says that it is incorrect syntax and it is expecting'(', FILLFACTORand some other reserved words. Please could you help me? Is it because a clustered index padded anyway because it's, well, clustered?
CREATE TABLE dbo.Tmp_bob
(
pkColA varchar(15) NULL,
otherCol float(53) NULL,
pkColB varchar(3) NULL,
otherCols datetime NULL
)
GO
ALTER TABLE dbo.Tmp_bob ALTER COLUMN pkColA VARCHAR(15) NOT NULL;
GO
ALTER TABLE dbo.Tmp_bob ALTER COLUMN pkColB VARCHAR(3) NOT NULL;
GO
ALTER TABLE dbo.Tmp_bob ADD CONSTRAINT
PK_Tmp_bob PRIMARY KEY CLUSTERED
(
pkColA,
pkColB
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Thank you very much!
June 25, 2009 at 2:34 am
As far as I can tell (http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx), pad_index is not a valid option for ALTER TABLE ... ADD CONSTRAINT, only for CREATE INDEX
Why do you want to pad the index anyway?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2009 at 9:02 am
GilaMonster (6/25/2009)
Why do you want to pad the index anyway?
I thought that it was good to pad indices when space wasn't a problem and one wanted better performance.
June 25, 2009 at 9:41 am
It's a good idea if you're expecting inserts into the middle of the index and you want to minimise fragmentation. That's certainly a possibility with the index you have defined there, it wouldn't be likely if the cluster was on an identity.
Pad index just ensures that there's space left in the non-leaf levels of the index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2009 at 8:11 am
Thanks Gila!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply