|
is that the same for TotalCount from the second CTE?
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Sounds like you want to round your result to 2 decimal places before dividing by 100
SELECT
(2/500)* 100 AS not_rounded,
(round(2/500,2))* 100 AS rounded
from dual;
returns
NOT_ROUNDED ROUNDED
----------- -------
0.4 0
Regrads
|
|
|
|
|
Which is the best database to manipulate 100 million records which configures with c# win. form.
|
|
|
|
|
All the known databases - SQL or NoSQL - can easily handle a 100 million records. All of them has also binding to .NET (so to C#)...
You may map your request on that data, store only, OLAP, more read than write...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
actually i am already working on sql but i am not sure that it can manage 100 million record. somebody suggested me oracle is oracle better than SQL.
|
|
|
|
|
I've worked with SQL had table with over 800 million rows - so no problem. The main factor is the usage. You have to find a good PK and as less as possible other indexes and FKs...
However if you aim to even higher you may look for some big-data database...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
I have worked with 300 million rows in an Oracle database and yes it handles them well however... so will a SQL Server database.
What matters is how the indexes and partitions are set up on tables, as access to tables with this number of rows will be considerably affected by these decisions.
With 100+ million rows you are most likely going to need to partition tables.
[edit]grammar correction[/edit]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 11-Feb-14 13:24pm.
|
|
|
|
|
GuyThiebaut wrote: partition tables
That could be a good idea, especially if you have much to read - however it can work without...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Oracle because it has more security than SQL.
|
|
|
|
|
I'm not familiar with Oracle. Would you care to expand on that statement? What security features does Oracle provide that are missing from MS SQL?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
What's funny but totally expected is that both Oracle and Microsoft claim that their database has the better security.
I'm of the opinion that the safer product is the one with the better DBA.
And since SQLServer is easier to run out of the box...
|
|
|
|
|
Really? Can you elaborate on that?
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Agent_Spock wrote: i am not sure that it can manage 100 million record
According to the Microsoft document "Maximum Capacity Specifications for SQL Server":
Rows per table: Limited by available storage
Database size: 524,272 terabytes
So unless each of your rows is going to exceed 5.36 gigabytes, you should be fine.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Agent_Spock wrote: sql but i am not sure that it can manage 100 million record.
I've worked with a SQL database that had a single table with a few billion rows (Inventory type stuff). So, yea SQL can scale as far as you need it too.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
and sir, is the searching good (fast).
|
|
|
|
|
Yup, never a problem. The table was indexed for the usage.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Does "Indexed" mean using primary key as filter or something else?
|
|
|
|
|
We work with both Oracle and SQL Server in those sort of volumes and I will reluctantly state that Oracle handles the volume better than SQL Server. However the reason may well be that for Oracle you generally need a dedicated DBA to drive the bastard. Also look into the comparative costs of purchase and support of the 2 systems, Oracle is substantially more expensive than SQL Server.
We have a rule of thumb that Oracle is used for enterprise apps and SQL Server for departmental apps.
[pet hate] Oracle also forces you to use UPPER case naming [/pet hate]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: However the reason may well be that for Oracle you generally need a dedicated DBA to drive the bastard
I believe you're onto something there.
Mycroft Holmes wrote: Oracle is substantially more expensive than SQL Server
I wonder how much of that's a myth? For us it was a little bit cheaper actually.
Mycroft Holmes wrote: [pet hate] Oracle also forces you to use UPPER case naming [/pet hate]
Grumble grumble grumble.
|
|
|
|
|
Jörgen Andersson wrote: I wonder how much of that's a myth? For us it was a little bit cheaper actually.
But don't you have to pay something like 22% of the original cost every year in "maintenance charges"? Whereas, once you've bought SQL Server, you only have to pay if you want to upgrade.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You don't need to pay the maintenance fee. You can keep on using it as long as you want to. What you're missing out on is support and "windows update" aswell as upgrades.
And that support is of a wildly varying quality. If you call in the morning, someone with a very strong Indian accent called "Kevin" answers, and if he understands my problem, I won't understand his answer.
Anyway, if you have SQLServer you still have to pay for support and upgrades, don't you?
It's not an apple to apple comparison.
What you can see is that Oracle is trying to price themselves into smaller systems while MS is trying to gain grounds among larger systems. Because when you have a large server farm Oracle is considerably more expensive.
|
|
|
|
|
Jörgen Andersson wrote: Anyway, if you have SQLServer you still have to pay for support and upgrades, don't you?
You have to pay for upgrades to a new version, or to a higher edition, and you have to pay if you want someone from Microsoft on the phone helping to diagnose a problem. However, service pack (which now seem to be a thing of the past) and cumulative updates are free.
Jörgen Andersson wrote: Oracle is trying to price themselves into smaller systems
I'd have thought that most small systems would be using the Express edition of SQL or Oracle, which is free.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes, but the express editions only use 1 GB of memory. It's good enough for CRUD, but very fast growing to small when you need to analyse data.
That's when the standard edition comes into play. That's competitively priced, in contrast to the Enterprice edition which is a class of its own.
|
|
|
|
|
I agree with them who said that both Microsoft SQL Server and Oracle can do so.
When you decide for one of it, note that Oracle is more complicated. SQL Server will handle many things for you automatically - like autoincrement values for keys, while with Oracle you have to setup a sequence, and a trigger for linking your table to the sequence on the appropriate events. In .Net languages, an empty string is not null, but for Oracle there is no difference - and meanwhile I say: Oracle is right from the view point of requirements. But it may cause problems for the programmer. And there are several more such oddities.
|
|
|
|
|
Bernhard Hiller wrote: In .Net languages, an empty string is not null, but for Oracle there is no difference - and meanwhile I say: Oracle is right from the view point of requirements.
I think you'll find that a lot of people disagree with you on that one - including the ANSI SQL 1992 standard.
Of course, Oracle's weird behaviour pre-dates the standard, and can't be changed for backwards-compatibility reasons.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|