Microsoft SQL Server Syntax Error involving CREATE TABLE

Question

I'm new at programming with Microsoft SQL and am trying to design a table that supports storing data over time that can eventually be used to plot a graph. I found an article (https://www.simple-talk.com/sql/database-administration/database-design-a-point-in-time-architecture/) that seems to point towards a design that could support something like that. However, the test code on it for CREATE TABLE doesn't seem to work when I try it.

CREATE TABLE [dbo].[Test_PTA_Table](
   [TestTablePK] [int] IDENTITY(1,1) NOT NULL,
   [TestTableText] [varchar](50) 
       COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [DateCreated] [datetime]     

       NOT NULL CONSTRAINT [DF_Test_PTA_Table_DateCreated]

                                      DEFAULT (getdate()),
   [DateEffective] [datetime] NOT NULL,
   [DateEnd] [datetime] NULL,
   [OperatorCode] [varchar](50) 

       COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [DateReplaced] [datetime] NULL   

       CONSTRAINT [DF_Test_PTA_Table_DateReplaced]
                                   DEFAULT (getdate()),
 CONSTRAINT [PK_Test_PTA_Table] PRIMARY KEY CLUSTERED 
(
   [TestTablePK] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

This throws an error on my machine, saying:

Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near '('.

Curiously enough, it works as soon as I remove the offending line of code WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) so I'm not sure what is going on here. Why is that line of code causing problems when the documentation for the Create Table syntax supports it in their help file? Knowing me the issue is probably a missing silly comma or something, but any help would be greatly appreciated. Thanks!

Alex · Accepted Answer · 2016-03-04 00:05:23Z

1

The syntax is valid and should compile on SQL2008 and higher, however, saying this, it is relatively safe to drop these options from your script.

Regarding their meaning,. PAD_INDEX specifies the index padding without the default being OFF. When it is ON the percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index, otherwise the intermediate-level pages are filled to near capacity, Generally you should never set this to ON unless you know exactly what you are doing

Regarding IGNORE_DUP_KEY, when it is OFF (default value), a duplicate key value causes an error and the entire statement is rolled back. That is, if the statement attempted to insert multiple rows, no rows are inserted, however, when it is set to ON this error is ignored and one of the duplicate rows will be inserted. As this option is set to OFF by default you can remove it safely from your script.

answered Mar 4, 2016 at 0:05

Alex

21.9k11 gold badges68 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Derp M. Over a year ago

+1 for explaining how the options work, and I did drop it from my script without issues. It still bothers me though why it wouldn't work if it was a valid syntax. Why wouldn't it work on my end?

Alex Over a year ago

Are you using SQL Server 2000 by any chance ? The syntax in question is support from SQL2005 onwards including Azure SQL.

Derp M. Over a year ago

SQL Server 2008 R2 actually.

Collectives™ on Stack Overflow

Microsoft SQL Server Syntax Error involving CREATE TABLE

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related