SQL SERVER TOP 50 INTERVIEW QUESTION AND ANSWERS

 # 50 SQL Server Interview Questions with Answers

## Basic SQL Server Questions

1. **What is SQL Server?**

   - SQL Server is a relational database management system (RDBMS) developed by Microsoft that supports transaction processing, business intelligence, and analytics applications.

2. **What are the different editions of SQL Server?**

   - Enterprise, Standard, Web, Developer, and Express editions.

3. **What is the difference between clustered and non-clustered indexes?**

   - A clustered index determines the physical order of data in a table (only one per table). A non-clustered index is a separate structure that points to the data (multiple allowed per table).

4. **What is a primary key?**

   - A primary key is a column or set of columns that uniquely identifies each row in a table and cannot contain NULL values.

5. **What is a foreign key?**

   - A foreign key is a column or set of columns that establishes a relationship between data in two tables, enforcing referential integrity.

## Intermediate SQL Server Questions

6. **What is the difference between DELETE, TRUNCATE, and DROP?**

   - DELETE removes rows one at a time with logging, TRUNCATE removes all rows quickly without logging individual row deletions, DROP removes the entire table structure.

when i restore the sql database i am getting error

when i restore the sql database i am getting error

TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server 'MAIN'. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The backup set holds a backup of a database other than the existing 'abc' database. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ 

Answer:
You're trying to overwrite existing database abc with a backup of a different database or a backup from another server.  The default action is not to allow that to happen.

If you're sure you're doing the right thing, add the WITH REPLACE option to the RESTORE DATABASE command and it will proceed.  You may also need WITH MOVE options if the backup came from a database whose files were in different locations that the abc database.

Error While Restoring the SQL SERVER Data base

Issues while restoring the data base in Sql server :

While restore the data base  i am getting the following error :

when i restore the sql database i am getting error

TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server 'MAIN'. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The backup set holds a backup of a database other than the existing 'abc' database. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ 

Answer: 

You're trying to overwrite existing database like adventure works with a backup of a different database or a backup from another server.  The default action is not to allow that to happen.

 If you're sure you're doing the right thing, add the WITH REPLACE option to the RESTORE DATABASE command  shown below image and it will proceed.  You may also need WITH MOVE options if the backup came from a database whose files were in different locations that the like  adventure work database. 

SQL Server real time question

I have one doubt of SQL SERVER DBA

I am settings in backup every Sunday 12 am fullback up.

And  for every 6 hours differential backup.

And for every 15 mints  log back up.

 If my server crashes at Thursday 4.30 pm...How to recovery and restore the backup.

SQL SEVER INTERVIEW QUESTION

1. What is  the default "SORT" order for SQL SEVER?


2. select adresses which are between  " 1/1/2015" and "1/04/2015" ?

3.why should you use CTE rather than using simple views?