Here is some important SQL Interview Questions and Answers, these SQL Interview Questions and Answers may be help in your interview round.
SQL Interview Questions and Answers
What are difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we can write WHERE clause in delete command.
Truncate command will actually remove all the rows from a table, we can not use where clause in traincate command, there is no log maintains for this.
Blow are some difference between Delete and Truncate.
- TRUNCATE Resets identity of the table.
- TRUNCATE is faster then Delete beacuse Truncate uses fewer system and transaction log resources than DELETE.
- TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
- TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
- You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
- It can not activate any trigger, because TRUNCATE TABLE is not logged.
- TRUNCATE can not be Rolled back using logs.
- TRUNCATE is DDL Command.
- DELETE is DML Command
- DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
- If you want to retain the identity counter, use DELETE instead.
- DELETE Can be used with or without a WHERE clause
- DELETE Activates Triggers.
- DELETE Can be Rolled back using logs.
- DELETE does not reset identity of the table.
1433 is the standard port for SQL server.
- Buffer Cache: Buffer cache is a memory pool in which data pages are read. The ideal performance of the buffer cache is indicated as: 95% indicates that pages that were found in the memory are 95% of time. and rest of it that is 5% is need physical disk access. If the value falls below 90%, it is the indication of more physical memory requirement on the server.
- Log Caches: Log cache is a memory pool used to read and write the log pages.
Stored procedures can also be used for sending mail on demand,Make sure that the SQL Server Mail account is configured correctly and enable Database Mail
@body = ‘body text,
@subject = ‘Your Subject’
How to get top 3 salary in Sql server?
SELECT * FROM Employee
SELECT TOP (3) sal
FROM Employee as EmployeeSalary
GROUP BY salary
ORDER BY salary DESC
SELECT MIN(salary) FROM Salary WHERE Employee IN(SELECT TOP 3 Employee FROM Salary ORDER BY Salary DESC)
What are difference between char and nvarchar / char and varchar data-type?
Local Temp Table:
Local temp table is created using the # sign and table name.
Temp table is created for single user session or duration of sql connection of sql and once the session or connection is closed temp table is dropped.
If we are creating the local temp table using the stored procedure then it will be automatically dropped after execution of the sql procedure.
Global Temp Table:
Global temp table is created using the ## sign and table name.
Global temp table is created for all user session of sql server and once all the users’ session is closed in sql server global table is automatically droped.
Global table is visible to all users and its dropped one last connection is closed in sql server.
What are difference between Primary key Constraint and Unique key Constraint in SQL Server?
Unique Key Constraint:
Unique key will create column data uniqueness in the table.
Unique Key allows null values in the column.
Unique key will create non-clustered index by default.
You can add any number of unique constraints in to a table.
Primary Key Constraint:
Primary key will create column data uniqueness in the table.
Primary key is not allowed Null values into the column values.
Once we set the primary column then by default Primary key will create clustered index.
We can create only one primary key into table.
What are difference between the temp table and table variable in SQL Server?
What is clustered and nonclustered index in Sql Server?
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
In SQL Server Clustered indexes store data as a B-Tree Structure and its left node contains the actual values. In clustered index data are physically sorted and stored into the disk and tables. You can create only one clustered index in tables.
CREATE CLUSTERED INDEX Test_TestTable_TestCol1
ON dbo.TestTable (TestCol1);
Non Clustered Index:
In Non Clustered Index’s store data as a B-Tree Structure and its leaf node contains the pointer of the values, leaf lode does contain actual values. In Non Clustered index the logical order of the index does meet the physical order of the row in disk. The Non clustered index structure is often use for finding the data quickly, its enhance the query performance. You can create the multiple non clustered index on the table. The data is stored into the Heap in non-clustered index.
CREATE NONCLUSTERED INDEX Test_Product_ID
ON Purchasing.Product (EntityID);
What are difference between Group by and Having clause in SQL Server?
Group by Clause:
The GROUP BY clause is used in SQL for SELECT statement to collecting the data across multiple records and making the group of one or more columns.
SELECT Department1, SUM(ProductSales) AS “Total ProductSales”
GROUP BY Department1;
The having clause is used for only on group rows of tables and act as a filter like as where clause. You can use having clause to filter data that that you get from group by clause in tables. For using the having clause in SQL Query first you need to use group by clause.
SELECT Department1, SUM(ProductSales) AS “Total ProductSales”
GROUP BY Department1
Having SUM(Total ProductSales) >50000
How to unlock the user without password reset
If any user try to login many times and failed. Sql server 2005 locked account automatically then that user try to login (other than sa for example “testuser1” user) in MS SQL Server and it shows the account be locked.
when he try into SQL Server Management Studio to unlock the testuser1 account, you receive this message: Read more
What are the difference between a primary key and a unique key?
Primary key and a Unique key enforce unique value of the column on which they are defined.
Main difference between them is that, primary key doesn’t allow any NULLs, but unique key allows one NULL only, and another difference is by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.
above are very common SQL Interview Questions and Answers, you should read our all SQL Interview Questions and Answers
you should prepare some most common SQL Interview Questions and Answers, you can find some SQL Interview Questions and Answers on our website, we will post some more SQL Interview Questions and Answers soon.