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

Adding New Microsoft Extensions to Legacy WCF and ASMX Web Services

I was asked by several people recently: Can I use dependency injection with my legacy WCF and ASMX web services? Microsoft has some new stuff they released with core–specifically, a new configuration system, caching, and logging providers. Can I use them in my legacy WCF and ASMX web services? The answer to both is yes you can! In fact, I recommend it! To illustrate this, we’ll take a look at taking a sample application that has a WCF service and a ASMX service and we’ll: Add dependency injection using Ninject. Add the new Configuration provider from the new  Microsoft.Extensions.Configuration  packages. Add caching functionality using Microsoft’s new  Microsoft.Extensions.Caching  packages. Use the new logging providers from  Microsoft.Extensions.Logging  packages. Dependency Injection We use Ninject, but you can just as easily use AutoFac or SimpleInjector as they also have packaged integrations for WCF. You can use other containers, but may need to hand-roll your
Read more

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