|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Any harm in adding a primary key...after my table has been created and populated with data?
I have an identity column (named 'CID') on my customers table. This value is often used in my queries and I'd like to take advantage of a clustered index (since none currently exist on the table). Will making this column a primary key have any adverse effects? There are currently 3,000 rows in the table and it's 10 columns wide. "Eric" <E***@discussions.microsoft.com> wrote in message 1. Why wasn't the table created with a PK in place already.news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@microsoft.com... > after my table has been created and populated with data? > I have an identity column (named 'CID') on my customers table. This > value is often used in my queries and I'd like to take advantage of a > clustered > index (since none currently exist on the table). > > Will making this column a primary key have any adverse effects? There are > currently 3,000 rows in the table and it's 10 columns wide. 2. Just because you make it a primary key, doesn't make it a clustered index. When you create a Primary Key in SQL Server it will choose Clustered by default, but it could be non-clustered if you tell it to. So to answer your question, you could just as easily create a clustered index on that CID column as create a Primary key on it. Should it be the Primary Key? <shrug> Who knows, you didn't post DDL, so it's hard to say. Since it is not a PK, I will assume that you are not using FK constraints either. This could be a very serious issue in your database. The PK and FK constraints are there to ensure the relational integrity of your data. If you have your customers table as described above, and an Orders table. Without these constraints, there is really nothing stopping someone from adding Orders to your database that don't map to any Customer, or delete Customers who may have orders pending for shipment. You should really finish modelling your system and include the PK and FK constraints, and any other constraints necessary to ensure the various integreties of your system (entity integrity, relational integrity and domain integrity). HTH Rick Sawtell MCT, MCSD, MCDBA 1) I have no idea as I did not design the db.
2) I wanted to take advantage of using a clustered index. I understand the importance of using the PK/FK for integrity. We are planning a db redesign in the near future, but for the time being, I just wanted to add the clustered index via a PK for performance purposes. Show quote "Rick Sawtell" wrote: > > "Eric" <E***@discussions.microsoft.com> wrote in message > news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@microsoft.com... > > after my table has been created and populated with data? > > I have an identity column (named 'CID') on my customers table. This > > value is often used in my queries and I'd like to take advantage of a > > clustered > > index (since none currently exist on the table). > > > > Will making this column a primary key have any adverse effects? There are > > currently 3,000 rows in the table and it's 10 columns wide. > > > 1. Why wasn't the table created with a PK in place already. > 2. Just because you make it a primary key, doesn't make it a clustered > index. When you create a Primary Key in SQL Server it will choose Clustered > by default, but it could be non-clustered if you tell it to. > > > So to answer your question, you could just as easily create a clustered > index on that CID column as create a Primary key on it. Should it be the > Primary Key? <shrug> Who knows, you didn't post DDL, so it's hard to say. > > Since it is not a PK, I will assume that you are not using FK constraints > either. This could be a very serious issue in your database. The PK and FK > constraints are there to ensure the relational integrity of your data. If > you have your customers table as described above, and an Orders table. > Without these constraints, there is really nothing stopping someone from > adding Orders to your database that don't map to any Customer, or delete > Customers who may have orders pending for shipment. > > You should really finish modelling your system and include the PK and FK > constraints, and any other constraints necessary to ensure the various > integreties of your system (entity integrity, relational integrity and > domain integrity). > > HTH > > Rick Sawtell > MCT, MCSD, MCDBA > > > > "Eric" <E***@discussions.microsoft.com> wrote in message Then by all means create a PK and specify Clustered.news:7585798D-DD21-4D84-9135-A21EB8F786DA@microsoft.com... > 1) I have no idea as I did not design the db. > 2) I wanted to take advantage of using a clustered index. > > I understand the importance of using the PK/FK for integrity. We are > planning a db redesign in the near future, but for the time being, I just > wanted to add the clustered index via a PK for performance purposes. > > "Rick Sawtell" wrote: > ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers_CID PRIMARY KEY CLUSTERED (CID) To get even better performance, you should also create indexes on what will eventually be your FK columns, so if you had an Orders table, you might do the following. CREATE NONCLUSTERED INDEX IX_Orders_CID ON dbo.Customers (CID) Rick Sawtell MCT, MCSD, MCDBA Eric wrote:
> 1) I have no idea as I did not design the db. Your question is whether adding the cluster key could do any harm. If> 2) I wanted to take advantage of using a clustered index. > > I understand the importance of using the PK/FK for integrity. We are > planning a db redesign in the near future, but for the time being, I just > wanted to add the clustered index via a PK for performance purposes. > > "Rick Sawtell" wrote: > your database is this weakly designed then I wouldn't take anything for granted. Maybe they left out an ORDER BY on a cursor somewhere and adding an extra index would cause some logic to fail. Test, test, test. -- David Portas SQL Server MVP -- David:
If you could please clarify the effect a cursor may have w/an omitted 'order by' cluase. I'm using a cursor in only one instance on my db. I would like to add a clustered index to one of my tables (Company.ID) but this table is in fact included in my cursor statement. Appreciate your input. Show quote "David Portas" wrote: > Eric wrote: > > 1) I have no idea as I did not design the db. > > 2) I wanted to take advantage of using a clustered index. > > > > I understand the importance of using the PK/FK for integrity. We are > > planning a db redesign in the near future, but for the time being, I just > > wanted to add the clustered index via a PK for performance purposes. > > > > "Rick Sawtell" wrote: > > > > Your question is whether adding the cluster key could do any harm. If > your database is this weakly designed then I wouldn't take anything for > granted. Maybe they left out an ORDER BY on a cursor somewhere and > adding an extra index would cause some logic to fail. Test, test, test. > > -- > David Portas > SQL Server MVP > -- > > I'm not David, but:
Some developer seems to think that the order of rows returned from a SELECT statement can be determined by having a certain index in place, or some other physical attribute of the table. That is not true. The only way to guarantee that the result from a SELECT statement is ordered in any certain way is to have ORDER BY. This is not exclusive to cursor operations, btw. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Eric" <E***@discussions.microsoft.com> wrote in message news:29804387-5EF8-434A-8BFA-D05A1303CBAE@microsoft.com... > David: > > If you could please clarify the effect a cursor may have w/an omitted 'order > by' cluase. I'm using a cursor in only one instance on my db. I would like > to add a clustered index to one of my tables (Company.ID) but this table is > in fact included in my cursor statement. Appreciate your input. > "David Portas" wrote: > >> Eric wrote: >> > 1) I have no idea as I did not design the db. >> > 2) I wanted to take advantage of using a clustered index. >> > >> > I understand the importance of using the PK/FK for integrity. We are >> > planning a db redesign in the near future, but for the time being, I just >> > wanted to add the clustered index via a PK for performance purposes. >> > >> > "Rick Sawtell" wrote: >> > >> >> Your question is whether adding the cluster key could do any harm. If >> your database is this weakly designed then I wouldn't take anything for >> granted. Maybe they left out an ORDER BY on a cursor somewhere and >> adding an extra index would cause some logic to fail. Test, test, test. >> >> -- >> David Portas >> SQL Server MVP >> -- >> >> Eric wrote:
> David: If you write cursor code without specifying ORDER BY in the DECLARE> > If you could please clarify the effect a cursor may have w/an omitted 'order > by' cluase. I'm using a cursor in only one instance on my db. I would like > to add a clustered index to one of my tables (Company.ID) but this table is > in fact included in my cursor statement. Appreciate your input. > "David Portas" wrote: > statement and if that cursor uses logic that assumes a particular ordering to the data then it may break at some point if the execution plan changes. This is made more likely if you create or drop indexes - i.e. the assumed order of the cursor may change. Always specify ORDER BY if the ordering is important to you. If you avoid cursors and stick to set-based code you'll avoid this potential problem. Most cursors are unnecessary and are written by people who don't know SQL well enough to do any better. -- David Portas SQL Server MVP -- If CID is unique (and it must be unique since it is an identity column), and
there are no natural key candidates, then do make it the primary key. Every table needs a primary key of some type. http://www.aspfaq.com/show.asp?id=2504 Also, 3000 rows x 10 columns is practically nothing, and if it's being hit frequently, SQL Server is probably keeping a complete in the buffer cache anyway and reducing the need to read it from disk. It would not benefit from a clustered index (although it wouldn't hurt). http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_1zu4.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_3339.asp Show quote "Eric" <E***@discussions.microsoft.com> wrote in message news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@microsoft.com... > after my table has been created and populated with data? > I have an identity column (named 'CID') on my customers table. This > value is often used in my queries and I'd like to take advantage of a > clustered > index (since none currently exist on the table). > > Will making this column a primary key have any adverse effects? There are > currently 3,000 rows in the table and it's 10 columns wide. On Wed, 28 Dec 2005 08:25:05 -0800, "Eric"
<E***@discussions.microsoft.com> wrote: >after my table has been created and populated with data? No harm.>I have an identity column (named 'CID') on my customers table. This PK is one thing, clustering is an option. >value is often used in my queries and I'd like to take advantage of a >clustered index (since none currently exist on the table). Alternatively clustering is one thing, PK is another. But if you want to do both at once, go ahead. Are there any other existing indexes? Creating a clustered index does mean rewriting the table and rebuilding the other indexes. > Only that it will then enforce uniqueness, can't have null values,>Will making this column a primary key have any adverse effects? just the standard behaviors of a PK. >There are Extremely modest by modern standards.>currently 3,000 rows in the table and it's 10 columns wide. J.  |
|||||||||||||||||||||||
Other interesting topics