Sql Server Interview Questions and Answers (1758) - Page 26

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

When we are transferring records from one database to another database so now if we use SqlBulkCopy class to transfer the records then which scenario we need to handle ?

NOTE: This is objective type question, Please click question title for correct answer.

What are CRUD operations in database?

The term CRUD stands for Create, Read, Update, and Delete.

Each letter in the acronym corresponds to each database operations.

C-Create :- corresponds to INSERT statement in sql
R-Read :- corresponds to SELECT statement in sql
U-Update:- corresponds to UPDATE statement in sql
D-Delete :- corresponds to DELETE statement in sql

Ref:http://www.dotnetspider.com/Database-Tutorial-128.aspx

Do stored procedures perform better than SQL queries (or embedded SQL)?

Its a myth that the stored procedure perform better than SQL queries. Definitely, it was a fact versions before SQL 7.0 where in stored procedures were a way to partially precompile an execution plan in SQL Server version 6.5 and earlier. Such partially compiled execution plan created at the the time the stored procedure was created, were stored in a system table. Please see the article for more details.
http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

Important anecdote from this article is as follows
"SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created."

How many types of indexes are available in SQL Server?

There are four type's of index's available in Sql Server.

Cluster : We can create only index in Table. When we create primary key then cluster index create automatically.

Non Cluster: we can create 256 non cluster index in table but should be crate 3 index in table because effect on performance. when create unique key then non cluster index create automatically.

Unique: We can create unique index too.

Composite Key: when we create index with two columns together then composite key index create. like class name and roll no create one index.

In SQL Server Database, what is the basic difference between a table scan and an index scan ?

Table Scan --- Here, row by row scanning is done to get the data. In case, there are huge number of data in a table, it becomes an overhead.

Index Scan --- Here in the first, index is created in the table. It then uses the index to get to the data that you wanted. It increases the performance.

What is BCP ?

BCP is stand for Bulk copy Program in sql server, bulk copy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structure same as source to destination.

What is MERGE statement?

MERGE is new feature in SQL Server 2008 that provide an efficient way to perform multiple operations. In previous version we had to write separate statement to INSERT,DELETE and UPDATE data based on certain conditions, but now using MERGE statement we can include the logic of such data modification in one statement that even checks when the data matched then just update it and when unmatched then insert it. most important advantage of MERGE statement is all the data is read and processed only once.

Write a sql query which will give result of the city name of the employee whose avg salary > 2000

select cityname, avg(salary) as avgsal from Employee   group by cityname  having  avg(salary) >2000

Find and Delete duplicate records in a table

Many times you can face problem of duplicate records in table.So How would you identify and delete duplicate records in a table?
For that Firstly check if table has duplicate records or not using below query.

SELECT [FirstName] FROM tblTest GROUP BY [FirstName] HAVING COUNT(*) > 1

Then Delete duplicate records.

DELETE FROM tblTest WHERE ID NOT IN (SELECT MAX(ID) FROM tblTest GROUP BY [FirstName])

A stored procedure can be called from a trigger, another stored procedure or even from a client applications. Are all the above statements correct ?

NOTE: This is objective type question, Please click question title for correct answer.

A stored procedure must contains at least two parts: (1) stored procedure name (2) its body

NOTE: This is objective type question, Please click question title for correct answer.

What is the command that is used to set a set of privileges that can be granted to users or different roles?

NOTE: This is objective type question, Please click question title for correct answer.

Which command removes all the rows from the table without logging individual row deletions ?

NOTE: This is objective type question, Please click question title for correct answer.

What is the clause that specifies a condition for a group or an aggregate?

NOTE: This is objective type question, Please click question title for correct answer.

Which of the following are the limitations of VIEWS?

NOTE: This is objective type question, Please click question title for correct answer.

Difference between Primary Key and unique key ?

Primary Key Restrict duplicate values and null values each table can have only one primary key,default clustered index is the primary key.

unique key restrict duplicate values and allow only one null value. default non clustered index is an unique key

Write a script to identify, Each character's count in a given string ? (Without using Loop) i.e: Pandian

Declare @String Varchar(100)

Select @String = 'Pandian'

;With CTEs

As

(

Select LEFT(@String,0) Chars,0 [String]

Union All

Select Substring(@String,[String]+1,1) Chars,[String]+1 From CTEs Where [String] <=LEN(@String)

)

Select Chars [Letter], COUNT(1) [Repeats] from CTEs Where Chars <>'' Group by Chars

Go

Result:

Letter	Repeats

------  ------

a	2

d	1

i	1

n	2

P	1

What is the difference between DELETE and TRUNCATE in SQL ?

Using TRUNCATE, we cannot restore the deleted data.

Syntax:

TRUNCATE TABLE table_name;

Example:

To delete all the rows from employee table, the query would be like,

TRUNCATE TABLE employee;

Unlike using DELETE, we can restore the data, as the physical data will not get deleted.

Syntax:

DELETE FROM table_name [WHERE condition];

Example:

To delete an employee with id 100 from the employee table, the sql delete query would be like,

DELETE FROM employee WHERE id = 100;

What is CTE in Sql server 2005 ?

A common table expression (CTE) can be thought of as a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is similar to a derived table. Unlike a derived table, a CTE can be self-referencing, not stored as object and can be referenced multiple times in the same query.

It can be recursive and non-recursive.

It provides the significant advantage of being able to reference itself because earlier version sql server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive step.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

What is magic table in Sql server ?

Sql Server automatically creates and manages two temporary, memory-resident tables (deleted and inserted tables) which are popularly known as magic tables.

Usually used with the DML triggers. Can not directly modify the data in the tables or perform ddl operation.

Primarily used to perform certain action like

1) Extend referential integrity between tables
2) Test for errors and take action based on the error.
3) Find the difference between the state of a table before and after data modification and take actions based on that difference.