May 23, 2012 at 12:13 pm
Hello,
I was wondering and cannot think straight on this, So I came with an idea of getting ideas/views from different people..
HARD RULE : When a Primary Key is defined on a table for a column, by default NULLS are not allowed and Clustered Index is created behind Scenes.
MY question : When an Unique Key and NOT NULL is defined on a column for a table, Does it creates CLUSTERED INDEX behind scenes ?
My understanding : I was reading on Internet and in books too, when a Unique Key is defined on table it creates a NON-CLUSTERED INDEX behind scenes. But does this changes, when a NOT NULL is defined along with Unique Key on a table, as this is logically creating a primary key. And I believe that Sql Server should created a Clustered Index.
Please let me know, your valuable comments on this..... Thank you
May 23, 2012 at 12:22 pm
quillis131 (5/23/2012)
Hello,I was wondering and cannot think straight on this, So I came with an idea of getting ideas/views from different people..
HARD RULE : When a Primary Key is defined on a table for a column, by default NULLS are not allowed and Clustered Index is created behind Scenes.
MY question : When an Unique Key and NOT NULL is defined on a column for a table, Does it creates CLUSTERED INDEX behind scenes ?
My understanding : I was reading on Internet and in books too, when a Unique Key is defined on table it creates a NON-CLUSTERED INDEX behind scenes. But does this changes, when a NOT NULL is defined along with Unique Key on a table, as this is logically creating a primary key. And I believe that Sql Server should created a Clustered Index.
Please let me know, your valuable comments on this..... Thank you
When you define a PRIMARY KEY on a table it defaults to being the clustered index on the table. Also, it doesn't default to NOT NULL but requires that NULLS not exist.
When creating indexes using the CREATE INDEX statement, all indexes default to nonclustered regardless if the index is declared UNIQUE or not and this does not change just because a column may be defined NOT NULL.
Also, there can only be one clustered index on a table.
May 23, 2012 at 12:22 pm
quillis131 (5/23/2012)
Hello,I was wondering and cannot think straight on this, So I came with an idea of getting ideas/views from different people..
HARD RULE : When a Primary Key is defined on a table for a column, by default NULLS are not allowed and Clustered Index is created behind Scenes.
MY question : When an Unique Key and NOT NULL is defined on a column for a table, Does it creates CLUSTERED INDEX behind scenes ?
My understanding : I was reading on Internet and in books too, when a Unique Key is defined on table it creates a NON-CLUSTERED INDEX behind scenes. But does this changes, when a NOT NULL is defined along with Unique Key on a table, as this is logically creating a primary key. And I believe that Sql Server should created a Clustered Index.
Please let me know, your valuable comments on this..... Thank you
No it won't do that. For one thing there can be one and ONLY one clustered index on a table. Just because it is not null does not mean it should be the clustered index.
Also just to be clear about your primary key and NULL comment.
HARD RULE : When a Primary Key is defined on a table for a column, by default NULLS are not allowed and Clustered Index is created behind Scenes.
The NOT NULL is not a default. You can't have a NULLABLE primary key. The two contradict each other. That is like saying you want a dead living fish.
--EDIT--
Dang I have answered several questions today that are nearly identical to the first answer less than a minute later. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2012 at 1:17 pm
quillis131 (5/23/2012)
I was wondering and cannot think straight on this, So I came with an idea of getting ideas/views from different people..
Make it easy: always specify all the options you want, rather than relying on hard-to-remember defaults:
DECLARE @Example1 AS TABLE
(
col1 integer NOT NULL PRIMARY KEY CLUSTERED,
col2 integer NULL UNIQUE NONCLUSTERED
);
DECLARE @Example2 AS TABLE
(
col1 integer NOT NULL PRIMARY KEY NONCLUSTERED,
col2 integer NULL UNIQUE CLUSTERED
);
DECLARE @Example3 AS TABLE
(
col1 integer NOT NULL PRIMARY KEY NONCLUSTERED,
col2 integer NOT NULL UNIQUE CLUSTERED
);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 2:39 pm
Also note that a unique column can contain 1 null:
USE test;
GO
CREATE TABLE pktest (id int primary key clustered, somestuff varchar(250) unique);
GO
INSERT INTO pktest (id, somestuff)
VALUES(NULL, '1 null is not okay for primary key');
GO
INSERT INTO pktest (id, somestuff)
VALUES(1, NULL);--1 null is allowed in a unique column
GO
INSERT INTO pktest (id, somestuff)
VALUES(2, NULL); --2 nulls are NOT allowed
GO
SELECT * FROM pktest;
GO
DROP TABLE pktest;
GO
Jared
CE - Microsoft
May 23, 2012 at 3:16 pm
SQLKnowItAll (5/23/2012)
Also note that a unique column can contain 1 null...
Unless the unique index is filtered to exclude NULLs 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 3:21 pm
SQL Kiwi (5/23/2012)
SQLKnowItAll (5/23/2012)
Also note that a unique column can contain 1 null...Unless the unique index is filtered to exclude NULLs 😀
Come on... That's like saying a column can accept nulls unless it can't. 😀
Jared
CE - Microsoft
May 23, 2012 at 3:25 pm
SQLKnowItAll (5/23/2012)
SQL Kiwi (5/23/2012)
SQLKnowItAll (5/23/2012)
Also note that a unique column can contain 1 null...Unless the unique index is filtered to exclude NULLs 😀
Come on... That's like saying a column can accept nulls unless it can't. 😀
Create table Blah (
id int
)
create unique nonclustered index Blah2 on Blah (ID) where ID is not null
Unique index on a column that allows nulls that will never contain any null values
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2012 at 3:29 pm
GilaMonster (5/23/2012)
SQLKnowItAll (5/23/2012)
SQL Kiwi (5/23/2012)
SQLKnowItAll (5/23/2012)
Also note that a unique column can contain 1 null...Unless the unique index is filtered to exclude NULLs 😀
Come on... That's like saying a column can accept nulls unless it can't. 😀
Create table Blah (id int
)
create unique nonclustered index Blah2 on Blah (ID) where ID is not null
Unique index on a column that allows nulls that will never contain any null values
Ha, I understand. Just making the point that since I didn't specify that the index was filtered, it was assumed to not be. I suppose there's nothing wrong with throwing that in there though.
Jared
CE - Microsoft
May 23, 2012 at 3:40 pm
GilaMonster (5/23/2012)
Unique index on a column that allows nulls that will never contain any null values
Ahem :w00t: So, the *index* won't contain any NULLs, but the column can (which I know you know, but I was pointing out to Jared):
CREATE TABLE #Example
(
id integer NOT NULL PRIMARY KEY CLUSTERED,
somestuff varchar(250) NULL
);
GO
CREATE UNIQUE NONCLUSTERED INDEX uq1
ON #Example (somestuff)
WHERE somestuff IS NOT NULL;
GO
INSERT #Example
(id, somestuff)
VALUES
(1, 'banana'),
(2, 'apple'),
(3, NULL),
(4, NULL);
GO
SELECT * FROM #Example AS e;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 3:46 pm
SQL Kiwi (5/23/2012)
GilaMonster (5/23/2012)
Unique index on a column that allows nulls that will never contain any null valuesAhem :w00t: So, the *index* won't contain any NULLs, but the column can (which I know you know, but I was pointing out to Jared):
CREATE TABLE #Example
(
id integer NOT NULL PRIMARY KEY CLUSTERED,
somestuff varchar(250) NULL
);
GO
CREATE UNIQUE NONCLUSTERED INDEX uq1
ON #Example (somestuff)
WHERE somestuff IS NOT NULL;
GO
INSERT #Example
(id, somestuff)
VALUES
(1, 'banana'),
(2, 'apple'),
(3, NULL),
(4, NULL);
GO
SELECT * FROM #Example AS e;
Which I am sure he knows as well. 😉
May 23, 2012 at 4:05 pm
Lynn Pettis (5/23/2012)
Which I am sure he knows as well. 😉
I got the reverse impression from this statement:
Come on... That's like saying a column can accept nulls unless it can't.
Anyway, and regardless of that, there might be someone reading the thread that didn't know it, and in any case it was a tongue-in-cheek observation, hence the original 😀 I used.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 4:12 pm
SQL Kiwi (5/23/2012)
Lynn Pettis (5/23/2012)
Which I am sure he knows as well. 😉I got the reverse impression from this statement:
Come on... That's like saying a column can accept nulls unless it can't.
Anyway, and regardless of that, there might be someone reading the thread that didn't know it, and in any case it was a tongue-in-cheek observation, hence the original 😀 I used.
Yea, well, he used the 😀 after his comment as well.
But you are correct, someone else reading this thread may not know that SQL Server 2008 supports filtered indexes.
May 23, 2012 at 4:24 pm
Lynn Pettis (5/23/2012)
But you are correct, someone else reading this thread may not know that SQL Server 2008 supports filtered indexes.
Or, more to the point, that they can be used to simulate ANSI-compliant UNIQUE indexes.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 5:12 pm
SQL Kiwi (5/23/2012)
Lynn Pettis (5/23/2012)
But you are correct, someone else reading this thread may not know that SQL Server 2008 supports filtered indexes.Or, more to the point, that they can be used to simulate ANSI-compliant UNIQUE indexes.
Well, I am certainly not the "knowitall" as my name suggests, at least not literally. I'm learning a little bit more every day. My very general and basic thought was that a simple unique constraint on a column that is nullable can contain at most 1 null. I thought that was important, because, in the case of a Primary Key this is not true. Thanks for following up and clarifying my thoughts, my words on my thoughts can be lacking at times!
Jared
CE - Microsoft
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply