SQL Server 2008 VS 2000 Create Table Syntax Difference

Preface

This is a post in record of how to resolve some incompatible SQL syntax (Create Table Syntax) between SQL Server 2008 and 2000. I will also explain the difference between two scripts (one is not working on SQL Server 2000 and the other is working on both), and I will analyze why they will create the same table too.

The Detailed Problem

Recently, I just came across an error when I ran a SQL script on SQL Server 2000. The wired thing is that the same script is working correctly on SQL Server 2008. Then I notice it must be compatibility problems.

The exact problem lies in the create table part as shown below:

CREATE TABLE [dbo].[tblUserTable] (
    [iRecordID] [int] IDENTITY(1, 1) NOT NULL,
    [iAttribute1] [int] NOT NULL,
    [biAttribute2] [bigint] NOT NULL,
    PRIMARY KEY CLUSTERED
    (
        [iRecordID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The above Create Table SQL query is actually directly exported from SQL Server 2008 with the embedded Primary Key declaration. This can be run on SQL Server 2008 of course, however, it will report syntax error when run it on SQL Server 2000.

It finally turns out that it is the embedded Primary Key declaration that is not supported in SQL Server 2000. So I just change a bit and rewrite the Create Table into the following one by moving out the primary key declaration and it is proved to be working on both SQL Server 2008 and 2000.

CREATE TABLE [dbo].[tblUserTable] (
    [iRecordID] [int] IDENTITY(1, 1) NOT NULL,
    [iAttribute1] [int] NOT NULL,
    [biAttribute2] [bigint] NOT NULL,
)
ALTER TABLE [dbo].[tblUserTable]
WITH NOCHECK ADD CONSTRAINT [PK_tblUserTable]
PRIMARY KEY CLUSTERED ([iRecordID])

To understand why such rewrite won’t change the result after execution of the create table, there are several points worth noting:

1) The “With (PAD_INDEX = OFF, …)” part in the first script actually could be eliminated because it just declare all the default options explicitly. That is, even if we don’t have this “With (PAD_INDEX = OFF, …)” part, all the options in this part will be set into the default value which is exactly the same as declared in the “With (PAD_INDEX = OFF, …)”
2) Both Clustered Primary Key is declared. To understand the difference between the Clustered index and the non-clustered index, you might want to check MSDN for further reference. Briefly, Clustered index sort the table rows in a physical manner, i.e., the real physical rows are sorted while the Non-clustered index sort the rows in a logical manner, i.e., each entry in the index is just a pointer or a locator to the real table row.
3) ON [PRIMARY] just indicates which file group the script will create table on.

Summary

To summarize, in this post, I rewrite an incompatible SQL script to create table correctly on both SQL Server 2008 and 2000. I also analyzed the detailed difference between these two different scripts and explains why they could create the same table.

Written on August 18, 2014