Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

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…

Read more

0 comments, 3,243 reads

Posted in Vivek's SQL Notes on 29 December 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…

Read more

1 comments, 215 reads

Posted in Vivek's SQL Notes on 28 December 2013

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

Read more

9 comments, 173 reads

Posted in Vivek's SQL Notes on 28 December 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…

Read more

14 comments, 217 reads

Posted in Vivek's SQL Notes on 25 December 2013

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…

Read more

0 comments, 1,610 reads

Posted in Vivek's SQL Notes on 25 December 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…

Read more

3 comments, 6,474 reads

Posted in Vivek's SQL Notes on 25 November 2013

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…

Read more

1 comments, 567 reads

Posted in Vivek's SQL Notes on 25 November 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…

Read more

9 comments, 873 reads

Posted in Vivek's SQL Notes on 31 December 2012

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…

Read more

1 comments, 588 reads

Posted in Vivek's SQL Notes on 31 December 2012

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…

Read more

0 comments, 946 reads

Posted in Vivek's SQL Notes on 31 December 2012

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…

Read more

3 comments, 545 reads

Posted in Vivek's SQL Notes on 31 December 2012

Welcome 2013


Happy New Year 2013




May this new year bring lots of Happiness in all of your life. Let  welcome 2013 together with lots of Hopes and happiness.



Read more

0 comments, 470 reads

Posted in Vivek's SQL Notes on 31 December 2012

Happy Diwali



Wish you all a very happy and blessed festive season. May this festival of lights brings more brightness in your lives in every way. Happy Diwali :)
Play safe...!

Read more

0 comments, 330 reads

Posted in Vivek's SQL Notes on 13 November 2012

Boyce - Codd Normal Form (BCNF)

Boyce - Codd Normal Form (BCNF)  :- A normal form is said to be a Boyce - Codd Normal Form if it is in 3NF and there is not a possibility of a key attribute is determined by a Non Key attribute.

For example , suppose there are 5 columns says A,B,C,D,E…

Read more

8 comments, 1,185 reads

Posted in Vivek's SQL Notes on 19 August 2012

Isolation levels in SQL Server



Isolation levels :- Isolation level is required to isolate a resource and protect it from other transactions. This is achieved with the help of locks but what locks are needed and how they can be established is decided on the isolation level set on the database level. If low level…

Read more

18 comments, 4,260 reads

Posted in Vivek's SQL Notes on 19 August 2012

Merge Command In Sql Server



Merge Command:- Merge command is a new feature introduced in the SQL Server 2008. It can perform Update, Insert and delete operations at a single statement which means all the data is processed and read only once instead of three times(In case of Insertion, deletion and update statements.) It has…

Read more

3 comments, 994 reads

Posted in Vivek's SQL Notes on 17 April 2012

Adding , Deleting and Updating a Column in a table

Many times we need to alter the table definition by adding , deleting or updating a column in the table. In this article, I am trying to explain the following :-


1. How to add a column
2. How to update a column
3. How to drop a column


Suppose…

Read more

2 comments, 858 reads

Posted in Vivek's SQL Notes on 15 April 2012

Derived Table In Sql Server

Derived tables:- Derived tables are the tables which are created on the fly with the help of the Select statement. It is different from the temporary table in the way that  in case of temporary table, first we have to create a temporary table,  insert the data into the table,…

Read more

0 comments, 5,379 reads

Posted in Vivek's SQL Notes on 15 April 2012

Pivot and Unpivot table in SQL SERVER


Pivot Table:- Pivot tables are used to summarize and display the data, specially in case of report data by means of aggregating the values. Pivot table can be used if we want to display the unique values of the column of a table as the columns of another table. It turns the…

Read more

7 comments, 22,319 reads

Posted in Vivek's SQL Notes on 14 April 2012

SQL Server- Output clause

Output Clause:- The SQL SERVER 2005 gives us an Output Clause which gives us the information about each row affected by the Insert, Update , Delete and Merge statement. It is more useful than @@scope_Identity and @@Identity column since these global variables gives us the information about the last inserted identity column value, but…

Read more

2 comments, 1,143 reads

Posted in Vivek's SQL Notes on 22 January 2012

Newer posts

Older posts