Wednesday, 15 June 2016

SQL : CASE IN COMPUTED COLUMNS

This time i come up with very basic but important concept of Computed columns in SQL.
Let's start with new fresh example that will expedite your understanding.
Step 1 : Create new Customer Table
CREATE TABLE CUSTOMER(Customer_Id INT, Customer_Name Varchar(100))
Step 2: Insert few records
INSERT INTO CUSTOMER VALUES(1,'A'),VALUES(2,'B'),VALUES(3,'C')
Step 3 : Execute below query, you will get all the records
SELECT * FROM CUSTOMER
Step 4 : Now, add new computed column with case statement in definition as below
ALTER TABLE CUSTOMER
ADD ADDRESS AS CAST (CAST WHEN Customer_ID=1 THEN 2 ELSE 3 END AS VARCHAR(100))
Step 5 : Execute step 3 query again, you will get new column named Address with values as per condition.

Author : Feel free to ask if 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.

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.

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.