SQL Server questions for interview Part3

-

What is the actions prevented once referential integrity is enforced
Actions prevented are  Breaking of relationships is prevented once referential integrity on a database is enforced. Can’t delete a row from primary table if there are related rows in secondary table. Can’t update primary table’s primary key if row being modified has related rows in secondary table. Can’t insert a new row in secondary table if there are not related rows in primary table. Can’t update secondary table’s foreign key if there is no related row in primary table. 

What are the commands available for Summarizing Data in SQL Server
Commands for summarizing data in SQL Server:

Command
Description
Syntax/Example
SUM
Sums related values
SELECT SUM(Sal) as Tot from Table1;
AVG
Average value
SELECT AVG(Sal) as Avg_Sal from Table1;
COUNT
Returns number of rows of resultset
SELECT COUNT(*) from Table1;
MAX
Returns max value from a resultset
SELECT MAX(Sal) from Table1;
MIN
Returns min value from a resultset
SELECT MIN(Sal) from Table1;
GROUP BY
Arrange resultset in groups
SELECT ZIP,City FROM Emp GROUP BY ZIP
ORDER BY
Sort resultset
SELECT ZIP,City FROM Emp ORDER BY City


List out the difference between CUBE operator and ROLLUP operator 
Difference between CUBE and ROLLUP:

CUBE
ROLLUP
It’s an additional switch to GROUP BY clause. It can be applied to all aggregation functions to return cross tabular result sets. .
It’s an extension to GROUP BY clause. It’s used to extract statistical and summarized information from result sets. It creates groupings and then applies aggregation functions on them.
Produces all possible combinations of subtotals specified in GROUP BY clause and a Grand Total.
Produces only some possible subtotal combinations.


What are the guidelines to use bulk copy utility of SQL Server:
Bulk copy is an API that allows interacting with SQL Server to export/import data in one of the two data formats. Bulk copy needs sufficient system credentials.
  • ·         Need INSERT permissions on destination table while importing.
  • ·         Need SELECT permissions on source table while exporting.
  • ·         Need SELECT permissions on sysindexes, sysobjects and syscolumns tables.

bcp.exe northwind..cust out "c:\cust.txt" –c -T
Export all rows in Northwind.Cust table to an ASCII-character formatted text file.  

What are the capabilities of Cursors:
Capabilities of cursors:
  • ·         Cursor reads every row one by one.
  • ·         Cursors can be used to update a set of rows or a single specific row in a resultset
  • ·         Cursors can be positioned to specific rows.
  • ·         Cursors can be parameterized and hence are flexible.
  • ·         Cursors lock row(s) while updating them. 

What are the ways to controlling Cursor Behavior? 
There are 2 ways to control Cursor behavior:
·         Cursor Types: Data access behavior depends on the type of cursor; forward only, static, keyset-drive and dynamic.
·         Cursor behaviors: Keywords such as SCROLL and INSENSITIVE along with the Cursor declaration define scrollability and sensitivity of the cursor. 

What are the advantages of using Stored Procedures? 
Advantages of using stored procedures are:
  • ·         They are easier to maintain and troubleshoot as they are modular.
  • ·         Stored procedures enable better tuning for performance.
  • ·          Stored procedures is much easier from a GUI end than building complex queries.
  • ·         They can be part of a separate layer which allows separating the concerns. Hence         Database layer can be handled by separate developers proficient in database queries.
  • ·         Help in reducing network usage.
  • ·         Provides more scalability to an application.
  • ·         Reusable and hence reduce code.

Sql Server Interview Questions:




Comments

Post a Comment