Primary key ID - Data Type: Integer

Primary key ID - Data Type: Integer

  
Hello

I'm looking at outsystems reference document
http://www.outsystems.com/help/integrationstudio/5.1/Reference/OutSystems_and_Database_Data_Types.htm

By default the primary key Id uses SQL Server data type: Int,   what happens if the primary int is exceeded, can it change from int to bigint and outsystems platform will be ok with this? (it will not break outsystems?)
Has anyone tested bigint?
Hi Robert,

I'm not aware of anyone testing BigInt so far, but I'm not sure it would work.

An Integer value in the Agile Platform is mapped to a C# Int32 data type

http://msdn.microsoft.com/en-us/library/system.int32.maxvalue.aspx

which maps directly to an "int" data type in SQL Server in terms of range.

http://sqlserverplanet.com/sql/sql-server-max-int-value/

As such, I would find it hard for it to work correctly if the goal of changing it to BigInt is to have a higher upper limit.

What are you trying to achieve?

Regards,

Paulo Tavares
Hello Paulo

Paulo Tavares: "An Integer value in the Agile Platform is mapped to a C# Int32 data type"

Thanks, I forgot to take into account what outsystems was actually doing in the background, but this just answered my question, if outsystems was to support SQL Server datatype: bigint, then the datatype variable in C# would need to be long c
onvert.ToInt64.

This would conclude that this test would fail, due to the limited range of int32 data type; Suggesting that, BIGINT is not supported, and by simply changing the record from int to bigint in SQL Server would not solve the problem, as described above.

Therefore the only option left here is to use decimal(19,0), this would work in the current situation, where bigint is not supported.


Paul Tavares: "What are you trying to achieve?"
Storing transaction records in a range greater than 2 billion records.
Instead of flooding WotC...

Why you need the support of Bigint?

Are you planning to move your database inside the OS-platform or are you going to use an extension to connect to that database?
The latter will change the integer to decimal, but no harm done on the database side, I think ?




"Why you need the support of Bigint?"

"Are you planning to move your database inside the OS-platform or are you going to use an extension to connect to that database?
The latter will change the integer to decimal, but no harm done on the database side, I think ?"

Using the database inside the OS platform.

Its more efficient to use Bigint rather than varchar (text) or decimal - mainly because both of these data type is larger to storage and slower in performance.


It might not be a big deal for an intranet application, but an internet web application, it would be ideal to support bigint.
Still not convinced.

You could have 2,147,483,647 records in a table with Integer. That is a LOT.

How many Outsystems applications would have a table that consists of more than  2,147,483,647 records out of the box?
If it's more than 50% of the applications, I will vote instantly for support of BigInt.




@Joost

"

How many Outsystems applications would have a table that consists of more than  2,147,483,647 records out of the box?
If it's more than 50% of the applications, I will vote instantly for support of BigInt."

Its not about how many applications you have or can install, you can have just 1 application platform and exceed  2,147,483,647 records.

But I do agree with you that 2,147,483,647 is a rather large number yet if you built facebook using the agile platform, your application will be processing 135+ billion messages per month, that means you would reach the int32 limitation of 2,147,483,647 in less less than 24 hours. 

 

Now you can see 1 billion is not a very large number when it comes to internet applications, but your not going to be building facebook anytime soon, so lets take a more practical example.
 

You are a cloud service provider and operate a Software as a Service business, that means you operate services that people and businesses can lease from your company. 

 

Now your company grows and you have 100's of businesses using your service, within 2 years you have 200 businesses using your service, eventually you have a few thousand. Now you have to support these businesses and their user base/customers as well.

2 billion records will then become a very big problem for you and your business as a cloud service provider using the agile platform.

 

Real life example -  paypal has over $2000 USD of payments flowing through its network every second. For the sake of simplicity say the average amount per transaction is $100, you would be doing ~200 transaction per second. However it is likely that you would design your database for double entry accounting.

 

Which would mean that for every single transaction, you would have *at least* two entries, therefore you would be doing ~400 transactions per second, after 1 minute 24000 transaction, after 1 hour 1440000 transactions, after 1 day 34,560,000 transactions and after 2 months, you would have exceed 2 billion records!


Regardless of this reaching the first billion records would take many years to reach, and you would have plenty of warning before hand, so its not a big problem right now. But it is definately a problem for cloud service providers and the future of your business as cloud service provider, using the agile platform. In time outsystems would have figured that there is a need to support int64 for cloud service providers and there is no reason not to support int64 anyways, is there? It can be done! the technology is already here, but it's not being initalised.




REF: http://news.cnet.com/8301-1023_3-10302072-93.html

----
If anyone reading this and deciding on building a cloud service business using the agile platform, you should do it! and then you can solve the problem later on when it actually does become a problem! -- Do note, it might not even become a problem for your business anyways, if your business does not grow large enough. 
Also have in mind that if you process "135+ billion messages per month" you'll need a lot more on the database architecture than just having a bigint primary key.

It's not something you can get away without stuff like partitioning and composed clustered indexes supporting it. And things like that you can do manually on the side.

Note that this causes come limitations in the platform like: no delete/update entity actions, no entity identifiers, contraints, ...

@Joao

" Also have in mind that if you process "135+ billion messages per month" you'll need a lot more on the database architecture than just having a bigint primary key."

Obviously not, facebook started off with mySQL , it still uses mySQL for its core but as for facebook messaging, nowdays it uses HBASE, which is simular to google's bigtable's database.

But the point made illustrates that the int32 limitation of  2,147,483,647 billion records is not that big, when it comes to cloud service providers needing to support multiple businesses and customers in order to provide its services and operate its business.
Followup: In regards to int64/bigint I have contacted outsystems and they are aware of the problem of using int32, the good news is int64 is already on outsystems roadmap! 
To kick this puppy again .... when ????
I have the same question, this problem was raise in 2011 an still nothing?!!!!
I also have crash this wall... in just one day/file: if I have +300K clients purchase records to aggregate at a eurocent level, I reach a 2.xxx.xxx.xxx value in just one file...So it's not about "how long will it takes".