I would like to alternate the background color for rows in a table. Is there
a simple way to do this?
Basically I got it working by returning a rank column that identified the
row number. I then used the following code in the TableRow BackgroundColor
property:
IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
Seems like there must be an easier way then having to return a rank column
in the dataset associated with the table.Greg,
That's pretty much the only way I know to do it. Although, I used a
different formula then you did. Here's what I stuck in my Background Color
property box:
=iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
OldLace was the color I chose because I didn't want something hard on the
eyes. The Nothing leaves it as Transparent, which isn't really a color, but
doesn't force it to be another color. You could just as easily enter another
one of the available colors in the False part.
Does this help any?
Catadmin
"Greg Larsen" wrote:
> I would like to alternate the background color for rows in a table. Is there
> a simple way to do this?
> Basically I got it working by returning a rank column that identified the
> row number. I then used the following code in the TableRow BackgroundColor
> property:
> IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> Seems like there must be an easier way then having to return a rank column
> in the dataset associated with the table.|||Yes this does help. By using RowNumber function you don't need to generate a
row number(or rank as I called it) as part of the dataset being returned.
Thank you that is exactly want I needed.
"Catadmin" wrote:
> Greg,
> That's pretty much the only way I know to do it. Although, I used a
> different formula then you did. Here's what I stuck in my Background Color
> property box:
> =iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
> OldLace was the color I chose because I didn't want something hard on the
> eyes. The Nothing leaves it as Transparent, which isn't really a color, but
> doesn't force it to be another color. You could just as easily enter another
> one of the available colors in the False part.
> Does this help any?
> Catadmin
> "Greg Larsen" wrote:
> > I would like to alternate the background color for rows in a table. Is there
> > a simple way to do this?
> >
> > Basically I got it working by returning a rank column that identified the
> > row number. I then used the following code in the TableRow BackgroundColor
> > property:
> >
> > IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> >
> > Seems like there must be an easier way then having to return a rank column
> > in the dataset associated with the table.
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Thursday, March 29, 2012
Alternate ways of deleting records - without logging
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>
Alternate the background color of rows
Hi!
I 'm working whit a matrix.
I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:
Please, if somebody can help me
Thanks
hey there
there is something on this page that may help you
http://blogs.msdn.com/chrishays/
|||Specifically you want to read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
-- Robert
|||Robert, I've used the suggestions in Chris Hay's example. However, it only gets me 90% to where I need.
I cannot get the subtotal background color to alternate. Do you know a way to get this functionality. Kind of pointless and ugly to have everything alternate except the last column or row.
Alternate the background color of rows
Hi!
I 'm working whit a matrix.
I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:
Please, if somebody can help me
Thanks
Please read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
-- Robert
Alternate the background color of rows
Hi!
I 'm working whit a matrix.
I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:
Please, if somebody can help me
Thanks
hey there
there is something on this page that may help you
http://blogs.msdn.com/chrishays/
|||Specifically you want to read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
-- Robert
|||Robert, I've used the suggestions in Chris Hay's example. However, it only gets me 90% to where I need.
I cannot get the subtotal background color to alternate. Do you know a way to get this functionality. Kind of pointless and ugly to have everything alternate except the last column or row.
Alternate Rows in Matrix
Hi All,
I need the alternate bg colors of the rows in the matrix to be
white smoke and grey.
when i used the following expression it throws a error.
=iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
The background color expression for the textbox â'ProfCountâ' has a scope
parameter that is not valid for RunningValue, RowNumber or Previous. The
scope parameter must be set to a string constant that is equal to the name of
a containing group within the matrix â'matrix1â'.
What i need to do get the alternate coloring in matrix,
Thanks in advance....I beleive I have posted an example called Matrix.Greenbar on
www.msbicentral.com
Look under Downloads, reporting services, RDL... There are several Matrix
examples there which will probably help you get started.
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B95FDE7C-7DAA-4327-AA61-4C1045DDEABA@.microsoft.com...
> Hi All,
> I need the alternate bg colors of the rows in the matrix to be
> white smoke and grey.
> when i used the following expression it throws a error.
> =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> The background color expression for the textbox 'ProfCount' has a scope
> parameter that is not valid for RunningValue, RowNumber or Previous. The
> scope parameter must be set to a string constant that is equal to the name
of
> a containing group within the matrix 'matrix1'.
> What i need to do get the alternate coloring in matrix,
> Thanks in advance....sql
I need the alternate bg colors of the rows in the matrix to be
white smoke and grey.
when i used the following expression it throws a error.
=iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
The background color expression for the textbox â'ProfCountâ' has a scope
parameter that is not valid for RunningValue, RowNumber or Previous. The
scope parameter must be set to a string constant that is equal to the name of
a containing group within the matrix â'matrix1â'.
What i need to do get the alternate coloring in matrix,
Thanks in advance....I beleive I have posted an example called Matrix.Greenbar on
www.msbicentral.com
Look under Downloads, reporting services, RDL... There are several Matrix
examples there which will probably help you get started.
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B95FDE7C-7DAA-4327-AA61-4C1045DDEABA@.microsoft.com...
> Hi All,
> I need the alternate bg colors of the rows in the matrix to be
> white smoke and grey.
> when i used the following expression it throws a error.
> =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> The background color expression for the textbox 'ProfCount' has a scope
> parameter that is not valid for RunningValue, RowNumber or Previous. The
> scope parameter must be set to a string constant that is equal to the name
of
> a containing group within the matrix 'matrix1'.
> What i need to do get the alternate coloring in matrix,
> Thanks in advance....sql
Alternate rows from different table
Dear All,
I have a requirement in which I have to display a row from one
table & the corresponding row from the another table. e.g. say there
are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
corresponding record in T2 as R1' then the display would come as
R1 /* Data from Table 1 */
R1' /* Data from Table 2 */
R2
R2'
& so on.....
This is possible by manipulating the resultset in a program. But I
would like to know if it is possible in the SQL query.
Thanks & Regards,
Praveenpkb wrote:
> Dear All,
> I have a requirement in which I have to display a row from one
> table & the corresponding row from the another table. e.g. say there
> are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
> corresponding record in T2 as R1' then the display would come as
> R1 /* Data from Table 1 */
> R1' /* Data from Table 2 */
> R2
> R2'
> & so on.....
> This is possible by manipulating the resultset in a program. But I
> would like to know if it is possible in the SQL query.
> Thanks & Regards,
> Praveen
Looks like a UNION to me. Assuming r is the common column that
determines R1, R2, etc, try:
SELECT r, col1, col2, ...
FROM
(SELECT r, 1 AS tbl, col1, col2, ...
FROM tbl1
UNION ALL
SELECT r, 2 AS tbl, col1, col2, ...
FROM tbl2) AS T
ORDER BY r, tbl ;
If that's not what you wanted then my signature explains how to post
better questions so that you can get better answers. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"pkb" <praveen.bhushan@.gmail.com> wrote in message
news:1138803314.265069.114980@.g49g2000cwa.googlegroups.com...
> Dear All,
> I have a requirement in which I have to display a row from one
> table & the corresponding row from the another table. e.g. say there
> are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
> corresponding record in T2 as R1' then the display would come as
> R1 /* Data from Table 1 */
> R1' /* Data from Table 2 */
> R2
> R2'
> & so on.....
> This is possible by manipulating the resultset in a program. But I
> would like to know if it is possible in the SQL query.
> Thanks & Regards,
> Praveen
You could:
select 1 AS rank, ... from T1 where ...
union
select 2 AS rank, ... from T2 where ...
Order by (you will have to add the corresponding columns, whatever that is),
rank
Sorry, can't give you anything more detailed without your table structure.|||Hi David, Raymond,
Thanks for your quick replies. The only problem in the above
solution will come when there is a record which is present in
one of the tables. Actually I wanted to make pairs from the two tables.
Well I have got the idea.
Regards,
Praveen
I have a requirement in which I have to display a row from one
table & the corresponding row from the another table. e.g. say there
are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
corresponding record in T2 as R1' then the display would come as
R1 /* Data from Table 1 */
R1' /* Data from Table 2 */
R2
R2'
& so on.....
This is possible by manipulating the resultset in a program. But I
would like to know if it is possible in the SQL query.
Thanks & Regards,
Praveenpkb wrote:
> Dear All,
> I have a requirement in which I have to display a row from one
> table & the corresponding row from the another table. e.g. say there
> are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
> corresponding record in T2 as R1' then the display would come as
> R1 /* Data from Table 1 */
> R1' /* Data from Table 2 */
> R2
> R2'
> & so on.....
> This is possible by manipulating the resultset in a program. But I
> would like to know if it is possible in the SQL query.
> Thanks & Regards,
> Praveen
Looks like a UNION to me. Assuming r is the common column that
determines R1, R2, etc, try:
SELECT r, col1, col2, ...
FROM
(SELECT r, 1 AS tbl, col1, col2, ...
FROM tbl1
UNION ALL
SELECT r, 2 AS tbl, col1, col2, ...
FROM tbl2) AS T
ORDER BY r, tbl ;
If that's not what you wanted then my signature explains how to post
better questions so that you can get better answers. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"pkb" <praveen.bhushan@.gmail.com> wrote in message
news:1138803314.265069.114980@.g49g2000cwa.googlegroups.com...
> Dear All,
> I have a requirement in which I have to display a row from one
> table & the corresponding row from the another table. e.g. say there
> are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
> corresponding record in T2 as R1' then the display would come as
> R1 /* Data from Table 1 */
> R1' /* Data from Table 2 */
> R2
> R2'
> & so on.....
> This is possible by manipulating the resultset in a program. But I
> would like to know if it is possible in the SQL query.
> Thanks & Regards,
> Praveen
You could:
select 1 AS rank, ... from T1 where ...
union
select 2 AS rank, ... from T2 where ...
Order by (you will have to add the corresponding columns, whatever that is),
rank
Sorry, can't give you anything more detailed without your table structure.|||Hi David, Raymond,
Thanks for your quick replies. The only problem in the above
solution will come when there is a record which is present in
one of the tables. Actually I wanted to make pairs from the two tables.
Well I have got the idea.
Regards,
Praveen
Alternate row in diffrerent colors
Hi,
I wish to change the back colour for alternate rows in a report (say Table).
I haven't found anything that tells me the current-rownum. The RowNum seems
more like a row-count. Please help!!
Thanks in advance,
Regards,
DattaTo have alternating colours in a table, use this formula
=iif(RowNumber(Nothing) Mod 2, "Gainsboro", "White")
A slightly more refined version can be found in Adlai Maschiach's blog:
http://dotnetjunkies.com/WebLog/adlaim/archive/2004/08/29/23594.aspx
This is the way to do it if you want to control the alternating colours in a
table with groups, as the first one will not take groupings into
consideration.
To do it in a matrix, you need to do it like Chris Hays writes
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
Kaisa M. Lindahl Lervik
"Datta" <Datta@.discussions.microsoft.com> wrote in message
news:5FC4A7F0-B950-4F0C-A726-D383AC381960@.microsoft.com...
> Hi,
> I wish to change the back colour for alternate rows in a report (say
> Table).
> I haven't found anything that tells me the current-rownum. The RowNum
> seems
> more like a row-count. Please help!!
> Thanks in advance,
> Regards,
> Datta
I wish to change the back colour for alternate rows in a report (say Table).
I haven't found anything that tells me the current-rownum. The RowNum seems
more like a row-count. Please help!!
Thanks in advance,
Regards,
DattaTo have alternating colours in a table, use this formula
=iif(RowNumber(Nothing) Mod 2, "Gainsboro", "White")
A slightly more refined version can be found in Adlai Maschiach's blog:
http://dotnetjunkies.com/WebLog/adlaim/archive/2004/08/29/23594.aspx
This is the way to do it if you want to control the alternating colours in a
table with groups, as the first one will not take groupings into
consideration.
To do it in a matrix, you need to do it like Chris Hays writes
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
Kaisa M. Lindahl Lervik
"Datta" <Datta@.discussions.microsoft.com> wrote in message
news:5FC4A7F0-B950-4F0C-A726-D383AC381960@.microsoft.com...
> Hi,
> I wish to change the back colour for alternate rows in a report (say
> Table).
> I haven't found anything that tells me the current-rownum. The RowNum
> seems
> more like a row-count. Please help!!
> Thanks in advance,
> Regards,
> Datta
Alternate row colors by group
I have a report in which the results are grouped. For example, the report
returnd 25 rows, the first 5 rows are one group (lets say all 'A's, the
second 5 rows are another group (lets say all 'B's), etc...
Can I add conditional formatting to alternate the backgroup color based on
groups?
I know I can alternate row colors using:
=iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
But I want to alternate by group. Is that possible and if so, how?
Thanks,I had a similar requirement recently but could find nothing in help or on the
net. I wrote this embedded code which works for me but I'm curious if there's
a simpler way.
Add this to the report's embedded code window:
Public GroupRowCounter As Integer = 0
Function IncrementCounter() As Integer
GroupRowCounter += 1
Return GroupRowCounter
End Function
Function GetCounter() As Integer
Return GroupRowCounter
End Function
Right click the header row you want the alternate coloring in and select
Insert Row Above. In the new, topmost header row set the Hidden property to
True and add this to one of the cells:
=Code.IncrementCounter
For the header/detail/footer portions of the group that will be visible, add
this to the BackgroundColor property to alternate the row (group) colors:
=IIF(Code.GetCounter Mod 2, "WhiteSmoke", "White")
"BillTWD" wrote:
> I have a report in which the results are grouped. For example, the report
> returnd 25 rows, the first 5 rows are one group (lets say all 'A's, the
> second 5 rows are another group (lets say all 'B's), etc...
> Can I add conditional formatting to alternate the backgroup color based on
> groups?
> I know I can alternate row colors using:
> =iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
> But I want to alternate by group. Is that possible and if so, how?
> Thanks,|||If you're grouping on Country, this is the code to use.
=Iif(RunningValue(Fields!Country.Value,CountDistinct, Nothing) Mod 2,
"LightGreen", "Cornsilk")
Mike Glaser
"BillTWD" wrote:
> I have a report in which the results are grouped. For example, the report
> returnd 25 rows, the first 5 rows are one group (lets say all 'A's, the
> second 5 rows are another group (lets say all 'B's), etc...
> Can I add conditional formatting to alternate the backgroup color based on
> groups?
> I know I can alternate row colors using:
> =iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
> But I want to alternate by group. Is that possible and if so, how?
> Thanks,
returnd 25 rows, the first 5 rows are one group (lets say all 'A's, the
second 5 rows are another group (lets say all 'B's), etc...
Can I add conditional formatting to alternate the backgroup color based on
groups?
I know I can alternate row colors using:
=iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
But I want to alternate by group. Is that possible and if so, how?
Thanks,I had a similar requirement recently but could find nothing in help or on the
net. I wrote this embedded code which works for me but I'm curious if there's
a simpler way.
Add this to the report's embedded code window:
Public GroupRowCounter As Integer = 0
Function IncrementCounter() As Integer
GroupRowCounter += 1
Return GroupRowCounter
End Function
Function GetCounter() As Integer
Return GroupRowCounter
End Function
Right click the header row you want the alternate coloring in and select
Insert Row Above. In the new, topmost header row set the Hidden property to
True and add this to one of the cells:
=Code.IncrementCounter
For the header/detail/footer portions of the group that will be visible, add
this to the BackgroundColor property to alternate the row (group) colors:
=IIF(Code.GetCounter Mod 2, "WhiteSmoke", "White")
"BillTWD" wrote:
> I have a report in which the results are grouped. For example, the report
> returnd 25 rows, the first 5 rows are one group (lets say all 'A's, the
> second 5 rows are another group (lets say all 'B's), etc...
> Can I add conditional formatting to alternate the backgroup color based on
> groups?
> I know I can alternate row colors using:
> =iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
> But I want to alternate by group. Is that possible and if so, how?
> Thanks,|||If you're grouping on Country, this is the code to use.
=Iif(RunningValue(Fields!Country.Value,CountDistinct, Nothing) Mod 2,
"LightGreen", "Cornsilk")
Mike Glaser
"BillTWD" wrote:
> I have a report in which the results are grouped. For example, the report
> returnd 25 rows, the first 5 rows are one group (lets say all 'A's, the
> second 5 rows are another group (lets say all 'B's), etc...
> Can I add conditional formatting to alternate the backgroup color based on
> groups?
> I know I can alternate row colors using:
> =iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
> But I want to alternate by group. Is that possible and if so, how?
> Thanks,
Tuesday, March 27, 2012
Alternate Background Color in Row
Hi,
I am working on a report in the Reporting Services. I was wondering if we
can set alternate background color for a group of rows on the report. Let's
see the example below.
ID Name Month Field4 Field5
1 A Jan 8 9 -- Background color: red
1 A Jan 10 2 -- Background color: red
1 A May 3 3 -- Background color:
transparent
2 B Feb 2 4 -- Background color: red
3 C Apr 5 6 -- Background color:
transparent
3 C Apr 7 5 -- Background color:
transparent
4 D Mar 2 2 -- Background color: red
4 D Jun 3 1 -- Background color:
transparent
The background color will be set based on the group of ID, Name and Month
fields. Does anyone know if this is doable? If so, how to do it?
Any help would be greatly appreciated!
Thank you in advance.
Jeannetteto alternate colors in a table, place this in BackgroundColor expression:
=iif(RowNumber(Nothing) Mod 2, "AliceBlue", "White")
feel free to place whatever colors you want in there
"Jeannette" wrote:
> Hi,
> I am working on a report in the Reporting Services. I was wondering if we
> can set alternate background color for a group of rows on the report. Let's
> see the example below.
> ID Name Month Field4 Field5
> 1 A Jan 8 9 -- Background color: red
> 1 A Jan 10 2 -- Background color: red
> 1 A May 3 3 -- Background color:
> transparent
> 2 B Feb 2 4 -- Background color: red
> 3 C Apr 5 6 -- Background color:
> transparent
> 3 C Apr 7 5 -- Background color:
> transparent
> 4 D Mar 2 2 -- Background color: red
> 4 D Jun 3 1 -- Background color:
> transparent
> The background color will be set based on the group of ID, Name and Month
> fields. Does anyone know if this is doable? If so, how to do it?
> Any help would be greatly appreciated!
> Thank you in advance.
> Jeannette|||Thank you very much for your response! Carl. Although your answer is not
exactly what I am looking for, it really helps me to get to what I need for
my report. As I explained earlier in my previous message, the alternate
background color will be set based on the GROUP of ID, Name, and Color
fields. Below is what I found for my question. It really works!
In BackgroundColor expression:
=iif(RunningValue(Cstr(Fields!ID.Value) & CStr(Fields!Name.Value) &
CStr(Fields!Month.Value),CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Jeannette
"Carl Henthorn" wrote:
> to alternate colors in a table, place this in BackgroundColor expression:
> =iif(RowNumber(Nothing) Mod 2, "AliceBlue", "White")
> feel free to place whatever colors you want in there
> "Jeannette" wrote:
> > Hi,
> >
> > I am working on a report in the Reporting Services. I was wondering if we
> > can set alternate background color for a group of rows on the report. Let's
> > see the example below.
> >
> > ID Name Month Field4 Field5
> > 1 A Jan 8 9 -- Background color: red
> > 1 A Jan 10 2 -- Background color: red
> > 1 A May 3 3 -- Background color:
> > transparent
> > 2 B Feb 2 4 -- Background color: red
> > 3 C Apr 5 6 -- Background color:
> > transparent
> > 3 C Apr 7 5 -- Background color:
> > transparent
> > 4 D Mar 2 2 -- Background color: red
> > 4 D Jun 3 1 -- Background color:
> > transparent
> >
> > The background color will be set based on the group of ID, Name and Month
> > fields. Does anyone know if this is doable? If so, how to do it?
> >
> > Any help would be greatly appreciated!
> >
> > Thank you in advance.
> >
> > Jeannette
I am working on a report in the Reporting Services. I was wondering if we
can set alternate background color for a group of rows on the report. Let's
see the example below.
ID Name Month Field4 Field5
1 A Jan 8 9 -- Background color: red
1 A Jan 10 2 -- Background color: red
1 A May 3 3 -- Background color:
transparent
2 B Feb 2 4 -- Background color: red
3 C Apr 5 6 -- Background color:
transparent
3 C Apr 7 5 -- Background color:
transparent
4 D Mar 2 2 -- Background color: red
4 D Jun 3 1 -- Background color:
transparent
The background color will be set based on the group of ID, Name and Month
fields. Does anyone know if this is doable? If so, how to do it?
Any help would be greatly appreciated!
Thank you in advance.
Jeannetteto alternate colors in a table, place this in BackgroundColor expression:
=iif(RowNumber(Nothing) Mod 2, "AliceBlue", "White")
feel free to place whatever colors you want in there
"Jeannette" wrote:
> Hi,
> I am working on a report in the Reporting Services. I was wondering if we
> can set alternate background color for a group of rows on the report. Let's
> see the example below.
> ID Name Month Field4 Field5
> 1 A Jan 8 9 -- Background color: red
> 1 A Jan 10 2 -- Background color: red
> 1 A May 3 3 -- Background color:
> transparent
> 2 B Feb 2 4 -- Background color: red
> 3 C Apr 5 6 -- Background color:
> transparent
> 3 C Apr 7 5 -- Background color:
> transparent
> 4 D Mar 2 2 -- Background color: red
> 4 D Jun 3 1 -- Background color:
> transparent
> The background color will be set based on the group of ID, Name and Month
> fields. Does anyone know if this is doable? If so, how to do it?
> Any help would be greatly appreciated!
> Thank you in advance.
> Jeannette|||Thank you very much for your response! Carl. Although your answer is not
exactly what I am looking for, it really helps me to get to what I need for
my report. As I explained earlier in my previous message, the alternate
background color will be set based on the GROUP of ID, Name, and Color
fields. Below is what I found for my question. It really works!
In BackgroundColor expression:
=iif(RunningValue(Cstr(Fields!ID.Value) & CStr(Fields!Name.Value) &
CStr(Fields!Month.Value),CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Jeannette
"Carl Henthorn" wrote:
> to alternate colors in a table, place this in BackgroundColor expression:
> =iif(RowNumber(Nothing) Mod 2, "AliceBlue", "White")
> feel free to place whatever colors you want in there
> "Jeannette" wrote:
> > Hi,
> >
> > I am working on a report in the Reporting Services. I was wondering if we
> > can set alternate background color for a group of rows on the report. Let's
> > see the example below.
> >
> > ID Name Month Field4 Field5
> > 1 A Jan 8 9 -- Background color: red
> > 1 A Jan 10 2 -- Background color: red
> > 1 A May 3 3 -- Background color:
> > transparent
> > 2 B Feb 2 4 -- Background color: red
> > 3 C Apr 5 6 -- Background color:
> > transparent
> > 3 C Apr 7 5 -- Background color:
> > transparent
> > 4 D Mar 2 2 -- Background color: red
> > 4 D Jun 3 1 -- Background color:
> > transparent
> >
> > The background color will be set based on the group of ID, Name and Month
> > fields. Does anyone know if this is doable? If so, how to do it?
> >
> > Any help would be greatly appreciated!
> >
> > Thank you in advance.
> >
> > Jeannette
Alternate Background Color
I have a table about 3 rows. I want to see every other row highlighted gray
so it easier for the eye to follow.
any ideas?
Thanks
Fab.You can use RowNumber(nothing) Mod 2 to find out if the row is even or odd,
and use this in an IIf to change the background color.
"Fab" wrote:
> I have a table about 3 rows. I want to see every other row highlighted gray
> so it easier for the eye to follow.
> any ideas?
> Thanks
> Fab.
>
>|||Use this in the backgroundcolor property :
=iif(RowNumber(nothing) mod 2 =0,"LightGrey",Nothing)
"Fab" wrote:
> I have a table about 3 rows. I want to see every other row highlighted gray
> so it easier for the eye to follow.
> any ideas?
> Thanks
> Fab.
>
>|||Hi
I would use a custom function for this as it is quite a common thing to
want to do.
' Alternate Row Colors
Public Function AltCol(ByVal RowNum As Integer) As String
Dim ReturnColor as String
If RowNum Mod 2
ReturnColor = "WhiteSmoke"
Else
ReturnColor = "White"
End If
Return ReturnColor
End Function
You could extend this by adding optional variables for the alterante
colors making this a completely generic function.
To use this go to expressions in the Row BG Color tab in properties.
add the following line: =Code!AltCol(RowNumber())
This is just pointer in the right direction but what it allows is for
you to uses this all over a report, groups with out having to IIF
everywhere.|||Hi
I would use a custom function for this as it is quite a common thing to
want to do.
' Alternate Row Colors
Public Function AltCol(ByVal RowNum As Integer) As String
Dim ReturnColor as String
If RowNum Mod 2
ReturnColor = "WhiteSmoke"
Else
ReturnColor = "White"
End If
Return ReturnColor
End Function
You could extend this by adding optional variables for the alterante
colors making this a completely generic function.
To use this go to expressions in the Row BG Color tab in properties.
add the following line: =Code!AltCol(RowNumber())
This is just pointer in the right direction but what it allows is for
you to uses this all over a report, groups with out having to IIF
everywhere.|||Is it possible to have different colors? like "LightGrey" for odd rows and
"WhiteSmoke" for even rows? How would I combine the two iif statements - one
for mod 2 =0 and other for mod 2 = 1?
Thanks.
"Eric" wrote:
> Use this in the backgroundcolor property :
> =iif(RowNumber(nothing) mod 2 =0,"LightGrey",Nothing)
>
> "Fab" wrote:
> > I have a table about 3 rows. I want to see every other row highlighted gray
> > so it easier for the eye to follow.
> >
> > any ideas?
> >
> > Thanks
> > Fab.
> >
> >
> >
so it easier for the eye to follow.
any ideas?
Thanks
Fab.You can use RowNumber(nothing) Mod 2 to find out if the row is even or odd,
and use this in an IIf to change the background color.
"Fab" wrote:
> I have a table about 3 rows. I want to see every other row highlighted gray
> so it easier for the eye to follow.
> any ideas?
> Thanks
> Fab.
>
>|||Use this in the backgroundcolor property :
=iif(RowNumber(nothing) mod 2 =0,"LightGrey",Nothing)
"Fab" wrote:
> I have a table about 3 rows. I want to see every other row highlighted gray
> so it easier for the eye to follow.
> any ideas?
> Thanks
> Fab.
>
>|||Hi
I would use a custom function for this as it is quite a common thing to
want to do.
' Alternate Row Colors
Public Function AltCol(ByVal RowNum As Integer) As String
Dim ReturnColor as String
If RowNum Mod 2
ReturnColor = "WhiteSmoke"
Else
ReturnColor = "White"
End If
Return ReturnColor
End Function
You could extend this by adding optional variables for the alterante
colors making this a completely generic function.
To use this go to expressions in the Row BG Color tab in properties.
add the following line: =Code!AltCol(RowNumber())
This is just pointer in the right direction but what it allows is for
you to uses this all over a report, groups with out having to IIF
everywhere.|||Hi
I would use a custom function for this as it is quite a common thing to
want to do.
' Alternate Row Colors
Public Function AltCol(ByVal RowNum As Integer) As String
Dim ReturnColor as String
If RowNum Mod 2
ReturnColor = "WhiteSmoke"
Else
ReturnColor = "White"
End If
Return ReturnColor
End Function
You could extend this by adding optional variables for the alterante
colors making this a completely generic function.
To use this go to expressions in the Row BG Color tab in properties.
add the following line: =Code!AltCol(RowNumber())
This is just pointer in the right direction but what it allows is for
you to uses this all over a report, groups with out having to IIF
everywhere.|||Is it possible to have different colors? like "LightGrey" for odd rows and
"WhiteSmoke" for even rows? How would I combine the two iif statements - one
for mod 2 =0 and other for mod 2 = 1?
Thanks.
"Eric" wrote:
> Use this in the backgroundcolor property :
> =iif(RowNumber(nothing) mod 2 =0,"LightGrey",Nothing)
>
> "Fab" wrote:
> > I have a table about 3 rows. I want to see every other row highlighted gray
> > so it easier for the eye to follow.
> >
> > any ideas?
> >
> > Thanks
> > Fab.
> >
> >
> >
altering columns performance issues
Good Day:
When altering column width in SQL2000SP3a we find that this is taking a long
running time on tables with many rows.
We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
the data should not need to be transformed, only the column altered.
There are some 20 million rows and some 200 columns that need this
alteration.
Any thoughts on how to speed this up?
We could create new tables and copy the data, but that requires all user
activity to be stopped, and that is not palatable.
I note that Oracle can do this sort of an alter instantly, for some reason.
THANKS
Mr. Lynn Teska
Mayo ClinicHi Lynn
I just wrote an article on ALTER TABLE for SQL Server Magazine, and why some
changes take a long time and other don't. Unfortunately, it won't appear
until January, so I'll give you a sneak preview.
You are right that the data doesn't need to be transformed, but I'm not sure
what you mean by 'only the column altered'. Yes, the column needs to be
altered, and in every single row in the table. For some changes, like
changing to a smaller datatype, only the metadata needs to be changed (but
the existing data does need to be validated to make sure it 'fits' into the
smaller type.)
If you alter a column's datatype to one that needs more storage space, SQL
Server will actually make the physical change to every row to allow the
additional bytes that are needed to store numeric(8,3).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a
long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some
reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||In addition to Kalen's response:
Watch out if you use EM for this. EM generally creates a new table, copy the data etc etc instead of
executing an ALTER TABLE tblname ALTER COLUMN colname command.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lynn Teska" <lteska@.mayo.edu> wrote in message news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||Good Day:
Well here is a hypothetical situation:
We have a 24x7x365 app with a particular table that has some 200
numeric(7,2) columns.
There are a number of other columns with constraints and indexes, but none
of the numeric (7,2) columns is indexed or constrained.
The applications store new data in the table every 1 minute, or thereabouts.
There are 25 Million rows in the table.
We need to widen all the numeric (7,2) columns to numeric (8,3).
We need to minimize or eliminate application outage.
SQL server is doing a huge amount of I/O to alter the columns.
SQL server appears to only be able to alter one column at a time.
Now when I add new columns SQL server is quick as you please.
What strategy would this group think best to accomplish the task of widening
the columns?
Thanks for any insight!
Lynn Teska
Mayo Clinic
--
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyk5vqrnDHA.688@.TK2MSFTNGP10.phx.gbl...
> In addition to Kalen's response:
> Watch out if you use EM for this. EM generally creates a new table, copy
the data etc etc instead of
> executing an ALTER TABLE tblname ALTER COLUMN colname command.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > Good Day:
> >
> > When altering column width in SQL2000SP3a we find that this is taking a
long
> > running time on tables with many rows.
> > We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> > the data should not need to be transformed, only the column altered.
> > There are some 20 million rows and some 200 columns that need this
> > alteration.
> >
> > Any thoughts on how to speed this up?
> > We could create new tables and copy the data, but that requires all user
> > activity to be stopped, and that is not palatable.
> >
> > I note that Oracle can do this sort of an alter instantly, for some
reason.
> >
> > THANKS
> > Mr. Lynn Teska
> > Mayo Clinic
> >
> >
>|||Hi Lynn,
I would like to thank Kalen and Tibor for their help. As I understand, you
want to widen all the numeric (7,2) columns to numeric (8,3) in the table
on the machine. If I have misunderstood, please feel free to let me know.
To change the schema of the table, we can perform SQL statements using
Query Analyzer or Change the table schema directly in SQL Server Enterprise
Manager. Because Enterprise Manager will create a new tale and drop the
original table for changing the schema, I think it is better to use Query
Analyzer with ALTER TABLE statement.
Example:
alter table <table name> alter column <column name> numeric(8,3)
For additional information regarding ALTER TABLE, please refer to the
following article on SQL Server Books Online.
Topic: "ALTER TABLE"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I understand how to do the task with the EM or TSQL.
The problem is the time it takes with 25 million rows is not acceptable in
at 24x7x365 system.
TSQL can only alter one column at a time, and with 200 columns to do that is
not so good.
EM makes copies as you note and that is not efficient in terms of disk i/o.
I am trying to get a strategy that will minimize or eliminate down time and
blocking of the table.
Interestingly Oracle can do this sort of alteration instantly, according to
our vendor.
Thanks
Lynn Teska
Mayo Clinic
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lynn,
> I would like to thank Kalen and Tibor for their help. As I understand, you
> want to widen all the numeric (7,2) columns to numeric (8,3) in the table
> on the machine. If I have misunderstood, please feel free to let me know.
> To change the schema of the table, we can perform SQL statements using
> Query Analyzer or Change the table schema directly in SQL Server
Enterprise
> Manager. Because Enterprise Manager will create a new tale and drop the
> original table for changing the schema, I think it is better to use Query
> Analyzer with ALTER TABLE statement.
> Example:
> alter table <table name> alter column <column name> numeric(8,3)
> For additional information regarding ALTER TABLE, please refer to the
> following article on SQL Server Books Online.
> Topic: "ALTER TABLE"
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Lynn
The vendors says Oracle can do this instantly, but you've never tried it
yourself?
I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
instantly too, that's why you come to a Technical forum to find out. Have
you asked this on an Oracle Technical forum?
As I mentioned, there are some kinds of ALTER TABLEs that can be done
instantly, and there is a lot of confusion, so someone who had not
researched this issue in technical detail could easily tell you that this
was an instantaneous operation, even on SQL Server.
You are wanting to physically change 25 million rows? How could this
possible be an instantaneous operation?
One faster way you might consider is to create a new table using select
into, which is a very fast command, and then dropping the old table, and
renaming the new one to the old name.
SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
col2 ...
INTO newtable
FROM original_table
GO
--recreate constraints, indexes etc on newtable
DROP original_table
EXEC sp_rename newtable, original_table
Good Luck!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I understand how to do the task with the EM or TSQL.
> The problem is the time it takes with 25 million rows is not acceptable in
> at 24x7x365 system.
> TSQL can only alter one column at a time, and with 200 columns to do that
is
> not so good.
> EM makes copies as you note and that is not efficient in terms of disk
i/o.
> I am trying to get a strategy that will minimize or eliminate down time
and
> blocking of the table.
> Interestingly Oracle can do this sort of alteration instantly, according
to
> our vendor.
> Thanks
> Lynn Teska
> Mayo Clinic
>
> "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > Hi Lynn,
> >
> > I would like to thank Kalen and Tibor for their help. As I understand,
you
> > want to widen all the numeric (7,2) columns to numeric (8,3) in the
table
> > on the machine. If I have misunderstood, please feel free to let me
know.
> >
> > To change the schema of the table, we can perform SQL statements using
> > Query Analyzer or Change the table schema directly in SQL Server
> Enterprise
> > Manager. Because Enterprise Manager will create a new tale and drop the
> > original table for changing the schema, I think it is better to use
Query
> > Analyzer with ALTER TABLE statement.
> >
> > Example:
> >
> > alter table <table name> alter column <column name> numeric(8,3)
> >
> > For additional information regarding ALTER TABLE, please refer to the
> > following article on SQL Server Books Online.
> > Topic: "ALTER TABLE"
> >
> > Please feel free to post in the group if this solves your problem or if
> you
> > would like further assistance.
> >
> > Regards,
> >
> > Michael Shao
> > Microsoft Online Partner Support
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> >
>|||Thanks for the info.
The vended product runs on both SQL server and Oracle, I have no reason to
doubt that the change is fast on Oracle.
They seem to be experienced in both platforms.
Thanks for your suggestion I will have a look at it.
Lynn Teska
PS:
This is an expert of a note I received from an Oracle expert:
Oracle always reserves a minimum of space in every database block for
updates, determined by the PCTFREE parameter (10% of the block size by
default, which we don't modify). Hence, Oracle does not have to reserve any
extra space, it's already there. Initially the old rows will surely fit
inside the new definition, as we have just widened the columns. If some
value needs some extra space, it will use the PCTFREE space. If this is not
enough, a phenomenon called row chaining will take place, as part of the row
will have to be stored in another block, and this is not so good for
performance, but it can be attacked later on. In our case this would never
happen as we won't go updating old values.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||All the more interesting since numeric (7,2) and numeric (8,3) both appear
to be stored in 5 bytes.
Lynn
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||Yes, there does appear to be some unexpected behavior going on. I am still
researching this issue.
OTOH, I have just learned that although you are correct, that Oracle does
make this change and all ALTER TABLE changes instantly, as only a metadata
change, the tradeoff is that actual row size adjustment must be made during
actual data use. In fact, if you change the datatype size to something very
different, or smaller, like char(5) to char(3) or char to int, Oracle will
not do any validation at the time of the alter table, but will wait until
your read the row. So you can end up getting conversion errors just by
reading data, which can be very problematic.
So, like many choices, there is a tradeoff. Normally, since altering tables
is a rare occurance, but updating and modifying data is done during
production operations, I'd rather the overhead was accrued during the alter,
when I can plan for it.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> All the more interesting since numeric (7,2) and numeric (8,3) both appear
> to be stored in 5 bytes.
> Lynn
> --
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > Lynn
> >
> > The vendors says Oracle can do this instantly, but you've never tried it
> > yourself?
> >
> > I wouldn't be surprised if a SQL Server vendor said SQL Server could do
it
> > instantly too, that's why you come to a Technical forum to find out.
Have
> > you asked this on an Oracle Technical forum?
> >
> > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > instantly, and there is a lot of confusion, so someone who had not
> > researched this issue in technical detail could easily tell you that
this
> > was an instantaneous operation, even on SQL Server.
> >
> > You are wanting to physically change 25 million rows? How could this
> > possible be an instantaneous operation?
> >
> > One faster way you might consider is to create a new table using select
> > into, which is a very fast command, and then dropping the old table, and
> > renaming the new one to the old name.
> >
> > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
as
> > col2 ...
> > INTO newtable
> > FROM original_table
> > GO
> >
> > --recreate constraints, indexes etc on newtable
> >
> > DROP original_table
> >
> > EXEC sp_rename newtable, original_table
> >
> > Good Luck!
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I understand how to do the task with the EM or TSQL.
> > > The problem is the time it takes with 25 million rows is not
acceptable
> in
> > > at 24x7x365 system.
> > >
> > > TSQL can only alter one column at a time, and with 200 columns to do
> that
> > is
> > > not so good.
> > > EM makes copies as you note and that is not efficient in terms of disk
> > i/o.
> > > I am trying to get a strategy that will minimize or eliminate down
time
> > and
> > > blocking of the table.
> > >
> > > Interestingly Oracle can do this sort of alteration instantly,
according
> > to
> > > our vendor.
> > >
> > > Thanks
> > >
> > > Lynn Teska
> > > Mayo Clinic
> > >
> > >
> > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > Hi Lynn,
> > > >
> > > > I would like to thank Kalen and Tibor for their help. As I
understand,
> > you
> > > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> > table
> > > > on the machine. If I have misunderstood, please feel free to let me
> > know.
> > > >
> > > > To change the schema of the table, we can perform SQL statements
using
> > > > Query Analyzer or Change the table schema directly in SQL Server
> > > Enterprise
> > > > Manager. Because Enterprise Manager will create a new tale and drop
> the
> > > > original table for changing the schema, I think it is better to use
> > Query
> > > > Analyzer with ALTER TABLE statement.
> > > >
> > > > Example:
> > > >
> > > > alter table <table name> alter column <column name> numeric(8,3)
> > > >
> > > > For additional information regarding ALTER TABLE, please refer to
the
> > > > following article on SQL Server Books Online.
> > > > Topic: "ALTER TABLE"
> > > >
> > > > Please feel free to post in the group if this solves your problem or
> if
> > > you
> > > > would like further assistance.
> > > >
> > > > Regards,
> > > >
> > > > Michael Shao
> > > > Microsoft Online Partner Support
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > >
> > >
> > >
> >
> >
>|||OK Thanks.
Let me know what you find.
A 4 hour outage is not a plus for us.
Thanks
Lynn Teska
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Jo3UejoDHA.3040@.TK2MSFTNGP11.phx.gbl...
> Yes, there does appear to be some unexpected behavior going on. I am still
> researching this issue.
> OTOH, I have just learned that although you are correct, that Oracle does
> make this change and all ALTER TABLE changes instantly, as only a metadata
> change, the tradeoff is that actual row size adjustment must be made
during
> actual data use. In fact, if you change the datatype size to something
very
> different, or smaller, like char(5) to char(3) or char to int, Oracle will
> not do any validation at the time of the alter table, but will wait until
> your read the row. So you can end up getting conversion errors just by
> reading data, which can be very problematic.
> So, like many choices, there is a tradeoff. Normally, since altering
tables
> is a rare occurance, but updating and modifying data is done during
> production operations, I'd rather the overhead was accrued during the
alter,
> when I can plan for it.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > All the more interesting since numeric (7,2) and numeric (8,3) both
appear
> > to be stored in 5 bytes.
> >
> > Lynn
> >
> > --
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > > Lynn
> > >
> > > The vendors says Oracle can do this instantly, but you've never tried
it
> > > yourself?
> > >
> > > I wouldn't be surprised if a SQL Server vendor said SQL Server could
do
> it
> > > instantly too, that's why you come to a Technical forum to find out.
> Have
> > > you asked this on an Oracle Technical forum?
> > >
> > > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > > instantly, and there is a lot of confusion, so someone who had not
> > > researched this issue in technical detail could easily tell you that
> this
> > > was an instantaneous operation, even on SQL Server.
> > >
> > > You are wanting to physically change 25 million rows? How could this
> > > possible be an instantaneous operation?
> > >
> > > One faster way you might consider is to create a new table using
select
> > > into, which is a very fast command, and then dropping the old table,
and
> > > renaming the new one to the old name.
> > >
> > > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
> as
> > > col2 ...
> > > INTO newtable
> > > FROM original_table
> > > GO
> > >
> > > --recreate constraints, indexes etc on newtable
> > >
> > > DROP original_table
> > >
> > > EXEC sp_rename newtable, original_table
> > >
> > > Good Luck!
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I understand how to do the task with the EM or TSQL.
> > > > The problem is the time it takes with 25 million rows is not
> acceptable
> > in
> > > > at 24x7x365 system.
> > > >
> > > > TSQL can only alter one column at a time, and with 200 columns to do
> > that
> > > is
> > > > not so good.
> > > > EM makes copies as you note and that is not efficient in terms of
disk
> > > i/o.
> > > > I am trying to get a strategy that will minimize or eliminate down
> time
> > > and
> > > > blocking of the table.
> > > >
> > > > Interestingly Oracle can do this sort of alteration instantly,
> according
> > > to
> > > > our vendor.
> > > >
> > > > Thanks
> > > >
> > > > Lynn Teska
> > > > Mayo Clinic
> > > >
> > > >
> > > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in
message
> > > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > > Hi Lynn,
> > > > >
> > > > > I would like to thank Kalen and Tibor for their help. As I
> understand,
> > > you
> > > > > want to widen all the numeric (7,2) columns to numeric (8,3) in
the
> > > table
> > > > > on the machine. If I have misunderstood, please feel free to let
me
> > > know.
> > > > >
> > > > > To change the schema of the table, we can perform SQL statements
> using
> > > > > Query Analyzer or Change the table schema directly in SQL Server
> > > > Enterprise
> > > > > Manager. Because Enterprise Manager will create a new tale and
drop
> > the
> > > > > original table for changing the schema, I think it is better to
use
> > > Query
> > > > > Analyzer with ALTER TABLE statement.
> > > > >
> > > > > Example:
> > > > >
> > > > > alter table <table name> alter column <column name> numeric(8,3)
> > > > >
> > > > > For additional information regarding ALTER TABLE, please refer to
> the
> > > > > following article on SQL Server Books Online.
> > > > > Topic: "ALTER TABLE"
> > > > >
> > > > > Please feel free to post in the group if this solves your problem
or
> > if
> > > > you
> > > > > would like further assistance.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Michael Shao
> > > > > Microsoft Online Partner Support
> > > > > Get Secure! - www.microsoft.com/security
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
When altering column width in SQL2000SP3a we find that this is taking a long
running time on tables with many rows.
We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
the data should not need to be transformed, only the column altered.
There are some 20 million rows and some 200 columns that need this
alteration.
Any thoughts on how to speed this up?
We could create new tables and copy the data, but that requires all user
activity to be stopped, and that is not palatable.
I note that Oracle can do this sort of an alter instantly, for some reason.
THANKS
Mr. Lynn Teska
Mayo ClinicHi Lynn
I just wrote an article on ALTER TABLE for SQL Server Magazine, and why some
changes take a long time and other don't. Unfortunately, it won't appear
until January, so I'll give you a sneak preview.
You are right that the data doesn't need to be transformed, but I'm not sure
what you mean by 'only the column altered'. Yes, the column needs to be
altered, and in every single row in the table. For some changes, like
changing to a smaller datatype, only the metadata needs to be changed (but
the existing data does need to be validated to make sure it 'fits' into the
smaller type.)
If you alter a column's datatype to one that needs more storage space, SQL
Server will actually make the physical change to every row to allow the
additional bytes that are needed to store numeric(8,3).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a
long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some
reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||In addition to Kalen's response:
Watch out if you use EM for this. EM generally creates a new table, copy the data etc etc instead of
executing an ALTER TABLE tblname ALTER COLUMN colname command.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lynn Teska" <lteska@.mayo.edu> wrote in message news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||Good Day:
Well here is a hypothetical situation:
We have a 24x7x365 app with a particular table that has some 200
numeric(7,2) columns.
There are a number of other columns with constraints and indexes, but none
of the numeric (7,2) columns is indexed or constrained.
The applications store new data in the table every 1 minute, or thereabouts.
There are 25 Million rows in the table.
We need to widen all the numeric (7,2) columns to numeric (8,3).
We need to minimize or eliminate application outage.
SQL server is doing a huge amount of I/O to alter the columns.
SQL server appears to only be able to alter one column at a time.
Now when I add new columns SQL server is quick as you please.
What strategy would this group think best to accomplish the task of widening
the columns?
Thanks for any insight!
Lynn Teska
Mayo Clinic
--
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyk5vqrnDHA.688@.TK2MSFTNGP10.phx.gbl...
> In addition to Kalen's response:
> Watch out if you use EM for this. EM generally creates a new table, copy
the data etc etc instead of
> executing an ALTER TABLE tblname ALTER COLUMN colname command.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > Good Day:
> >
> > When altering column width in SQL2000SP3a we find that this is taking a
long
> > running time on tables with many rows.
> > We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> > the data should not need to be transformed, only the column altered.
> > There are some 20 million rows and some 200 columns that need this
> > alteration.
> >
> > Any thoughts on how to speed this up?
> > We could create new tables and copy the data, but that requires all user
> > activity to be stopped, and that is not palatable.
> >
> > I note that Oracle can do this sort of an alter instantly, for some
reason.
> >
> > THANKS
> > Mr. Lynn Teska
> > Mayo Clinic
> >
> >
>|||Hi Lynn,
I would like to thank Kalen and Tibor for their help. As I understand, you
want to widen all the numeric (7,2) columns to numeric (8,3) in the table
on the machine. If I have misunderstood, please feel free to let me know.
To change the schema of the table, we can perform SQL statements using
Query Analyzer or Change the table schema directly in SQL Server Enterprise
Manager. Because Enterprise Manager will create a new tale and drop the
original table for changing the schema, I think it is better to use Query
Analyzer with ALTER TABLE statement.
Example:
alter table <table name> alter column <column name> numeric(8,3)
For additional information regarding ALTER TABLE, please refer to the
following article on SQL Server Books Online.
Topic: "ALTER TABLE"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I understand how to do the task with the EM or TSQL.
The problem is the time it takes with 25 million rows is not acceptable in
at 24x7x365 system.
TSQL can only alter one column at a time, and with 200 columns to do that is
not so good.
EM makes copies as you note and that is not efficient in terms of disk i/o.
I am trying to get a strategy that will minimize or eliminate down time and
blocking of the table.
Interestingly Oracle can do this sort of alteration instantly, according to
our vendor.
Thanks
Lynn Teska
Mayo Clinic
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lynn,
> I would like to thank Kalen and Tibor for their help. As I understand, you
> want to widen all the numeric (7,2) columns to numeric (8,3) in the table
> on the machine. If I have misunderstood, please feel free to let me know.
> To change the schema of the table, we can perform SQL statements using
> Query Analyzer or Change the table schema directly in SQL Server
Enterprise
> Manager. Because Enterprise Manager will create a new tale and drop the
> original table for changing the schema, I think it is better to use Query
> Analyzer with ALTER TABLE statement.
> Example:
> alter table <table name> alter column <column name> numeric(8,3)
> For additional information regarding ALTER TABLE, please refer to the
> following article on SQL Server Books Online.
> Topic: "ALTER TABLE"
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Lynn
The vendors says Oracle can do this instantly, but you've never tried it
yourself?
I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
instantly too, that's why you come to a Technical forum to find out. Have
you asked this on an Oracle Technical forum?
As I mentioned, there are some kinds of ALTER TABLEs that can be done
instantly, and there is a lot of confusion, so someone who had not
researched this issue in technical detail could easily tell you that this
was an instantaneous operation, even on SQL Server.
You are wanting to physically change 25 million rows? How could this
possible be an instantaneous operation?
One faster way you might consider is to create a new table using select
into, which is a very fast command, and then dropping the old table, and
renaming the new one to the old name.
SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
col2 ...
INTO newtable
FROM original_table
GO
--recreate constraints, indexes etc on newtable
DROP original_table
EXEC sp_rename newtable, original_table
Good Luck!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I understand how to do the task with the EM or TSQL.
> The problem is the time it takes with 25 million rows is not acceptable in
> at 24x7x365 system.
> TSQL can only alter one column at a time, and with 200 columns to do that
is
> not so good.
> EM makes copies as you note and that is not efficient in terms of disk
i/o.
> I am trying to get a strategy that will minimize or eliminate down time
and
> blocking of the table.
> Interestingly Oracle can do this sort of alteration instantly, according
to
> our vendor.
> Thanks
> Lynn Teska
> Mayo Clinic
>
> "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > Hi Lynn,
> >
> > I would like to thank Kalen and Tibor for their help. As I understand,
you
> > want to widen all the numeric (7,2) columns to numeric (8,3) in the
table
> > on the machine. If I have misunderstood, please feel free to let me
know.
> >
> > To change the schema of the table, we can perform SQL statements using
> > Query Analyzer or Change the table schema directly in SQL Server
> Enterprise
> > Manager. Because Enterprise Manager will create a new tale and drop the
> > original table for changing the schema, I think it is better to use
Query
> > Analyzer with ALTER TABLE statement.
> >
> > Example:
> >
> > alter table <table name> alter column <column name> numeric(8,3)
> >
> > For additional information regarding ALTER TABLE, please refer to the
> > following article on SQL Server Books Online.
> > Topic: "ALTER TABLE"
> >
> > Please feel free to post in the group if this solves your problem or if
> you
> > would like further assistance.
> >
> > Regards,
> >
> > Michael Shao
> > Microsoft Online Partner Support
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> >
>|||Thanks for the info.
The vended product runs on both SQL server and Oracle, I have no reason to
doubt that the change is fast on Oracle.
They seem to be experienced in both platforms.
Thanks for your suggestion I will have a look at it.
Lynn Teska
PS:
This is an expert of a note I received from an Oracle expert:
Oracle always reserves a minimum of space in every database block for
updates, determined by the PCTFREE parameter (10% of the block size by
default, which we don't modify). Hence, Oracle does not have to reserve any
extra space, it's already there. Initially the old rows will surely fit
inside the new definition, as we have just widened the columns. If some
value needs some extra space, it will use the PCTFREE space. If this is not
enough, a phenomenon called row chaining will take place, as part of the row
will have to be stored in another block, and this is not so good for
performance, but it can be attacked later on. In our case this would never
happen as we won't go updating old values.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||All the more interesting since numeric (7,2) and numeric (8,3) both appear
to be stored in 5 bytes.
Lynn
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||Yes, there does appear to be some unexpected behavior going on. I am still
researching this issue.
OTOH, I have just learned that although you are correct, that Oracle does
make this change and all ALTER TABLE changes instantly, as only a metadata
change, the tradeoff is that actual row size adjustment must be made during
actual data use. In fact, if you change the datatype size to something very
different, or smaller, like char(5) to char(3) or char to int, Oracle will
not do any validation at the time of the alter table, but will wait until
your read the row. So you can end up getting conversion errors just by
reading data, which can be very problematic.
So, like many choices, there is a tradeoff. Normally, since altering tables
is a rare occurance, but updating and modifying data is done during
production operations, I'd rather the overhead was accrued during the alter,
when I can plan for it.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> All the more interesting since numeric (7,2) and numeric (8,3) both appear
> to be stored in 5 bytes.
> Lynn
> --
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > Lynn
> >
> > The vendors says Oracle can do this instantly, but you've never tried it
> > yourself?
> >
> > I wouldn't be surprised if a SQL Server vendor said SQL Server could do
it
> > instantly too, that's why you come to a Technical forum to find out.
Have
> > you asked this on an Oracle Technical forum?
> >
> > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > instantly, and there is a lot of confusion, so someone who had not
> > researched this issue in technical detail could easily tell you that
this
> > was an instantaneous operation, even on SQL Server.
> >
> > You are wanting to physically change 25 million rows? How could this
> > possible be an instantaneous operation?
> >
> > One faster way you might consider is to create a new table using select
> > into, which is a very fast command, and then dropping the old table, and
> > renaming the new one to the old name.
> >
> > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
as
> > col2 ...
> > INTO newtable
> > FROM original_table
> > GO
> >
> > --recreate constraints, indexes etc on newtable
> >
> > DROP original_table
> >
> > EXEC sp_rename newtable, original_table
> >
> > Good Luck!
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I understand how to do the task with the EM or TSQL.
> > > The problem is the time it takes with 25 million rows is not
acceptable
> in
> > > at 24x7x365 system.
> > >
> > > TSQL can only alter one column at a time, and with 200 columns to do
> that
> > is
> > > not so good.
> > > EM makes copies as you note and that is not efficient in terms of disk
> > i/o.
> > > I am trying to get a strategy that will minimize or eliminate down
time
> > and
> > > blocking of the table.
> > >
> > > Interestingly Oracle can do this sort of alteration instantly,
according
> > to
> > > our vendor.
> > >
> > > Thanks
> > >
> > > Lynn Teska
> > > Mayo Clinic
> > >
> > >
> > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > Hi Lynn,
> > > >
> > > > I would like to thank Kalen and Tibor for their help. As I
understand,
> > you
> > > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> > table
> > > > on the machine. If I have misunderstood, please feel free to let me
> > know.
> > > >
> > > > To change the schema of the table, we can perform SQL statements
using
> > > > Query Analyzer or Change the table schema directly in SQL Server
> > > Enterprise
> > > > Manager. Because Enterprise Manager will create a new tale and drop
> the
> > > > original table for changing the schema, I think it is better to use
> > Query
> > > > Analyzer with ALTER TABLE statement.
> > > >
> > > > Example:
> > > >
> > > > alter table <table name> alter column <column name> numeric(8,3)
> > > >
> > > > For additional information regarding ALTER TABLE, please refer to
the
> > > > following article on SQL Server Books Online.
> > > > Topic: "ALTER TABLE"
> > > >
> > > > Please feel free to post in the group if this solves your problem or
> if
> > > you
> > > > would like further assistance.
> > > >
> > > > Regards,
> > > >
> > > > Michael Shao
> > > > Microsoft Online Partner Support
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > >
> > >
> > >
> >
> >
>|||OK Thanks.
Let me know what you find.
A 4 hour outage is not a plus for us.
Thanks
Lynn Teska
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Jo3UejoDHA.3040@.TK2MSFTNGP11.phx.gbl...
> Yes, there does appear to be some unexpected behavior going on. I am still
> researching this issue.
> OTOH, I have just learned that although you are correct, that Oracle does
> make this change and all ALTER TABLE changes instantly, as only a metadata
> change, the tradeoff is that actual row size adjustment must be made
during
> actual data use. In fact, if you change the datatype size to something
very
> different, or smaller, like char(5) to char(3) or char to int, Oracle will
> not do any validation at the time of the alter table, but will wait until
> your read the row. So you can end up getting conversion errors just by
> reading data, which can be very problematic.
> So, like many choices, there is a tradeoff. Normally, since altering
tables
> is a rare occurance, but updating and modifying data is done during
> production operations, I'd rather the overhead was accrued during the
alter,
> when I can plan for it.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > All the more interesting since numeric (7,2) and numeric (8,3) both
appear
> > to be stored in 5 bytes.
> >
> > Lynn
> >
> > --
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > > Lynn
> > >
> > > The vendors says Oracle can do this instantly, but you've never tried
it
> > > yourself?
> > >
> > > I wouldn't be surprised if a SQL Server vendor said SQL Server could
do
> it
> > > instantly too, that's why you come to a Technical forum to find out.
> Have
> > > you asked this on an Oracle Technical forum?
> > >
> > > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > > instantly, and there is a lot of confusion, so someone who had not
> > > researched this issue in technical detail could easily tell you that
> this
> > > was an instantaneous operation, even on SQL Server.
> > >
> > > You are wanting to physically change 25 million rows? How could this
> > > possible be an instantaneous operation?
> > >
> > > One faster way you might consider is to create a new table using
select
> > > into, which is a very fast command, and then dropping the old table,
and
> > > renaming the new one to the old name.
> > >
> > > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
> as
> > > col2 ...
> > > INTO newtable
> > > FROM original_table
> > > GO
> > >
> > > --recreate constraints, indexes etc on newtable
> > >
> > > DROP original_table
> > >
> > > EXEC sp_rename newtable, original_table
> > >
> > > Good Luck!
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I understand how to do the task with the EM or TSQL.
> > > > The problem is the time it takes with 25 million rows is not
> acceptable
> > in
> > > > at 24x7x365 system.
> > > >
> > > > TSQL can only alter one column at a time, and with 200 columns to do
> > that
> > > is
> > > > not so good.
> > > > EM makes copies as you note and that is not efficient in terms of
disk
> > > i/o.
> > > > I am trying to get a strategy that will minimize or eliminate down
> time
> > > and
> > > > blocking of the table.
> > > >
> > > > Interestingly Oracle can do this sort of alteration instantly,
> according
> > > to
> > > > our vendor.
> > > >
> > > > Thanks
> > > >
> > > > Lynn Teska
> > > > Mayo Clinic
> > > >
> > > >
> > > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in
message
> > > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > > Hi Lynn,
> > > > >
> > > > > I would like to thank Kalen and Tibor for their help. As I
> understand,
> > > you
> > > > > want to widen all the numeric (7,2) columns to numeric (8,3) in
the
> > > table
> > > > > on the machine. If I have misunderstood, please feel free to let
me
> > > know.
> > > > >
> > > > > To change the schema of the table, we can perform SQL statements
> using
> > > > > Query Analyzer or Change the table schema directly in SQL Server
> > > > Enterprise
> > > > > Manager. Because Enterprise Manager will create a new tale and
drop
> > the
> > > > > original table for changing the schema, I think it is better to
use
> > > Query
> > > > > Analyzer with ALTER TABLE statement.
> > > > >
> > > > > Example:
> > > > >
> > > > > alter table <table name> alter column <column name> numeric(8,3)
> > > > >
> > > > > For additional information regarding ALTER TABLE, please refer to
> the
> > > > > following article on SQL Server Books Online.
> > > > > Topic: "ALTER TABLE"
> > > > >
> > > > > Please feel free to post in the group if this solves your problem
or
> > if
> > > > you
> > > > > would like further assistance.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Michael Shao
> > > > > Microsoft Online Partner Support
> > > > > Get Secure! - www.microsoft.com/security
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Altering column in a large table
Hi,
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
ManuThis will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:
> Hi,
> I need to alter one column which is a part of a table with billions of rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:
> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> > Hi,
> >
> > I need to alter one column which is a part of a table with billions of
> > rows
> > in it. What would be the finest and fastest approach to do it?
> >
> > Thanks in advance
> > Manu
>
>|||I'm pretty certain it is a meta-data only change (provided you use ALTER TABLE and not the GUI
tool). I suggest you create a table with some million rows and test, just to be certain...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3C68C5CC-0532-4035-9D56-7599FBF9C53C@.microsoft.com...
> The change is just to alter the not null property of a column in this huge
> table to NULL.
> Thanks
> Manu
> "TheSQLGuru" wrote:
>> What, exactly, is the alteration? Some changes could be done more
>> efficiently by bulk exporting data, dropping all structures on table,
>> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
>> Others are nothing more than a meta-data change.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>> > Hi,
>> >
>> > I need to alter one column which is a part of a table with billions of
>> > rows
>> > in it. What would be the finest and fastest approach to do it?
>> >
>> > Thanks in advance
>> > Manu
>>
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
ManuThis will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:
> Hi,
> I need to alter one column which is a part of a table with billions of rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:
> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> > Hi,
> >
> > I need to alter one column which is a part of a table with billions of
> > rows
> > in it. What would be the finest and fastest approach to do it?
> >
> > Thanks in advance
> > Manu
>
>|||I'm pretty certain it is a meta-data only change (provided you use ALTER TABLE and not the GUI
tool). I suggest you create a table with some million rows and test, just to be certain...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3C68C5CC-0532-4035-9D56-7599FBF9C53C@.microsoft.com...
> The change is just to alter the not null property of a column in this huge
> table to NULL.
> Thanks
> Manu
> "TheSQLGuru" wrote:
>> What, exactly, is the alteration? Some changes could be done more
>> efficiently by bulk exporting data, dropping all structures on table,
>> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
>> Others are nothing more than a meta-data change.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>> > Hi,
>> >
>> > I need to alter one column which is a part of a table with billions of
>> > rows
>> > in it. What would be the finest and fastest approach to do it?
>> >
>> > Thanks in advance
>> > Manu
>>
Monday, March 19, 2012
ALTER TABLE (ADD column question)
Hi All,
I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
column with values?
Have any way to do this?
SQL Server 2005
--
ThanksHi
I cannot test it on SQL Server 2005 right now but I did some testing on SQL
Server 2000
CREATE TABLE #Test (col1 INT)
--Insert some data
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
--Alter table
ALTER TABLE #Test ADD col2 INT IDENTITY(1,1) NOT NULL
GO
ALTER TABLE #Test ADD CONSTRAINT my_coms UNIQUE NONCLUSTERED (col2)
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>|||"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:<#xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl>...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
You can add a non-nullable column by specifying a default and then dropping
it afterwards. Example:
ALTER TABLE tbl
ADD x INTEGER NOT NULL
CONSTRAINT df_tbl_x DEFAULT (0) ;
ALTER TABLE tbl DROP CONSTRAINT df_tbl_x ;
As for adding the unique constraint, obviously you'll have to populate the
column with unique values first. You haven't told us what this data is or
where it comes from so it's hard to help you with that. Is this supposed to
be a surrogate key? Are you aware of the IDENTITY feature in SQL Server?
David Portas
SQL Server MVP
--|||As David Says, If you want to add a new column which is not null, you MUSt
provide a default non null value - otherwise what will the value for the
existing value for rows be - except null... Afterwords, you can drop the
default if you wish...
This may run long, becuase will have to re-write all of the existing rows.
Also watch your tran log..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ReTF" wrote:
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
>|||>> I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE , how I can add this column wit
h values? <<
You can use an ALTER TABLE to add the columns. If you also have a
DEFAULT, you will get a single value; if not, you will get a NULL. Use
the NULL, so you can find problems after the UPDATE.
You have a serious problem because someone missed a key in their data
model. You will need to update the new column with the new key, based
on some rule that matches it to the existing key.
Once those values are in place, you then need to check to see that
there are no NULLs and that all values are unique. Then use another
ALTER TABLE to add UNIQUE NOT NULL constraints.
I did this once when merging two inventory systems that used different
part numbers for the same items. It is a pain and you will probalby
have some errors.|||> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
Not unless you use the IDENTITY property or NEWID().
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136385177.110647.17120@.o13g2000cwo.googlegroups.com...
> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
> You have a serious problem because someone missed a key in their data
> model. You will need to update the new column with the new key, based
> on some rule that matches it to the existing key.
> Once those values are in place, you then need to check to see that
> there are no NULLs and that all values are unique. Then use another
> ALTER TABLE to add UNIQUE NOT NULL constraints.
> I did this once when merging two inventory systems that used different
> part numbers for the same items. It is a pain and you will probalby
> have some errors.
>
I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
column with values?
Have any way to do this?
SQL Server 2005
--
ThanksHi
I cannot test it on SQL Server 2005 right now but I did some testing on SQL
Server 2000
CREATE TABLE #Test (col1 INT)
--Insert some data
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
--Alter table
ALTER TABLE #Test ADD col2 INT IDENTITY(1,1) NOT NULL
GO
ALTER TABLE #Test ADD CONSTRAINT my_coms UNIQUE NONCLUSTERED (col2)
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>|||"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:<#xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl>...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
You can add a non-nullable column by specifying a default and then dropping
it afterwards. Example:
ALTER TABLE tbl
ADD x INTEGER NOT NULL
CONSTRAINT df_tbl_x DEFAULT (0) ;
ALTER TABLE tbl DROP CONSTRAINT df_tbl_x ;
As for adding the unique constraint, obviously you'll have to populate the
column with unique values first. You haven't told us what this data is or
where it comes from so it's hard to help you with that. Is this supposed to
be a surrogate key? Are you aware of the IDENTITY feature in SQL Server?
David Portas
SQL Server MVP
--|||As David Says, If you want to add a new column which is not null, you MUSt
provide a default non null value - otherwise what will the value for the
existing value for rows be - except null... Afterwords, you can drop the
default if you wish...
This may run long, becuase will have to re-write all of the existing rows.
Also watch your tran log..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ReTF" wrote:
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
>|||>> I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE , how I can add this column wit
h values? <<
You can use an ALTER TABLE to add the columns. If you also have a
DEFAULT, you will get a single value; if not, you will get a NULL. Use
the NULL, so you can find problems after the UPDATE.
You have a serious problem because someone missed a key in their data
model. You will need to update the new column with the new key, based
on some rule that matches it to the existing key.
Once those values are in place, you then need to check to see that
there are no NULLs and that all values are unique. Then use another
ALTER TABLE to add UNIQUE NOT NULL constraints.
I did this once when merging two inventory systems that used different
part numbers for the same items. It is a pain and you will probalby
have some errors.|||> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
Not unless you use the IDENTITY property or NEWID().
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136385177.110647.17120@.o13g2000cwo.googlegroups.com...
> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
> You have a serious problem because someone missed a key in their data
> model. You will need to update the new column with the new key, based
> on some rule that matches it to the existing key.
> Once those values are in place, you then need to check to see that
> there are no NULLs and that all values are unique. Then use another
> ALTER TABLE to add UNIQUE NOT NULL constraints.
> I did this once when merging two inventory systems that used different
> part numbers for the same items. It is a pain and you will probalby
> have some errors.
>
Wednesday, March 7, 2012
Alter column to Varchar(max) takes to long
Hi,
I need to modify existing table in my database to varchar(max) from varchar(2000)
This table contains 30 million plus rows and has more than 70 columns.
now when i am running alter command for this it take too long(more than 9 mins) which is not acceptable. . Is their any way to reduce this execution time
Following is the query i am using for this
ALTER TABLE Receipt
ALTER COLUMN CUSTOM VARCHAR(MAX) NULL
Please let me know if you have any suggestion to improve this
TAI
Prashant
Try to add a new column with the new type and then try to do something like:
UPADTE Table
SET
NewCol = Col1,
Col1 = NULL
After that drop the old column. I don′t know if that will save you the additional space the second column will need, but it should be worth a try doing this in one step. If it does not work for you, create a column first copy the data over to the new column, then drop the old one and rename the new one. You will have to do that in a maintaince window to not procude dirty write in the new column.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Alter Column Question
Hello,
I have a table with 10million rows where I need to change the datatype on a
column from int to bigint. I'm assuming this will take a long time to
complete by just going in to Management Studio changing the datatype and
saving.
Any tips on speeding up the process?
Thanks in advance.
Any help appreciated!
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B6BE9290-1C47-4F43-8E87-BE399792BE21@.microsoft.com...
> Hello,
> I have a table with 10million rows where I need to change the datatype on
> a
> column from int to bigint. I'm assuming this will take a long time to
> complete by just going in to Management Studio changing the datatype and
> saving.
> Any tips on speeding up the process?
> Thanks in advance.
> Any help appreciated!
Do not use the Management Studio UI to do such things. Create an ALTER
script, test it in a test environment then run it on your target system. The
Management Studio UI is not the best way because it does some things behind
the scenes that may not be obvious unless you review the commands before you
execute them.
David Portas
I have a table with 10million rows where I need to change the datatype on a
column from int to bigint. I'm assuming this will take a long time to
complete by just going in to Management Studio changing the datatype and
saving.
Any tips on speeding up the process?
Thanks in advance.
Any help appreciated!
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B6BE9290-1C47-4F43-8E87-BE399792BE21@.microsoft.com...
> Hello,
> I have a table with 10million rows where I need to change the datatype on
> a
> column from int to bigint. I'm assuming this will take a long time to
> complete by just going in to Management Studio changing the datatype and
> saving.
> Any tips on speeding up the process?
> Thanks in advance.
> Any help appreciated!
Do not use the Management Studio UI to do such things. Create an ALTER
script, test it in a test environment then run it on your target system. The
Management Studio UI is not the best way because it does some things behind
the scenes that may not be obvious unless you review the commands before you
execute them.
David Portas
Saturday, February 25, 2012
Alter Column Question
Hello,
I have a table with 10million rows where I need to change the datatype on a
column from int to bigint. I'm assuming this will take a long time to
complete by just going in to Management Studio changing the datatype and
saving.
Any tips on speeding up the process?
Thanks in advance.
Any help appreciated!"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B6BE9290-1C47-4F43-8E87-BE399792BE21@.microsoft.com...
> Hello,
> I have a table with 10million rows where I need to change the datatype on
> a
> column from int to bigint. I'm assuming this will take a long time to
> complete by just going in to Management Studio changing the datatype and
> saving.
> Any tips on speeding up the process?
> Thanks in advance.
> Any help appreciated!
Do not use the Management Studio UI to do such things. Create an ALTER
script, test it in a test environment then run it on your target system. The
Management Studio UI is not the best way because it does some things behind
the scenes that may not be obvious unless you review the commands before you
execute them.
--
David Portas
I have a table with 10million rows where I need to change the datatype on a
column from int to bigint. I'm assuming this will take a long time to
complete by just going in to Management Studio changing the datatype and
saving.
Any tips on speeding up the process?
Thanks in advance.
Any help appreciated!"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B6BE9290-1C47-4F43-8E87-BE399792BE21@.microsoft.com...
> Hello,
> I have a table with 10million rows where I need to change the datatype on
> a
> column from int to bigint. I'm assuming this will take a long time to
> complete by just going in to Management Studio changing the datatype and
> saving.
> Any tips on speeding up the process?
> Thanks in advance.
> Any help appreciated!
Do not use the Management Studio UI to do such things. Create an ALTER
script, test it in a test environment then run it on your target system. The
Management Studio UI is not the best way because it does some things behind
the scenes that may not be obvious unless you review the commands before you
execute them.
--
David Portas
Monday, February 13, 2012
Allocations of LOB datatypes
All
We have a very large table (1M+ rows) in which we are
storing large xmls (datalength(column) of 10K) in a text
datatype field.
We are using up space at a much greater rate than
originally planned - and will stop saving the the xml
for certain events. There are about 25 events during the
lifetime of an order.
I am trying to understand how we can get back the space
allocated to this object if we decided to set the column = null. It does not seem to change the information returned
by sp_spaceused even afer using the updateusage flag.
Must I truncate and reload this table to reclaim the space.
(Doing a select into of the table to a new table does seem
to reclaim the space)
Thanks
LBHi Len
Take a look at DBCC CLEANTABLE and see if that helps.
It's documented in Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Len Bearse" <anonymous@.discussions.microsoft.com> wrote in message
news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> All
> We have a very large table (1M+ rows) in which we are
> storing large xmls (datalength(column) of 10K) in a text
> datatype field.
> We are using up space at a much greater rate than
> originally planned - and will stop saving the the xml
> for certain events. There are about 25 events during the
> lifetime of an order.
> I am trying to understand how we can get back the space
> allocated to this object if we decided to set the column => null. It does not seem to change the information returned
> by sp_spaceused even afer using the updateusage flag.
> Must I truncate and reload this table to reclaim the space.
> (Doing a select into of the table to a new table does seem
> to reclaim the space)
> Thanks
> LB
>|||It did not seem to help - please note in my tests
I am just setting the column = null.
>--Original Message--
>Hi Len
>Take a look at DBCC CLEANTABLE and see if that helps.
>It's documented in Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
in message
>news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> All
>> We have a very large table (1M+ rows) in which we are
>> storing large xmls (datalength(column) of 10K) in a text
>> datatype field.
>> We are using up space at a much greater rate than
>> originally planned - and will stop saving the the xml
>> for certain events. There are about 25 events during the
>> lifetime of an order.
>> I am trying to understand how we can get back the space
>> allocated to this object if we decided to set the
column =>> null. It does not seem to change the information
returned
>> by sp_spaceused even afer using the updateusage flag.
>> Must I truncate and reload this table to reclaim the
space.
>> (Doing a select into of the table to a new table does
seem
>> to reclaim the space)
>> Thanks
>> LB
>
>.
>|||Len
Ok, I see. You're not dropping the column, just setting it to null. But,
hey, that's a thought that might be a bit more efficient that a complete
recreate. Drop the column, run dbcc cleantable and then readd the column.
But of course, YMMV and you should test it well first.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:036801c3a577$66680450$a401280a@.phx.gbl...
> It did not seem to help - please note in my tests
> I am just setting the column = null.
>
> >--Original Message--
> >Hi Len
> >
> >Take a look at DBCC CLEANTABLE and see if that helps.
> >It's documented in Books Online.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> >> All
> >> We have a very large table (1M+ rows) in which we are
> >> storing large xmls (datalength(column) of 10K) in a text
> >> datatype field.
> >>
> >> We are using up space at a much greater rate than
> >> originally planned - and will stop saving the the xml
> >> for certain events. There are about 25 events during the
> >> lifetime of an order.
> >>
> >> I am trying to understand how we can get back the space
> >> allocated to this object if we decided to set the
> column => >> null. It does not seem to change the information
> returned
> >> by sp_spaceused even afer using the updateusage flag.
> >>
> >> Must I truncate and reload this table to reclaim the
> space.
> >> (Doing a select into of the table to a new table does
> seem
> >> to reclaim the space)
> >>
> >> Thanks
> >>
> >> LB
> >>
> >
> >
> >.
> >|||I guess the real underlying question is how is space
allocated to the structures the contain the lob data.
I had thought that the b-tree structure would allocate
data in a manner similar to other SQL server objects.
That as pages and extents were deallocated would be freed
and listed as available. It doesn't seem to be working
that way. In testing we don't seem to be freeing the data
to the same degree we are using it up.
I think what I am going to do one of the following
1) No check constraints - bcp table out - truncate table -
bcp table in - re-enable constraints
2) no check constraints - rename table to old_tbl - create
new_tab - insert into new empty table - re-enable
constraints
Len
>--Original Message--
>Len
>Ok, I see. You're not dropping the column, just setting
it to null. But,
>hey, that's a thought that might be a bit more efficient
that a complete
>recreate. Drop the column, run dbcc cleantable and then
readd the column.
>But of course, YMMV and you should test it well first.
>
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:036801c3a577$66680450$a401280a@.phx.gbl...
>> It did not seem to help - please note in my tests
>> I am just setting the column = null.
>>
>> >--Original Message--
>> >Hi Len
>> >
>> >Take a look at DBCC CLEANTABLE and see if that helps.
>> >It's documented in Books Online.
>> >
>> >--
>> >HTH
>> >--
>> >Kalen Delaney
>> >SQL Server MVP
>> >www.SolidQualityLearning.com
>> >
>> >
>> >"Len Bearse" <anonymous@.discussions.microsoft.com>
wrote
>> in message
>> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> >> All
>> >> We have a very large table (1M+ rows) in which we
are
>> >> storing large xmls (datalength(column) of 10K) in a
text
>> >> datatype field.
>> >>
>> >> We are using up space at a much greater rate than
>> >> originally planned - and will stop saving the the xml
>> >> for certain events. There are about 25 events during
the
>> >> lifetime of an order.
>> >>
>> >> I am trying to understand how we can get back the
space
>> >> allocated to this object if we decided to set the
>> column =>> >> null. It does not seem to change the information
>> returned
>> >> by sp_spaceused even afer using the updateusage flag.
>> >>
>> >> Must I truncate and reload this table to reclaim the
>> space.
>> >> (Doing a select into of the table to a new table does
>> seem
>> >> to reclaim the space)
>> >>
>> >> Thanks
>> >>
>> >> LB
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
We have a very large table (1M+ rows) in which we are
storing large xmls (datalength(column) of 10K) in a text
datatype field.
We are using up space at a much greater rate than
originally planned - and will stop saving the the xml
for certain events. There are about 25 events during the
lifetime of an order.
I am trying to understand how we can get back the space
allocated to this object if we decided to set the column = null. It does not seem to change the information returned
by sp_spaceused even afer using the updateusage flag.
Must I truncate and reload this table to reclaim the space.
(Doing a select into of the table to a new table does seem
to reclaim the space)
Thanks
LBHi Len
Take a look at DBCC CLEANTABLE and see if that helps.
It's documented in Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Len Bearse" <anonymous@.discussions.microsoft.com> wrote in message
news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> All
> We have a very large table (1M+ rows) in which we are
> storing large xmls (datalength(column) of 10K) in a text
> datatype field.
> We are using up space at a much greater rate than
> originally planned - and will stop saving the the xml
> for certain events. There are about 25 events during the
> lifetime of an order.
> I am trying to understand how we can get back the space
> allocated to this object if we decided to set the column => null. It does not seem to change the information returned
> by sp_spaceused even afer using the updateusage flag.
> Must I truncate and reload this table to reclaim the space.
> (Doing a select into of the table to a new table does seem
> to reclaim the space)
> Thanks
> LB
>|||It did not seem to help - please note in my tests
I am just setting the column = null.
>--Original Message--
>Hi Len
>Take a look at DBCC CLEANTABLE and see if that helps.
>It's documented in Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
in message
>news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> All
>> We have a very large table (1M+ rows) in which we are
>> storing large xmls (datalength(column) of 10K) in a text
>> datatype field.
>> We are using up space at a much greater rate than
>> originally planned - and will stop saving the the xml
>> for certain events. There are about 25 events during the
>> lifetime of an order.
>> I am trying to understand how we can get back the space
>> allocated to this object if we decided to set the
column =>> null. It does not seem to change the information
returned
>> by sp_spaceused even afer using the updateusage flag.
>> Must I truncate and reload this table to reclaim the
space.
>> (Doing a select into of the table to a new table does
seem
>> to reclaim the space)
>> Thanks
>> LB
>
>.
>|||Len
Ok, I see. You're not dropping the column, just setting it to null. But,
hey, that's a thought that might be a bit more efficient that a complete
recreate. Drop the column, run dbcc cleantable and then readd the column.
But of course, YMMV and you should test it well first.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:036801c3a577$66680450$a401280a@.phx.gbl...
> It did not seem to help - please note in my tests
> I am just setting the column = null.
>
> >--Original Message--
> >Hi Len
> >
> >Take a look at DBCC CLEANTABLE and see if that helps.
> >It's documented in Books Online.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> >> All
> >> We have a very large table (1M+ rows) in which we are
> >> storing large xmls (datalength(column) of 10K) in a text
> >> datatype field.
> >>
> >> We are using up space at a much greater rate than
> >> originally planned - and will stop saving the the xml
> >> for certain events. There are about 25 events during the
> >> lifetime of an order.
> >>
> >> I am trying to understand how we can get back the space
> >> allocated to this object if we decided to set the
> column => >> null. It does not seem to change the information
> returned
> >> by sp_spaceused even afer using the updateusage flag.
> >>
> >> Must I truncate and reload this table to reclaim the
> space.
> >> (Doing a select into of the table to a new table does
> seem
> >> to reclaim the space)
> >>
> >> Thanks
> >>
> >> LB
> >>
> >
> >
> >.
> >|||I guess the real underlying question is how is space
allocated to the structures the contain the lob data.
I had thought that the b-tree structure would allocate
data in a manner similar to other SQL server objects.
That as pages and extents were deallocated would be freed
and listed as available. It doesn't seem to be working
that way. In testing we don't seem to be freeing the data
to the same degree we are using it up.
I think what I am going to do one of the following
1) No check constraints - bcp table out - truncate table -
bcp table in - re-enable constraints
2) no check constraints - rename table to old_tbl - create
new_tab - insert into new empty table - re-enable
constraints
Len
>--Original Message--
>Len
>Ok, I see. You're not dropping the column, just setting
it to null. But,
>hey, that's a thought that might be a bit more efficient
that a complete
>recreate. Drop the column, run dbcc cleantable and then
readd the column.
>But of course, YMMV and you should test it well first.
>
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:036801c3a577$66680450$a401280a@.phx.gbl...
>> It did not seem to help - please note in my tests
>> I am just setting the column = null.
>>
>> >--Original Message--
>> >Hi Len
>> >
>> >Take a look at DBCC CLEANTABLE and see if that helps.
>> >It's documented in Books Online.
>> >
>> >--
>> >HTH
>> >--
>> >Kalen Delaney
>> >SQL Server MVP
>> >www.SolidQualityLearning.com
>> >
>> >
>> >"Len Bearse" <anonymous@.discussions.microsoft.com>
wrote
>> in message
>> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> >> All
>> >> We have a very large table (1M+ rows) in which we
are
>> >> storing large xmls (datalength(column) of 10K) in a
text
>> >> datatype field.
>> >>
>> >> We are using up space at a much greater rate than
>> >> originally planned - and will stop saving the the xml
>> >> for certain events. There are about 25 events during
the
>> >> lifetime of an order.
>> >>
>> >> I am trying to understand how we can get back the
space
>> >> allocated to this object if we decided to set the
>> column =>> >> null. It does not seem to change the information
>> returned
>> >> by sp_spaceused even afer using the updateusage flag.
>> >>
>> >> Must I truncate and reload this table to reclaim the
>> space.
>> >> (Doing a select into of the table to a new table does
>> seem
>> >> to reclaim the space)
>> >>
>> >> Thanks
>> >>
>> >> LB
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
Subscribe to:
Posts (Atom)