August 20, 2013 at 8:56 am
Hi,
My database has a bunch of tables on it that have no indexes at all on them. I want to add indexes to these tables but I am having trouble doing it and am getting messages about dependencies... As an example:
I have a table with this column:
[ACC_Name] [nvarchar](10) NULL
(the table also has some other columns but I do not think they are relevant to this...)
I want to add a primary key clustered in order to enforce the acc_name to always be unique (and to speed up query times). I have tested and as of right now the acc_name is indeed always unique (# distinct acc_name's = # rows in table).
I first tried to create the table the way I wanted it (with the PK clustered), insert * from the existing table into my new table and then delete the existing table and re-name my new one, but it won't let me delete the existing table because there are a bunch of views that point at the existing table.
Next I tried to add the primary key clustered to the existing table like this, figuring this would maybe have been a smarter way to do it anyhow:
ALTER TABLE [dbo].[ACC_STRUCTURE]
alter column [ACC_Name] [nvarchar](10) NOT NULL
ALTER TABLE [dbo].[ACC_STRUCTURE]
ADD CONSTRAINT PK_ACC_Name PRIMARY KEY CLUSTERED(ACC_Name)
...but it won't complete the first statement because of the dependencies mentioned above. Is there a way around this?
August 20, 2013 at 9:35 am
A primary key doesn’t have to be indexed, although SQL Server will create a unique clustered index on a PK by default; likewise, the clustered index doesn’t have to be on – or even include – the PK. It’s generally unwise not to have a unique index on a PK though. An FK constraint will force a scan of the whole PK column for an FK edit/insert to ensure that the inserted value exists. It’s also worth bearing in mind that “Primary Key” tells the optimiser that the column is not null and has unique values and it will use this information.
If it’s only indexes you are interested in, then you could use something like CREATE UNIQUE CLUSTERED INDEX ucx_something ON MyTable (something).
To perform your exercise the way you want to, you will have to script out the constraints, drop them, create your clustered primary key, then recreate them again.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 20, 2013 at 9:38 am
Don't make it a primary key, just make it a unique, clustered index.
CREATE UNIQUE CLUSTERED INDEX CX_ACC_Name
ON dbo.ACC_STRUCTURE ( ACC_Name )
--naturally chg WITH options as needed
WITH ( FILLFACTOR = 100
/*, ONLINE = ON*/ --use if available
/*, SORT_IN_TEMPDB = ON */ --use if tempdb has enough free space available
)
ON [PRIMARY]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 20, 2013 at 9:40 am
ChrisM@Work (8/20/2013)
A primary key doesn’t have to be indexed
I thought it did. Doesn't SQL always create an index to enforce a PK?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 20, 2013 at 9:42 am
ScottPletcher (8/20/2013)
ChrisM@Work (8/20/2013)
A primary key doesn’t have to be indexed
I thought it did. Doesn't SQL always create an index to enforce a PK?
It has to be indexed to work 😀
Yes, it creates an index by default - but you can drop it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 20, 2013 at 9:45 am
ChrisM@Work (8/20/2013)
ScottPletcher (8/20/2013)
ChrisM@Work (8/20/2013)
A primary key doesn’t have to be indexed
I thought it did. Doesn't SQL always create an index to enforce a PK?
It has to be indexed to work 😀
Yes, it creates an index by default - but you can drop it.
No, you can't, you have to drop the constraint. Perhaps other RDBMS's allow that (?), but SQL Server does not.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 20, 2013 at 9:50 am
taigovinda (8/20/2013)
Hi,My database has a bunch of tables on it that have no indexes at all on them. I want to add indexes to these tables but I am having trouble doing it and am getting messages about dependencies... As an example:
I have a table with this column:
[ACC_Name] [nvarchar](10) NULL(the table also has some other columns but I do not think they are relevant to this...)
I want to add a primary key clustered in order to enforce the acc_name to always be unique (and to speed up query times). I have tested and as of right now the acc_name is indeed always unique (# distinct acc_name's = # rows in table).
I first tried to create the table the way I wanted it (with the PK clustered), insert * from the existing table into my new table and then delete the existing table and re-name my new one, but it won't let me delete the existing table because there are a bunch of views that point at the existing table.
Next I tried to add the primary key clustered to the existing table like this, figuring this would maybe have been a smarter way to do it anyhow:
ALTER TABLE [dbo].[ACC_STRUCTURE]
alter column [ACC_Name] [nvarchar](10) NOT NULL
ALTER TABLE [dbo].[ACC_STRUCTURE]
ADD CONSTRAINT PK_ACC_Name PRIMARY KEY CLUSTERED(ACC_Name)
...but it won't complete the first statement because of the dependencies mentioned above. Is there a way around this?
You probably have schema bound views that point to this table.
The easiest way will be to drop the schema bound views, make the table changes, and create the schema bound views again.
August 20, 2013 at 9:53 am
ChrisM@Work (8/20/2013)
A primary key doesn’t have to be indexed, although SQL Server will create a unique clustered index on a PK by default; likewise, the clustered index doesn’t have to be on – or even include – the PK. It’s generally unwise not to have a unique index on a PK though. An FK constraint will force a scan of the whole PK column for an FK edit/insert to ensure that the inserted value exists. It’s also worth bearing in mind that “Primary Key” tells the optimiser that the column is not null and has unique values and it will use this information.If it’s only indexes you are interested in, then you could use something like CREATE UNIQUE CLUSTERED INDEX ucx_something ON MyTable (something).
To perform your exercise the way you want to, you will have to script out the constraints, drop them, create your clustered primary key, then recreate them again.
Thanks. I think I will probably go with the suggestion you both gave and just do "create unique clustered index..." Nonetheless, to help me understand what is going on here....
Can you explain what is meant by your last sentence above? You mean I would have to drop and recreate all the views that point at this table?
Also this table is a unique list of accounts and it is used to return the names and other pieces of info about the accounts. Views depend on it for this. I don't understand the concept of a FK at all and evidently don't understand the concept of a PK very well... Is there a reason I would want a PK instead of just a unique clustered index?
Thanks for all the help.
Tai
August 20, 2013 at 9:57 am
As info I looked at the list of dependencies and there are dozens and dozens of views that point to these tables (the account table I've asked about is one of several to which I would like to add clustered indexes) - and views that point at those views as well.
August 20, 2013 at 10:04 am
Michael Valentine Jones (8/20/2013)
taigovinda (8/20/2013)
Hi,My database has a bunch of tables on it that have no indexes at all on them. I want to add indexes to these tables but I am having trouble doing it and am getting messages about dependencies... As an example:
I have a table with this column:
[ACC_Name] [nvarchar](10) NULL(the table also has some other columns but I do not think they are relevant to this...)
I want to add a primary key clustered in order to enforce the acc_name to always be unique (and to speed up query times). I have tested and as of right now the acc_name is indeed always unique (# distinct acc_name's = # rows in table).
I first tried to create the table the way I wanted it (with the PK clustered), insert * from the existing table into my new table and then delete the existing table and re-name my new one, but it won't let me delete the existing table because there are a bunch of views that point at the existing table.
Next I tried to add the primary key clustered to the existing table like this, figuring this would maybe have been a smarter way to do it anyhow:
ALTER TABLE [dbo].[ACC_STRUCTURE]
alter column [ACC_Name] [nvarchar](10) NOT NULL
ALTER TABLE [dbo].[ACC_STRUCTURE]
ADD CONSTRAINT PK_ACC_Name PRIMARY KEY CLUSTERED(ACC_Name)
...but it won't complete the first statement because of the dependencies mentioned above. Is there a way around this?
You probably have schema bound views that point to this table.
The easiest way will be to drop the schema bound views, make the table changes, and create the schema bound views again.
That's incredibly risky. What about possible GRANTs, DENYs, etc., for those views? And other dependencies: other views based on that view which are based on ....
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 20, 2013 at 12:32 pm
In trying to understand this a bit better, I googled for 'primary key clustered' vs 'unique key clustered' ... if I understand correctly the unique key will NOT prevent a user from inserting multiple rows with the same account number, but a primary key will. Is this right? And then if I want the primary key I do indeed have to drop all of the views that depend on the table?
August 20, 2013 at 12:55 pm
taigovinda (8/20/2013)
In trying to understand this a bit better, I googled for 'primary key clustered' vs 'unique key clustered' ... if I understand correctly the unique key will NOT prevent a user from inserting multiple rows with the same account number, but a primary key will. Is this right? And then if I want the primary key I do indeed have to drop all of the views that depend on the table?
Any UNIQUE index will prevent duplicate values from being INSERTed, it doesn't have to be a PK; for example:
CREATE TABLE dbo.test1 ( c1 nvarchar(10), CONSTRAINT test1__CL UNIQUE CLUSTERED (c1))
INSERT INTO dbo.test1 VALUES(N'abc1')
INSERT INTO dbo.test1 VALUES(N'abc2')
INSERT INTO dbo.test1 VALUES(N'abc1')
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 20, 2013 at 3:05 pm
ScottPletcher (8/20/2013)
Michael Valentine Jones (8/20/2013)
taigovinda (8/20/2013)
Hi,My database has a bunch of tables on it that have no indexes at all on them. I want to add indexes to these tables but I am having trouble doing it and am getting messages about dependencies... As an example:
I have a table with this column:
[ACC_Name] [nvarchar](10) NULL(the table also has some other columns but I do not think they are relevant to this...)
I want to add a primary key clustered in order to enforce the acc_name to always be unique (and to speed up query times). I have tested and as of right now the acc_name is indeed always unique (# distinct acc_name's = # rows in table).
I first tried to create the table the way I wanted it (with the PK clustered), insert * from the existing table into my new table and then delete the existing table and re-name my new one, but it won't let me delete the existing table because there are a bunch of views that point at the existing table.
Next I tried to add the primary key clustered to the existing table like this, figuring this would maybe have been a smarter way to do it anyhow:
ALTER TABLE [dbo].[ACC_STRUCTURE]
alter column [ACC_Name] [nvarchar](10) NOT NULL
ALTER TABLE [dbo].[ACC_STRUCTURE]
ADD CONSTRAINT PK_ACC_Name PRIMARY KEY CLUSTERED(ACC_Name)
...but it won't complete the first statement because of the dependencies mentioned above. Is there a way around this?
You probably have schema bound views that point to this table.
The easiest way will be to drop the schema bound views, make the table changes, and create the schema bound views again.
That's incredibly risky. What about possible GRANTs, DENYs, etc., for those views? And other dependencies: other views based on that view which are based on ....
I don't think I would call this "incredibly risky", it's just the normal process that you have to go through when you use schema bound views (or other schema bound objects) and you have to make schema changes to the underlying tables.
I wasn't trying to give a step by step cookbook for how to do it. Obviously, you would have to do a competent job of scripting the schema bound views, including the permissions, etc., and scripting all views through the chain of dependency so that you can create them again. You might even want to test it in a test database to make sure that it all works. 😎
August 21, 2013 at 12:32 am
taigovinda (8/20/2013)
In trying to understand this a bit better, I googled for 'primary key clustered' vs 'unique key clustered' ... if I understand correctly the unique key will NOT prevent a user from inserting multiple rows with the same account number, but a primary key will. Is this right? And then if I want the primary key I do indeed have to drop all of the views that depend on the table?
Both prevent duplication but one way they differ is in their allowance and handling of NULL. A PK does not support columns that allow NULL. With a unique index some or all of the key-columns can be NULL. This is best illustrated with a simple code sample:
USE YourDatabaseName
CREATE TABLE pkTest
(
col1 INT NOT NULL,
col2 INT NOT NULL,
PRIMARY KEY CLUSTERED (col1, col2)
)
-- succeeds
INSERT INTO dbo.pkTest
(col1, col2)
VALUES (0, 0)
-- fails as duplicate
INSERT INTO dbo.pkTest
(col1, col2)
VALUES (0, 0)
-- fails because columns in a PK cannot be NULL
INSERT INTO dbo.pkTest
(col1, col2)
VALUES (0, NULL)
-- fails because columns in a PK cannot be NULL
INSERT INTO dbo.pkTest
(col1, col2)
VALUES (NULL, 0)
----------------------------------
CREATE TABLE uiTest
(
col1 INT NULL,
col2 INT NULL
)
CREATE UNIQUE INDEX uiTest1 ON uiTest (col1, col2);
-- succeeds
INSERT INTO dbo.uiTest
(col1, col2)
VALUES (0, 0)
-- fails as duplicate
INSERT INTO dbo.uiTest
(col1, col2)
VALUES (0, 0)
-- succeeds because our columns allow NULL
INSERT INTO dbo.uiTest
(col1, col2)
VALUES (0, NULL)
-- succeeds because our columns allow NULL
INSERT INTO dbo.uiTest
(col1, col2)
VALUES (NULL, 0)
-- fails as dup, NULL is treated as a value in this context
INSERT INTO dbo.uiTest
(col1, col2)
VALUES (NULL, 0)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 21, 2013 at 5:48 am
Thanks!!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply