Hi,
In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
changing the table column nullability via the ALTER TABLE ALTER COLUMN while
a default is also defined for that column? If yes, what are they?
--
Many thanks,
OskarYou'll need to drop the default constraint before ALTER TABLE...ALTER COLUMN
and re-add the constraint afterwards.
Column nullability doesn't affect the behavior of the default constraint so
I'm not aware of any drawbacks. The default value is used only when the
column is not included in the column list of an INSERT statement. Of
course, an explicit NULL value will be permitted only when the column allows
nulls.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:D50EF76B-BF46-4B49-9AD9-07364BF75E8A@.microsoft.com...
> Hi,
> In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
> changing the table column nullability via the ALTER TABLE ALTER COLUMN
> while
> a default is also defined for that column? If yes, what are they?
> --
> Many thanks,
> Oskar
>|||Yes, but when I needed to change a column from NULL to NOT NULL in one of the
tables and a default was defined on that column, the SQL Server parser didn't
complain about that. It allowed me to change the definition without any
warnings. Does this mean that I've done it in the wrong way? If yes, why?
--
Many thanks,
Oskar
"Dan Guzman" wrote:
> You'll need to drop the default constraint before ALTER TABLE...ALTER COLUMN
> and re-add the constraint afterwards.
> Column nullability doesn't affect the behavior of the default constraint so
> I'm not aware of any drawbacks. The default value is used only when the
> column is not included in the column list of an INSERT statement. Of
> course, an explicit NULL value will be permitted only when the column allows
> nulls.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:D50EF76B-BF46-4B49-9AD9-07364BF75E8A@.microsoft.com...
> > Hi,
> > In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
> > changing the table column nullability via the ALTER TABLE ALTER COLUMN
> > while
> > a default is also defined for that column? If yes, what are they?
> >
> > --
> > Many thanks,
> > Oskar
> >
> >
>
>|||No, Oskar, you didn't do anything wrong. My comment about dropping and
re-adding the constraint applies when changing the column data type but not
when changing only column nullability. Sorry for the confusion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:3049DDD6-BA64-4A17-BEB6-7B34B9C68D80@.microsoft.com...
> Yes, but when I needed to change a column from NULL to NOT NULL in one of
> the
> tables and a default was defined on that column, the SQL Server parser
> didn't
> complain about that. It allowed me to change the definition without any
> warnings. Does this mean that I've done it in the wrong way? If yes, why?
> --
> Many thanks,
> Oskar
> "Dan Guzman" wrote:
>> You'll need to drop the default constraint before ALTER TABLE...ALTER
>> COLUMN
>> and re-add the constraint afterwards.
>> Column nullability doesn't affect the behavior of the default constraint
>> so
>> I'm not aware of any drawbacks. The default value is used only when the
>> column is not included in the column list of an INSERT statement. Of
>> course, an explicit NULL value will be permitted only when the column
>> allows
>> nulls.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
>> news:D50EF76B-BF46-4B49-9AD9-07364BF75E8A@.microsoft.com...
>> > Hi,
>> > In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
>> > changing the table column nullability via the ALTER TABLE ALTER COLUMN
>> > while
>> > a default is also defined for that column? If yes, what are they?
>> >
>> > --
>> > Many thanks,
>> > Oskar
>> >
>> >
>>
2012年2月18日星期六
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" <Eric@.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|||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:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@.microsoft.com...
>
> 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 Cluster
ed
> 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. I
f
> 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" <Eric@.discussions.microsoft.com> wrote in message
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:
>
Then by all means create a PK and specify Clustered.
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.
> 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
--|||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:
> 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 attrib
ute of the table. That
is not true. The only way to guarantee that the result from a SELECT stateme
nt is ordered in any
certain way is to have ORDER BY. This is not exclusive to cursor operations,
btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.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 'ord
er
> by' cluase. I'm using a cursor in only one instance on my db. I would li
ke
> to add a clustered index to one of my tables (Company.ID) but this table i
s
> in fact included in my cursor statement. Appreciate your input.
> "David Portas" wrote:
>|||Eric wrote:
> David:
> If you could please clarify the effect a cursor may have w/an omitted 'ord
er
> by' cluase. I'm using a cursor in only one instance on my db. I would li
ke
> to add a clustered index to one of my tables (Company.ID) but this table i
s
> in fact included in my cursor statement. Appreciate your input.
> "David Portas" wrote:
>
If you write cursor code without specifying ORDER BY in the DECLARE
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/d...br />
1zu4.asp
http://msdn.microsoft.com/library/d...br />
3339.asp
"Eric" <Eric@.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"
<Eric@.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
>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).
PK is one thing, clustering is an option.
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.
>Will making this column a primary key have any adverse effects?
Only that it will then enforce uniqueness, can't have null values,
just the standard behaviors of a PK.
>There are
>currently 3,000 rows in the table and it's 10 columns wide.
Extremely modest by modern standards.
J.
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" <Eric@.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|||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:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@.microsoft.com...
>
> 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 Cluster
ed
> 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. I
f
> 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" <Eric@.discussions.microsoft.com> wrote in message
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:
>
Then by all means create a PK and specify Clustered.
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.
> 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
--|||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:
> 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 attrib
ute of the table. That
is not true. The only way to guarantee that the result from a SELECT stateme
nt is ordered in any
certain way is to have ORDER BY. This is not exclusive to cursor operations,
btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.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 'ord
er
> by' cluase. I'm using a cursor in only one instance on my db. I would li
ke
> to add a clustered index to one of my tables (Company.ID) but this table i
s
> in fact included in my cursor statement. Appreciate your input.
> "David Portas" wrote:
>|||Eric wrote:
> David:
> If you could please clarify the effect a cursor may have w/an omitted 'ord
er
> by' cluase. I'm using a cursor in only one instance on my db. I would li
ke
> to add a clustered index to one of my tables (Company.ID) but this table i
s
> in fact included in my cursor statement. Appreciate your input.
> "David Portas" wrote:
>
If you write cursor code without specifying ORDER BY in the DECLARE
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/d...br />
1zu4.asp
http://msdn.microsoft.com/library/d...br />
3339.asp
"Eric" <Eric@.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"
<Eric@.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
>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).
PK is one thing, clustering is an option.
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.
>Will making this column a primary key have any adverse effects?
Only that it will then enforce uniqueness, can't have null values,
just the standard behaviors of a PK.
>There are
>currently 3,000 rows in the table and it's 10 columns wide.
Extremely modest by modern standards.
J.
订阅：
博文 (Atom)