July 10, 2017 at 11:09 pm
Comments posted to this topic are about the item Dropping PKs
July 10, 2017 at 11:09 pm
Good question, nice reminder thanks Steve
...
July 11, 2017 at 1:56 am
Confused question!
If the author intended the primary key is clustered, this apply (from BOL):
When a constraint that created a clustered index is deleted, the data rows that were stored in the leaf level of the clustered index are stored in a nonclustered table.
So the correct answer is wrong!
If the author intended that a clustered index exists and a constraint primary key is dropped
the clustered index is NOT dropped.
So the correct answer is wrong!
Here the example:use tempdb
create table i(i int unique clustered, constraint pk primary key(i))
exec sp_help i
alter table i drop constraint pk
exec sp_help i
drop table i
.... or maybe I don't understand the question!
July 11, 2017 at 2:21 am
Thanks Steve for this question and answer.
Although this is not the main topic, could be interested for some readers.
In the real world, we do not usually know the name of a constraint.
But we know the name of the table ( unless we do not have strong naming convention! ).
In this case, droping a constraint could be accomplished by using a script as shown below.
DECLARE @myTable NVARCHAR(256)= N'dbo.mycosts';
DECLARE @tSql NVARCHAR(MAX);
DECLARE @conType NVARCHAR(2) = 'PK'
SELECT
@tSql = 'ALTER TABLE ' +
@myTable +
' DROP CONSTRAINT ' +
name + ';'
FROM sys.key_constraints
WHERE [type] = @conType
AND [parent_object_id] = OBJECT_ID(@myTable);
--PRINT @tSql
EXEC sp_executeSQL @tSql;
'sys.key_constraints' is a system view. A interesting thing could be is how is this view is implemented.
In order to find out, we can query another system view sys.all_sql_modules by issuing following command
SELECT
definition
FROM sys.all_sql_modules
WHERE definition LIKE '%CREATE VIEW sys.key_constraints%';
The result is :
CREATE VIEW sys.key_constraints AS
SELECT
o.name
,o.object_id
,o.principal_id
,o.schema_id
,o.parent_object_id
,o.type
,o.type_desc
,o.create_date
,o.modify_date
,o.is_ms_shipped
,o.is_published
,o.is_schema_published
,r.indepid AS unique_index_id
,o.is_system_named
FROM sys.objects$ o
LEFT JOIN sys.syssingleobjrefs r
ON r.depid = o.object_id
WHERE o.type IN ('PK', 'UQ')
Here are sys.objects$ i syssingleobjrefs hidden tables which can be viewed by using DAC connection.
Similar, if we would like to find out what are permission on this view ( sys.key_constraints) we can issue following command.
DECLARE @object_id as bigint = (SELECT
object_id
FROM sys.all_sql_modules
WHERE definition LIKE '%CREATE VIEW sys.key_constraints%');
SELECT
*
FROM sys.database_permissions p
JOIN sys.database_principals pr
ON p.grantee_principal_id = pr.principal_id
WHERE p.major_id = @object_id;
And the result
GRANT SELECT ON OBJECT::[sys].[key_constraints] TO [public]
It means 'SELECT' is granted to the role 'Public'.
July 11, 2017 at 2:29 am
Darko Martinovic - Tuesday, July 11, 2017 2:21 AMIn the real world, we do not usually know the name of a constraint.
Unless you have coding rules that make you give names to all constraints 🙂
July 11, 2017 at 2:54 am
Carlo Romagnano - Tuesday, July 11, 2017 1:56 AMConfused question!
If the author intended the primary key is clustered, this apply (from BOL):When a constraint that created a clustered index is deleted, the data rows that were stored in the leaf level of the clustered index are stored in a nonclustered table.
So the correct answer is wrong!If the author intended that a clustered index exists and a constraint primary key is dropped
the clustered index is NOT dropped.
So the correct answer is wrong!Here the example:
use tempdb
create table i(i int unique clustered, constraint pk primary key(i))
exec sp_help i
alter table i drop constraint pk
exec sp_help i
drop table i
.... or maybe I don't understand the question!
The correct answer is right. But depending on how you created the table results may vary. In your example you already specify a unique clustered constraint before the primary key is created. Because of that the Clustered Index is created on the UQ and not on the PK. As a result dropping the PK will indeed not drop the clustered index. The poster of the question did not intend for this to be specified and probably had something like this in mind:
create table dbo.Mycosts(MyCostKey int not null)
ALTER TABLE dbo.Mycosts ADD CONSTRAINT MyCostsPK PRIMARY KEY (MyCostKey)
exec sp_help Mycosts
ALTER TABLE dbo.Mycosts DROP CONSTRAINT MyCostsPK
exec sp_help Mycosts
drop table dbo.Mycosts
This code gives the right result. But as you have proven the answer is only right if the table was created in a specific way.
July 11, 2017 at 5:59 am
Since the question begins with :
I have a table with a PK that is also the clustered index. I added this PK with the following code:
It leaves open the question of how the clustered index was created. Nothing indicates the clustered index did or did not exist prior to the creation of the PK constraint.
Sooooo...... the answer, in typical IT fashion is IT DEPENDS! but that wasn't a selection option.
Like so many interactions with database admin questions, there is usually a need for more information to have the correct response to questions.
One of my favorites: "SharePoint says it can't connect to the database"
July 11, 2017 at 6:01 am
Darko Martinovic - Tuesday, July 11, 2017 2:21 AMIn the real world, we do not usually know the name of a constraint.
But we know the name of the table ( unless we do not have strong naming convention! ).
In the real world, I would rename all the constraints to comply with an adequate naming convention. I wouldn't drop constraints automatically, either. 😉
Carlo Romagnano - Tuesday, July 11, 2017 1:56 AMConfused question!
If the author intended the primary key is clustered, this apply (from BOL):When a constraint that created a clustered index is deleted, the data rows that were stored in the leaf level of the clustered index are stored in a nonclustered table.
So the correct answer is wrong!If the author intended that a clustered index exists and a constraint primary key is dropped
the clustered index is NOT dropped.
So the correct answer is wrong!Here the example:
use tempdb
create table i(i int unique clustered, constraint pk primary key(i))
exec sp_help i
alter table i drop constraint pk
exec sp_help i
drop table i
.... or maybe I don't understand the question!
In your example, the primary key is not the clustered index. Your example has 2 indexes, one for the unique constraint and one for the PK constraint. The unique constraint is defined as clustered. If you drop the unique constraint (leaving the PK) you'll see that the non-clustered index is left and the clustered index is gone.
July 11, 2017 at 6:03 am
Tried it out. Did not work as "correct" answer says. Likely because my test table had the clustered index defined non-unique. Confused question.
July 11, 2017 at 6:08 am
morlindk - Tuesday, July 11, 2017 6:03 AMTried it out. Did not work as "correct" answer says. Likely because my test table had the clustered index defined non-unique. Confused question.
Then your primary key wasn't the clustered index as the question says.
July 11, 2017 at 7:45 am
For those talking about things being stored in a "nonclustered table", remember that a non-clustered table has no clustered index. The wording in BOL is awkward to poor in that they could have just come out and said the clustered index would be dropped, but it's still correct the way it's written.,
Also, don't forget that if the table in question has more than 128 Extents (8MB), the original clustered table will be held in place until the clustered index has been dropped and the table converted into a HEAP. If the table is large, that can create a slightly larger and possibly unwanted growth of you MDF/NDF file. The LDF could take a beating, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2017 at 2:35 am
Jeff Moden - Tuesday, July 11, 2017 7:45 AMFor those talking about things being stored in a "nonclustered table", remember that a non-clustered table has no clustered index. The wording in BOL is awkward to poor in that they could have just come out and said the clustered index would be dropped, but it's still correct the way it's written.,Also, don't forget that if the table in question has more than 128 Extents (8MB), the original clustered table will be held in place until the clustered index has been dropped and the table converted into a HEAP. If the table is large, that can create a slightly larger and possibly unwanted growth of you MDF/NDF file. The LDF could take a beating, as well.
Agreed..
I rather enjoyed this one, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
July 14, 2017 at 8:14 am
very easy one
Manik
You cannot get to the top by sitting on your bottom.
July 24, 2017 at 3:36 pm
I think the correct answer depends upon how the clustered index is created. The posed question is not explicit in that, only in how the constraint was created.
July 24, 2017 at 7:04 pm
jbwa - Monday, July 24, 2017 3:36 PMI think the correct answer depends upon how the clustered index is created. The posed question is not explicit in that, only in how the constraint was created.
That would be true but, this particular question was rather clear that creating the PK also created the Clustered Index.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply