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.

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.

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)

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.

Thank you for providing this series of interview questions and answers.

For the question above on the maximum DB size in SQL Server Express Edition, the 4 GB answer is correct for SQL 2008 and SQL 2005, but has been raised to 10 GB for SQL 2008 R2. See the “Compare Editions” link on the SQL Server product site: http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx under the section on Scalability and Performance.

It will be interesting to see if the maximum DB size stays the same (10 GB) or is increased for SQL Server Express Edition “Denali”.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.