What is the difference between clustered and non-clustered index?

Clustered Index: A clustered index is a particular type of index that

reorders the way records in the table are physically stored. It gives a
sequence of data which is physically stored in the database. Therefore a
table can have only one clustered index. The leaf nodes of a clustered
index contain the data pages. Index id of the clustered index is 0. So a
primary key constraint automatically creates a clustered index.

Non-clustered Index: A non-clustered index is a particular type of index

in which the logical order of the index does not match the physically
stored order of the rows on disk. In non-clustered index data and indexes
are stored in different places. The leaf node of a non-clustered index does
not consist of the data pages. Instead, the leaf nodes contain index rows.
Index id of non-clustered indexes is greater than 0.
What is the difference between HAVING CLAUSE and WHERE CLAUSE in SQL
Server?
HAVING Clause: HAVING CLAUSE is used only with the SELECT
statement. It is generally used in a GROUP BY clause in a query.
If GROUP BY is not used, HAVING works like a WHERE clause. HAVING
clause can be used with the aggregate function.
What is the recursive stored procedure in SQL Server?
The Recursive stored procedure is defined as a method of problem-solving
wherein the solution arrives repetitively. SQL Server supports recursive
stored procedure which calls by itself. It can nest up to 32 levels. It can be
called by itself directly or indirectly
There are two ways to achieve recursion in the stored procedure:
Mutual Recursion: By Using mutually recursive stored procedure, indirect
recursion can be achieved
o Chain Recursion: If we extend mutual recursion process then we can
achieve chain recursion.

What are the advantages of using stored procedures in SQL Server?

A list of advantages of Stored Procedures:
o Stored procedures help in reducing the network traffic and latency.
It boosts up the application performance.
o Stored procedures facilitate the reusability of the code.
o Stored procedures provide better security for data.
o You can encapsulate the logic using stored procedures and change
stored procedure code without affecting clients.
o It is possible to reuse stored procedure execution plans, which are
cached in SQL Server's memory. This reduces server overhead.
o It provides modularity of application.

Define the one-to-one relationship while designing tables.

One-to-One relationship: It can be implemented as a single table and
rarely as two tables with primary and foreign key relationships

One to one relationship exists if an entity in one table has a link with only
one entity on another table. Let?s take an example of the employee and
their employee id so that a unique employee id will be there for a
particular employee at another table.

What is CHECK constraint in SQL Server?

A CHECK constraint is applied to a column in a table to limit the values

that can be placed in a column. It enforces integrity. After using the check
constraint on the single column, we can only give some specific values for
that particular column. Check constraint apply a condition for each column
in the table.
CREATE TABLE Employee (
EMP_ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Employee CHECK (Age>20AND City= 'Greenville')
);
In which TCP/IP port does SQL Server run? Can it be changed?
SQL Server runs on port 1433. Yes, it can be changed from the network
utility TCP/IP properties.
What is the DBCC command and why is it used?

DBCC stands for database consistency checker. This command is used to

check the consistency of the database. DBCC command help to review
and monitoring the maintenance of tables, database, and for validation of
operations done on the database, etc. For example:
DBCC CHECKDB: It makes sure that table in the database and the
indexes are correctly linked.
DBCC CHECKALLOC: It checks all pages in the database and makes sure
that all are correctly allocated.
DBCC CHECKFILEGROUP: It checks all table file group for any damage.
What command is used to rename the database?
sp_renamedb 'oldname', 'newname';
What are the different types of collation sensitivity in SQL Server?
There are four types of collation sensitivity in SQL Server:
o Case sensitivity
o Accent sensitivity
o Kana Sensitivity
o Width sensitivity
What is the usage of SIGN function?
SIGN function is used to define whether the number specified is Positive,
Negative and Zero. This will return +1,-1 or 0. SIGN function returns the
value with its sign.
SIGN (number)
If the number>0, then it will return +1
If the number=0, then it will return 0
If the number<0, then it will return -1
What is sub-query in SQL server? Explain its properties.
In SQL Server, a query within the main query like Select, Update, Insert or
Delete, is termed as sub-query. It is also called as INNER Query.
A subquery can be Added to WHERE clause, the FROM clause, or the
SELECT clause.
Some properties of the subqueries are given below:
o A sub-query can add WHERE, GROUP BY, and HAVING CLAUSE but it's optional.
o SELECT clause and a FROM clause must be included a subquery.
o A User can include more than one query
o A sub query should not have order by clause
o A sub query should be placed in the right hand side of the comparison operator of
the main query
o A sub query should be enclosed in parenthesis because it needs to be executed first
before the main query
Can we check locks in database? If so, how can we do this lock check?
o Yes, we can check locks in the database. It can be achieved by using in-built stored
procedure called sp_lock.
What is Trigger ? and Types?
Triggers are used to execute a batch of SQL code when insert or update or delete
commands are executed against a table. Triggers are automatically triggered or executed
when the data is modified. It can be executed automatically on insert, delete and update
operations.

 Insert
 Delete
 Update
 Instead of
Select * from [Link] where type='tr'  Will give the list
of the triggers
What is the difference between UNION and UNION ALL?

 UNION: To select related information from two tables UNION command is used. It
is similar to JOIN command.
 UNION All: The UNION ALL command is equal to the UNION command, except that
UNION ALL selects all values. It will not remove duplicate rows, instead it will
retrieve all rows from all tables.
How to get the version of the SQL Server?
Select SERVERPROPERTY('productversion')
What is the use of SET NOCOUNT ON/OFF statement?
By default, NOCOUNT is set to OFF and it returns number of records got affected whenever
the command is getting executed. If the user doesn’t want to display the number of
records affected, it can be explicitly set to ON- (SET NOCOUNT ON).
What is the difference between SUBSTR and CHARINDEX in the SQL Server?
The SUBSTR function is used to return specific portion of string in a given string. But,
CHARINDEX function gives character position in a given specified string.
SUBSTRING('Smiley',1,3)
CHARINDEX('i', 'Smiley',1)
How can you create a login?
You can use the following command to create a login
CREATE LOGIN MyLogin WITH PASSWORD = '123';

What is the use of @@SPID?

A @@SPID returns the session ID of the current user process.

What is the command used to Recompile the stored procedure at run time?
Stored Procedure can be executed with the help of keyword called RECOMPILE.
Example
Exe <SPName> WITH RECOMPILE

How to delete duplicate rows in SQL?

Method 1: Using Max[ID]
View Duplicates:
SELECT *
FROM [SampleDB].[dbo].[Employee]
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM [SampleDB].[dbo].[Employee]
GROUP BY [FirstName],
[LastName],
[Country]
);
To Delete:
DELETE FROM [SampleDB].[dbo].[Employee]
WHERE ID NOT IN
(
SELECT MAX(ID) AS MaxRecordID
FROM [SampleDB].[dbo].[Employee]
GROUP BY [FirstName],
[LastName],
[Country]
);
Method 2: using CTE and Row_Number() function

WITH CTE([firstname],
[lastname],
[country],
duplicatecount)
AS (SELECT [firstname],
[lastname],
[country],
ROW_NUMBER() OVER(PARTITION BY [firstname],
[lastname],
[country]
ORDER BY id) AS DuplicateCount
FROM [SampleDB].[dbo].[employee])
SELECT *
FROM CTE;

How to Delete: WITH CTE([FirstName],

[LastName],
[Country],
DuplicateCount)
AS (SELECT [FirstName],
[LastName],
[Country],
ROW_NUMBER() OVER(PARTITION BY [FirstName],
[LastName],
[Country]
ORDER BY ID) AS DuplicateCount
FROM [SampleDB].[dbo].[Employee])

DELETE FROM CTE

WHERE DuplicateCount > 1;

Using Rank Method:

SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [SampleDB].[dbo].[Employee] E
INNER JOIN
(
SELECT *,
RANK() OVER(PARTITION BY firstname,
lastname,
country
ORDER BY id) rank
FROM [SampleDB].[dbo].[Employee]
) T ON [Link] = [Link];
Using Sort operator in SSIS package
What is the difference between GETDATE and SYSDATETIME?
Both are same but GETDATE can give time till milliseconds and SYSDATETIME
can give precision till nanoseconds. SYSDATE TIME is more accurate than
GETDATE.
Which command is used for user defined error messages?
RAISEERROR is the command used to generate and initiates error processing for
a given session. Those user defined messages are stored in [Link] table.
What do mean by XML Datatype?
XML data type is used to store XML documents in the SQL Server database.
Columns and variables are created and store XML instances in the database.
What is CDC?
CDC is abbreviated as Change Data Capture which is used to capture the data
that has been changed recently. This feature is present in SQL Server 2008.
What is SQL injection?
SQL injection is an attack by malicious users in which malicious code can be
inserted into strings that can be passed to an instance of SQL server for parsing
and execution. All statements have to checked for vulnerabilities as it executes
all syntactically valid queries that it receives.
Even parameters can be manipulated by the skilled and experienced attackers.
What are the methods used to protect against SQL injection attack?
Following are the methods used to protect against SQL injection attack:

 Use Parameters for Stored Procedures

 Filtering input parameters
 Use Parameter collection with Dynamic SQL
 In like clause, user escape characters
What is Filtered Index?
Filtered Index is used to filter some portion of rows in a table to improve query
performance, index maintenance and reduces index storage costs. When the
index is created with WHERE clause, then it is called Filtered Index
How to get the nth Highest salary?
SELECT name, salary FROM #Employee e1 WHERE N-
1 = (SELECT COUNT(DISTINCT salary) FROM #Employee
e2 WHERE [Link] > [Link])

SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N

salary FROM #Employee ORDER BY salary DESC ) AS temp ORDER
BY salary

SELECT salary FROM Employee ORDER BY salary DESC LIMIT N-1, 1

Syntaxes:
Add Column:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'payment_bank_cd_t' AND COLUMN_NAME = 'add1')
BEGIN
ALTER TABLE dbo.payment_bank_cd_t ADD add1 NVARCHAR(100)
END
GO
Drop Column:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'adjustment_info_t' AND COLUMN_NAME = 'syslvl_entry_method')
BEGIN
ALTER TABLE adjustment_info_t DROP COLUMN syslvl_entry_method;
END
DROP TempTable:
IF OBJECT_ID('tempdb..#contract_info_t') IS NOT NULL
BEGIN
DROP TABLE #contract_info_t;
END
Create Proc:
IF EXISTS (SELECT id FROM sysobjects WHERE id =
object_id('sp_starz_international_invoice_format') and OBJECTPROPERTY(id,
'IsProcedure') = 1)
--DROP PROCEDURE sp_starz_international_invoice_format
GO
CREATE PROCEDURE dbo.sp_starz_international_invoice_format
@invoice_nb_in NVARCHAR(6)
AS
Declare
@close_dt_in DATETIME,....
BEGIN
SET @close_dt_in ='2019-08-07 04:50:03.347'
END
GO
GRANT EXECUTE ON [dbo].[sp_bulk_adjust_summary] TO [SYSADMIN]
GO
Create View:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ViewName]'))
DROP VIEW [dbo].[ViewName]
GO
CREATE VIEW [dbo].[ViewName] AS
SELECT * FROM Table_Name;
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[ViewName] TO [SYSADMIN]
GO
 -- NOTE: these are required to avoid PB throwing errors due to the use of the XML type.
they should only persist within the scope of this SP
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

CREATE CURSOR:

DECLARE @date_description NVARCHAR(100),

@contract_nb NVARCHAR(5),
@primary_contract NVARCHAR(5)

DECLARE bundled_contract_cursor CURSOR FOR

SELECT date_description, contract_nb, primary_contract from #bundled_contracts;
--select 'abc','def','ghi'

OPEN bundled_contract_cursor;

FETCH NEXT FROM bundled_contract_cursor INTO

@date_description,@contract_nb,@primary_contract;

WHILE @@FETCH_STATUS = 0
BEGIN
print @date_description
print 'guptha'
FETCH NEXT FROM bundled_contract_cursor INTO
@date_description,@contract_nb,@primary_contract;
END

-- Close
CLOSE bundled_contract_cursor;

DEALLOCATE bundled_contract_cursor;

SELECT TOP 100 dba.schedule_crtc_log.program_id,

Stuff((SELECT ',' + Isnull(c.key_code, '')
FROM dba.program_caption b
JOIN [Link] c
ON b.caption_id = c.caption_id
WHERE b.program_id = dba.schedule_crtc_log.program_id
FOR xml path('')
),
1,
1, ''
 )
FROM dba.schedule_crtc_log

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =

'revc_contract_t' AND COLUMN_NAME = 'last_update_user')
BEGIN
ALTER TABLE revc_trueup_percent_t DROP COLUMN last_update_user ;
ALTER TABLE revc_rate_per_sub_t DROP COLUMN last_update_user ;
ALTER TABLE revc_fixedfee_t DROP COLUMN last_update_user ;
ALTER TABLE revc_station_t DROP COLUMN last_update_user ;
ALTER TABLE revc_contract_t DROP COLUMN
last_update_user ;
END
GO
\
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'revc_contract_t' AND COLUMN_NAME = 'last_update_user_id')
BEGIN
ALTER TABLE revc_trueup_percent_t ADD last_update_user_id NVARCHAR(8) ;
ALTER TABLE revc_rate_per_sub_t ADD last_update_user_id NVARCHAR(8) ;
ALTER TABLE revc_fixedfee_t ADD last_update_user_id NVARCHAR(8) ;
ALTER TABLE revc_station_t ADD last_update_user_id NVARCHAR(8) ;
ALTER TABLE revc_contract_t ADD last_update_user_id
NVARCHAR(8) ;
END
GO

What is Bulk Exception?

What is mutating Trigger?

..
"కాలభైరవాష్టకం"
దేవరాజసేవ్యమానపావనాంఘ్రిపంకజం వ్యాలయజ్ఞసూత్రమిందుశేఖరం కృపాకరమ్ |
నారదాదియోగిబృందవందితం దిగంబరం కాశికాపురాధినాథ కాలభైరవం భజే || 1||
భానుకోటిభాస్వరం భవాబ్ధితారకం పరం
నీలకంఠ మీప్సితార్థ దాయకం త్రిలోచనమ్ |
కాలకాలమంబుజాక్ష మక్షశూలమక్షరం
కాశికాపురాధినాథ కాలభైరవం భజే || 2 ||
శూలటంక పాశదండ పాణిమాదికారణం
శ్యామకాయ మాదిదేవ మక్షరం నిరామయమ్ |
భీమవిక్రమంప్రభుం విచిత్రతాండవప్రియం కాశికాపురాధినాథ కాలభైరవం భజే || ౩
||
భుక్తిముక్తిదాయకం ప్రశస్తచారువిగ్రహం
భక్తవత్సలంస్థిరం సమస్తలోకవిగ్రహమ్ |
నిక్వణన్మనోజ్ఞహేమ కింకిణీలసత్కటిం
కాశికాపురాధినాథ కాలభైరవం భజే || 4 ||
ధర్మసేతుపాలకం త్వధర్మమార్గనాశకం
కర్మపాశమోచకం సుశర్మదాయకం విభుమ్ |
స్వర్ణవర్ణకేశపాశ శోభితాంగనిర్మలం
కాశికాపురాధినాథ కాలభైరవం భజే || 5 ||
రత్నపాదుకాప్రభాభి రామపాదయుగ్మకం నిత్యమద్వితీయ మిష్టదైవతం నిరంజనమ్ |
మృత్యుదర్పనాశనం కరాలదంష్ట్రభూషణం కాశికాపురాధినాథ కాలభైరవం భజే || 6 ||
అట్టహాస భిన్నపద్మ జాండకోశసంతతిం
దృష్టిపాత నష్టపాప జాలముగ్రశాసనమ్ |
అష్టసిద్ధిదాయకం కపాలమాలికాధరం
కాశికాపురాధినాథ కాలభైరవం భజే || 7 ||
భూతసంఘనాయకం విశాలకీర్తిదాయకం
కాశివాసి లోకపుణ్య పాపశోధకం విభుమ్ |
నీతిమార్గకోవిదం పురాతనం జగత్పతిం కాశికాపురాధినాథ కాలభైరవం భజే || 8 ||
కాలభైరవాష్టకం పఠంతియే మనోహరం
జ్ఞానముక్తిసాధకం విచిత్రపుణ్యవర్ధనమ్ |
శోకమోహ లోభదైన్య కోపతాపనాశనం
తేప్రయాంతి కాలభైరవాంఘ్రి సన్నిధింధ్రువమ్ || 9||
How does the below SQL query will be executed?
SELECT p.plan_name,Count(plan_id) AS total_count
FROM plans p
JOIN subscriptions s
ON s.plan_id = p.plan_id
WHERE p.plan_name != 'premium'
GROUP BY p.plan_name
HAVING total_count > 100
ORDER BY p.plan_name
LIMIT 10;

Step 01: Get the table data required to run the sql query
Operations: FROM, JOIN (From plans p, Join subscriptions s)

Step 02: Filter the data rows

Operations: WHERE (where p.plan_name=’premium’)

Step 03: Group the data

Operations: GROUP (group by p.plan_name)

Step 04: Filter the grouped data

Operations: HAVING (having total_count > 100)

Step 05: Select the data columns

Operations: SELECT (select p.plan_name, count(p.plan_id)

Step 06: Order the data

Operations: ORDER BY (order by p.plan_name)

Step 07: Limit the data rows

Operations: LIMIT (limit 100)

What is mutating table error?

A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or
change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In
particular, this error occurs when a row-level trigger attempts to read or write the table from
which the trigger was fired
Fixing the mutating table error
1. First, declare an array of customer record that includes customer id and credit limit.
2. Second, collect affected rows into the array in the row-level trigger.
3. Third, update each affected row in the statement-level trigger.

List all the databases in Microsoft SQL Server?

SELECT name FROM [Link]
EXEC sp_databases