July 16, 2014 at 8:41 am
sknox (7/16/2014)
Koen Verbeeck (7/16/2014)
Raghavendra Mudugal (7/16/2014)
Koen Verbeeck (7/16/2014)
Unless a clustered index was already defined. 🙂
how you mean? "already"
this simple statement creates a clustered index
CREATE TABLE TT1
(
ID INT PRIMARY KEY,
NAME VARCHAR(10)
);
You can create a table as a heap, create a clustered non-unique index on some columns and then define a primary key.
For example:
CREATE TABLE [dbo].[TT2](
[CI] [int] NOT NULL,
[PK] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CI_TT2] ON [dbo].[TT2]
(
[CI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT2] ADD CONSTRAINT [PK_TT2] PRIMARY KEY NONCLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Just expanding on this a bit, it might be a good QotD at some point.
USE tempdb;
GO
CREATE TABLE [dbo].[TT2](
[CI] [int] NOT NULL,
[PK] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CI_TT2] ON [dbo].[TT2]
(
[CI] ASC
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT2] ADD CONSTRAINT [PK_TT2] PRIMARY KEY
(
[PK] ASC
) ON [PRIMARY]
GO
SELECT i.name AS IndexName,o.name AS ObjName,i.type_desc AS IndexType
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
WHERE o.name = 'TT2';
Notice how the creation of a PK defaults to creating a non-clustered index in this case?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 16, 2014 at 9:17 am
Dave62 (7/16/2014)
nimbell (7/15/2014)
Question didn't really make an adequate distinction between the logical and the physical:Does the logical definition of a Primary Key imply the presence of an index? No
In SQL Server, does the presence of a Primary Key imply some kind of index is also present? Yes
The question asked: "Is a Primary Key an index?"
If the presence of a Primary Key implies some kind of index is also present, the word also confirms that the index is not the Primary Key.
My point is just that it wasn't immediately obvious what part of knowledge the question was testing. The skill here was more in interpreting the question correctly than in understanding either the implication or implementation of PKs.
July 16, 2014 at 11:42 am
SQLRNNR (7/16/2014)
Notice how the creation of a PK defaults to creating a non-clustered index in this case?
Yea... as the CI is created at first and a table can have one CI, so when the PK is created it checks if there are any existing CI if yes then it picks NCI if not then PK's column also becomes CI.
thank you for the example.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
July 16, 2014 at 12:31 pm
There are important distinctions, but this is a SQL server board and SQL server implements the logical construct of the primary key by creating a special case index.
July 16, 2014 at 1:00 pm
I have to admit I had to think about this one longer than I should have. Ultimately, it was the category of the question (Database Design) that saved me. That made me realize Phil was asking about the concept, not the implementation.
July 16, 2014 at 1:15 pm
Good question thanks Phil
July 16, 2014 at 7:21 pm
Koen Verbeeck (7/16/2014)
So it is 100% possible to decouple the primary key from the index
Can you illustrate how?
, as the latter one is just a matter of implementation.
The fact of life: PRIMARY KEY implemented in form of a unique index.
So, in reality - PK is an index.
Unique index, to be specific.
Oh, sorry, there is no unique index, actually.
It's UNIQUE constraint, to be pedantic.
Which, again, is implemented in form of an index.
So, it is an index, despite it's not.
If MS one sunny day will introduce any other form of PK implementation the answer could be changed.
But at the current moment in history - this QOTD is plainly wrong.
_____________
Code for TallyGenerator
July 16, 2014 at 7:27 pm
PurpleLady (7/16/2014)
.... but creating a primary key does create a corresponding index (if it doesn't already exist).
Wrong.
It creates a corresponding unique index even if you have 10 unique indexes already created on the column.
_____________
Code for TallyGenerator
July 17, 2014 at 1:25 am
well, it HAS an idex, so what ?
July 17, 2014 at 7:00 am
h.tobisch (7/17/2014)
well, it HAS an idex, so what ?
No, it's implemented in form of an index.
So, IT IS an index.
Take the index away -and there is no key.
Here is another example.
Is a human being a body?
Well, some religions tell us that there is a soul which can live without a body, which existed before and/or will exist after that time it's asociated with that particular body.
Probably it's true. In some other world(s).
But in this particular world, the world we all are living in - a human in its entirety is a body. And all of its appearances - including soul, mind, dreams, reactions, etc. - are the results of chemical reactions happening in the cells made of carbon-based materia.
Stop those reactions - and there is no human.
Is there anything else required for the human to exist? No, as long as chemical reactions inside the body are going - the human exists.
Therefore - a human is a body.
_____________
Code for TallyGenerator
July 17, 2014 at 8:03 am
@Sergei
Please correct me if I'm wrong but I'd always believed a primary key was enforced by a constraint which was implemented by means of both a UNIQUE index and NOT NULL constraints on all the participating columns.
The reason for my pedantry is nothing to do with metaphysics but all to do with foreign keys, and candidate keys. Hmmm. I've maybe got a few more QOTDs to do, even if I'm really just asking myself.
Best wishes,
Phil Factor
July 17, 2014 at 8:24 am
Interesting also, is that when you create a PK on a table, in SSMS that it lists that PK under Indexes and not under Constraints.
July 17, 2014 at 9:09 am
pmadhavapeddi22 (7/15/2014)
This is my understanding. primary key is a constraint not an index, but it creates an index automatically by default, but primary key is called a constraint not an index though. so I got it correct (though I had to think little bit)Good question 🙂
Good analytical thinking 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
July 17, 2014 at 9:16 am
Steve Lund (7/17/2014)
Interesting also, is that when you create a PK on a table, in SSMS that it lists that PK under Indexes and not under Constraints.
True, although PK is considered as Constraint not Index 😎
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
July 17, 2014 at 2:04 pm
Sergiy (7/16/2014)
Koen Verbeeck (7/16/2014)
So it is 100% possible to decouple the primary key from the indexCan you illustrate how?
Easy peasy. I draw a table diagram in Visio and declare the combination of a few columns to be the primary key.
(in other words, I am creating the logical design)
Where do you see an index?
The index we are talking about is the manifistation of the primary key on the physical level.
On the logical level however, I simply do not care how it is implemented at the physical level. It does not matter.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply