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.
| Author |
Topic |
|
freephoneid
Yak Posting Veteran
52 Posts |
 Posted - 2010-06-10 : 18:02:59
|
| Hi, I'm using SQL Server 2008 server & I would like to know what is the difference between the following 2 create statements:CREATE TABLE [dbo].[Accounts]( [id] [int] IDENTITY(1,1) NOT NULL, [login] [nvarchar](100) NOT NULL, [password] [nvarchar](100) NOT NULL, [name] [nvarchar](256) NOT NULL, [phones] [nvarchar](max) NOT NULL,CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED ( [id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]CREATE TABLE [dbo].[Accounts]( [id] [int] IDENTITY(1,1) NOT NULL, [login] [nvarchar](100) NOT NULL, [password] [nvarchar](100) NOT NULL, [name] [nvarchar](256) NOT NULL, [phones] [nvarchar](max) NOT NULL,CONSTRAINT [PK_Accounts] PRIMARY KEY)Which create statement should I consider while creating the above table? What would be the efficient method from above statement?Thanks! |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-10 : 18:10:38
|
| You haven't got the PK columns defined in the second one ...Just some thoughts:Do you really need Nvarchar for those columns?100 character login? and a password? and the name is 256 characters? Need a big label to put on the envelope!Does PHONES need Nvarchar?We normally start IDENTITY at 10,000 rather than "1" - as very small numbers look a bit silly in a list where everything else is 2, 3, 4, 5 digits ... |
 |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-06-10 : 18:15:17
|
| Hi Kristen, Thanks for the reply. What I really want to know is whether adding below lines are efficient or not:CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]In 2nd create table, I've defined primay key as shown below:CONSTRAINT [PK_Accounts] PRIMARY KEY(id)Sorry, I missed the id in my post earlier.I'll appreciate if you can tell me the difference about all these extra parameters & whether it would be effective using all these paramteres such as PAD_INDEX = OFF, IGNORE_DUP_KEY, etcThanks! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-10 : 18:38:02
|
| They are the same, pretty much.There are lots of optional settings. Mostly going with the defaults is all you need to do.Worth giving consideration as to whether you want something OTHER than the PK as the Clustered Index, but 95% of the time the PK being the Clustered Index is best.You may want to consider having a fill-factor which is LESS than 100%. If the index is an IDENTITY value (as in this case) then 100% is best.For a random key you might want to leave some "slack" in the index pages so that additional keys can be added before a page-split is necessary; but there is also good evidence that that has no benefit at all - the fact that all pages are then a little bit loose means that the index is bigger, and a few more disk reads are required to find anything ... which is offset by not needing to split a page when you add a key. On balance folk will prefer to save disk space and pack the index at 100%Worth considering what order your PK keys are when you have two-or-more keys in the index. The first one should be the column that is the most selective - that is to say its values are more distinct, and there are fewer duplicates, than other columns. An IDENTITY column is perfect as every row is unique; at the opposite end is a BIT field which is either 1 or 0 on every row, and thus not selective at all.So, basically, the default settings are going to be fine 95% of the time |
|
|
|
|
|
|
|
|
SQL Server 2008 Primay Key clustered