Hi,
I am thinking to suggest Reporting Services 2005 to our client to build
several complex/mission critical reports.
The only point that is not clear for me is that how robust is the current
beta version of SSRS 2005?
Can we start report development on SSRS 2005 latest beta version and then
continue using the final release in November?
Is the latest beta version of SSRS 2005 aligning with the Visual Studio 2005
Release Candidate 1?
I am asking because there are some report viewer user controls for visual
studio 2005 that is included in SSRS 2005. I am not sure those report viewer
user controls work in VS2005 release candidate 1.
Any help, suggestion or advice would be appreciated,
AlanAlan,
There will, I suspect, be few changes between Sept CTP of RS2005 and
the RTM version.
Sept CTP is certainly robust enough to develop on and previous
versions have been for quite some time.
Sept CTP of RS2005 is compatible with VS2005 RC.
Install Sept CTP first, then VS2005.
Andrew Watt
MVP - InfoPath
On Sat, 15 Oct 2005 12:01:05 -0400, "A.M-SG"
<alanalan@.newsgroup.nospam> wrote:
>Hi,
>
>I am thinking to suggest Reporting Services 2005 to our client to build
>several complex/mission critical reports.
>
>The only point that is not clear for me is that how robust is the current
>beta version of SSRS 2005?
>
>Can we start report development on SSRS 2005 latest beta version and then
>continue using the final release in November?
>
>Is the latest beta version of SSRS 2005 aligning with the Visual Studio 2005
>Release Candidate 1?
>
>I am asking because there are some report viewer user controls for visual
>studio 2005 that is included in SSRS 2005. I am not sure those report viewer
>user controls work in VS2005 release candidate 1.
>
>Any help, suggestion or advice would be appreciated,
>Alan
>
>|||Hello,
For questions of SQL server 2005, please access the following website and
then click "SQL Server 2005 Forums on MSDN":
http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
Thanks for cooperation.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Sophie,
This newsgroup, as I understand it, is intended to cover all versions
of Reporting Services.
I have asked internally if there will be a separate Reporting Services
2005 newsgroup and was told that this newsgroup is the one where SQL
Server 2000 and 2005 Reporting Services will be answered.
I am happy to continue to help users and answer Reporting Services
2005 questions in this newsgroup.
Of course, the fora provide an alternate place to ask questions.
Andrew Watt
MVP - InfoPath
On Mon, 17 Oct 2005 02:02:13 GMT, v-sguo@.online.microsoft.com (Sophie
Guo [MSFT]) wrote:
>Hello,
>For questions of SQL server 2005, please access the following website and
>then click "SQL Server 2005 Forums on MSDN":
>http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
>Thanks for cooperation.
>Sophie Guo
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>=====================================================>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
>=====================================================>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hello Andrew,
Thanks very much for your reply and useful information provided. I agreed
much with you that most of features should be OK now and it is OK to
program in SQL 2005. Perhaps there will be some minor change between CTP
and RTM version. But Alan doesn't need to change much code after the final
version releases. Besides, you are right that all SQL reporting service
related issues can be posted here.
For this particular post, Alan is a MSDN subscriber and posted his question
in MSDN managed newsgroup. Our team has commitment to reply him and work
with him on it. Or if it is beyond our newsgroup support boundary, we
should let Alan know as soon as possible so that he could find some other
proper channel if he feel it is necessary. That is why Sophie replied him
that.
Surely all we have done is not stopping community from answering Alan. I
believe all the information is welcome here expecially experience sharing.
Please do continue to share your experience here and let's work together to
make it better and better.
Thanks very much for your understanding.
Have a good day.
Best regards,
Yanhong Huang
Microsoft Community Support
Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.|||I'm surprised there are no words of caution.
Looking at your time frame, there are only a few weeks until the final
version is released, so there is a limited amount of work you'll be
able to accomplish before you'll be looking to re-install.
Installing the final release where you had beta versions running may
not be a trivial task. I don't know how many machines you are dealing
with, but you may want to limit the extent to which you install the
CTP.|||Hi Yanhong,
No problem. :) I am sure that we all want users to get maximum benefit
from Reporting Services 2005.
Andrew Watt
MVP - InfoPath
On Mon, 17 Oct 2005 09:29:03 GMT, yhhuang@.online.microsoft.com
(Yan-Hong Huang[MSFT]) wrote:
>Hello Andrew,
>Thanks very much for your reply and useful information provided. I agreed
>much with you that most of features should be OK now and it is OK to
>program in SQL 2005. Perhaps there will be some minor change between CTP
>and RTM version. But Alan doesn't need to change much code after the final
>version releases. Besides, you are right that all SQL reporting service
>related issues can be posted here.
>For this particular post, Alan is a MSDN subscriber and posted his question
>in MSDN managed newsgroup. Our team has commitment to reply him and work
>with him on it. Or if it is beyond our newsgroup support boundary, we
>should let Alan know as soon as possible so that he could find some other
>proper channel if he feel it is necessary. That is why Sophie replied him
>that.
>Surely all we have done is not stopping community from answering Alan. I
>believe all the information is welcome here expecially experience sharing.
>Please do continue to share your experience here and let's work together to
>make it better and better.
>Thanks very much for your understanding.
>Have a good day.
>Best regards,
>Yanhong Huang
>Microsoft Community Support
>Get Secure! ¨C www.microsoft.com/security
>This posting is provided "AS IS" with no warranties, and confers no rights.|||I'm surprised there are no words of caution.
Looking at your time frame, there are only a few weeks until the final
version is released, so there is a limited amount of work you'll be
able to accomplish before you'll be looking to re-install.
Installing the final release where you had beta versions running may
not be a trivial task. I don't know how many machines you are dealing
with, but you may want to limit the extent to which you install the
CTP.sql
Friday, March 30, 2012
How robust is Reporting Services 2005
Hi,
I am thinking to suggest Reporting Services 2005 to our client to build several complex/mission critical reports.
The only point that is not clear for me is that how robust is the current beta version of SSRS 2005?
Can we start report development on SSRS 2005 latest beta version and then continue using the final release in November?
Is the latest beta version of SSRS 2005 aligning with the Visual Studio 2005 Release Candidate 1?
I am asking because there are some report viewer user controls for visual studio 2005 that is included in SSRS 2005. I am not sure those report viewer user controls work in VS2005 release candidate 1.
Any help, suggestion or advice would be appreciated,
Alan
You should first install CTP September and then VS 2005 RC1.
CTP September should give you already a good view of SSRS 2005 and you can certainly use it to start your development. The final SQL Server 2005 RTM release will have a few additional improvements.
-- Robert
How ro append characetrs to auto-incrementing number
Hi,
I am creating a new table. in the table, I want to have a field called
OrderNo, which will have values like TR-1, TR-2 , TR-3 ... and so on. This
value will be the primary key and thus unique for thr record. How do I appen
d
'TTK-' to an autoincrementing number? Do I have to use a trigger which
automatically appends 'TTk-' to the auto-incrementing number as soon as it i
s
inserted in the databse? If so, what will be the code for that trigger? Or i
s
there a better way to do it.
Please help.
Thanks
--
pmudIf TTK- prepends every unique value in the column, then it is no more unique
than the number itself. Think about it.
Anyway, the way to do this and to needlessly use up disk space so you can
fool yourself into believing you have a better key is to use a computed
field:
CREATE TABLE dbo.Orders
(
OrderNo INT IDENTITY(1,1),
..,
FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
);
Or a view:
CREATE VIEW dbo.View_Orders
AS
SELECT OrderNo, ..., 'TTk-'+RTRIM(OrderNo)
FROM dbo.Orders;
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:6D76A707-E2A2-46C1-8B86-5F06EB01C989@.microsoft.com...
> Hi,
> I am creating a new table. in the table, I want to have a field called
> OrderNo, which will have values like TR-1, TR-2 , TR-3 ... and so on. This
> value will be the primary key and thus unique for thr record. How do I
> append
> 'TTK-' to an autoincrementing number? Do I have to use a trigger which
> automatically appends 'TTk-' to the auto-incrementing number as soon as it
> is
> inserted in the databse? If so, what will be the code for that trigger? Or
> is
> there a better way to do it.
> Please help.
> Thanks
> --
> pmud|||Hi Aaron,
We have orders for differnt companies in different tables. The primary
reason for doing this is that we want to allocate a separate chunk of order
nu,bers for each company rather than haveing Order no 10 for Comapny1 and 1
1
for Company2. So to aviod jumping numbers, we broke orders into different
tables based on teh company.
Now we want to prefix each Order no, in each table with the first 3
characters of the company name. For this, will the following trigger work on
each of the tables:
CREATE TRIGGER IDENTITY_TRIGGER
ON INSERT
AS
{
UPDATE TABLE1
SET OrderNo=CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo)
}
Please let me know if there is any mistake I have made. Also, please let me
know if you have any better quick ideas.
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:
> If TTK- prepends every unique value in the column, then it is no more uniq
ue
> than the number itself. Think about it.
> Anyway, the way to do this and to needlessly use up disk space so you can
> fool yourself into believing you have a better key is to use a computed
> field:
> CREATE TABLE dbo.Orders
> (
> OrderNo INT IDENTITY(1,1),
> ...,
> FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
> );
> Or a view:
> CREATE VIEW dbo.View_Orders
> AS
> SELECT OrderNo, ..., 'TTk-'+RTRIM(OrderNo)
> FROM dbo.Orders;
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:6D76A707-E2A2-46C1-8B86-5F06EB01C989@.microsoft.com...
>
>|||Warning, warning! I've got alarm bells going off in my head.
You just said you're using 1 column to store 2 facts. This is a really
bad design. If you've got 2 facts (OrderNo & CompanyCode) then store
them in 2 different columns. You table should look something like:
create table dbo.Orders
(
OrderNo int identity(1,1) primary key,
CompanyCode char(3) not null,
..,
CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as
varchar(15)),
..,
);
Use the OrderNo as your unique primary key, store the company code in a
separate column and if you want to combine those 2 facts then do so in a
computed column. Don't mix the 2 values in a single materialised
column, it'll just cause you headaches in the long term.
*mike hodgson*
http://sqlnerd.blogspot.com
pmud wrote:
>Hi Aaron,
>We have orders for differnt companies in different tables. The primary
>reason for doing this is that we want to allocate a separate chunk of order
>nu,bers for each company rather than haveing Order no 10 for Comapny1 and
11
>for Company2. So to aviod jumping numbers, we broke orders into different
>tables based on teh company.
>Now we want to prefix each Order no, in each table with the first 3
>characters of the company name. For this, will the following trigger work o
n
>each of the tables:
>CREATE TRIGGER IDENTITY_TRIGGER
>ON INSERT
>AS
>{
>UPDATE TABLE1
>SET OrderNo=CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo)
>}
>Please let me know if there is any mistake I have made. Also, please let me
>know if you have any better quick ideas.
>Thanks
>|||Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> If TTK- prepends every unique value in the column, then it is no more
> unique than the number itself. Think about it.
> Anyway, the way to do this and to needlessly use up disk space so you can
> fool yourself into believing you have a better key is to use a computed
> field:
> CREATE TABLE dbo.Orders
> (
> OrderNo INT IDENTITY(1,1),
> ...,
> FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
> );
To add to Aaron's post: if you need to, you can index the computed
column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||pmud (pmud@.discussions.microsoft.com) writes:
> We have orders for differnt companies in different tables. The primary
> reason for doing this is that we want to allocate a separate chunk of
> order nu,bers for each company rather than haveing Order no 10 for
> Comapny1 and 11 for Company2. So to aviod jumping numbers, we broke
> orders into different tables based on teh company.
You did what?
It's alright of wanting to have separate order numbers per company, but
you're criminal if you split the table into one per company only be able
to use IDENTITY for the order number. The normal key for the table would
be (company_id, order_id). "But then I can't use auto-increment?" So,
don't use auto-increment then. Rolling your own incrementing number is no
big deal:
BEGIN TRANSACTION
SELECT @.orderid = coalesce(MAX(orderid), 0) + 1
FROM orders WITH (UPDLOCK, HOLDLOCK)
WHERE company = @.company
INSERT orders (company, orderid, ...
VALUES (@.company, @.orderid, ...
..
COMMIT TRANSACTION
And I suspect that if you want one series per company, you want it to
be contiguous. For company ABC there should be orders, 1, 2, 3, 4, 5
and not 1, 5, 7 which is what you could get if you use IDENTITY.
I will have to add one caveat: the chief reason to use IDENTITY is
not simplicity of proramming, but scalability. In a high-transaction
environment where hundreds of rows are inserted by minute, IDENTITY
may be essential to avoid conentention. But somehow, I don't expect
the order rate for a single company be that high.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Mike,
You said that it was criminal to break out tables into different orders
table per company. What do you think are the flaws in this approach? I know
that it is not a good design but what are the exact resons?
Thanks
--
pmud
"Mike Hodgson" wrote:
> Warning, warning! I've got alarm bells going off in my head.
> You just said you're using 1 column to store 2 facts. This is a really
> bad design. If you've got 2 facts (OrderNo & CompanyCode) then store
> them in 2 different columns. You table should look something like:
> create table dbo.Orders
> (
> OrderNo int identity(1,1) primary key,
> CompanyCode char(3) not null,
> ...,
> CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as
> varchar(15)),
> ...,
> );
> Use the OrderNo as your unique primary key, store the company code in a
> separate column and if you want to combine those 2 facts then do so in a
> computed column. Don't mix the 2 values in a single materialised
> column, it'll just cause you headaches in the long term.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> pmud wrote:
>
>|||Hi Earl,
What are the flaws in this structure?
Thanks
--
pmud
"Erland Sommarskog" wrote:
> pmud (pmud@.discussions.microsoft.com) writes:
> You did what?
> It's alright of wanting to have separate order numbers per company, but
> you're criminal if you split the table into one per company only be able
> to use IDENTITY for the order number. The normal key for the table would
> be (company_id, order_id). "But then I can't use auto-increment?" So,
> don't use auto-increment then. Rolling your own incrementing number is no
> big deal:
> BEGIN TRANSACTION
> SELECT @.orderid = coalesce(MAX(orderid), 0) + 1
> FROM orders WITH (UPDLOCK, HOLDLOCK)
> WHERE company = @.company
> INSERT orders (company, orderid, ...
> VALUES (@.company, @.orderid, ...
> ...
> COMMIT TRANSACTION
> And I suspect that if you want one series per company, you want it to
> be contiguous. For company ABC there should be orders, 1, 2, 3, 4, 5
> and not 1, 5, 7 which is what you could get if you use IDENTITY.
> I will have to add one caveat: the chief reason to use IDENTITY is
> not simplicity of proramming, but scalability. In a high-transaction
> environment where hundreds of rows are inserted by minute, IDENTITY
> may be essential to avoid conentention. But somehow, I don't expect
> the order rate for a single company be that high.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Earl,
I am not very sure about transactions. Do I have to write teh code for
transactions in trigger window? Also, with the approach you mentioned, will
it be possible to have contiguous order nos for each company?
If so, then that will be great.
Thanks for your help..
--
pmud
"Erland Sommarskog" wrote:
> Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> To add to Aaron's post: if you need to, you can index the computed
> column.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||I didn't say it was criminal, Erland did.
But I agree that it's a bad idea to have 1 table per company - if you
add another company you'd have to change the schema of the DB rather
than simply inserting a row in a table. As a matter of course, you
shouldn't be changing the schema of production databases regularly
simply because you have new data (that has already been defined, eg. an
order). Also this design would make querying across multiple companies
much more complicated because you'd need to use joins or union operators
to combine the sets of data - yuk! To automate queries like this at
runtime you'd need to use dynamic SQL as the structure of your queries
would change each time you add a new company - imagine how ugly that
would quickly become. There are plenty of other things wrong with this
approach if you just think ahead a year or two and use you imagination a
bit.
When I initially posted in this thread I'd missed the bit where you said
you wanted to store orders specific to a company in company-specific
tables. I just thought you wanted to have 1 orders table and keep all
you orders in there. My main objection to your design was storing 2
facts in a single column. Why would you do that? If you store 2 facts,
why not 3, or 4 or 5 or 100? Imagine how hard it would be to write
queries against a table with a single varchar(8000) column that stored
an OrderNo, a CompanyCode, a OrderDate, a ShippingDate, a
DeliveryAddress, a ContactName, etc., etc.
The basic premise behind relational database theory (very much
simplified) is that a table represents a single entity or relationship
(this is in conflict with the idea of multiple orders tables to store
orders) and a column in a table represents a single fact or attribute of
that entity (this is in conflict with combining multiple attributes in a
single column). These are just the fundamental tenets of this area of
theory. (Oh no, I'm starting to sound a little like Celko - LOL!)
Chris Date, Fabian Pascal or E.F. Codd could give you a much better
explanation about relational theory than I could. If you're really
interested in the theory I recommend reading one or more of their books
(An Introduction to Database Systems
<http://www.amazon.com/gp/product/03...5Fencoding=UTF8>
by Chris Date is probably a good starting point or further to that his
latest book Database in Depth
<http://www.amazon.com/gp/product/05...5Fencoding=UTF8> ).
*mike hodgson*
http://sqlnerd.blogspot.com
pmud wrote:
>Hi Mike,
>You said that it was criminal to break out tables into different orders
>table per company. What do you think are the flaws in this approach? I know
>that it is not a good design but what are the exact resons?
>Thanks
>
I am creating a new table. in the table, I want to have a field called
OrderNo, which will have values like TR-1, TR-2 , TR-3 ... and so on. This
value will be the primary key and thus unique for thr record. How do I appen
d
'TTK-' to an autoincrementing number? Do I have to use a trigger which
automatically appends 'TTk-' to the auto-incrementing number as soon as it i
s
inserted in the databse? If so, what will be the code for that trigger? Or i
s
there a better way to do it.
Please help.
Thanks
--
pmudIf TTK- prepends every unique value in the column, then it is no more unique
than the number itself. Think about it.
Anyway, the way to do this and to needlessly use up disk space so you can
fool yourself into believing you have a better key is to use a computed
field:
CREATE TABLE dbo.Orders
(
OrderNo INT IDENTITY(1,1),
..,
FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
);
Or a view:
CREATE VIEW dbo.View_Orders
AS
SELECT OrderNo, ..., 'TTk-'+RTRIM(OrderNo)
FROM dbo.Orders;
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:6D76A707-E2A2-46C1-8B86-5F06EB01C989@.microsoft.com...
> Hi,
> I am creating a new table. in the table, I want to have a field called
> OrderNo, which will have values like TR-1, TR-2 , TR-3 ... and so on. This
> value will be the primary key and thus unique for thr record. How do I
> append
> 'TTK-' to an autoincrementing number? Do I have to use a trigger which
> automatically appends 'TTk-' to the auto-incrementing number as soon as it
> is
> inserted in the databse? If so, what will be the code for that trigger? Or
> is
> there a better way to do it.
> Please help.
> Thanks
> --
> pmud|||Hi Aaron,
We have orders for differnt companies in different tables. The primary
reason for doing this is that we want to allocate a separate chunk of order
nu,bers for each company rather than haveing Order no 10 for Comapny1 and 1
1
for Company2. So to aviod jumping numbers, we broke orders into different
tables based on teh company.
Now we want to prefix each Order no, in each table with the first 3
characters of the company name. For this, will the following trigger work on
each of the tables:
CREATE TRIGGER IDENTITY_TRIGGER
ON INSERT
AS
{
UPDATE TABLE1
SET OrderNo=CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo)
}
Please let me know if there is any mistake I have made. Also, please let me
know if you have any better quick ideas.
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:
> If TTK- prepends every unique value in the column, then it is no more uniq
ue
> than the number itself. Think about it.
> Anyway, the way to do this and to needlessly use up disk space so you can
> fool yourself into believing you have a better key is to use a computed
> field:
> CREATE TABLE dbo.Orders
> (
> OrderNo INT IDENTITY(1,1),
> ...,
> FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
> );
> Or a view:
> CREATE VIEW dbo.View_Orders
> AS
> SELECT OrderNo, ..., 'TTk-'+RTRIM(OrderNo)
> FROM dbo.Orders;
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:6D76A707-E2A2-46C1-8B86-5F06EB01C989@.microsoft.com...
>
>|||Warning, warning! I've got alarm bells going off in my head.
You just said you're using 1 column to store 2 facts. This is a really
bad design. If you've got 2 facts (OrderNo & CompanyCode) then store
them in 2 different columns. You table should look something like:
create table dbo.Orders
(
OrderNo int identity(1,1) primary key,
CompanyCode char(3) not null,
..,
CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as
varchar(15)),
..,
);
Use the OrderNo as your unique primary key, store the company code in a
separate column and if you want to combine those 2 facts then do so in a
computed column. Don't mix the 2 values in a single materialised
column, it'll just cause you headaches in the long term.
*mike hodgson*
http://sqlnerd.blogspot.com
pmud wrote:
>Hi Aaron,
>We have orders for differnt companies in different tables. The primary
>reason for doing this is that we want to allocate a separate chunk of order
>nu,bers for each company rather than haveing Order no 10 for Comapny1 and
11
>for Company2. So to aviod jumping numbers, we broke orders into different
>tables based on teh company.
>Now we want to prefix each Order no, in each table with the first 3
>characters of the company name. For this, will the following trigger work o
n
>each of the tables:
>CREATE TRIGGER IDENTITY_TRIGGER
>ON INSERT
>AS
>{
>UPDATE TABLE1
>SET OrderNo=CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo)
>}
>Please let me know if there is any mistake I have made. Also, please let me
>know if you have any better quick ideas.
>Thanks
>|||Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> If TTK- prepends every unique value in the column, then it is no more
> unique than the number itself. Think about it.
> Anyway, the way to do this and to needlessly use up disk space so you can
> fool yourself into believing you have a better key is to use a computed
> field:
> CREATE TABLE dbo.Orders
> (
> OrderNo INT IDENTITY(1,1),
> ...,
> FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
> );
To add to Aaron's post: if you need to, you can index the computed
column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||pmud (pmud@.discussions.microsoft.com) writes:
> We have orders for differnt companies in different tables. The primary
> reason for doing this is that we want to allocate a separate chunk of
> order nu,bers for each company rather than haveing Order no 10 for
> Comapny1 and 11 for Company2. So to aviod jumping numbers, we broke
> orders into different tables based on teh company.
You did what?
It's alright of wanting to have separate order numbers per company, but
you're criminal if you split the table into one per company only be able
to use IDENTITY for the order number. The normal key for the table would
be (company_id, order_id). "But then I can't use auto-increment?" So,
don't use auto-increment then. Rolling your own incrementing number is no
big deal:
BEGIN TRANSACTION
SELECT @.orderid = coalesce(MAX(orderid), 0) + 1
FROM orders WITH (UPDLOCK, HOLDLOCK)
WHERE company = @.company
INSERT orders (company, orderid, ...
VALUES (@.company, @.orderid, ...
..
COMMIT TRANSACTION
And I suspect that if you want one series per company, you want it to
be contiguous. For company ABC there should be orders, 1, 2, 3, 4, 5
and not 1, 5, 7 which is what you could get if you use IDENTITY.
I will have to add one caveat: the chief reason to use IDENTITY is
not simplicity of proramming, but scalability. In a high-transaction
environment where hundreds of rows are inserted by minute, IDENTITY
may be essential to avoid conentention. But somehow, I don't expect
the order rate for a single company be that high.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Mike,
You said that it was criminal to break out tables into different orders
table per company. What do you think are the flaws in this approach? I know
that it is not a good design but what are the exact resons?
Thanks
--
pmud
"Mike Hodgson" wrote:
> Warning, warning! I've got alarm bells going off in my head.
> You just said you're using 1 column to store 2 facts. This is a really
> bad design. If you've got 2 facts (OrderNo & CompanyCode) then store
> them in 2 different columns. You table should look something like:
> create table dbo.Orders
> (
> OrderNo int identity(1,1) primary key,
> CompanyCode char(3) not null,
> ...,
> CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as
> varchar(15)),
> ...,
> );
> Use the OrderNo as your unique primary key, store the company code in a
> separate column and if you want to combine those 2 facts then do so in a
> computed column. Don't mix the 2 values in a single materialised
> column, it'll just cause you headaches in the long term.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> pmud wrote:
>
>|||Hi Earl,
What are the flaws in this structure?
Thanks
--
pmud
"Erland Sommarskog" wrote:
> pmud (pmud@.discussions.microsoft.com) writes:
> You did what?
> It's alright of wanting to have separate order numbers per company, but
> you're criminal if you split the table into one per company only be able
> to use IDENTITY for the order number. The normal key for the table would
> be (company_id, order_id). "But then I can't use auto-increment?" So,
> don't use auto-increment then. Rolling your own incrementing number is no
> big deal:
> BEGIN TRANSACTION
> SELECT @.orderid = coalesce(MAX(orderid), 0) + 1
> FROM orders WITH (UPDLOCK, HOLDLOCK)
> WHERE company = @.company
> INSERT orders (company, orderid, ...
> VALUES (@.company, @.orderid, ...
> ...
> COMMIT TRANSACTION
> And I suspect that if you want one series per company, you want it to
> be contiguous. For company ABC there should be orders, 1, 2, 3, 4, 5
> and not 1, 5, 7 which is what you could get if you use IDENTITY.
> I will have to add one caveat: the chief reason to use IDENTITY is
> not simplicity of proramming, but scalability. In a high-transaction
> environment where hundreds of rows are inserted by minute, IDENTITY
> may be essential to avoid conentention. But somehow, I don't expect
> the order rate for a single company be that high.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Earl,
I am not very sure about transactions. Do I have to write teh code for
transactions in trigger window? Also, with the approach you mentioned, will
it be possible to have contiguous order nos for each company?
If so, then that will be great.
Thanks for your help..
--
pmud
"Erland Sommarskog" wrote:
> Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> To add to Aaron's post: if you need to, you can index the computed
> column.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||I didn't say it was criminal, Erland did.
But I agree that it's a bad idea to have 1 table per company - if you
add another company you'd have to change the schema of the DB rather
than simply inserting a row in a table. As a matter of course, you
shouldn't be changing the schema of production databases regularly
simply because you have new data (that has already been defined, eg. an
order). Also this design would make querying across multiple companies
much more complicated because you'd need to use joins or union operators
to combine the sets of data - yuk! To automate queries like this at
runtime you'd need to use dynamic SQL as the structure of your queries
would change each time you add a new company - imagine how ugly that
would quickly become. There are plenty of other things wrong with this
approach if you just think ahead a year or two and use you imagination a
bit.
When I initially posted in this thread I'd missed the bit where you said
you wanted to store orders specific to a company in company-specific
tables. I just thought you wanted to have 1 orders table and keep all
you orders in there. My main objection to your design was storing 2
facts in a single column. Why would you do that? If you store 2 facts,
why not 3, or 4 or 5 or 100? Imagine how hard it would be to write
queries against a table with a single varchar(8000) column that stored
an OrderNo, a CompanyCode, a OrderDate, a ShippingDate, a
DeliveryAddress, a ContactName, etc., etc.
The basic premise behind relational database theory (very much
simplified) is that a table represents a single entity or relationship
(this is in conflict with the idea of multiple orders tables to store
orders) and a column in a table represents a single fact or attribute of
that entity (this is in conflict with combining multiple attributes in a
single column). These are just the fundamental tenets of this area of
theory. (Oh no, I'm starting to sound a little like Celko - LOL!)
Chris Date, Fabian Pascal or E.F. Codd could give you a much better
explanation about relational theory than I could. If you're really
interested in the theory I recommend reading one or more of their books
(An Introduction to Database Systems
<http://www.amazon.com/gp/product/03...5Fencoding=UTF8>
by Chris Date is probably a good starting point or further to that his
latest book Database in Depth
<http://www.amazon.com/gp/product/05...5Fencoding=UTF8> ).
*mike hodgson*
http://sqlnerd.blogspot.com
pmud wrote:
>Hi Mike,
>You said that it was criminal to break out tables into different orders
>table per company. What do you think are the flaws in this approach? I know
>that it is not a good design but what are the exact resons?
>Thanks
>
How revoke different from deny?
Regarding the permission check marks, the manual says:
'The green check mark grants the permission, the red X denies the
permission, and the blank box revokes a previously set permission.'
I'm not clear how a blank box is different from a box which has a red X
mark? Do both all mean deny?
Bing
Never mind. I've figured out how revoke is different from deny.
Bing
"bing" wrote:
> Regarding the permission check marks, the manual says:
> 'The green check mark grants the permission, the red X denies the
> permission, and the blank box revokes a previously set permission.'
> I'm not clear how a blank box is different from a box which has a red X
> mark? Do both all mean deny?
> Bing
|||The grants, revokes and deny's work with our role based permissions.
Follow this example and see if it helps you to understand:
I have a database with a table in it called Table1.
I have a user named Frog who would like to work with Table1.
Frog belongs to the Public role. (Everyone is always a member of public).
Frog also belongs to another role called Marketing
Frog also belongs to another role called Accounting.
SELECT permissions on Table1 have been set as follows:
Public <-- Grant
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- None set (which is the same as Revoke).
You must combine all the permissions for Frog to determine whether or not he
is allowed to SELECT from Table1. Looking at all of the permissions, here
are the rules:
1. He must be Granted somewhere in his list of permissions.
2. He must NOT be denied anywhere in his list of permissions. (Once
Denied, ALWAYS denied).
From our example above, Frog does have SELECT permissions on Table1 becausse
he was granted them by his membership in Public.
If we modify that permission scheme as follows:
Public <-- Grant
Marketing <-- Deny
Accounting<-- Grant
Frog <-- Grant
We then follow our rules:
1. He has been Granted somewhere in his list of permissions.
2. He was denied somewhere in his list of permissions.
Therefore, He DOES NOT have SELECT permissions.
If we revoke all permission on the table as follows:
Public <-- Revoke
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- Revoke
Check our rules:
1. No grant anywhere in the hierarchy, therefore, no SELECT permissions.
2. Not denied anywhere, but see rule 1.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
'The green check mark grants the permission, the red X denies the
permission, and the blank box revokes a previously set permission.'
I'm not clear how a blank box is different from a box which has a red X
mark? Do both all mean deny?
Bing
Never mind. I've figured out how revoke is different from deny.
Bing
"bing" wrote:
> Regarding the permission check marks, the manual says:
> 'The green check mark grants the permission, the red X denies the
> permission, and the blank box revokes a previously set permission.'
> I'm not clear how a blank box is different from a box which has a red X
> mark? Do both all mean deny?
> Bing
|||The grants, revokes and deny's work with our role based permissions.
Follow this example and see if it helps you to understand:
I have a database with a table in it called Table1.
I have a user named Frog who would like to work with Table1.
Frog belongs to the Public role. (Everyone is always a member of public).
Frog also belongs to another role called Marketing
Frog also belongs to another role called Accounting.
SELECT permissions on Table1 have been set as follows:
Public <-- Grant
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- None set (which is the same as Revoke).
You must combine all the permissions for Frog to determine whether or not he
is allowed to SELECT from Table1. Looking at all of the permissions, here
are the rules:
1. He must be Granted somewhere in his list of permissions.
2. He must NOT be denied anywhere in his list of permissions. (Once
Denied, ALWAYS denied).
From our example above, Frog does have SELECT permissions on Table1 becausse
he was granted them by his membership in Public.
If we modify that permission scheme as follows:
Public <-- Grant
Marketing <-- Deny
Accounting<-- Grant
Frog <-- Grant
We then follow our rules:
1. He has been Granted somewhere in his list of permissions.
2. He was denied somewhere in his list of permissions.
Therefore, He DOES NOT have SELECT permissions.
If we revoke all permission on the table as follows:
Public <-- Revoke
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- Revoke
Check our rules:
1. No grant anywhere in the hierarchy, therefore, no SELECT permissions.
2. Not denied anywhere, but see rule 1.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
How revoke different from deny?
Regarding the permission check marks, the manual says:
'The green check mark grants the permission, the red X denies the
permission, and the blank box revokes a previously set permission.'
I'm not clear how a blank box is different from a box which has a red X
mark? Do both all mean deny?
BingNever mind. I've figured out how revoke is different from deny.
Bing
"bing" wrote:
> Regarding the permission check marks, the manual says:
> 'The green check mark grants the permission, the red X denies the
> permission, and the blank box revokes a previously set permission.'
> I'm not clear how a blank box is different from a box which has a red X
> mark? Do both all mean deny?
> Bing|||The grants, revokes and deny's work with our role based permissions.
Follow this example and see if it helps you to understand:
I have a database with a table in it called Table1.
I have a user named Frog who would like to work with Table1.
Frog belongs to the Public role. (Everyone is always a member of public).
Frog also belongs to another role called Marketing
Frog also belongs to another role called Accounting.
SELECT permissions on Table1 have been set as follows:
Public <-- Grant
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- None set (which is the same as Revoke).
You must combine all the permissions for Frog to determine whether or not he
is allowed to SELECT from Table1. Looking at all of the permissions, here
are the rules:
1. He must be Granted somewhere in his list of permissions.
2. He must NOT be denied anywhere in his list of permissions. (Once
Denied, ALWAYS denied).
From our example above, Frog does have SELECT permissions on Table1 becausse
he was granted them by his membership in Public.
If we modify that permission scheme as follows:
Public <-- Grant
Marketing <-- Deny
Accounting<-- Grant
Frog <-- Grant
We then follow our rules:
1. He has been Granted somewhere in his list of permissions.
2. He was denied somewhere in his list of permissions.
Therefore, He DOES NOT have SELECT permissions.
If we revoke all permission on the table as follows:
Public <-- Revoke
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- Revoke
Check our rules:
1. No grant anywhere in the hierarchy, therefore, no SELECT permissions.
2. Not denied anywhere, but see rule 1.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
'The green check mark grants the permission, the red X denies the
permission, and the blank box revokes a previously set permission.'
I'm not clear how a blank box is different from a box which has a red X
mark? Do both all mean deny?
BingNever mind. I've figured out how revoke is different from deny.
Bing
"bing" wrote:
> Regarding the permission check marks, the manual says:
> 'The green check mark grants the permission, the red X denies the
> permission, and the blank box revokes a previously set permission.'
> I'm not clear how a blank box is different from a box which has a red X
> mark? Do both all mean deny?
> Bing|||The grants, revokes and deny's work with our role based permissions.
Follow this example and see if it helps you to understand:
I have a database with a table in it called Table1.
I have a user named Frog who would like to work with Table1.
Frog belongs to the Public role. (Everyone is always a member of public).
Frog also belongs to another role called Marketing
Frog also belongs to another role called Accounting.
SELECT permissions on Table1 have been set as follows:
Public <-- Grant
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- None set (which is the same as Revoke).
You must combine all the permissions for Frog to determine whether or not he
is allowed to SELECT from Table1. Looking at all of the permissions, here
are the rules:
1. He must be Granted somewhere in his list of permissions.
2. He must NOT be denied anywhere in his list of permissions. (Once
Denied, ALWAYS denied).
From our example above, Frog does have SELECT permissions on Table1 becausse
he was granted them by his membership in Public.
If we modify that permission scheme as follows:
Public <-- Grant
Marketing <-- Deny
Accounting<-- Grant
Frog <-- Grant
We then follow our rules:
1. He has been Granted somewhere in his list of permissions.
2. He was denied somewhere in his list of permissions.
Therefore, He DOES NOT have SELECT permissions.
If we revoke all permission on the table as follows:
Public <-- Revoke
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- Revoke
Check our rules:
1. No grant anywhere in the hierarchy, therefore, no SELECT permissions.
2. Not denied anywhere, but see rule 1.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
How return the duplicate row?
Hello,
I would like return in a file the duplicates lines from a table.
Is it possible with a composant?
Or must I use a sql request.
But my problem with the request is the null value in the cell of the line. When U do a join and there is a null value in the row, there no resoult from the request.
How can U do to solve this problem?
Thank you
Regards.
Laurent Albiac
You could copy them into a plain file, for example, keep in mind that your statement is like this:
SELECT f1,f2,f3,count(*)
from table
group by f1,f2,f3
having count(*)> 1
I don't see where's the problem
|||or you can try this
SELECT ROW_NUMBER() OVER (ORDER BY field1 ASC, field2 ASC) AS ROWID,
field1,field2 FROM table
rabbiwan wrote:
Hello,
I would like return in a file the duplicates lines from a table.
Is it possible with a composant?
Or must I use a sql request.
But my problem with the request is the null value in the cell of the line. When U do a join and there is a null value in the row, there no resoult from the request.
How can U do to solve this problem?
Thank you
Regards.
Laurent Albiac
If you want to find duplicates use the Aggregate component to see how many rows there are of each combination. Anything that is plural - those are your duplicates.
-Jamie
How restore database on other server with rights to all objects
Can you help me?
Thanks
Alina
Alina
Look at RESTORE command in the BOL
Also you will have to move all logins from the OLD server to the NEW one.
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>
|||Hi,
You could see the below links to restore and map the users.
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Thanks
Hari
MCDBA
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>
Thanks
Alina
Alina
Look at RESTORE command in the BOL
Also you will have to move all logins from the OLD server to the NEW one.
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>
|||Hi,
You could see the below links to restore and map the users.
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Thanks
Hari
MCDBA
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>
How restore database on other server with rights to all objects
Can you help me?
Thanks
AlinaAlina
Look at RESTORE command in the BOL
Also you will have to move all logins from the OLD server to the NEW one.
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>|||Hi,
You could see the below links to restore and map the users.
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Thanks
Hari
MCDBA
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>
Thanks
AlinaAlina
Look at RESTORE command in the BOL
Also you will have to move all logins from the OLD server to the NEW one.
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>|||Hi,
You could see the below links to restore and map the users.
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Thanks
Hari
MCDBA
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>
Thursday, March 29, 2012
How restore database on other server with rights to all objects
Can you help me?
Thanks
AlinaAlina
Look at RESTORE command in the BOL
Also you will have to move all logins from the OLD server to the NEW one.
"Alina Górna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>|||Hi,
You could see the below links to restore and map the users.
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Thanks
Hari
MCDBA
"Alina Górna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>
Thanks
AlinaAlina
Look at RESTORE command in the BOL
Also you will have to move all logins from the OLD server to the NEW one.
"Alina Górna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>|||Hi,
You could see the below links to restore and map the users.
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Thanks
Hari
MCDBA
"Alina Górna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>
How restore BAK file?
We use Avaya IP Office. Call records are stored in programs such as Delta
Server and Database Server Supervisor and backed up with SDX Archiver.
Avaya says it's using Microsoft's SQL database.
Question: How do I restore a database? I have a .BAK file that I need to
restore, to try to retreive some data. Avaya says it's a standard Microsoft
SQL backup.
Can someone tell me how to restore this backup? I know Windows, but I've
never used any flavor of SQL.
thanks,
BillYou can read about the RESTORE DATABASE command in Books Online. If you are totally new on SQL
Server, I suggest you pick up a book at appropriate level, that will save you lots of time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:01FB537F-9227-485F-9A4D-D23CB8EB3718@.microsoft.com...
> We use Avaya IP Office. Call records are stored in programs such as Delta
> Server and Database Server Supervisor and backed up with SDX Archiver.
> Avaya says it's using Microsoft's SQL database.
> Question: How do I restore a database? I have a .BAK file that I need to
> restore, to try to retreive some data. Avaya says it's a standard Microsoft
> SQL backup.
> Can someone tell me how to restore this backup? I know Windows, but I've
> never used any flavor of SQL.
> thanks,
> Bill|||Hi Bill,
If you don't know SQL Server, then you must be unawared of Books Online...
Here, you can find info regarding to Backup and Restore in SQL Server 2005
under this topic.
http://msdn2.microsoft.com/en-us/library/ms187048.aspx (This is Books
Online)
If you need further help, we are around here.
Ekrem Ã?nsoy
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:01FB537F-9227-485F-9A4D-D23CB8EB3718@.microsoft.com...
> We use Avaya IP Office. Call records are stored in programs such as Delta
> Server and Database Server Supervisor and backed up with SDX Archiver.
> Avaya says it's using Microsoft's SQL database.
> Question: How do I restore a database? I have a .BAK file that I need to
> restore, to try to retreive some data. Avaya says it's a standard
> Microsoft
> SQL backup.
> Can someone tell me how to restore this backup? I know Windows, but I've
> never used any flavor of SQL.
> thanks,
> Billsql
Server and Database Server Supervisor and backed up with SDX Archiver.
Avaya says it's using Microsoft's SQL database.
Question: How do I restore a database? I have a .BAK file that I need to
restore, to try to retreive some data. Avaya says it's a standard Microsoft
SQL backup.
Can someone tell me how to restore this backup? I know Windows, but I've
never used any flavor of SQL.
thanks,
BillYou can read about the RESTORE DATABASE command in Books Online. If you are totally new on SQL
Server, I suggest you pick up a book at appropriate level, that will save you lots of time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:01FB537F-9227-485F-9A4D-D23CB8EB3718@.microsoft.com...
> We use Avaya IP Office. Call records are stored in programs such as Delta
> Server and Database Server Supervisor and backed up with SDX Archiver.
> Avaya says it's using Microsoft's SQL database.
> Question: How do I restore a database? I have a .BAK file that I need to
> restore, to try to retreive some data. Avaya says it's a standard Microsoft
> SQL backup.
> Can someone tell me how to restore this backup? I know Windows, but I've
> never used any flavor of SQL.
> thanks,
> Bill|||Hi Bill,
If you don't know SQL Server, then you must be unawared of Books Online...
Here, you can find info regarding to Backup and Restore in SQL Server 2005
under this topic.
http://msdn2.microsoft.com/en-us/library/ms187048.aspx (This is Books
Online)
If you need further help, we are around here.
Ekrem Ã?nsoy
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:01FB537F-9227-485F-9A4D-D23CB8EB3718@.microsoft.com...
> We use Avaya IP Office. Call records are stored in programs such as Delta
> Server and Database Server Supervisor and backed up with SDX Archiver.
> Avaya says it's using Microsoft's SQL database.
> Question: How do I restore a database? I have a .BAK file that I need to
> restore, to try to retreive some data. Avaya says it's a standard
> Microsoft
> SQL backup.
> Can someone tell me how to restore this backup? I know Windows, but I've
> never used any flavor of SQL.
> thanks,
> Billsql
how report manager works?
Understand how Report Manager functions internally through the use of classes in the ReportingServicesWebUserInterface assembly and leverage its undocumented functionality.
Hi,
The power of the report manager is not in the report manager itself. Reporting Services is implemented as a web service. Report Manager is just an interface (.APSX) on top of that.
If needed, you can create your own interface using any language that understands web services.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||do you know any sample example? I would greatly appreciate.
Thanks,
How Replication Works with inserts and Autonumber pKs
Hi,
I am using SQL Server replication.
I have published articles and my subscribers are some users with pocket pc.
I have a couple of tables with Identity primary keys.
I have already set them to "Not for replication".
My questions:
For tables with identity values, say tblContact, when different users add new contacts new numbers are generated.
What happens when they sync? if two users added a new records , and both have the same number, will sql server consider one as updating the other? Or it will add both with new numbers to the master table and update both subscriptions.
In other words, how should the autonumber primary keys be handled in replication? should I create a composite key, say userID+autonumber to avoid such a situation?
I hope my question is clear.
Thanksmake your identity keys guids (sql type uniqueidentifier, set default property to Newid() ) or random integers. choice depends on number of records your table will have. ie: < 1billion, could probably get away with random integer with low risk of collision, > 1 billion better use guids. I personally use guids for most stuff because I don't have to ever worry about collisions and storage is not an issue, but this may be an issue for you on a pocketpc.
hth
burt|||btw...
if you need the new identity right away and your provider doesn't support refreshing the underlying value you may just wish to generate your own guid (don't set a default on the field). That's one other advantage to this method.
another way: if you're using stored procedures you could add an output parameter to return the newly created guid. this is especially helpful if your programming language doesn't have built in facilities to create a guid.|||I like the newid() idea, but if you are set on identity values, you can make them mutually exclusive sets. This will require you, however, to declare how many servers participate in the replication at the time of setup, because there won't be much going back (although I have not read up on dbcc fixidentity (sp?) lately).
Server 1 has
col1 int identity (1, 3) primary key
Server 2 has
col1 int identity (2, 3) primary key
Server 3 has
col1 int identity (3, 3) primary key
This way, server 1 has values 1,4,7,10,13,...
server 2 has 2,5,8,11,...
and server three has 3,6,9,12,...
Server 4 gets no break.|||Thanks for your reply,
I read up the Identity range and GUID in books online.
But I have a question:
Instead of using guid and identity range, what if we use a composite key, for example use an identity column combined with the userID?
How is that?|||then you'd might as well just find the natural key, assuming one exists?
compound primary keys, while fully supported, will make your queries much more complex.
the project i'm working on now has several compound primary keys (one with 4 fields in the key) and the queries are a nightmare is some places - especially because this is a big project with well over 100 stored procs.
note also that i'm not sure you can do a "compound foreign key". in other words you may still have to have an artificial primary key to establish relationships.
I am using SQL Server replication.
I have published articles and my subscribers are some users with pocket pc.
I have a couple of tables with Identity primary keys.
I have already set them to "Not for replication".
My questions:
For tables with identity values, say tblContact, when different users add new contacts new numbers are generated.
What happens when they sync? if two users added a new records , and both have the same number, will sql server consider one as updating the other? Or it will add both with new numbers to the master table and update both subscriptions.
In other words, how should the autonumber primary keys be handled in replication? should I create a composite key, say userID+autonumber to avoid such a situation?
I hope my question is clear.
Thanksmake your identity keys guids (sql type uniqueidentifier, set default property to Newid() ) or random integers. choice depends on number of records your table will have. ie: < 1billion, could probably get away with random integer with low risk of collision, > 1 billion better use guids. I personally use guids for most stuff because I don't have to ever worry about collisions and storage is not an issue, but this may be an issue for you on a pocketpc.
hth
burt|||btw...
if you need the new identity right away and your provider doesn't support refreshing the underlying value you may just wish to generate your own guid (don't set a default on the field). That's one other advantage to this method.
another way: if you're using stored procedures you could add an output parameter to return the newly created guid. this is especially helpful if your programming language doesn't have built in facilities to create a guid.|||I like the newid() idea, but if you are set on identity values, you can make them mutually exclusive sets. This will require you, however, to declare how many servers participate in the replication at the time of setup, because there won't be much going back (although I have not read up on dbcc fixidentity (sp?) lately).
Server 1 has
col1 int identity (1, 3) primary key
Server 2 has
col1 int identity (2, 3) primary key
Server 3 has
col1 int identity (3, 3) primary key
This way, server 1 has values 1,4,7,10,13,...
server 2 has 2,5,8,11,...
and server three has 3,6,9,12,...
Server 4 gets no break.|||Thanks for your reply,
I read up the Identity range and GUID in books online.
But I have a question:
Instead of using guid and identity range, what if we use a composite key, for example use an identity column combined with the userID?
How is that?|||then you'd might as well just find the natural key, assuming one exists?
compound primary keys, while fully supported, will make your queries much more complex.
the project i'm working on now has several compound primary keys (one with 4 fields in the key) and the queries are a nightmare is some places - especially because this is a big project with well over 100 stored procs.
note also that i'm not sure you can do a "compound foreign key". in other words you may still have to have an artificial primary key to establish relationships.
How replication works in this scenario.. Please help
Hello all,
I am a newbie when it comes to replication. I have read the BOL but I
was overwhilemed with the amount of information about replication.
We are planning to have a server with sql server 2005 and disconnected
laptops with sql server express. How would replication work in this
scenario assuming we are using merge replication:
Table AA [ a replicated table on the server and 9 columns]
User #1 has table AA on his laptop... and has added 3 records, deleted
1 and
update 1 record (5 transaction in total)...
Table AA has 3 new records added after AA last sync his replica...
Joe Admin comes and decided to make schema changes to table AA on the
server...
Q#1 can he do that even though the log has 3 transactions?
Q#2 can he does it if the log was empty?
Q#3 assume the admin did the schema changes to table AA...and Now user
#1 trying
to sync
does that mean User #1 will lose all her changes (the 5 transaction
above)...
Q#4: What is the normal best practice if you have sql express.. How
would handle such
scheduled schema change?
Thank you very much!
There are some complications with 'altering' the schema of merge replicated
tables while the subscriber does data changes, but from your description you
are just adding a column on the publisher and this scenario looks OK. The
laptop users should not lose their changes. Finally remember that
sp_repladdcolumn is run on the publisher only.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I am a newbie when it comes to replication. I have read the BOL but I
was overwhilemed with the amount of information about replication.
We are planning to have a server with sql server 2005 and disconnected
laptops with sql server express. How would replication work in this
scenario assuming we are using merge replication:
Table AA [ a replicated table on the server and 9 columns]
User #1 has table AA on his laptop... and has added 3 records, deleted
1 and
update 1 record (5 transaction in total)...
Table AA has 3 new records added after AA last sync his replica...
Joe Admin comes and decided to make schema changes to table AA on the
server...
Q#1 can he do that even though the log has 3 transactions?
Q#2 can he does it if the log was empty?
Q#3 assume the admin did the schema changes to table AA...and Now user
#1 trying
to sync
does that mean User #1 will lose all her changes (the 5 transaction
above)...
Q#4: What is the normal best practice if you have sql express.. How
would handle such
scheduled schema change?
Thank you very much!
There are some complications with 'altering' the schema of merge replicated
tables while the subscriber does data changes, but from your description you
are just adding a column on the publisher and this scenario looks OK. The
laptop users should not lose their changes. Finally remember that
sp_repladdcolumn is run on the publisher only.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
How replicate a db with msde?
How replicate a db in msde?
Best regards
RV
Hi,
Look the below article:-
http://support.microsoft.com/default...;en-us;Q324992
Thanks
Hari
MCDBA
"Richard Villaln" <rv@.cantv.net> wrote in message
news:eU#wC$AVEHA.484@.TK2MSFTNGP10.phx.gbl...
> How replicate a db in msde?
> Best regards
> RV
>
Best regards
RV
Hi,
Look the below article:-
http://support.microsoft.com/default...;en-us;Q324992
Thanks
Hari
MCDBA
"Richard Villaln" <rv@.cantv.net> wrote in message
news:eU#wC$AVEHA.484@.TK2MSFTNGP10.phx.gbl...
> How replicate a db in msde?
> Best regards
> RV
>
How remove blank values from report parameter list
Hi,
In report designer, while adding report parameters how to remove blank or empty values from parameter drop down list. Blank values will appear even after unchecking "Allow blank value" option.
Thanx
I think you messed something up. Allow blank values only means that you can put in a empty string as a parameter value. If you have a data driven combobox you will have to redefine the query that fills the combobox to make it only to show non blank values. Look for the column you specified as value column if you can filter the values out, that are retrieved from the database.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
sql
How remove all quote characters in a sql table?
Hi
I have a table with a few hundred emailadresses. How can I delete all quotes (') from the addresses, so that'email@.email.com' is replaced asemail@.email.com.
Thank you
zipfeli
You can use the REPLACE Function. And you want to an UPDATE not a DELETE.
UPDATE table SET Column =REPLACE(column, '''', '')
|||
UPDATE TableName SET EMailAddrCol = REPLACE(EMailAddrCol, '''', '')
I hope this helps,
Best of Luck!
How reliable is Microsoft's type4-JDBC driver for MsSQL Server?
Hai Friends,
I am using MsSQLServer in the backend and java in the front-end.
How reliable Microsoft's type4-JDBC driver for MsSQL Server?
I am very afraid as its only a beta version and version 1.0 and my project is a commercial one.
Is there any other better type4-JDBC driver for MsSQLServer available -which i can get for no cost-free of cost. Please guide me.
Thank You
Okay!
I am not going to use Microsoft's type4-JDBC driver for MsSQL Server.
I am going to use jTDC of sourceforge.com
|||The new SQL Server JDBC driver shipped last month and is quite reliable -- it passes cert suites from all the major vendors and is already being widely used.
Check it out here -- http://www.microsoft.com/downloads/details.aspx?familyid=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en
-shelby
How redirect SQL Sserver XML output?
I have to generate an XMl from a SQL Server 2000 table. Using the clause FOR
XML i can only send the result to screen or to file.
I want, instead, store the result, into a field column in another table.
There's a way i can do it without passing through a file (and OpenXML), but
storing the result directly into the field column?
tnks in advance
andrea
Do you want to say something along the line of
insert into T(xmlcol) values (select ... for xml ...)
?
If so, you will need to wait for SQL Server 2005. In SQL Server 2000, you
had to do some really ugly workarounds by moving the XML to the client and
then reload it back into the server.
Best regards
Michael
"vikys" <vikys@.hotmail.com> wrote in message
news:utcDmwwcFHA.3712@.TK2MSFTNGP09.phx.gbl...
>I have to generate an XMl from a SQL Server 2000 table. Using the clause
>FOR
> XML i can only send the result to screen or to file.
> I want, instead, store the result, into a field column in another table.
> There's a way i can do it without passing through a file (and OpenXML),
> but
> storing the result directly into the field column?
> tnks in advance
> andrea
>
XML i can only send the result to screen or to file.
I want, instead, store the result, into a field column in another table.
There's a way i can do it without passing through a file (and OpenXML), but
storing the result directly into the field column?
tnks in advance
andrea
Do you want to say something along the line of
insert into T(xmlcol) values (select ... for xml ...)
?
If so, you will need to wait for SQL Server 2005. In SQL Server 2000, you
had to do some really ugly workarounds by moving the XML to the client and
then reload it back into the server.
Best regards
Michael
"vikys" <vikys@.hotmail.com> wrote in message
news:utcDmwwcFHA.3712@.TK2MSFTNGP09.phx.gbl...
>I have to generate an XMl from a SQL Server 2000 table. Using the clause
>FOR
> XML i can only send the result to screen or to file.
> I want, instead, store the result, into a field column in another table.
> There's a way i can do it without passing through a file (and OpenXML),
> but
> storing the result directly into the field column?
> tnks in advance
> andrea
>
How realize Windows Authentication in small networks?
Hello,
In SQL Servern Books is written:
SQL Server Authentication is provided for backward compatibility only. When possible, use Windows Authentication.
How can Windows Authentication be realized in small networks from two ore more computers running Windows XP?
Having all a application written in VB Net 2005 for example, which connects to a central database on one computer. Where the cost and afford for a domain controller running Windows Server is not necessary.
If Windows Authentication can't be realized or can't be realized easy in such a scenario, and SQL Server Authentication is not supported any more, then SQL Server can't be taken as database server for this scenario, where the focus is at simplicity and low cost.
Regards,
Markus
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
> costly problems
i think simplicity and low cost need not be a problem. When possible, it should be a great advantage :-)
|||We still support SQL Authentication, use it in good health.
BOL is just trying to encourage folks on larger networks who have Windows Auth available to them to use it as it offers several advantages from a management point of view. For small Workgroup networks such as that you describe, SQL Auth is your answer.
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
How Query Same Columns w/Mulitple Criteria
I am trying to build my first report that is more than a simple columnar
report and wonder if you guys can help. I need to query the same sets of
fields but with different criteria and compare them over time as follows:
-show order NetAmount, Gross Amount, %NetAmount for each sales person for
2003
-show order NetAmount, Gross Amount, %NetAmount for each sales person for
2004
-show % change
Might look something like:
SalesPersId 03GrsAmt 03NetAmt 03NetAmt% | 04GrsAmt 04NetAmt 04NetAmt%
| GrsChg% NetChg%
Not sure if this needs subqueries/subreports or some othe report feature.
I am struggling with the documentation so many thanks for any helpTake a look at [Company Sales.rdl] sample report that ships with the
product.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23gFwvgDrEHA.3868@.TK2MSFTNGP15.phx.gbl...
> I am trying to build my first report that is more than a simple columnar
> report and wonder if you guys can help. I need to query the same sets of
> fields but with different criteria and compare them over time as follows:
> -show order NetAmount, Gross Amount, %NetAmount for each sales person for
> 2003
> -show order NetAmount, Gross Amount, %NetAmount for each sales person for
> 2004
> -show % change
> Might look something like:
> SalesPersId 03GrsAmt 03NetAmt 03NetAmt% | 04GrsAmt 04NetAmt
04NetAmt%
> | GrsChg% NetChg%
> Not sure if this needs subqueries/subreports or some othe report feature.
> I am struggling with the documentation so many thanks for any help
>
>|||Thanks for the resonse. I don't have the sampe data base loaded that is
needed for the sampler reports and not sure why it wasn't created during the
install.
-How would I go about installing that DB?
-If I were using straigt SQL for this, what functions would I need;
subqueries, group by ..?
Thanks again!sql
report and wonder if you guys can help. I need to query the same sets of
fields but with different criteria and compare them over time as follows:
-show order NetAmount, Gross Amount, %NetAmount for each sales person for
2003
-show order NetAmount, Gross Amount, %NetAmount for each sales person for
2004
-show % change
Might look something like:
SalesPersId 03GrsAmt 03NetAmt 03NetAmt% | 04GrsAmt 04NetAmt 04NetAmt%
| GrsChg% NetChg%
Not sure if this needs subqueries/subreports or some othe report feature.
I am struggling with the documentation so many thanks for any helpTake a look at [Company Sales.rdl] sample report that ships with the
product.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23gFwvgDrEHA.3868@.TK2MSFTNGP15.phx.gbl...
> I am trying to build my first report that is more than a simple columnar
> report and wonder if you guys can help. I need to query the same sets of
> fields but with different criteria and compare them over time as follows:
> -show order NetAmount, Gross Amount, %NetAmount for each sales person for
> 2003
> -show order NetAmount, Gross Amount, %NetAmount for each sales person for
> 2004
> -show % change
> Might look something like:
> SalesPersId 03GrsAmt 03NetAmt 03NetAmt% | 04GrsAmt 04NetAmt
04NetAmt%
> | GrsChg% NetChg%
> Not sure if this needs subqueries/subreports or some othe report feature.
> I am struggling with the documentation so many thanks for any help
>
>|||Thanks for the resonse. I don't have the sampe data base loaded that is
needed for the sampler reports and not sure why it wasn't created during the
install.
-How would I go about installing that DB?
-If I were using straigt SQL for this, what functions would I need;
subqueries, group by ..?
Thanks again!sql
How Query Question
I have a form where users pick multiple values. I then want to run a query against the Sql Server backend and limit the result set based on the multiple picks the user just made.
IF those picks were in a 2nd table - it would look like:
Select * from firsttable join secondtable on firsttable_pk=secondtable_fk
The problem is that this hypothetical 2nd table would be local to the application, not on the back end.
I could store those pick in an Array, but not sure if that can be passed to the back end for use in a query.
Any suggestions?
Thanks,I've solved this problem in the past by inserting the selected values into a SQL Server #Temp table, and then joining to that table in my select statement. This works as long as each user has their own connection.|||Thanks blindman. I'll check BOL for the #TEMP table and give it a try. Each user will have their own connection.|||I've also used a User Defined Function for a similar purpose. I pass in a list of values separated by commas and then a UDF to parse them into a table. Then you can join to that.
Here is a link to one possible version of the ParseString Function: http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp
IF those picks were in a 2nd table - it would look like:
Select * from firsttable join secondtable on firsttable_pk=secondtable_fk
The problem is that this hypothetical 2nd table would be local to the application, not on the back end.
I could store those pick in an Array, but not sure if that can be passed to the back end for use in a query.
Any suggestions?
Thanks,I've solved this problem in the past by inserting the selected values into a SQL Server #Temp table, and then joining to that table in my select statement. This works as long as each user has their own connection.|||Thanks blindman. I'll check BOL for the #TEMP table and give it a try. Each user will have their own connection.|||I've also used a User Defined Function for a similar purpose. I pass in a list of values separated by commas and then a UDF to parse them into a table. Then you can join to that.
Here is a link to one possible version of the ParseString Function: http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp
How Query Analyzer works in Text Mode option ?
Hi,
I want to write a application works in same manner as SQL Query Analyzer
works in Text mode option. I studied the behavior of query analyzer and find
out that in text mode option query analyzer does not wait for the query to
get finished, for displaying the result.
It diplays the result in Text form as and when it gets the result. It works
in some different manner than Query Analyzer working in GRID option.
I also want to write a application which does not wait for all the results
of the query, just give me the records which are available right now and
then give the remaining ones also in similar chunks.
Query Analyzer must be using some API calls through which this is possible.
Can any one help me in figure out what APIs are used by query analyzer in
text mode option.
Any help is appreciated.
Thanks
PushkarTo the best of my knowledge, you cannot use ADO or ADO.NET for this. QA uses
ODBC, and I have a
feeling that it is possible using OLEDB as well. So pick up a programmers ma
nual for one of those
APIs and check for asynchronous execution. But you should carefully consider
the extra work to use
these low level APIs compared to the productivity of a higher level API.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:eSsFbyTMGHA.2704@.TK2MSFTNGP15.phx
.gbl...
> Hi,
> I want to write a application works in same manner as SQL Query Analyzer w
orks in Text mode
> option. I studied the behavior of query analyzer and find out that in text
mode option query
> analyzer does not wait for the query to get finished, for displaying the r
esult.
> It diplays the result in Text form as and when it gets the result. It work
s in some different
> manner than Query Analyzer working in GRID option.
> I also want to write a application which does not wait for all the results
of the query, just give
> me the records which are available right now and then give the remaining o
nes also in similar
> chunks.
> Query Analyzer must be using some API calls through which this is possible
.
> Can any one help me in figure out what APIs are used by query analyzer in
text mode option.
> Any help is appreciated.
> Thanks
> Pushkar
>
>|||Pushkar wrote:
> Hi,
> I want to write a application works in same manner as SQL Query Analyzer
> works in Text mode option. I studied the behavior of query analyzer and fi
nd
> out that in text mode option query analyzer does not wait for the query to
> get finished, for displaying the result.
> It diplays the result in Text form as and when it gets the result. It work
s
> in some different manner than Query Analyzer working in GRID option.
> I also want to write a application which does not wait for all the results
> of the query, just give me the records which are available right now and
> then give the remaining ones also in similar chunks.
> Query Analyzer must be using some API calls through which this is possible
.
> Can any one help me in figure out what APIs are used by query analyzer in
> text mode option.
> Any help is appreciated.
> Thanks
> Pushkar
It may help you to read:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Good thinking, David. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139910046.905427.137570@.g43g2000cwa.googlegroups.com...
> Pushkar wrote:
> It may help you to read:
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
I want to write a application works in same manner as SQL Query Analyzer
works in Text mode option. I studied the behavior of query analyzer and find
out that in text mode option query analyzer does not wait for the query to
get finished, for displaying the result.
It diplays the result in Text form as and when it gets the result. It works
in some different manner than Query Analyzer working in GRID option.
I also want to write a application which does not wait for all the results
of the query, just give me the records which are available right now and
then give the remaining ones also in similar chunks.
Query Analyzer must be using some API calls through which this is possible.
Can any one help me in figure out what APIs are used by query analyzer in
text mode option.
Any help is appreciated.
Thanks
PushkarTo the best of my knowledge, you cannot use ADO or ADO.NET for this. QA uses
ODBC, and I have a
feeling that it is possible using OLEDB as well. So pick up a programmers ma
nual for one of those
APIs and check for asynchronous execution. But you should carefully consider
the extra work to use
these low level APIs compared to the productivity of a higher level API.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:eSsFbyTMGHA.2704@.TK2MSFTNGP15.phx
.gbl...
> Hi,
> I want to write a application works in same manner as SQL Query Analyzer w
orks in Text mode
> option. I studied the behavior of query analyzer and find out that in text
mode option query
> analyzer does not wait for the query to get finished, for displaying the r
esult.
> It diplays the result in Text form as and when it gets the result. It work
s in some different
> manner than Query Analyzer working in GRID option.
> I also want to write a application which does not wait for all the results
of the query, just give
> me the records which are available right now and then give the remaining o
nes also in similar
> chunks.
> Query Analyzer must be using some API calls through which this is possible
.
> Can any one help me in figure out what APIs are used by query analyzer in
text mode option.
> Any help is appreciated.
> Thanks
> Pushkar
>
>|||Pushkar wrote:
> Hi,
> I want to write a application works in same manner as SQL Query Analyzer
> works in Text mode option. I studied the behavior of query analyzer and fi
nd
> out that in text mode option query analyzer does not wait for the query to
> get finished, for displaying the result.
> It diplays the result in Text form as and when it gets the result. It work
s
> in some different manner than Query Analyzer working in GRID option.
> I also want to write a application which does not wait for all the results
> of the query, just give me the records which are available right now and
> then give the remaining ones also in similar chunks.
> Query Analyzer must be using some API calls through which this is possible
.
> Can any one help me in figure out what APIs are used by query analyzer in
> text mode option.
> Any help is appreciated.
> Thanks
> Pushkar
It may help you to read:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Good thinking, David. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139910046.905427.137570@.g43g2000cwa.googlegroups.com...
> Pushkar wrote:
> It may help you to read:
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
how query Active Directory from sql2k server?
I've seen this linked server syntax many times in the group. but it
didn't work for me, maybe someone can explain it better for me?
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
i know that:
* " ADSI" is the server argument, which will be the name of this
linked server.
* "Active Directory Services" is the srvproduct argument, which is
the name of the OLE DB data source that you are adding as a linked
server.
* "ADSDSOObject" is the provider_name argument and indicates you
are using the OLE DB Provider.
* "adsdatasource" is the data_source argument, which is the name of
the data source as interpreted by the OLE DB Provider.
my questions are:
what "ADSDSOObject" and "adsdatasource" should be in my case? do i
need an dsn for odbc, does the AD server needs to have an instance of
sql server and so on.
for example, if i have a sql2k server call 'sql2ksvr', and an active
directory server called 'adsvr'.
exactly what step by step instructions would be to set it up in a way
that i can query the groups and users information in AD?
thank you guys!!Hi Steve
You linked server should be as is, the open query should specify your
server. See the example at:
http://msdn.microsoft.com/library/d...eneous_data.asp
And the query such as:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
You will need to change the IP address (or server name) and thje OU/DC parts
.
This may also help:
http://msdn.microsoft.com/library/d...r />
_94fn.asp
John
"=== Steve L ===" wrote:
> I've seen this linked server syntax many times in the group. but it
> didn't work for me, maybe someone can explain it better for me?
> sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
> 'ADSDSOObject', 'adsdatasource'
> i know that:
> * " ADSI" is the server argument, which will be the name of this
> linked server.
> * "Active Directory Services" is the srvproduct argument, which is
> the name of the OLE DB data source that you are adding as a linked
> server.
> * "ADSDSOObject" is the provider_name argument and indicates you
> are using the OLE DB Provider.
> * "adsdatasource" is the data_source argument, which is the name of
> the data source as interpreted by the OLE DB Provider.
>
> my questions are:
> what "ADSDSOObject" and "adsdatasource" should be in my case? do i
> need an dsn for odbc, does the AD server needs to have an instance of
> sql server and so on.
> for example, if i have a sql2k server call 'sql2ksvr', and an active
> directory server called 'adsvr'.
> exactly what step by step instructions would be to set it up in a way
> that i can query the groups and users information in AD?
> thank you guys!!
>|||i ran the following scripts on my sql2k server
script#1
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
go
script#2
SELECT * FROM
OpenQuery( ADSI, 'SELECT ADsPath FROM "LDAP://DC=MyCompany's domain
name,DC=COM" WHERE
objectClass= "user" ')
and i've got the following error
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
ICommandPrepare::Prepare returned 0x80040e14].
is there a way to test if the linked server is working? or is there a
sample folder in AD i can query against with to see where the problems
are?
thank you.|||i finally figured how that works. i have to log into server directly
and run the query. and now i have a new question.
how to modify the following query so i can have the users and groups
they belong to? I just want to find out all the groups and users
relationship.
can some one tell me what fields available? I try to use * but it
didnt' work.
SELECT a.name, a.adspath, b.name, b.adspath
FROM OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
WHERE objectCategory = ''person''') a,
OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
WHERE objectCategory = ''group''') b|||Steve how did you finally get it to work because I am still getting that
error message running straight from the SQL Server.
"=== Steve L ===" wrote:
> i finally figured how that works. i have to log into server directly
> and run the query. and now i have a new question.
> how to modify the following query so i can have the users and groups
> they belong to? I just want to find out all the groups and users
> relationship.
> can some one tell me what fields available? I try to use * but it
> didnt' work.
> SELECT a.name, a.adspath, b.name, b.adspath
> FROM OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
> WHERE objectCategory = ''person''') a,
> OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
> WHERE objectCategory = ''group''') b
>
didn't work for me, maybe someone can explain it better for me?
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
i know that:
* " ADSI" is the server argument, which will be the name of this
linked server.
* "Active Directory Services" is the srvproduct argument, which is
the name of the OLE DB data source that you are adding as a linked
server.
* "ADSDSOObject" is the provider_name argument and indicates you
are using the OLE DB Provider.
* "adsdatasource" is the data_source argument, which is the name of
the data source as interpreted by the OLE DB Provider.
my questions are:
what "ADSDSOObject" and "adsdatasource" should be in my case? do i
need an dsn for odbc, does the AD server needs to have an instance of
sql server and so on.
for example, if i have a sql2k server call 'sql2ksvr', and an active
directory server called 'adsvr'.
exactly what step by step instructions would be to set it up in a way
that i can query the groups and users information in AD?
thank you guys!!Hi Steve
You linked server should be as is, the open query should specify your
server. See the example at:
http://msdn.microsoft.com/library/d...eneous_data.asp
And the query such as:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
You will need to change the IP address (or server name) and thje OU/DC parts
.
This may also help:
http://msdn.microsoft.com/library/d...r />
_94fn.asp
John
"=== Steve L ===" wrote:
> I've seen this linked server syntax many times in the group. but it
> didn't work for me, maybe someone can explain it better for me?
> sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
> 'ADSDSOObject', 'adsdatasource'
> i know that:
> * " ADSI" is the server argument, which will be the name of this
> linked server.
> * "Active Directory Services" is the srvproduct argument, which is
> the name of the OLE DB data source that you are adding as a linked
> server.
> * "ADSDSOObject" is the provider_name argument and indicates you
> are using the OLE DB Provider.
> * "adsdatasource" is the data_source argument, which is the name of
> the data source as interpreted by the OLE DB Provider.
>
> my questions are:
> what "ADSDSOObject" and "adsdatasource" should be in my case? do i
> need an dsn for odbc, does the AD server needs to have an instance of
> sql server and so on.
> for example, if i have a sql2k server call 'sql2ksvr', and an active
> directory server called 'adsvr'.
> exactly what step by step instructions would be to set it up in a way
> that i can query the groups and users information in AD?
> thank you guys!!
>|||i ran the following scripts on my sql2k server
script#1
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
go
script#2
SELECT * FROM
OpenQuery( ADSI, 'SELECT ADsPath FROM "LDAP://DC=MyCompany's domain
name,DC=COM" WHERE
objectClass= "user" ')
and i've got the following error
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
ICommandPrepare::Prepare returned 0x80040e14].
is there a way to test if the linked server is working? or is there a
sample folder in AD i can query against with to see where the problems
are?
thank you.|||i finally figured how that works. i have to log into server directly
and run the query. and now i have a new question.
how to modify the following query so i can have the users and groups
they belong to? I just want to find out all the groups and users
relationship.
can some one tell me what fields available? I try to use * but it
didnt' work.
SELECT a.name, a.adspath, b.name, b.adspath
FROM OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
WHERE objectCategory = ''person''') a,
OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
WHERE objectCategory = ''group''') b|||Steve how did you finally get it to work because I am still getting that
error message running straight from the SQL Server.
"=== Steve L ===" wrote:
> i finally figured how that works. i have to log into server directly
> and run the query. and now i have a new question.
> how to modify the following query so i can have the users and groups
> they belong to? I just want to find out all the groups and users
> relationship.
> can some one tell me what fields available? I try to use * but it
> didnt' work.
> SELECT a.name, a.adspath, b.name, b.adspath
> FROM OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
> WHERE objectCategory = ''person''') a,
> OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
> WHERE objectCategory = ''group''') b
>
How precise is a float?
in QA I have
select
sum(F_Basic_premium) TotalBasicPremium
from fat_bse_po_risk_detail(nolock)
where f_latest_sum_insured = 'Y'
-- 1. 22421075998346.898
-- 1. 22421075998346.594
select
sum(F_Basic_premium) TotalBasicPremium
from fat_bse_po_risk_detail(nolock)
where f_latest_sum_insured = 'Y'
-- 2. 22421075998347.285
-- 2. 22421075998346.680
Weird?Have you read the first paragraph under the topic "float and real" in SQL
Server Books Online?
Anith|||Float is not precise, it is approximate. If you want precise numbers, which
I assume you want with things like premiums, use the decimal datatype, in
this case decimal(17,3).
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:25AC21E1-119C-4725-99F0-78B98A2FF19D@.microsoft.com...
> in QA I have
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 1. 22421075998346.898
> -- 1. 22421075998346.594
>
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 2. 22421075998347.285
> -- 2. 22421075998346.680
>
> Weird?
>|||to answer your question directly, much more accurately than you need. The
degree of accuracy depends on what number you are trying to store... If you
are trying to store one of the numbers that a float can represent, then it's
100% Dead-on balls accurate... Floats are no different than any other
datatype in that they can represent any number in a set of numbers, what
matters is the distance between the individual numbers that they can
represent exactly... It's the same as with Integers, you can represent 1, or
2 exactly, but if you try to use an integer for 0.5, then the integer is
going to be at least .5 off... Or if you use a decimal(5,2) (2 decimal
places), to represent 0,12 or 0.13 exactly, but if you try to represent
0.125...
So with floats, the only difference is that a float stores binary numbers,
not decimals, and stores them in scientific notation format, not directly, a
s
an integer or decimal datatype does... With a float, which is 64 bits wide,
53 bits are used for the mantissa, and the remaining 11 are used for the
exponent. So the mantissa can be any binary number between minus 2 to the 5
2
power, and Plus 2 to the 52 power -1
So the "accuracy", so to speak of a float, in storing the mantissa portion
could be said to be within .000000000000000111022302462515654042% (thats 1
divided by 2 to the 53nd power)
The exponent is 11 bits and is an integral type, so is is 100% dead-on balls
accurate for any integer exponent... This gives floats their Min and max
values, which are:
Negative numbers: - 1.79E + 308 through -2.23E - 308,
and for
positive numbers: 2.23E -308 through 1.79E + 308.
and of course zero (0)
So, for any real number you pick, (within the range ) there is a float
within .000000000000000111022302462515654042% of that value.
"marcmc" wrote:
> in QA I have
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 1. 22421075998346.898
> -- 1. 22421075998346.594
>
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 2. 22421075998347.285
> -- 2. 22421075998346.680
>
> Weird?
>|||Marc,
A float is very precise - about 53 binary digits of accuracy, or about
15-16 decimal digits of accuracy. (It's not the issue here, but worth
noting that using a decimal value to represent a float is itself not
precise, since the exact values stored in floats are not all exactly
representable as decimals unless you use about 50+ decimal places.)
Most likely, the reason you are seeing only 13 or 14 digits of accuracy
is because of the sum() function. There is unavoidable inaccuracy in
floating point addition: the precise sum may contain more significant
digits than the addends, because of a "carry", but floats have a fixed
number of bits of precision, and there is truncation or rounding. The
order in which the SUM() is carried out can affect the approximate
answer. Here are some examples to show the difference that can result:
set nocount on
go
select sum(1e0) + sum(0.0000000077777e0) from Northwind..Orders
select sum(1.0+0.0000000077777e0) from Northwind..Orders
select sum(1e0/1101 + 77e0) from Northwind..Orders
select sum(partialSum) from (
select sum(1e0/1101 + 77e0) as partialSum
from Northwind..Orders
group by CustomerID
) T
Steve Kass
Drew University
marcmc wrote:
>in QA I have
>select
>sum(F_Basic_premium) TotalBasicPremium
>from fat_bse_po_risk_detail(nolock)
>where f_latest_sum_insured = 'Y'
>-- 1. 22421075998346.898
>-- 1. 22421075998346.594
>
>select
>sum(F_Basic_premium) TotalBasicPremium
>from fat_bse_po_risk_detail(nolock)
>where f_latest_sum_insured = 'Y'
>-- 2. 22421075998347.285
>-- 2. 22421075998346.680
>
>Weird?
>
>|||While float is not precise, it is predictable right? Are you saying you ran
the same query, on the same data set, in the same table structures,
everything the same, other than on different servers? Why the nolock? Are
you sure that noone is changing the data? Can you give more details?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:25AC21E1-119C-4725-99F0-78B98A2FF19D@.microsoft.com...
> in QA I have
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 1. 22421075998346.898
> -- 1. 22421075998346.594
>
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 2. 22421075998347.285
> -- 2. 22421075998346.680
>
> Weird?
>|||Jacco,
I would add that precise vs. approximate is not the best way to
characterize the difference between decimals and floats. Both have
limited precision, and both represent a certain set of rational numbers
exactly.
There are two important differences:
1. Floats use a "floating-point" representation, whereas decimals use a
"fixed-point" representation.
As a result, the values a float can store have greater extremes than
decimal values, but the values a decimal can store are equally spaced on
the number line, where float-storable values are tightly packed near
zero and very far apart away from zero.
2. Decimal precision is based on base-10 digits, and float precision is
based on base-2 digits.
As a result, decimal-storable numbers can be converted to human-readable
strings more conveniently than floats. A decimal with 20 digits of
precision requires at most 22 ASCII characters to represent it exactly
in human-readable form. On the other hand, a float with ~ 54 binary
digits of precision (which is only about "15 decimal places of
accuracy," to say it in a sloppy but familiar way), can require more
than 50 ASCII characters to represent it exactly in human-readable form
(allowing "scientific notation", which saves space), since floats are
based on binary, not decimal fractions: 1/2 = 0.5, 1/4 = 0.25, 1/8 =
0.125, 1/2^ 53 = 0.[53 decimal digits]. Rarely are client tools built
to display float exactly, though they could be. Because of this, floats
often suffer unnecessary loss of accuracy when they are converted to
ASCII and back. This happens less often for decimals, because they
"play well" with ASCII representations.
That said, your remark is much better in terms of practical advice than
mine. ;)
SK
Jacco Schalkwijk wrote:
>Float is not precise, it is approximate. If you want precise numbers, which
>I assume you want with things like premiums, use the decimal datatype, in
>this case decimal(17,3).
>
>|||There is one twist - numbers in "binary scientific notation" can always
be written as
M times 2^E
for M >= 0.5 and M < 1 (any other range with endpoints in 2/1 ratio
works, too)
Represented in binary, then, every possibly value of M looks like
0.1xxxxxxx. Some
very clever person realized that if every value of M has a 1 after the
twoimal point, we
don't need to store it. So only the xxxxxx.. are stored. We avoid
wasting space, and
we don't end up with all kinds of multiple representations of the same
number, like
[written in base 2 here] 0.011 * 1000 = 0.0011 * 10000 = 0.11 * 100
... (not
wasting space and avoiding duplicates are really the same thing - math
stuff about
injections and pigeonholes)
So the 1 is not stored, and there's a "free" extra bit of accuracy.
There are also some quirks to allow the representation of some numbers
closer
to zero than +/- 10^308, with reduced precision, and for storing (and
making sense
of!) +/- infinity. A good discussion is here:
http://stevehollasch.com/cgindex/coding/ieeefloat.html
SK
CBretana wrote:
>to answer your question directly, much more accurately than you need. The
>degree of accuracy depends on what number you are trying to store... If you
>are trying to store one of the numbers that a float can represent, then it'
s
>100% Dead-on balls accurate... Floats are no different than any other
>datatype in that they can represent any number in a set of numbers, what
>matters is the distance between the individual numbers that they can
>represent exactly... It's the same as with Integers, you can represent 1, o
r
>2 exactly, but if you try to use an integer for 0.5, then the integer is
>going to be at least .5 off... Or if you use a decimal(5,2) (2 decimal
>places), to represent 0,12 or 0.13 exactly, but if you try to represent
>0.125...
>So with floats, the only difference is that a float stores binary numbers,
>not decimals, and stores them in scientific notation format, not directly,
as
>an integer or decimal datatype does... With a float, which is 64 bits wide,
>53 bits are used for the mantissa, and the remaining 11 are used for the
>exponent. So the mantissa can be any binary number between minus 2 to the
52
>power, and Plus 2 to the 52 power -1
>So the "accuracy", so to speak of a float, in storing the mantissa portion
>could be said to be within .000000000000000111022302462515654042% (thats
1
>divided by 2 to the 53nd power)
>The exponent is 11 bits and is an integral type, so is is 100% dead-on ball
s
>accurate for any integer exponent... This gives floats their Min and max
>values, which are:
>Negative numbers: - 1.79E + 308 through -2.23E - 308,
>and for
>positive numbers: 2.23E -308 through 1.79E + 308.
>and of course zero (0)
>So, for any real number you pick, (within the range ) there is a float
>within .000000000000000111022302462515654042% of that value.
>
>
>"marcmc" wrote:
>
>|||The results of arithmetic on floats is not deterministic unless the order
in which the arithmetic takes place is deterministic. Especially for
SUM() or another aggregate, we shouldn't expect the floats to be
added in the same order every time, so we shouldn't expect the same
result.
A similar thing happens with decimals, but as far as I know, it can't
be because of changes in row order. Instead, it's got to do with how
expressions are typed:
select sum(1.1101111111 + 77.0) from Northwind..Orders
select sum(1.1101111111) + sum(77.0) from Northwind..Orders
SK
Louis Davidson wrote:
>While float is not precise, it is predictable right? Are you saying you ra
n
>the same query, on the same data set, in the same table structures,
>everything the same, other than on different servers? Why the nolock? Are
>you sure that noone is changing the data? Can you give more details?
>
>|||thx All
select
sum(F_Basic_premium) TotalBasicPremium
from fat_bse_po_risk_detail(nolock)
where f_latest_sum_insured = 'Y'
-- 1. 22421075998346.898
-- 1. 22421075998346.594
select
sum(F_Basic_premium) TotalBasicPremium
from fat_bse_po_risk_detail(nolock)
where f_latest_sum_insured = 'Y'
-- 2. 22421075998347.285
-- 2. 22421075998346.680
Weird?Have you read the first paragraph under the topic "float and real" in SQL
Server Books Online?
Anith|||Float is not precise, it is approximate. If you want precise numbers, which
I assume you want with things like premiums, use the decimal datatype, in
this case decimal(17,3).
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:25AC21E1-119C-4725-99F0-78B98A2FF19D@.microsoft.com...
> in QA I have
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 1. 22421075998346.898
> -- 1. 22421075998346.594
>
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 2. 22421075998347.285
> -- 2. 22421075998346.680
>
> Weird?
>|||to answer your question directly, much more accurately than you need. The
degree of accuracy depends on what number you are trying to store... If you
are trying to store one of the numbers that a float can represent, then it's
100% Dead-on balls accurate... Floats are no different than any other
datatype in that they can represent any number in a set of numbers, what
matters is the distance between the individual numbers that they can
represent exactly... It's the same as with Integers, you can represent 1, or
2 exactly, but if you try to use an integer for 0.5, then the integer is
going to be at least .5 off... Or if you use a decimal(5,2) (2 decimal
places), to represent 0,12 or 0.13 exactly, but if you try to represent
0.125...
So with floats, the only difference is that a float stores binary numbers,
not decimals, and stores them in scientific notation format, not directly, a
s
an integer or decimal datatype does... With a float, which is 64 bits wide,
53 bits are used for the mantissa, and the remaining 11 are used for the
exponent. So the mantissa can be any binary number between minus 2 to the 5
2
power, and Plus 2 to the 52 power -1
So the "accuracy", so to speak of a float, in storing the mantissa portion
could be said to be within .000000000000000111022302462515654042% (thats 1
divided by 2 to the 53nd power)
The exponent is 11 bits and is an integral type, so is is 100% dead-on balls
accurate for any integer exponent... This gives floats their Min and max
values, which are:
Negative numbers: - 1.79E + 308 through -2.23E - 308,
and for
positive numbers: 2.23E -308 through 1.79E + 308.
and of course zero (0)
So, for any real number you pick, (within the range ) there is a float
within .000000000000000111022302462515654042% of that value.
"marcmc" wrote:
> in QA I have
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 1. 22421075998346.898
> -- 1. 22421075998346.594
>
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 2. 22421075998347.285
> -- 2. 22421075998346.680
>
> Weird?
>|||Marc,
A float is very precise - about 53 binary digits of accuracy, or about
15-16 decimal digits of accuracy. (It's not the issue here, but worth
noting that using a decimal value to represent a float is itself not
precise, since the exact values stored in floats are not all exactly
representable as decimals unless you use about 50+ decimal places.)
Most likely, the reason you are seeing only 13 or 14 digits of accuracy
is because of the sum() function. There is unavoidable inaccuracy in
floating point addition: the precise sum may contain more significant
digits than the addends, because of a "carry", but floats have a fixed
number of bits of precision, and there is truncation or rounding. The
order in which the SUM() is carried out can affect the approximate
answer. Here are some examples to show the difference that can result:
set nocount on
go
select sum(1e0) + sum(0.0000000077777e0) from Northwind..Orders
select sum(1.0+0.0000000077777e0) from Northwind..Orders
select sum(1e0/1101 + 77e0) from Northwind..Orders
select sum(partialSum) from (
select sum(1e0/1101 + 77e0) as partialSum
from Northwind..Orders
group by CustomerID
) T
Steve Kass
Drew University
marcmc wrote:
>in QA I have
>select
>sum(F_Basic_premium) TotalBasicPremium
>from fat_bse_po_risk_detail(nolock)
>where f_latest_sum_insured = 'Y'
>-- 1. 22421075998346.898
>-- 1. 22421075998346.594
>
>select
>sum(F_Basic_premium) TotalBasicPremium
>from fat_bse_po_risk_detail(nolock)
>where f_latest_sum_insured = 'Y'
>-- 2. 22421075998347.285
>-- 2. 22421075998346.680
>
>Weird?
>
>|||While float is not precise, it is predictable right? Are you saying you ran
the same query, on the same data set, in the same table structures,
everything the same, other than on different servers? Why the nolock? Are
you sure that noone is changing the data? Can you give more details?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:25AC21E1-119C-4725-99F0-78B98A2FF19D@.microsoft.com...
> in QA I have
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 1. 22421075998346.898
> -- 1. 22421075998346.594
>
> select
> sum(F_Basic_premium) TotalBasicPremium
> from fat_bse_po_risk_detail(nolock)
> where f_latest_sum_insured = 'Y'
> -- 2. 22421075998347.285
> -- 2. 22421075998346.680
>
> Weird?
>|||Jacco,
I would add that precise vs. approximate is not the best way to
characterize the difference between decimals and floats. Both have
limited precision, and both represent a certain set of rational numbers
exactly.
There are two important differences:
1. Floats use a "floating-point" representation, whereas decimals use a
"fixed-point" representation.
As a result, the values a float can store have greater extremes than
decimal values, but the values a decimal can store are equally spaced on
the number line, where float-storable values are tightly packed near
zero and very far apart away from zero.
2. Decimal precision is based on base-10 digits, and float precision is
based on base-2 digits.
As a result, decimal-storable numbers can be converted to human-readable
strings more conveniently than floats. A decimal with 20 digits of
precision requires at most 22 ASCII characters to represent it exactly
in human-readable form. On the other hand, a float with ~ 54 binary
digits of precision (which is only about "15 decimal places of
accuracy," to say it in a sloppy but familiar way), can require more
than 50 ASCII characters to represent it exactly in human-readable form
(allowing "scientific notation", which saves space), since floats are
based on binary, not decimal fractions: 1/2 = 0.5, 1/4 = 0.25, 1/8 =
0.125, 1/2^ 53 = 0.[53 decimal digits]. Rarely are client tools built
to display float exactly, though they could be. Because of this, floats
often suffer unnecessary loss of accuracy when they are converted to
ASCII and back. This happens less often for decimals, because they
"play well" with ASCII representations.
That said, your remark is much better in terms of practical advice than
mine. ;)
SK
Jacco Schalkwijk wrote:
>Float is not precise, it is approximate. If you want precise numbers, which
>I assume you want with things like premiums, use the decimal datatype, in
>this case decimal(17,3).
>
>|||There is one twist - numbers in "binary scientific notation" can always
be written as
M times 2^E
for M >= 0.5 and M < 1 (any other range with endpoints in 2/1 ratio
works, too)
Represented in binary, then, every possibly value of M looks like
0.1xxxxxxx. Some
very clever person realized that if every value of M has a 1 after the
twoimal point, we
don't need to store it. So only the xxxxxx.. are stored. We avoid
wasting space, and
we don't end up with all kinds of multiple representations of the same
number, like
[written in base 2 here] 0.011 * 1000 = 0.0011 * 10000 = 0.11 * 100
... (not
wasting space and avoiding duplicates are really the same thing - math
stuff about
injections and pigeonholes)
So the 1 is not stored, and there's a "free" extra bit of accuracy.
There are also some quirks to allow the representation of some numbers
closer
to zero than +/- 10^308, with reduced precision, and for storing (and
making sense
of!) +/- infinity. A good discussion is here:
http://stevehollasch.com/cgindex/coding/ieeefloat.html
SK
CBretana wrote:
>to answer your question directly, much more accurately than you need. The
>degree of accuracy depends on what number you are trying to store... If you
>are trying to store one of the numbers that a float can represent, then it'
s
>100% Dead-on balls accurate... Floats are no different than any other
>datatype in that they can represent any number in a set of numbers, what
>matters is the distance between the individual numbers that they can
>represent exactly... It's the same as with Integers, you can represent 1, o
r
>2 exactly, but if you try to use an integer for 0.5, then the integer is
>going to be at least .5 off... Or if you use a decimal(5,2) (2 decimal
>places), to represent 0,12 or 0.13 exactly, but if you try to represent
>0.125...
>So with floats, the only difference is that a float stores binary numbers,
>not decimals, and stores them in scientific notation format, not directly,
as
>an integer or decimal datatype does... With a float, which is 64 bits wide,
>53 bits are used for the mantissa, and the remaining 11 are used for the
>exponent. So the mantissa can be any binary number between minus 2 to the
52
>power, and Plus 2 to the 52 power -1
>So the "accuracy", so to speak of a float, in storing the mantissa portion
>could be said to be within .000000000000000111022302462515654042% (thats
1
>divided by 2 to the 53nd power)
>The exponent is 11 bits and is an integral type, so is is 100% dead-on ball
s
>accurate for any integer exponent... This gives floats their Min and max
>values, which are:
>Negative numbers: - 1.79E + 308 through -2.23E - 308,
>and for
>positive numbers: 2.23E -308 through 1.79E + 308.
>and of course zero (0)
>So, for any real number you pick, (within the range ) there is a float
>within .000000000000000111022302462515654042% of that value.
>
>
>"marcmc" wrote:
>
>|||The results of arithmetic on floats is not deterministic unless the order
in which the arithmetic takes place is deterministic. Especially for
SUM() or another aggregate, we shouldn't expect the floats to be
added in the same order every time, so we shouldn't expect the same
result.
A similar thing happens with decimals, but as far as I know, it can't
be because of changes in row order. Instead, it's got to do with how
expressions are typed:
select sum(1.1101111111 + 77.0) from Northwind..Orders
select sum(1.1101111111) + sum(77.0) from Northwind..Orders
SK
Louis Davidson wrote:
>While float is not precise, it is predictable right? Are you saying you ra
n
>the same query, on the same data set, in the same table structures,
>everything the same, other than on different servers? Why the nolock? Are
>you sure that noone is changing the data? Can you give more details?
>
>|||thx All
How powerful is sqlserver?
I know this is a very open ended question, but it is one that I have not yet
found an answer to. How powerful is sql server?
I appreciate that there are ways of making it more efficient, but surely
there are limits. For example:
how many records can it handle in a database before it just becomes too big?
How many requests can it run concurrently?
I understand that this can all be expanded through clustering, etc. but lets
just assume I only have a single license, which i do.
Thanks. I look forward to hearing what you all have to say.>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
How fast is fast? There is no SQL Server speed limit. Performance is
mostly a function of hardware and application design.
> how many records can it handle in a database before it just becomes too
> big?
A table size is technically limited only by available storage. I've had
single table with billions of rows with excellent performance (properly
indexed of course). A consideration is manageability, though. Very large
tables are usually partitioned for manageability rather than performance.
> How many requests can it run concurrently?
It depends what you mean by 'concurrently'. A CPU (logical processor) can
run only one request at a time so the maximum number of active queries using
CPU is equal to the number of available (logical) processors. In practice,
queries often spend a considerable amount of time waiting for disk or
network I/O to complete and SQL Server can manage hundreds of concurrent
queries in a variety of states. The end result is that thousands of
transactions per second can be executed on a single server in a typical OLTP
profile.
Hope this helps.
Dan Guzman
SQL Server MVP
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.|||"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.
See:
http://www.microsoft.com/sql/prodinfo/compare/benchmarks.mspx
http://www.wintercorp.com/VLDB/2005_TopTen_Survey/TopTenWinners_2005.asp
http://www.tpc.org/
In view of the fact that you are asking this question I'd guess the right
answer is probably "More powerful than you need". :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks guys. I'm putting together an idea for a company, but it will be quite
database intensive, so i really need to find out what my technical
requirements are.
"David Portas" wrote:
> "P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
> news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
> >I know this is a very open ended question, but it is one that I have not
> >yet
> > found an answer to. How powerful is sql server?
> >
> > I appreciate that there are ways of making it more efficient, but surely
> > there are limits. For example:
> >
> > how many records can it handle in a database before it just becomes too
> > big?
> > How many requests can it run concurrently?
> >
> > I understand that this can all be expanded through clustering, etc. but
> > lets
> > just assume I only have a single license, which i do.
> >
> > Thanks. I look forward to hearing what you all have to say.
> See:
> http://www.microsoft.com/sql/prodinfo/compare/benchmarks.mspx
> http://www.wintercorp.com/VLDB/2005_TopTen_Survey/TopTenWinners_2005.asp
> http://www.tpc.org/
> In view of the fact that you are asking this question I'd guess the right
> answer is probably "More powerful than you need". :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||On Wed, 15 Feb 2006 06:51:32 -0800, "P1ayboy"
<P1ayboy@.discussions.microsoft.com> wrote:
>Thanks guys. I'm putting together an idea for a company, but it will be quite
>database intensive, so i really need to find out what my technical
>requirements are.
I've run SQLServer on some modest but current hardware and just been
amazed at its capacity.
Remember that a lot depends on the other tiers, a sloppy middle tier
that generates 100x more load than it should, and SQLServer isn't
really the issue.
Properly configured server should handle a couple hundred simple
selects per second on a well-designed database, assuming that most of
the frequently accessed data can stay in RAM. If you have to scan
terabyte tables, you're into hardware constraints, not really
SQLServer. If your database tier logic is complex, you'll have to
provide a few more hints!
OTOH, if your "server" is a single processor with a single physical
disk, capacity will be a LOT lower.
All very rough, your mileage may vary, advice worth what you paid for
it, have a nice day.
J.|||It depends upon how you define "database intensive". I've dealt with
customer who serve 100% of the content for their website dynamically
straight out of SQL Server. One of these has tens of millions of users,
hundreds of thousands of which are banging on the website at any given time,
billions of pages of content served every day, and tens of billions of
database requests every day. The trick all goes back to database design,
application code, and indexing. I have seen entire financial exchanges
running on a single SQL Server. So, in terms of what can be done, I don't
know of a single application ever built that can not be handled by SQL
Server.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:A78A40EA-A80F-4178-BC6A-B757F21DA9B3@.microsoft.com...
> Thanks guys. I'm putting together an idea for a company, but it will be
> quite
> database intensive, so i really need to find out what my technical
> requirements are.
> "David Portas" wrote:
>> "P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
>> news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>> >I know this is a very open ended question, but it is one that I have not
>> >yet
>> > found an answer to. How powerful is sql server?
>> >
>> > I appreciate that there are ways of making it more efficient, but
>> > surely
>> > there are limits. For example:
>> >
>> > how many records can it handle in a database before it just becomes too
>> > big?
>> > How many requests can it run concurrently?
>> >
>> > I understand that this can all be expanded through clustering, etc. but
>> > lets
>> > just assume I only have a single license, which i do.
>> >
>> > Thanks. I look forward to hearing what you all have to say.
>> See:
>> http://www.microsoft.com/sql/prodinfo/compare/benchmarks.mspx
>> http://www.wintercorp.com/VLDB/2005_TopTen_Survey/TopTenWinners_2005.asp
>> http://www.tpc.org/
>> In view of the fact that you are asking this question I'd guess the right
>> answer is probably "More powerful than you need". :-)
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>sql
found an answer to. How powerful is sql server?
I appreciate that there are ways of making it more efficient, but surely
there are limits. For example:
how many records can it handle in a database before it just becomes too big?
How many requests can it run concurrently?
I understand that this can all be expanded through clustering, etc. but lets
just assume I only have a single license, which i do.
Thanks. I look forward to hearing what you all have to say.>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
How fast is fast? There is no SQL Server speed limit. Performance is
mostly a function of hardware and application design.
> how many records can it handle in a database before it just becomes too
> big?
A table size is technically limited only by available storage. I've had
single table with billions of rows with excellent performance (properly
indexed of course). A consideration is manageability, though. Very large
tables are usually partitioned for manageability rather than performance.
> How many requests can it run concurrently?
It depends what you mean by 'concurrently'. A CPU (logical processor) can
run only one request at a time so the maximum number of active queries using
CPU is equal to the number of available (logical) processors. In practice,
queries often spend a considerable amount of time waiting for disk or
network I/O to complete and SQL Server can manage hundreds of concurrent
queries in a variety of states. The end result is that thousands of
transactions per second can be executed on a single server in a typical OLTP
profile.
Hope this helps.
Dan Guzman
SQL Server MVP
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.|||"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.
See:
http://www.microsoft.com/sql/prodinfo/compare/benchmarks.mspx
http://www.wintercorp.com/VLDB/2005_TopTen_Survey/TopTenWinners_2005.asp
http://www.tpc.org/
In view of the fact that you are asking this question I'd guess the right
answer is probably "More powerful than you need". :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks guys. I'm putting together an idea for a company, but it will be quite
database intensive, so i really need to find out what my technical
requirements are.
"David Portas" wrote:
> "P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
> news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
> >I know this is a very open ended question, but it is one that I have not
> >yet
> > found an answer to. How powerful is sql server?
> >
> > I appreciate that there are ways of making it more efficient, but surely
> > there are limits. For example:
> >
> > how many records can it handle in a database before it just becomes too
> > big?
> > How many requests can it run concurrently?
> >
> > I understand that this can all be expanded through clustering, etc. but
> > lets
> > just assume I only have a single license, which i do.
> >
> > Thanks. I look forward to hearing what you all have to say.
> See:
> http://www.microsoft.com/sql/prodinfo/compare/benchmarks.mspx
> http://www.wintercorp.com/VLDB/2005_TopTen_Survey/TopTenWinners_2005.asp
> http://www.tpc.org/
> In view of the fact that you are asking this question I'd guess the right
> answer is probably "More powerful than you need". :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||On Wed, 15 Feb 2006 06:51:32 -0800, "P1ayboy"
<P1ayboy@.discussions.microsoft.com> wrote:
>Thanks guys. I'm putting together an idea for a company, but it will be quite
>database intensive, so i really need to find out what my technical
>requirements are.
I've run SQLServer on some modest but current hardware and just been
amazed at its capacity.
Remember that a lot depends on the other tiers, a sloppy middle tier
that generates 100x more load than it should, and SQLServer isn't
really the issue.
Properly configured server should handle a couple hundred simple
selects per second on a well-designed database, assuming that most of
the frequently accessed data can stay in RAM. If you have to scan
terabyte tables, you're into hardware constraints, not really
SQLServer. If your database tier logic is complex, you'll have to
provide a few more hints!
OTOH, if your "server" is a single processor with a single physical
disk, capacity will be a LOT lower.
All very rough, your mileage may vary, advice worth what you paid for
it, have a nice day.
J.|||It depends upon how you define "database intensive". I've dealt with
customer who serve 100% of the content for their website dynamically
straight out of SQL Server. One of these has tens of millions of users,
hundreds of thousands of which are banging on the website at any given time,
billions of pages of content served every day, and tens of billions of
database requests every day. The trick all goes back to database design,
application code, and indexing. I have seen entire financial exchanges
running on a single SQL Server. So, in terms of what can be done, I don't
know of a single application ever built that can not be handled by SQL
Server.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:A78A40EA-A80F-4178-BC6A-B757F21DA9B3@.microsoft.com...
> Thanks guys. I'm putting together an idea for a company, but it will be
> quite
> database intensive, so i really need to find out what my technical
> requirements are.
> "David Portas" wrote:
>> "P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
>> news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>> >I know this is a very open ended question, but it is one that I have not
>> >yet
>> > found an answer to. How powerful is sql server?
>> >
>> > I appreciate that there are ways of making it more efficient, but
>> > surely
>> > there are limits. For example:
>> >
>> > how many records can it handle in a database before it just becomes too
>> > big?
>> > How many requests can it run concurrently?
>> >
>> > I understand that this can all be expanded through clustering, etc. but
>> > lets
>> > just assume I only have a single license, which i do.
>> >
>> > Thanks. I look forward to hearing what you all have to say.
>> See:
>> http://www.microsoft.com/sql/prodinfo/compare/benchmarks.mspx
>> http://www.wintercorp.com/VLDB/2005_TopTen_Survey/TopTenWinners_2005.asp
>> http://www.tpc.org/
>> In view of the fact that you are asking this question I'd guess the right
>> answer is probably "More powerful than you need". :-)
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>sql
How powerful is sqlserver?
I know this is a very open ended question, but it is one that I have not yet
found an answer to. How powerful is sql server?
I appreciate that there are ways of making it more efficient, but surely
there are limits. For example:
how many records can it handle in a database before it just becomes too big?
How many requests can it run concurrently?
I understand that this can all be expanded through clustering, etc. but lets
just assume I only have a single license, which i do.
Thanks. I look forward to hearing what you all have to say.
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
How fast is fast? There is no SQL Server speed limit. Performance is
mostly a function of hardware and application design.
> how many records can it handle in a database before it just becomes too
> big?
A table size is technically limited only by available storage. I've had
single table with billions of rows with excellent performance (properly
indexed of course). A consideration is manageability, though. Very large
tables are usually partitioned for manageability rather than performance.
> How many requests can it run concurrently?
It depends what you mean by 'concurrently'. A CPU (logical processor) can
run only one request at a time so the maximum number of active queries using
CPU is equal to the number of available (logical) processors. In practice,
queries often spend a considerable amount of time waiting for disk or
network I/O to complete and SQL Server can manage hundreds of concurrent
queries in a variety of states. The end result is that thousands of
transactions per second can be executed on a single server in a typical OLTP
profile.
Hope this helps.
Dan Guzman
SQL Server MVP
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.
|||"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.
See:
http://www.microsoft.com/sql/prodinf...enchmarks.mspx
http://www.wintercorp.com/VLDB/2005_...nners_2005.asp
http://www.tpc.org/
In view of the fact that you are asking this question I'd guess the right
answer is probably "More powerful than you need". :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks guys. I'm putting together an idea for a company, but it will be quite
database intensive, so i really need to find out what my technical
requirements are.
"David Portas" wrote:
> "P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
> news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
> See:
> http://www.microsoft.com/sql/prodinf...enchmarks.mspx
> http://www.wintercorp.com/VLDB/2005_...nners_2005.asp
> http://www.tpc.org/
> In view of the fact that you are asking this question I'd guess the right
> answer is probably "More powerful than you need". :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
|||On Wed, 15 Feb 2006 06:51:32 -0800, "P1ayboy"
<P1ayboy@.discussions.microsoft.com> wrote:
>Thanks guys. I'm putting together an idea for a company, but it will be quite
>database intensive, so i really need to find out what my technical
>requirements are.
I've run SQLServer on some modest but current hardware and just been
amazed at its capacity.
Remember that a lot depends on the other tiers, a sloppy middle tier
that generates 100x more load than it should, and SQLServer isn't
really the issue.
Properly configured server should handle a couple hundred simple
selects per second on a well-designed database, assuming that most of
the frequently accessed data can stay in RAM. If you have to scan
terabyte tables, you're into hardware constraints, not really
SQLServer. If your database tier logic is complex, you'll have to
provide a few more hints!
OTOH, if your "server" is a single processor with a single physical
disk, capacity will be a LOT lower.
All very rough, your mileage may vary, advice worth what you paid for
it, have a nice day.
J.
|||It depends upon how you define "database intensive". I've dealt with
customer who serve 100% of the content for their website dynamically
straight out of SQL Server. One of these has tens of millions of users,
hundreds of thousands of which are banging on the website at any given time,
billions of pages of content served every day, and tens of billions of
database requests every day. The trick all goes back to database design,
application code, and indexing. I have seen entire financial exchanges
running on a single SQL Server. So, in terms of what can be done, I don't
know of a single application ever built that can not be handled by SQL
Server.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:A78A40EA-A80F-4178-BC6A-B757F21DA9B3@.microsoft.com...[vbcol=seagreen]
> Thanks guys. I'm putting together an idea for a company, but it will be
> quite
> database intensive, so i really need to find out what my technical
> requirements are.
> "David Portas" wrote:
found an answer to. How powerful is sql server?
I appreciate that there are ways of making it more efficient, but surely
there are limits. For example:
how many records can it handle in a database before it just becomes too big?
How many requests can it run concurrently?
I understand that this can all be expanded through clustering, etc. but lets
just assume I only have a single license, which i do.
Thanks. I look forward to hearing what you all have to say.
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
How fast is fast? There is no SQL Server speed limit. Performance is
mostly a function of hardware and application design.
> how many records can it handle in a database before it just becomes too
> big?
A table size is technically limited only by available storage. I've had
single table with billions of rows with excellent performance (properly
indexed of course). A consideration is manageability, though. Very large
tables are usually partitioned for manageability rather than performance.
> How many requests can it run concurrently?
It depends what you mean by 'concurrently'. A CPU (logical processor) can
run only one request at a time so the maximum number of active queries using
CPU is equal to the number of available (logical) processors. In practice,
queries often spend a considerable amount of time waiting for disk or
network I/O to complete and SQL Server can manage hundreds of concurrent
queries in a variety of states. The end result is that thousands of
transactions per second can be executed on a single server in a typical OLTP
profile.
Hope this helps.
Dan Guzman
SQL Server MVP
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.
|||"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.
See:
http://www.microsoft.com/sql/prodinf...enchmarks.mspx
http://www.wintercorp.com/VLDB/2005_...nners_2005.asp
http://www.tpc.org/
In view of the fact that you are asking this question I'd guess the right
answer is probably "More powerful than you need". :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks guys. I'm putting together an idea for a company, but it will be quite
database intensive, so i really need to find out what my technical
requirements are.
"David Portas" wrote:
> "P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
> news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
> See:
> http://www.microsoft.com/sql/prodinf...enchmarks.mspx
> http://www.wintercorp.com/VLDB/2005_...nners_2005.asp
> http://www.tpc.org/
> In view of the fact that you are asking this question I'd guess the right
> answer is probably "More powerful than you need". :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
|||On Wed, 15 Feb 2006 06:51:32 -0800, "P1ayboy"
<P1ayboy@.discussions.microsoft.com> wrote:
>Thanks guys. I'm putting together an idea for a company, but it will be quite
>database intensive, so i really need to find out what my technical
>requirements are.
I've run SQLServer on some modest but current hardware and just been
amazed at its capacity.
Remember that a lot depends on the other tiers, a sloppy middle tier
that generates 100x more load than it should, and SQLServer isn't
really the issue.
Properly configured server should handle a couple hundred simple
selects per second on a well-designed database, assuming that most of
the frequently accessed data can stay in RAM. If you have to scan
terabyte tables, you're into hardware constraints, not really
SQLServer. If your database tier logic is complex, you'll have to
provide a few more hints!
OTOH, if your "server" is a single processor with a single physical
disk, capacity will be a LOT lower.
All very rough, your mileage may vary, advice worth what you paid for
it, have a nice day.
J.
|||It depends upon how you define "database intensive". I've dealt with
customer who serve 100% of the content for their website dynamically
straight out of SQL Server. One of these has tens of millions of users,
hundreds of thousands of which are banging on the website at any given time,
billions of pages of content served every day, and tens of billions of
database requests every day. The trick all goes back to database design,
application code, and indexing. I have seen entire financial exchanges
running on a single SQL Server. So, in terms of what can be done, I don't
know of a single application ever built that can not be handled by SQL
Server.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:A78A40EA-A80F-4178-BC6A-B757F21DA9B3@.microsoft.com...[vbcol=seagreen]
> Thanks guys. I'm putting together an idea for a company, but it will be
> quite
> database intensive, so i really need to find out what my technical
> requirements are.
> "David Portas" wrote:
How powerful is sqlserver?
I know this is a very open ended question, but it is one that I have not yet
found an answer to. How powerful is sql server?
I appreciate that there are ways of making it more efficient, but surely
there are limits. For example:
how many records can it handle in a database before it just becomes too big?
How many requests can it run concurrently?
I understand that this can all be expanded through clustering, etc. but lets
just assume I only have a single license, which i do.
Thanks. I look forward to hearing what you all have to say.>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
How fast is fast? There is no SQL Server speed limit. Performance is
mostly a function of hardware and application design.
> how many records can it handle in a database before it just becomes too
> big?
A table size is technically limited only by available storage. I've had
single table with billions of rows with excellent performance (properly
indexed of course). A consideration is manageability, though. Very large
tables are usually partitioned for manageability rather than performance.
> How many requests can it run concurrently?
It depends what you mean by 'concurrently'. A CPU (logical processor) can
run only one request at a time so the maximum number of active queries using
CPU is equal to the number of available (logical) processors. In practice,
queries often spend a considerable amount of time waiting for disk or
network I/O to complete and SQL Server can manage hundreds of concurrent
queries in a variety of states. The end result is that thousands of
transactions per second can be executed on a single server in a typical OLTP
profile.
Hope this helps.
Dan Guzman
SQL Server MVP
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.|||"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.
See:
http://www.microsoft.com/sql/prodin...benchmarks.mspx
http://www.wintercorp.com/VLDB/2005...inners_2005.asp
http://www.tpc.org/
In view of the fact that you are asking this question I'd guess the right
answer is probably "More powerful than you need". :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks guys. I'm putting together an idea for a company, but it will be quit
e
database intensive, so i really need to find out what my technical
requirements are.
"David Portas" wrote:
> "P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
> news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
> See:
> http://www.microsoft.com/sql/prodin...benchmarks.mspx
> http://www.wintercorp.com/VLDB/2005...inners_2005.asp
> http://www.tpc.org/
> In view of the fact that you are asking this question I'd guess the right
> answer is probably "More powerful than you need". :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||On Wed, 15 Feb 2006 06:51:32 -0800, "P1ayboy"
<P1ayboy@.discussions.microsoft.com> wrote:
>Thanks guys. I'm putting together an idea for a company, but it will be qui
te
>database intensive, so i really need to find out what my technical
>requirements are.
I've run SQLServer on some modest but current hardware and just been
amazed at its capacity.
Remember that a lot depends on the other tiers, a sloppy middle tier
that generates 100x more load than it should, and SQLServer isn't
really the issue.
Properly configured server should handle a couple hundred simple
selects per second on a well-designed database, assuming that most of
the frequently accessed data can stay in RAM. If you have to scan
terabyte tables, you're into hardware constraints, not really
SQLServer. If your database tier logic is complex, you'll have to
provide a few more hints!
OTOH, if your "server" is a single processor with a single physical
disk, capacity will be a LOT lower.
All very rough, your mileage may vary, advice worth what you paid for
it, have a nice day.
J.|||It depends upon how you define "database intensive". I've dealt with
customer who serve 100% of the content for their website dynamically
straight out of SQL Server. One of these has tens of millions of users,
hundreds of thousands of which are banging on the website at any given time,
billions of pages of content served every day, and tens of billions of
database requests every day. The trick all goes back to database design,
application code, and indexing. I have seen entire financial exchanges
running on a single SQL Server. So, in terms of what can be done, I don't
know of a single application ever built that can not be handled by SQL
Server.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:A78A40EA-A80F-4178-BC6A-B757F21DA9B3@.microsoft.com...[vbcol=seagreen]
> Thanks guys. I'm putting together an idea for a company, but it will be
> quite
> database intensive, so i really need to find out what my technical
> requirements are.
> "David Portas" wrote:
>
found an answer to. How powerful is sql server?
I appreciate that there are ways of making it more efficient, but surely
there are limits. For example:
how many records can it handle in a database before it just becomes too big?
How many requests can it run concurrently?
I understand that this can all be expanded through clustering, etc. but lets
just assume I only have a single license, which i do.
Thanks. I look forward to hearing what you all have to say.>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
How fast is fast? There is no SQL Server speed limit. Performance is
mostly a function of hardware and application design.
> how many records can it handle in a database before it just becomes too
> big?
A table size is technically limited only by available storage. I've had
single table with billions of rows with excellent performance (properly
indexed of course). A consideration is manageability, though. Very large
tables are usually partitioned for manageability rather than performance.
> How many requests can it run concurrently?
It depends what you mean by 'concurrently'. A CPU (logical processor) can
run only one request at a time so the maximum number of active queries using
CPU is equal to the number of available (logical) processors. In practice,
queries often spend a considerable amount of time waiting for disk or
network I/O to complete and SQL Server can manage hundreds of concurrent
queries in a variety of states. The end result is that thousands of
transactions per second can be executed on a single server in a typical OLTP
profile.
Hope this helps.
Dan Guzman
SQL Server MVP
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.|||"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
>I know this is a very open ended question, but it is one that I have not
>yet
> found an answer to. How powerful is sql server?
> I appreciate that there are ways of making it more efficient, but surely
> there are limits. For example:
> how many records can it handle in a database before it just becomes too
> big?
> How many requests can it run concurrently?
> I understand that this can all be expanded through clustering, etc. but
> lets
> just assume I only have a single license, which i do.
> Thanks. I look forward to hearing what you all have to say.
See:
http://www.microsoft.com/sql/prodin...benchmarks.mspx
http://www.wintercorp.com/VLDB/2005...inners_2005.asp
http://www.tpc.org/
In view of the fact that you are asking this question I'd guess the right
answer is probably "More powerful than you need". :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks guys. I'm putting together an idea for a company, but it will be quit
e
database intensive, so i really need to find out what my technical
requirements are.
"David Portas" wrote:
> "P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
> news:364B5A09-DBA1-4B52-94DE-CDC4C2A9E516@.microsoft.com...
> See:
> http://www.microsoft.com/sql/prodin...benchmarks.mspx
> http://www.wintercorp.com/VLDB/2005...inners_2005.asp
> http://www.tpc.org/
> In view of the fact that you are asking this question I'd guess the right
> answer is probably "More powerful than you need". :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||On Wed, 15 Feb 2006 06:51:32 -0800, "P1ayboy"
<P1ayboy@.discussions.microsoft.com> wrote:
>Thanks guys. I'm putting together an idea for a company, but it will be qui
te
>database intensive, so i really need to find out what my technical
>requirements are.
I've run SQLServer on some modest but current hardware and just been
amazed at its capacity.
Remember that a lot depends on the other tiers, a sloppy middle tier
that generates 100x more load than it should, and SQLServer isn't
really the issue.
Properly configured server should handle a couple hundred simple
selects per second on a well-designed database, assuming that most of
the frequently accessed data can stay in RAM. If you have to scan
terabyte tables, you're into hardware constraints, not really
SQLServer. If your database tier logic is complex, you'll have to
provide a few more hints!
OTOH, if your "server" is a single processor with a single physical
disk, capacity will be a LOT lower.
All very rough, your mileage may vary, advice worth what you paid for
it, have a nice day.
J.|||It depends upon how you define "database intensive". I've dealt with
customer who serve 100% of the content for their website dynamically
straight out of SQL Server. One of these has tens of millions of users,
hundreds of thousands of which are banging on the website at any given time,
billions of pages of content served every day, and tens of billions of
database requests every day. The trick all goes back to database design,
application code, and indexing. I have seen entire financial exchanges
running on a single SQL Server. So, in terms of what can be done, I don't
know of a single application ever built that can not be handled by SQL
Server.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"P1ayboy" <P1ayboy@.discussions.microsoft.com> wrote in message
news:A78A40EA-A80F-4178-BC6A-B757F21DA9B3@.microsoft.com...[vbcol=seagreen]
> Thanks guys. I'm putting together an idea for a company, but it will be
> quite
> database intensive, so i really need to find out what my technical
> requirements are.
> "David Portas" wrote:
>
Subscribe to:
Posts (Atom)