Can I: remove clustered constraint, but keep clustering?

All,
In a database accessed 24/7, I have a table a few GB in size that is
currently clustered on a UNIQUE constraint. The data rules have changed,
and the key combination is no longer going to be unique. However, it is
still desirable to cluster on that set of columns.
On a test database, when I do
ALTER TABLE foo
DROP CONSTRAINT bar
CREATE CLUSTERED INDEX bar
ON foo (zip, zap)
it takes lots of time (over an hour). I believe this is because the DROP
CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
heap, then the CREATE INDEX restores clustering, so SQL Server re-organizes
the table according to the clustered index. This is less efficient than I
would prefer, and I do not want to take the database off-line for an hour.
Is there some SQL Server trick I do not know about? Can I leave the
constraint, but somehow disable checking? Can I safely edit the system
tables? I suspect the latter is possible, but I would not want to proceed
without expert advice.
TIA,
Scott NicholAll,
I have found info about DROP_EXISTING at, e.g.,
http://www.winnetmag.com/SQLServer/Article/ArticleID/40405/40405.html, but
this does not apply since I want to go from a constraint to an index, right?
--
Scott Nichol
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uF4D7C8TEHA.204@.TK2MSFTNGP10.phx.gbl...
> All,
> In a database accessed 24/7, I have a table a few GB in size that is
> currently clustered on a UNIQUE constraint. The data rules have changed,
> and the key combination is no longer going to be unique. However, it is
> still desirable to cluster on that set of columns.
> On a test database, when I do
> ALTER TABLE foo
> DROP CONSTRAINT bar
> CREATE CLUSTERED INDEX bar
> ON foo (zip, zap)
> it takes lots of time (over an hour). I believe this is because the DROP
> CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
> heap, then the CREATE INDEX restores clustering, so SQL Server
re-organizes
> the table according to the clustered index. This is less efficient than I
> would prefer, and I do not want to take the database off-line for an hour.
> Is there some SQL Server trick I do not know about? Can I leave the
> constraint, but somehow disable checking? Can I safely edit the system
> tables? I suspect the latter is possible, but I would not want to proceed
> without expert advice.
> TIA,
> Scott Nichol
>|||The answer is no you cannot disabled the constraint nor can you modify the system table safely to handle the change
As for what is happening, when the clustered index is dropped all non-clustered indexes are changed to having row poitners to the data instead of using the clustered index as the access path
All of them are rebuilt as soon as the clustered index is dropped
When you then recreate the clustered index the whole non-clustered thing happens again switch back from the row pointers to the data identifiers to use accessing the clustered index
I suggest script the drop and add of all your non-clustered indexes, drop them first, then drop and add your clustered, then readd your non-clustered. May still take a while thou so you will have to do an outage time frame.

Can I: remove clustered constraint, but keep clustering?

All,
In a database accessed 24/7, I have a table a few GB in size that is
currently clustered on a UNIQUE constraint. The data rules have changed,
and the key combination is no longer going to be unique. However, it is
still desirable to cluster on that set of columns.
On a test database, when I do
ALTER TABLE foo
DROP CONSTRAINT bar
CREATE CLUSTERED INDEX bar
ON foo (zip, zap)
it takes lots of time (over an hour). I believe this is because the DROP
CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
heap, then the CREATE INDEX restores clustering, so SQL Server re-organizes
the table according to the clustered index. This is less efficient than I
would prefer, and I do not want to take the database off-line for an hour.
Is there some SQL Server trick I do not know about? Can I leave the
constraint, but somehow disable checking? Can I safely edit the system
tables? I suspect the latter is possible, but I would not want to proceed
without expert advice.
TIA,
Scott Nichol
All,
I have found info about DROP_EXISTING at, e.g.,
http://www.winnetmag.com/SQLServer/A...05/40405.html, but
this does not apply since I want to go from a constraint to an index, right?
Scott Nichol
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uF4D7C8TEHA.204@.TK2MSFTNGP10.phx.gbl...
> All,
> In a database accessed 24/7, I have a table a few GB in size that is
> currently clustered on a UNIQUE constraint. The data rules have changed,
> and the key combination is no longer going to be unique. However, it is
> still desirable to cluster on that set of columns.
> On a test database, when I do
> ALTER TABLE foo
> DROP CONSTRAINT bar
> CREATE CLUSTERED INDEX bar
> ON foo (zip, zap)
> it takes lots of time (over an hour). I believe this is because the DROP
> CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
> heap, then the CREATE INDEX restores clustering, so SQL Server
re-organizes
> the table according to the clustered index. This is less efficient than I
> would prefer, and I do not want to take the database off-line for an hour.
> Is there some SQL Server trick I do not know about? Can I leave the
> constraint, but somehow disable checking? Can I safely edit the system
> tables? I suspect the latter is possible, but I would not want to proceed
> without expert advice.
> TIA,
> Scott Nichol
>
|||The answer is no you cannot disabled the constraint nor can you modify the system table safely to handle the change.
As for what is happening, when the clustered index is dropped all non-clustered indexes are changed to having row poitners to the data instead of using the clustered index as the access path.
All of them are rebuilt as soon as the clustered index is dropped.
When you then recreate the clustered index the whole non-clustered thing happens again switch back from the row pointers to the data identifiers to use accessing the clustered index.
I suggest script the drop and add of all your non-clustered indexes, drop them first, then drop and add your clustered, then readd your non-clustered. May still take a while thou so you will have to do an outage time frame.

Can I: remove clustered constraint, but keep clustering?

All,
In a database accessed 24/7, I have a table a few GB in size that is
currently clustered on a UNIQUE constraint. The data rules have changed,
and the key combination is no longer going to be unique. However, it is
still desirable to cluster on that set of columns.
On a test database, when I do
ALTER TABLE foo
DROP CONSTRAINT bar
CREATE CLUSTERED INDEX bar
ON foo (zip, zap)
it takes lots of time (over an hour). I believe this is because the DROP
CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
heap, then the CREATE INDEX restores clustering, so SQL Server re-organizes
the table according to the clustered index. This is less efficient than I
would prefer, and I do not want to take the database off-line for an hour.
Is there some SQL Server trick I do not know about? Can I leave the
constraint, but somehow disable checking? Can I safely edit the system
tables? I suspect the latter is possible, but I would not want to proceed
without expert advice.
TIA,
Scott NicholAll,
I have found info about DROP_EXISTING at, e.g.,
http://www.winnetmag.com/SQLServer/...405/40405.html, but
this does not apply since I want to go from a constraint to an index, right?
Scott Nichol
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uF4D7C8TEHA.204@.TK2MSFTNGP10.phx.gbl...
> All,
> In a database accessed 24/7, I have a table a few GB in size that is
> currently clustered on a UNIQUE constraint. The data rules have changed,
> and the key combination is no longer going to be unique. However, it is
> still desirable to cluster on that set of columns.
> On a test database, when I do
> ALTER TABLE foo
> DROP CONSTRAINT bar
> CREATE CLUSTERED INDEX bar
> ON foo (zip, zap)
> it takes lots of time (over an hour). I believe this is because the DROP
> CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
> heap, then the CREATE INDEX restores clustering, so SQL Server
re-organizes
> the table according to the clustered index. This is less efficient than I
> would prefer, and I do not want to take the database off-line for an hour.
> Is there some SQL Server trick I do not know about? Can I leave the
> constraint, but somehow disable checking? Can I safely edit the system
> tables? I suspect the latter is possible, but I would not want to proceed
> without expert advice.
> TIA,
> Scott Nichol
>|||The answer is no you cannot disabled the constraint nor can you modify the s
ystem table safely to handle the change.
As for what is happening, when the clustered index is dropped all non-cluste
red indexes are changed to having row poitners to the data instead of using
the clustered index as the access path.
All of them are rebuilt as soon as the clustered index is dropped.
When you then recreate the clustered index the whole non-clustered thing hap
pens again switch back from the row pointers to the data identifiers to use
accessing the clustered index.
I suggest script the drop and add of all your non-clustered indexes, drop th
em first, then drop and add your clustered, then readd your non-clustered. M
ay still take a while thou so you will have to do an outage time frame.

Can I use a 'Conditional CHECK' constraint ?

Hi SQL Gurus,
Using Sql2000, I have a table to maintain stock availibility, the
requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
constraint like below :
CREATE TABLE StockAvailibility
(prod_code CHAR(5) NOT NULL PRIMARY KEY,
qty_on_hand INTEGER NOT NULL,
qty_in_orders INTEGER NOT NULL,
CHECK (qty_on_hand >= qty_in_orders));
The problem is : user need more flexible approach, they want to decide at
initial implementation / runtime where they need this constraint, there is a
parameter table to store this setting.
CREATE TABLE App_Parameter
(Check_Stock bit);
The above CHECK constraint should only be run if App_Parameter.Check_Stock = 1.
How can I do this ?
TIA,
KristYou could possibly use a UDF which returns 0 or 1 and which checks against the other table. Watch
out, though, that if you don't refer to the column in the check constraint, then the optimizer might
not deem is necessary to validate the CHECK constraint. This can happen when you have CHECK
constraint which uses UDF can work against other tables. I don't think that it will be a problem in
your case, though. You could have something like:
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"tristant" <krislioe@.cbn.net.id> wrote in message news:uZGQ$YYuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> Hi SQL Gurus,
> Using Sql2000, I have a table to maintain stock availibility, the
> requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
> constraint like below :
> CREATE TABLE StockAvailibility
> (prod_code CHAR(5) NOT NULL PRIMARY KEY,
> qty_on_hand INTEGER NOT NULL,
> qty_in_orders INTEGER NOT NULL,
> CHECK (qty_on_hand >= qty_in_orders));
> The problem is : user need more flexible approach, they want to decide at
> initial implementation / runtime where they need this constraint, there is a
> parameter table to store this setting.
> CREATE TABLE App_Parameter
> (Check_Stock bit);
> The above CHECK constraint should only be run if App_Parameter.Check_Stock => 1.
> How can I do this ?
> TIA,
> Krist
>
>
>|||Tibor,
Did you intend to give an example'
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OIZOqdYuDHA.1740@.TK2MSFTNGP12.phx.gbl...
> You could possibly use a UDF which returns 0 or 1 and which checks against
the other table. Watch
> out, though, that if you don't refer to the column in the check
constraint, then the optimizer might
> not deem is necessary to validate the CHECK constraint. This can happen
when you have CHECK
> constraint which uses UDF can work against other tables. I don't think
that it will be a problem in
> your case, though. You could have something like:
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "tristant" <krislioe@.cbn.net.id> wrote in message
news:uZGQ$YYuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > Hi SQL Gurus,
> > Using Sql2000, I have a table to maintain stock availibility, the
> > requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
> > constraint like below :
> >
> > CREATE TABLE StockAvailibility
> > (prod_code CHAR(5) NOT NULL PRIMARY KEY,
> > qty_on_hand INTEGER NOT NULL,
> > qty_in_orders INTEGER NOT NULL,
> > CHECK (qty_on_hand >= qty_in_orders));
> >
> > The problem is : user need more flexible approach, they want to decide
at
> > initial implementation / runtime where they need this constraint, there
is a
> > parameter table to store this setting.
> > CREATE TABLE App_Parameter
> > (Check_Stock bit);
> >
> > The above CHECK constraint should only be run if
App_Parameter.Check_Stock => > 1.
> > How can I do this ?
> >
> > TIA,
> > Krist
> >
> >
> >
> >
> >
>|||I guess I did, but the hunger overwhelmed me and I went for lunch ;-). Below is a very stripped down
to bare essentials example:
create table t(c1 bit)
GO
create function f() returns bit as
begin
RETURN (SELECT TOP 1 c1 FROM dbo.t)
end
GO
Create table t1(c1 int)
GO
ALTER TABLE t1 ADD CONSTRAINT x CHECK(dbo.f() = 0 OR (dbo.f() = 1 AND c1 < 10))
GO
INSERT t values(0) --Means do not check
INSERT t1 values(10)
GO
UPDATE t SET c1 = 1 --Means check
INSERT t1 values(10) --Fails
UPDATE t1 SET c1 = 11 --Also fails
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SriSamp" <ssampath@.sct.co.in> wrote in message news:%23$o2JsYuDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Tibor,
> Did you intend to give an example'
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OIZOqdYuDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > You could possibly use a UDF which returns 0 or 1 and which checks against
> the other table. Watch
> > out, though, that if you don't refer to the column in the check
> constraint, then the optimizer might
> > not deem is necessary to validate the CHECK constraint. This can happen
> when you have CHECK
> > constraint which uses UDF can work against other tables. I don't think
> that it will be a problem in
> > your case, though. You could have something like:
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> news:uZGQ$YYuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > Hi SQL Gurus,
> > > Using Sql2000, I have a table to maintain stock availibility, the
> > > requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
> > > constraint like below :
> > >
> > > CREATE TABLE StockAvailibility
> > > (prod_code CHAR(5) NOT NULL PRIMARY KEY,
> > > qty_on_hand INTEGER NOT NULL,
> > > qty_in_orders INTEGER NOT NULL,
> > > CHECK (qty_on_hand >= qty_in_orders));
> > >
> > > The problem is : user need more flexible approach, they want to decide
> at
> > > initial implementation / runtime where they need this constraint, there
> is a
> > > parameter table to store this setting.
> > > CREATE TABLE App_Parameter
> > > (Check_Stock bit);
> > >
> > > The above CHECK constraint should only be run if
> App_Parameter.Check_Stock => > > 1.
> > > How can I do this ?
> > >
> > > TIA,
> > > Krist
> > >
> > >
> > >
> > >
> > >
> >
> >
>

can I update based on a join result with a calculation

My table design is
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
and I also have data files of the same structure. My goal is to import the
data files in order to update the Total table where the hKey matches.
I could
A. Read each record (4.5 million) and send to a stored procedure which does
a look up and add to the total field.
or
B. Import the data file into a temp table, join the HashKeyTotals table with
some sort of calc'd field summing the two totals and put that into a temp
table to be loaded back into the HashKeyTotals table.
I'm not even sure if B is possible but just a crazy idea. Will it work and
will it be that much faster than plan A? The HashKeyTotals table can have up
to 25 million records and I'd be loading a data file with 5 million records
in it each day.
Any thoughts?
ThanksSo, you're incrementing the Total by what you get in the import table for
the corresponding key? If so, try:
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [PK_HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [HashKeyTotals2] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [PK_HashKeyTotals2] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert HashKeyTotals values (1, 10)
insert HashKeyTotals values (2, 20)
insert HashKeyTotals2 values (1, 5)
insert HashKeyTotals2 values (2, 15)
go
update h
set
Total = h.Total + h2.Total
from
HashKeyTotals h
join HashKeyTotals2 h2 on h2.hKey = h.hKey
go
select * from HashKeyTotals
go
drop table HashKeyTotals, HashKeyTotals2
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike" <Mike@.mike.com> wrote in message
news:uuIuedUcGHA.3936@.TK2MSFTNGP05.phx.gbl...
My table design is
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
and I also have data files of the same structure. My goal is to import the
data files in order to update the Total table where the hKey matches.
I could
A. Read each record (4.5 million) and send to a stored procedure which does
a look up and add to the total field.
or
B. Import the data file into a temp table, join the HashKeyTotals table with
some sort of calc'd field summing the two totals and put that into a temp
table to be loaded back into the HashKeyTotals table.
I'm not even sure if B is possible but just a crazy idea. Will it work and
will it be that much faster than plan A? The HashKeyTotals table can have up
to 25 million records and I'd be loading a data file with 5 million records
in it each day.
Any thoughts?
Thanks|||Yes thats exactly what I need. Thank you very much.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23LBk8iVcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> So, you're incrementing the Total by what you get in the import table for
> the corresponding key? If so, try:
> CREATE TABLE [HashKeyTotals] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [PK_HashKeyTotals] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [HashKeyTotals2] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [PK_HashKeyTotals2] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert HashKeyTotals values (1, 10)
> insert HashKeyTotals values (2, 20)
> insert HashKeyTotals2 values (1, 5)
> insert HashKeyTotals2 values (2, 15)
> go
> update h
> set
> Total = h.Total + h2.Total
> from
> HashKeyTotals h
> join HashKeyTotals2 h2 on h2.hKey = h.hKey
> go
> select * from HashKeyTotals
> go
> drop table HashKeyTotals, HashKeyTotals2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Mike" <Mike@.mike.com> wrote in message
> news:uuIuedUcGHA.3936@.TK2MSFTNGP05.phx.gbl...
> My table design is
> CREATE TABLE [HashKeyTotals] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> and I also have data files of the same structure. My goal is to import the
> data files in order to update the Total table where the hKey matches.
> I could
> A. Read each record (4.5 million) and send to a stored procedure which
> does
> a look up and add to the total field.
> or
> B. Import the data file into a temp table, join the HashKeyTotals table
> with
> some sort of calc'd field summing the two totals and put that into a temp
> table to be loaded back into the HashKeyTotals table.
> I'm not even sure if B is possible but just a crazy idea. Will it work and
> will it be that much faster than plan A? The HashKeyTotals table can have
> up
> to 25 million records and I'd be loading a data file with 5 million
> records
> in it each day.
> Any thoughts?
> Thanks
>