relationships as a table

I have the tables tblEmployees and tblProjects.
tblProjects has one column called Percentagetime, to hold the percentage of
time by an employee for a project.

My Question : Why should we introduce a 3rd table called
tblProjectsAndEmployees?
tblProjectsAndEmployees will have
1)EmployeeNumber(PrimaryKey in tblEmployees)
2)ProjectNumber(Primary key in tblProjects)
3)PercentageTime

Having only the tblEmployees and tblProjects is not sufficient?

Kind Regards,
Vinodh Kumar PVinodh Kumar P (vinodh@.here.com) writes:
> I have the tables tblEmployees and tblProjects. tblProjects has one
> column called Percentagetime, to hold the percentage of time by an
> employee for a project.
> My Question : Why should we introduce a 3rd table called
> tblProjectsAndEmployees?
> tblProjectsAndEmployees will have
> 1)EmployeeNumber(PrimaryKey in tblEmployees)
> 2)ProjectNumber(Primary key in tblProjects)
> 3)PercentageTime
> Having only the tblEmployees and tblProjects is not sufficient?

As long as an employee only works on one single project it is. Or a project
never can be staffed by more than one employee.

But in real life an employee works on more than project, and project have
several members. Then you have a many-to-many relation, and the typical
way to sort that out is to introduce a linking table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Relationship problem

Hi All... Two of my tables are:

Users - primary key is UserId, an int with identity turned on.

Messages - has a column named UserId that references the same in Users.

I'm using Visual Studio 2005 against a SQL 2005 database.

Using both the diagram tool and table data, I'm trying to set up the relationship implied above and am getting the following error:

Users table saved successfully.

Messages table

- unable to create relationship 'FK_Messages_UserId'.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 'FK_Messages_UserId'. The conflict occurred in database 'XXXX', table 'dbo.Users', column 'UserId'.

I've done several other similar relationships without incident. But this one (and one or two others) refuse to work. I'm a bit of newbie with these rascals, so that doesnt help much... Any ideas what this things trying to tell me? Thanks! -- Curt

You might already have entries in your child table which have no parent entry in the parent table.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens... Thanks for the reply. You threw me a little at first on your use of "parent" and "child", but yeah you nailed it. To try and tie those terms to the tables in my original post, I had some records in Messages (child) that referenced a primary key that did not exist in Users (parent). Geez, these FKs really help us keep a clean house, dont they... Thanks again!! Curt

relationship between xml in xml data type column

How would i express a relation between different kinds of XML segments
in the same table with an XML datatype column.
Further, can i do this using dataset designer? Do you know where I can
get more information on how to use the dataset designer, or how to
write xsd for sql server 2005?
thanks,
Joyce
If the relationship is between elements in the same instance (i.e. same "cell"
in your table) then you can write an XML schema and make use of types xsd:ID
anjd xsd:IDREF. If the relationship is between XML fragments stored in different
rows or columns then this won't work.
Here's a link to the Books On Line page about creating an XML schema collection:
http://msdn2.microsoft.com/en-us/library/ms176009.aspx
If you can post more information on what you're trying to accomplish I'd be glad
to help.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169140712.945912.315860@.q2g2000cwa.googlegro ups.com...
> How would i express a relation between different kinds of XML segments
> in the same table with an XML datatype column.
> Further, can i do this using dataset designer? Do you know where I can
> get more information on how to use the dataset designer, or how to
> write xsd for sql server 2005?
> thanks,
> Joyce
>
|||hi Denis
This is a helpful start. If I would like to relate between elements in
different row, but in the same column, is there a way to do that?
I'm looking at using Visual Studio dataset designer, and writing out
some queries that can perhaps write the schema out for me.
thanks,
Joyce
Denis Ruckebusch [MSFT] wrote:[vbcol=seagreen]
> If the relationship is between elements in the same instance (i.e. same "cell"
> in your table) then you can write an XML schema and make use of types xsd:ID
> anjd xsd:IDREF. If the relationship is between XML fragments stored in different
> rows or columns then this won't work.
> Here's a link to the Books On Line page about creating an XML schema collection:
> http://msdn2.microsoft.com/en-us/library/ms176009.aspx
> If you can post more information on what you're trying to accomplish I'd be glad
> to help.
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "joyce chan" <joyceschan@.fastmail.fm> wrote in message
> news:1169140712.945912.315860@.q2g2000cwa.googlegro ups.com...
|||I'm not familiar with the dataset designer.
I'd look at using relational mechanisms such as id columns to express the
dependency.
Denis Ruckebusch
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169218663.655366.182930@.v45g2000cwv.googlegr oups.com...
> hi Denis
> This is a helpful start. If I would like to relate between elements in
> different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>
|||Hello joyce,
No thats not possible. Schema validation is only on an individual cell.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> hi Denis
> This is a helpful start. If I would like to relate between elements
> in different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:

relationship between xml in xml data type column

How would i express a relation between different kinds of XML segments
in the same table with an XML datatype column.
Further, can i do this using dataset designer? Do you know where I can
get more information on how to use the dataset designer, or how to
write xsd for sql server 2005?
thanks,
JoyceIf the relationship is between elements in the same instance (i.e. same "cel
l"
in your table) then you can write an XML schema and make use of types xsd:I
D
anjd xsd:IDREF. If the relationship is between XML fragments stored in diffe
rent
rows or columns then this won't work.
Here's a link to the Books On Line page about creating an XML schema collect
ion:
http://msdn2.microsoft.com/en-us/library/ms176009.aspx
If you can post more information on what you're trying to accomplish I'd be
glad
to help.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169140712.945912.315860@.q2g2000cwa.googlegroups.com...
> How would i express a relation between different kinds of XML segments
> in the same table with an XML datatype column.
> Further, can i do this using dataset designer? Do you know where I can
> get more information on how to use the dataset designer, or how to
> write xsd for sql server 2005?
> thanks,
> Joyce
>|||hi Denis
This is a helpful start. If I would like to relate between elements in
different row, but in the same column, is there a way to do that?
I'm looking at using Visual Studio dataset designer, and writing out
some queries that can perhaps write the schema out for me.
thanks,
Joyce
Denis Ruckebusch [MSFT] wrote:
> If the relationship is between elements in the same instance (i.e. same "c
ell"
> in your table) then you can write an XML schema and make use of types xsd
:ID
> anjd xsd:IDREF. If the relationship is between XML fragments stored in dif
ferent
> rows or columns then this won't work.
> Here's a link to the Books On Line page about creating an XML schema colle
ction:
> http://msdn2.microsoft.com/en-us/library/ms176009.aspx
> If you can post more information on what you're trying to accomplish I'd b
e glad
> to help.
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "joyce chan" <joyceschan@.fastmail.fm> wrote in message
> news:1169140712.945912.315860@.q2g2000cwa.googlegroups.com...|||I'm not familiar with the dataset designer.
I'd look at using relational mechanisms such as id columns to express the
dependency.
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169218663.655366.182930@.v45g2000cwv.googlegroups.com...
> hi Denis
> This is a helpful start. If I would like to relate between elements in
> different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>|||Hello joyce,
No thats not possible. Schema validation is only on an individual cell.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> hi Denis
> This is a helpful start. If I would like to relate between elements
> in different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>sql

Relationship Annotation and legal column names

Hi
I wonder if anyone can help answer my question. Are you able to use
column names with dots in within a relationship annotation in SQL XML
mapping schemas. For example:
<sql:relationship
name="RelationshipName"
parent="[Table.Name]"
parent-key="[Primary.ID]"
child="[Table.Name.2]"
child-key="[Foreign.Key]" />
You appear to be able to use "." notation for table names but not field
names, is this a bug? Does anybody know of a workaround?
nrudd
---
nrudd's Profile: http://www.dbtalk.net/m183
View this thread: http://www.dbtalk.net/t300313I am not sure anymore whether we allow qualified names. But please be aware
that by putting the name inbetween [] you are making it a single name...
Try Table.Col without the [].
Best regards
Michael
"nrudd" <nrudd.26gwkz@.no-mx.forums.yourdomain.com.au> wrote in message
news:nrudd.26gwkz@.no-mx.forums.yourdomain.com.au...
> Hi
> I wonder if anyone can help answer my question. Are you able to use
> column names with dots in within a relationship annotation in SQL XML
> mapping schemas. For example:
> <sql:relationship
> name="RelationshipName"
> parent="[Table.Name]"
> parent-key="[Primary.ID]"
> child="[Table.Name.2]"
> child-key="[Foreign.Key]" />
> You appear to be able to use "." notation for table names but not field
> names, is this a bug? Does anybody know of a workaround?
>
> --
> nrudd
> ---
> nrudd's Profile: http://www.dbtalk.net/m183
> View this thread: http://www.dbtalk.net/t300313
>

Relational databases

I have a membership database and a profile database with a userid column in both. The foreign key is in the profile database where i want a persons profile details to go like location,occupation and what not. How do I make it so when a new user registers a userId is created in the profile database that matched the userid in the membership database so i can query out profile details based on a registered members userid?

There's a couple of ways -

- Add a separate column for the membership id to the profile.

- Make the UserID in the Profile non-auto generated and set it when you create the record as under

INSERT INTO MembershipTable .....DECLARE @.UserID INTSET @.UserID = SCOPE_IDENTITY() -- gets the identity of the lastnew record-- create the profile recordINSERT INTO ProfileTable (UserId, .... VALUES(@.UserID, ....

relate two tables based on an 'active' column

So I have a question and I will give a related example. Say I have a list of records in a table:

Product Cost Active

hat 1.00 false

coat 2.00 true

I have a DataGridView that shows purchases from a purchase table: Date, ProductID, Buyer

Is there a way to have a DataGridViewComboBoxColumn show only active items so that a user can only select active items in the puchases datagridview, but when they click a purchase from long ago when an item was active they have the ability to pick that old(unactive) item or a new one for an update.

Basically if I just have the ComboBoxColumn bound to a dataset with just active items, the datagrid will fail to load cause old purchases are not in its item list, but I also don't want all the items to be in the list.

So... you want a union of active items and inactive previously-bought items?

select *
from Products
where Active = 'true' --Should you be using a bit/tinyint/smallint here?
UNION ALL
select distinct p.*
from
Purchases pu
join

Products p
on p.ProductID = pu.ProductID
where p.Active = 'false'
and pu.Buyer = @.Buyer

Maybe?

Rob|||Hi,

well this is more a front-end related question. You can capture the SelectedIndexchangedEvent of the Combobox and select upon the value or the column where the row is marked as new / old if it can be choosen or not. Depending on your control you could also use another color for each combobox value (and therefore hrey out the non selectable). If you want to filter the combobox values in the first place without showing them to the user, you might want to hook into the ItemDatabound event. That highly depends on your used language / controls (which you did not mentioned)

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I got it hammered out. I set the combobox data source on a filtered binding source when the OnBeginEdit event fired. The filtered string simply included and "or combobox.Value = sourceTable.ID". Then on the OnEndEdit event, I set the combobox datasource back to an unfiltered view of the items.

Reinserting certain records of a table in to the same table

Hi

How can I reinsert certain records of a table in to the same table and change only one column? This is story : I have a table that user enters daily records in it , most of these record are same as records of yesterday. So I want to reinsert them and let user to edit them if needed.

I have a other question too , there are 2 tables that are related to table above and I have to reinsert the related records too for example there are 4 records in second table that related to the fist row of the first table .

I can do all of it with asp.net using loops and connecting and disconnecting to database per insert but it doesn't seems to be so wisely , I rather do it with a stored procedure .

Thank you in advance

This ought to work:

insert into table (...columns...) select ...columns... from table where ....

Assumes you have an identity column that is NOT in the list of fields (ie, let sql server handle that for you)

I don't understand your second question, ie, how is it different than the first?

|||

Thank you David ,

Abut my first question, there is a column that I have to change it's value , It concerns abut date of report how do I handle that ?I gues it must be something like this :
insert into table (...columns...) ,[TodayReportID] select ...columns... from table where .... ? @.TodayReportIDFor better understanding see the example of question 2My second question :Assume these are table above columns :
? [Table1ID] ,[ column1], [ column2], [TodayReportID]
1 AAAA BBBB 1
2 CCCC DDDD 1
3 EEEE FFFF 1
And there is an other table I name it Table 2 and [Table1ID] is a foreign key in it related to table 1 so all records below are related to 2^nd row of table one .
? [Table2ID] ,[Table1ID], [ column2]
1 2 GGG
2 2 HHH
3 2 KKK
Now assume I reinsert second row of table 1 it will be something like this :
? [Table1ID] ,[ column1], [ column2], [TodayReportID]
…
4 CCCC DDDD 2
Now I have to reinsert related row of Table2 too :
? [Table2ID] ,[Table1ID], [ column2]
4 4 GGG
5 4 HHH
6 4 KKK

Thank very much

|||

u can use trigger ... while inserting and data to a certain table it will then automatically call your trigger and update your second table.

/* */

in your table use data time field to separate your distinct date data.

|||Thank you ,But I still didn't get my answer of first question about extra column that I have to insert it manually (by a parameter ). And I'm not familiar with trigger yet so can you give a an example how to use a trigger to perform this task ? Thank you again

|||I don't think triggers was the best option, I need something like loop so I can select some records and from Table1 and reinsert records for each row.|||

If you want to change the date when the record is created it's easy, just supply the new value in the select list that's getting the original data, eg, this example puts current date into column 3

insert into table1 (col1, col2, col3, col4)
select col1, col2,getdate(), col4) from table1

I don't see any use for triggers in this example, btw

|||

thank you ,

I did this and it works :

Create PROCEDURE [dbo].[proc_DailyReport_CopyInformation](@.ReportIDDECIMAL,@.ReportNewDECIMAL)ASBEGINSET NOCOUNT ONDECLARE @.ErrintINSERT INTO DailyReport_Activity (ReportID,ActivityDesc,hajm,vahedID,Tozihat,WBS) SELECT ReportID=(@.ReportNew),ActivityDesc,hajm,vahedID,Tozihat,WBS From DailyReport_Activity where ReportID = @.ReportIDSET @.Err = @.@.ErrorRETURN @.ErrEND

but I didn't find out my secont question yet please help .

|||

I don't really understand your second question -- can you elaborate? How is it different than your first question, aren't you still just inerting records based on existing records?

|||The problem is I don't know how can I make loop so based on the records I reinserted in the first table I reinsert records in the second table .

Or I need reinsert related records to second table while I'm inserting in the first table .

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables?
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***
Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/de...es_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default...b;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default...b;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/d...>
_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/defaul...kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as
maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you sho
uld use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.
gbl...
> If you're using a non-named instance and are running SP4, you can use a -s
upportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named
instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/defaul...kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job wit
h two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you t
he script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2M
SFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.developersdex.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>>I need to reindex a table with a computed column. The column is not
>> included in any indexes, but when I run the DBCC it crashes with the
>> following error:
>> DBCC failed because the following SET options have incorrect settings:
>> 'QUOTED_IDENTIFIER'.
>> Any ideas on how I can reindex these tables'
>> Thanks!
>> Richard
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>

reindexing table with blob data type

Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advanceUnfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> > Just to clarify, what Tom means is to bcp out/in the data, not to run
DBCC
> > DBREINDEX on the table.
> >
> > We have this fixed in SQL Server 2005.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > > Unfortunately, the only way to defrag tables with BLOB columns is to
> > rebuild
> > > the table.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinpub.com
> > > .
> > > "james" <kush@.brandes.com> wrote in message
> > > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > > Hi! Is there a way to defrag image data type column in a table? I use
> dbcc
> > > dbreindex on the table itself during maintenance period, but I don't
> know
> > if
> > > that is sufficient to defrag those pages which actually stores image
> data
> > > type. We use third party application and they designed to put some
> > > transaction information on image data type column. Now we are
> experiencing
> > > gradual degradation on performance and wondering if it is related to
> image
> > > data type and its growth.
> > >
> > > Sql server 2000 sp3a
> > >
> > > thanks in advance
> > >
> > >
> >
> >
>|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>

reindexing table with blob data type

Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance
Unfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance
|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>
|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
DBCC
> rights.
> dbcc
> know
> data
> experiencing
> image
>
|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas

"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>

reindexing table with blob data type

Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advanceUnfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
DBCC[vbcol=seagreen]
> rights.
> dbcc
> know
> data
> experiencing
> image
>|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>

Reindexing

I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>

Reindexing

I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John
Have you any other indexes on the table? If so, drop them first and add
them back last.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John
|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>
|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>
|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>
|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>

Reindexing

I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>

Regular expressions in Sql Server?

Hi,

I'm using MS SQL Server 2000 and am trying to execute a select where I want a column to match this regular expression:

((\w)*\|)*$KEY(\|(\w)*)*

as would be defined in Perl (btw, the "|" is a pipe, not an L :))

$KEY would be subsituted by some text I'm querying for.

I tried using LIKE with "[(%|)[]]$KEY[[](|%)]", eg:

select * from mytable m
where m.a like '[(%|)[]]$KEY[[](|%)]'

but it didn't work (again, the $KEY is replaced with text when I'm executing. I just have it here for an example). Is there any way I could get around this problem? I would really appreciate any help.

Thanksthe best you will do is "%|$Key|%", ie, a string that starts with something, has a pipe char, the string you are looking for, a pipe char, ending with something.

I believe "\|" in perl is how you specify a literal "|".

Regular expressions are not supported in TSQL! There are some simple expressions but not what a perl or python programmer is used to.