Hello,
Within a view I've created, I have combined 2 fields to make 1.
dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
This is for display (to populate a listbox in .NET).
The problem is, if there is nothing in the STR_LAYER field, the whole field
is blank.
Is it possible to display Stratum always, and Layer when it's available?
Thanks!
AmberUse functions ISNULL or COALESCE.
Example:
coalesce(dbo.TABLE1.STR_STRATUM + N' ', N'') +
coalesce(dbo.TABLE2.STR_LAYER, '') AS StratumLayer
AMB
"amber" wrote:
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole fiel
d
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||SELECT dbo.TABLE1.STR_STRATUM + ISNULL( N' ' + dbo.TABLE2.STR_LAYER AS
StratumLayer, '')
Jacco Schalkwijk
SQL Server MVP
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||If you concatenate a string with a null value, it will return null.
Use ISNULL function:
dbo.TABLE1.STR_STRATUM + N' ' + ISNULL(dbo.TABLE2.STR_LAYER ISNULL(), '')
Francesco Anti
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||This worked.
Thanks!
Amber
Showing posts with label combined. Show all posts
Showing posts with label combined. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Combined result...
hi! can anybody please help me...what would be my query string if i want to combine 3 column into one column?
example. I have 3 columns in my customer table namely street,City,postal_code and i want to query that 3 column as address having it combined. thanks in advance.well, daimous, it seems like you did not understand why i moved your previous thread to the microsoft SQL Server forum
so here is the SQL answer --select street||City||postal_code as address
from yourtableif you find that this doesn't work in SQL Server, i trust it will bring to your attention that SQL Server questions should be posted in the SQL Server forum and not the SQL forum
:)|||Try this
Select [street]+', '+[city]+' '+[postal_code] as Address
from YourTable
This assumes that you have [postal_code] defined as a varchar, and not an integer or numeric field. I put in some spaces and a comma, so your output would be something like this:
Street, City Postal_Code|||If you have NULL values in your table and are using default SQL Server settings, you may need to use this:
Select coalesce([street]+', ', '')+Coalesce([city]+' ', '')+Coalesce([postal_code], '') as Address
from YourTable
Now, go open up Books Online and read about concatenation and the COALESCE function.
example. I have 3 columns in my customer table namely street,City,postal_code and i want to query that 3 column as address having it combined. thanks in advance.well, daimous, it seems like you did not understand why i moved your previous thread to the microsoft SQL Server forum
so here is the SQL answer --select street||City||postal_code as address
from yourtableif you find that this doesn't work in SQL Server, i trust it will bring to your attention that SQL Server questions should be posted in the SQL Server forum and not the SQL forum
:)|||Try this
Select [street]+', '+[city]+' '+[postal_code] as Address
from YourTable
This assumes that you have [postal_code] defined as a varchar, and not an integer or numeric field. I put in some spaces and a comma, so your output would be something like this:
Street, City Postal_Code|||If you have NULL values in your table and are using default SQL Server settings, you may need to use this:
Select coalesce([street]+', ', '')+Coalesce([city]+' ', '')+Coalesce([postal_code], '') as Address
from YourTable
Now, go open up Books Online and read about concatenation and the COALESCE function.
Combined Primary Key - Why?
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen>> If (b), then how do I ensure that a combination of ProductID and OrderID
In t-SQL, you'd use a NOT NULL UNIQUE CONSTRAINT on those values.
Anith|||I would normally go with ProductID, OrderID for primary key, as that is the
natural key. If I have to go with a surrogate key, then I'd make sure
there's a unique index/constraint on ProductID, OrderID combination.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen|||You can create a UNIQUE constraint on the combination of ProductID and
OrderID. Although my preference is to have a Primary Key on ProductID and
OrderID, and, if I think a single column (Identity) key is useful, to have
the UNIQUE constraint on the Identity column. In this scenario there is
usually not much need to have an Identity column on the ProductOrders table
though.
Jacco Schalkwijk
SQL Server MVP
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition
> for
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID
> are
> unique?
> Thanks,
> Naveen|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||I wasn't talking about the order of columns within the compound key - but
yes, that's a good point.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23NHEglEPFHA.2520@.tk2msftngp13.phx.gbl...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||There is only one key, (order_id, product_id) by definition. This is
basic RDBMS; a key must be made up of attributes that exist in the data
model. There is no ProductOrderID(mostly identity) in the real world;
it is derived from the internal state of the hardware at insertion
time:
CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Inventory(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
order_id INTEGER NOT NULL
REFERENCES Orders(order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_id, product_id));
Newbies screw up and do it the other way. They can then get duplicates
and destroy the data integrity of the schema.|||This Product Orders table is just a OrderDetails, OrderItems or
InvoiceDetails table..
If you are going have any other tables "hanging off" this table, where you
will need to have a FK referring back to the ProductOrders (I'd call it
OrderDetails)
Then I'd go with b) strictly to avoid having to use composite FKs in other
table(s).
In that event, add another "Alternate" key (using Unique Index, or Unique
Constraint, on (OrderID, ProductID) to ensure uniqueness.
Regardless of whether you also have a single column surrogate key, make the
Composite key the Clustered Index, and if more queries will select data by
OrderID than by ProductID from this table, (THis is normally true for such
tables) use (OrderID, ProductID) as the sequence. But also put another inde
x
on ProductID by itself, for Joins back to Product Table
"Naveen" wrote:
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition fo
r
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID a
re
> unique?
> Thanks,
> Naveensqlsql
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen>> If (b), then how do I ensure that a combination of ProductID and OrderID
In t-SQL, you'd use a NOT NULL UNIQUE CONSTRAINT on those values.
Anith|||I would normally go with ProductID, OrderID for primary key, as that is the
natural key. If I have to go with a surrogate key, then I'd make sure
there's a unique index/constraint on ProductID, OrderID combination.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen|||You can create a UNIQUE constraint on the combination of ProductID and
OrderID. Although my preference is to have a Primary Key on ProductID and
OrderID, and, if I think a single column (Identity) key is useful, to have
the UNIQUE constraint on the Identity column. In this scenario there is
usually not much need to have an Identity column on the ProductOrders table
though.
Jacco Schalkwijk
SQL Server MVP
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition
> for
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID
> are
> unique?
> Thanks,
> Naveen|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||I wasn't talking about the order of columns within the compound key - but
yes, that's a good point.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23NHEglEPFHA.2520@.tk2msftngp13.phx.gbl...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||There is only one key, (order_id, product_id) by definition. This is
basic RDBMS; a key must be made up of attributes that exist in the data
model. There is no ProductOrderID(mostly identity) in the real world;
it is derived from the internal state of the hardware at insertion
time:
CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Inventory(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
order_id INTEGER NOT NULL
REFERENCES Orders(order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_id, product_id));
Newbies screw up and do it the other way. They can then get duplicates
and destroy the data integrity of the schema.|||This Product Orders table is just a OrderDetails, OrderItems or
InvoiceDetails table..
If you are going have any other tables "hanging off" this table, where you
will need to have a FK referring back to the ProductOrders (I'd call it
OrderDetails)
Then I'd go with b) strictly to avoid having to use composite FKs in other
table(s).
In that event, add another "Alternate" key (using Unique Index, or Unique
Constraint, on (OrderID, ProductID) to ensure uniqueness.
Regardless of whether you also have a single column surrogate key, make the
Composite key the Clustered Index, and if more queries will select data by
OrderID than by ProductID from this table, (THis is normally true for such
tables) use (OrderID, ProductID) as the sequence. But also put another inde
x
on ProductID by itself, for Joins back to Product Table
"Naveen" wrote:
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition fo
r
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID a
re
> unique?
> Thanks,
> Naveensqlsql
Combined or separate?
We are trying to decide wether to place SQL Server 2000 on the IIS box or
another separate box.
What are the pros and cons of doing this? I'm thinking something like
performance (shared memory vs. TCP),
memory/cpu consumption of the SQL server. Currently the IIS box has an
average CPU usage of ~10%
without having SQL Server 2000 installed.
CasperIf the box can handle the memory and extra proc, then physically no
problem...
However, it is generally not a good practice to put your SQL on the IIS box
because of the security risk... If someone is able to hack into your IIS
box, then they also get SQL for free...
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
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OVJ0i2POFHA.2384@.tk2msftngp13.phx.gbl...
> We are trying to decide wether to place SQL Server 2000 on the IIS box or
> another separate box.
> What are the pros and cons of doing this? I'm thinking something like
> performance (shared memory vs. TCP),
> memory/cpu consumption of the SQL server. Currently the IIS box has an
> average CPU usage of ~10%
> without having SQL Server 2000 installed.
> Casper
>|||In general the only time this is a good idea, is when implementing a web
farm, and the load on the SQL functionality is a bottleneck to the web
application, and the SQL funtionality (from web app) is read-only.
Say in the case of something like a searh, where the write (update)
functionality is implemented through some kind of publishing on a scheduled,
recurring basis, and the web site (IIS) only reading the local database...
Then the idea of having multiple local copies of the Database, one on each
IIS box in the web farm, can allow you to scale out to any degree
necessary...
"Casper Hornstrup" wrote:
> We are trying to decide wether to place SQL Server 2000 on the IIS box or
> another separate box.
> What are the pros and cons of doing this? I'm thinking something like
> performance (shared memory vs. TCP),
> memory/cpu consumption of the SQL server. Currently the IIS box has an
> average CPU usage of ~10%
> without having SQL Server 2000 installed.
> Casper
>
>|||So, is this because using shared memory don't make a significant difference
in performance or because there are other di
vantages that outweigh that
performance increase?
Casper
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:8395D217-1EAD-42D0-BB69-4BB617A2F5C4@.microsoft.com...
> In general the only time this is a good idea, is when implementing a web
> farm, and the load on the SQL functionality is a bottleneck to the web
> application, and the SQL funtionality (from web app) is read-only.
> Say in the case of something like a searh, where the write (update)
> functionality is implemented through some kind of publishing on a
scheduled,
> recurring basis, and the web site (IIS) only reading the local database...
> Then the idea of having multiple local copies of the Database, one on each
> IIS box in the web farm, can allow you to scale out to any degree
> necessary...|||I would suggest that there are many reasons to separate SQL
and IIS onto different machines. As Wayne said, there is a
security risk with putting SQL on the same machine as IIS.
There is also the issue of licenses if you have multiple web
servers (and thus multiple SQL Server instances). Then there
is the idea of isolating the bottlenecks. If IIS is the
bottleneck, you can upgrade your IIS servers with more
memory, for example, without having to touch your SQL boxes.
If SQL's drive array is the bottleneck, you can upgrade one
array (assuming one SQL Server and multiple IIS machines)
without having to update all of your IIS machines. If SQL's
memory and/or CPU are the bottleneck, you can again upgrade
the SQL box while using inexpensive servers for your IIS
servers. If you need better read scalability, then you can
create a shared-none cluster of SQL servers separately from
how you handle IIS.
In short, I would suggest that only in the simplest designs
or most extreme, esoteric reasons should anyone consider
putting SQL on the same box as IIS.
Thomas
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OVJ0i2POFHA.2384@.tk2msftngp13.phx.gbl...
> We are trying to decide wether to place SQL Server 2000 on
> the IIS box or
> another separate box.
> What are the pros and cons of doing this? I'm thinking
> something like
> performance (shared memory vs. TCP),
> memory/cpu consumption of the SQL server. Currently the
> IIS box has an
> average CPU usage of ~10%
> without having SQL Server 2000 installed.
> Casper
>|||Shared memory is not the issue, unless the database is very small, the data
will be on disk at least part of the time... The issue, is that in any
production system, there is a chance that you will need more performace as
the business grows, and f your database system proovides OLTP (OnLine
Transaction Processing) functionality, which requires large numbers of
read/write operations, then you cannot <easily> scale up the performance whe
n
the database is "Copies" in multiple places. The "Writes" become a massive
issue. Reads are no problem, because you can replicate the data to multiple
instances...
So if you design and architect your system around having IIS AND SQL On the
same box, and your company never needs morethan one IIS/SQL box to service
it's business, then you're fine... But if yu need to increase performance, a
s
soon as you need to add another IIS box, (and create a web farm) you will
need to put the SQL on it's own separate box...
Again, the exception is if the SQL functionality used by the web app is
readonly...
"Casper Hornstrup" wrote:
> So, is this because using shared memory don't make a significant differenc
e
> in performance or because there are other divantages that outweigh that
> performance increase?
> Casper
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:8395D217-1EAD-42D0-BB69-4BB617A2F5C4@.microsoft.com...
> scheduled,
>
>
another separate box.
What are the pros and cons of doing this? I'm thinking something like
performance (shared memory vs. TCP),
memory/cpu consumption of the SQL server. Currently the IIS box has an
average CPU usage of ~10%
without having SQL Server 2000 installed.
CasperIf the box can handle the memory and extra proc, then physically no
problem...
However, it is generally not a good practice to put your SQL on the IIS box
because of the security risk... If someone is able to hack into your IIS
box, then they also get SQL for free...
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
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OVJ0i2POFHA.2384@.tk2msftngp13.phx.gbl...
> We are trying to decide wether to place SQL Server 2000 on the IIS box or
> another separate box.
> What are the pros and cons of doing this? I'm thinking something like
> performance (shared memory vs. TCP),
> memory/cpu consumption of the SQL server. Currently the IIS box has an
> average CPU usage of ~10%
> without having SQL Server 2000 installed.
> Casper
>|||In general the only time this is a good idea, is when implementing a web
farm, and the load on the SQL functionality is a bottleneck to the web
application, and the SQL funtionality (from web app) is read-only.
Say in the case of something like a searh, where the write (update)
functionality is implemented through some kind of publishing on a scheduled,
recurring basis, and the web site (IIS) only reading the local database...
Then the idea of having multiple local copies of the Database, one on each
IIS box in the web farm, can allow you to scale out to any degree
necessary...
"Casper Hornstrup" wrote:
> We are trying to decide wether to place SQL Server 2000 on the IIS box or
> another separate box.
> What are the pros and cons of doing this? I'm thinking something like
> performance (shared memory vs. TCP),
> memory/cpu consumption of the SQL server. Currently the IIS box has an
> average CPU usage of ~10%
> without having SQL Server 2000 installed.
> Casper
>
>|||So, is this because using shared memory don't make a significant difference
in performance or because there are other di
vantages that outweigh thatperformance increase?
Casper
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:8395D217-1EAD-42D0-BB69-4BB617A2F5C4@.microsoft.com...
> In general the only time this is a good idea, is when implementing a web
> farm, and the load on the SQL functionality is a bottleneck to the web
> application, and the SQL funtionality (from web app) is read-only.
> Say in the case of something like a searh, where the write (update)
> functionality is implemented through some kind of publishing on a
scheduled,
> recurring basis, and the web site (IIS) only reading the local database...
> Then the idea of having multiple local copies of the Database, one on each
> IIS box in the web farm, can allow you to scale out to any degree
> necessary...|||I would suggest that there are many reasons to separate SQL
and IIS onto different machines. As Wayne said, there is a
security risk with putting SQL on the same machine as IIS.
There is also the issue of licenses if you have multiple web
servers (and thus multiple SQL Server instances). Then there
is the idea of isolating the bottlenecks. If IIS is the
bottleneck, you can upgrade your IIS servers with more
memory, for example, without having to touch your SQL boxes.
If SQL's drive array is the bottleneck, you can upgrade one
array (assuming one SQL Server and multiple IIS machines)
without having to update all of your IIS machines. If SQL's
memory and/or CPU are the bottleneck, you can again upgrade
the SQL box while using inexpensive servers for your IIS
servers. If you need better read scalability, then you can
create a shared-none cluster of SQL servers separately from
how you handle IIS.
In short, I would suggest that only in the simplest designs
or most extreme, esoteric reasons should anyone consider
putting SQL on the same box as IIS.
Thomas
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OVJ0i2POFHA.2384@.tk2msftngp13.phx.gbl...
> We are trying to decide wether to place SQL Server 2000 on
> the IIS box or
> another separate box.
> What are the pros and cons of doing this? I'm thinking
> something like
> performance (shared memory vs. TCP),
> memory/cpu consumption of the SQL server. Currently the
> IIS box has an
> average CPU usage of ~10%
> without having SQL Server 2000 installed.
> Casper
>|||Shared memory is not the issue, unless the database is very small, the data
will be on disk at least part of the time... The issue, is that in any
production system, there is a chance that you will need more performace as
the business grows, and f your database system proovides OLTP (OnLine
Transaction Processing) functionality, which requires large numbers of
read/write operations, then you cannot <easily> scale up the performance whe
n
the database is "Copies" in multiple places. The "Writes" become a massive
issue. Reads are no problem, because you can replicate the data to multiple
instances...
So if you design and architect your system around having IIS AND SQL On the
same box, and your company never needs morethan one IIS/SQL box to service
it's business, then you're fine... But if yu need to increase performance, a
s
soon as you need to add another IIS box, (and create a web farm) you will
need to put the SQL on it's own separate box...
Again, the exception is if the SQL functionality used by the web app is
readonly...
"Casper Hornstrup" wrote:
> So, is this because using shared memory don't make a significant differenc
e
> in performance or because there are other di
vantages that outweigh that> performance increase?
> Casper
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:8395D217-1EAD-42D0-BB69-4BB617A2F5C4@.microsoft.com...
> scheduled,
>
>
combined merge + custom conflict resolution
I have what seems to be a unique case, but logically, would think that
someone out there is already doing this. I have the following configuration:
- merge replication
- column level tracking
- single pub./dist. server replicating with a single subscriber
- SQL Server 2000 Ent. on Windows 2000 Adv. Server
I have succesfully implemented replication, and data is merged properly in
both directions. However, when confilcts occurr, I want the ability to allow
those records that would merge accordingly (not in conflict), but for those
columns in conflict, I want the ability to run a stored procedure with my
proper business logic. Is there a way to do this? When using a stored proc
for conflict resolution, you are forced to return the "winning values" for
all the fields. This is where I lose the data that would have normally
replicated that was not in conflict. In my stored proc, I wish I had the
ability to see or read from a table the data values that would normally be
merged that were not in conflict, so I could supply them in my returned
recordset. I have the logic of reading both Subscriber and Publisher field
values for the current rowguid, but I don't know which of the two values (sub
or pub) has been edited so that it can merge.
Am I explaining this properly? I currently have an open instance with
Microsoft tech support ($250 !!), but they have yet to come back with a
solution. They have had the specs to my issue for a week now, and they have
not been able to resolve it. Maybe someone out there has done this. Thanks
in advance for all the help!
This type of logic is possible using merge replication, but you will have to
hack into the stored procedures that the merge agent uses to apply the
changes to the subscriber and publisher.
However, I think bi-directional transactional replication is a better choice
for implementing this form of custom business logic. When you use XCALL the
before and after images of the data will flow from the Publisher to the
Subscriber and you can incorporate logic to handle your conflicts this way.
So lets consider what a conflict is. A conflict is
1) a pk violation, trying to insert a PK value where a row with that PK
value already exists on the subscriber
2) trying to update a row on the Subscriber and instead of updating a single
row you update 0 or more than one rows.
3) trying to delete a row on the Subscriber and instead of deleting a single
row you delete 0 or more than one rows.
So, before your insert proc fires it will do an existence check. If the row
exists, it can instead do an update which will incorporate your custom
business logic. If the update ends up updating more than one row you have to
consider what is going on. This probably violates your database integrity,
but may not depending on how your database is set up or what you are trying
to accomplish. For instance consider you are replicating to audit table. A
row with a PK value of 1 may be inserted at the publisher and replicated to
the Subscriber. Then this row is deleted at the publisher, but this delete
is not replicated to the subscriber (remember the subscriber is an audit
table, so we need a record of that row). Then this row is readded with the
same PK (1) and replicate to the subscriber. If you haven't handled the
possibility of duplicate PK's you will have a problem.
Updates and deletes are simple as long as you can preserve the one to one
mapping of rows on the Publisher to the Subsriber. If you are doing a one to
many from the Publisher to the Subscriber it gets more complex, but it is
not impossible.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> I have what seems to be a unique case, but logically, would think that
> someone out there is already doing this. I have the following
configuration:
> - merge replication
> - column level tracking
> - single pub./dist. server replicating with a single subscriber
> - SQL Server 2000 Ent. on Windows 2000 Adv. Server
> I have succesfully implemented replication, and data is merged properly in
> both directions. However, when confilcts occurr, I want the ability to
allow
> those records that would merge accordingly (not in conflict), but for
those
> columns in conflict, I want the ability to run a stored procedure with my
> proper business logic. Is there a way to do this? When using a stored
proc
> for conflict resolution, you are forced to return the "winning values" for
> all the fields. This is where I lose the data that would have normally
> replicated that was not in conflict. In my stored proc, I wish I had the
> ability to see or read from a table the data values that would normally be
> merged that were not in conflict, so I could supply them in my returned
> recordset. I have the logic of reading both Subscriber and Publisher
field
> values for the current rowguid, but I don't know which of the two values
(sub
> or pub) has been edited so that it can merge.
> Am I explaining this properly? I currently have an open instance with
> Microsoft tech support ($250 !!), but they have yet to come back with a
> solution. They have had the specs to my issue for a week now, and they
have
> not been able to resolve it. Maybe someone out there has done this.
Thanks
> in advance for all the help!
>
|||being that I am only capable to connect at night, I was under the impression
that Merge Replication was my only option. Transaction Replication os for
constant connection, right? If I am wrong, please advise. As for the
remainder of your comments, I am not that familiar with Replication, to
understand everything you are referring to. If you know of any publications
or web sites that can help educate me, that would be great. So far, the web
has seemed to be kinda light on info that deals with the deep intricate
details regarding replication. It brushes lightly on the topic, but thats
it. Thanks so much for your assistance. I have been stuck with this for a
week now. Its frustrating.
"Hilary Cotter" wrote:
> This type of logic is possible using merge replication, but you will have to
> hack into the stored procedures that the merge agent uses to apply the
> changes to the subscriber and publisher.
> However, I think bi-directional transactional replication is a better choice
> for implementing this form of custom business logic. When you use XCALL the
> before and after images of the data will flow from the Publisher to the
> Subscriber and you can incorporate logic to handle your conflicts this way.
> So lets consider what a conflict is. A conflict is
> 1) a pk violation, trying to insert a PK value where a row with that PK
> value already exists on the subscriber
> 2) trying to update a row on the Subscriber and instead of updating a single
> row you update 0 or more than one rows.
> 3) trying to delete a row on the Subscriber and instead of deleting a single
> row you delete 0 or more than one rows.
> So, before your insert proc fires it will do an existence check. If the row
> exists, it can instead do an update which will incorporate your custom
> business logic. If the update ends up updating more than one row you have to
> consider what is going on. This probably violates your database integrity,
> but may not depending on how your database is set up or what you are trying
> to accomplish. For instance consider you are replicating to audit table. A
> row with a PK value of 1 may be inserted at the publisher and replicated to
> the Subscriber. Then this row is deleted at the publisher, but this delete
> is not replicated to the subscriber (remember the subscriber is an audit
> table, so we need a record of that row). Then this row is readded with the
> same PK (1) and replicate to the subscriber. If you haven't handled the
> possibility of duplicate PK's you will have a problem.
> Updates and deletes are simple as long as you can preserve the one to one
> mapping of rows on the Publisher to the Subsriber. If you are doing a one to
> many from the Publisher to the Subscriber it gets more complex, but it is
> not impossible.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> configuration:
> allow
> those
> proc
> field
> (sub
> have
> Thanks
>
>
|||While you can use bi-directional transactional replication in a disconnected
manner it is not really advisable to do so, as the conflict tracking and
resolving mechanisms are essentially non-existent.
If you get a conflict your distribution agent will fail and you will have to
resolve the conflict and restart the agent. If you have many conflicts this
is not acceptable, but if you have few conflicts or you can design your
replication solution to minimize conflicts this might be a solution for you.
Again, I don't know your data, data flow, or topology so I can't really
advise you on it. You know it more intimately than I so you should make the
decision on this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> being that I am only capable to connect at night, I was under the
impression
> that Merge Replication was my only option. Transaction Replication os for
> constant connection, right? If I am wrong, please advise. As for the
> remainder of your comments, I am not that familiar with Replication, to
> understand everything you are referring to. If you know of any
publications
> or web sites that can help educate me, that would be great. So far, the
web
> has seemed to be kinda light on info that deals with the deep intricate
> details regarding replication. It brushes lightly on the topic, but thats
> it. Thanks so much for your assistance. I have been stuck with this for
a[vbcol=seagreen]
> week now. Its frustrating.
> "Hilary Cotter" wrote:
have to[vbcol=seagreen]
choice[vbcol=seagreen]
the[vbcol=seagreen]
way.[vbcol=seagreen]
single[vbcol=seagreen]
single[vbcol=seagreen]
row[vbcol=seagreen]
have to[vbcol=seagreen]
integrity,[vbcol=seagreen]
trying[vbcol=seagreen]
A[vbcol=seagreen]
to[vbcol=seagreen]
delete[vbcol=seagreen]
the[vbcol=seagreen]
one[vbcol=seagreen]
one to[vbcol=seagreen]
is[vbcol=seagreen]
properly in[vbcol=seagreen]
to[vbcol=seagreen]
my[vbcol=seagreen]
stored[vbcol=seagreen]
for[vbcol=seagreen]
normally[vbcol=seagreen]
the[vbcol=seagreen]
normally be[vbcol=seagreen]
returned[vbcol=seagreen]
values[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
|||I appreciate your feedback Hilary. I do think that bi-directional
transactional replication is not an option for me, due to the number of
conflicts anticipated. I hope that Microsoft's support people will help me
find the right answer. Its costing me $250, so they better! :-)
Thanks again. I enjoy your particiapation on this discussion board. Its a
great educational tool!
"Hilary Cotter" wrote:
> While you can use bi-directional transactional replication in a disconnected
> manner it is not really advisable to do so, as the conflict tracking and
> resolving mechanisms are essentially non-existent.
> If you get a conflict your distribution agent will fail and you will have to
> resolve the conflict and restart the agent. If you have many conflicts this
> is not acceptable, but if you have few conflicts or you can design your
> replication solution to minimize conflicts this might be a solution for you.
> Again, I don't know your data, data flow, or topology so I can't really
> advise you on it. You know it more intimately than I so you should make the
> decision on this.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> impression
> publications
> web
> a
> have to
> choice
> the
> way.
> single
> single
> row
> have to
> integrity,
> trying
> A
> to
> delete
> the
> one
> one to
> is
> properly in
> to
> my
> stored
> for
> normally
> the
> normally be
> returned
> values
> a
> they
>
>
someone out there is already doing this. I have the following configuration:
- merge replication
- column level tracking
- single pub./dist. server replicating with a single subscriber
- SQL Server 2000 Ent. on Windows 2000 Adv. Server
I have succesfully implemented replication, and data is merged properly in
both directions. However, when confilcts occurr, I want the ability to allow
those records that would merge accordingly (not in conflict), but for those
columns in conflict, I want the ability to run a stored procedure with my
proper business logic. Is there a way to do this? When using a stored proc
for conflict resolution, you are forced to return the "winning values" for
all the fields. This is where I lose the data that would have normally
replicated that was not in conflict. In my stored proc, I wish I had the
ability to see or read from a table the data values that would normally be
merged that were not in conflict, so I could supply them in my returned
recordset. I have the logic of reading both Subscriber and Publisher field
values for the current rowguid, but I don't know which of the two values (sub
or pub) has been edited so that it can merge.
Am I explaining this properly? I currently have an open instance with
Microsoft tech support ($250 !!), but they have yet to come back with a
solution. They have had the specs to my issue for a week now, and they have
not been able to resolve it. Maybe someone out there has done this. Thanks
in advance for all the help!
This type of logic is possible using merge replication, but you will have to
hack into the stored procedures that the merge agent uses to apply the
changes to the subscriber and publisher.
However, I think bi-directional transactional replication is a better choice
for implementing this form of custom business logic. When you use XCALL the
before and after images of the data will flow from the Publisher to the
Subscriber and you can incorporate logic to handle your conflicts this way.
So lets consider what a conflict is. A conflict is
1) a pk violation, trying to insert a PK value where a row with that PK
value already exists on the subscriber
2) trying to update a row on the Subscriber and instead of updating a single
row you update 0 or more than one rows.
3) trying to delete a row on the Subscriber and instead of deleting a single
row you delete 0 or more than one rows.
So, before your insert proc fires it will do an existence check. If the row
exists, it can instead do an update which will incorporate your custom
business logic. If the update ends up updating more than one row you have to
consider what is going on. This probably violates your database integrity,
but may not depending on how your database is set up or what you are trying
to accomplish. For instance consider you are replicating to audit table. A
row with a PK value of 1 may be inserted at the publisher and replicated to
the Subscriber. Then this row is deleted at the publisher, but this delete
is not replicated to the subscriber (remember the subscriber is an audit
table, so we need a record of that row). Then this row is readded with the
same PK (1) and replicate to the subscriber. If you haven't handled the
possibility of duplicate PK's you will have a problem.
Updates and deletes are simple as long as you can preserve the one to one
mapping of rows on the Publisher to the Subsriber. If you are doing a one to
many from the Publisher to the Subscriber it gets more complex, but it is
not impossible.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> I have what seems to be a unique case, but logically, would think that
> someone out there is already doing this. I have the following
configuration:
> - merge replication
> - column level tracking
> - single pub./dist. server replicating with a single subscriber
> - SQL Server 2000 Ent. on Windows 2000 Adv. Server
> I have succesfully implemented replication, and data is merged properly in
> both directions. However, when confilcts occurr, I want the ability to
allow
> those records that would merge accordingly (not in conflict), but for
those
> columns in conflict, I want the ability to run a stored procedure with my
> proper business logic. Is there a way to do this? When using a stored
proc
> for conflict resolution, you are forced to return the "winning values" for
> all the fields. This is where I lose the data that would have normally
> replicated that was not in conflict. In my stored proc, I wish I had the
> ability to see or read from a table the data values that would normally be
> merged that were not in conflict, so I could supply them in my returned
> recordset. I have the logic of reading both Subscriber and Publisher
field
> values for the current rowguid, but I don't know which of the two values
(sub
> or pub) has been edited so that it can merge.
> Am I explaining this properly? I currently have an open instance with
> Microsoft tech support ($250 !!), but they have yet to come back with a
> solution. They have had the specs to my issue for a week now, and they
have
> not been able to resolve it. Maybe someone out there has done this.
Thanks
> in advance for all the help!
>
|||being that I am only capable to connect at night, I was under the impression
that Merge Replication was my only option. Transaction Replication os for
constant connection, right? If I am wrong, please advise. As for the
remainder of your comments, I am not that familiar with Replication, to
understand everything you are referring to. If you know of any publications
or web sites that can help educate me, that would be great. So far, the web
has seemed to be kinda light on info that deals with the deep intricate
details regarding replication. It brushes lightly on the topic, but thats
it. Thanks so much for your assistance. I have been stuck with this for a
week now. Its frustrating.
"Hilary Cotter" wrote:
> This type of logic is possible using merge replication, but you will have to
> hack into the stored procedures that the merge agent uses to apply the
> changes to the subscriber and publisher.
> However, I think bi-directional transactional replication is a better choice
> for implementing this form of custom business logic. When you use XCALL the
> before and after images of the data will flow from the Publisher to the
> Subscriber and you can incorporate logic to handle your conflicts this way.
> So lets consider what a conflict is. A conflict is
> 1) a pk violation, trying to insert a PK value where a row with that PK
> value already exists on the subscriber
> 2) trying to update a row on the Subscriber and instead of updating a single
> row you update 0 or more than one rows.
> 3) trying to delete a row on the Subscriber and instead of deleting a single
> row you delete 0 or more than one rows.
> So, before your insert proc fires it will do an existence check. If the row
> exists, it can instead do an update which will incorporate your custom
> business logic. If the update ends up updating more than one row you have to
> consider what is going on. This probably violates your database integrity,
> but may not depending on how your database is set up or what you are trying
> to accomplish. For instance consider you are replicating to audit table. A
> row with a PK value of 1 may be inserted at the publisher and replicated to
> the Subscriber. Then this row is deleted at the publisher, but this delete
> is not replicated to the subscriber (remember the subscriber is an audit
> table, so we need a record of that row). Then this row is readded with the
> same PK (1) and replicate to the subscriber. If you haven't handled the
> possibility of duplicate PK's you will have a problem.
> Updates and deletes are simple as long as you can preserve the one to one
> mapping of rows on the Publisher to the Subsriber. If you are doing a one to
> many from the Publisher to the Subscriber it gets more complex, but it is
> not impossible.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> configuration:
> allow
> those
> proc
> field
> (sub
> have
> Thanks
>
>
|||While you can use bi-directional transactional replication in a disconnected
manner it is not really advisable to do so, as the conflict tracking and
resolving mechanisms are essentially non-existent.
If you get a conflict your distribution agent will fail and you will have to
resolve the conflict and restart the agent. If you have many conflicts this
is not acceptable, but if you have few conflicts or you can design your
replication solution to minimize conflicts this might be a solution for you.
Again, I don't know your data, data flow, or topology so I can't really
advise you on it. You know it more intimately than I so you should make the
decision on this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> being that I am only capable to connect at night, I was under the
impression
> that Merge Replication was my only option. Transaction Replication os for
> constant connection, right? If I am wrong, please advise. As for the
> remainder of your comments, I am not that familiar with Replication, to
> understand everything you are referring to. If you know of any
publications
> or web sites that can help educate me, that would be great. So far, the
web
> has seemed to be kinda light on info that deals with the deep intricate
> details regarding replication. It brushes lightly on the topic, but thats
> it. Thanks so much for your assistance. I have been stuck with this for
a[vbcol=seagreen]
> week now. Its frustrating.
> "Hilary Cotter" wrote:
have to[vbcol=seagreen]
choice[vbcol=seagreen]
the[vbcol=seagreen]
way.[vbcol=seagreen]
single[vbcol=seagreen]
single[vbcol=seagreen]
row[vbcol=seagreen]
have to[vbcol=seagreen]
integrity,[vbcol=seagreen]
trying[vbcol=seagreen]
A[vbcol=seagreen]
to[vbcol=seagreen]
delete[vbcol=seagreen]
the[vbcol=seagreen]
one[vbcol=seagreen]
one to[vbcol=seagreen]
is[vbcol=seagreen]
properly in[vbcol=seagreen]
to[vbcol=seagreen]
my[vbcol=seagreen]
stored[vbcol=seagreen]
for[vbcol=seagreen]
normally[vbcol=seagreen]
the[vbcol=seagreen]
normally be[vbcol=seagreen]
returned[vbcol=seagreen]
values[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
|||I appreciate your feedback Hilary. I do think that bi-directional
transactional replication is not an option for me, due to the number of
conflicts anticipated. I hope that Microsoft's support people will help me
find the right answer. Its costing me $250, so they better! :-)
Thanks again. I enjoy your particiapation on this discussion board. Its a
great educational tool!
"Hilary Cotter" wrote:
> While you can use bi-directional transactional replication in a disconnected
> manner it is not really advisable to do so, as the conflict tracking and
> resolving mechanisms are essentially non-existent.
> If you get a conflict your distribution agent will fail and you will have to
> resolve the conflict and restart the agent. If you have many conflicts this
> is not acceptable, but if you have few conflicts or you can design your
> replication solution to minimize conflicts this might be a solution for you.
> Again, I don't know your data, data flow, or topology so I can't really
> advise you on it. You know it more intimately than I so you should make the
> decision on this.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> impression
> publications
> web
> a
> have to
> choice
> the
> way.
> single
> single
> row
> have to
> integrity,
> trying
> A
> to
> delete
> the
> one
> one to
> is
> properly in
> to
> my
> stored
> for
> normally
> the
> normally be
> returned
> values
> a
> they
>
>
Combined line and column graph?
I have a great column graph, but I was wondering if there were any options
on the graph so I add a trend line?
Or maybe there's an option I'm missing on the line graph.You can combine a column and a line chart. Just set the chart type to column
and look for the "Plot data as line" checkbox on the data value appearance
tab. You may also be interested in this how to article:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:e3c8GoorFHA.3264@.TK2MSFTNGP10.phx.gbl...
>I have a great column graph, but I was wondering if there were any options
> on the graph so I add a trend line?
> Or maybe there's an option I'm missing on the line graph.
>
on the graph so I add a trend line?
Or maybe there's an option I'm missing on the line graph.You can combine a column and a line chart. Just set the chart type to column
and look for the "Plot data as line" checkbox on the data value appearance
tab. You may also be interested in this how to article:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:e3c8GoorFHA.3264@.TK2MSFTNGP10.phx.gbl...
>I have a great column graph, but I was wondering if there were any options
> on the graph so I add a trend line?
> Or maybe there's an option I'm missing on the line graph.
>
Combined into one select
Hi all,
I have the following:
select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1
Is there anyway to combined them into one query? So I get one result?
Thanks,
KenPlace UNION between the select statements.
Originally posted by GA_KEN
Hi all,
I have the following:
select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1
Is there anyway to combined them into one query? So I get one result?
Thanks,
Ken|||Union sort of worked, but I need the data in columns, union gave me rows.|||try this
select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) as UNDER25_12_SINGLE ,
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) as UNDER25_12_MULTIPLE
:
:|||Try this:
select * from
(select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1) as A,
(select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1) as B,
(select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1) as C,
(select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1) as D,
(select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1) as E,
(select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1) as F
Originally posted by GA_KEN
Union sort of worked, but I need the data in columns, union gave me rows.|||Originally posted by msieben
try this
select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) as UNDER25_12_SINGLE ,
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) as UNDER25_12_MULTIPLE
:
:
This works just like I wanted! I knew there had to be a way! I've been pulling my hair out trying to get it to work!|||You guys are awesome!!
Thanks for all your help!
Ken|||select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) AS UNDER25_12_SINGLE
,(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) AS UNDER25_12_MULTIPLE
,(select count([Account Number]) from OH_UNDER25_36MONTHS where AccountCount = 1) AS UNDER25_36_SINGLE
,(select count([Account Number]) from OH_UNDER25_36MONTHS where AccountCount > 1) AS UNDER25_36_MULTIPLE
,(select count([Account Number]) from OH_UNDER25_60MONTHS where AccountCount = 1) AS UNDER25_60_SINGLE
,(select count([Account Number]) from OH_UNDER25_60MONTHS where AccountCount > 1) AS UNDER25_60_MULTIPLE
Good luck !
I have the following:
select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1
Is there anyway to combined them into one query? So I get one result?
Thanks,
KenPlace UNION between the select statements.
Originally posted by GA_KEN
Hi all,
I have the following:
select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1
Is there anyway to combined them into one query? So I get one result?
Thanks,
Ken|||Union sort of worked, but I need the data in columns, union gave me rows.|||try this
select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) as UNDER25_12_SINGLE ,
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) as UNDER25_12_MULTIPLE
:
:|||Try this:
select * from
(select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1) as A,
(select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1) as B,
(select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1) as C,
(select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1) as D,
(select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1) as E,
(select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1) as F
Originally posted by GA_KEN
Union sort of worked, but I need the data in columns, union gave me rows.|||Originally posted by msieben
try this
select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) as UNDER25_12_SINGLE ,
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) as UNDER25_12_MULTIPLE
:
:
This works just like I wanted! I knew there had to be a way! I've been pulling my hair out trying to get it to work!|||You guys are awesome!!
Thanks for all your help!
Ken|||select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) AS UNDER25_12_SINGLE
,(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) AS UNDER25_12_MULTIPLE
,(select count([Account Number]) from OH_UNDER25_36MONTHS where AccountCount = 1) AS UNDER25_36_SINGLE
,(select count([Account Number]) from OH_UNDER25_36MONTHS where AccountCount > 1) AS UNDER25_36_MULTIPLE
,(select count([Account Number]) from OH_UNDER25_60MONTHS where AccountCount = 1) AS UNDER25_60_SINGLE
,(select count([Account Number]) from OH_UNDER25_60MONTHS where AccountCount > 1) AS UNDER25_60_MULTIPLE
Good luck !
Combined Index not using in SQL 7.0 SP4
Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
--
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
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binusqlsql
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
--
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
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binusqlsql
Combined Index not using in SQL 7.0 SP4
Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks Binu
The optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
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
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>
|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks Binu
The optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
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
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>
|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
Combined Index not using in SQL 7.0 SP4
Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
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
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index no
t
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
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
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index no
t
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
Combined Chart (One series with Line and One Bar)
Is it possible to create combined charts with the Report Designer Chart?
I want to have one series charted as bar (sales) and the other series as
line (i.e market share).
Want to do it in one chart (not to overlay one chart over the other)
Thanx.Please read this related newsgroup posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Minas Papageorgiou" <MinasPapageorgiou@.discussions.microsoft.com> wrote in
message news:8AD71606-450E-4E5D-B4DD-12CA9A4A49C5@.microsoft.com...
> Is it possible to create combined charts with the Report Designer Chart?
> I want to have one series charted as bar (sales) and the other series as
> line (i.e market share).
> Want to do it in one chart (not to overlay one chart over the other)
> Thanx.
>
I want to have one series charted as bar (sales) and the other series as
line (i.e market share).
Want to do it in one chart (not to overlay one chart over the other)
Thanx.Please read this related newsgroup posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Minas Papageorgiou" <MinasPapageorgiou@.discussions.microsoft.com> wrote in
message news:8AD71606-450E-4E5D-B4DD-12CA9A4A49C5@.microsoft.com...
> Is it possible to create combined charts with the Report Designer Chart?
> I want to have one series charted as bar (sales) and the other series as
> line (i.e market share).
> Want to do it in one chart (not to overlay one chart over the other)
> Thanx.
>
combined 2 data and separate them
I have a data grid with dropdownlist.
the dropdownlist is populated with datas wth a sql statement with 2 combined data
my sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1
When i select a value from the dropdownlist, i need to separate the data, name and id into different columns
how do i do it?
Is there a way to manipulate the sql to do such a thing?you can use the split function.
the dropdownlist is populated with datas wth a sql statement with 2 combined data
my sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1
When i select a value from the dropdownlist, i need to separate the data, name and id into different columns
how do i do it?
Is there a way to manipulate the sql to do such a thing?you can use the split function.
Subscribe to:
Posts (Atom)