SQL Server 2012 Auto Identity Column Value Jump bug!

I am working on a database using SQL Server 2012. There is a table named Order has an auto Identity Column named OrderId datatype is int .

I noticed that the sequence of the identity is just not right. It has jumped with 1000.
After research about this issue, I found that this is an issue with SQL Server 2012 version.

From SQL Server 2012 version, when SQL Server instance is restarted then its auto Identity column value is jumped based on identity column datatype.

If it is an integer data type, then jump value is 1000 and if  it is a big integer, then jump value is 10000.

So now how can we solve this issue?
We can work around by 2 ways:

1- Using Sequence

We will have to remove Identity column from the table.
Create a sequence without cache feature and insert number from that sequence.
Please find below code sample:

CREATE SEQUENCE OrderId_Sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 0
NO MAXVALUE
NO CACHE

This is how you can use that Sequence:

INSERT INTO Order VALUES(NEXT VALUE FOR OrderId_Sequence, 'OrderName', 'OrderDescription');

2- Register -t272 to SQL Server Startup Parameter

Go to SQLServer configuration manager.
Select SQL Server 2012 instance then select Properties Menu.
You should find a tabbed dialog window.
Now you select start up parameters tab from there and register -t272.
Finally restart SQL Server 2012 instance.

Source: https://www.nilebits.com/blog/2014/02/sql-server-2012-auto-identity-column-value-jump-bug/

How to get Year, Month and Day out of SQL Date in SQL Server

Let is say that we have the following Date 2007-04-05 10:13:14.109 and we want to get Year, Month and Day out of this date.

There is a function named DATEPART() that can be used to archive this.

DECLARE @MyDate AS DATETIME 
SET @MyDate = '2007-04-05 10:13:14.109'
SELECT DATEPART(DAY, @MyDate) AS MyDay, DATEPART(MONTH, @MyDate) AS MyMonth, DATEPART(YEAR, @MyDate) AS MyYear

The result would be:

MyYear	MyMonth	MyDay
2007	04	05

Difference between Inner and Outer Join in SQL

Joins are used to combine the data from two tables, with the result being a new, temporary table.
The temporary table is created based on column(s) that the two tables share, which represent meaningful column(s) of comparison.
The goal is to extract meaningful data from the resulting temporary table.
Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join.
A join can be either an inner join or an outer join, depending on how one wants the resulting table to look.

Example:
Suppose you have two Tables, with a single column and data as follows:

Table1: ID1                        Table2: ID2

A                                        C

B                                        D

C                                        E

D                                        F

Inner Join Query would look like:

select * from Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID2

You would get the following result:

ID1    ID2

C       C

D       D

Left Outer Join Query would look like:

select * from Table1 LEFT OUTER JOIN Table2 ON Table1.ID1 = Table2.ID2

You would get the following result:

ID1    ID2

A       null

B       null

C        C

D        D

Full Outer Join Query would look like:

select * from Table1 FULL OUTER JOIN Table2 ON Table1.ID1 = Table2.ID2

You would get the following result:

 ID1    ID2

A       null

B       null

C        C

D        D

null      E

null      F

How to check SQL Server Database is Exists using C#

The following method using sys. schema and views such as sys.databases and sys.tables.
As we know that the old style sysobjects and sysdatabases and those catalog views have been deprecated since SQL Server 2005.

private static bool CheckDatabaseExists(string connectionString, string databaseName)
{
    string sqlQuery;
    bool result = false;

    try
    {
        SqlConnection conn = new SqlConnection(connectionString);
        sqlQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name = '{0}'", databaseName);

        using (conn)
        {
            using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
            {
                conn.Open();
                int databaseID = (int)cmd.ExecuteScalar();
                conn.Close();
                result = (databaseID > 0);
            }
        }
    }
    catch (Exception ex)
    {
        result = false;
    }
    return result;
}

How to Dynamically Select rows in SQL Server

If you want to restrict other developers from using your Stored Procedures that returns a huge amount of data which affects server performance and Application Performance weather it is a Windows or Web Application.

You can use the following idea when you create Stored Procedures:

-- =============================================
-- Author:        Amr Saafan
-- Create date: Jan 5, 2010
-- Description:    Get Employees
-- =============================================
CREATE PROCEDURE GetEmployees
@RowsCount INT = 10
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT TOP (@RowsCount) [EmployeeID], [EmployeeName], [EmployeeEmail]
FROM [dbo].[Employee]

END
GO

As we can see whenever the Stored Procedure called, it will not return more than 10 rows which is a reasonable amount of rows and in the same time if you want less or more you can just pass the number of rows you need @RowsCount = 1 or 1000