Accent-Sensitivity in SQL Server

Accent comes in picture where an application runs most the queries with first name and last name.

Some application which frequently include the functionality to support database queries based on first name and last name, which is a problematic when names contain accent character and the database is accent – sensitivity

For Example we have a table Emp_Master where we want to check all the names start with TEST, the result should be include

   TEST

   TEST1

   TEST123

   TEST1234

   ´TEST12345

   TEST´4567

But when database collation is accent sensitive the result include only

  TEST

  TEST1

  TEST123

  TEST1234

If you noticed above queries has left out all the name which are related with accent which is not a desire result.

To get a desire result we can provide two solutions to above

1)       Configure the database so that it is not accent sensitive, change the collation to SQL_LATIN1_GENERAL_CP1_CI_AI

2)       Select * from Emp_Master where fname like ‘TEST%’ collation SQL_LATIN1_GENERAL_CP1_CI_AI

Option 1 is a permanent solution and the so that when you install any new instance change the collation second option is based on requirement so that you do not have to change any database settings

Change the SQL Server Collation

Please remember it is a complex operation and can perform with below three steps

1)       Create the scripts that need to re create the database

2)       Export all your data

3)       Drop all the user database

4)       Rebuild master database new SQL Collation Property by using below command

 

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 

/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 

/SQLCOLLATION=CollationName

 

 Keep Reading!!!!!!!!! (Sanjay) (mesanjaybakshi@gmail.com)

(SQL Server )Output Clause $action Function

In SQL Server 2005 and above there is a feature of displaying an output result by output clause.The clause returns a copy of the data which user has inserted or deleted.

User can return the data into a table variable, temporary or parmanent table or to the processing application that's call the DML statements. We can use this as a archieving,confirmation message or other application requirements.

SQL 2005 allows user to add on output clause to insert,update and delete statement.Sql 2008 make us eligible to use output clause with Merge statement also.

In this blog we will learn how to use output clause with these statements.

Using with Insert Statement
*****************************************************
--Created Main Table which is actual table
Create Table TEST_INSERTED
(
EMP_NAME VARCHAR(100)
)
--Created Recorded Table where data needs to be inserted 
Create Table TEST_INSERTED_RECORDED_Example1
(
EMP_NAME VARCHAR(100)
)
--Inserted record into Actual Table
Insert into TEST_INSERTED
Output
INSERTED.*
INTO TEST_INSERTED_RECORDED_Example1
values('ABC')

--Get Record into Actual Table
 Select * from TEST_INSERTED

--Get Record into Recorded Table
Select * from TEST_INSERTED_RECORDED_Example1

User can see same record in both the table
******************************************************
Using with Update Statement
********************************************************We will use same actual table for update statement but we will create new recorded table, as we will store old value and new value both.

--Created Recorded Table where data needs to be inserted
Create Table TEST_INSERTED_RECORDED_Example2
(
NEW_EMP_NAME VARCHAR(100),
OLD_EMP_NAME VARCHAR(100)
)

--Write Update Statement
Update TEST_INSERTED
SET EMP_NAME='ABC_NEW_NAME'
output
Deleted.Emp_Name,
Inserted.Emp_Name
into
TEST_INSERTED_RECORDED_Example2
WHERE EMP_NAME='ABC'

--Get Record into Actual Table
Select * from TEST_INSERTED

--Get Record into Recorded Table
Select * from TEST_INSERTED_RECORDED_Example2

******************************************************
Using with Delete Statement
********************************************************We will use same actual table for delete statement but we will create new deleted table

--Created Recorded Table where data needs to be inserted 
Create Table TEST_INSERTED_RECORDED_Example3
(
Deleted_EMP_NAME VARCHAR(100)
)

--Write Update Statement
Delete from TEST_INSERTED
Output Deleted.* Into TEST_INSERTED_RECORDED_Example3


--Get Record into Actual Table
Select * from TEST_INSERTED

--Get Record into Recorded Table
Select * from TEST_INSERTED_RECORDED_Example3

******************************************************

Happy Reading.......
Sanjay
mesanjaybakshi@gmail.com

SQL Server (CTE)

 

 

 

 

What is CTE

 

CTE stands with Common Table Expression. CTE can be thought as a temporary result set that defined within the execution scope of Single Select/Update/Delete/Create View.

 

This worked like derived table which does not store data as an object and last only till the duration of query.

 

Where Developer can use CTE

 

1) When Developer wants to use Recursive Query

2) Substitute of View when you want to run the View and do not want to store the definition

3) Refrence as a table which developer uses very often

 

 

Disadvantage of CTE

 

CTE can not be nested like Sub Query

 

How to use

 

USE <DB NAME>

GO

 

With TEST_CTE (Emp_Id,Emp_Name,DOJ)

as

--Define the Query

(

 

Select Emp_Id,Emp_Name,DOJ  from <Table Name>

 

)

 

--Define the Query Result

 

 

Select Emp_Id,Emp_Name,DOJ from Test_CTE

 

 

 

Happy Reading!!!!!!!!!!!!!!

mesanjaybakshi@gmail.com