Solved

How To Tweak Digit Length

Posted on 2010-09-01
5
413 Views
Last Modified: 2012-05-10
I have a bigINT column in SQL server where the digit length is 14. How and where can I tweak the digit length in SQL Server 2008. Would like this system created bigint column to default to 10 digits.
0
Comment
Question by:jazzcatone
5 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 33579358
If you want a specified number of digits, choose a DECIMAL column.   In SQL Server, a BigINT column is an 8-byte integer, which is good to 14 digits.

Alternately, you could apply conditions on the row to only allow values between 1000000000 and 9999999999, so other values would return errors.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 250 total points
ID: 33579364
You can convert it to varchar and truncate the digits
select left(cast(yourcol as varchar), 10)
or you can divide the bigint number by 10000
select yourcol / 10000
If that's not what you want, please clarify with an example
0
 
LVL 3

Expert Comment

by:Blood
ID: 33579435
When you say "system created" do you mean you told it to create a new column called "X" and just took the default of bigINT or are you talking about some system column?
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 250 total points
ID: 33584223
If you mean you have a bigint IDENTITY column, then a number cannot be definition have leading zeroes.
However, you can force it to start at 1 billion which is the smallest number with 10 digits.
You can reseed the identity using something like this

create table xy (i bigint identity(1,1), other varchar(10))
dbcc checkident(xy, reseed, 1000000000)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now