SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 20 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 are Policy Management Terms?

To have a better grip on the concept of Policy-based management, there are some key terms you need to understand.

Target – A type of entity that is appropriately managed by Policy-based management. For example, a table, database and index, to name a few.

Facet -A property that can be managed in policy-based management. A clear example of facet is the name of Trigger or the Auto Shrink Property of database.

Conditions – Criteria that specifies the state of facet to true or false. For example, you can adjust the state of a facet that gives you clear specifications of all stored procedures in the Schema ‘Banking’.

Policy – A set of rules specified for the server objects or the properties of database.

(Read more here)

What is the ‘FILLFACTOR’?

A “FILLFACTOR” is one of the important arguments that can be used while creating an index.

According to MSDN, FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.

Specifying a fill-factor value of 70 would imply that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table. The fill-factor setting applies only when the index is created or rebuilt. (Read more here)

Where in MS SQL Server is ’100’ equal to ‘0’?

Fill-factor settings of 0 and 100 are equal! (Read more here)

What are Points to Remember while Using the FILLFACTOR Argument?

1. If fill-factor is set to 100 or 0, the Database Engine fills pages to their capacity while creating indexes.
2. The server-wide default FILLFACTOR is set to 0.
3. To modify the server-wide default value, use the sp_configure system stored procedure.
4. To view the fill-factor value of one or more indexes, use sys.indexes.
5. To modify or set the fill-factor value for individual indexes, use CREATE INDEX or ALTER INDEX statements.
6. Creating a clustered index with a FILLFACTOR < 100 may significantly increase the amount of space the data occupies because the Database Engine physically reallocates the data while building the clustered index. (Read more here)

What is a ROLLUP Clause?

ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. If we want sum on different levels without adding any new column, then we can do it easily using ROLLUP. We have to just add the WITH ROLLUP Clause in group by clause. (Read more here)

What are Various Limitations of the Views?

• ORDER BY clause does not work in View.

(Read more here)

• Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, then we will have to modify them first.

(Read more here)

• Index created on view not used often.

(Read more here)

• Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed.

(Read more here)

• One of the most prominent limitations of the View it is that it does not support COUNT (*); however, it can support COUNT_BIG (*).

(Read more here)

• UNION Operation is now allowed in Indexed View.

(Read More Here)

• We cannot create an Index on a nested View situation means we cannot create index on a view which is built from another view.

(Read More Here)

• SELF JOIN Not Allowed in Indexed View.

(Read more here)

• Outer Join Not Allowed in Indexed Views.

(Read more here)

• Cross Database Queries Not Allowed in Indexed View.

(Read more here)

What is a Covered index?

It is an index that can satisfy a query just by its index keys without having needed to touch the data pages.

It means that when a query is fired, SQL Server doesn’t need to go to the table to retrieve the rows, but can produce the results directly from the index as the index covers all the columns used in query. (Read more here)

When I Delete any Data from a Table, does the SQL Server reduce the size of that table?

When data are deleted from any table, the SQL Server does not reduce the size of the table right away; however, it marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for sometime, the background process de-allocates the pages, finally reducing the page size. (Read more here)

What are Wait Types?

There are three types of wait types, namely,

Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker or it’s not yet available.

Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.

External Waits. External waits occur when an SQL Server worker is waiting for an external event. (Read more here)

How to Stop Log File Growing too Big?

If your Transaction Log file was growing too big and you wanted to manage its size, then instead of truncating transaction log file, you should choose one of the options mentioned below.

1) Convert the Recovery Model to Simple Recovery

If you change your recovery model to Simple Recovery Model, then you will not encounter the extraordinary growth of your log file. However, please note if you have one long running transaction it will for sure grow your log file till the transaction is complete.

2) Start Taking Transaction Log Backup

In this Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits.

(Read more here)

If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?

No, we can’t see definition of encrypted stored procedure in Activity Monitor.(Read More Here)

List of all the Interview Questions and Answers Series blogs

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