Monday, June 27, 2011

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

.NET INTERVIEW QUESTIONS...

1) How to convert a DataSet to a DataReader ?
Ans : DataTableReader rd = ds.Tables[0].CreateDataReader();

2) What are the Expansions of MSIL, JIT, CLR, CTS, CLS and RCW ?
Ans : 1. MSIL - Microsoft Intermediate Language.
2. JIT - Just-In-Time compiler .
3. CLR - Common Language Runtime.
4. CTS - Common Type System.
5. CLS - Common Language Specification.
6. RCW - Runtime Callable Wrapper.

Friday, June 24, 2011

An Architect Is...

Accepting my responsibility as an architect, I will strive daily to learn and perfect my trade.

Readily will I defend my organization’s I.T. investments from complexity, our greatest enemy.

Changing requirements will not break my design for it will be flexible and able to change with the requirements.

Helping developers to understand the reasons for the architecture and seeking their input is of great importance to our success.

I will never use acronyms or suggest technologies that are not pertinent to the problem at hand.

Technologies evolve and I know that solid architectures should accommodate and enable these evolutions.

Every day, I will strive to help our software achieve ideals that will make it flexible and easy to maintain.

Concern for the success of my organization’s I.T. investments will drive me to make appropriate decisions.

Teaching others about my trade will be an overarching responsibility that I accept as a part of my duties.