Jan 27, 2014

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

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.

Jan 21, 2014

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


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'

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'  

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'

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