Difference between two creat table statments

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Difference between two creat table statments

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-01-29 : 12:46:58

I created a table by two ways, the first was from the design view and the second through my first attemtp at a creat table statment in Query Analizer. The two scripts below are what SQL Studio generated to recreate each table. They look very simlare to me, can someone look the two over and tell me if there is really any difference I should care about or if it is just syntac? (Yes, I know somce column names and types are different)

CREATE TABLE [dbo].[SectionInfo](
	[ID] [smallint] IDENTITY(1,1) NOT NULL,
	[SectionTitle] [varchar](250) NOT NULL,
	[ShortName] [varchar](50) NOT NULL,
	[LinkName] [varchar](50) NOT NULL,
	[Synopsis] [varchar](max) NOT NULL,
 CONSTRAINT [PK_SectionInfo] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [LinkName] UNIQUE NONCLUSTERED 
(
	[LinkName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[SectionInfo](
	[SectionID] [smallint] IDENTITY(0,1) NOT NULL,
	[SectionTitle] [varchar](250) NOT NULL,
	[ShortName] [varchar](50) NOT NULL,
	[LinkName] [varchar](50) NOT NULL,
	[Synopsis] [varchar](max) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[SectionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[LinkName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-29 : 13:16:33

The only thing that caught my eye was that in the first table the seed value for the identity column is 1, but in the second it is 0.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:26:24

In addition I just saw in first query you've names specified for constraints which makes it easier if you want to modify or drop them. otherwise you've first search in catalog view and get its name before you do some altering on them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-01-29 : 14:35:03

The frist one was when I did it up in deisgn view of Managment Studio. The second one I created by using the code below. Is there a way to mode my code so I can name the constaints like in the first one? I d owant to have reasy names for them

CREATE TABLE dbo.SectionInfo
(
	SectionID smallint IDENTITY(0,1) NOT NULL PRIMARY KEY CLUSTERED,
	SectionTitle varchar(250) NOT NULL,
	ShortName varchar(50) NOT NULL,
	LinkName varchar(50) NOT NULL UNIQUE NONCLUSTERED,
	Synopsis varchar(MAX) NOT NULL
)
ON [PRIMARY];

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:52:44

for that you need to do is to include constraints separately beneath as shown in first query with names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-01-29 : 15:27:10

Great, thanks for the help. I recreated the table and moved the contrainst to below the column decloration and named them. Now the output is identical.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 15:33:33

ok.. great

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources