Monday, September 6, 2010

Renaming a column in Sql Server

 We often need to change the name of a column of a table to a new name. We  can do this with the help of the Exec Sp_rename command.


 The Syntax of the Sp_rename is given below:-

 Exec sp_rename 'TableName.[OldColumnName]', '[NewColumnName]', 'Column'


 For example, suppose we have a table called Employee who has the following structure:-



 CREATE TABLE Employee (Emplyeeid int identity(1,1),
                                      Empnumber nvarchar(10),
                                      Firstname nvarchar(150), 
                                      Lastname nvarchar(150),
                                      Age int, 
                                      Phoneno nvarchar(15), 
                                      Address nvarchar(200),
                                      Empdate datetime)


Now suppose we insert the following data into the table Employee



Insert into Employee values('VIV123', 'Vivek', 'Johari', 27, '9211134', 'Delhi', getdate())


If we execute the Select command into the table Employee, we get the following result:-










Suppose now  we want to change the column name Firstname to Fname the we use the given query:-


Exec Sp_rename 'Employee.Firstname', 'Fname', 'column'



If we again execute the Select command into the table Employee, we get the following result:-









Here the column name "Firstname" changed to the column "Fnamn".


Friday, September 3, 2010

Thanks for supporting this blog and make it successful

Hi friends
Thank you very much for supporting this blog. It really give me lots of encouragement to share my knowledge with my friends. Due to some other high priorities, I was not able to publish any articles on this blog from last few months. But now I am busy with writing many articles through which I can share my experience with you.
I am very soon going to publish many articles on this blog which includes


1) Sql concept (Triggers, Cursor, Merge, CTE, Derived tables, linked server etc.)


2) Sql Server Business Intelligence ( SSIS, SSAS, SSRS)


3) Oracle concepts


4) Many more interview questions on SQL Server and SSIS, SSAS, SSRS


5) Interview Questions on PL/SQL


6) Unix Concepts


So keep visiting my blog regularly. Also I am trying to modify my existing articles by adding more informations in them.


Also keep giving me your valuable feedback so that I can improve my articles.


Best Regards
Vivek

Monday, February 1, 2010

Interview Questions on Sql Server -Part 3


1. What are the Sql Joins?
Sql Joins are the way to get data from many tables based on some logical conditions. The different  types of Joins in Sql Server are

1) Inner join or Equi join
2) Self Join (it can be considered as the part of Inner join)
3) Outer Join
4) Cross join

 Read more about Sql Joins

2.What are the Inner joins?

Inner joins:- This type of join is also known as the Equi join. This join returns all the rows from both tables where there is a match. This type of join can be used in the situation where we need to select only those rows which have values common in the columns which are specified in the ON clause.

Read more about Inner Joins 

3. What are the Self Joins?

Self Joins:-Sometime we need to join a table to itself. This type of join is called Self join. In this Join, we need to open two copies of a same table in the memory. Since the table name is the same for both instances, we use the table aliases to make identical copies of the same table to be open in different memory locations.

Read more about Self Joins 

4. What are the Outer Joins?

Outer Joins:-This type of join is needed when we need to select all the rows from the table on the left (or right or both) regardless of whether the other table has common values or not and it usually enter null values for the data which is missing.

The Outer join can be of three types
  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join  

Read more about Outer Joins 

5.What are the Cross Joins?

Cross Joins:-This join combines all the rows from the left table with every row from the right table. This type of join is needed when we need to select all the possible combinations of rows and columns from both the tables. This type of join is generally not preferred as it takes lot of time and gives a huge result that is not often useful.

Read more about Cross Joins

6. What are the Indexes in Sql server?

Indexes-Indexing  is way to sort and search records in the table. It will improve the speed of locating and retrieval of records from the table.It can be compared with the index which we use in the book to search a particular record.

7. What are the different types of Indexes in the Sql server?
In Sql Server there are two types of Index
1) Clustered Index
2) Non Clustered Index


8. What is the Clustered Index?
Clustered Index:- Clustered index physically stored the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined.


9. What is the  Non Clustered Index?
Non Clustered Index:- In case of Non clustered index it create a sperate list of key values (or created a table of pointers) which points towards the location of the datain the data pages.


10. What are the difference between Clustered and Non Clustered Indexes?
Clustered Index:- Clustered index physically stored the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined.

In a table only 1clustered index is possible.

In a clustered Index, the leaf node contains the actual data.


Non Clustered Index:- In case of Non clustered index it create a sperate list of key values (or created a table of pointers) which points towards the location of the datain the data pages.

In a table 249 non clustered index is possible.

In a non clustered Index, the leaf node contains the pointer to the data rows of the table.


11. What are the different ways of creating a table in Sql Server?
1)Creation of a table with the help of a create statement
2)Creation of table with the help of another table.  




Related Articles


 

Sunday, January 31, 2010

Interview Questions On Sql Server -Part 2

1. What is the difference between the Union and Union All?

1) Union is used to select distinct data from the tables but Union All allows duplicate rows to be selected from the tables.

2) The second difference between Union and Union All can be given on the basic of their uses. Union All can be used to insert multiple rows in the table in a single query whereas Union can not be used to insert multiple rows in the table.

3)Also since Union does not allowed the duplication of the data, therefore it has to first select the whole data and then use the Distinct function to eliminate the duplicate data.It will increase the cost of the execution plan for the query since it have to use the two functions whereas Union All allow the duplication of the data so it only needs the select statement to show the data. So it will cost little as compare to the Union.


2. what is the stored procedures?
Stored Procedure:- Stored Procedure In Sql server can be defined as the set of logically group of sql statement which are grouped to perform a specific task.There are many benefits of  using a stored procedure. The main benefit of  using a stored procedure is that it increases the performance of the database


3.What are the benefits of using stored procedures?
1) One of the main benefit of using the Stored procedure is that it  reduces the amount of information sent to the database server.It can become more important benefit when the bandwidth of the network is less.Since if we send the sql query (statement)which is executing in a loop to the server through network and the network get disconnected then the execution of the sql statement don't returns the expected results, if the sql query is not  used between Transaction statement and rollback statement is not used.

2) Compilation step is required only once when the stored procedure is created.Then after it  does not required recompilation before executing unless it is modified and reutilizes the same execution plan whereas the sql statements needs to be compiled every time whenever it is sent for execution even if we send the same sql statement every time.
3) It helps in re usability of the sql code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same sql statement every time. It helps in reduces the development time.

4) Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure. 
5) Sometime it is useful to use the database for storing the business logic in the form of stored procedure since it make it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.

4. What are the difference between Stored procedures and Functions?
1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.

2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement. 
3) Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function.

4) A stored procedure can return multiple parameters but a function can return only one value.

5) We can use join in the outcome from the functions but we can't use joins in the outcome from stored procedures.

6) Transaction management is not possible in functions but it is possible in Stored procedures.

7)Print function can not be called within the function but it can be called within the stored procedure.

Read more about difference between stored procedures and Functions 

5.What is the Group By Clause .......... Having Clause?
Group By:- Group By clauses is used to groups rows based on the distinct values of the specified columns.

The syntax of the Group by clause is:-
  Select column1, column2, column3, aggregate_function(expression ) 
           From TableName Where (condition) 
           Group By Column1, column2, column3
Having Clause :- Having clause is used in conjunction with the group clause by imposing a condition on the group by clause to further filter the records return by the group by clause.

Syntax for Having Clause:-
       Select column1, column2, column3, aggregate_function(expression ) 
       From TableName Where (condition) 
       Group By Column1, column2, column3
       having aggregate_function(expression )  operator value


6. What are the difference between Having and Where Clause?
1) Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.

2) We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas  we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.

 3) Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).

Read more on difference between Having and Where Clause 

7.How we can find out the duplicate values in a table?
We can find out the duplicate values in a table with the help of the Having clause in conjunction with the Group By Clause.

Read more about how to find out the Duplicate values in a table 

8.What are the Views?
Views:- View can be described as virtual table which derived its data from one or more than one table columns.It is stored in the database. It is used to implements the security mechanism in the Sql Server.

Read more about Views  

9. What are the difference between Query, Subquery, Nested Subquery and the Corelated Subquery?
Query: - Query can be defined as a way to inquire the data from the database. It is used to extract the data from one table or multiple tables depending upon the user needs.

Subquery:-If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.

Nested Subquery:-If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.




Correlated Subquery:-If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.


10.Suppose we have a table whose structure is given below
      Create table addcounter( counterid int identity(1,1))
Now how can we insert values in the table

By using the following Insert Statement
Insert into addcounter defalut values
 








 

  

Interview Questions on sql server -Part-1


1. What is the Normalization?
Normalization:-Normalization can be defined as the process of organization the data to reduce the redundant table data to the minimum. This process is carried out by dividing the database into two or more than two tables and defining relationship between them so that deletion, updation and insertion can be made to just one table and it can be propagated to other tables through defined relationships.


Read more about Normalization 


2. What are the normal forms and what are the different normal forms of Normalization?


Normal Forms: - The normal form can be refers to the highest normal form condition that it meets and hence indicates the degree to which it has been modified. The normal forms are:-

  1. First Normal Form (INF)
  2. Second Normal Form (2NF)
  3. Third Normal Form (3NF)
  4. Boyce- Codd Normal Form
  5. Forth Normal Form (4NF)   
  6. Fifth Normal Form(5NF)
Read more about Normal forms




3. What is the Denormalization?
Denormalization:- Denormalization can be defined as the process of moving from higher normal form to a lower normal forms in order to speed up the database access.

4. What is the Primary key?
Primary Key: - Primary key is used to uniquely identify a row in a table. A table can have only one primary key. Primary keys don’t allow null values. The data help across the primary key column must be unique. It can be defined at the column level or at the table level.
Primary Key can be defined while creating a table with Create Table command or it can be added with the Alter table command.
Syntax for Primary Key


(ColumnName) (DataType) ((Size)) Primary Key 
Read more about Primary key 


5. What is the Unique key?
Unique key:-Unique key constraint enforces that the column can contains only unique values on which unique constraints is defined. This column can contain multiple null values but all not null values must be unique. Unique Constraint can be defined either at the column level or at the table level.


Read more about Unique key 

6. What is the difference between Primary key and Unique Key?

1)There can be only one Primary key possible in a table but there can be many unique keys possible in a table.
2)Primary key do not allow NULL values but a Unique key allow one NULL value.If we try to insert NULL value into the primary key, we get an error message.
3)When a Primary key is created, a clustered index is made by default but if an Unique key is created, a non-clustered index is created by default.
 
Read more about difference between Primary key and Unique key 


7. What is the Foreign Key?
Foreign Key:- Foreign key is used to prevent destroying the link between two tables. In foreign key, the table (Child table) in which the foreign key is defined points to the primary column of another table (Master table). A foreign key can points to the primary column of the same table. In this Foreign key relationship is said to be defined within the same table. Due to foreign key relationship, a value from the primary column of the master table can not be deleted until its all references from the child tables are deleted. Also a new value in the column in which primary key is defined can not be inserted until the value is already existed in the primary column of the master table.

Read more about Foreign Key 



8. What is the check constraints?
Check constraints:- Check constraints are the user defined business rules which can be applied to the database table column. For example a check constraint on the column “Salary” of the table Employee salary can be defined which state that no employee can have salary less than 5000.


Read more about Check constraints

9. What is the difference between Delete command and Truncate command?
 1) Delete command maintained the logs files of each deleted row but Truncate command do not maintain the logs files for each deleted row but maintains the record for deallocation of the datapages in the log files.The deallocation of the datafiles means that the data rows still exists in the data pages but the extends have marked as empty for reuse.

2) Truncate command is much faster than delete command.

3) You can use Where clause in case of Delete command to delete a particular row but in case of Truncate command you have to delete the data from all the row since Where clause is not work with Truncate command.

4) Triggers is fired in case of Delete command only and they are not fired when Truncate command is used.

5) Truncate command resets the Identity property to its initial value whereas Delete command do not resets the Identity property of the column.

6) Delete is a DML command and Truncate is a DDL command.


Read more about difference between Delete and Truncate Command

10. What is the Identity property?

Read about the Identity Property

Related articles


Interview questions on Sql server - Part 2 


Interview questions on Sql server - Part 3



Group by.....Having Clause

Group By:- Group By clauses is used to groups rows based on the distinct values of the specified columns.

The syntax of the Group by clause is:-
  Select column1, column2, column3, aggregate_function(expression ) 
           From TableName Where (condition) 
           Group By Column1, column2, column3

For Example, suppose we have a table named EmpInfo which contains the information about the Id of the employee and the Id of the project and it contains the following data

Table:- EmpInfo
EmployeeId  Projectid
--------------------------
1                          1
2                          2
3                          2
4                          2
5                          3

Now suppose we want to know about the number of employee belonging to each project then we can use the group by clause as given below:-

select Projectid , count(employeeid) as NumberOfEmployee  from  EmpInfo  Group by ProjectId

This query will give us the following result:-
ProjectId     NumberOfEmployee     
1                    1
2                    3
3                    1

Having Clause :- Having clause is used in conjunction with the group clause by imposing a condition on the group by clause to further filter the records return by the group by clause.

Syntax for Having Clause:-
       Select column1, column2, column3, aggregate_function(expression ) 
       From TableName Where (condition) 
       Group By Column1, column2, column3
       having aggregate_function(expression )  operator value

For example, suppose we want to know which project has more than 2 employee and the total no of their respective employees, we can use the following query

select Projectid , count(employeeid) as NumberOfEmployee  from  EmpInfo  Group by ProjectId having count(employeeid) >2


The query will give us the following result:-
 ProjectId  NumberOfEmployee
2                    3

In the above example, the Having clause further filter the result return by the Group By clause by imposing a condition that only those projectid will be shown who has more than 2 employee.



DMCA.com

Difference Between Having and Where Clause in Sql Server


Both Having Clause and Where clause is used to filter the data coming from the Select statement, but still there are some differences between them. These difference are given below:-

To show the difference between the Where Clause and the Having clause we will going to use the table EmployeeDeptInfo whose create query statement  is given below :-

Create table EmployeeDeptInfo ( Employeeid int, Departmentid int)

and it contains the following data 

Employeeid              Departmentid
1                                  1
2                                  2
3                                  2
4                                  3
3                                  2
2                                  2
5                                  4
2                                  2

1) Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.

For example, the sql query
         Update EmployeeDeptInfo Set departmentid =7 Where employeeid=4 
will work fine but the query
         Update EmployeeDeptInfo Set departmentid =7 Having employeeid=4
will not work

2) We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas  we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.

For example,  the sql query
select * from EmployeeDeptInfo where count(employeeid)>1 
will not  work but the query
Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1)
will work fine

3) Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).

For Example, in the below sql Query
 select employeeid, departmentid from EmployeeDeptInfo where employeeid=5

the where clause will  search the table EmployeeDeptInfo for the record whose employeeid is 5 and then show the output.

but in the below query,
Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having  employeeid=5

the result are  first grouped by the Group By Clause and then they become again filtered by the condition defined in the having clause. Sometime , like above both queries, we get the same result with the help of Where clause and having clause but which way is best is determined automatically by the optimizer and it select the best way of executing it.



   

DMCA.com

How to find duplicate values in a table

Sometimes duplicate values in tables can create a major problem when we do not make a primary key or a unique key on a table. In these cases we need to find out the duplicate records and need to delete them. We can use the Having Clause to find out that duplicate records.we show this with the help of an example.

Suppose we have a table named EmployeeDeptInfo which have the column Employeid and Departmentid. The query for creating this table is given below:-

Create table EmployeeDeptInfo ( Employeeid int, Departmentid int)

Employeeid contains the Id of the employee and Departmentid contains the Id of the department to which he/she is belonged. This table do not have any primary key defined on it and also it doesnt have any unique key constraint defined on any column.

Suppose the table contains the following data

Employeeid              Departmentid
1                                  1
2                                  2
3                                  2
4                                  3
3                                  2
2                                  2
5                                  4
2                                  2
In this table, entries for the employee having employeeid 2 & 3 are get repeated.In this example the data in the table is not much for the example point of view but in real time scenario it can be billions of rows and duplication of rows can be a very big concern. Therefore it is necessary to find out the duplicate rows in the table. We can use the Having Clause to find out the duplicate rows:-

Query for finding out the duplicate rows in the table:-

Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1 and count (Departmentid) >1)

This query will give us the following results:-
Employeeid         Departmentid
2                              2
3                              2

We can also find out that how many times a record is repeated in the table. For example, The following query gives us the result of how many times a given record is repeated

Select Employeeid, Departmentid, count (Employeeid) as NoOfRepeat from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1 and count (Departmentid) >1)


This query will give us the following results


Employeeid           Departmentid        NoOfRepeat
    2                                 2                           3
    3                                 2                           2

Here NoOfRepeat shows the number of times the records are duplicated in the table
Summary
This article shows that we can find the duplicate records with the help of the Group By and Having Clause.

Sql Server View


Views:- View can be described as virtual table which derived its data from one or more than one table columns.It is stored in the database. It is used to implements the security mechanism in the Sql Server. For example, suppose there is table called Employeeinfo whose structure is given below:-

Create table EmployeeInfo(EmpId int, EmpName nvarchar(200),EmpLogin nvarchar(20), Emppassword nvarchar(20) , EmploymentDate datetime )

And it contains the following data

EmpId        EmpName         EmpLogin       Emppassword     EmploymentDate 
1                 Vivek Johari         Vivek                  VikJoh           29/01/2006
2                Virender Singh      Virender              Virender         06/02/2007
3                Raman Thakur       Raman                Raman          14/05/2007 
4                Uma Dutt Sharma   Uma                   Uma             30/03/2008
5                Ravi Kumar Thakur  Ravi                    Ravi            30/06/2007

Now suppose that the Administrator do not want that the users have excess to the table EmployeeInfo which contains the some critical information (Emplogin, EmpPassword etc) of the Employees. So he can create a view which gives the empid, empname, employmentdate as the output and give the permission for the view to the user. In this way the administrator do not need to bother about giving the access permission for the table to the user.

The syntax for creating a View is given below:-

Create View Viewname As
 Select Column1, Column2  From Tablename
 Where (Condition)
Group by (Grouping Condition) having (having Condition)

For Example,
  Create View View_Employeeinfo As 
        Select EmpId, EmpName, employmentdate 
          From EmployeeInfo
Now user can use the view View_EmployeeInfo as a table to get the empid , empname and employmentdate information of the employees by using the giving query

Select  *   from  View_EmployeeInfo  where empid=2

It would gives the following result
    EmpId        EmpName             EmploymentDate
       2              Virender Singh         06/02/2007

We can also use Sql Joins in the Select statement in deriving the data for the view

For Example, Suppose there is a table named EmpProjInfo which contains the information about the employee project. the structure of the EmpProjInfois given below:-

Create table EmpProjInfo (EmpId int, Projectname nvarchar(200))
and it contains the following data
EmpId          Projectname
1                   Abcbank
2                   AtoZfinancialsol
3                   learningsystem
4                   ebooksystem
5                  AtoZfinancialsol

Now we can create a view Vw_EmployeeProj which gives the information about the Employees and its projects

Create view Vw_EmployeeProj As
  Select EmployeeInfo.EmpId, EmployeeInfo.EmpName, EmpProjInfo.Projectname from EmployeeInfo 
   inner join EmpProjInfo on EmployeeInfo.EmpId=EmpProjInfo.EmpId

Altering an View

  If we want to alter the view, then we can use the Alter View command to alter the view
For Example,
Alter view Vw_EmployeeProj As
  Select EmployeeInfo.EmpId, EmployeeInfo.EmpName, EmpProjInfo.Projectname from EmployeeInfo inner join EmpProjInfo on EmployeeInfo.EmpId=EmpProjInfo.EmpId where EmployeeInfo.EmpId in (2,3,4)

Getting Information about the Views:-
   We can use the System Procedure Sp_Helptext to get the definition about the views
For example,  we can use the sp_helptext command to get the information about the view Vw_EmployeeProj
          sp_helptext Vw_EmployeeProj

Renaming the view:-
    We can use the sp_rename system procedure to rename a view. The syntax of the sp_rename command is given below:-
  SP_Rename 'Old Name', 'New name'
 For example if we want to rename our view View_Employeeinfo to Vw_EmployeeInfo, we can write the sp_rename command as follows:-
sp_rename 'View_Employeeinfo', 'Vw_EmployeeInfo'

Dropping  a View
We can use the Drop command to drop a view 
  For example, to drop the view  Vw_EmployeeInfo, we can use the following statement
Drop view Vw_EmployeeInfo
 
Summary :-
  This article tells us that view can be described as a virtual table which can derived its data from one or more than one table.We can create a view with the help of the Create View command,can alter its definition with the help of Alter view command, get its definition with the help of Sp_helptext command, rename a view with the Sp_rename command and drop a view the Drop view command.



            
DMCA.com

Tuesday, January 26, 2010

Second Normal Form (2NF)

-->
Second Normal Form (2NF) :-A table is said to be in its Second Normal Form if it satisfied the following conditions:-
1) It satisfies the condition for the First Normal Form (1NF),
2) It do not includes any partial dependencies where a column is dependent only a part of a primary key.

For example suppose we have a table EmpProjDetail, which contains the employee details and its project details like projected, project name and durations in terms of days on which he/she is allocated to the project.

Table Name:-EmpProjDetail
Primary Key :- EmpId + projectid

EmpId
ProjectId
EmpName
ProjectName
Days
1
1
Vivek
Abc Bank
35
2
2
Sudeep
AbeBook
10

In this table, the primary key is composition of two columns EmpId and ProjectId. Now this table is in 1NF but it is not in the 2NF since the column EmpName can be depended on the column EmpId and the column ProjectName can be depended on the column ProjectId which violates the second condition for the 2NF.

We can break this table into three different tables to convert it into the 2NF. These tables are given below:-

Table name:- EmpDetails
Primary Key: - EmpId

EmpId
EmpName
1
Vivek
2
Sudeep

Table name:- ProjDetails
Primary Key: - ProjectId

ProjectId
ProjectName
1
Abc Bank
2
AbeBook



Table name:- EmpProjdetails
Primary Key: - empId + ProjectId

EmpId
ProjectId
Days
1
1
35
2
2
10

Now all the three tables are in 1NF and all the columns of these tables are fully depended on their respective primary keys.