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.

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, 541 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, 811 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, 494 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, 433 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, 288 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, 944 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, 3,024 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, 901 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, 784 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, 4,148 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, 20,045 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,042 reads

Posted in Vivek's SQL Notes on 22 January 2012

CTE command In Sql Server


Common table expression (CTE):- Common table expression or CTE can be described as the temporary, named record set return by the execution of the query (insert, delete, update, select or update view statement). It is not stored as an object in the database and it last only till the execution…

Read more

8 comments, 2,258 reads

Posted in Vivek's SQL Notes on 17 January 2012

SQL Server-Table Variable

Table variable:- SQL Server provides an variable known as table variable which is used to store data in a similar way as we store data in physical tables but with some limitations. Like other SQL variable, it is also declare with the help of the Declare keyword with @ prefix. The Syntax…

Read more

4 comments, 2,299 reads

Posted in Vivek's SQL Notes on 7 January 2012

SQL Server - Local temporary table


Local temporary table:- Local temporary table is created in the tempdb and it is visible to the current user's session only. It remains exists till the current user session is connected. Once the user connection is disconnected it gets destroyed. Since the local temporary table is created in the tempdb,…

Read more

0 comments, 2,817 reads

Posted in Vivek's SQL Notes on 7 January 2012

SQL Server - Global temporary tables


Global temporary table:- Global temporary table is created in the tempdb  and it is visible to all other sessions as well. Global temporary tables are only destroyed when the current user disconnected and all the sessions referring to it closed. It means no user sessions refers it. Since the global temporary table is…

Read more

8 comments, 9,806 reads

Posted in Vivek's SQL Notes on 7 January 2012

How to remane a Table in Sql Server

To rename a column in a SQL Table following command can be reused:
SP_RENAME  'old table name', 'New table name'


For example, if we want to rename the table from employee to employeemaster, following command can be used.


SP_RENAME  'employee', 'employeemaster'


This command will rename the table from employee to employeemaster.

Read more

0 comments, 852 reads

Posted in Vivek's SQL Notes on 2 January 2012

How to change the database collation

To change the collation of an database following commands can be used:




ALTER DATABASE [database name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE  [database name]  COLLATE Newcollation

ALTER DATABASE  [database name]  SET MULTI_USER


For example , suppose if the name of your database is learningdb and…

Read more

0 comments, 966 reads

Posted in Vivek's SQL Notes on 2 January 2012

SQL Triggers - An Introduction

Introduction
Triggers can be defined as the database objects which perform some action for automatic execution whenever users try to do execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are bound to specific tables. As per MSDN, triggers can be defined as the special…

Read more

2 comments, 3,905 reads

Posted in Vivek's SQL Notes on 16 November 2011

SQL Triggers - An Introduction

Introduction
Triggers can be defined as the database objects which perform some action for automatic execution whenever users try to do execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are bound to specific tables. As per MSDN, triggers can be defined as the special…

Read more

21 comments, 391 reads

Posted in Vivek's SQL Notes on 10 November 2011

Newer posts

Older posts