Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How To Tweak Digit Length

Posted on 2010-09-01
5
Medium Priority
?
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
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 1000 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 1000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 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