Question { Satyam, 13250 }

Difference Between varchar and nvarchar datatype?

Answer

The difference in varchar and nvarchar datatypes is
simple. Nvarchar stores UNICODE data. If you have
requirements to store UNICODE or multilingual data,
nvarchar is your choice. Varchar stores ASCII data and
should be your data type of choice for normal use

Question { 8373 }

How will u get 5 years back record?

Answer

select *
from table
where date between '01/01/2003' and '12-31-2008'

or

today is 09-08-2008

select *
from table
where year(date)=year(getdate)-5 and month(date)= '08' and
day(date) ='09'

or

select *
from table
where date >='09-08-2003'
and date<='09-08-2008'

Question { 7406 }

What is a materialized view?

Answer

In a database management system following the relational
model, a view is a virtual table representing the result of
a database query. Whenever an ordinary view's table is
queried or updated, the DBMS converts these into queries or
updates against the underlying base tables. A materialized
view takes a different approach in which the query result
is cached as a concrete table that may be updated from the
original base tables from time to time. This enables much
more efficient access, at the cost of some data being
potentially out-of-date. It is most useful in data
warehousing scenarios, where frequent queries of the actual
base tables can be extremely expensive.

In addition, because the view is manifested as a real
table, anything that can be done to a real table can be
done to it, most importantly building indexes on any
column, enabling drastic speedups in query time. In a
normal view, it's typically only possible to exploit
indexes on columns that come directly from (or have a
mapping to) indexed columns in the base tables; often this
functionality is not offered at all.

Materialized views were implemented first by the Oracle
database.

There are three types of materialized views:

1) Read only

Cannot be updated and complex materialized views are
supported
2) Updateable

Can be updated even when disconnected from the master site.
Are refreshed on demand.
Consumes fewer resources.
Requires Advanced Replication option to be installed.
3) Writeable

Created with the for update clause.
Changes are lost when view is refreshed.
Requires Advanced Replication option to be installed

Question { Wipro, 10252 }

which query u can write to sql server doesn't work
inbetween 7.00PM to nextday 9.00AM

Answer

select * from urTable
Where urDateColumn
Between '2007-10-20 17:00:00 PM'
and '2007-10-21 09:00:00 AM'