Do not SELECT

Hi,
Is there any way how to formulate SQL query to select all columns except
column D and E?
So if the table has the columns A,B,C,D the result woud contain only columns
A,B,C
SELECT " * except D,E"
FROM myTable
Thanks,
Lubomir
SELECT A, B, C
FROM myTable
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||Hello,
If you want it permamanent then you can create a view and then query the
view
CREATE VIEW A1
as
Select A,B,C from myTable
After the creation query the view
Select * from A1
THanks
Hari
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||The problem is, the tables are created on the fly, so I don't know what
columns the particular table will have. I know however, every table has two
columns ("help" columns) that will not be displayed, as they are used for
another purposes.
From that reason, it would be very convenient to make query like SELECT *
and to specify those 2 columns to be exclusive.
Thanks,
Lubomir
"Hari Prasad" wrote:

> Hello,
> If you want it permamanent then you can create a view and then query the
> view
> CREATE VIEW A1
> as
> Select A,B,C from myTable
> After the creation query the view
> Select * from A1
> THanks
> Hari
>
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
>
>
|||On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:

>The problem is, the tables are created on the fly, so I don't know what
>columns the particular table will have.
Hi Lubomir,
That is indeed a problem. And it's also a sign that you are using the
database in a way that it's not intended to be used - applications that
need to create tables on the fly are almost always the result of some
bad design decisions.
Could you explain in some more detail WHY your application does not have
a fixed data model?

>From that reason, it would be very convenient to make query like SELECT *
>and to specify those 2 columns to be exclusive.
There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
vote against it - personally, I'd rather remove the SELECT * than to
extend it!)
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||How do you manage, populate, update, etc., tables that you don't know the
structure of? How do you know you are getting the right number of columns,
with the correct names, in your UI application? How do you know that your
datasets will not break the front end? And if you're just not "displaying"
the help columns, then just don't "display" them. What you do or don't
display in the UI doesn't have to be the same as what you retrieve from the
database.
In all seriousness though, get rid of the SELECT * and start naming your
columns. It will save you a bunch of headaches down the road.
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:8587187A-53B8-4C53-A2DD-2F300FADFF3A@.microsoft.com...[vbcol=seagreen]
> The problem is, the tables are created on the fly, so I don't know what
> columns the particular table will have. I know however, every table has
> two
> columns ("help" columns) that will not be displayed, as they are used for
> another purposes.
> From that reason, it would be very convenient to make query like SELECT *
> and to specify those 2 columns to be exclusive.
> Thanks,
> Lubomir
> "Hari Prasad" wrote:
|||Hi Hugo,
The reason is, that that this application serves more application (like
plugins) with their own tables.
Lubomir
"Hugo Kornelis" wrote:

> On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:
>
> Hi Lubomir,
> That is indeed a problem. And it's also a sign that you are using the
> database in a way that it's not intended to be used - applications that
> need to create tables on the fly are almost always the result of some
> bad design decisions.
> Could you explain in some more detail WHY your application does not have
> a fixed data model?
>
> There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
> vote against it - personally, I'd rather remove the SELECT * than to
> extend it!)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Mon, 7 May 2007 09:38:00 -0700, Lubomir wrote:

>Hi Hugo,
>The reason is, that that this application serves more application (like
>plugins) with their own tables.
Hi Lubomir,
As long as the data requirements for all these plugin applications are
relatively stable, they can each have their own set of tables that
you'll have to design and deploy once (and possibly more often, if and
when requirements change).
If the data requirements can change on a daily basis, you're probably
best advised to move away from relational databases, since they're
really designed to be used with a fixed datamodel.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||If you are talking about SQL 2005, then you can write DDL trigger and
programically create view or procedure wich select all columns from table
exept those 2.
Ramunas
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...[vbcol=seagreen]
> Hi Hugo,
> The reason is, that that this application serves more application (like
> plugins) with their own tables.
> Lubomir
>
> "Hugo Kornelis" wrote:
*[vbcol=seagreen]
|||Yes, that could work.
Thanks,
Lubomir
"Ramunas Balukonis" wrote:

> If you are talking about SQL 2005, then you can write DDL trigger and
> programically create view or procedure wich select all columns from table
> exept those 2.
> Ramunas
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...
> *
>
>

do I need to do my "snapshot" offline

sql2k sp3
Howdy kids. Well by now you know the story as good as I
do. The Subscriber has more columns than the Publisher.
example:
Publisher:
[TransDate] [smalldatetime] NULL ,
Subscriber:
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
So, I can write procs to handle the inserts and updates
after the initial snapshot is done. But for that initial
snapshot Ive got problems. No matter how I get the data to
the Subscriber (dts, insert...select, etc.) , how do I
keep the Subscriber in sync? In other words, while Im
doing my initial population, data will be coming into the
Publisher? How do my process' tell the difference
betwenn "snapshot" and "new" data? I really don't want to
do this off hours. This cant be a new scenario.
TIA, ChrisR
The log reader reads transactions that are committed to the database after
the snapshot agent has bcp'd the data out to the snapshot. Nothing is
missed, guaranteed.
I posted something for you to try to generate the snapshot using the custom
sync object. Does it work?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:975f01c48619$a87ec080$a601280a@.phx.gbl...
> sql2k sp3
> Howdy kids. Well by now you know the story as good as I
> do. The Subscriber has more columns than the Publisher.
> example:
> Publisher:
> [TransDate] [smalldatetime] NULL ,
>
> Subscriber:
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> So, I can write procs to handle the inserts and updates
> after the initial snapshot is done. But for that initial
> snapshot Ive got problems. No matter how I get the data to
> the Subscriber (dts, insert...select, etc.) , how do I
> keep the Subscriber in sync? In other words, while Im
> doing my initial population, data will be coming into the
> Publisher? How do my process' tell the difference
> betwenn "snapshot" and "new" data? I really don't want to
> do this off hours. This cant be a new scenario.
> TIA, ChrisR
>
|||Yes. And it is greatly appreciated. However, you didnt
specify what was supposed to be in
the 'c:\temp\TestWithDifferentSchema.sql'
script. I didn't want to be pushy, so I just kep diggin on
my own. Does that hold the answer to this as well? Ive
done my own since then but couldnt get it to work the way
I wanted.

>--Original Message--
>The log reader reads transactions that are committed to
the database after
>the snapshot agent has bcp'd the data out to the
snapshot. Nothing is
>missed, guaranteed.
>I posted something for you to try to generate the
snapshot using the custom
>sync object. Does it work?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:975f01c48619$a87ec080$a601280a@.phx.gbl...
to[vbcol=seagreen]
the[vbcol=seagreen]
to
>
>.
>
|||I just tried your first example, and mad the schema on the
subscriber match the view on the Publisher. I got an EOF
like with mine.

>--Original Message--
>The log reader reads transactions that are committed to
the database after
>the snapshot agent has bcp'd the data out to the
snapshot. Nothing is
>missed, guaranteed.
>I posted something for you to try to generate the
snapshot using the custom
>sync object. Does it work?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:975f01c48619$a87ec080$a601280a@.phx.gbl...
to[vbcol=seagreen]
the[vbcol=seagreen]
to
>
>.
>

Division Problem

Hi there,

I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.

Any help is appreciated

I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.

If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:

1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1

The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:

1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression

The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.

|||Thanks for the reply.

I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,

Instead of using

<current value> / <Previous value> * 100 >= 10

to get the percentage.

I can use

(<current value> - <Previous value>) >= <current value> * 0.1

However the previous value may be bigger than the current so i also have to check

(<current value> - <Previous value>) <= (<current value> * 0.1) * -1

Therefor there is no division.

Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||

first. . . why two columns. . . this is not normalized. . .

you should have

Table: MonthlyTaskData:
TaskID|MonthID | MonthValue

any other schema is plain wrong!!!

[rant over]

given @.CurrMonthID and @.PrevMonthID

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

|||Sorry, my bad.

This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.

I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.

Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

Division Problem

Hi there,

I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.

Any help is appreciated

I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.

If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:

1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1

The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:

1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression

The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.

|||Thanks for the reply.

I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,

Instead of using

<current value> / <Previous value> * 100 >= 10

to get the percentage.

I can use

(<current value> - <Previous value>) >= <current value> * 0.1

However the previous value may be bigger than the current so i also have to check

(<current value> - <Previous value>) <= (<current value> * 0.1) * -1

Therefor there is no division.

Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||

first. . . why two columns. . . this is not normalized. . .

you should have

Table: MonthlyTaskData:
TaskID|MonthID | MonthValue

any other schema is plain wrong!!!

[rant over]

given @.CurrMonthID and @.PrevMonthID

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

|||Sorry, my bad.

This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.

I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.

Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

Division not returning whole numbers and not decimal values

I'm trying to divide 2 int columns and am expecting a result in a decimal
value, however, only whole values are being returned. First I tried
returning a calculated value from a select statement like this:
CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
INSERT INTO #tmp (col1, col2)
VALUES (7,5)
SELECT col2 / col1
FROM #tmp
But it returned zero. So then I tried to update the calculated value into a
decimal column but I got the same result.
UPDATE #tmp
SET TEST = col2 / col1
SELECT col1, col2, TEST FROM #tmp
Any help on this? THANKS!
moondaddy@.nospam.nospamYou've got to convert at least 1 of the values to a floating-point or decima
l
type first. e.g:
select 4 / 3 -- returns int
select convert(float, 4) / 3 -- returns float
"moondaddy" wrote:

> I'm trying to divide 2 int columns and am expecting a result in a decimal
> value, however, only whole values are being returned. First I tried
> returning a calculated value from a select statement like this:
>
> CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
> INSERT INTO #tmp (col1, col2)
> VALUES (7,5)
> SELECT col2 / col1
> FROM #tmp
> But it returned zero. So then I tried to update the calculated value into
a
> decimal column but I got the same result.
> UPDATE #tmp
> SET TEST = col2 / col1
> SELECT col1, col2, TEST FROM #tmp
> Any help on this? THANKS!
> --
> moondaddy@.nospam.nospam
>
>

Division by zero and computed by column

I've got two integer columns in the table, third one is computed by previous two ones division. That's fine, however sometimes can happen that divided by column is set to zero. How can i avoid division by zero exception, please? TIA

The best way to avoid the problem is to use a CASE statement like:

declare @.aTable table(value1 int, value2 int)
insert into @.aTable values (32, 4)
insert into @.aTable values (5, 0)

select value1,
value2,
case when value2 <> 0 then value1/value2 end
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0 NULL
*/

-- Or maybe:

select value1,
value2,
isnull(convert(varchar(11), case when value2 <> 0 then value1/value2 end), '')
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0
*/

|||

Thank you for your reply.

So you prefer to get the result via select command instead of fixed computed by column, right?

|||I am not sure that I understand that last question; what exactly do you mean?|||

You can use the CASE structure in a computed column definition:

Something like:

Code Snippet

ALTER TABLE MyTable

ADD COLUMN MyComputedCol AS ( CASE WHEN ( [Col1] <> 0 ) THEN ( [Col2] / [Col1] ) END )

|||Thanks for picking me up, Arnie! Again, I am asleep at the wheel! Sheesh! To answer your question, Jan, no, I have no issue against using the computed column. As Anie indicated, the computed column should be just fine. (I'm BRAINDEAD today!)|||Wow, that's amazing. Thank you Kent and Arnie!

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divide
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!
Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/
|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:

> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>
|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divid
e
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:

> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divide
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:
> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Distribution task marvelously slow

Greetings!
We are replicating a table (many of them actually, but one in particular)
which is about 10 GB...660K rows including some text columns. We needed to
change the properties of one column and did not want to pay the penalty of
reinitializing the subscription, so I added a temp column "A_temp", populated
that with the data from the original column "A" then dropped column "A" and
readded it with new properties including NOT NULL. We are then loading "A"
from "A_temp" and then dropping the "A_temp" column. In theory this is fine
and it is working...however the updates are remarkably slow...three records a
second with the sp_MSUpd_... stored procedure. At that rate, the whole
shebang will take days instead of hour or so it would have with the reinit
and snapshot. This is generally the way MS would recommend doing the schema
change from what I have studied. The publisher and distributor are same
server and the subscriber is a remote, but on the LAN, machine.
Any ideas? Thanks so much!
You could check the query plan of the update stored proc - perhaps it would
benefit from different indexes at the subscriber or perhaps the indexes are
fragmented - also worth checking. Finally, check to see if there is any
blocking going on that accounts for the slowdown.
HTH,
Paul Ibison
|||Thank you for your reply. I did notice that the replication update was
blocking other things, but we have rectified that - for now. The actual
update doesn't appear to be blocked at all. We do have the column with a
clustered index on it. Actually thought of dropping indexes to see if it
sped up, but the column which is getting updated is not indexes, so I wasn't
expected a lot out of that. I haven't checked out the query plan...I think
you are right that it is worth a look. I'll see if I can get one of my
admins to toss me an actual sp call instead of having me guess at the
parameters...
Thanks again!
"Paul Ibison" wrote:

> You could check the query plan of the update stored proc - perhaps it would
> benefit from different indexes at the subscriber or perhaps the indexes are
> fragmented - also worth checking. Finally, check to see if there is any
> blocking going on that accounts for the slowdown.
> HTH,
> Paul Ibison
>