how to create SQL unique constraint

We will discuss about the SQL unique constraint and difference between Primary Key and Unique key and demonstrate how to create/drop unique constraint

The SQL unique constraint ensures that all values in a columns are unique.

By default Primary Key constraint also granted the uniqueness of columns values and the only difference is that primary key does not accept NULL value but unique key constraint accept a single NULL value in column.

Difference between Primary Key Vs Unique Key:

·         Primary Key does not accept NULL value but Unique Key do

·         Only one Primary key per table but we can create multiple unique key per table.

Here is a table OrderStatus, which contains Primary Key on StatusID

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50) NOT NULL,

       CONSTRAINT [OrderStatus_PK] PRIMARY KEY CLUSTERED ( StatusID ASC)

)

Syntax: Create SQL Unique Constraint on Create Table:

The below SQL script will create a unique constraint  ‘OrderStatusCode_UC  ’ on  a Column StatusCode in table and it ensures that Column should not duplicate Code.

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50) NOT NULL,

       CONSTRAINT [OrderStatus_PK] PRIMARY KEY CLUSTERED ( StatusID ASC),

       CONSTRAINT OrderStatusCode_UC UNIQUE (StatusCode)

)

Syntax: Create SQL Unique Constraint on Alter Table:

The below SQL script will create a unique constraint ‘OrderStatusCode_UC’ on already existing table

ALTER TABLE OrderStatus

ADD CONSTRAINT OrderStatusCode_UC UNIQUE (StatusCode);

The below SQL script will create a unique constraint ‘OrderStatusDescCode _UC’ on multiple columns

ALTER TABLE OrderStatus

ADD CONSTRAINT OrderStatusDescCode_UC UNIQUE (StatusCode, StatusDescription);

Drop SQL Unique Key Constraint:

You can simply use below SQL script to Drop unique constraint

ALTER TABLE OrderStatus

DROP CONSTRAINT OrderStatusDescCode_UC;

SQLBulkCopy : Received an invalid column length from the bcp client for colid 10

SQLBulkCopy : Received an invalid column length from the bcp client for colid 10.

I recently encountered this exception while migrating data from old system to new system. I am using SQLBulkCopy ddl to bulk copy of data and here is C# Code

C#:

void BulkInsert<TEntity> (IEnumerable items, string schemaName, bool keepIdentity = true) where TEntity : class

        {

            var context = new DBEntities() { AuditChanges = false };

            context.Database.Connection.Open();

            var conn = context.Database.Connection;

            var options = SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.CheckConstraints;

            if (!keepIdentity)

            {

                options = SqlBulkCopyOptions.CheckConstraints;

            }

            using (var scope = conn.BeginTransaction(IsolationLevel.ReadCommitted))

            {

                var copy = new SqlBulkCopy(conn as SqlConnection, options, scope as SqlTransaction)

                {

                 

                    BatchSize = 20000,               

                    DestinationTableName = schemaName + $".{typeof(TEntity).Name}",

                };

                var dbReader = items.AsDataReader();              

                copy.WriteToServer(dbReader);

                scope.Commit();

            }

         }

I looked into data and destination table schema and found that there are few record in source table, which length is exceeded to destination table's column.

So to resolve this issue, I simple increased the length of destination table column from varchar (10) to varchar (20).

SQL Script –  Increase size of varchar column 10 to 20

         

 ALTER TABLE [dbo].[TableName] ALTER COLUMN [ColumnName] Varchar(20)

Thanks for visiting!!

SQL database Project : A project which specifies SQL Server vNext CTP as the target platform cannot be published to SQL Server 20016

“A project which specifies SQL Server vNext CTP as the target platform cannot be published to SQL Server 20016.”

I recently encountered the above error message while publishing or generating SQL script from database project.

After getting this error, I investigated and found that database project’s Target Platform was SQL Server 2017 and Target SQL Database is SQL Server 2016 version and followed to below steps to resolve this incompatibility issue.

Step 1: Click on Advanced Button

Step 2: Advance Publish Settings à General Tab à Check “Allow incompatible platform” option under Advanced Deployment Options

Step 3: Click on Generate Script Button and your SQL script is successfully generated

Thanks for visiting !!

System.Data.SqlClient.SqlException : Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

In this blog, we will discuss how to resolve the SQL Exception - Execution Timeout Expired

Recently I was dealing with huge data approximate 160M records and performing data analyzing task and on some point, I encountered SQL Exception.

System.Data.SqlClient.SqlException

  HResult=0x80131904

  Message=Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

  Source=.Net SqlClient Data Provider

  StackTrace:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

Usually default command timeout is 30 seconds and if you don’t receive anything from database within 30 seconds, it will throw System.Data.SqlClient.SqlException : Execution Timeout Expired.

To resolve this problem, we need to increase the connection timeout for SQL command, if you feels SQL script /proc is already well tuned and tables are proper indexed.

Eg. :

SqlCommand command = new SqlCommand(commandText, sqlConnection);

command.CommandTimeout = 60000;

You can simply set waiting time for SQLCommand

There are two timeout options available for application to access SQL Server Database :

• Connection Timeout
• Command Timeout

Connection Timeout: it is waiting time for application to establish connection with Database and if application is not able to establish connection with database, it throws SQL Exception

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

You can increase the waiting time for SQL connection timeout and you have to add connection timeout attribute in SQL connection string

string connectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30";

Command Timeout – it is waiting time for command to execute script, if within command timeout it does not complete execution, it throws SQL Exception

System.Data.SqlClient.SqlException : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding

And by default, command timeout value is 30 seconds and you can increase the waiting time for command timeout.

SqlCommand command = new SqlCommand(commandText, sqlConnection);

command.CommandTimeout = 6000; (values in millisecond)

Thanks for visiting!!

SQL : how to create temporal table in sql server 2016

In SQL Server 2016, Microsoft introduced a new feature Temporal Table. by using this feature, we can easily keep the full history of data change for table without doing any extra coding or efforts. It will allow to you get table data at any point of time of past.

Temporal table have same number of fields as Regular/Main tables and have two new extra columns start and end date.

1. If you update a record in main table, the old version of record will be added into history table with start and current end date stamp. 2. If you delete a record from main table, the deleted record will be added into history table with start and current end date stamp.

Temporal table serves many purposes : Audit : temporal table is being used to store the data history of main table and you can retrieve the old version of data on given specific date time. Back-up : temporal table helps you to restore the accidentally deleted or updated record.

Syntax to Create Temporal Table :

There are many options available to create temporal table.

1. Auto-generated  name: you can create a temporal table without specify schema and table name and system create the corresponding history table with auto-generated name.

CREATE TABLE Office  

(   

OfficeID int NOT NULL PRIMARY KEY CLUSTERED

  , OfficeName varchar(50) NOT NULL

  , Street   varchar(50) NOT NULL

  , City varchar(50) NOT NULL

  , State varchar(2) NOT NULL

  , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

  , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)   

WITH (SYSTEM_VERSIONING = ON)  

2. User defined name : you can create a temporal table with use defined schema and table name.

CREATE TABLE Office  

(   

OfficeID int NOT NULL PRIMARY KEY CLUSTERED

  , OfficeName varchar(50) NOT NULL

  , Street   varchar(50) NOT NULL

  , City varchar(50) NOT NULL

  , State varchar(2) NOT NULL

  , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

  , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)  

WITH   

  (  

     SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OfficeHistory)  

  )

Insert new record in Office table :

Insert dbo.office(officeID, OfficeName, Street, City, State)

Select 1, 'NYK', '1001 Street RD', 'NYK', 'OH'

Select * from dbo.office

SELECT *  FROM [dbo].[OfficeHistory]

No Record available in history table

Update Office Street Address :

Update dbo.office SET Street = '1999 PTS Street' where OfficeName = 'NYK'

Select * from dbo.office

SELECT *  FROM [dbo].[OfficeHistory]

you can see old version of office record in history table with start and end time stamp.

Delete Office Street Address :

 Delete from dbo.office where OfficeName = 'NYK'

 Select * from dbo.office

 No Record available in office table

  SELECT *  FROM [dbo].[OfficeHistory]

In history table, you can see two old version of record for same office ( office id – 1)