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

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

What Operator performs Pattern Matching ?

Pattern matching operator is LIKE and it is used with two attributes:

1. % - means matching zero or more characters

2. _ ( underscore ) - means mathing exactly one character

How can i hide a particular table name of our schema ?

You can hide the table name of your schema by creating synonyms.

Example:

you can create a synonym y for table x

create synonym y for x;

What is difference between DBMS and RDBMS ?

The main difference between DBMS & RDBMS is

RDBMS have Normalization. Normalization means to refining the redundant and maintain the stablization.
DBMS hasn't normalization concept.

What are Data Marts ?

Data Warehousing is a process in which the data is stored and accessed from central location.
Data Marts are smaller section of Data Warehouses which help data warehouses to collect data.
For example your company has lot of branches which are spanned across the globe. Head-office of the company decides to collect data from all these branches for anticipating market. So to achieve this IT department can setup data mart in all branch offices and a central data warehouse where all data will finally reside.

What is Snow Flake Schema design in database ? What’s the difference between Star and Snow flake schema ?

The denormalization of star schema is known as snow flake design.
Star schema is good when you do not have big tables in data warehousing. But when tables start becoming really huge it is better to denormalize. When you denormalize star schema it is nothing but snow flake design.

We have an employee salary table, how do we find the second highest from it ?

You can use the below query to find out the second highest salary from employee salary table.

SELECT * FROM TblEmployeeSalary a WHERE (2=(SELECT COUNT(DISTINCT(b.salary)) FROM TblEmployeeSalary b WHERE b.salary>=a.salary))

What is RAID and what are different types of RAID configurations ?

RAID means Redundant Array of Inexpensive Disks. It is used to provide fault tolerance to database servers. There are six RAID levels from 0 through 5, which offers different levels of performance, fault tolerance.

How to determine the service pack currently installed on SQL Server ?

The global variable @@Version stores the build number of the sqlservr.exe.
This is the way which is used to determine whether the service pack is installed or not.

What is the query to retrieve first name and sum of order qty for order sum greater than 25, and group the order sum by first name.?

The query to retrieve first name and sum of order qty for order sum greater than 25, and group the order sum by first name is as follows:

SELECT FIRSTNAME,SUM(QTY)  

FROM orders  

GROUP BY FIRSTNAME  

HAVING SUM(QTY)>25;

What is the query to retrieve all employees whose name has a String "au"?

The query to retrieve all employees whose name has a String "au" is as follows:

SELECT *  

FROM employees emp  

WHERE emp.firstname LIKE ‘%au%’;

Can we call a Trigger in store procedure ?

We cannot call a trigger explicitly from a Stored Procedure as it is also a special kind of Stored Procedure.
A Trigger will fire automatically on the happening of an event like before or after insert, update or delete.

How to get number of Maximum connections can be established to SQL ?

To get the number of Maximum connections established to SQL, the following query is used :

select @@MAX_Connections

Why do we use SET ROWCOUNT in Sql ?

This syntax is used in SQL Server to stop processing the query after the specified number of rows are returned.

Example:

SELECT * FROM EMPLOYEES

- It will return 10 rows of records.



After add SET ROWCOUNT

SET ROWCOUNT 5

SELECT * FROM EMPLOYEES

- It will return 5 rows of records instead of 10 row. 

(Bacause you set the RowCount to 5)

How is the SUBSTR keyword used in SQL ?

SUBSTR is used for string manipulation with column name, first position and string length used as arguments.

Example:

SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial

FROM Person.Contact

WHERE LastName like 'Barl%'

ORDER BY LastName



Result:



LastName   Initial

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

Barley     R

Barlow     B



(2 row(s) affected)

What level of permissions does a user need to create an assembly with UNSAFE permissions ?

The answer is sysadmin
Only a sysadmin can create the assemblies that are specified as unsafe.

What port do you need to open on your server firewall to enable named pipes connections ?

To enable named pipes connections, you need to open Port 445 on your server firewall.
Named pipes communicates across TCP port 445.

Define Candidate Key ?

A candidate key is a key which can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

Example:

CREATE TABLE Appointments (

     ClientID int not null,

     CounsellorID int not null,

     RoomID int not null,

     AppointmentTime datetime not null

 )

The candidate keys for this table are {ClientID,AppointmentTime}, {CounsellorID,AppointmentTime} and {RoomID,AppointmentTime}. Any of those combinations of columns could be used to uniquely identify a row in the table, and all of them are composite keys.

What is lock escalation ?

Lock escalation is the process which involves of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).
Every lock is a memory structure.
More number of locks would mean, more memory being occupied by locks.
To prevent this from happening, SQL Server escalates many fine-grain locks to fewer coarse-grain locks.
Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it is dynamically managed by SQL Server.

What is RAID ?

RAID stands for Redundant Array of Inexpensive Disks.
It is used to provide fault tolerance to database servers.

What is meant by Blocking ?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type.
This forces the second connection to wait, blocked on the first.