SQL SERVER INTERVIEW QUESTIONS 2014- SET 12 By
"Mr.J.Venkatesan Prabu"
KAASHIV INFOTECH
"Mr.J.Venkatesan Prabu"
[ The Asia, India & Tamil Nadu Book Of Record Holders ]
SQL Server Interview Questions 2014
SQL SERVER –Booklet 12
- Gives you the interview tips for SQL Server
SQL SERVER INTERVIEW QUESTIONS WITH ANSWERS
1. What is Cross Join?
A
cross join that does not have a WHERE clause produces the Cartesian product of
the tables involved in the join. The size of a Cartesian product result set is
the number of rows in the first table multiplied by the number of rows in the
second table. The common example is when company wants to combine each product
with a pricing table to analyze each product at each price.
FOR XML (ROW, AUTO, EXPLICIT)
You
can execute SQL queries against existing relational databases to return results
as XML rather than standard rowsets. These queries can be executed directly or
from within stored procedures. To retrieve XML results, use the FOR XML clause
of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.
OPENXML
is a Transact-SQL keyword that provides a relational/rowset view over an in-memory
XML document. OPENXML is a rowset provider similar to a table or a view.
OPENXML provides a way to access XML data within the Transact-SQL context by
transferring data from an XML document into the relational tables. Thus,
OPENXML allows you to manage an XML document and its interaction with the
relational environment.
sp_renamedb ‘oldname’ , ‘newname’
If someone sp_dboptions. Use
sp_renamedb to rename database. Use sp_dboptions to bring database to multi
user mode.
4. What are the different types of
replication? Explain.
The SQL Server 2000-supported replication types are as follows:
- Transactional
- Snapshot
- Merge
Snapshot
replication distributes data exactly as it appears at a specific moment in time
and does not monitor for updates to the data. Snapshot replication is best used
as a method for replicating data those changes infrequently or where the most
up-to-date values (low latency) are not a requirement. When synchronization
occurs, the entire snapshot is generated and sent to Subscribers.
5. What
are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac
co-ordinator)
6. What
are three SQL keywords used to change or set someone’s permissions?
GRANT, DENY,
and REVOKE.
7. What does it mean to have quoted
identifier on? What are the implications of having it off?
When
SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation
marks, and literals must be delimited by single quotation marks. When SET
QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all
Transact-SQL rules for identifiers.
8. What is
the STUFF function and how does it differ from the REPLACE function?
STUFF function
to overwrite existing characters. Using this syntax, STUFF(string_expression,
start, length, replacement_characters), string_expression is the string that
will have characters substituted, start is the starting position, length is the
number of characters in the string that are substituted, and
replacement_characters are the new characters interjected into the string.
REPLACE function
to replace existing characters of all occurance. Using this syntax REPLACE(string_expression,
search_string, replacement_string), where every incidence of search_string
found in the string_expression will be replaced with replacement_string.
9. How many rows are returned by
these two select statements?
2 and 2
Each select statement actually returns 2 rows. You can use this script to check
this:
create table mytable
( id int identity(1,1)
, mychar varchar(20)
)
go
insert mytable select null
insert mytable select ''
insert mytable select ' '
go
set ansi_nulls on
set ansi_null_dflt_on on
select * from mytable
where mychar is not null
select * from mytable
where mychar <> '' and mychar is not null
select * from mytable
where mychar <> ' ' and mychar is not null
select * from mytable where mychar = ''
select * from mytable where mychar = ' '
set ansi_null_dflt_on off
set ansi_nulls off
go
drop table mytable
10. If
you run this, what does it return?
select applock_mode('public',
'SalesApp', 'Transaction')
This command returns the lock mode held by an application that was requested
with the sp_getapplock procedure.
insert mytable select ''
insert mytable select ' '
select * from mytable where mychar = ''
KaaShiv InfoTech Offers Best Inpant Training in Chennai.
1. What is Cross Join?
A
cross join that does not have a WHERE clause produces the Cartesian product of
the tables involved in the join. The size of a Cartesian product result set is
the number of rows in the first table multiplied by the number of rows in the
second table. The common example is when company wants to combine each product
with a pricing table to analyze each product at each price.
.
2. How do SQL server 2000 and
XML linked? Can XML be used to access data?
FOR XML (ROW, AUTO, EXPLICIT)
You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve XML results, use the FOR XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.
OPENXML
OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.
OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.
3.What command do we use to
rename a db?
sp_renamedb ‘oldname’ , ‘newname’
If someone sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.
4. What are the different types of
replication? Explain.
The SQL Server 2000-supported replication types are as follows:
- Transactional
- Snapshot
- Merge
Snapshot
replication distributes data exactly as it appears at a specific moment in time
and does not monitor for updates to the data. Snapshot replication is best used
as a method for replicating data those changes infrequently or where the most
up-to-date values (low latency) are not a requirement. When synchronization
occurs, the entire snapshot is generated and sent to Subscribers.
Transactional
replication, an initial snapshot of data is applied at Subscribers, and then when data
modifications are made at the Publisher, the individual transactions are
captured and propagated to Subscribers.
Merge
replication is the process of distributing data from Publisher to Subscribers,
allowing the Publisher and Subscribers to make updates while connected or
disconnected, and then merging the updates between sites when they are
connected.
5. What
are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
6. What
are three SQL keywords used to change or set someone’s permissions?
GRANT, DENY, and REVOKE.
7. What does it mean to have quoted
identifier on? What are the implications of having it off?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
8. What is
the STUFF function and how does it differ from the REPLACE function?
STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurance. Using this syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.
9. How many rows are returned by
these two select statements?
2 and 2
Each select statement actually returns 2 rows. You can use this script to check this:
create table mytable
( id int identity(1,1)
, mychar varchar(20)
)
go
insert mytable select null
insert mytable select ''
insert mytable select ' '
go
set ansi_nulls on
set ansi_null_dflt_on on
select * from mytable
where mychar is not null
select * from mytable
where mychar <> '' and mychar is not null
select * from mytable
where mychar <> ' ' and mychar is not null
select * from mytable where mychar = ''
select * from mytable where mychar = ' '
set ansi_null_dflt_on off
set ansi_nulls off
go
drop table mytable
10. If
you run this, what does it return?
select applock_mode('public', 'SalesApp', 'Transaction')
The type of lock being held by an
application that requested it.
This command returns the lock mode held by an application that was requested with the sp_getapplock procedure.
insert mytable select ''
insert mytable select ' '
select * from mytable where mychar = ''
select * from mytable where mychar = ' 'This command returns the lock mode held by an application that was requested with the sp_getapplock procedure.
insert mytable select ''
insert mytable select ' '
select * from mytable where mychar = ''
KaaShiv InfoTech Offers Best Inpant Training in Chennai.
The training at KAASHIV INFOTECH focus on developing the technical oriented concepts that turn graduates into employable assets. Handled only by professionals from MNC companies, we know how to equip you with strong technologies fundamentals.
Day
|
Programme
|
Day 1
|
BigData (Practical Demos)
|
Day 2
|
Windows 8 App Development (Practical Demos)
|
Day 3
|
Ethical Hacking (Facebook Hack,Server/Website Hacking(20 Attacks)
|
Day 4
|
Cloud Computing (Live Server Demo,Live Pjt Implementation)
|
Day 5
|
CCNA (-Networking-Router Configurations Practical Demo)
|
INPLANT TRAINING SCHEDULE FOR ELECTRONIC/ELECTRICAL/EIE STUDENTS
Day
|
Programme
|
Day 1
|
Embedded System (Embedded Program Designing ,Chip Burning)
|
Day 2
|
Wireless System (Device Designing,Controlling Fans with Wireless Sensors)
|
Day 3
|
CCNA (-Networking-Router Configurations Practical Demo)
|
Day 4
|
Ethical Hacking (Facebook Hack,Server/Website Hacking(20 Attacks)
|
Day 5
|
Matlab (Capture Image,Processing, Animate Images-Practical Demos)
|
Mechanical/Civil Inplant training Schedule
Day
|
Programme
|
Day 1
|
Aircraft Designing
|
Day 2
|
Vehicle Movement in Airports
|
Day 3
|
3D Packaging Designs
|
Day 4
|
3D Modeling
|
Day 5
|
3D Window Shading
|
Address:
KAASHIV INFO TECH
Shivanantha Building,
X41, 5th Floor,2nd Avenue,
(Near Ayyappan Temple)
Anna Nagar, Chennai = 600040.
Shivanantha Building,
X41, 5th Floor,2nd Avenue,
(Near Ayyappan Temple)
Anna Nagar, Chennai = 600040.
Send Us Your Request Email To arun@kaashivinfotech.com,
Contact Number : 9840678906 ,9003718877 , 9962345637 .
Visit our other websites
http://www.inplanttrainingchennai.com
http://www.inplanttraining-in-chennai.com
http://www.Kaashiveinotech.com
http://www.internshipinchennai.in
https://plus.google.com/u/0/b/110228862465265998202/dashboard/overview
https://plus.google.com/u/0/b/117408505876070870512/dashboard/overview
https://plus.google.com/u/0/b/104468163439231303834/dashboard/overview
https://plus.google.com/u/0/b/117640664472494971423/dashboard/overview
Feedback URLS
KaaShiv InfoTech Facebook Page
https://www.facebook.com/KaaShivInfoTech
Inplant Training Program in Chennai
https://www.facebook.com/pages/Inplant-Training-Program-in-Chennai/1402097696706380
Internship Training Program in Chennai
https://www.facebook.com/pages/Internship-in-Chennai-KaaShiv/1446147235603704
Facebook Inplant Training page
https://www.facebook.com/pages/Inplant-Training/256116284550327
