Unsigned Integers in SQL Server, Oracle, MySQL, Postgres, and DB2

A recent project required the need to store an unsigned 64-bit integer in a SQL Server database table. BIGINT won't cut it because BIGINT has a max value of 9,223,372,036,854,775,807 (signed 64-bit integer), and the unsigned 64-bit integer's max value is 18,446,744,073,709,551,615.  The solution is to use NUMERIC(20) instead.

I recalled that other DBMS's do support unsigned integers as integer types vs. coercion with numeric, so I decided to compare a few of them and document them here.

DBMSUnsigned 64BitUnsigned 32BitUnsigned 16Bit
SQL Server (as of "Denali") numeric(20) numeric(10) numeric(5)
Oracle (as of 11g) number(20), numeric(20)number(10), numeric(10) number(5), numeric(5)
MySQL (as of 5.x) bigint, numeric(20) int, numeric(10) smallint, numeric(5)
Postgres (as of 9.x) numeric(20) numeric(10)numeric(5)
DB2 UDB (as of 9.x) numeric(20) numeric(10)numeric(5)

Comments

Post a Comment

Popular posts from this blog

Using NHibernate in Asp.Net Core

Starting with version 5.1, NHibernate now supports .Net Core and .Netstandard as well as the full framework. Let’s take a quick look at how to set it up in an ASP.Net Core 2.x/3.x application. Configuration As of this writing, NHibernate doesn’t support the configuration system within Asp.Net Core based on Microsoft.Extensions.Configuration. The good news is, you don’t have to use a  hibernate.cfg.xml  file or the   section in app.config. You can add the properies manually, and pull the values from Asp.Net Core’s configuration, taking advantage of all the goodies that come with it. This opens up a world of configuration deployment possibilities. var config = new Configuration (); IDictionary < string , string > properties = new Dictionary < string , string > { { "connection.connection_string" , Configuration . GetConnectionString ( "Default" ) }, { "dialect" , "NHibernate.Dialect.SQLiteDialect" }, ...
Read more

Code Coverage for Multiple Projects in a Single Build using Dotnet Test and Coverlet

Most of the time, your solution will have more than one project and a test unit project for each of those. Azure DevOps only, as of this writing, only allows you to update a single code coverage summary. If you upload more than one, each overwrites the next and only the last one remains. To accomplish this, we can use the merge functionality in Coverlet . I use the MSBuild extension, because it is better suited for CI pipelines. In your test project XML, add the package... <PackageReference Include="coverlet.msbuild" Version="2.7.0">     <PrivateAssets>all</PrivateAssets>     <IncludeAssets>runtime; build; native; contentfiles; analyzers</IncludeAssets> </PackageReference> Then, in your dotnet test command or msbuild command, tell it to use Coverlet and to merge results. If you're using Azure DevOps, your test task looks this... (line wrapped for read ability in the article) - task: DotNetCoreCLI@2 displayName: Test i...
Read more

My Experience with JetBrains Rider

Disclaimer: This is an OpEd and mostly anecdotal. Performance wasn't measured, just perceived based on whether it left like the IDE was hanging a lot or productivity was impacted. I'll start off by saying: there is no replacement for Visual Studio. None. That's my opinion anyway. That said, JetBrains Rider is a decent .Net IDE, especially if you are a ReShaper fan (I am not) or IntelliJ fan (I am). What made me decide to give it a go? Well, I started work at Linedata as a Cloud Solution Architect and Application Architect. They are heavy users of ReSharper, and use it to enforce code formatting, quality, standards, and test coverage ( dotCover ) as part of their CI builds. It only took a few hours of me getting fed up with ReSharper crippling Visual Studio for me to install Rider and give it a go. I've been using it at work since. Some take-aways After using Rider as my primary C# IDE for a few months, here's my impression... Good Impressions P...
Read more