Dates in SQL

Hi Folks!!!!!
This time i am here with very important but basic topic of “How to use DATE in different format in SQL”.
Here by i am mentioning some of the important formats of dates which we used in daily work.

Few Generic queries :

 

Format	Query	Results
1	Select convert(varchar,getdate( ),1)	08/31/11
2	Select convert(varchar,getdate( ),2)	11.08.31
3	Select convert(varchar,getdate( ),3)	31/08/11
4	Select convert(varchar,getdate( ),4)	31.08.11
5	Select convert(varchar,getdate( ),5)	31-08-11
6	Select convert(varchar,getdate( ),6)	31 Aug 11
7	Select convert(varchar,getdate( ),7)	Aug 31 11
10	Select convert(varchar,getdate( ),10)	08-31-11
11	Select convert(varchar,getdate( ),11)	11/08/31
101	Select convert(varchar,getdate( ),101)	08/31/11
102	Select convert(varchar,getdate( ),102)	2011.08.31
103	Select convert(varchar,getdate( ),103)	31/08/2011
104	Select convert(varchar,getdate( ),104)	31.08.2011
105	Select convert(varchar,getdate( ),105)	31-08-2011
106	Select convert(varchar,getdate( ),106)	31 Aug 2011
107	Select convert(varchar,getdate( ),107)	Aug 31 2011
110	Select convert(varchar,getdate( ),110)	08-31-2011
111	Select convert(varchar,getdate( ),111)	2011/08/31

Author : Feel free to put on your query here, i will be more than happy if i will help you.

 

NOLOCK in SQL

This time i am here with one of the fascinated member of SQL family which we love to call is “NOLOCK”

What is NOLOCK
As the name suggest, it will help in accessing the locked data.

Why NOLOCK
Sometime it is really required to access the data which is locked by some other transaction, hence we are in requirement of NOLOCK.

Where NOLOCK
It is used with SELECT command of SQL.

USE of NOLOCK
Let’s start the use of NOLOCK with the help of example.

Step 1 :
We have a table named “NOLOCKS” and fired the query to retrieve data

Step 2 :
Fire update query under TRANSACTION command without giving ROllBACK or COMMIT command at the end

Step 3 :
Try to execute the “SELECT” in new widow, it will execute forever

Step 4:
You have seen that SELECT query which executed in second’s of time initially, now executing for ever, this happens because UPDATE command under TRANSACTION is not yet committed to the database so the records are locked, hence SELECT query is not able to retrieve the data as it did initially, under such scenario what will we do if we really required data to be fetched ? no worries we have the solution in the name of “NOLOCK” and get updated record as well, let’s have a look on below query

Step 5 :
Now you can see, records has been fetched though the data has been locked by Update command.

Point to remember :

• If you rollback or commit the Update command under the transaction, there is no need of NOLOCK as data is already released by update command.

• If you ROLLBACK the command, changes that has been made by Update command will be rollbacked.

Author : Feel free to ask if you have any query.

 

User Defined DataType in SQL 2008 R2

User Defined Data types
In our generic work around, sometime it requires to create column which having its own definition of datatype apart from existing one, this is something where User Defined Data types (UDD) come into light.
In this article , i am going to elaborate how to create datatype which having its own rules and will act as a existing datatype.

Why UDD
If we want to have column containing phone number type data e.g. +91-9898098980 (Necessarily ‘+’ at first position and ‘-‘ at fourth, you will be in the need of new data type, hence UDD come into light.

Let’s start the thing with example

Problem

Lets take same problem of having column that have
1. ‘+’ at the start.
2. ‘-‘ at fourth place.
e.g. +91-9898098980

How to proceed

Step 1 : First of all we need to create Default and Rule for Data Type, below is the query for the same.

Step 2 : We need to create User defined data type,

• Click DataBases –> DataBase Name (TestDB) –>Programmability –> Types –> Right Click –> New –> User Defined Data Types ; you will find page as below :

Step 3 :  You can provide detail as desired and click on OK, Your UDD is ready now, detail is below

General
Schema	Describes the schema of table.
Name	Name of Data Type
Data Type	Type of data type
Length	Length of the value e.g here in this case +91-9898098980 it is 14.

Binding
Default	Browse the Default which we have created initially.
Rule	Browse the rule which we have created initially.

Step 4 : Open Table Design of Same database having schema dbo, you will find new data type

Now we are done with UDD, by using PhoneNumber:varchar(14), we are able to create a column which satisfies both condition.

Author : Feel free to ask if you have any query.

 

Collation in SQL Server

What is collation

In one line, collation is set of rules that governs how to use characters, alphabets or language in SQL Server.

We came across problem of “Cannot resolve collation conflict” whenever we try to fetch data from two databases having different collation.Here i am elaborating the issue with detail by taking example.

Example

Let’s start from the scratch……..

Step 1 : we are going to create two database with different collation.

create database DB1 collate Latin1_General_CI_AS

Create database DB2 collate Latin1_General_CI_AI

Step 2 Creating tables and insertion of data.

use DB1

Create table Test1(id1 int not null primary key, Name1 varchar(255))
Insert into Test1 (id1,Name1) values (1,’Amit’)

use DB2

Create table Test1(id int not null primary key, Name varchar(255))
Insert into Test1 (id,Name) values (1,’Amit’)

Step 3 : Let’s fire two query as below :

Query 1 : Joining based on ID (Int datatype)

select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.id1=T2.id

we will get the result as below :

Id1    Name1   Id   Name

1       Amit       1     Amit

** that is what we want……

Query 2 : Joining based on Name (varchar datatype) :collation will play role here

select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.Name1=T2.Name

we will get error in this case as below..

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “Latin1_General_CI_AS” in the equal to operation.

Question

Why we are getting error in second case, though we are able to execute the first query.

Answer

This happens because of different collation of database, difference in database collation won’t effect  int datatype, hence generating result in first case while throws error when we try to fetch data based on joining of Name (varchar datatype) column

Solution

Just a minor updation in query will do our work, i.e set a collation to the query no 2 either of database DB1 or DB2, have a look on collate part in below query

we can do either way

1. Select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.Name1=T2.Name
    collate Latin1_General_CI_AS

2. Select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.Name1=T2.Name
    collate Latin1_General_CI_AI

Both will yield result as desire, in our case we will get result as follow

Id1    Name1   Id   Name

1      Amit       1    Amit

Author : We will discuss on some other topic in next post.

 

Triggers

Firing of Triggers in Order

• Some time we need triggers to fire in order, in general we have no specific control over the order in which triggers will fire, in order to control the same, i eventually come across to the system stored procedure named sp_settriggerorder.
• sp_settriggerorder.is meant for to provide ordering to the triggers to fire, parameters are as below

sp_settriggerorder_parameters

Exec sp_settriggerorder @triggername = trigger_name
@order =[FIRST|LAST|NONE],
@stmttype =[INSERT|UPDATE|DELETE|],
@namespace = [DATABASE|SERVER|NULL]

Example

• Scenario : We have table say Table1 where we want to insert data, we need to fire two insert triggers on Table1, also we need to fix the order in which these triggers will fire.

• Workaround : First of all, we have created Trigger1 and then Trigger2, we have inserted the record into the table

Sequence of execution are as follow :

1. INSERT command
2. Trigger1
3. Trigger2

Note : if we drop the trigger1 and recreated the same, order of the execution of triggers has been change as below, as system prioritize the triggers as First come first server basis.

Sequence of execution will be as follow :

1. INSERT command
2. Trigger2
3. Trigger1

Here comes the role of sp_settriggerorder, we need to do some tweaking while providing parameters in sp_settriggerorder as below

exec sp_settriggerorder @triggername = Trigger1
@order = FIRST,
@stmttype =INSERT,
@namespace = NULL

exec sp_settriggerorder @triggername = Trigger2
@order = LAST,
@stmttype =INSERT,
@namespace = NULL

Now if we executed the insert command it will always executed in below mention order.:

Sequence of execution are as follow :

1. insert command
2. Trigger1
3. Trigger2

Note : it doesn’t matter whether trigger1 is created first or last.

Over All order of execution will be as follow

exec sp_settriggerorder @triggername = Trigger1
@order = FIRST,
@stmttype =INSERT,
@namespace = NULL

exec sp_settriggerorder @triggername = Trigger2
@order = LAST,
@stmttype =INSERT,
@namespace = NULL

Insert into table1 (col) values(value1)

Result : 

Message

Firing modified Trigger1

Firing Modified Trigger2

Author : We will discuss on some other topic in next post.