SQL Server - Interview Questions for Testers
What are the two authentication modes in SQL Server?
Two authentication modes in SQL Server are:
What are the differences between local and global temporary tables?
These local and global temporary tables are physical tables created within the Temporary Tables folder in tempdb database.
Local temporary table name is prefixed with hash ("#") sign.
Local temporary tables are visible only in the current session, i.e; are only available to the current connection for the user. These tables are deleted or destroyed after the user disconnects from the instance of SQL Server.
Global temporary table name is prefixed with a double hash ("##") sign.
Global temporary tables are visible to all sessions and all users. Global temporary tables are deleted or dropped when all users referencing the table disconnect from the instance of SQL Server.
Global temporary tables are dropped automatically when the last session using the temporary table has completed.
Temporary tables are automatically dropped when they go out of scope, unless they are explicitly dropped using DROP TABLE.
Example of Local temporary table
CREATE TABLE #tmp_local (id int, name varchar(50)) INSERT INTO #tmp_local VALUES (1, 'AAA'); SELECT * FROM #tmp_localExample of Global temporary table
CREATE TABLE ##tmp_global (id int, name varchar(50)) INSERT INTO ##tmp_global VALUES (1, 'AAA'); SELECT * FROM ##tmp_global
Microsoft SQL Server Interview Questions By Chandan Sinha
What is sub query and its properties?
A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed.
Properties of sub query can be defined as
A sub query should not have order by clause.
A sub query should be placed in the right hand side of the comparison operator of the main query.
A sub query should be enclosed in parenthesis because it needs to be executed first before the main query.
More than one sub query can be included.
Types of sub query
There are three types of sub query –
Single row sub query which returns only one row.
Multiple row sub query which returns multiple rows.
Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.
What is COALESCE in SQL Server?
COALESCE returns first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.
Example 1 of COALESCE
Select * from Students
Select ID, COALESCE(First_Name, Middle_Name, Last_Name) from Students
Result of running the above two queries:
|ID||(No column name)|
Example 2 of COALESCE
Select COALESCE(1, 2, 3, 4, 5, NULL)
|(No Column Name)|
Select COALESCE(Null, Null, Null, 4, 5, NULL)
|(No Column Name)|
How to get the version of SQL Server?
What is Normalization?
Goal of Normalization
When you normalize a database, you have four goals: arranging data into logical groupings such that each group describes a small part of the whole; minimizing the amount of duplicate data stored in a database; organizing the data such that, when you modify it, you make the change in only one place; and building a database in which you can access and manipulate the data quickly and efficiently without compromising the integrity of the data in storage.
Data normalization helps you design new databases to meet these goals or to test databases to see whether they meet the goals. Sometimes database designers refer to these goals in terms such as data integrity, referential integrity, or keyed data access. Ideally, you normalize data before you create database tables. However, you can also use these techniques to test an existing database. [Source]
A database is in first normal form if it satisfies the following conditions:
Contains only atomic values that is there must be only one field for each item of data you want to include.
Below table is not in first normal form:
In order to normalize the above table to first normal form, above table is divided into two tables as below:
A database is in second normal form if it satisfies the following conditions:
- is in 1st normal form
- all the keys are dependent on the whole of the key
This above table has a composite primary key [Furniture_ID, Furniture_Store_ID]. The non-key attribute is [Furniture_Store_Location]. In this case, [Furniture_Store_Location] only depends on [Furniture_Store_ID], which is only part of the primary key. Therefore, this table does not satisfy second normal form. So it is divided into below two tables to normalize it into second normal form.
A database is in third normal form if it satisfies the following conditions:
-if it is in second normal form
-It has no non-key dependencies (means there are no fields that are dependent on other fields that are not part of the key)
In the table above, [Booking_ID] determines [Location_ID], and [Location_ID] determines [Location]. Therefore, [Booking_ID] determines [Location] via [Location_ID] and we have transitive functional dependency, and this structure does not satisfy third normal form. So the above table is normalized to third normal form and divided into two below tables.
How is ACID property related to Database?
Atomicity - a transaction to transfer funds from one account to another involves making a withdrawal operation from the first account and a deposit operation on the second. If the deposit operation failed, you don’t want the withdrawal operation to happen either.
Consistency - a database tracking a checking account may only allow unique check numbers to exist for each transaction.
Isolation - a teller looking up a balance must be isolated from a concurrent transaction involving a withdrawal from the same account. Only when the withdrawal transaction commits successfully and the teller looks at the balance again will the new balance be reported.
Durability - A system crash or any other failure must not be allowed to lose the results of a transaction or the contents of the database. Durability is often achieved through separate transaction logs that can "re-create" all transactions from some picked point in time (like a backup). [Source]
What is a Stored Procedure?
A stored procedure groups one or more T-SQL statements into a logical unit, stored as an object in a SQL Server database. After stored procedure is created, its definition is stored in the sys.sql_module system catalog view. [SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach by Joseph Sack]
Example 1 of Stored Procedure:
CREATE PROCEDURE AddTwoNumbers @num1 int=0, @num2 int=0, @result int OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @result=@num1 + @num2 END GO DECLARE @i INT EXECUTE AddTwoNumbers 3,2,@i OUTPUT SELECT @iResult of running the above stored procedure:
|(No column name)|
Example 2 of Stored Procedure:
Select * from A
Two procedures are there: A_details and A_insert_row:
Create Procedure A_details As Begin Select * from A End --Run the procedure on SQL Server: Execute A_details Create Procedure A_insert_row (@ID int, @Name nchar(10)) As Begin Insert Into A Values (@ID, @Name) End Execute A_insert_row 4,'B'
More on Stored Procedures:
Reasons for using stored procedures
How stored procedures help reduce the Network Traffic?
You can reduce the amount of extraneous information sent back by a stored procedure (for example, the message N rows affected) by placing the following statement at the top of your stored procedure:
SET NO COUNT ON
When you use the Set NOCOUNT ON statement, you limit the amount of extraneous information sent back to the calling process, thus reducing network traffic and increasing performance. The following example, created in the pubs database, shows how to use the statement in a stored procedure:
Create procedure usp_nocount as SET NOCOUNT ON Select * from authors[Source: Microsoft SQL Server DBA Survival Guide]
What is an Index?
Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.
A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view.
Unlike a clustered indexed, the leaf nodes of a non-clustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.
SQL Server Index Basics
What are different types of Joins?
Suppose we have two tables A & B:
It returns all rows from multiple tables where the join condition is met.
Syntax of SQL Server INNER JOINS
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;Using Inner Join on tables A & B result will be:
LEFT OUTER JOIN
It returns all rows from the LEFT table specified in the ON condition and only those rows from the other table where the join condition is met.
Syntax of SQL Server LEFT OUTER JOIN
SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column;Using Left Outer Join on tables A & B result will be:
RIGHT OUTER JOIN
It returns all rows from the RIGHT table specified in the ON condition and only those rows from the other table where the join condition is met.
Syntax of SQL Server RIGHT OUTER JOIN
SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column;Using Right Outer Join on tables A & B result will be:
FULL OUTER JOIN
It returns all rows from the LEFT table and RIGHT table with nulls in place where the join condition is not met.
Syntax of SQL Server FULL OUTER JOIN
SELECT columns FROM table1 FULL [OUTER] JOIN table2 ON table1.column = table2.column;Using Full Outer Join on tables A & B result will be:
What is the difference between a Function and a Stored Procedure?
Functions must always return value to the caller. On the other hand stored procedures do not have this requirement. You commonly use functions within another expression, whereas you often use stored procedures independently. [Microsoft SQL Server 2008 For Dummies By Mike Chapple]
Difference between a Function and a Stored Procedure
What are Primary Keys and Foreign Keys?
A primary key constraint is a column or a set of columns that uniquely identify a row in the table.
A foreign key constraint consists of a column or a set of columns that participates in a relationship with a primary key table. The primary key is on the one side of the relationship, whereas the foreign key is on the many side of the relationship. A table can have only one primary key, but it can have multiple foreign keys. Each foreign key relates to a different primary key in a separate table. [Sams Teach Yourself SQL Server 2005 Express in 24 Hours By Alison Balter]
Difference between Primary Keys and Foreign Keys
What is the difference between Clustered and a Non-clustered Index?
Only one per table
Faster to read than non clustered as data is physically stored in index order
Non Clustered Index
Can be used many times per table
Quicker for insert and update operations than a clustered index
Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations. Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.
SQL Server will normally only use an index if its selectivity is above 95%.
Difference between Clustered and a Non-clustered Index
Difference between Clustered and a Non-clustered Index
What’s the difference between a Primary Key and a Unique Key?
There can only be one primary key in a table.
In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL.
Primary Key is a unique key identifier of the record.
Can be more than one unique key in one table.
Unique key can have null values.
It can be a candidate key.
Unique key can be null and may not be unique.
Difference between a Primary Key and a Unique Key
Difference between a Primary Key and a Unique Key
What is difference between DELETE and TRUNCATE Commands?
|After Truncate, Rollback is not possible.||We can Rollback after Delete.|
|Its DDL||Its DML|
|Can't use WHERE clause with truncate||Can use WHERE clause with Delete|
|Syntax:Truncate Table Table_Name||Syntax:Delete From Table_Name|
Difference between Truncate / Delete
Difference between Truncate / Drop
What is the difference between a HAVING clause and a WHERE clause?
HAVING is used to check conditions after the aggregation takes place.
WHERE is used before the aggregation takes place.
select City, CNT=Count(1) From Address Where State = 'MA' Group By CityGives you a count of all the cities in MA.
select City, CNT=Count(1) From Address Where State = 'MA' Group By City Having Count(1)>5Gives you the count of all the cities in MA that occur 6 or more times.
Difference between a HAVING clause and a WHERE clause
What is the difference between UNION and UNION ALL?
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports). [Source]
Difference between UNION and UNION ALL
What is an Identity?
When you make a column an identity column, SQL Server automatically assigns a sequenced number to this column with every row you insert. The number that SQL Server starts counting from is called the seed value, and the amount that the value increases or decreases by with each row is called the increment. The default is for a seed of 1 and an increment of 1. Once you have set a seed (the starting point) and the increment, your values only go up (or down if you set the increment to a negative number). There is no automatic mechanism to go back and fill in the numbers for any rows you may have deleted. If you want to fill in blank spaces like that, you need to use SET IDENTITY_INSERT ON, which allows you to turn off the identity process for inserts from the current connection. [Beginning Microsoft SQL Server 2012 Programming By Paul Atkinson, Robert Vieira]
What is CHECK Constraint?
A check constraint limits the values allowed in a column.
CREATE TABLE MyOrders (OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Amount DECIMAL(18,2), Tax DECIMAL(5,2) CHECK (Tax >= 0.0 AND Tax <= 200.0) )
Now when you try inserting data:
INSERT INTO MyOrders(Amount, Tax) VALUES (150.0, 2.75) INSERT INTO MyOrders(Amount, Tax) VALUES (250.0, 15.75) INSERT INTO MyOrders(Amount, Tax) VALUES (300.0, 210.0)
The first two insert statements will just work fine but the third statement will give below error:
Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "CK__Orders__Tax__2BFE89A6". The conflict occurred in database "new_DB", table "dbo.Orders", column 'Tax'. The statement has been terminated.
How to delete duplicate rows?
Below are two ways to delete duplicate records from a table in SQL Server:
Suppose we have a table P as shown below:
Select * from P
This query deletes all the duplicate rows:
DELETE FROM P WHERE ID IN ( SELECT MIN(ID) FROM P GROUP BY ID, Name having count(*)>1)Result after running the above query:
Select * from P
Lets again use our table P and see second method of deleting duplicate rows from a table:
Select * from P
This query deletes all duplicate rows but one. For example if you have two same rows in a table it deletes one and keeps the other.
WITH cte AS ( SELECT ID, Name, row_number() OVER(PARTITION BY ID, Name ORDER BY ID) AS [rn] FROM P ) DELETE cte WHERE [rn] > 1Results of running the above query:
Select * from P
Deleting duplicate records
What is NOT NULL Constraint?
NOT NULL constraint is a type of a check constraint because it checks that a column value is set to something and not specifically set to NULL.
NOT NULL Constraint
What are user-defined functions? What are the types of user-defined functions that can be created?
Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
User-defined scalar functions return a single data value of the type defined in the RETURNS clause.
CREATE FUNCTION MyReverse1(@string varchar(100)) RETURNS varchar(100) AS BEGIN DECLARE @custName varchar(100) ='' SET @custName = Reverse (@string) RETURN @custName ENDCall the above function as
|(No column name)|
User-defined table-valued functions return a table data type.
CREATE FUNCTION SalaryMoreThan(@Sal INT) RETURNS TABLE AS RETURN SELECT Id, Name, Salary FROM teacher WHERE Salary > @SalSELECT Id, Name, Salary FROM SalaryMoreThan(200)
SQL Server provides many system functions that you can use to perform a variety of operations. They cannot be modified.
Which TCP/IP port does the SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.
What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it used for?
Its heap and it is used for inserting and updating records.
What is the difference between CHAR and VARCHAR Datatypes?
The primary difference between the varchar and char types is data padding. If you have a column called FirstName that is a varchar(10) data type and you store the value of "SAM" in the column, only 3 bytes are physically stored, plus a little overhead. If you store the same value in a char(10) data type, all 10 bytes would be used. SQL inserts trailing spaces to fill the 10 characters.
Example depicting the difference between CHAR and VARCHAR
DECLARE @Var_Char Char(10) = 'SAM', @Var_Varchar VarChar(10) = 'SAM' SELECT DATALENGTH(@Var_Char) Char_Space_Used, DATALENGTH(@Var_Varchar) VarChar_Space_Used
Another good source of difference between CHAR and VARCHAR.
Professional Microsoft SQL Server 2014 Administration by Adam Jorgensen, Bradley Ball, Steven Wort, Ross LoForte, Brian Knight
What is the difference between VARCHAR and NVARCHAR datatypes?
The difference is that nvarchar data types can be used for unicode data that can store data in any language whereas varchar data types cannot store data an all languages. So if you need your application to be truly international it is best to use nvarchar data types. [Source: Practical Sql: Microsoft Sql Server T-SQL for Beginners by Mark O'Donovan]
The main difference between the VARCHAR and the NVARCHAR data types is that each NVARCHAR character is stored in 2 bytes, while each VARCHAR character uses 1 byte of storeage space. The maximum number of characters in a column of NVARCHAR data type is 4000. [Source: Microsoft SQL Server 2012 A Begineers Guide by PETKOVIC]
Why can there be only one Clustered Index and not more than one?
The leaf level of a clustered index is the table itself. The clustered index sits on the top of the table. As a result, the table is physically sorted according to the clustered key. For this reason, a table can have only one clustered index. [Building Client-Server Applications with Visual FoxPro and SQL Server 7. 0 by Chuck Urwiler, Gary DeWitt, Leslie Koorhan, Mike Levy]
The reason why there can be only one clustered index on a table is that the clustered index governs the physical placement of the data, and the data cannot be in two places at once. There can only be one sequence in which the data can be physically placed. [Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook by Ken England, Gavin JT Powell]
How to copy data from one table to Another Table?
If both tables are truly the same schema:
INSERT INTO newTable
SELECT * FROM oldTable
Otherwise, you'll have to specify the column names (the column list for newTable is optional if you are specifying a value for all columns and selecting columns in the same order as newTable's schema):
INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
How to find the second highest salary from a table?
CREATE TABLE EMP ( EMP_ID int, Salary int ); Insert into EMP values (1, 200); Insert into EMP values (2, 100); Insert into EMP values (3, 50); Insert into EMP values (4, 600); Insert into EMP values (5, 300);Select * from EMP
Example 1 of Finding the 2nd highest salaryResult:
--Second highest Salary SELECT max(Salary) FROM EMP WHERE Salary < (SELECT max(Salary) FROM EMP);
|(No column name)|
Example 2 of Finding the 2nd highest salaryResult:
--Nth highest salary SELECT TOP 1 Salary FROM ( SELECT TOP 2 Salary FROM EMP ORDER BY Salary DESC) AS EMP ORDER BY Salary ASC