Sql Server Interview Questions and Answers (1758) - Page 37

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

ACID rules in sql server?

Atomicity
Consistency
Isolation
Durablity

Atomicity:
Modification on the data in the database either fail or succeed. The beginning of such a modification starts with a transactionand ends when a transaction finishes (either by a commit or arollback). A software crash entails an implicit rollback.
Consisyency:
Modification on the data in the database either fail or succeed. The beginning of such a modification starts with a transactionand ends when a transaction finishes (either by a commit or arollback). A software crash entails an implicit rollback.

Isolation:
One transaction does not interfere with another. The 'executor' of a transaction has the feeling that he has the entire database for himeself.
Durablity:
A commited transaction will not be lost.

Difference between IDENT_CURRENT ,@@IDENTITY and SCOPE_IDENTITY ?

1.When we use the IDENT_CURRENT , it will return the last identity value generated for a specific table in any session and any scope.
2.When we use the @@IDENTITY , it will return the last identity value generated for any table in the current session, across all scopes.
3.When we use the SCOPE_IDENTITY, it will return the last identity value generated for any table in the current session and the current scope.

write a query to select second highest salary of the employee from the employee table?

select * from employee

select top 1 salary from
(select top 2 salary from employee order by(salary) desc )a
order by(salary)

It first select top 2 salary from table and arrange them in descending order now we have the second highest salary in second number then select top 1 by ascending it that is the second highest salary.

why we are using distinct keyword in sql server?

The DISTINCT keyword in SQL allows you to select only those records that contain unique values for the columns requested in a SELECT statement. Duplicate values are ignored and only displayed once.
Ex:- using emp table
Emp ID EmpName
1 Aswini A
2 Aswini A
3 Aluri

then
select distinct EmpName from emp
then it displays
only
EmpName
Aswini A
Aluri

Can we provide more than one primary key to a table?

NOTE: This is objective type question, Please click question title for correct answer.

What is FillFactor?

It defines the amount of empty space to leave in each data page of an index when it is
created. This is only applied at the time an index is created or rebuilt.

What is PAD_INDEX?

It specifies whether the FILLFACTOR for the index should be applied to the nonleaf
data pages for the index. The PAD_INDEX option is used when data manipulation
language operations that lead to excessive nonleaf level page splitting need
to be mitigated.

What is the purpose of IGNORE_DUP_KEY?

Changes the behavior when duplicate keys are encountered when performing
inserts into a table. When enabled, rows violating the key constraint will fail. When
the default behavior is disabled, the entire insert will fail.

What is the use of sys.indexes?

The sys.indexes catalog view provides information on each index in a database. For every table, index, or table-valued function there is one row within the catalog view. This provides a full accounting of all indexes in a database.

The information in sys.indexes is useful in a few ways.

First, the catalog view includes the name of the index. Along with that is the type of the index, identifying whether the index is clustered, nonclustered, and so forth.

Along with that information are the properties on the definition of the index. This includes the fill factor, filter definition, the uniqueness flag, and other items that were used to define the index.

What is the purpose of sys.index_columns catalog view ?

The sys.index_columns catalog view provides a list of all of the columns included in an index. For each key and included column that is a part of an index, there is one row in this catalog view. For each of the columns in the index, the order of columns is included along with the order in which the column is sorted in the index.

What is the importance of sys.xml_indexes?

The catalog view sys.xml_indexes is similar to sys.indexes. This catalog view returns one row per XML index in a database.

The view includes information on whether the XML index is a primary or secondary XML index. If the XML index is a secondary XML index, the catalog view includes a type for the secondary index.

What is the purpose of sys.column_store_dictionaries ?

The sys.column_store_dictionaries catalog view is one of the new catalog views that supports columnstore indexes. This catalog view returns one row for each column in a columnstore index. The data describes the structure and type of dictionary built for the column.

What is the benefit of Column Store Index over Row Store Index?

Traditionally, indexes are stored in rowbased organization, also known as row store. This form of storage is extremely efficient when one row or a small range is requested. When a large range or all rows are returned, this organization can become inefficient.

The column store index favors the return of large ranges of rows by storing data in column-wise organization. When we create a column store index, we typically include all the columns in a table.
This ensures that all columns are included in the enhanced performance benefits of the column store organization. In data warehousing scenarios, often less than 15 percent of the columns in an index are needed for the
results of a query

Why column Store Index are suitable for large data warehouses?

Data modifications like INSERT, UPDATE, and DELETE statements, are disallowed in the case of Column Store Index. For this reason, column store indexes are ideally situated for large data warehouses where the data is not changed that
frequently.

Explain briefly about RowLevel Compression

With row-level compression, an index compresses each record at the row level. When row-level compression is enabled, a number of changes are made to each record. The metadata for the row is stored in an alternative format that decreases the amount of information stored on each column, but because of another change it may actually increase the size of the overhead. The main changes to the records are numerical data
changes from fixed to variable length and blank spaces at the end of fixed-length string data types that are not stored. Another change is that null or zero values do not require any space to be stored.

Explain DBCC EXTENTINFO

The DBCC command DBCC EXTENTINFO provides information about extents allocations that occur within a database. The command can be used to identify how extents have been allocated and whether the extents being used are mixed or uniform.For every extent allocation, there will be one row in the results. Since extents are comprised of eight pages, there can be as many as eight allocations for an extent when there are single page allocations, such as when mixed extents are used. When uniform extents are used, there will be only one extent allocation and one row returned for the extent.DBCC EXTENTINFO can be extremely useful for investigating how pages are allocated to tables and indexes.Using the DBCC command can be extremely useful when trying to investigate issues related to fragmentation and how pages have been allocated.

What is SOUNDEX function?

It compares the field values that sounds like the supplied input word.
SQL> Select * From tblPlayers
2 Where Soundex(BELONGSTO) = Soundex('Lindia');

PLAYERID PLAYERFIRS PLAYERLAST BELONGSTO DOB FEEPERMATCH
---------- ---------- ---------- ---------- --------- -----------
11 P Chopra India 14-FEB-98
1 A Raman India 20-DEC-84 2000
2 B Kadir India 01-JAN-80 1000
3 C Nadir India 21-APR-00 3000
4 D Das India 11-NOV-80 500
5 E Punchkar India 14-FEB-80 12000
6 rows selected.