Tuesday, December 31, 2013

Happy New Year 2014




                              HAPPY NEW YEAR 2014




Wishing all of the readers of this blog a very happy New Year. Thanks for supporting me and my blog last year 2013. I hope this support will continue this year too. May God bless all of you with lots of lots of success. 

Sunday, December 29, 2013

Deleting Duplicate rows using CTE

Many times we caught in a situation where we have a table where there is no primary or unique column defined in it and rows with duplicate data inserted in the table. In this example I will tries to show how to delete the duplicate rows using CTE.

Suppose we have a table name "tbl_dublicatedata" whose table creation script is given below:-

Create table tbl_dublicatedata (name nvarchar(100), age int, salary int)

Now we insert duplicate data into this table. Insert script for these rows are given below:-

Insert  into tbl_dublicatedata
select 'Vivek', 28, 800000
union all
select 'Avinash', 29, 600000
union all
select 'Vivek', 28, 800000

If we want to see the data into the table , we can run the following query

select * from  tbl_dublicatedata 

This query will return the following result




With the help of the CTE we can remove these duplicate records. 

With cte_duplicate (name, age, salary, rownumber)
as (
select name,age,salary, row_number()over(partition by name, age , salary order by name, age , salary)as rank from tbl_dublicatedata
)
delete from cte_duplicate where rownumber<>1  

If we again see the data in the table tbl_dublicatedata using the same query which we used earlier we will get the following result:-

select * from  tbl_dublicatedata 





From the result we can see that the duplicate rows are deleted and there is no duplicate data in the table.

DMCA.com

Saturday, December 28, 2013

Answer -Question of the Week - 25/12/2013- Question 1


Question:- Suppose we have a table says tbl_students whose structure is given below:-

Create table tbl_students(Studentid int identity(1,1) , Studentname nvarchar(150))

Suppose it contains the following data:-

Studentid  Studentname
1               Vivek Johari
2               Chandra Singh
4               Avinash Dubey
6               Akhil
7               Sunny Rana
10             Saurabh

As one can see from the data that some rows are deleted from the table as some studentids are missing.

So write a query which gives the studentid which are deleted from this table


Answer:-


 Thanks all for answering that question. I really appreciate the effort of all the people who tried and gives the response.We get multiple answers for this question in which correct answer is given below:-

Correct answer in case the first coulmn is an identity column

Answer given by Arjan  Dingemans


WITH Numbers AS (
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < IDENT_CURRENT('tbl_students')
)
SELECT Number
FROM Numbers N
LEFT JOIN tbl_students S ON N.Number = S.StudentId
WHERE S.StudentId IS NULL


Answer given By Chandra Prakash Singh

select number from master..spt_values s
left join tbl_students on s.number = tbl_students.studentid
where s.type='P'
and s.number <= IDENT_CURRENT('tbl_students')
and tbl_students.studentid is null and number > 0


Correct answer in case the first coulmn is not an identity column 

Answer given By Avinash Dubey

WITH DeletedRows (missnum, maxid)
AS

SELECT 1 AS missnum, (select max(Studentid) from tbl_Students) UNION ALL SELECT missnum + 1, maxid FROM DeletedRows 
WHERE missnum < maxid
)
SELECT missnum FROM DeletedRows
LEFT OUTER JOIN tbl_Students e on e.Studentid = DeletedRows.missnum
WHERE e.Studentid is NULL OPTION (MAXRECURSION 0)


Answer given by Aviral Lamba

select number from master..spt_values s
left join tbl_students on s.number = tbl_students.studentid
where s.type='P'
and s.number < (select MAX(studentid) from tbl_students)
and tbl_students.studentid is null and number > 0 


Also I appreciate the effort done by chetan Deshpande in attempting the question

DMCA.com

Question of the Week - 28/12/2013 - Question 2

Question:- Suppose we have a table named tbl_charactername have only one column say "charactername".
It contains 10 rows. First 5 rows contains "Vivek Johari" and the next 5 rows contains "Abhinav Golwalkar"

tbl_charactername
---------------------
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar

Now we need a SQL Query which can return the result in the form

Desired Result:-

Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar

Query should work even if the table has 30 rows with first 15 contains "vivek johari" and next 15 contains "Abhinav Golwalkar" or 40 rows with first 20 contains "vivek johari" and next 20 contains "Abhinav Golwalkar" etc. But output should contains "Vivek Johari" and "Abhinav Golwalkar" in alternate rows.

Keep Answering.......:-)

Wednesday, December 25, 2013

Question of the Week -25/12/2013- Question1

Suppose we have a table says tbl_students whose structure is given below:-

Create table tbl_students(Studentid int identity(1,1) , Studentname nvarchar(150))

Suppose it contains the following data:-

Studentid   Studentname 
1               Vivek Johari
2              Chandra Singh
4              Avinash Dubey
6              Akhil 
7              Sunny Rana
10            Saurabh

As one can see from the data that some rows are deleted from the table as some studentids are missing.

So write a query which gives the studentid which are deleted from this table.
DMCA.com

Ranking Functions in SQL Server

Ranking Function:- Sometimes we need to provide a Row number to the rows in a table or within a partition. For example, suppose we want to give rank to sales man according to their sales amount in a particular month/year. For this purpose, SQL server provides us Ranking functions. Ranking functions are used to provide a Rank to a row in a given partition.

There are 4 Ranking functions provided by SQL server

1) ROW_NUMBER()

2) RANK ()

3) DENSE_RANK()

4) NTILE()

In this article, I am going to explain these functions with the help of the table "tbl_companysales". Queries for creation and insertion of data into this table is given below:-

Create Table tbl_companysales(id int identity(1,1), employeeid int,  employeename nvarchar(150), month_name nvarchar(100), year int,salesamount int)

Insert Into tbl_companysales (employeeid,employeename,month_name,year,salesamount)
Select 1, 'Vivek' ,'April', 2012, 200000
union all
Select 2, 'Ravi', 'April', 2012, 150000
union all
Select 3, 'Uma', 'April', 2012, 150000
union all
Select 4, 'Raman','April', 2012, 140000
union all
Select 1, 'Vivek', 'October', 2012, 300000
union all
Select 2, 'Ravi', 'October', 2012, 200000
union all
Select 3, 'Uma', 'October', 2012, 400000
union all
Select 4, 'Raman','October', 2012,300000
union all
Select 5, 'Monika', 'October', 2012, 300000
union all
Select  1, 'Vivek' , 'November', 2012, 300000
union all
Select 2, 'Ravi', 'November', 2012,200000
union all
Select 3, 'Uma', 'November', 2012, 200000
union all
Select 4, 'Raman', 'November', 2012, 200000
union all
Select  5, 'Monilka', 'November', 2012, 400000

We can use the following query to know the rows contains by this table

select  *   from tbl_companysales


Sample data for Ranking function Article


ROW_NUMBER():- This function gives the sequential number of a row within a partition of a resultset, starting from 1.

Syntax :-

   ROW_NUMBER ( )     OVER ( [ ] )

Example:-  If we want to gives rank to employee within a month, we can use the following query

Query:-

SELECT employeename,month_name ,ROW_NUMBER () OVER (partition by month_name ORDER BY month_name) AS "Row Number"  ,salesamount FROM tbl_companysales

Result:-

This query will gives the following result:-




If we see the result, we found that the function ROW_NUMBER() gives every row a number within a partition which is in case is month.
RANK():- This function return the rank of a row within a partition of a result set

Syntax:-

RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )

Example:-Suppose if we want to get the rank of employee according to their sales amount within a month, we can use the following query

Query:-

SELECT employeename,month_name ,RANK() OVER (partition by month_name ORDER BY salesamount) AS Rank ,salesamount FROM tbl_companysales

Result:-
This query will gives the following result:-



 If we see that result we found that RANK() function gives each rows a rank based on the sales amount value within a partition (in this month). Also we see that if more than one rows have the same sales amount then it gives same rank to all the rows containing the same sales amount value within a partition and for the next row it gives the next rank with a gap. For example in the above result, Vivek get the rank 4th in month April since it comes on 4th number instead of 3 and two rows in April with employees Ravi and Uma get the same rank.


DENSE_RANK():- This function returns the rank of a row within a partition of a result set similarly to Rank function but it return the rank of a row without any gap.

Syntax:-

DENSE_RANK ( )    OVER ( [ ] < order_by_clause > )

Example:-Suppose if we want to get the dense rank of employee according to their sales amount within a month, we can use the following query

Query:-

SELECT employeename,month_name ,DENSE_RANK() OVER (partition by month_name ORDER BY salesamount) AS "Dense Rank" ,salesamount FROM tbl_companysales

Result:-

This query will gives the following result:-





If we see the result , we found that Dense_Rank() function gives Rank without any gap as it gives 3rd rank to employee Vivek in month April.


NTILE():- This function distributes the rows in an ordered partition into a specified number of groups. For each row, NTILE() will returns the number of the group to which the row belongs.

Syntax:-

NTILE (integer_expression)    OVER ( [ ] < order_by_clause > )

Example:- 
  In the following example, we divides the rows into 4 groups. since the number of rows are not fully divisible by 4 so it creates first two groups of 4 rows and next 2 groups of 3 rows each 

Query:-

SELECT employeename,month_name ,NTILE(4) OVER (ORDER BY month_name) AS Quartile ,salesamount FROM tbl_companysales

Result:-

This query will gives the following result:-




If we see the result, we found that the Ntile() function divides the rows into 4 groups. Employees in the first group are given the rank 1 since they belong to first group. similarly are employees are given the rank according to the number of the group to which they belong. 

Summary :- In this article , I tried to explain the concepts of Ranking functions in SQL Server with the help of examples. We mostly used the functions Row_number() and Rank(). This function is also used in deleting the duplicate records in a table. I am waiting for your valuable comments (feedback) on this article. If you have any query regarding this, you can send me a mail at askvivekjohari@gmail.com. 

Keep reading and keep visiting my blog :-)



DMCA.com

Monday, November 25, 2013

Stuff VS Replace function in SQL Server



Stuff function: - This function is used to replace string from the given start position, passed as 2nd argument with string passed as last argument. In Stuff function, 3rd argument defines the number of characters which are going to be replaced.

 
Syntax:-

STUFF ( character_expression , start , length , replaceWith_expression )

For example:-

  Select Stuff ('Software', 3, 3, 'abc')

This query will return the string "Soabcare". In this example, Stuff function replaces the string "Software" onwards the 3rd position('ftw') with 'abc'.


Replace Function :- Replace function is used to replace all occurrence of a specified with the string passed as last argument.

Syntax :-

REPLACE ( string_expression , string_pattern , string_replacement )

For example:-

Select Replace ('Abcabcabc', 'bc', 'xy')


This query will return the string Axyaxyaxy. In this example, Replace function replaces the occurrence of each 'bc' string with 'xy'.
DMCA.com

Case Statement/ Expression in SQL Server


Case Statement/ Expression in SQL server 
 
Sometimes we need to get one result output from multiple possible outcomes based on the evaluation of some expression. This expression can be a simple case expression which compares an expression with a set of Expression/Values to determine the result or it can be a search case expression where it evaluates a list of Boolean expressions to determine the result.

We can use the case statement in Select statement, Update statement, in Where clause, In clause, Having Clause, in  procedures and in User Defined Functions

Simple Case expression:-It compares an expression with a set of Expression/Values to determine the result.

Syntax:-

CASE input_expression
     WHEN when input_expression 1 THEN result_expression1
     WHEN when input_expression 2 THEN result_expression2 [ ...n ]
     ELSE else_result_expression 
END


Example :-Suppose we have a stored procedure 'usp_getcountryname' which takes country code as input and returns the country name as output.

     
Create Procedure usp_getcountryname
(@I_countrycode nvarchar(10))

As
Begin

 select case (@I_countrycode)
                when 'IN ' then 'INDIA'
                when 'HK ' then 'HONG KONG'
                when 'CN ' then 'CHINA'
                when 'BR ' then 'BRAZIL'
                when 'AU ' then 'AUSTRALIA'
                when 'AF ' then 'AFGHANISTAN'
                else 'No country found'   end as Countryname
               
End


 when we execute this stored procedure using the command
given below, it gives the result  'INDIA' . In the above example, we compare the input value with multiple values and based upon the comparison , it gives the result.

 exec usp_getcountryname 'IN'

Search case Expression:- In this case, it evaluates a list of Boolean expressions to determine the result.

Syntax:-
CASE
WHEN Boolean_expression1 THEN expression1
WHEN Boolean_expression2 THEN expression2
WHEN Boolean_expression2 THEN expression2 [...n]
ELSE expression
END


Example:- Suppose we have a table called tbl_Employee which contains the employee information regarding  their name, gender, age and salary.

Table creation script is given below:-

create table tbl_Employee(id int identity(1,1),Empname nvarchar(100),Gender nvarchar(5),Age int,Salary int)


Use the following Insert script to insert data in the table mentioned above:- 

insert into tbl_Employee(Empname,Gender,Age,Salary)

select 'Ram','M',20,100000

union all

select 'Neha','F', 25,200000

union all

select 'Praveen','M',25,200000

union all

select 'Garima','F', 20, 100000


So, the initial data is given below:-


 Select * from tbl_Employee           







Suppose we have a requirement to update the salary of employees based on the following given conditions :-
 

1) If female and age less than 25 increase salary by 4 time
2) If female and age is greater or equal to 25 then increase salary by 3 times
3) If male and age is less than 25 then increase salary by 4.5 times
4) If male and age is greater or equal to 25 then increase salary by 2.5

In that case we can use the Search case expression to update employee salary



After the execution of the above update command, the data in the table becomes








Summary:- In this article , I tried to explain the Case expresion concept in SQL server with the help of examples.

                                

DMCA.com

Tuesday, January 1, 2013

Replication in SQL Server


Replication  is a way of distribution of database and their objects from one master database to one or more recipient databases which can be on the same server or on the remote server.It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, we can distribute data to different locations and to remote users over local and wide area networks.It is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users so that they can make changes to their local copy of the database.

Replication Components

SQL Server replication consists of three components: The Publisher, the Distributor and the Subscriber. These components act on articles that are defined within publications and subscriptions.

Article

For each SQL Server object that should be replicated, a replication article needs to be defined. Each article corresponds to a single SQL Server object, or a subset of an object. The objects that are replicated most often are tables, views and stored procedures. The properties of an article determine whether the article contains the entire object, or if a filtered subset of the objects makes up the replicated article.

Publication

A group of articles that logically belong together can be combined into a publication. The publication has options defined that apply to all the articles in that publication. The main option defined by a publication is the type of replication that is to be used.

Publisher

The SQL Server instance that makes a publication available for replication is called the publisher.
The publisher monitors all articles for changes, and makes information about those changes available to the distributor.

Distributor

The distributor is the SQL Server instance keeps track of all subscribers and all published changes and makes sure that each subscriber gets notified of each change. Most of the changes are tracked in a distribution database. The distributor can be a separate SQL Server instance, but often the distribution service runs on the same machine as the publisher.

Subscriber

The subscriber is the SQL Server instance that receives all the published information through subscriptions. 

Subscription

A subscription is the counterpart of the publication. A subscription defines which server (subscriber) is to receive the updates published in a publication. Each subscription creates a link between one publication and one subscriber. There are two types of subscriptions: push subscriptions and pull subscriptions. In a push subscription, the distributor directly updates the data in the subscriber database. In a pull subscription, the subscriber asks the distributor regularly if any new changes are available, and then updates the data itself.

Types of replication:-

  1.          Snapshot Replication
  2.          Transactional Replication
  3.          Merge Replication

Snapshot Replication:-


Snapshot replication is also known as static replication. Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time. Every time snapshot replication is run, everything is recopied from scratch, so it has high bandwidth and storage requirements. Therefore this replication is advisable only when the size of the database is small.

Transactional Replication:-


Transactional replication works on a transaction basis. In Transactional replication only the committed changes are logged into the subscribers. Transactional replication allows for close to real time synchronization and leaves only a small footprint on the publisher. It is the best way of replication to work one way only.

Merge Replication:-


Merge replication was designed to allow the changes to the data to be made on the publisher as well as the subscriber side. In Merge Transaction, a subscriber might not be connected during the day. That subscriber would synchronize after reconnecting in the evening. If a row gets updated in two different places at the same time, a conflict occurs. Merge replication have many built in options to resolve those conflicts.


DMCA.com

Log Shipping in SQL Server

Log Shipping:- Log Shipping is a process of keeping the latest copy of the database of the primary database server on the secondary database server with the help of transaction logs backup. It is done on a regular basis to minimize the loss of the database in case of database fail over. It is a continuous process in which the backup of the transaction logs has been taken on the regular basics and then they are  transported to the secondary server. In the secondary server, the transactions backups are restored to make the database on the secondary database server as the exact replica of the primary database or near to the current primary database.

This process can have three components:-

Primary database Server:- This server contains the primary database .The backup of the transaction logs of the Primary database is taken on this server and send to the Secondary database server.

Secondary database Server:- This server contains the secondary database . There can be one or more than one secondary database server. Here backup of the primary database server transactions log are applied to the secondary database server databases individuality so that they have database which is close to the Primary server database.

Monitor Server:- This optional server, monitor the log shipping process by recording the history and the status of the the backups of the transaction logs. It also monitor the restore operations on the secondary server. It can also raise alarm if the process is not completed as per scheduled.
DMCA.com

Rebuild And Reorganization of Indexes

Rebuild and  Reorganization of Indexes:- SQL Server has the ability of maintaining the indexes whenever we makes changes (update, Insert, Delete) in the tables. Over a period of time, the may causes the fragmentation on the table in which  the logical ordering based on the key value pairs does not match with the physical ordering inside the data files. This causes the degradation of the performance of the SQL Query.To solve this problem of fragmentation, we use rebuilding or reorganization of the indexes.

In case of Rebuilding, it drop the particular index and again recreate it.It removes fragmentation, reclaims the disk space by compacting the pages based on the specified or existing fill factor setting, and again reorders the index rows in those contiguous pages. We can rebuild all the indexes of the table within a single transaction by specifying the ALL with it.


ALTER INDEX [Indexname] ON [tablename] REBUILD

ALTER INDEX ALL ON [tablename] REBUILD

In case of Reorganization, it defragments the leaf level nodes of indexes by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes.  It uses minimal system resources and also compact the index pages

ALTER INDEX [Indexname] ON [tablename] REORGANIZE

ALTER INDEX ALL ON [tablename] REORGANIZE

Rebuild should be used when the fragmentation index is greater than 30% and reorganization option should be used when the fragmentation index is between 5% and 30%. Rebuilding of an Index can be done online or offline. But to achieve the availability of the index, rebuilding should be done online. Reorganization can be done online.


Related Article
Fragmentation
DMCA.com

Fragmentation in SQL Server

Fragmentation:- Fragmentation can be defined as condition where data is stored in a non continuous manner. In can be defined into two types

1. Internal Fragmentation
2. External Fragmentation

Internal Fragmentation:- In this fragmentation, there exists a space between the different records within a page. This is caused due to the Insert, delete or Update process and due to this Index takes more space than it needs to and it result in more read operation during scanning. It can also be caused due to the low value of fill factor of the page which determine how much % of the page should be used for storing the records. 


External Fragmentation:- In this fragmentation, the extents of the table is not physically stored continuously on the disk which causes the jump from one extent to another extent which takes longer time. 

Both the fragmentation can be resolved by Rebuilding or Reorganization of the indexes of the tables. 

For Rebuilding or Reorganization of the indexes , please refers the below link:

http://vivekjohari.blogspot.in/2013/01/rebuild-and-reorganization-of-indexes.html
DMCA.com