N00b SQLS2008 to SQLS2000 Syntax Problem

• AndrewJacksonZA

  SSCarpal Tunnel

  Points: 4143

  More actions

  June 25, 2009 at 2:02 am

  #211370

  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!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 25, 2009 at 2:34 am

  #1015133

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• AndrewJacksonZA

  SSCarpal Tunnel

  Points: 4143

  More actions

  June 25, 2009 at 9:02 am

  #1015362

  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.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 25, 2009 at 9:41 am

  #1015396

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• AndrewJacksonZA

  SSCarpal Tunnel

  Points: 4143

  More actions

  June 26, 2009 at 8:11 am

  #1016049

  Thanks Gila!