SQL SERVER INTERVIEW QUESTIONS:

3) Constraints in Sql Server :
Ans : Constraint is a condition specified on column in table to maintain data integrity within the database.

NotNull- To Restrict the inserting Null to a column.
Default- To provide a default value for a column.
Unique -
Primary Key -
Check Constraint -
Super Key -
Candidate Key -
Alternate/Secondary key-
Foreign Key -
Composite Constraints -

4) What are the data types in sql server ?
Ans:
Exact numerics:
===========
bit - 1 (Byte)- boolean values
tinyint-1
smallint-2
int-4
bigint-8
numeric - 5 to 7
decimal - 5 to 7
smallmoney - 4
money - 8

Approximate numerics:
=====================

float - 4
real - 8

Date and time:
==============

date - 3 - 1/1/0001 to 31/12/9999
time - 5 bytes time only
smalldatetime - 1/1/1900 to 6/6/2079
datetime - 1/1/1753 to 31/12/9999
datetime2 - 1/1/0001 to 31/12/9999
datetimeoffset

Character strings:
==================

char
varchar
text

Unicode character strings:
==========================

nchar
nvarchar
ntext

Binary strings:
===============

binary
varbinary
image


Other data types:
=================

sql_variant
timestamp
uniqueidentifier
xml
cursor
table
hierarchyid
Geometry
Geography
Filestream


New data types in Sql Server 2008 are:

Datetime2
Datetimeoffset
hierarchyid
Geometry
Geography
Filestream

5)Whenever we want to change the name in the table freaquently then the datatype used is ?
Ans: Char

6) Which data type will be used to store the image and video files ?
Ans: For Image file ------ image data type
For Video file ------ binary/varbinary

7) Where’s the Boolean data type?
Ans: SQL Server doesn’t have a Boolean data type, at least not by that name.To store True/False, Yes/No, and On/Off values, use the bit data type.It accepts only three values: 0, 1, and NULL(NULL is supported by SQL Server 7.0 and later.).

8) What is the difference between smalldatetime and datetime?
Ans: Both smalldatetime and datetime store a combination date and time value,
but the minimum and maximum values, accuracy, and storage size are different,
as compared in below.we can use datetime even when all dates fall into smalldatetime’s range,if you require up-to-the-second accuracy.
Smalldatetime and datetime restrictions:

Data type Minimum value Maximum value
smalldatetime January 1, 1900 June 6, 2079
datetime January 1, 1753 December 31, 9999

9) what is Identity ?
Ans : Sql server provides identity options to automatically generate values for a column in a table, generally it is used for primary key or unique key columns.

Syntax: identity(seed,increment)

Seed : is used to specify the first valut to be generated by the identity.
Increment: is used to specify increment to be done everytime generates a new value while inserting a new row.
Ex : sid int primarykey identity(1001,1)
seed - 1001
increment-1

Identity Functions:

1)ident_seed('tablename')
2)ident_incr('tablename')
3)ident_current('tablename')
4)select @@ identity - Returns identity value generated by the last insert statement that may be on any table of that database.
5)Scope_identity


SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

10) How can we get the last inserted/generated value in a row ( identity column value ) ?
Ans : ident_current('tablename')

11) What is the difference between Delete and Truncate ?
Ans : Both will be used to delete rows from the table.

Delete :
========
Sys: delete from tablename where colname=value
It can be used to delete only specific rows or all rows from the table.
It deletes the rows one by one.
It is slow when compared to truncate.
When identity is available on the table then if you delete all rows using delete command then identity value will not be reset to its seed.
Delete is a DML ( Data Manupulation Language ).

Truncate :
==========
sys: truncate from tablename
It can be used only for deleting all rows from the table.
It deletes the rows page by page.
It is fast when compared to delete.
When identity is available on the table then if you delete all rows using truncate command then identity value will be reset to its seed.
Truncate is a DDL ( Data Defination Language ).

1) What is the difference between Primary key and Unique key ?
Ans :
2) What is the difference between Stored procedure and Functions(user defined functions) ?
Ans :
3) What is the different types of joins ?
Ans :
4) waht is Self Join and how can we get the employees details and employee's manager name from below table?
Ex table - emp :
empno ename eloc emgr(empno)

Ans: Join a table to itself is called as selfjoin.

select e.empno,e.ename,e.loc,emgr,m.ename as "Manager Name"
from emp e innerjoin emp m on e.emgr=m.empno