Insert default value when data or parameter is null in sql server.

Insert default value in a table when data or parameter or value is null in a not null column in sql server.

Suppose we have created a table TestDefault by following sql query:

CREATE TABLE TestDefault(

    Id INT IDENTITY PRIMARY KEY,

    Value INT NOT NULL DEFAULT(1),

    Remarks VARCHAR(100) NULL

)

We can insert default value in column value in following ways:

1. INSERT INTO TestDefault DEFAULT VALUES

2. INSERT INTO TestDefault(Value,Remarks) VALUES(DEFAULT, 'Exact Help')

3. INSERT INTO TestDefault(Remarks) VALUES('Exact Help')

But when we will try to insert NULL in column Value we will get error message. For example:

DECLARE @Data AS INT = NULL

INSERT INTO TestDefault(Value) VALUES(@Data)

Error message will something like this: Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

Solution 1:

We can insert own default value when data is null. For example:

DECLARE @Data AS INT = NULL

INSERT INTO TestDefault(Value) VALUES(ISNULL(@Data,1))

Solution 2: If we want to insert default value of column then we can do in this way:

DECLARE @Data AS INT = NULL

INSERT INTO TestDefault(Value) VALUES(ISNULL(@Data,dbo.DefaultVal('Value','TestDefault')))

For this fisrt you have to create user defined function DefaultVal. Which will something like this:

CREATE FUNCTION DefaultVal(

    @ColumnName AS VARCHAR(100),

    @TableName AS VARCHAR(100)

)

RETURNS VARCHAR(200)

AS

BEGIN

    DECLARE @DefaultValue AS VARCHAR(200)

   

    SELECT  @DefaultValue = dc.definition   

    FROM sys.columns c INNER JOIN sys.default_constraints dc 

    ON c.default_object_id = dc.object_id

    WHERE c.object_id = object_id(@TableName) AND c.name = @ColumnName

    SET @DefaultValue = REPLACE(REPLACE(@DefaultValue,')',''),'(','')

    RETURN @DefaultValue

END

Note: This function is not useful if default value is complex expression like ((5)+(3)) or default value of character type column keeps ')' or '('.

Writing dynamic sql queries in sql server
Difference between temporary table and table variable in sql server
Difference between varchar and nvarchar in sql server.
Difference between char and varchar in sql server.
Variables in sql server 2008 R2
BETWEEN keyword in sql server
How to use variable in ORDER BY clause in sql server.

Sql query to update and insert records without deleting and inserting into a table in sql server

Alternative way to update a table without deleting and inserting in sql sever

Suppose we have two tables: tblSource and tblTarget.

tblSource

CREATE TABLE tblSource(

    ID INT IDENTITY PRIMARY KEY,

    vcData VARCHAR(500)

)

INSERT INTO tblSource VALUES('PC'),('Laptop'),('Mobile')

ID	vcData
11	PC
12	Laptop
13	Mobile

tblTarget.

CREATE TABLE tblTarget(

    ID INT IDENTITY PRIMARY KEY,

    vcData VARCHAR(500)

)

INSERT INTO tblTarget VALUES('Laptop')

ID	vcData
6	Laptop

Now we want transmit the data from tblSource to tblTarget without first deleting from tblTarget then inserting into it from tblSource.

SQL query:

WITH cteTarget

AS(

    SELECT

         *,

         ROW_NUMBER() OVER(ORDER BY ID) AS TID

    FROM tblTarget

)

MERGE cteTarget

USING(

    SELECT

         vcData,

         ROW_NUMBER() OVER(ORDER BY ID) AS SID

         FROM tblSource

) AS tblSource(vcData,SID)

ON cteTarget.TID =  tblSource.SID

WHEN MATCHED THEN

    UPDATE SET vcData = tblSource.vcData

WHEN NOT MATCHED BY SOURCE THEN

    DELETE

WHEN NOT MATCHED BY TARGET THEN

    INSERT(vcData) VALUES(tblSource.vcData);

After executing this query data in the tblSource will be:

ID	vcData
6	PC
7	Laptop
8	Mobile

Explanation:

Now I am going to explain how this query work. I have created one pseudo column (Or rwo id) in both tblSource and tblTarget. Which will look some thing like this:

tblSource

ID	vcData	SID
11	PC	1
12	Laptop	2
13	Mobile	3

tblTarget(cteTarget)

ID	vcData	TID
6	Laptop	1

This query will compare SID and TID. If SID is equal to TID then it will update the vcData column of tblTarget. That is "Laptop" will be updated by "Pc". If SID is not equal to TID then if value of SID is not present in TID then it will insert the records into the tblTarget. That is  row of SID 2 and 3 will be inserted into tblTarget. And if value of TID is not in SID then that rows will be deleted from tblTarget.

Effect of collation in indexing in sql server

Changing the collation name of a string in sql server can decrease the performance of some queries. It is due to query cannot use some index to optimize the performance.  For example, suppose we have student table of following schema:

CREATE TABLE Employee(

    EmpId INT IDENTITY PRIMARY KEY CLUSTERED,

    EmpName VARCHAR(100),

    Country VARCHAR(50)

)

Inserting some data into it:

INSERT INTO Employee(EmpName,Country) VALUES('Scott','USA'),('Greg','usa'),('Marry','UK') 

Creating non clustered index on it

CREATE NONCLUSTERED INDEX NXI_Employee

ON Employee(Country)

INCLUDE(EmpName)

Now we will observe the execution plan of following sql query:

SELECT EmpName

FROM Employee

WHERE Country = 'USA'

It is using index seek to search the 'USA' in the country Column of Employee table.

Now we will check the execution plan of same query but different collation that is:

SELECT EmpName

FROM Employee

WHERE Country = 'USA' COLLATE Latin1_General_CS_AI

Now it is not using index seek to search the 'USA' in the country Column of Employee table. Now it will follow index scan (slow process)

Indexes best practices in sql server: Where to start creating indexes?
Sql server query optimization tips : Tuning best practices with examples
Fastest or most efficient way to insert data in sql server
Index in sql server with examples
What is clustered index in sql server
What is non clustered index in sql server
When sql server is able to use indexes and when not

Width sensitive and insensitive collation examples in sql server

In collation name meaning WS

WS: Width sensitive

Example of width insensitive comparison in sql server

IF 'm3' = 'm³' COLLATE Latin1_General_CI_AI

    SELECT 'Width insensitive'

ELSE

    SELECT 'Width sensitive'  

Output:  Width insensitive

Example of width insensitive comparison in sql server

   

IF 'm3' = 'm³' COLLATE Latin1_General_CI_AI_WS

    SELECT 'Width insensitive'

ELSE

    SELECT 'Width sensitive'

Output:  Width sensitive

Indexes best practices in sql server: Where to start creating indexes?
Sql server query optimization tips : Tuning best practices with examples
Fastest or most efficient way to insert data in sql server
Index in sql server with examples
What is clustered index in sql server

Ascent sensitive and insensitive collation example in sql server

In collation name meaning of AI and AS

AI: Ascent insensitive

AS: Ascent sensitive

Example of ascent insensitive comparison in sql server

IF 'a' = 'à' COLLATE Latin1_General_CI_AI

    SELECT 'Ascent insensitive'

ELSE

    SELECT 'Ascent sensitive'   

Output:  Ascent insensitive

Example of ascent insensitive comparison in sql server

   

IF 'a' = 'à' COLLATE Latin1_General_CI_AS

    SELECT 'Ascent insensitive'

ELSE

    SELECT 'Ascent sensitive'  

Output:  Ascent sensitive

What is non clustered index in sql server
When sql server is able to use indexes and when not
How to use database engine tuning advisor to get recommendations in sql server
Effect of collation in indexing in sql server.
Write sql queries in set based approach sql server
Sql server best practices for stored procedure
What is Adhoc query in sql server: Cached Query Plan
Subquery vs inner join which one is better in sql server

Case sensitive and insensitive collation examples in sql server

In collation name meaning of CI and CS

CI: Case insensitive

CS: Case sensitive

Example of case insensitive comparison in sql server

IF 'a' = 'A' COLLATE Latin1_General_CI_AI

    SELECT 'Case insensitive'

ELSE

    SELECT 'Case sensitive'

Output:  Case insensitive

Example of case insensitive comparison in sql server

   

IF 'a' = 'A' COLLATE Latin1_General_CS_AI

    SELECT 'Case insensitive'

ELSE

    SELECT 'Case sensitive'

Output:  Case sensitive

Difference between actual execution plan and estimated execution plan in sql server
Best ways to pass comma delimited varchar variables to IN clause sql server

How to perform case sensitive comparison in sql server

How to make or perform case sensitive search or comparison in WHERE clause in sql server

Sql sever default comparison is case insensitive. For example suppose we have Customer table

CustomerID	CompanyName	Country
1	Microsoft	usa
2	IBM	USA

  

SELECT CustomerID,CompanyName

FROM Customer

WHERE Country = 'USA'

Output: 

CustomerID	CompanyName
1	Microsoft
2	IBM

We can observe comparison is case insensitive. It is due to default collation SQL_Latin1_General_CP1_CI_AS which is case insensitive.   We can make it case sensitive by using case sensitive collation. For example:

SELECT CustomerID,CompanyName

FROM Customer

WHERE Country = 'USA' COLLATE SQL_Latin1_General_CP1_CS_AS

Output:

CustomerID	CompanyName
2	IBM

Note:  SQL_Latin1_General_CP1_CS_AS is case sensitive collation

SQL server interview questions and answers with explanation
Width sensitive and insensitive collation examples in sql server.
Ascent sensitive and insensitive collation example in sql server.
Case sensitive and insensitive collation examples in sql server.