locked
Custom Data Types RRS feed

  • Question

  • I'm would like to create some custom Data Types for my database that is used for an .Net application.

    What I'm trying to accomplish.

    In .Net there are many data types that do not map one-for-one to SQL data types.  So I figure there has got to be a way to create these .Net data types in SQL.

    As an example, in .Net there is an Int16 type that maps directly to SQL smallint with a size of 2 bytes and value of -32,768 to 32,767.  But .Net also has a UInt16 with a size of 2 bytes and value of 0 to 65,535.

    I would like to create a custom data type in SQL named usmallint that only accepts values of 0 to 65535.  Preferably using only two bytes, but size doesn't really matter for this.

    can anyone help or point me in the right direction?

    Tuesday, December 29, 2015 11:54 PM

Answers

  • I would like to create a custom data type in SQL named usmallint that only accepts values of 0 to 65535.  Preferably using only two bytes, but size doesn't really matter for this.

    can anyone help or point me in the right direction?

    On the database side, you can use a SQL type large enough for desired domain of data, plus a check constraint to limit data to the desired range.  For example:

    CREATE TABLE dbo.Example(
    	UInt16 int CONSTRAINT CK_Example_UInt16 CHECK(UInt16 BETWEEN 0 AND 65535)
    );

    You'll still need to explicitly convert the value to the desired .NET type (e.g. Convert.ToInit16()) but the conversion of non-null values will be safe due to the check constraint.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, December 30, 2015 12:15 PM
    Answerer
  • Hi,

    As per data type mappings given here - https://msdn.microsoft.com/en-us/library/bb726019(v=sql.110).aspx

    use smallint for UInt16 as well. You can create a custom data type but read here for some of the drawbacks - http://stackoverflow.com/questions/7267233/sql-server-2008-unsigned-integer-data-types


    Wednesday, December 30, 2015 1:59 AM
  • Hi MEFletcher,

    Adding to other post, though it is impossible to custom a data type in SQL Server that only accepts values of 0 to 65535, you can convert the Uint16 type to int32 using Convert.ToInt32 Method (UInt16) method, and the accepted value of int32  type is from -2,147,483,648 to +2,147,483,647. Then the int32 data type can be mapped directly to SQL Server int data type which can accept data value from -2147483648 to +2147483647.

    Regards,
    Ice fan


    Ice Fan
    TechNet Community Support


    Wednesday, December 30, 2015 7:56 AM

All replies

  • Hi,

    As per data type mappings given here - https://msdn.microsoft.com/en-us/library/bb726019(v=sql.110).aspx

    use smallint for UInt16 as well. You can create a custom data type but read here for some of the drawbacks - http://stackoverflow.com/questions/7267233/sql-server-2008-unsigned-integer-data-types


    Wednesday, December 30, 2015 1:59 AM
  • Hi MEFletcher,

    Adding to other post, though it is impossible to custom a data type in SQL Server that only accepts values of 0 to 65535, you can convert the Uint16 type to int32 using Convert.ToInt32 Method (UInt16) method, and the accepted value of int32  type is from -2,147,483,648 to +2,147,483,647. Then the int32 data type can be mapped directly to SQL Server int data type which can accept data value from -2147483648 to +2147483647.

    Regards,
    Ice fan


    Ice Fan
    TechNet Community Support


    Wednesday, December 30, 2015 7:56 AM
  • I would like to create a custom data type in SQL named usmallint that only accepts values of 0 to 65535.  Preferably using only two bytes, but size doesn't really matter for this.

    can anyone help or point me in the right direction?

    On the database side, you can use a SQL type large enough for desired domain of data, plus a check constraint to limit data to the desired range.  For example:

    CREATE TABLE dbo.Example(
    	UInt16 int CONSTRAINT CK_Example_UInt16 CHECK(UInt16 BETWEEN 0 AND 65535)
    );

    You'll still need to explicitly convert the value to the desired .NET type (e.g. Convert.ToInit16()) but the conversion of non-null values will be safe due to the check constraint.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, December 30, 2015 12:15 PM
    Answerer