7.27.2012

SQL Server Difference FAQs-4


1.Difference between Correlated subquery and Nested subquery
S.No Correlated subquery Nested subquery
1
Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
2 Correlated subquery follows down to top approach i.e., main query is executed first(even though parenthesis are present) and then child query.

We can also say: in a Correlated subquery,Inner query condition is used in the outer query
Nested subquery follows top-down approach i.e., child query is executed first and then parent .

We can also say:In a subquery
Outer query condition is used in the the inner query.
4
Example:

select e1.empname, e1.basicsal, e1.deptno from emp e1

where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)
Example:

select empname, basicsal, deptno from emp

where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

2.Difference between Weak Entity Set and Strong Entity Set

S.No Weak Entity Set Strong Entity Set
1 An entity set which does not possess sufficient attributes to form a primary key is known as a weak entity set. An entity set which does have a primary key is called a strong entity set.
2 Member of a weak entity set is a subordinate entity. Member of a strong entity set is a dominant entity.
3 Example:

Specific Person,Company,Event,Plant
Example:

Set of all Persons,Companies,Trees,Holidays

3.Difference between char and varchar data types in Sql Server
S.No Char Varchar
1
Fixed length memory storage
Variable length memory storage(Changeable)
2
CHAR takes up 1 byte per character
VARCHAR takes up 1 byte per character, + 2 bytes to hold length information
3
Use Char when the data entries in a column are expected to be the same size like phone number
Use Varchar when the data entries in a column are expected to vary considerably in size like address
4
Ex:
Declare test Char(100);
test="Test" -
Then "test" occupies 100 bytes first four bytes with values and rest with blank data
Ex:
Declare test VarChar(100);
test="Test" -
Then "test" occupies only 4+2=6 bytes. first four bytes for value and other two bytes for variable length information.


4.Difference between Sql Server 2005 and Sql Server 2008
S.No Sql Server 2005 Sql Server 2008
1
XML datatype is introduced.
XML datatype is used.
2
Cannot encrypt the entire database.
Can encrypt the entire database introduced in 2008.
3
Datetime is used for both date and time.
Date and time are seperately used for date and time
4
No table datatype is included.
Table datatype introduced.
5
SSIS is started using.
SSIS avails in this version.
6
CMS is not available.
Central Management Server(CMS) is Introduced.
7
PBM is not available
Policy based management(PBM) server is Introduced.

1 comment: