Interview Questions

SQL Server T-SQL Interview Questions

Question : What are the two commands to remove all of the data from a table? Are there any implications with the specific commands?
The DELETE command.
The TRUNCATE command.
In terms of implications, a few different issues could occur:
With the DELETE or TRUNCATE command, you will lose all of your data in a table.
A single DELETE command could fill up the transaction log since it is a single transaction.
A TRUNCATE command could cause issues for Log Shipping since it is a minimally logged operation.

Question: What are the three ways that Dynamic SQL can be issued?
Writing a query with parameters.
Using EXEC.
Using sp_executesql

Question: True or False – SQL Server can format the date in over 10 different patterns.
True – With the CONVERT command there are over 15 different date formats such as MM/DD/YY, MM DD, YY, DD-MM-YY, etc.

2. How do we handle Error?
Ans: I think we can use @@Error. Right after the query condition is executed we can check for @@Error <> 0, if it is not returning zero mean some error occured. Raiserror is another command for raising error We can also use Try catch block

What is PatIndex?
Ans: Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found
Syntax – PATINDEX ( ‘%pattern%’ , expression )

SELECT PATINDEX(‘%ensure%’, Character_Column_For_Search)
FROM Table_Name
WHERE

How to query a string contains %?
Ans: SELECT Name FROM tblPlayer WHERE Name Like ‘%[”%”]’

How to get values from a table with comma seperated?
Ans: declare @vName nvarchar(100)
set @vName = ”
select @vName = @vName + ‘,’+ [Name] from HumanResources.Shift
select @vName

How to update ‘Yes’ to ‘No’ and viceversa in a query?
Ans: Update tablename set ColumnName1 = (case ColumnName1 when ‘Yes’
then ‘No’else ‘Yes’ end)

Consider you have a table with columns ID(primary key), Country and State.
Now if you have some rows with combination of country and state repeating,
ie, two rows with combination India, Kerala. Write a query for deleting
duplicate records?

Ans: With T1 as
(Select *,Row_Number() over (partition by Country, State order by ID)
as ‘RowNo’ From TableName)
Delete from T1 where RowNo > 1;

How to create temporary table? How do we apply noncluster index? What is nolock? When and where is nolock applied normally?

Ans. Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply “nolock”. nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.

1.
CREATE TABLE #tmpTable
(
OfficeName varchar(50)
, officeid int
, CustID int
, AgentID int
, mlsid varchar(4)
, RequestMoreDetails int null
, Emails int null
)
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID)

2.
select
OfficeName
, officeid
, o.CustID
, AgentID –
, o.mlsid
, PrintBrochure_Views = null
, RequestMoreDetails = null
, Emails = null
into #ForOffices from #Offices o
LEFT JOIN dbo.planparts WITH (NOLOCK)
ON bppa.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID)

Difference between stored procedure and function

1) Procedure can return zero or n values whereas function can return one value which is mandatory.
2) Procedures can have input, output parameters for it whereas functions can have only input parameters.
3) Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
4) Functions can be called from procedure whereas procedures cannot be called from function.
5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6) We can go for transaction management in procedure whereas we can’t go in function.
7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

CAST versus CONVERT in SQL Server

1. CONVERT is SQL speicific but CAST is ANSI standarised
2. CONVERT deal nicely with datetime using format, which is missing from CAST

Q. What is the maximum limit of SQL Server instances for a standalone computer?
Ans:
· 50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.

Q. Can we install SQL Server using a configure file?
Ans:
Yes! We can prepare a configuration file. While installing SQL Server the path to the configuration file is specified in the “Ready to Install” page in the configuration file path section. Cancel the setup without actually completing the installation, to generate the INI file.
File Location and Name:
%programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\\ConfigurationFile.ini.

Q. What are the top performance counters to be monitor in Performance Monitor?
Ans:
Processor\%Processor Time: Monitoring CPU consumption allows you to check for a bottleneck on the server (indicated by high sustained usage).
High percentage of Signal Wait: Signal wait is the time a worker spends waiting for CPU time after it has finished waiting on something else (such as a lock, a latch or some other wait). Time spent waiting on the CPU is indicative of a CPU bottleneck. Signal wait can be found by executing DBCC SQLPERF (waitstats) on SQL Server 2000 or by querying sys.dm_os_wait_stats on SQL Server 2005.
Physical Disk\Avg. Disk Queue Length: Check for disk bottlenecks: if the value exceeds 2 then it is likely that a disk bottleneck exists.
MSSQL$Instance: Buffer Manager\Page Life Expectancy: Page Life Expectancy is the number of seconds a page stays in the buffer cache. A low number indicates that pages are being evicted without spending much time in the cache, which reduces the effectiveness of the cache.
MSSQL$Instance: Plan Cache\Cache Hit Ratio: A low Plan Cache hit ratio means that plans are not being reused.
MSSQL$Instance:General Statistics\Processes Blocked: Long blocks indicate contention for resources.

sql> SELECT * FROM runners;
+—-+————–+
| id | name |
+—-+————–+
| 1 | John Doe |
| 2 | Jane Doe |
| 3 | Alice Jones |
| 4 | Bobby Louis |
| 5 | Lisa Romero |
+—-+————–+

sql> SELECT * FROM races;
+—-+—————-+———–+
| id | event | winner_id |
+—-+—————-+———–+
| 1 | 100 meter dash | 2 |
| 2 | 500 meter dash | 3 |
| 3 | cross-country | 2 |
| 4 | triathalon | NULL |
+—-+—————-+———–+
What will be the result of the query below?
SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races)

CREATE TABLE dbo.envelope(id int, user_id int);
CREATE TABLE dbo.docs(idnum int, pageseq int, doctext varchar(100));

INSERT INTO dbo.envelope VALUES
(1,1),
(2,2),
(3,3);

INSERT INTO dbo.docs(idnum,pageseq) VALUES
(1,5),
(2,6),
(null,0);

What will the result be from the following query:
UPDATE docs SET doctext=pageseq FROM docs INNER JOIN envelope ON envelope.id=docs.idnum
WHERE EXISTS (
SELECT 1 FROM dbo.docs
WHERE id=envelope.id
);

What is wrong with this SQL query? Correct it so it executes properly.
SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE BillingYear >= 2010;

Given these contents of the Customers table:
Id Name ReferredBy
1 John Doe NULL
2 Jane Smith NULL
3 Anne Jenkins 2
4 Eric Branford NULL
5 Pat Richards 1
6 Alice Barnes 2
Here is a query written to return the list of customers not referred by Jane Smith:
SELECT Name FROM Customers WHERE ReferredBy <> 2;
What will be the result of the query? Why? What would be a better way to write it?

SELECT * FROM users;

user_id username
1 John Doe
2 Jane Don
3 Alice Jones
4 Lisa Romero

SELECT * FROM training_details;

user_training_id user_id training_id training_date
1 1 1 “2015-08-02”
2 2 1 “2015-08-03”
3 3 2 “2015-08-02”
4 4 2 “2015-08-04”
5 2 2 “2015-08-03”
6 1 1 “2015-08-02”
7 3 2 “2015-08-04”
8 4 3 “2015-08-03”
9 1 4 “2015-08-03”
10 3 1 “2015-08-02”
11 4 2 “2015-08-04”
12 3 2 “2015-08-02”
13 1 1 “2015-08-02”
14 4 3 “2015-08-03”

SELECT
u.user_id,
username,
training_id,
training_date,
count( user_training_id ) AS count
FROM users u JOIN training_details t ON t.user_id = u.user_id
GROUP BY user_id,
training_id,
training_date
HAVING count( user_training_id ) > 1
ORDER BY training_date DESC;
user_id username training_id training_date count
4 Lisa Romero 2 August, 04 2015 00:00:00 2
4 Lisa Romero 3 August, 03 2015 00:00:00 2
1 John Doe 1 August, 02 2015 00:00:00 3
3 Alice Jones 2 August, 02 2015 00:00:00 2

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’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.
In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” (located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is run.