SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31

Click here to get free chapters (PDF) in the mailbox

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

What is Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY for filtering query using aggregate values.

The following functions are aggregate functions.

AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP (Read more here )

What is Use of @@ SPID in SQL Server?

A SPID is the returns sessions ID of the current user process. And using that session ID, we can find out that the last query was executed. (Read more here)

What is the Difference between Index Seek vs. Index Scan?

An index scan means that SQL Server reads all the rows in a table, and then returns only those rows that satisfy the search criteria. When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially means that all the rows of the index are examined instead of the table directly. This is sometimes compared to a table scan, in which all the table data is read directly. However, there is usually little difference between an index scan and a table scan.

An index seek, on the other hand, means that the Query Optimizer relies entirely on the index leaf data to locate rows satisfying the query condition. An index seek will be most beneficial in cases where a small percentage of rows will be returned. An index seek will only affect the rows that satisfy a query condition and the pages that contain these qualifying rows; in terms of performance, this is highly beneficial when a table has a very large number of rows. (Read more here)

What is the Maximum Size per Database for SQL Server Express?

SQL Server Express supports a maximum size of 4 GB per database, which excludes all the log files. 4 GB is not a very large size; however, if the database is properly designed and the tables are properly arranged in a separate database, this limitation can be resolved to a certain extent.

(Read more here)

How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?

In one way, it is quite easy to figure this out by just looking at the result set; however, this method cannot be relied upon every time as it is difficult to reach a conclusion when there are many columns and many rows.

It is easy to measure how much data is retrieved from server to client side. The SQL Server Management Studio has feature that can measure client statistics. (Read more here)

What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?

In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users. (Read more here)

How to Create Primary Key with Specific Name while Creating a Table?

CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1)NOTNULL,
[FirstName] [varchar](100)NULL,
CONSTRAINT [PK_TestTable] PRIMARYKEYCLUSTERED
([ID] ASC))
GO

(Read more here)

What is T-SQL Script to Take Database Offline – Take Database Online?

-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO

(Read more here)

How to Enable/Disable Indexes?

--Disable Index
ALTER INDEX [IndexName] ON TableName DISABLE
GO
--Enable Index
ALTER INDEX [IndexName] ON TableName REBUILD
GO

(Read more here)

Can we Insert Data if Clustered Index is Disabled?

No, we cannot insert data if Clustered Index is disabled because Clustered Indexes are in fact original tables which are physically ordered according to one or more keys (Columns).
(Read more here)

How to Recompile Stored Procedure at Run Time?

We can Recompile Stored Procedure in two ways.

Option 1:

CREATE PROCEDURE dbo.PersonAge(@MinAge INT, @MaxAge INT)
WITH RECOMPILE
AS
SELECT*
FROM dbo.tblPerson
WHERE Age <= @MinAge AND Age >= @MaxAge
GO

Option 2:

EXEC dbo.PersonAge65, 70 WITHRECOMPILE

We can use RECOMPILE hint with a query and recompile only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.

This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan. (Read more here)

Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?

There is no performance difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table). (Read more here)

What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?

INSERT TOP (N) INTO Table is faster than Using Top with INSERT but when we use INSERT TOP (N) INTO Table, the ORDER BY clause is totally ignored. (Read more here)

Does the Order of Columns in UPDATE statements Matter?

No, the order of columns in UPDATE statement does not matter for results updated.

Both the below options produce the same results.

Option 1:

UPDATE TableName
SET Col1 ='Value', Col2 ='Value2'

Option 2:

UPDATE TableName
SET Col2 ='Value2', Col1 ='Value'

(Read more here)

List of all the Interview Questions and Answers Series blogs

Reference: Pinal Dave (https://blog.sqlauthority.com)