SQLServer Interview Questions

Every .NET Developer Should Know About the Database they are working with: Click @ me 

What is SQL Transaction?

Transaction can be defined as a Sequence of Operations performed together as a Single logical unit of Work, that must be completed to maintain the Consistency and Integrity of Database.

a single unit of work must process the four Properties.- ACID

Automicity- this states that either all the operations are performed or none of them.

Consistency- this states that all Data is in a Consistent state after a Transaction is Completed.

Isolation- this states that any Data Modification made by one Transaction must be Isolated from the modification made by other Transaction.

Durability- this states that any Changes in the Data by a Completed Transactions  remains Permanently in effect in the System.

What is distributed transactions in SQL Server? When are they used?

Distributed transactions involve two or more databases within a SQL server. The management of such transactions is done by a component called as Transaction Manager. Distributed transactions must be used when real time updates are required simultaneously on multiple databases.

Can you explain the different types of transaction states in a database system?

In a database system, there are four different types of transaction states: Active, Committed, Aborted, and Unknown. Active means that the transaction is currently in progress and has not yet been completed. Committed means that the transaction has been completed successfully and all changes have been made to the database. Aborted means that the transaction has been unsuccessful and all changes have been rolled back. Unknown means that the status of the transaction is unknown.

What is the need to SAVE TRANSACTION?

SAVE TRANSACTION is used for dividing (or) breaking a transaction into multiple units. So that the user has a chance of rollbacking a transaction up to a location.

Nested Transactions in SQL Server: https://dotnettutorials.net/

Delete duplicate rows in SQL Server Click here

Difference between Unique Key and Primary Key
1. Primary Key does not allow null values whereas unique constraint allows 'single' null value.
2. A table can have only single Primary Key where as it can have multiple unique constraints
( max 16)
3. Primary Key creates clustered index by default whereas Unique Key creates non-clustered index by default.

What happens if we use “With NOLOCK” on a table?

If you use the “With NOLOCK” hint when querying a table, it means that you are willing to read data that is currently being modified by other transactions. This can lead to dirty reads, where you read data that is in an inconsistent state, or even missing data, if the other transaction ends up rolling back its changes.

Difference between TRUNCATE and DELETE commands?

1.TRUNCATE is a DDL command whereas DELETE is a DML command.
2.DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. 3.WHERE can be used with DELETE and not with TRUNCATE.
4. DELETE Maintain Log file for each row deletion, but Truncate don’t.
5. Delete Command: Delete a row based on where Condition. Truncate command: Delete all the Rows from table.
6.Truncate Resets Identity Column counters.
7. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains.

How can you optimize stored procedures?

1. Use the SET NOCOUNT ON statement
2. Use the schema name with the object name
3. Do not use the prefix "sp_" in the name
4. Use IF EXISTS (SELECT 1) instead of SELECT *
5. Avoid using cursors
6. Keep the transactions short as possible.
7. Use sp_execute sql instead of the EXEC statement for dynamic SQL
8. Execution Plans in SQL Server Click here

How do you optimize queries? 

1. You can use SQL Profiler to locate long running queries
2. In general, keep your DBs normalized
3. Move queries to stored procedures: SPs are compiled and run on the server
4. Limit your searches as much as possible-only grab what you need
5. Don't use joins if you don't have to, they are expensive. Try to join on indexed fields if you can.
6. Add indexes to fields you often filter by, especially if they are an Integer, Boolean, or Number
7. You can limit the number of rows returned, or possibly do a pagination, so you don't have to get a giant amount of rows.

Difference between Rank and Dense_Rank function?

The RANK() function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.

The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.

Is it Possible to Update View in SQL Server
Yes, it is Possible when Data you are updating directly refers to the Column of only one Base Table. With multiple columns of more then Table can not be updated.

Union vs. Union All 
The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

Cast and Convert 
CAST and CONVERT are both used to convert data from one data type to another.
Here is an example using both CAST and CONVERT in the same statement:

SELECT CAST ('10' as int) * 20, CONVERT (int, '10') * 20

CONVERT differences lie in that it accepts an optional style parameter which is used for formatting.
For example, when converting a DateTime datatype to Varchar, you can specify the resulting date’s format, such as YYYY/MM/DD or MM/DD/YYYY.

SELECT CONVERT(VARCHAR,GETDATE(),101) as MMDDYYYY, CONVERT(VARCHAR,GETDATE(),111) as YYYYMMDD

What is mean by constraint?
Constraint : Protect columns of the table from unwanted values. NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of constraints define in SQL Server.

Cache dependency on sql server

What is stored procedure?
It's nothing but a set of T-SQL statements combined to perform a single task of several tasks. It’s basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

Materilised View
A materialized view is defined just as a regular view but the result set of the query is stored as persistent data object such as table which is frequently updated from the underlying base tables when changes occur. They are useful to aggregate data in business intelligence applications with complex queries

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Both Clause are used to check the Condition at the time of Retrieval of records in Database. HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

[If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.(Where clause doesn’t work with Aggregate Function)]

Example to check Duplicate Records:

select empid from employee where empid not in(select empid from employee group by empid

having count(*)>1)

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

To Add Primary Key in A Table

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

To Delete Primary Key in a Table

ALTER TABLE Persons

DROP CONSTRAINT pk_PersonID

DML-- DML stands for Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database.

What is mean by DDL and Usage?

DDL -- Data Definition Language.
create and modify the structure of database objects.

What keyword does an SQL SELECT statement use for a string search?

The LIKE keyword allows for string searches. The % sign is used as a wildcard.

To Rename a Database- 

sp_renamedb 'oldname' , 'newname'

What are the 2 types of Temporary Tables in SQL Server?
http://www.codeproject.com/

There are 2 types of temporary tables, local and global in sql server.

Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends.

CREATE TABLE #LocalTempTable(UserID int,

    UserName varchar(50),UserAddress varchar(150))

Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.

CREATE TABLE ##LocalTempTable(

    UserID int,

    UserName varchar(50),

    UserAddress varchar(150))

What is NOT NULL Constraint
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What is the difference between primary key and foreign key?
Primary key is use to uniquely identify each row they are unique and cannot be null where as foreign key are use to ensure data integrity and relationship between tables.
PK will enforce entity integrity whereas FK will enforce referential integrity.

• A Table can have maximum 1024 Columns.

FOREIGN KEY Constraint:
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

CREATE TABLE Orders

(

OrderNo int NOT NULL,

P_Id int,

CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)

REFERENCES Persons(P_Id)

)

To Add Foreign Key in A Table

ALTER TABLE Orders

ADD CONSTRAINT fk_PerOrders

FOREIGN KEY (P_Id)

REFERENCES Persons(P_Id)

There is a table with name Country Table which has only one column - CountryName. Write a query to select the name of the countries whose name repeat more than 3 times in the table.

SELECT CountryName FROM Country Table GROUP BY CountryName HAVING COUNT(*) >=3

Define Identity Column?
Identity column is a column that automatically generates numeric values. Start and increment value are the two properties of prior importance that need to be set with respect to Identity column. Value of this column is mostly used to identify each row uniquely.

Define collation?
Collation is a set of rules that defines how the data  is to be sorted and compared.

Difference between Function and stored procedure?
Store Procedure:

1.       Stored procedure may or not return values.
2.       It will allow DQL select statements as well as DML statements such as insert, update, delete etc.
3.       Stored procedures have mutually input and output parameters.
4.       We can use try and catch blocks for exception handling in stored procedures.
5.       Stored procedure can use transactions inside stored procedures.
6.       Stored procedure can use both table variables as well as temporary table in it.
7.       Stored Procedures be able to call functions.
8.       Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec statement can be used to call/execute stored procedure.
9.       Store procedures can’t be used in Join clause.

UDF User Define Function:
1.       Function necessities return a value.
2.       UDF will allow only Select statement; it will not allow us to use DML statements.
3.       UDF will allow only input parameters, doesn’t support output parameters.
4.       UDF will not allow us to use try-catch blocks.
5.       UDF transactions are not allowed within functions.
6.       We can use only table variables; UDF will not allow using temporary tables.
7.       Stored procedures can’t be called from function.
8.       Functions can be called from select statement.
9.     UDF can be used in join clause as a result set.

What is a VIEW in Sql Server?
View is a Virtual table which contains columns from one or more tables. It does not contain any data directly.

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

What is composite key?
A key formed by combining at least two or more columns is called composite key.

 The SQL SELECT DISTINCT Statement
Returns only Distinct Value from a table => SELECT DISTINCT City FROM Persons

SQL AND & OR Operators
SELECT * FROM Persons WHERE LastName='Svendson'  AND (FirstName='Tove' OR FirstName='Ola')

SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set
The ORDER BY keyword sorts the records in ascending order by default.

SELECT * FROM Persons ORDER BY LastName DESC

SQL TOP Clause- is used to specify the number of records to return.

SELECT TOP 2 * FROM Persons

SELECT TOP 50 PERCENT * FROM Persons

SQL LIKE Operator-
we want to select the persons living in a city that starts with "s" from the table

SELECT * FROM Persons WHERE City LIKE 's%'

we want to select the persons living in a city that ends with an "s" from the "Persons" table.

SELECT * FROM Persons WHERE City LIKE '%s'

SQL Wildcards Queries---

SELECT * FROM Persons WHERE City LIKE 'sa%'

SELECT * FROM Persons WHERE City LIKE '%nes%'

SELECT * FROM Persons WHERE FirstName LIKE '_la'

SELECT * FROM Persons WHERE LastName LIKE 'S_end_on'

SELECT * FROM Persons WHERE LastName LIKE '[bsp]%'

SELECT * FROM Persons WHERE LastName LIKE '[!bsp]%'

SQL Alias Query

SELECT a.SNAME,a.SMobile,b.AllergyID  from AmitTest.dbo.Student as a,

AmitTest.emr.Allergy as b where a.SID= 1

SQL Joins
SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

The SELECT INTO  statement selects data from one table and inserts it into a different table.
The SELECT INTO statement is most often used to create backup copies of tables.

SELECT LastName,FirstName INTO Persons_Backup FROM Persons

SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.

EX-  a Query for Check Constraint on Multiple Columns.

CREATE TABLE Persons(

    P_Id int NOT NULL,

    FirstName varchar(255),

    City varchar(255),

    CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

    )

The DEFAULT constraint is used to insert a default value into a column.
EX-  this query insert Current date in OrderDate by default

CREATE TABLE Orders(

    O_Id int NOT NULL,

    P_Id int,

    OrderDate date DEFAULT GETDATE()

    )

How to find out which index is defined on table?
Ans: sp_helpindex tablename

Scope_Identity will return the identity value in table that is currently in scope

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE table_name ADD column_name datatype

ALTER TABLE table_name DROP COLUMN column_name

ALTER TABLE table_name ALTER COLUMN column_name datatype (to change datatype of a column)

SQL AUTO INCREMENT Field
Auto-increment allows a unique number to be generated when a new record is inserted into a table.

CREATE TABLE Persons

(

  P_Id int PRIMARY KEY IDENTITY,

  LastName varchar(255) NOT NULL

)

NOW()               Returns the current date and time
CURDATE()     Returns the current date
CURTIME()     Returns the current time

SQL IS NULL and IS NOT NULL
SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

SQL Function

The AVG() function returns the average value of a numeric column.|

SELECT AVG(column_name) FROM table_name

SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

The COUNT() function returns the number of rows that matches a specified criteria.

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The MAX() function returns the largest value of the selected column.

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

The SUM() function returns the total sum of a numeric column.

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

A Trigger is a SQL Procedure that initiates an Action when an event (Insert, Update, Delete) Occurs.

The COALESCE() function returns the first non-null expression in a list.
COALESCE(expr1, expr2, ...., expr_n)

Difference between char, varchar and nvarchar 
char, varchar and nvarchar are SQL Server data type that used for allocated memory in storage device. But there is little difference between them which described below:

char: It stores fixed length of character. For example if you declare char (10) then it allocates memory for 10 characters. If you store 5 character words then it stores it in 5 characters memory location and other 5 character's memory location will be wasted. Per character, it takes 1 Byte memory.

varchar
varchar is flexible data type means it allocates memory as per data stored into this. If you specify varchar(10) at the time of declaration then it allocates 0 memory location at the starting if you specify, it can be null. When you store 5 characters word it will allocate memory for 5 characters and store into that. So there will be no memory loss. It stores only non-unicode characters means for English language only. Per character, it takes 1 Bytes memory.

nvarchar: nvarchar means unicode variable characters. When you want to store international character in database then used nvarchar. It used really because it increases database size twice than varchar datatype because it takes 2 Bytes memory for each character.

Display all tables in a SQL Server database:

USE <YOURDBNAME>
SELECT * from information_schema.tables WHERE  table_type = 'base table'

Copy a table from one database to another in SQL Server 2008
If you have a table in a database and you would like to copy the table to another database,
use this query:

SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers

Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

If you want to transfer all the objects from one database to another,
open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts. Then run these scripts against the new database.

Transfer both schema and data
To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).

When do you use SQL Profiler?
SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..

What do you understand by database tuning advisor?
Ans: Database Engine Tuning Advisor examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning.

SET ANSI_NULLS

Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server.

Click here

Why to avoid naming stored procs with "sp_" prefix - Slows down execution
Causes SQL Server to search for the stored proc in the master db

Suppose that you have 100 lines of query ,this query is executed successfully with in 5 min ,next day again you have to run the same query that time query is take a long time around 1 hour ,So how can you analyze the query..(what happen query performance is decreased)

Ans: You can explain your own way, but what I think that there would be some memory allocation problem. So first I will check is there any other users allocated more memory in the server, if yes try when sever has sufficient memory. Next check when the tables got analyzed. Next check is there any degree of parallelism has changed. There are so many factors causing performance.

How do you optimize queries? 
You can use SQL Profiler to locate long running queries
In general, keep your DBs normalized
Move queries to stored procedures: SPs are compiled and run on the server
Limit your searches as much as possible-only grab what you need
Don't use joins if you don't have to, they are expensive. Try to join on indexed fields if you can.
Add indexes to fields you often filter by, especially if they are an Integer, Boolean, or Number

You can limit the number of rows returned, or possibly do a pagination, so you don't have to get a giant amount of rows.

http://dbtricks.blogspot.com/
http://www.complexsql.com/