SQL SERVER – Storing 64-bit Unsigned Integer Value in Database

Here is a very interesting question I received in an email just another day. Some questions just are so good that it makes me wonder how come I have not faced it first hand. Anyway here is the question -

“Pinal, I am migrating my database from MySQL to SQL Server and I have faced unique situation.

I have been using Unsigned 64-bit integer in MySQL but when I try to migrate that column to SQL Server, I am facing an issue as there is no datatype which I find appropriate for my column. It is now too late to change the datatype and I need immediate solution.

One chain of thought was to change the data type of the column from Unsigned 64-bit (BIGINT) to VARCHAR(n) but that will just change the data type for me such that I will face quite a lot of performance related issues in future. In SQL Server we also have the BIGINT data type but that is Signed 64-bit datatype. BIGINT datatype in SQL Server have range of -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807). However, my digit is much larger than this number.

Is there anyway, I can store my big 64-bit Unsigned Integer without loosing much of the performance of by converting it to VARCHAR.”

Very interesting question, for the sake of the argument, we can ask user that there should be no need of such a big number or if you are taking about identity column I really doubt that if your table will grow beyond this table. Here the real question which I found interesting was how to store 64-bit unsigned integer value in SQL Server without converting it to String data type. After thinking a bit, I found a fairly simple answer.

I can use NUMERIC data type.

I can use NUMERIC(20) datatype for 64-bit unsigned integer value, NUMERIC(10) datatype for 32-bit unsigned integer value and NUMERIC(5) datatype for 16-bit unsigned integer value. Numeric datatype supports 38 maximum of 38 precision.

Now here is another thing to keep in mind.

  1. Using NUMERIC datatype will indeed accept the 64-bit unsigned integer but in future if you try to enter negative value, it will also allow the same. Hence, you will need to put any additional constraint over column to only accept positive integer there.
  2. Here is another big concern, SQL Server will store the number as numeric and will treat that as a positive integer for all the practical purpose. You will have to write in your application logic to interpret that as a 64-bit Unsigned Integer. On another side if you are using unsigned integers in your application, there are good chance that you already have logic taking care of the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Simple Explanation of Data Type Precedence

While I was working on creating a question for SQL SERVER – SQL Quiz – The View, The Table and The Clustered Index Confusion, I had actually created yet another question along with this question. However, I felt that the one which is posted on the SQL Quiz is much better than this one because what makes that question more challenging is that it has a multiple answer.

Here is the question regarding Simple Explanation of Data Type Precedence:

Run the following example first and then observe the query execution plan.

USE tempdb
GO
CREATE TABLE FirstTable (ID INT, Col VARCHAR(100))
GO
INSERT INTO FirstTable (ID, Col)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third'
GO
-- Enable Execution Plan; Result to Text
--SET SHOWPLAN_ALL OFF
GO
SELECT *
FROM FirstTable
WHERE ID = N'1' AND Col = N'First'
GO
-- Clean up
DROP TABLE FirstTable
GO

Move your mouse over the table scan operation.

You will notice that there are two usages of the CONVERT_IMPLICIT functions. If you carefully observe them, you will also notice that the functions are implemented once to the right side of the operator and once to the left side of the operator. Technically speaking, this means it is applied once to the column of the table and once to the parameter passed to the function.

Let us try to understand this further. In the created table, we have one column as VARCHAR and another as INT. The variable which we are comparing along with that is of type NVARCHAR. What is interesting with this is that the passed NVARCHAR parameter has been converted once to INT, while another time table column was converted to NVARCHAR.

The reason for this is very simple: data type precedence:

  1. SQL Server uses the following precedence order for data types:
  2. user-defined data types (highest)
  3. sql_variant
  4. xml
  5. datetimeoffset
  6. datetime2
  7. datetime
  8. smalldatetime
  9. date
  10. time
  11. float
  12. real
  13. decimal
  14. money
  15. smallmoney
  16. bigint
  17. int
  18. smallint
  19. tinyint
  20. bit
  21. ntext
  22. text
  23. image
  24. timestamp
  25. uniqueidentifier
  26. nvarchar (including nvarchar(max) )
  27. nchar
  28. varchar (including varchar(max) )
  29. char
  30. varbinary (including varbinary(max) )
  31. binary (lowest)

In terms of precedence, INT has a higher precedence than NVARCHAR, leading NVARCHAR data type to be converted into INT. However, VARCHAR has a lower precedence than NVARCHAR, making that column to convert it into NVARCHAR.

I hope this is clear enough so it can help you to understand the execution plan better. You can read further details on this subject over here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Difference and Explanation among DECIMAL, FLOAT and NUMERIC

The basic difference between Decimal and Numeric :
They are the exactly same. Same thing different name.

The basic difference between Decimal/Numeric and Float :
Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly.
Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale.

Converting from Decimal or Numeric to float can cause some loss of precision. For the Decimal or Numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. DECIMAL(2,2) and DECIMAL(2,4) are different data types. This means that 11.22 and 11.2222 are different types though this is not the case for float. For FLOAT(6) 11.22 and 11.2222 are same data types.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL DataTypes

SQL SERVER – 2005 – List All The Column With Specific Data Types

Since we upgraded to SQL Server 2005 from SQL Server 2000, we have used following script to find out columns with specific datatypes many times. It is very handy small script.

SQL Server 2005 has new datatype of VARCHAR(MAX), we decided to change all our TEXT datatype columns to VARCHAR(MAX). The reason to do that as TEXT datatype will be deprecated in future version of SQL Server and VARCHAR(MAX) is superior to TEXT datatype in features. We run following script to identify all the columns which are TEXT datatype and developer converts them to VARCHAR(MAX)

Script 1 : Simple script to identify all the columns with datatype TEXT in specific database
SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'text' --you can change text to other datatypes
ORDER BY c.OBJECT_ID;
GO

Script 2 : Extended script to identify all the columns datatype and other information in specific database
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
ORDER BY c.OBJECT_ID;

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types

Yesterday, in Friday Afternoon team meeting. I was asked question by one of application developer

“I am asked in new coding standards to use VARHCAR(MAX) instead of TEXT. Is VARCHAR(MAX) big enough to store TEXT field?”

Well, I realize that I was not clear enough in my coding standard. It is extremely important for coding standards to be clear and have a enough explanation that developer have no doubt about them. I updated coding standards after the meeting. The answer is

“Yes, VARCHAR(MAX) is big enough to accommodate TEXT field. TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommanded to use new data types which are VARHCAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).”

There are more reasons to use VARHCAR(MAX) though this was verbal answer to technical question in our general meeting where the focus was “Web Application Architecture and SQL Server”.

Reference : Pinal Dave (http://blog.SQLAuthority.com)