SQL Server Interview Questions (Part-4)

What is a transaction?
A transaction is a logical unit of work in which, all the steps must be performed or none.
Read more in detail...

What are ACID properties
ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.

Difference Between Implict Transaction And Explict Transaction
Implicit Transaction is auto commit and there is no beginning or ending of the transaction.
Explicit Transaction has the beginning, ending and rollback of transactions with the command
Begin Transaction, Commit Transaction and Rollback Transation
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.

What is the use of @@TRANCOUNT in SQL Server?
@@TRANCOUNT returns the number of active transactions for the current connection.

What is a deadlock?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.

What is NOLOCK?
NOLOCK is used to query data which is affected by the transaction  but yet committed. Querying uncommited data using NOLOCK is called "dirty read"

How to Get nth Record in a Table?
Refer this post : find nth highest salary

How to generate Random number in SQL Server?
Refer this post : generate random number for each row in SQL

How to delete duplicate record in SQL?
Refer this post : delete duplicate records in SQL

How do we find the last date of current month?
SQL Server 2012 introduced function EOMONTH to find the last date of the month
In prior versions, we have to write our own logical script
Refer this post : Last day of Month

What is computed column in SQL Server?
Computed Column is the column whose values are generated with an expression. An expression can be a non-computed column name, constant, function and combination of any of these connected by one or more operators. Computed columns can be used in select list, where clause, order by clause.
Read more in detail with example...

What is the use of @@ROWCOUNT?
@@ROWCOUNT returns the number of row(s) affected as a result of DML statement like Insert/Update/Delete.

<< Prev

SQL Server Interview Questions (Part-3)

What is user-defined function
User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

What is the use of COALESCE in SQL Server?
COALESCE accepts "n" no of parameters and returns first not null values from the parameters passed to the function.
Read more in detail with example... 

What is Row_Number()?
ROW_NUMBER() is used to generate a unique row number in the result set.
Read more in detail with example...

What is Apply Operator?
The Apply operator acts like a Join without the ON clause. The Apply function in a query allows you to join a table to a table-valued function in such a way that function is invoked for each row returned from the table
Read more in detail with example...

What is the STUFF function and how does it differ from the REPLACE function?
STUFF function is used to overwrite existing characters.

Syntax

STUFF (string_expression, start, length, replacement_characters)

• string_expression is the string that will have characters substituted
• start is the starting position
• length is the number of characters in the string that are substituted
• replacement_characters are the new characters interjected into the string.
• REPLACE function is used to replace existing characters of all occurrences. 

Syntax 

REPLACE (string_expression, search_string, replacement_string)

Every incidence of search_string found in the string_expression will be replaced with replacement_string.

What is a Stored Procedure?
Stored Procedure in nothing but a set of T-SQL statements combined to perform several set of statements as a single task.

What are the advantages of using Stored Procedures?

• Stored procedure can reduced network traffic and latency, boosting application performance.
• Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
• Stored procedures help promote code reuse.
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
• Stored procedures provide better security to your data.

Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?
1024

What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query.
From within Query Analyzer is an option called "Show Execution Plan"
(located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?
WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure.
Read more in detail with example..

How to pass data table to stored procedure as parameter in SQL Server 2005 (Write stored procedure) ?
Refer this post : Passing Data Table to Stored Procedure

How to achieve paging from SQL side.
Refer this post : Custom Paging

<< Prev Next >>

SQL Server Interview Questions (Part-2)

What is the difference between a Local and a Global temporary table?
Local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What is #temp table and @table variable in SQL Server?
Both temp table and table variable are used to store data temporarily for a particular session.
Both have some similarities and differences.
Read More in detail...

What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
It is called Heap. A heap is a table that does not have any index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data.

What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

What is difference between Delete and Truncate commands?

Delete is a DML Command	Truncate is a DDL Command
"Where" clause can be used to remove partial rows	Can't use "Where" clause
Slow as maintain transaction logs	Faster as transnational log is not maintained
Rollback is possible from log files	Rollback is not possible from log files.
Delete does not reset identity of the table.	Truncate resets the identity of the table
Delete activates Trigger because the operations are logged	Does not activate trigger because operations are not logged.

What is Trigger?
Triggers are special types of stored procedures which are automatically executes when an event occur in database.
Read more in Detail...

How many types of triggers are there?
There are four types of triggers.

1. Insert
2. Delete
3. Update
4. Instead of

Read more in Detail...

What are Magic Tables?
Whenever DML operations insert/update/delete are performed "INSERTED" and "DELETED" are created automatically. These tables are called Magic Tables.
Read more in detail...

<< Prev Next >>

SQL Server Interview Questions (Part-1)

Which TCP/IP port does SQL Server run on?
SQL Server by default runs on port 1433. However it can be changed from the Network Utility TCP/IP properties.

What are the authentication modes available in SQL Server?

• Windows mode 
• Mixed Mode - SQL and Windows. 

When UPDATE_STATISTICS command is used?
The indexes need to be updated when a large no. of DML operations like insertion, deletion and modifications are done.UPDATE_STATISTICS command performs this job.

What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.
Read more in detail...

Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

Difference between char and varchar?
Char takes exact "n" bytes regardless of the data you store in it (with trailing spaces, if the data length is less than "n" where as Varchar takes "x" no. of bytes depending on the data + 2 bytes to store the length of the data.
Read more in detail...

What is the use of Set NOCOUNT ON?
By Default, When a command is executed it returns the number of affected record as result of the command. UsingSET NOCOUNT ON commands will no longer returns the number of affected records in the current scope.

What is an index?
An index is used to speed up the performance of queries. They are created for faster retrieval of data.

What are the difference between clustered and a non-clustered index?
A Clustered index is an index that sorts the data physically on the basis of columns used in clustered index and therefore only one clustered index is possible. The leaf nodes of a clustered index contain the data pages.
A Non Clustered index is an index in which the logical order of the index does not match the physical stored order of the rows. The leaf node of a non clustered index does not consist of the data pages, instead, the leaf nodes contain index rows.

What are Constraint?
SQL Server users constraints restricts only valid data to be inserted into column of a table.
There are following types of constraints.
Primary Key, Foreign Key, Unique, Default, Check, Not Null.

What is PRIMARY KEY?
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

What is FOREIGN KEY?
A FOREIGN KEY constraint allows to create relationship between tables. It ensures that data in FOREIGN KEY column should exists in the referring PRIMARY KEY column thus mainlining the referential integrity

What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the data in a set of columns, so no duplicate values are entered.

What is CHECK Constraint?
A CHECK constraint is used to restrict the set of values that can be entered in a column. The check constraints are used to enforce domain integrity.

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.

WHAT is the difference between a Primary Key and a Unique Key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where as unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULL, but unique key allows one NULL.

Next >>

Temp Table vs Table Variable in SQL

Both temp table and table variable are used to store data temporarily for a particular scope. You can perform all DML operations on both like you perform in a normal table. In terms of querying data they are similar to normal tables except the scope which is limited to a particular session.

Name of temp table starts with "#" and name of table variable starts with "@"

Syntax for Temp Table

CREATE TABLE #temp (id int, name VARCHAR(100))

Syntax for Table Variable

DECLARE @tab TABLE (id int, name VARCHAR(100))

Both have some similarities and differences.

Similarities

• Both are instantiated in TempDB (System Database)
• Clustered index can be created on both Temp Table and Table Variable
• Perform any DML operations like Insert/Update/Delete.

Differences

• Transaction logs are logged for Temp tables but not for Table Variable
• Tables variables can not have non-clustered index
• Indexes can be explicitly created on Temp tables but not on table variable i.e index can be created while creating table only
• Scope of table variable is limited to the Object only in which it is declared while scope of temp variable is the session in which it is created.
• Temp table can be created using "SELECT INTO" but not Table variables.
• Table variables can be used as parameter.
• Default collation for table variable is collation of the current database where as temp tables takes the default collation of tempdb.

Except and Intersect in SQL Server

EXCEPT and INTERSECT operators were introduced in SQL Server 2005. Both operator works on two result-sets.

Basic rules for combining two result-sets

• No. of columns in both result-set should be equal
• The order of columns should be same
• Corresponding data type of each column in each select should be compatible

Syntax

SELECT col1, col2 FROM tab1
EXCEPT
SELECT col1, col2 FROM tab2

EXCEPT returns distinct rows from the first result-set which not exists in second result-set.

INTERSECT returns rows which exists in both result-set

Example

declare @tab1 table (col1 int, col2 varchar(10))
insert into @tab1
select 1, 'val1' union all
select 1, 'val2' union all
select 2, 'val1'

declare @tab2 table (col1 int, col2 varchar(10))
insert into @tab2
select 1, 'val1' union all
select 1, 'val3' union all
select 3, 'val1'

select col1, col2 from @tab1
except
select col1, col2 from @tab2

select col1, col2 from @tab1
intersect
select col1, col2 from @tab2

OUTPUT

Look at the Output

• First output using EXCEPT where we are getting 2 rows which exist in table 1 but not in table 2
• Second output using INTERSECT where we are getting 1 row which exists in both tables.

Row_Number in SQL Server

Row_Number (window function) was introduced in SQL Server 2005.

Row_Number generates a run-time column in the result set which generates sequential number to each row according to the column used in order by clause.

Syntax

Row_Number() over (order by col1, col2, ....n partition by col1, col2, ....n)

• Order By is mandatory. Row Number is assigned in the result set based on the column provided in Order By clause.
• Partition By is optional which groups the result set based on the column provided in Partition By clause wherein each group, the sequence starts with 1.

Let's create an Employee table on which we would see how it works.

create table tblEmployee(
    EmpId char(7)
    , FirstName varchar(50)
    , LastName varchar(50)
    , Gender char(1)
)

insert into tblEmployee
select 'EMP0001', 'Sandeep', 'Mittal', 'M' union all
select 'EMP0003', 'Abhay', 'Kumar', 'M' union all
select 'EMP0005', 'Priya', 'Gupta', 'F' union all
select 'EMP0002', 'Reema', 'Gupta', 'F' union all
select 'EMP0004', 'Ritesh', 'Kumar', 'M'

Now, we would generate a sequential number for all the employees using Row_Number.

select  EmpId, FirstName, LastName, Gender
        , row_number() over (order by EmpId) as RowNum
from    tblEmployee

OUTPUT

Check out the result set where RowNum is generated based on EmpId as provided in Order By.

Multiple columns can be used in the Order By clause.

Let's take an another example where we would use multiple columns in Order By clause.

select  EmpId, FirstName, LastName, Gender
        , row_number() over (order by FirstName, LastName) as RowNum
from    tblEmployee

OUTPUT

In the next example, we will see how Partition By clause works

select  EmpId, FirstName, LastName, Gender
        , row_number() over (partition by Gender order by EmpId) as RowNum
from    tblEmployee

OUTPUT

Check out the result set where RowNum is generated starting from 1 for each group i.e. Gender which is provided in Partition By clause

In Partition By clause also, we can use multiple columns as in Order By clause

Collation in SQL Server

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Types of collation sensitivity

• Case Sensitivity
• Accent Sensitivity
• Kana Sensitivity
• Width Sensitivity

Case Sensitivity
If A and a, B and b, etc. are treated in the same way, then it is case-insensitive.
A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

• CI specifies Case Insensitive 
• CS specifies Case Sensitive

Accent Sensitivity
If a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.

• AI specifies Accent Insensitive 
• AS specifies Accent Sensitive

Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width Sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Query to check collation and compatibility level of a database

SELECT compatibility_level, collation_name
FROM   sys.databases
WHERE  name = 'DatabaseName'

Query to find out all available collations in SQL Server database

SELECT    [name]
        , COLLATIONPROPERTY([name], 'CodePage') AS [CodePage]
        , COLLATIONPROPERTY([name], 'LCID') AS [LCID]
        , COLLATIONPROPERTY([name], 'ComparisonStyle') AS [ComparisonStyle]
        , COLLATIONPROPERTY([name], 'Version') AS [Version]
        , [description]
FROM    fn_helpcollations()

In the next article, we will explore,
common issues encountered with collation

Output Clause in SQL Server 2005

SQL Server 2005 introduced a new TSQL feature OUTPUT clause that allows you to retrieve data affected by insert/update/delete statements easily. The  OUTPUT clause returns the data that you've inserted or deleted from the table within the statement.

Basically OUTPUT clause has the access to magic tables (inserted and deleted) like triggers. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table  Let us take examples to understand how OUTPUT works.

First create a table Employee

CREATE TABLE Employee(
    EmpId VARCHAR(10), EmpName varchar(10), DOJ DATETIME
)

Example 1: Displaying the data just inserted with output clause within the same insert statement.

INSERT INTO Employee
OUTPUT inserted.*
SELECT 'EMP001', 'Sandeep', '01/01/2008' UNION ALL
SELECT 'EMP002', 'Abhay', '06/01/2008'

OUTPUT

In the above example we have inserted two rows in the Employee table and with OUTPUT clause we are displaying the rows we have just inserted from the same insert statement.

Example 2: Insert the data in other table on insertion and deletion of a table

DECLARE @TAB TABLE(
    EmpId VARCHAR(10), EmpName varchar(10), DOJ DATETIME, Action CHAR(1), LogTime DATETIME
)
INSERT INTO Employee
OUTPUT inserted.*, 'I', GETDATE() INTO @TAB
SELECT 'EMP003', 'Ritesh', '09/01/2008'

DELETE Employee
OUTPUT deleted.*, 'D', GETDATE() INTO @TAB
WHERE EmpId = 'EMP003'

SELECT * FROM @TAB

OUTPUT

In the above example first we have inserted and then deleted a row in the Employee table and with OUTPUT clause we have inserted both the inserted and deleted rows in the table variable from the inserted and deleted tables respectively. 

Coalesce in SQL Server

Coalesce function accepts "n" number of arguments and returns the first non-null expression of the arguments. If all the arguments are null then it returns null.

Syntax

select coalesce(p1, p2, p3.....n)

Let's take example to understand more clearly how coalesce function works

select coalesce(null, 1)
select coalesce(null, null, 1)
select coalesce(null, null, 1, null)
select coalesce(1, 2)

OUTPUT

Look at the output, we are getting value as "1" in each output because in all select statements "1" is first non-null value in the arguments.

NOTE: At least one of the null values must be a typed NULL.

select coalesce(null, null)

OUTPUT

In the above select statement we are passing NULL as value in all arguments and NULL are not typed, so we are getting the error.

Now, let's try with NULL values as typed

declare @i int
select coalesce(null, @i)

OUTPUT

In this Example, it worked fine without any error because values of the argument are still NULL but at least one of them is typed.

Coalesce can be used in place of following case expression

case when expression1 is not null then expression1
     when expression1 is not null then expression1
     ...
     when expressionN is not null then expressionN
end

Let's take an example to show how coalesce can be used in place of case expression

declare @tab1 table(id int, value varchar(10))
insert into @tab1 values (1, 'val1')
insert into @tab1 values (2, null)
insert into @tab1 values (3, null)
insert into @tab1 values (4, null)
declare @tab2 table(id int, value varchar(10))
insert into @tab2 values (1, null)
insert into @tab2 values (2, 'val2')
insert into @tab2 values (3, null)
insert into @tab2 values (4, null)
declare @tab3 table(id int, value varchar(10))
insert into @tab3 values (1, null)
insert into @tab3 values (2, null)
insert into @tab3 values (3, 'val3')
insert into @tab3 values (4, null)

select  t1.id
        , case when t1.value is not null then t1.value
               when t2.value is not null then t2.value
               when t3.value is not null then t3.value
          end as [value using case]
        , coalesce(t1.value, t2.value, t3.value) as [value using coalesce]
from    @tab1 t1
inner join @tab2 t2 on t1.id = t2.id
inner join @tab3 t3 on t1.id = t3.id

OUTPUT

Computed Column in SQL Server

Today, on one of my post related to Auto generation of Id, a reader asked the meaning of PERSISTED PRIMARY KEY that is related with Computed Column. I answered the question on the post itself, but it gave me an idea to have a detailed post on computed column on my blog.

Computed Column is the column whose values are generated with an expression. An expression can be a non-computed column name, constant, function and combination of any of these connected by one or more operators.
Computed columns can be used in select list, where clause, order by clause.

There are two types of computed column.

• Persisted Columns: Persisted columns values are calculated at the time of insertion and updation (when any columns that is part of their calculation changes) and are physically stored in the table. Indexes can be created on persisted columns as they are physically stored.
• Non-Persisted Columns: Non-persisted columns are calculated on run-time every time they are referenced in a query. Indexes cannot be created on Non-persisted columns as they are not physically stored.

Some examples of Computed column

Example 1: Auto generation of Id 

Example 2:  Calculate Date of Retirement 
Date of retirement is dependent on column Date of Birth as Date of retirement is calculated as (DOB + 60 years), so we are creating DOR as computed column to compute the same

create table tblEmployee(     
    EmpId    varchar(10)
    , DOB    datetime
    , DOR    as (dateadd(yy, 60, DOB)) persisted
)
insert into tblEmployee (EmpId, DOB)
select 'EMP0001', '01/1/1980' union all
select 'EMP0002', '04/1/1981' union all
select 'EMP0003', '03/06/1977'

select * from tblEmployee

OUTPUT

Example 3: Calculate total (price * qty) using function

create function dbo.udf_CalclateTotal (@price int, @qty int)
returns int
as
begin
    return @price*@qty
end

create table tblSales(
      OrderId    int identity   
      , Price    int
      , Qty      int
      , Total as (dbo.udf_CalclateTotal(Price, Qty))
)

insert into tblSales (Price, Qty)
select 8, 3 union all
select 5, 5 union all
select 2, 50

select * from tblSales

OUTPUT