Today, I will show you How to Use sys.dm_os_performance_counters to get your Buffer cache hit ratio. In order to calculate the Buffer cache hit ratio we need to query the sys.dm_os_performance_counters dynamic management view. There are 2 counters we need in order to do our calculation, one counter is Buffer cache hit ratio and the other counter is Buffer cache hit ratio base. We divide Buffer cache hit ratio base by Buffer cache hit ratio and it will give us the Buffer cache hit ratio.

Here is the query that will do that, this query will only work on SQL Server 2005 and up.
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value,OBJECT_NAME

FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'

AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Page life expectancy
Now let's look at Page life expectancy. Page life expectancy is the number of seconds a page will stay in the buffer pool, ideally it should be above 300 seconds. If it is less than 300 seconds this could indicate memory pressure, a cache flush or missing indexes.

Here is how to get the Page life expectancy
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

In this tutorial, I will write an article about How to Calculate Total Rows Inserted per Second in SQL Server 2014. Ever expected to compute the quantity of columns embedded consistently, for each table in every database on a server? Alternately, have you ever expected to approve that all methods have quit keeping in touch with tables? These sorts of inquiries come up routinely for me. To help with this, I've composed the following script, which inspects metadata qualities utilizing sys.partitions. This system isn't as precise as running SELECT COUNT(*) FROM, however its much quicker.

Remember, since it’s just looking at row counts, its very little help on tables that have a considerable measure of update/delete  action. Yet it does what I need it to do, and I utilize it pretty frequently, so I thought I'd experience case any other individual can advantage from it as well.

/* Declare Parameters */

DECLARE @newBaseline BIT = 1 -- change to 0 when you don't want to replace the baseline, i.e. after initial run
  , @delay CHAR(8) = '00:00:30'; -- change as needed 
IF @newBaseline = 1
BEGIN
    IF OBJECT_ID('tempdb..#baseline') IS NOT NULL
        DROP TABLE #baseline; 
    CREATE TABLE #baseline
    (
         database_name  SYSNAME
       , table_name     SYSNAME
       , table_rows     BIGINT
       , captureTime    DATETIME NULL
    );
END 

IF OBJECT_ID('tempdb..#current') IS NOT NULL
    DROP TABLE #current;
 CREATE TABLE #current
(
     database_name  SYSNAME
   , table_name     SYSNAME
   , table_rows     BIGINT
   , captureTime    DATETIME NULL
); 
IF @newBaseline = 1
BEGIN
    EXECUTE sp_MSforeachdb 'USE ?;
        INSERT INTO #baseline
        SELECT DB_NAME()
            , o.name As [tableName]
            , SUM(p.[rows]) As [rowCnt]
            , GETDATE() As [captureTime]
        FROM sys.indexes As i
        JOIN sys.partitions As p
            ON i.[object_id] = p.[object_id]
           AND i.index_id  = p.index_id
        JOIN sys.objects As o
            ON i.[object_id] = o.[object_id]
        WHERE i.[type] = 1
        GROUP BY o.name;' 
    WAITFOR DELAY @delay;
END
 EXECUTE sp_MSforeachdb 'USE ?;
INSERT INTO #current
SELECT DB_NAME()
    , o.name As [tableName]
    , SUM(p.[rows]) As [rowCnt]
    , GETDATE() As [captureTime]
FROM sys.indexes As i
JOIN sys.partitions As p
    ON i.[object_id] = p.[object_id]
   AND i.index_id  = p.index_id
JOIN sys.objects As o
    ON i.[object_id] = o.[object_id]
WHERE i.[type] = 1
GROUP BY o.name;' 
SELECT  c.*
      , c.table_rows - b.table_rows AS 'new_rows'
      , DATEDIFF(second, b.captureTime, c.captureTime) AS 'time_diff'
      , (c.table_rows - b.table_rows) / DATEDIFF(second, b.captureTime, c.captureTime) AS 'rows_per_sec'
FROM #baseline AS b
JOIN #current AS c
    ON b.table_name = c.table_name
   AND b.database_name = c.database_name
ORDER BY new_rows DESC;

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Today, let me explain you about Export to Excel in c# from list and using Linq filter. You will find lot export to excel coding in other website. I'm not going to give any new in this. I am also giving here with simple List collection with basic LINQ filter and export to excel sheet.
Note: Include the Microsoft.Office.Interop.Excel in your project.

List Collection With LINQ Filter:
    Eg: lstLocal = lstFlspc.Where(m => m.ClientLocation == “Chennai”).ToList();

Export to Excel in c#.net with List and LINQ Filter Query:
    public void ExportToExcel(List<FileInfoLocal> lst)
    {
        try
        {
//Filter in List collection
          lstLocal = lstFlspc.Where(m => m.ClientPath.Path == child.Text).ToList();


          Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

          if (xlApp == null)
          {
              MessageBox.Show("Excel is not properly installed!!");
              return;
          }   

          Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
          Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
          object misValue = System.Reflection.Missing.Value;

          xlWorkBook = xlApp.Workbooks.Add(misValue);
          xlWorkSheet =(Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
             

          int iRow = 1, iColumn = 1;
          //Header Test
          xlWorkSheet.Cells[iRow, iColumn] = "Column1 Name";
          xlWorkSheet.Cells[iRow, iColumn + 1] = "Column2 Name";
          xlWorkSheet.Cells[iRow, iColumn + 2] = "Column3 Name";
          xlWorkSheet.Cells[iRow, iColumn + 3] = "Column4 Name";
          xlWorkSheet.Cells[iRow, iColumn + 4] = "Column5 Name";
          iRow++;

// Filter in List collection using LINQ

          if (lstLocal!= null)
          {
          foreach (var item in lstLocal)
            {
              // .. Add other parameters here. Body Text
              xlWorkSheet.Cells[iRow, iColumn] = item.Name;
              xlWorkSheet.Cells[iRow, iColumn + 1] = item.Date;
              xlWorkSheet.Cells[iRow, iColumn + 2] = item.Path;
              xlWorkSheet.Cells[iRow, iColumn + 3] = item.UserName;
              xlWorkSheet.Cells[iRow, iColumn + 4] = item.Desc;
              iRow++;                                  
            }
          }

         string sExcelFile = Directory.GetCurrentDirectory() + "Report.xls";

         xlWorkBook.SaveAs(sExcelFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
         xlWorkBook.Close(true, misValue, misValue);
         xlApp.Quit();

         releaseObject(xlWorkSheet);
         releaseObject(xlWorkBook);
         releaseObject(xlApp);

         MessageBox.Show("Excel file created successfully , you can find the file " + sExcelFile, "Export To Excell");

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }



    // Release the Excel object from memory
    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

Today, I will explain you about how to Remove DataBase In Sql Server Without Using Wizard. This system stored procedure will helps you.
exec sp_dbremove 'dataBaseName'

The following is the entire script of the stored procedure:
USE [TMS] 
GO 
/****** Object:  StoredProcedure [sys].[sp_dbremove]    Script Date: 08-12-2014 22:09:09 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER procedure [sys].[sp_dbremove] --- 1996/04/08 00:00 
    @dbname sysname = null, 
    @dropdev varchar(10) = null 
as 
    declare @dbid int 
    declare @devname sysname 
    declare @physname varchar(255) 
 
    if @dbname is null 
    begin 
        raiserror(15131,-1,-1) 
        return(1) 
    end 
 
    if lower(@dropdev) <> 'dropdev' and @dropdev is not null 
    begin 
        raiserror(15131,-1,-1) 
        return(1) 
    end 
 
    -- You must be SA to execute this sproc. 
    if (not (is_srvrolemember('sysadmin') = 1)) 
    begin 
        raiserror(15247,-1,-1) 
        return(1) 
    end 
 
    -- Make sure not trying to remove within a transaction. 
    set implicit_transactions off 
    if @@trancount > 0 
    begin 
        raiserror(15002,-1,-1,'sys.sp_dbremove') 
        return(1) 
    end 
 
    -- Check to see if database exists. 
    select @dbid = dbid from master.dbo.sysdatabases where name=@dbname 
    if @dbid is null 
    begin 
        raiserror(15010,-1,-1,@dbname) 
        return(1) 
    end 
 
    -- Make sure no one is in the db. 
    if (select count(*) from master.dbo.sysprocesses where dbid = @dbid) > 0 
    begin 
        raiserror(15069,-1,-1) 
        return (1) 
    end 
 
    -- Note: database @dbname may not exist anymore 
    -- If invoke gets error, exception will abort this proc. 
    EXEC %%DatabaseRef(Name = @dbname).SetSuspect(Value = 1) 
 
    declare @dropCmd nvarchar(max) 
 
    select @dropCmd = 'drop database ' + quotename(@dbname) 
    exec (@dropCmd) 
    raiserror(15458,-1,-1) 
 
    return(0)   -- sp_dbremove 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

Today, with this post we explain that how to calculate median in SQL Server or function to find median in SQL Server. Before we calculate/find median in sql server first we understand the what is the median and formula to calculate the median in sql server.

Median means denoting or relating to a value or quality lying at the midpoint of a frequency distribution of observed values or quantities, such that there is an equal probability or falling or below it. To calculate or Find median grou by in SQL Server click here Calculate median group by in SQL Server. For ex: supposed to find the median of bellows values like:
{1,2,3,4,5} then the median is equal 3
{1,2,5,10} then the median is equal 3.5
{1,5,8,7} then median is equal 6

So how to calculate median in SQL Server query, below is the query to find the median

Query:
SELECT CONVERT(FLOAT,
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score ASC) AS BottomHalf))
+ CONVERT(FLOAT,
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score DESC) AS TopHalf)
) / 2) AS Median

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

Today, with this post we explain that how to calculate median in SQL Server or function to find median in SQL Server. Before we calculate/find median in sql server first we understand the what is the median and formula to calculate the median in sql server.

Median means denoting or relating to a value or quality lying at the midpoint of a frequency distribution of observed values or quantities, such that there is an equal probability or falling or below it. To calculate or Find median grou by in SQL Server click here Calculate median group by in SQL Server. For ex: supposed to find the median of bellows values like:
{1,2,3,4,5} then the median is equal 3
{1,2,5,10} then the median is equal 3.5
{1,5,8,7} then median is equal 6

So how to calculate median in SQL Server query, below is the query to find the median

Query:
SELECT CONVERT(FLOAT,
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score ASC) AS BottomHalf))
+ CONVERT(FLOAT,
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score DESC) AS TopHalf)
) / 2) AS Median

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server
Reason for database to go into suspect mode:

• Data files or log files are corrupt.
• Database server was shut down improperly
• Lack of Disk Space
• SQL cannot complete a rollback or roll forward operation

    
How to recover database from suspect mode:

• Change the status of your database. Suppose database name is “BluechipDB”?

EXEC sp_resetstatus '';
Example:
EXEC sp_resetstatus 'BlueChipDB'

• Set the database in “Emergency” mode

ALTER DATABASE  SET EMERGENCY;
Example:
ALTER DATABASE BlueChipDB SET EMERGENCY

• Check the database for any inconsistency

DBCC CHECKDB('');

Example:
DBCC checkdb('BlueChipDB')

If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:
ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

• For safety, take the backup of the database.
• Run the following query as next step.Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone.
• There is no way to go back to the previous state of the database.
• So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:
DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

• Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER

• Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

In this tutorial, I will explain about inline queries. Most of the developers are very familiar with inline queries. Inline queries are basically known as sub queries or Inner Select statements which are always used inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They can be used anywhere in SQL scripts to encounter the conditional expressions. The SQL statement containing an Inline query is also known an outer query or outer select.

How can Inline Queries helpful?

Most of the T-SQL statements which include inline queries can be alternatively formulated as join to encounter the conditional expressions. Other questions can be posed only with inline queries. In T-SQL, there is usually no performance difference between a statement that includes an inline query and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance.

Components of an Inline Query

An inline query has the same features as a normal SQL query. They only persist in the SQL statements and could have the following components –

• A regular SELECT query including regular select list components from the main table.
• A regular FROM clause including one or more table, function or view names.
• An optional WHERE clause to encounter the conditional expressions.
• An optional GROUP BY clause if your query contains aggregation functions.
• An optional HAVING clause if your query contains aggregation functions.

Points to be remember

The SELECT query of an Inline query is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified. There are three basic types of inline queries-
Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
Are introduced with an unmodified comparison operator and must return a single value.
Are existence tests introduced with EXISTS.

To understand their functionality, we will create a table variable to work as the base table for the inline statements as given below-

---- declare OrderMaster table variable body
DECLARE @OrderMaster TABLE
(
OrdId INT IDENTITY(100,1),
OrdDate VARCHAR(30),
CustName VARCHAR(20)
)
---- declare OrderDetails table variable body
DECLARE @OrderDetails TABLE
(
OrdId INT,
ItemId VARCHAR(30),
ItemName VARCHAR(20),
SellingPrice Decimal(12,4)
)
---- Insert Values
INSERT INTO @OrderMaster (OrdDate,CustName)
Values (Getdate(),'Ryan Arjun'),(Getdate(),'Bill Trade'),
(Getdate(),'Rosy White')

---- Pull Order Data
SELECT * FROM @OrderMaster

----Fill Order Details
Insert Into @OrderDetails (OrdId, ItemId, ItemName, SellingPrice)
Values (100, 201,'Apple',135.78),(100, 202,'Mango',235.78)
,(101, 203,'Banana',124.50),(101, 204,'Orange',321.15)
,(102, 205,'Banana',124.50),(102, 204,'Orange',321.15),(102, 201,'Apple',135.78)

--- Pull Order Details
select * from @OrderDetails

Now, we have the tables and want to pull the order and customer wise total sales.

Pull Single Value

There are many ways to do this but we are using inline query to accomplish this task. To pull the single value, we are using inline query within the main SQL statement as given below-

---- Use inline query in the select statement
select OrdId, CustName,
---- Inline query for single value
[Price] =(Select 
[Price]=sum(SellingPrice)
from @OrderDetails
where OrdId=  Om.OrdId
)
from @OrderMaster OM

Pull Multiple Values with Group By
If we want to pull more than one column then inline query should work as join with the main query as given below:

---- Inline Query as Join
SELECT Om.OrdId, Om.CustName,
OD.[Item-Qty], OD.Price
FROM @OrderMaster OM
JOIN
(
SELECT  OrdId,
[Item-Qty]=COUNT(ItemId),
[Price]=SUM(SellingPrice)
FROM @OrderDetails
GROUP BY ORDID
)OD
ON OM.ORDID=OD.ORDID

Pull Value with Where Clause and Group By
If we want to pull more than one column based on some conditional expression then inline query should work as join with the main query as given below:

---- Inline Query as Join
SELECT Om.OrdId, Om.CustName,
OD.[Item-Qty], OD.Price
FROM @OrderMaster OM
JOIN
(SELECT  OrdId,
[Item-Qty]=COUNT(ItemId),
[Price]=SUM(SellingPrice)
FROM @OrderDetails
---- Conditional Expression
WHERE ItemName='Apple'
GROUP BY ORDID
)OD
ON OM.ORDID=OD.ORDID

Conclusion

It’s very beneficial concept in SQL and we can use them inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They are easily applicable in the function and stored procedure. These features are important in some Transact-SQL statements; the inline-query can be evaluated as if it were an independent query. Conceptually, the inline-query results are substituted into the outer query.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We 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 was established to cater to an underserved market in the hosting industry; web hosting for customers who want excellent service. HostForLIFE.eu - a cheap, constant uptime, excellent customer service, quality, and also reliable hosting provider in advanced Windows and ASP.NET technology. HostForLIFE.eu proudly announces the availability of the ASP.NET Core 1.0 RC2 hosting in their entire servers environment.

From SQL Server 2012 version, when SQL Server instance is restarted, then table's Identity value is jumped and the actual jumped value depends on identity column data type. If it's integer (int) data type, then jump value is a thousand and if huge integer (bigint), then jump value is ten thousand. From our application point of view, this increment isn't acceptable for all the business cases especially once the value shows to the client. this is the special case/issue ships with only SQL Server 2012 and older versions don't have any such issue.

A few days ago, our QA Engineer claims that one of our table's identity column jumped ten thousand. Which means the last identity value of that table was 2200 now it's 12001. In our business logic is like that the value shows to the client and it will not be accepted by the client. So we must solve the issue.
Using the Code

The first time, we all are surprised and confused on how it's possible? we usually don't insert any value in identity column (insert value to identity column is possible). The identity value is maintained by SQL Server itself. one of our core team members started investigation the issue and found out the solution. Now, i want to elaborate the issue and solution that was found out by my colleague.

Now, you need to setup SQL Server 2012 and create a test database. Then create a table with auto identity column with the following code:
create table MyTestTable(Id int Identity(1,1), Name varchar(255));

Now insert 2 rows there:
insert into MyTestTable(Name) values ('Mr.Tom');
insert into MyTestTable(Name) values ('Mr.Jackson');

You see the result:
SELECT Id, Name FROM MyTestTable;

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.

Now, insert another 2 rows to the same table again:
insert into MyTestTable(Name) values ('Mr.Tom2');
insert into MyTestTable(Name) values ('Mr.Jackson2');

Now, you can see the result:
SELECT Id, Name FROM MyTestTable;

Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000. Previously, I said that we also see if the data type of that identity column is bigint, then it will jump 10000.

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:
CREATE SEQUENCE Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE

   insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom');

   insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson');

How to Register -t272 to SQL Server Startup Parameter?
First, open the 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 as you can see on the following picture:

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. I hope you will read all those and take the appropriate decision.

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