May 25, 2011 at 10:30 pm
Hi,
I am using identity column in table.but not set that column as primary key.
by makking that column as primary key any performance will be increased?
May 25, 2011 at 11:20 pm
my English is poor,sorry.
PRIMARY KEY=UNIQUE CLUSTERED INDEX+NOT NULL
CLUSTERED INDEX is important for a table,if there is not a CLUSTERED INDEX on your table ,you'd better create it.
May 26, 2011 at 12:18 am
kuppurajm (5/25/2011)
Hi,I am using identity column in table.but not set that column as primary key.
by makking that column as primary key any performance will be increased?
Primary Key is merely the 'official' row location method. It's not necessary but helps others down the road. It's merely an enforced UNIQUE index.
The one that will make a significant difference is what you use as the clustered index, which doesn't have to be the PK. However, it's not necessary if you know why you're using a heap instead of a cluster. In this case, since you're not sure, I would recommend using a clustered index on your most common joining or searching columns. The PK is for convenience, really, and make it non-clustered if the identity is merely there for identification, and not joining/searching.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 26, 2011 at 1:01 am
Adding a point, depends on the cluster key and its uniquness, the other non-cluster key will change.
Please refer the below:
http://sqlzealot.blogspot.com/2011/02/sql-server-index-details.html
May 26, 2011 at 6:45 am
kuppurajm (5/25/2011)
I am using identity column in table.but not set that column as primary key.by makking that column as primary key any performance will be increased?
Not necessarily. Look at the predicate of you queries, are they accessing/filtering by such an indentity column?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 26, 2011 at 6:51 am
There's no way to make a general rule out of this. It must be evaluated for all tables inividually depending on the data and how it is accessed.
What problem are you trying to solve at the moment?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply