SQL Server Tutorial

SQL Server is a relational database management system (RDBMS) from Microsoft that’s designed for the enterprise environment. SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.

SQL Server 2012 Auto Identity Column Value Jump Issue

In this article I will tell you how to Auto Identity Column Value Jump Issue in SQL Server 2012. Microsoft changed the Identity behavior to jump in increment of 1000(if INT) or 10000(if BIGINT) whenever the SQL Server 2012 restart, Service Restart or Failover.

You need to setup SQL Server 2012 and create a test database. Then create a table with auto identity column:

Now insert 2 rows there:

You see the result:

NormalTable

The result is as expected. Now just restart your SQL Server service. There are various ways in which you can do it. We did it from SQL Server management studio.

ReStart

Now, insert another 2 rows to the same table again:

Now see the result:

FullTable

Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000.

Is it really a bug?

Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful. But in our case, it would not be acceptable because that number is shown to the client and the client will be surprised to see that new number after jump and the new number depends on how many times SQL Server is restarted. If it is not visible to the client, then it might be acceptable so that the number is used internally.

Solutions

If we are not interested in this so called feature, then we can do two things to stop that jump.

  • Using Sequence
  • Register -t272 to SQL Server Startup Parameter

Using Sequence

First, we need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. The following is the code sample:

Register -t272 to SQL Server Startup Parameter

Open SQLServer configuration manager from your server. Select SQL Server 2012 instance there right client and select Properties menu. You will find a tabbed dialog window. You select start up parameters tab from there and register -t272. Then restart SQL Server 2012 instance again and see the difference:

StartupParameter

Summary

If too many tables contain identity column to your database and all contain existing values, then it is better to go for solution 2. Because it is a very simple solution and its scope is server wise. This means if you add SQL Server 2012 parameter -t272 there, then it will affect all your databases there. If you want to create a new database and you need auto generated number field, then you can use solution 1, that means use sequence value to a column instead of auto Identity value. There are so many articles you can find online about when you will use auto identity column when using sequence and advantages/disadvantages of each other.

Our Recommended SQL 2008/2012/2014/2016 Hosting

ASPHostPortal.com provides its customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server. ASPHostPortal.com offers SQL 2016 hosting starts from $5. ASPHostPortal also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL 2014 Hosting, ASPHostPortal should be your best choice.

European leading web hosting provider, HostForLIFE.eu announced support for Microsoft SQL Server 2016 Hosting plan due to high demand of Microsoft SQL Server 2016 users in Europe.

SQL Server 2016 Hosting

HostForLIFE.eu was established to cater to an underserved market in the hosting industry; web hosting for customers who want excellent service. HostForLIFE.eu a worldwide provider of hosting has announced the latest release of Microsoft’s widely-used SQL relational database management system SQL Server 2016. You can take advantage of the powerful SQL Server 2016 technology in all Windows Shared hosting, Windows Reseller hosting and Windows Cloud hosting packages.

In addition, SQL Server 2016 Hosting provides the biggest leap forward in Microsoft’s data platform history with real-time operational analytics, rich visualizations on mobile devices, built-in advanced analytics, new advanced security technology, and new hybrid cloud scenarios.

SQL Server 2016 delivers breakthrough mission-critical capabilities with in-memory performance and operational analytics built-in. Comprehensive security features like new Always Encrypted technology help protect your data at rest and in motion, and a world-class high availability and disaster recovery solution adds new enhancements to AlwaysOn technology.

Organizations will gain deeper insights into all of their data with new capabilities that go beyond business intelligence to perform advanced analytics directly within their database and present rich visualizations for business insights on any device.

The customers can also gain the benefits of hyper-scale cloud with new hybrid scenarios enabled by new Stretch Database technology that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure in a secured way so your data is always at hand for queries, no matter the size. In addition, SQL Server 2016 delivers a complete database platform for hybrid cloud, enabling you to easily build, deploy and manage solutions that span on-premises and cloud.

The first new option is Microsoft SQL Server 2016 Hosting, which is available to customers from today. With the public release just last week of Microsoft’s latest version of their premier database product, HostForLIFE has been quick to respond with updated their shared server configurations. They know that our customers are always looking for new technologies and the latest Microsoft product. HostForLIFE are committed to providing the latest technology and a service that is unbeatable. They want to make sure that their customers have their good opportunity to test this new technology. HostForLIFE customers can now take advantage of SQL Server 2016’s capabilities.”

Hostforlife is excited to see the benefits of this release add value to the energy management and manufacturing arena. Ensuring compatibility with Microsoft’s new SQL Server 2016 demonstrates how HostForLIFE and Microsoft remain committed together to providing leading edge technology for the benefit of their shared customers.

For more information about this new product, please visit http://hostforlife.eu/European-SQL-Server-2016-Hosting

About HostForLIFE.eu

HostForLIFE.eu is an European Windows Hosting Provider which focuses on the Windows Platform only. HostForLIFE.eu deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.asp.net/hosting/hostingprovider/details/953). Their service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, they have also won several awards from reputable organizations in the hosting industry and the detail can be found on their official website.

error: Content is protected !!