SQL Interview Questions Archives - Coding Compiler | Programming Tutorials

SQL Server DBA Tutorial – Part 3

SQL Server Backup Tutorial from Coding compiler | SQL Server DBA Tutorial – Part 3, this blog is the third tutorial in the series, here we are going to learn about these below topics. Before going to learn this tutorial, we recommend you to go through the SQL Server DBA Tutorial – Part 1 and SQL Server DBA Tutorial – Part 2.

Topics Covered in this SQL Server DBA Tutorial

Introduction to Backups
Recovery Models
Backup Types
Advanced Backup Types
Situations of Tail Log Backup

Tricky SQL Server Interview Questions And Answers For Experienced

SQL Server Interview Questions And Answers For Experienced 2018. Here Coding compiler sharing a comprehensive list of 250 SQL Server Interview Questions. This list is prepared by the industry expert SQL Server professionals, it will help you to crack your next SQL Server job interview. These complex SQL Server questions were asked in many top MNC companies like Infosys, IBM, TCS, Etc. Let’s start learning SQL Interview Questions and Answers, all the best for your future and happy SQL learning.

SQL Server Interview Questions

What is a candidate key?
What are alternate keys?
What is a primary key?
What is the difference between a primary key and a unique key? Are they the same?
What is referential integrity?
What is an Entity-Relationship Diagram (ERD)?
What is a subquery?
What is a correlated subquery?
How do you use a subquery to find records that exist in one table and do not exist in another?
What does it mean to normalize a database and why would you do it?
What is denormalization?

SQL Server Interview Questions And Answers

Here is the list of 250 SQL Server interview questions and answers for freshers and experienced. Start learning now.

General RDBMS Interview Questions And Answers

Q. What is a candidate key?

A. A candidate key is one that can uniquely identify each row of a table. Generally, a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key and the rest are called alternate keys.

Q. What are alternate keys?

A. Any candidate keys not chosen as the primary key.

Q. What is a primary key?

A. A primary key is a special candidate key. It is an attribute or minimal set of attributes that not only uniquely identifies a specific occurrence of the entity, but also exists for every occurrence of an entity. The difference between a primary key and any other unique key is that SQL server automatically uses the primary key definition in various Referential integrity constructs.

In other words, the primary key is a field (or set of fields) that uniquely identifies each record of a table. A table can have only one key declared as primary. If a primary key consists of more than one column, duplicate values are allowed in one of the columns, but the combination of values from all the columns in the primary key must be unique.

Q. What is the difference between a primary key and a unique key? Are they the same?

A. No, these two are two different keys. By default, a primary key creates a clustered index on the column and unique keys create a nonclustered index. Primary keys do not allow NULL values, but unique keys allow one NULL value. However, both primary and unique keys enforce uniqueness of the column on which they are defined. You can use primary key constraints to enforce uniqueness as well as referential integrity. Defining a primary key on a table helps to relate that table to other tables. These relationships help to normalize a database. A table can have only one primary key.

Q. What is referential integrity?

A. Referential integrity is a relationship that requires that all foreign key column values must match to a primary key column value.

Q. What is an Entity-Relationship Diagram (ERD)?

A. An Entity Relationship Diagram (ERD) is a way to graphically describe the way tables and columns are connected in your database. Entities equate to tables, and relationships describe the connection between two entities, i.e. one-to-one, one-to-many, and many-to-many. A relationship works by matching data in key columns.

Basic SQL Server Interview Questions And Answers

Q. What are the types of relationships between tables?

A. There are three possible types of relationships between tables. The type of relationship depends on how the related columns are defined. The three types of relationships are:

One-to-Many Relationships

Many-to-Many Relationships

One-to-One Relationships

Q. What is One-to-Many Relationships?

A. A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in Table A can have many matching rows in Table B, but a row in Table B can have only one matching row in Table A. For example, the Sales and Stores tables have a one-to-many relationship with each other: each Store can have many sales, but each sale can have only one store. This relationship uses the Stor_ID field for its key field.

Q. What is Many-to-Many Relationships?

A. In a many-to-many relationship, a row in Table A can have many matching rows in Table B, and vice versa. In SQL Server, you create such a relationship by defining a third table, called a “junction table”, whose primary key consists of the foreign keys from both Table A and Table B. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the Titleauthors table. The primary key of the Titleauthors table is the combination of the au_id column (the Authors table’s primary key) and the title_id column (the Titles table’s primary key).

Q. What is One-to-One Relationships?

A. In a one-to-one relationship, a row in Table A can have no more than one matching row in Table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

Q. What is a subquery?

A. A subquery is a nested select statement where the inner select statement is evaluated first. You can use the results of the inner select statement as the input for another select statement. For example, you can use the results of a subquery as a search condition that uses the IN ( ) function or EXISTS operator:

USE northwind

SELECT * FROM Orders WHERE orderId IN
(SELECT orderId FROM [Order Details]
WHERE OrderId = 10248)

USE pubs
SELECT * FROM authors WHERE au_id
NOT IN (SELECT au_id FROM titleauthor)

Q. What is a correlated subquery?

A. A correlated subquery is a subquery where the inner query is evaluated once for every value returned by the outer query. Generally, a query which has a subquery will execute the subquery once and substitute the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values.

For example, using the northwind database, if you want to find out the price of all the books ordered, you can use a correlated subquery. Price information about each book is in the [Order Details] table, and the Orders table has all the books ordered.

SELECT O.OrderID, O.OrderDate,

(SELECT MAX (Od.UnitPrice) FROM [Order Details] AS Od WHERE Od.OrderID = O.orderid) AS MaxUnitPrice
FROM Orders AS O

The subquery runs once for each row that the main query returns. This repeated access to the [Order Details] table could be inefficient. Generally, correlated subqueries can be re-written as a query using a join.

Q. How do you use a subquery to find records that exist in one table and do not exist in another?

A. Use a subquery with the NOT IN clause or NOT EXISTS clause.

For example, Find out authors who do not have any records in the titleauthor table?

USE pubs
SELECT * FROM authors WHERE au_id
NOT IN (SELECT au_id FROM titleauthor)

Prefered method is now an ANSI outer join where outside join table value is NULL.

SELECT a.*
FROM a
LEFT OUTER JOIN b
ON a.joinColumn = b.joinColumn
WHERE b.joinColumn IS NULL

Q. What does it mean to normalize a database and why would you do it?

A. Normalization is the process of organizing data in the most optimized way. This includes creating tables and establishing relationships between those tables to eliminate redundancy and inconsistant data.

There are rules for database normalization. Each rule is called a “normal form”. If the first rule is observed, the database is said to be in “first normal form”. If the first three rules are observed, the database is considered to be in “third normal form”. Although additional levels of normalization are possible, third normal form is considered the highest level necessary for most applications. If a database adheres to the first rule and the third rule, but is not in accordance with the second rule, it is still considered in the first normal form.

Benefits of Normalization:

Minimizes amount of space required to store the data by eliminating redundant data.
Minimizes the risk of data inconsistencies within a database.
Minimizes the introduction of possible update and delete anomalies.
Maximizes the stability of the data structure.

Q. What is First Normal form (1NF)?

A. First normal form eliminates repeating groups of data through the creation of separate tables of related data. You can reduce entities to first normal form by removing repeating or multivalued attributes to another child entity.

Q. What is Second Normal Form (2NF)?

A. Second normal form requires that each non-key attribute be fully dependent upon the entire primary key.

Q. What is Third Normal Form (3NF)?

A. Third normal form requires that each non-key attribute depend on the entire primary key (exactly like 2NF) and nothing but the primary key .

To achieve 3NF eliminate fields that do not depend on the key.

Q. What is Boyce/Codd normal form (BCNF)?

A. An entity is in BCNF if and only if all attributes are determined only by each full candidate (primary or alternate) key, and not by any other subset of candidate keys.

Q. What is Fourth Normal Form (4NF)?

A. Fourth normal form is a form of normalization that is not commonly used which further separates any multivalued attributes that may exist in tables by separating them into child table.

Q. What is denormalization?

A. Denormalization is the opposite of normalization process. It is deliberately introducing redundancy into a table design. In some situation, redundancy helps query performance as the number of joins can be reduced, and some calculated values can be kept in a column reducing the overhead of doing calculations with every query. An example of denormalization is a database that stores the quartely sales figures for each item as a separate piece of data. This approach is often used in data warehouse or in on-line analytical processing applications.

SQL Designing And Programming Interview Questions And Answers

Q. How can I detect whether a given connection is blocked?

A. A connection is blocked when it requires an object that another connection is using and has a lock on. One way is to use SQL Enterprise; Click on Node Management to expand it, then expand Current Activity by clicking on it, then click on Locks. Once you will click on Lock/Process ID, it will show all the Locks on the right hand side window.

Q. How would you design a database for an online site, which would average a million hits a day?

A. This is an open ended question to check your understanding about the subject. The interviewer wants to see how inquisitive you are and how well you prepare before you jump on the design board. The interviewer certainly expects some more questions in reponse from you to see your analytical ability.

Before you answer any thing you must ask, about the application. What kind of application it is supporting? Is it an On-line Transaction Processing (OLTP) or Decision Support System (DSS) application? What time of day is the traffic is highest? Is the traffic evenly distributed, or does it come and go in peaks? What is the spike threshold the site is expected to endure? What is the size of the database? How many tables will be there? What is the hardware configuration? Is it preexisting, or is the interviewer expecting you to design that as well? How many hard disks you will have? What is the performance expectation? What hardware is available? Are the servers running clustering or SAN technology?

To approach this question, first get all the facts about the application and the system, and then begin reciting the best practices of database design.

For a good database design there are many things to be considered, but few of them are of paramount importance, namely :

– Normalization and appropriate denormalization of the database

– Proper indexing of the tables

– Optimum use of the stored procedures, functions etc.

– Distribution of data tables, their indicies and transaction logs on different hard disks

– Proper choice of data types

After basic design with the help of a performace monitor, use the Index Tuning Wizard and Profiler to further refine the design to a more detailed level.

Above all, make sure you are desiging the right system. It would be unfortunate to ramble on for 15 minutes about all the correct things for a SQL Server implementation only to find out that the database will reside on a legacy server running Linux.

Most Asked SQL Server Interview Questions And Answers

Q. You are testing the performance of a query. The first time you run the query, the performance is slow. The second time you run the query, the performance is fast. Why is this?

A. The second query is faster bacause SQL server caches the data, and keeps the query plans in memory.

Q. What you can do to remove data from the cache and query plans from memory for testing the performance of a query repeatedly?

A. If you want to run your query and test its performance under the same circumstances each time, use command DBCC DROPCLEANBUFFERS after each run to remove the data from the memory and DBCC FREEPROCCACHE to remove the query plans from the memory.

Q. What is wrong in the following SQL statement?

UPDATE customer
SET c.name = u.name
FROM customer c, customerdetail cd
WHERE c.custid = cd.custid

A. In update clause, once an alias is defined, you must use that alias in the update clause or an error will be generated. The correct statement is below.

UPDATE c
SET c.name = u.name
FROM customer c, customerdetail cd
WHERE c.custid = cd.custid

Q. Write the fastest query to find out how many rows exist in a table.

A. The fastest query for this purpose is:

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID (tablename)
AND indid < 2

With this query, SQL does not have to do a full table scan or an index scan to find out the number of rows in a table. The rows column in the sysindexes table keeps the current rowcount dynamically for the clustered index. If table has a clustered index then indid = 1. If no clustered index exists then indid = 0 for the table.

The statement

SELECT COUNT(*) FROM TableName
requires a full table scan or full clustered index scan to determine the total number of rows. Recall that the interviewer asked for the fastest query that would return the results. SELECT COUNT(*)… will return the correct answer, but it will not be the fastest.

Q. The COUNT() function always returns a int value type. What should you do if you need to count rows from a query which you know will return a value that is too large for an int value type?

A. If you think that the count function will be returning rows more than 2^31 -1, then you can use the new function COUNT_BIG, which always returns a big integer value type.

Q. What is a derived table?

A. A derived table is just another name for the result of using another SELECT statement in the FROM clause of a SELECT statement. The result of an inner SELECT statement is a table, which is exactly what the FROM clause requires.

SELECT *
FROM (SELECT * FROM authors) as T
WHERE T.city LIKE ‘san%’

Q. You have a table with three columns: Amount1, Amount2, and Amount3. In a single row only one of these three columns will have a value and the other two will be null. Write a SQL query to retrieve the values from these columns.

A. Use the COALESCE function.

SELECT ‘Amount’= COALESCE (Amount1, Amount2, Amount3)
FROM TableName

COALESCE is equivalent to a searched CASE expression where a NOT NULL expression1 returns expression1 and a NULL expression1 returns expression2. In searched CASE expression form, it would look like this:

CASE

WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2
END

COALESCE returns the first non-null value from the list.

Q. Write a query to get all of the employees and all of the orders that were processed this year. Employees who have no orders should also be displayed.

A. Use this query:

SELECT e.EmpID, e.FirstName, e.LastName, o.Orders FROM Orders o

RIGHT OUTER JOIN Employee e ON
o.EmpID = e.EmpID
AND DATEPART(YYYY, OrderDate) = DATEPART(YYYY, GetDate())
ORDER BY EmpID

Q. How can you fix a poorly performing query?

A. Some general issues that would cause a query to perform poorly that you could talk about would be: no indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, having procedures and triggers without the SET NOCOUNT ON directive, unnecessarily complicated joins, excessive normalization of the database, or inappropriate or unnecessary usage of cursors and temporary tables.

Some of the tools and techniques that help you troubleshoot performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, and the graphical execution plan in Query Analyzer.

Q. What is the physical representation for a many-to-many relationship?

A. A many-to-many relationship is usually represented by a third table that resolves all valid combinations of the two entities.

For example, consider a hospital, where a patient can be treated by different doctors and a doctor can have many patients. If we have two tables, defining Patients and Doctors each, we need a third table to define the relationship between Patients and Doctors.

Common SQL Server Interview Questions And Answers

Q. What is the maximum length of an extended property?

A. The value of an extended property is a sql_variant. It can contain up to 7,500 bytes of data. (MRG: Add infomraiotn about extended properties)

Q. In which database can extended stored procedures be added?

A. Extended stored procedures can only be added to the master database. In SQL Enterprise Manager, the master database must be selected to activate the extended stored procedures option. Only a person in the sysadmin role can add extended stored procedures.

Extended stored procedures may also be installed via sp_addextendedproc, which will register the procedure in the master database.

Q. Why does this query return 0?

SELECT (3/4) * 100

A. Because 3/4 (.75) is converted into integer, but the result is less than one, therefore it becomes 0. To make this query work use

SELECT (3.0/4) * 100

SELECT (CAST (3 AS DOUBLE) / (4 AS DOUBLE) ) * 100

Q. If a column is an image value type, how you can compare column values? How you can use this column in join clause?

A. You cannot use the equal (=) sign to compare a column of type image. You have to use substring function.

E.g.

SELECT d.emp, e.empname
FROM dept d
INNER JOIN Employee e ON
SUBSTRING(d.empNo,1,30) = SUBSTRING(e.empno,1,30)

Q. Which data type cannot be used as a parameter in a UDF?

A. The timestamp data type and user-defined data types are not supported.

Q. Which four data types cannot be used as a return type from a user-defined function?

A. text, ntext, image, and timestamp.

Q. Can an extended stored procedure be called from inside a user-defined function?

A. Yes, however, the extended stored procedure, when called from inside a function, cannot return result sets to the client. Any ODS (open data services) APIs that return result sets to the client will return FAIL.

Q. How you can make a parameterized view?

A. Inline table-valued functions can be used to achieve the functionality of parameterized views.

CREATE FUNCTION CustomerNamesInRegion ( @RegionParameter nvarchar(30) )
RETURNS table
AS

RETURN ( SELECT CustomerID, CompanyName FROM Northwind.dbo.Customers WHERE Region = @RegionParameter
)
GO

SELECT *
FROM CustomerNamesInRegion(N’WA’)
GO

The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable because it is set by the format of the result set of the SELECT statement in the RETURN clause.

There is no function_body delimited by BEGIN and END.

The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.

SQL Server Query Interview Questions

Q. Write a SQL Query to sort on different column name according to the parameters passed in the function.

CREATE FUNCTION [ListProducts] (@OrderBy int)
RETURNS TABLE
AS

RETURN
(
SELECT productID, ProductName FROM Products
ORDER BY
CASE WHEN @OrderBy = 1 THEN ProductID END,

CASE WHEN @OrderBy = 2 THEN ProductName

END

)

select * from NorthWind.dbo.ListProducts (2)

Q. What is the downside of using UDF?

A. User defined functions are powerful and flexible; however, UDF’s use row-by-row processing, which can be very expensive. Let us see it in detail with an example:

If I have a function, which gives me the day of the week

CREATE FUNCTION GetDayofWeek (@Date Datetime, @Day int)
RETURNS CHAR(12)
BEGIN
RETURN ( SELECT CONVERT (CHAR, DATEADD(dd,-(DATEPART(dw, @Date
) – @Day ), @Date), 101))
END

if I execute these two statements, one with UDF, one with out it.

select top 100 Empid, dbo.GetDayOfWeek (JobDate, 1 ) from Timesheet

select top 100 Empid,

CONVERT (CHAR, DATEADD(dd,-(DATEPART(dw, JobDate ) – 1 ), JobDate), 101)
from Timesheet

SQL Indexes Interview Questions

Q. What is an index?

A. An index is a structure, which provides an efficient method for locating a particular row or set of rows.

It is commonly used to enforce uniqueness constraints.

Q. What is the preferred way to create a clustered and non-clustered index? Which index should you create first the clustered or non-clustered?

A. The preferred way to build indexes on tables is to start with the clustered index and then build the nonclustered indexes. When dropping all indexes, drop the nonclustered indexes first and the clustered index last. That way, no indexes need to be rebuilt.

If a clustered index is created on a table with several secondary indexes, all of the secondary indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Likewise, if a clustered index is deleted on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation.

Q. Can a unique index be created on a column, which contains NULL?

A. if a column contains NULL in more than one row, you cannot create a unique index on that column. NULL values are treated as duplicate values for indexing purposes. Therefore, if there is only one row with a null value, a unique index can be created on it, however if more than one row has a NULL value, then a unique index can not be created.

Q. How would you choose between a clustered and a non-clustered index?

A. A clustered index determines the storage order of data in a table. A table can contain only one clustered index.

Before creating clustered indexes, understand how your data will be accessed.

Consider using a clustered index for:

• Columns that contain a limited number of distinct values, such as a state column that contains only 50 distinct state codes. However, if there are very few distinct values, such as only 1 and 0, no index should be created.

• Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.

• Columns that are accessed sequentially.

• Queries that return large result sets.

• Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns.
• Column(s) specified in the ORDER BY or GROUP BY clause. This eliminates the need for SQL Server to sort the data because the rows are already sorted.
• OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.

Clustered indexes are not a good choice for:

• Columns that undergo frequent changes. When columns change that are part of a clustered index, the entire row moves because SQL Server must keep the rows data values in physical order. This is an important consideration in high-volume transaction processing systems where data tends to be volatile.

• Covered queries. The more columns within the search key, the greater the chance for the data in the indexed column to change, resulting in additional I/O.

Q. Your table has a large character field. There are queries that use this field in their search clause. What should you do?

A. Creating indexes on very large fields is not advisable. SQL Server 2000 provides a new function CHECKSUM, which computes a checksum on a row or a column, and its value is always a 4 byte integer. You can create a computed column to be the checksum of your large character field and then build an index on that computed column. The values returned by CHECKSUM are not guaranteed to be absolutely unique, but there will be few duplicates. Since there is the possibility of two character string values having the same value for the checksum, your queries will need to include the full string that you’re looking for.

DROP INDEX titles.titleind
GO

ALTER TABLE titles
ADD check_title AS CHECKSUM(title)
GO

CREATE INDEX check_title_index ON titles(check_title)
GO

SELECT *
FROM titles
WHERE title = ‘Straight Talk About Computers’
AND check_title =
CHECKSUM(‘ Straight Talk About Computers’)

SQL Server Interview Questions PDF

Q. What is a fill factor?

A. When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table data and reduce the potential for page splits. The fill factor value is a percentage from 0 to 100 that specifies how much to fill the data pages after the index are created.

Q. When you should use a low fill factor?

A. A large number of unique values and data is updated often dictate that the fill factor should be low and the index should be non-clustered.

If data is changing very frequently, keep the fill factor low (75%) so less page splits will take place, which will increase the performance.

Q. What are statistics?

A. Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these statistics in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

1) If there have been significant changes in the key values in the index

2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated

3) The database has been upgraded from a previous version

SQL 2000 provides auto updating of statistics.

Q. What is clustering?

A. Clustering is physically ordering the rows of table to match the order of the index.

Q. What is the difference between DBCC INDEXDEFRAG and DBCC REINDEX?

A. DBCC REINDEX drops the index and creates the index again. DBCC INDEXDEFRAG is an online operation, it does not hold long-term locks that can block running queries or updates. With INDEXDEFRAG the index is always available, unlike DBREINDEX.

DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX on a relatively unfragmented index, but a large amount of fragmentation can cause INDEXDEFRAG to run considerably longer than DBREINDEX, which may or may not outweigh the benefit of its online capabilities.

To improve the clustering of pages, rebuild the index.

DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. It can rebuild all of the indexes for a table in one statement.

DBCC DBREINDEX is not supported for use on system tables.

Q. How you can find out if an index is useful to the optimizer?

A. DBCC SHOW_STATISTICS (table, target)

The results returned indicate the selectivity of an index and provide the basis for determining whether an index is useful to the optimizer. The lower the density returned, the higher the selectivity.

Q. Where are full-text indexes stored?

A. Full-text indexes are stored in the server’s file system. Regular SQL indexes are stored in the database in which they are defined.

Q. How many full-text indexes can a table have?

A. One. Each table can have only one full-text index. Each table can have several regular SQL indexes.

Q. Indexes are updated automatically. Is the full-text index also updated automatically?

A. No. Full-text indexes are not updated automatically.

Q. How is a full-text index updated?

A. Full-text indexes are not updated automatically. The stored procedure sp_fulltext_catalog must be run to update the full-text indexes. Regular SQL indexes are updated automatically as data is modified.

Full-text indexes are grouped into a catalog of full-text indexes for the database in which the index is defined. Regular SQL indexes are not grouped.

Full-text indexes are created, managed, and dropped via stored procedures.

SQL Server Interview Questions By Shivprasad Koirala

Q. Can you force a query to use a specific Index?

A. Yes, by using index hints.

SELECT fname, lname
FROM emps (INDEX (idx_fname, idx_lname))

Q. Which data type columns are the best candidates for full-text indexing?

A. Datatypes text and varchar are the best candidates for full-text indexing.

Q. When would you prefer to have a minimum number of indexes?

A. In an OLTP application, you want a minimum number of indexes. An online transaction processing (OLTP) database is typically characterized by having a large number of concurrent users’ actively adding and modifying data.

Indexes must be updated each time a row is added or modified. To avoid over indexing heavily updated tables, keep indexes narrow.

Q. Why is there a performance difference between two similar queries where one uses UNION and the other uses UNION ALL?

A. The UNION query has to sort all of the results and remove any duplicate rows, this step is performed even if there are no duplicated rows in the results. The UNION ALL query bypasses the sort step and will use the indexes you have on the individual tables.

The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. Result sets combined using UNION must have the same structure. They must have the same number of columns, in the same order, and the corresponding result set columns must have compatible data types.

Q. What is the difference between OPENROWSET and OPENQUERY?

A. Both of these functions execute a pass-through Query on the given linked server. OPENROWSET, Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. Both the functions can be referenced in the FROM clause of a query as though it is a table name. Although the query may return multiple result sets, but these functions returns only the first one.

OPENQUERY is the preferred way, OPENROWSET has to have connection information, password, and user name hard coded into T-SQL, which makes it vulnerable to breaking, if ever the connection information changes. Writing passwords in connection string text also increase the chance of security breaches.

Q. How you can add messages to the NT event log from within a stored procedure?

A. XP_logevent will log a user-defined message into the SQL Server™ log file and the Microsoft Windows NT Event Viewer. xp_logevent can also be used to send an alert without sending a message to the client.

Q. What are three ways you can use an identity value inside a trigger? Why would you prefer one way over another?

A. There are three ways to get an Identity value. IDENT_CURRENT(), SCOPE_IDENTITY() and @@IDENTITY. All three return last-generated identity values. However, the scope and session make the result different in each of these functions.

Q. How can I get the name of the first day of the month using SQL Query?

A. SELECT DATENAME (dw, CAST(DATEPART ( mm,GETDATE()) AS CHAR(2)) + ‘/1’ + ‘/’ + CAST(DATEPART (YYYY,GETDATE()) AS CHAR(4)))

Q. How can I return the name of last day of the current month using SQL Query?

A. This is a little more interesting. The easiest way to do this is with a small trick. Divide and conquer. If the current month is December, return the DATENAME value for 12/31 concatentated with the current year. If the month is not December, return the value for the first of the next month, then substract one to return the last day of the previous month. This will return the last day of the current month and will automatically take into account leap years and leap centuries as well as months that just do not have 31 days.

SELECT
CASE
WHEN DATEPART ( mm,GETDATE()) = 12 THEN

DATENAME(dw, CAST( ’12/31/’ + CAST((DATEPART (YYYY,GETDATE())) AS VARCHAR(4)) AS DATETIME) – 1)
ELSE

DATENAME(dw, CAST(CAST((DATEPART ( mm,GETDATE())+1) AS VARCHAR(2)) + ‘/1’ + ‘/’ +
CAST((DATEPART (YYYY,GETDATE())) AS VARCHAR(4)) AS DATETIME) – 1)
END

SQL Views Interview Questions And Answers

Q. List some of the rules that apply to creating and using a ‘view’.

A. When creating and using a view, these rules apply:

CREATE VIEW statements cannot be combined with other SQL statements in a single batch.

You cannot create a trigger on a view.

Data modification statements (INSERT or UPDATE) are allowed on multi-table views if the data modification statement affects only one base table. Data modification statements cannot be used on more than one table in a single statement.

INSERT statements are not allowed if a computed column exists within the view.

All column(s) being modified must adhere to all restrictions of the base table. This applies to column nullability, constraints, identity columns, and columns with rules and/or defaults and base table triggers.

UPDATE statements cannot change any column in a view that is a computation, nor can they change a view that includes aggregate functions, built-in functions, a GROUP BY clause, or DISTINCT.

You cannot use READTEXT or WRITETEXT on text or image columns in views.

Q. You added a row to a view, but the row is not shown on the view.

Explain how this can happen, and how you can remedy the situation.

A. By default, data modification statements on views are not checked to determine whether the rows affected will be within the scope of the view. You can issue an INSERT statement on a view to add a row to the underlying base table, but not to add it to the view. Similarly, you can issue an UPDATE statement that changes a row so that the row no longer meets the criteria for the view. If all modifications should be checked, use the WITH CHECK option.

For example, if you insert a value of ‘AAA’ into a view that only returns values that are LIKE ‘B%’. This would not be allowed if the WITH CHECK option was in place.

SQL Query Interview Questions And Answers

Q. Can an ORDER BY clause be used in a creation of a view?

A. Yes, but only if the TOP clause is also used.

For example:

CREATE VIEW [ORDERBYVIEW] AS
SELECT TOP 10 customerID, companyname
FROM customers
ORDER BY companyname

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Q. ‘Order by’ is not allowed in a view. How can you sort information from a view?

A. Using ‘SELECT TOP’ you can have a view sorted in the desired order, for e.g.

CREATE VIEW vAuthors AS
SELECT TOP 100 PERCENT au_lname, au_fname
FROM Authors ORDER BY au_lname
GO

SELECT * vAuthors

FROM GO

Q. What is a derived Table?

A. Derived tables are a SELECT statement that you use in a FROM clause in place of tables references.

Transact-SQL has extensions that support the specification of objects other than tables or views in the FROM clause. These other objects return a result set, or rowset that form a virtual table. The SELECT statement then operates as if the result set were a table.

These virtual tables can help you in executing certain queries that are not possible without having a view or a temp table.

SELECT au_lname, au_fname , royaltyper
FROM (SELECT A.au_lname, A.au_fname, TA.royaltyper
FROM Authors A
JOIN TitleAuthor TA on
A.au_id = TA.au_id
WHERE state = ‘CA’)
ORDER BY au_lname

Q. What are Information Schema Views?

A. Information Schema Views are objects that provide indirect access to the data in the system catalogs.

SQL server provides a number of views for accessing the system catalogs. These objects provide meta data and system level information from the server. You should always use these views instead of querying system catalog tables directly.

These views are ANSI SQL-92 compliant, so you can depend up on them, even if SQL server internal tables or their structure will change; these views should remain consistent.

You must refer to these views using INFORMATION_SCHEMA database schema. These views reside in MASTER database, but they run in the context of the current database.

USE pubs
SELECT *
FROM INFORMATION_SCHEMA.TABLES

Q. What is a partitioned view?

A. A partitioned view is a view that unites tables together making the data appear as it is from one table. For example for a large internet site may use a partitioned view to unite twelve tables for the year. Every month would have a separate table. Using portioned views would allow these tables to be treated as a single table.

There are two types of partitioned views: Local Partitioned View (LPV) and Distributed Partitioned View (DPV). In a local partitioned view, all participating tables and the view reside on the same instance of SQL Server. In a distributed partitioned view, at least one of the participating tables resides on a different (remote) server.

Q. What is an Indexed View?

A. A View is a conceptual table that is sometimes called a virtual table. Views do not store any data. The result set of a non-indexed view is not stored permanently in the database. Each time a query references the view, SQL Server dynamically merges the logic needed to build the view result set with the logic needed to build the complete query result set from the data in the base tables.

The overhead of dynamically executing query and building the result set can be substantial for views that involve complex processing of large numbers of rows.

Indexed views have the answer to this. If complex views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored.

SQL Server DBA Interview Questions

Q. How you can get a list of all the table constraints in a database?

A. SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.

See the question on ANSI SQL Schema view for detail.

SQL Server Administration Interview Questions

Q. How you can get the list of largest tables in a database?

A. System table ‘sysindexes’ has all the information, you need.

SELECT object_Name (id) , dpages * 8
FROM sysindexes
WHERE indid in (1,0) AND objectproperty (id, ‘IsUserTable’) = 1 ORDER BY rowcnt desc

Q. How you can move data or databases between servers and databases in SQL Server?

A. Some of the options you have are: BACKUP/RESTORE, detaching and attaching databases, replication, DTS, BCP, log shipping, INSERT…SELECT, SELECT…INTO, creating INSERT scripts to generate data.

Q. If no size is defined while creating the database, what size will the database have?
A. The size of the model database determines the initial size of a database if no size is indicated in the CREATE DATABASE statement.

Q. Can a database be shrunk with users active?

A. Yes. Users can be working in the database when it is shrunk. This includes system databases.

Q. How can you set the database to single user mode and restrict the access to dbo use only?

A. In SQL Server 2000, a database cannot be in single-user mode with dbo use only. Instead, the following alternative options are available by using the ALTER DATABASE command:

ALTER DATABASE database SET SINGLE_USER.

This command restricts access to the database to only one user at a time.

ALTER DATABASE database SET RESTRICTED_USER.

This command restricts access to the database to only members of the db_owner, dbcreator, or sysadmin roles.

ALTER DATABASE database SET MULTI_USER.

This command returns access to the database to its normal operating state.

Q. As a general practice, it is recommended to have dbo be the owner of all database objects. However, in your database you find number of tables owned by a user other than dbo, how could you fix this?

A. One alternative is

DECLARE
@TableName varchar (50)

DECLARE TableCur CURSOR FOR
SELECT NAME FROM sysobjects WHERE xtype = ‘U’

OPEN TableCur

FETCH NEXT FROM TableCur into @TableName WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @TableName = ‘OLDUSERNAME.’ + @TableName EXEC sp_changeobjectowner @TableName , ‘dbo’ FETCH NEXT FROM TableCur into @TableName
END

CLOSE TableCur
DEALLOCATE TableCur

SQL Server DBA Interview Questions And Answers For Experienced

Q.How you can list all the indexes for a table in a database?

DECLARE
@TABLENAME varchar(255)
select @TABLENAME =’Application’

select sysindexes.name, ‘TableName’ = sysobjects.name, ‘INDEX-TYPE’ =

CASE
WHEN indid = 1 THEN ‘clusterd’
WHEN indid > 1 THEN ‘non-clustered’

END
from sysindexes JOIN sysobjects on
sysindexes.id = sysobjects.id
where sysobjects.name = @TABLENAME
and sysobjects.xtype = ‘U’
and indid < 255

Q. Why does a SQL statement work correctly outside of a user-defined function, but incorrectly inside it?

A. You may have included a statement in the BEGIN-END block that has side effects, which are not allowed in user-defined functions. Function side effects are any permanent changes to the state of a resource that has a scope outside the function. Changes can be made only to local objects such as local cursors or variables. Examples of actions that cannot be performed in a function include modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification.

Q. Can a table be moved to different Filegroup?

A. Once a table is created it cannot be moved to a different file group.

Q. Can a database be shrunk to 0 Bytes, if not, why?

A. A database cannot be shrunk smaller than the Model system database, which starts at 1.5 MB.

SQL Server does not shrink a database to a size any smaller than the size that was specified in the CREATE DATABASE statement.

Q. What does the Automatic recovery do?

A. The automatic recovery process is initiated when the SQL Server service starts to insure that all databases are consistent. It rolls forward any committed transactions that have not been written to the database and rolls back any uncommitted transactions.

Q. Can an automatic recovery be initiated by a user?

A. Automatic recovery is performed by the SQL Server Service when the SQL server restarts, It cannot be initiated by a user.

Q. What is the primary use of the model database?

A. The model database is used as a template for all new user databases. Users do not use the model database. Template can include specific settings, security constructs and all sorts of useful stuff.

Q. What information is maintained within the msdb database?

A. The msdb database contains information about tasks, alerts, and operators for both user-defined tasks and tasks related to replication.

SQL Server Administration Interview Questions And Answers

Q. What stored procedure can you use to display the current processes?

A. The stored procedures sp_who and sp_who2 can be used to display the current processes as well as which process is blocking another process.

Q. What stored procedure would you use to view lock information?

A. The stored procedure sp_lock can be used to obtain information about all locks or about locks affiliated with a specific process ID.

Q. How can a database be repaired?

A. DBCC CHECKDB is the safest repair statement because it catches and repairs the widest possible range of errors. If only allocation errors are reported for a database, execute DBCC CHECKALLOC with a repair option to correct them.

DBCC CHECKDB is a superset of DBCC CHECKALLOC and includes allocation checks in addition to checks of index structure and data integrity.

DBCC CHECKTABLE checks the linkages and sizes of text, ntext and image pages for the specified table. However, DBCC CHECKTABLE does not check the allocations of pages in the specified table. Use DBCC CHECKALLOC to check page allocations.

DBCC CHECKTABLE requires a shared lock on all tables and indexes in the database for the duration of the operation.

To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

Q. How can you find out if the current user is a member of the specified Microsoft® Windows NT® group or Microsoft SQL Server™ role?

A. The Is_Member function.

IS_MEMBER ({group | role})

Q. Your SQL Server is running out of disk space. You notice that there are several large files with LDF extensions. What are these files?

A. LDF files are the database log files.

Q. You notice that the transaction log on one of your databases is over 4GB. The size of the data file is 2MB. What could cause this situation, and how can you fix it?

A. A large transaction log can mean several things. It is helpful to know if the log grew slowly over time or all at once. It is possible that an exceptional number of transactions occurred quickly, thereby increasing the size of the log. It is also possible that the log was set up to grow automatically and has never been truncated or shrunk. Should check for a stuck transaction too.

Q. You accidentally delete the MSDB database. What effect does this have on your existing SQL databases, and how do you recover?

A. Your ability to use SQL Server Agent to schedule alerts, jobs, and recording operators has been eliminated. To recover from this situation, restore the MSDB database from a backup.

Q. Where can you add custom error messages to SQL Server?

A. Custom messages can be added to the sysmessages table in the master database.

Q. Is it important for a Database Administrator to understand the operating system and file access?

A. You should answer this one honestly for you. Different companies have different philosophies on this, and no answer is correct for all situations.

Q. What is the difference between writing data to mirrored drives versus RAID5 drives.

A. The difference is performance. RAID5 drives can be much slower than writing to a mirrored drive set. This is due to the overhead of computing and writing the check data that is needed for recovery.

Important SQL Queries Asked in Interviews

Q. In the physical file layout, where should the transaction log be stored in relation to the data file?

A. For up to the minute restorations, the transaction log should be stored on a different physical drive as the data file.

Q. You have separate development and production systems. You want to move a copy of a development database into production. To do this, you do a backup on the development system and restore to the production system. After a few minutes, you begin getting calls from several customers saying that they are denied access to the system. Why?

A. When the restore happened, you also restored the security from the development system onto the production system. The customers do not have access to the development system. One way to avoid this is to script out all the security in the production database prior to doing the restore and then to reapply the security after the restore.

Q. What is a mixed extent?

A. A mixed extent is a single extent that contains multiple tables. Extents are the basic unit in which space is allocated to tables and indexes.

To make its space allocation efficient, SQL Server does not allocate entire extents to tables with small amounts of data. SQL Server has two types of extents:

Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owner object.

Mixed extents are shared by up to eight objects.

Q. You have a table with close to 100 million records. Recently, a huge amount of this data was updated. Now, various queries against this table have slowed down considerably. What is the quickest option to remedy the situation?

A. Run the UPDATE STATISTICS command.

UPDATE STATISTICS will update the statistics for the table. The sp_updatestats stored procedure will update all the tables in the database, which is not required in the situation. There is no need to recreate the index, as the updating statistics should be first step to see the query reached to the optimized level of speed.

Once the statistics have been updated, the query analyzer will be able to make better decisions about which index to use.

If Auto update Statitics is on, then you don’t have to do any of this.

Q. How can you check the level of fragmentation on a table?

A. DBCC SHOWCONTIG. To display fragmentation information for the data and indexes of the specified table, use DBCC SHOWCONTIG. See SQL Server Books On-Line for additional information.

Q. You have developed an application which uses many stored procedures and triggers to update various tables. Users ocassionally get locking problems. Which tool is best suited to help you diagnose the problem?

A. SQL Server Profiler. Use SQL Server Profiler with a trace to observe Lock conflicts.

Q. Which table keeps the locking information?

A. SysLockInfo contains information on all granted, converting, and waiting lock requests. This table is a denormalized tabular view of internal data structures of the lock manager and is stored only in the master database.

Q. You want to be sure that queries in a database always execute at the maximum possible speed. To achieve this goal you have created various indexes on tables. Which other statement will keep the database in good condition?

A. Execute the Update Statistics command for tables that are accessed in the query. Update Statistics updates information about the distribution of key values for one or more statistics groups in the specified table. UPDATE STATISTICS is run automatically when an index is created on a table that already contains data.

The stored procedure Sp_Updatestats will update statistics for all the tables in the current database.

Complex SQL Server Interview Questions And Answers

Q. During a recent migration project, John inserted 10,000 records in a table that has an Identity Column called ticketID, which automatically increases by 1 each time a record is inserted in the table. A month after the database went live; John noticed that record with ticketID 5123 has some incorrect information. So John deletes this record and decides to re-insert this record in the table. He wants to re-use the ticketID 5123. He needs to achieve this while the database is in production. What should he do?

A. John can do this with the following code:

SET IDENTITY_INSERT Tickets ON
GO

INSERT INTO Tickets ( ticketID, FirstName, LastName)
Values (5123, ‘John,Doe)
GO

Set IDENTITY_INSERT Tickets ON allows explicit values to be inserted into the identity column of a table.

Q. Jenny wants to export data to Pivot table in Excel spreadsheet from a table in SQL Server. This data changes frequently. She wants to automate the process of updating the Excel spreadsheet using the SQL Job Scheduler. What tool is the best choice for the task?

A. Use Data Transformation Services (DTS) to populate the spreadsheet. Using DTS you can import and export data between heterogeneous sources. DTS is covered in depth later in the book.

Q. You have written an application in VB which uses SQL 7 for its database. You have used many stored procedure and triggers to make your application fast. However, users complain that saving records take too much time. To rectify the problem, you start the profiler and create a trace using the trace wizard. How would you go about identifying the cause of the problem?

A. Find the worst performing query. As you know from the users that the problem is in while saving the record, find the worst performing query. You can create a trace that captures events relating to T-SQL event classes, specifically RPC:Completed and SQL:BatchCompleted. Include all data columns in the trace, group by duration, and specify event criteria, for example, that the duration of the event must be at least 1,000 milliseconds. This event criterion eliminates short-running events from the trace. The duration minimum value can be increased as required. If you want to monitor only one database at a time, specify a value for the database ID event criteria. Use the find the worst performing queries option in the Create Trace Wizard to automatically create this trace definition.

Q. You have a table with employee information that rarely changes. However this table is used from many applications in the organization to validate the data and to produce reports. What would be the optimal fill factor to choose for indexes created on this table?

A. 100. Tables that rarely change can use fill factor of 100%. More frequent change requires low fill factor. If you set fill factor to 100, SQL Server creates both clustered and nonclustered indexes with each page 100 percent full. Setting fill factor to 100 is suitable only for read-only tables, to which additional data is never added.

The default for fill factor is 0. Smaller fill factor values cause SQL Server to create new indexes with pages that are not full. For example, a fill factor value of 10 is a reasonable choice if you are creating an index on a table that you know contains only a small portion of the data that it will eventually hold. Smaller fill factor values cause each index to take more storage space, allowing room for subsequent insertions without requiring page splits.

Since the table in question keeps the employee information and will be changed rarely, only when an employee leaves the company or a new employee joins the company, which is a rare occurrence not a daily or weekly occurrence. It is safe to have this table with 100% fill factor.

Q. What is the difference between a fill factor of 100 and 0?

A. The fill factor value is a percentage from 0 to 100 that specifies how much to fill the data pages after the index is created. A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table. A value of 0 behaves like 100 but leaves some extra space in the upper level of the data tree.

Q. How will you know when statistics on a table are obsolete?

A. Looking at the query plan and look at the numbers for ‘Estimated Row count’ and ‘Row Count’, if the difference is very big, say more than double or three times, it means the statistics are obsolete on the table. You should run update statistics on the table to make the statistics current.

Q. Explain different backup plans.

A. Full database backup, which backs up the entire database including the transaction log.

Differential database backup performed between full database backups. You cannot perform a differential backup, if you have not already done the full database backup. Differential backup backs up all the data since the last Full backup.

Transaction log backup or incremental backup.

File(s) and Filegroup(s) backup.

Use BACKUP to back up database files and filegroups instead of the full database when time constraints make a full database backup impractical. Separate transaction log backups must be performed. After restoring a file backup, apply the transaction log to roll the file contents forward to make it consistent with the rest of the database.

Q. What is a full backup?

A. A full database backup copies all the pages from a database to a backup device; it can be a local disk file or a network disk file, a local tape drive. SQL Server also copies the portion of the transaction log that was active while the backup was in process.

Q. Explain a differential backup.

A. A differential backup copies only the extents that have changed since the last full backup. Generally, you make several differential backups between full backups, and each differential backup contains all the changes since the last full backup.

SQL Server 2000 tells which extents need to be backed up by examining a special page called the Differential Changed Map (DCM) in each file of the database. A file’s DCM contains a bit for each extent in the file. Each time you make a full backup, all the bit values revert to 0. When any page in an extent is changed, the page’s corresponding bit in the DCM page changes to 1. SQL Server copies the portion of the transaction log that was active during the backup.

Q. Explain an incremental backup.

A. A database has a transaction log that records data modifications made in the database. The log records every transaction. A transaction log backup copies all the log records that SQL Server has written since the last log backup. Even if you’ve made full database backups, a log backup always contains all the records since the last log backup.

Exact behavior of the BACKUP LOG command depends on your database’s recovery-model setting. If the database is using the full recovery model, the BACKUP LOG command copies the entire contents of the transaction log. If the database is using the simple recovery model, you cannot perform a log backup because the log is truncated regularly.

In the bulk_logged recovery model, a transaction log backup copies the contents of the log and all the extents containing data pages that bulk operations have modified since the last log backup.

In a typical scenario, an administrator would make a series of log backups between full database backups, with each log backup containing only the log records recorded since the last log backup.

Q. What is Log shipping?

A. Log shipping increases a SQL Server database’s availability by automatically copying and restoring the database’s transaction logs to another database on a standby server. Because the standby database receives all changes to the original database, it’s an exact duplicate of the original database—out of date only by the delay in the copy-and-load process.

Log shipping is better than replication in some scenarios. For example, after a switchover, you need to know exactly which transactions the standby server holds. When you use replication that information is not available, and you have to be willing to lose a certain number of transactions. However, when you use log shipping, you can find out which transactions the standby server holds because you know the last point of consistency.

Q. Every night you run a full backup. After every 3 three hours you make a differential backup. Every hour you make an incremental backup. In a worst-case scenario, how much work you can lose?

A. In the worst case you will loose, one hour of work, as every hour you take incremental backup.

Q. Explain a Checkpoint?

A. A Checkpoint is essentially a bookmark for activity in the database transaction log. A database has a transaction log that records data modifications made in the database. The log records every transaction. SQL Server stores enough information in the log to either redo (roll forward) or undo (roll back) the data modifications that make up a transaction

As writing data on disk is a slow process, SQL Server caches modifications in buffers for a period of time to optimize disk writes. A page in this cached buffer is known as a dirty page. Writing this dirty buffer page to disk is called flushing the page. In SQL Server, first log images are written to disk before the corresponding data modification.

A commit operation writes all log records for a transaction to the log file. A commit operation does not have to force all the modified data pages to disk as long as all the log records are flushed to disk. A system recovery can roll the transaction forward or backward using only the log records.

Periodically, SQL Server ensures that all dirty log and data pages are flushed. This is called a checkpoint.

Q. Explain an Automatic Checkpoint.

A. By default SQL Server always generates automatic checkpoints. The interval between automatic checkpoints does not depend on time but on the number of records in the log.

The interval between automatic checkpoints is calculated from the ‘recovery interval’ server configuration option. This option specifies the maximum time SQL Server should use to recover a database during a system restart. SQL Server estimates how many log records it can process in the recovery interval during a recovery operation. The interval between automatic checkpoints also depends on whether or not the database is using the simple recovery model.

If the database is using the simple recovery model, an automatic checkpoint is generated whenever the log becomes 70 percent full or the number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

Automatic checkpoints truncate the unused portion of the transaction log if the database is using the simple recovery model. The log is not truncated by automatic checkpoints if the database is using the full or bulk-logged recovery models.

Tricky SQL Queries For Interview

Q. How you can list all the tables in a database?

A. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

SELECT name FROM sysobjects WHERE type = ‘U’

The first query will tell you all table name plus system tables and views too.

Q. How can you list all the columns in a database?

A. SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Q. How can you list all the table constraints in a database?

A. SELECT * FROM INFORMATION_SCHEMA. TABLE_CONSTRAINTS.

Q. What are the advantages DTS has over bcp?

A. DTS has many functions that bcp does not provide, Moving data between:

SQL Server and non-SQL Server databases.

Two different kind of ODBC data sources.

DTS contains functions for modifying and transforming data fields during the bulk copy operation.

DTS can copy other objects (views, stored procedures, constraints, rules, user defined datatypes and user defined function etc ) between two SQL database . It can copy even indexes and triggers between two sql databases.

Q. How do you rebuild an identity column?

A. DBCC CHECKIDENT

Q. How can you transfer data from a text file to a database table? Or how can you export data from a table to a comma delimited (CSV) file? Or how can you import data from MS Access to a table in a database? Or how can you export data from a table to an Excel file?

A. To transfer data from a text file to a database table or vice versa use DTS.

Open SQL server enterprise manager, right click on a database, and choose ‘All Tasks’ and then ‘Import task’. Follow the instruction on the DTS wizard.

You should practice couple of scenarios before you go for an interview as it often happens that you may asked to perform these tasks.

Q. When does the auto update index statistics feature in SQL server turn itself on?
A. If the number of rows in a table are greater than 6, but less than or equal to 500, then statistics are automatically updated when there have been 500 modifications made.

If the number of rows in the table is greater than 500, then updates are automatically made when (500 plus 20 percent of the number of rows in the table) has been modified.

For addtioinal information refer to http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q195565&.

Q. What specific conditions database should meet, before you can Bulk copy data into it using BCP.

A.First is Select into/bulk copy option should be activated on the database. You can reach to this option by right clicking on the database and choosing property option.

Second, the destination table should be free of indexes.

SQL Replication Interview Questions

Q. What is database replication? What are the different types of replication you can set up in SQL Server?

A. Replication is the process of copying/moving data between databases on the same or different servers.

SQL Server supports the following types of replication scenarios:

Snapshot replication

Transactional replication (with immediate updating subscribers, with queued updating subscribers)

Merge replication

All these different kind of replications are very well defined in ‘Books on line’, please Refer to BOL for further detail.

Q. Which database stores information about replication?

A. The master database stores information about replication. The master and distribution databases keep all information about tables used in replication. See System Tables in books online. MSDB has information about the replication jobs.

Q. Your company has 50 branches all over the country. All the branches, including the Head Office have SQL Server as the database. Every night all 50 branches upload certain information to the Head Office. Which Replication topology is best suited for the above scenario?

A. Central Subscriber is the best topology for this scenario. In a Central Subscriber scenario, a number of Publishers replicate information into a common destination table at a Subscriber. The destination table is partitioned horizontally and contains a location-specific column as part of the primary key. Each Publisher replicates rows containing location-specific data.

This replication configuration may be useful, for example, for rolling up inventory data from a number of servers at local warehouses into a central Subscriber at corporate headquarters. It could also be used to roll up information from autonomous business divisions within a company, or to consolidate order processing from dispersed locations.

Q. Which of the following option(s) is(are) an inappropriate usage of merge replication: a company time sheet database, a static look up table, a high transaction based application that requires real time replication to subscribers or a company information price list that is updated at remote sites and replicated to a central database.

A. The static look up table and high transaction based applications that require real time replication to subscribers are not good candidates for merge replication.

Static look up tables are most effective with snap shot replication. High transaction based applications that require real time replication are best implemented with transactional replication.

SQL Server Interivew Questions For Experienced

Q. What are the restraints imposed on the table design by a Merge Replication?

A. You should not use a timestamp column in the table. Merge replication does not support timestamp columns. Timestamp values are generated automatically by the local server and guaranteed unique within a specific database only. Therefore, it is impossible for a change to the timestamp value created at one server to be applied to the timestamp column at another server. You must remove the timestamp column from any table you want to publish using merge replication.

SQL Security Interview Questions

Q. A user is a member of the Public role and the Sales role. The Public role has select permission on all the tables. The Sales role does not have select permission on some of the tables. Will the user be able to select from all tables?

A. No, the user will not be able to select information from tables. Permissions assigned to a role supercede those assigned to the role Public. This is true whether permissions assigned to the role are more restrictive or less restrictive than those assigned to the role Public.

Q. If a user does not have permission to a table, but has permission to a view created on it, will he be able to view the data in table?

A. Yes. The user permission for the object referenced by a view is ignored. If a user does not have select permission on the table, but if he has permission on the view based on same table, he can select from the view.

In this case, view and table must belong to the same owner, if the table owner is different then the view owner, then user will not be able to select on view. The owner of the view must own the table also for the above scenario.

Q. A user has not been given a user account for any database, but has a valid login in the SQL server. Can he use the database?

A. Any user who has not been given a User Account for a database will be allowed to use permissions assigned to the User Account guest. Thus, if a user has a valid Login ID for an SQL Server but has not been assigned to a database role or given a User Account for the database, the user has access via the account guest.

A user who has been given a User Account for a database will automatically be a member of the database role public. The user may also be assigned to other database roles.

Q. Tony works in the Sales Department and has created a table named OrderDetail for the Sales department. You write a stored procedure which will help Mark, a sales representative, update the OrderDetail table. However, when Mark uses the stored procedure he gets an error. You realize that this is a security issue. What is required to enable Mark to use your stored procedure?

A. You must assign EXECUTE permission to Mark on your stored procedure, and Tony must assign UPDATE permission on the Table. For tables and views, the owner can grant INSERT, UPDATE, DELETE, SELECT, and REFERENCES permissions, or ALL permissions. A user must have INSERT, UPDATE, DELETE, or SELECT permissions on a table before they can use it in any INSERT, UPDATE, DELETE, or SELECT statements.

The owner of a stored procedure can grant EXECUTE permissions for the stored procedure. If the owner of a base table wants to prevent users from accessing the table directly, they can grant permissions on views or stored procedures referencing the table, but not grant any permissions on the table itself.

The REFERENCES permission lets the owner of another table use columns in your table as the target of a REFERENCES FOREIGN KEY constraint from their table.

You can eliminate this problem by having dbo own all objects in a database. In that case, the only thing you would need to have done was to grant Mark execute permissions to the stored procedure.

SQL Transactions Interview Questions

Q. Define Concurrency Control.

A. Concurrency Control is the practice of defining controls so that multiple users can use and update data in a database simultaneously. In a multi-user system, we require some kind of mechanism to protect one-person change adversely affecting the others change. There are two main types of concurrency control, Optimistic Concurrency Control, and Pessimistic Concurrency Control.

Q. What is Pessimistic Concurrency Control?

A. In Pessimistic Concurrency Control a lock is placed on data while it is in use and no other process or user can access that data until that lock is released. Use Pessimistic Concurrency Control when the need for data integrity is paramount. Pessimistic Concurrency Control is not appropriate in a multi-user read-only situation, such as a data warehouse or on-line analytical processing application.

SQL Server Interview Questions And Answers For Experienced

Q. What is Optimistic Concurrency Control?

A. In Optimistic concurrency control, works on the assumption that no other user will be changing the data. Users do not lock data when they read it. When an update is performed, the system checks to see if another user changed the data after it was read. If another user updated the data, an error is raised. Typically, the user receiving the error rolls back the transaction and starts over.

Users specify the type of concurrency control in a transactions and in cursor by specifying there isolation level.

Q. Explain Isolation levels.

A. An isolation level determines the degree of isolation of data between concurrent transactions. SQL-92 defines the following isolation levels, all of which are supported by SQL Server:

READ COMMITTED

Specifies that shared locks are held only while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This is the least restrictive of the four isolation levels.

REPEATABLE READ

Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction.

SERIALIZABLE

Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels.

SQL Interview Questions 108

The default isolation level is READ COMMITTED. When the isolation level is specified, the locking behavior for all SELECT statements in the SQL Server session operates at that isolation level and remains in effect until the session terminates, or until the isolation level is set to another level.

USE pubs
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO

BEGIN TRANSACTION
SELECT au_lname FROM authors

Q. What is the difference between the REPEATABLE READ and SERIALIZABLE isolation levels?

A. In a Repeatable Read isolation level, new rows can be inserted into the dataset. In a Serializable isolation level all the rows are locked for the duration of the transaction, no insert, update or delete is allowed.

SQL Interview Questions For Experienced Professionals

Q. What is the default isolation level in SQL server?

A. READ COMMITTED is the default isolation level for SQL server. READ COMMITTED specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

Q. What is the most restrictive isolation level?

A. SERIALIZABLE is the most restrictive isolation level. SERIALIZABLE places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels.

Q. What is the least restrictive isolation level?

A. READ UNCOMMITTED is the least restrictive isolation level. READ UNCOMMITTED implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This is the least restrictive of the four isolation levels.

Q. How do you determine the current isolation level?

A. To determine the transaction isolation level currently set, use the DBCC USEROPTIONS statement.

Q. What are the conditions an underlying table must satisfy before a cursor can be used by a positioned UPDATE or DELETE statement?

A. The SELECT statement in the cursor declaration must contain the FOR BROWSE option. To use the FOR BROWSE option, the table must have both a unique index and a timestamp column.

Q. Explain Locks.

A. A lock is an object created by SQL Server to indicate that a user is currently using a resource. The lock does not allow other users to perform operations on the resource that would adversely affect the first user who owns the lock. Locks are managed internally by system software, are acquired, and released based on actions taken by the user.

Microsoft® SQL Server™ 2000 uses locks to implement concurrency control among multiple users in a database at the same time. Transactions and locks are managed on a per connection basis.

SQL Server locks are applied at various levels of granularity in the database. Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases. SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement.

The level at which locks are applied does not have to be specified by users and needs no configuration by administrators.

Q. Explain Lock escalation.

A. Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Microsoft® SQL Server™ 2000 automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold.

SQL Server may dynamically escalate or deescalate the granularity or type of locks. For example, if an update acquires a large number of row locks and has locked a significant percentage of a table, the row locks are escalated to a table lock. If a table lock is acquired, the rowlocks are released. SQL Server 2000 rarely needs to escalate locks; the query optimizer usually chooses the correct lock granularity at the time the execution plan is compiled.

Q. Under what condition it is possible to have a page level lock and row lock at the same time for a query?

A. If enough contiguous keys in a nonclustered index node are selected to satisfy the query, SQL Server may choose to do both row and page locking for the same query, placing page locks on the index and row locks on the data. This reduces the likelihood that lock escalation will be necessary.

Q. Explain how to use transactions efficiently.

A. Get all required input from users before a transaction is started. Do not open a transaction while browsing through data. Keep the transaction as short as possible. After you get all the data, start a transaction, execute the modification statements, then immediately commit or roll back. Do not open the transaction before it is required. Make intelligent use of lower transaction isolation levels. Not all transactions require the serializable transaction isolation level. Make intelligent use of lower cursor concurrency options, such as optimistic concurrency options. Access the least amount of data possible while in a transaction. This lessens the number of locked rows, thereby reducing contention between transactions.

SQL Server Triggers Interview Questions

Q. What you can do to delete a table without the delete trigger firing?

A. TRUNCATE TABLE statement is not caught by a DELETE trigger. Although a TRUNCATE TABLE statement is, in effect, like a DELETE without a WHERE clause (it removes all rows), it is not logged and thus cannot execute a trigger. Since permission for the TRUNCATE TABLE statement defaults to the table owner and is not transferable, only the table owner should be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement.

Q. Other than Truncate statement, which other command can by-pass the trigger on the tables?

A. The WRITETEXT statement, whether logged or unlogged, does not activate a trigger.

Disabling the recursive triggers setting only prevents direct recursions. To disable indirect recursion as well, set the nested triggers server option to 0 using sp_configure.

If any of the triggers do a ROLLBACK TRANSACTION, regardless of the nesting level, no further triggers are executed.

Q. What are the differences between Triggers and Stored Procedures?

A. Triggers are called automatically by SQL server in response to some action on the table, e.g. insert, delete and update. Stored Procedures can be called from triggers.

Triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table.

Triggers contain Transact-SQL statements, much the same as stored procedures.

Q. Is this statement true: A trigger can reference objects outside the current database?

A. Yes, a trigger is created only in the current database; however, a trigger can reference objects outside the current database.

Q. Can a trigger be created on a view?

A. No, a trigger cannot be created on a view.

Q. Will the WRITETEXT statement activate a trigger?

A. No, the WRITETEXT statement, whether logged or unlogged, does not activate a trigger.

Q. Can a table be created inside a Trigger?

A. CREATE TABLE statements are not allowed in a trigger.

Q. When should you use an INSTEAD OF trigger?

A. INSTEAD OF triggers are executed instead of the triggering action. INSTEAD OF triggers can also be defined on views. Each table or view can have one INSTEAD OF trigger for each triggering action (UPDATE, DELETE, and INSERT). You can work around this limitation by creating multiple views on top of the table or view in question, each with their own INSTEAD OF Triggers.

INSTEAD OF triggers allow views that would normally not surpport updates to allow updates. A view made up of multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. Another advantage of INSTEAD OF triggers is that they allow you to process data before updating the table. Let’s explore this with an example

SET NOCOUNT ON
GO

USE tempdb
GO

CREATE TABLE Customer
(ID int Identity,
LastName varchar(30),
FirstName varchar (30),
Status varchar (30)
)
GO

CREATE TABLE CreditLimit
(CustomerID int,
CreditLimit float)
GO

INSERT Customer VALUES (‘Rafat’, ‘Sarosh’, ‘Normal’)
INSERT Customer VALUES (‘Tony’, ‘Nitzke’,’Normal’)
INSERT Customer VALUES (‘Paul’, ‘Sponenburg’,’Premium’)

INSERT CreditLimit VALUES (1,10000)
INSERT CreditLimit VALUES (2,15000)
INSERT CreditLimit VALUES (3,20000)

CREATE VIEW ViewCustomer AS

SELECT FirstName , LastName , CreditLimit FROM Customer join CreditLimit ON ID = CustomerID

CREATE TRIGGER ViewCustomer_INSERT ON ViewCustomer INSTEAD OF INSERT AS

INSERT Customer (FirstName, LastName,status)
SELECT FirstName, LastName, CASE
WHEN CreditLimit < 20000 THEN ‘Normal’
ELSE ‘Premium’
END

FROM inserted
INSERT CreditLimit SELECT SCOPE_IDENTITY() , creditlimit FROM
inserted
GO

INSERT ViewCustomer (FirstName, LastName,CreditLimit) VALUES
(‘Aseem’,’Shukla’, 25000)
GO

SELECT * FROM Customer
SELECT * FROM CreditLimit
GO

DROP TABLE Customer
DROP TABLE CreditLimit
DROP VIEW ViewCustomer
GO

Use an INSTEAD OF trigger when you would like to:

• Ignore parts of a batch.

• Log the problem rows.

• Take an alternative action if an error condition is encountered.

Q. Can the “If Update (colName)” statement be used in a delete trigger?

A. No, IF UPDATE (column) Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations, as you cannot delete specific columns.

Stored Procedures Interview Questions

Q. Why should one not prefix user stored procedures with ‘sp_’?

A. If the stored procedure does not exist in the master database, do not prefix your stored procedure with ‘sp_’, as it causes a performance penalty. SQL server gives name resolution preference to the master database for procedure that have ‘sp_’ suffix. Once the SQL fails to find the stored procedure in the master database, it assumes that this procedure requires a recompilation as it is not in the cache, and acquires an exclusive lock on the stored procedure for a short time. This lock causes the performance hit.

The exclusive lock is required because only one copy of a stored procedure is generally in the cache at any time. This requires serialization of some parts of the compilation process, and this synchronization process is accomplished in part through the use of compile locks.

Read more on http://support.microsoft.com/support/kb/articles/q263/8/89.asp

Q. What are the results of running this script?

CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO

CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO

CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO

EXEC Test1
GO

A. 1

All references to the name within the stored procedure are resolved against the temporary table created in the procedure, not the version that existed before the procedure. Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure.

SQL Server Stored Procedures Interview Questions And Answers

Q. Which table keeps information about Stored Procedures?

A. The SysObjects table contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb, this table includes a row for each temporary object.

Q. What will be the value of @@FETCH_STATUS if a row that was a part of the cursor resultset has been deleted from the database after the time the stored procedure that opened the cursor was executed?

A -2.

Q. Why and when do stored procedure recompile?

A. Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure that have changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table. By default for temporary tables SQL Server determines that after 6 modifications any stored procedure referencing that table will need to be recompiled.

http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q195565& Here are few of the reasons why stored procedures will recompile:

• Dropping and recreating the stored procedure.

• Using WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement

• Changing the schema of any referenced objects

• Running the sp_recompile system stored procedure against a table referenced by the stored procedure
• Stored procedures plan dropping from the cache.

• Any reference to the temporary tables in the stored procedure.

The following SET options are ON by default in SQL Server, and changing the state of these options in your stored procedure will cause the stored procedure to recompile:

SET ANSI_DEFAULTS
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
SET CONCAT_NULL_YIELDS_NULL

A run-time recompilation of a stored procedure takes place in the following situation:

• Data in a table referenced by the routine has changed.

• The procedure contains a mixture of DDL and DML operations.

• Certain operations on temporary tables are performed.

Q. How can you find out which stored procedures are recompiling?

A. Use the SQL Profiler.

To determine if you have a problem with a specific stored procedure:

Start Profiler.

Start a new trace.

On the Events Tab, remove all default events and add SP:Recompile, SP:Starting, and SP:Completed under Stored Procedure events. If you want to determine the statement that causes the recompile also add SP:StmtStarting and SP:StmtCompleted to the selection.

Click on the filter tab, and add filter to reduce the clutter.

Q. How can you stop stored procedures from recompiling?

A. By using sp_executesql and using the KEEPFIXED PLAN query hint. KEEPFIXED PLAN Forces the query optimizer not to recompile a query due to changes in statistics or to the indexed column (update, delete, or insert). Specifying KEEPFIXED PLAN ensures that a query will be recompiled only if the schema of the underlying tables is changed or sp_recompile is executed against those tables.

SELECT COUNT(*) FROM #Temp OPTION (KEEPFIXED PLAN)
Recompiling takes place when temporary objects are created and referenced throughout the code. Temporary objects will not exist when the initial compilation of the code takes place, so SQL Server has to recompile the stored procedure during execution. This recompilation takes place after the temporary object is referenced for the first time.

By placing all of your temporary table creation statements together, SQL Server can create plans for those temporary tables when one of the tables is referenced for the first time. Recompile will still take place during the execution of the stored procedure, but it will happen only twice, not as many times as many temp tables you have. (Once for the stored procedure and once when the first reference to a temporary table is made).

SQL Server will also be able to reuse the execution plan for the stored procedure the next time the procedure is called there will be no recompiles. Like permanent objects, if you change the schema of a temporary table, the change will cause the stored procedure to recompile as well. Make all schema changes (such as index creation) right after your create table statements and before you reference any of the temporary tables.

Use SQL Profiler to determine if and when your procedures are being recompiled.

SQL Server Cursors Interview Questions And Answers

Q. How many types of cursor type are there?

A. ODBC, ADO, and DB-Library define four cursor types supported by Microsoft® SQL Server™2000. The DECLARE CURSOR statement has been extended; thus you can specify the four cursor types for Transact-SQL cursors.

The four API server cursor types supported by SQL Server are:

Static cursors

Dynamic cursors

Forward-only cursors

Keyset-driven cursors

Static cursors detect few or no changes but consume relatively few resources while scrolling, although they store the entire cursor in tempdb. Dynamic cursors detect all changes but consume more resources while scrolling, although they make the lightest use of tempdb. Keyset-driven cursors lie in between, detecting most changes but at less expense than dynamic cursors.

Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, SQL Server does not. SQL Server considers both forward only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors.

Q. What is the difference between insensitive and scroll cursor?

A. Insensitive cursors make a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table; modifications made to base tables will not be reflected in the data returned by fetches made to this cursor. This cursor does not allow modifications. In other words, this cursor remains insensitive to changes to underlying tables.

In a scroll cursor, committed deletes and updates made to the underlying tables (by any users) are reflected in subsequent fetches.

Q. If a table does not have a unique index, can a cursor be opened on it?

A. Yes, but only insensitive cursors can be opened on a table which does not have a unique index.

Q. Can a cursor be updated? If yes, how you can protect which columns are updated?

A. By default, cursors are updatable. To prevent updates from occurring to any row use READ ONLY options.

To specify the columns which can be updatable, use UPDATE OF option. For e.g.

DECLARE MyCursor CURSOR FOR
Select * from Emp for UPDATE OF( FirstName ).

If OF column_list is supplied, only the columns listed will allow modifications. If no list is supplied, all columns can be updated unless the cursor has been defined as READ ONLY.

Q. While using a cursor, how can you differentiate between a deleted row and a row that has been inserted with NULL data values?
A. Use the global variable @@FETCH_STATUS after each FETCH is made against the cursor. This is covered in greater detail in the next question.

Q. How can you know if the row fetched from cursor is still valid in underlying table?

A. A global variable, @@FETCH_STATUS, will be updated at every execution of FETCH. At a successful fetch, @@FETCH_STATUS will be set to 0. If no data was fetched because the requested cursor position exceeded the results set, −1 will be returned. If the row returned is no longer a member of the results set (if the row was deleted from the base table after the cursor was opened), @@FETCH_STATUS will return −2.

Always use this to determine the validity of the data returned from a cursor fetch prior to attempting any operation against that data.

Q. How can you find out how many rows returned in a cursor?

A. Use the @@CURSOR_ROWS function to determine how many rows have been returned.

Q. What does it mean if @@CURSOR_ROW returns a negative number?

A. Once a cursor has been opened; use the global variable @@CURSOR_ROWS to receive the number of qualifying rows in the last opened cursor. @@CURSOR_ROWS returns:

−m If the cursor is being populated asynchronously. The value returned (−m) refers to the number of rows currently in the keyset.

n If the cursor is fully populated. The value returned (n) refers to the number of rows.

0 If no cursors have been opened or the last opened cursor has been closed or deallocated.

Q. How can you set the threshold at which SQL Server will generate keysets asynchronously?

A. Use the cursor threshold configuration option.

Q. What is the difference between Deallocate cursor and Close cursor?

A. Deallocate removes the cursor data structures. A closed cursor can be re-opened. DEALLOCATE releases all data structures associated with the cursor and remove the definition of the cursor.

Q. Define Cursor Locking.

A. The SELECT statement in a cursor definition obeys the transaction locking rules that apply to any other SELECT statement. In cursors, an additional set of scroll locks can be acquired based on the specification of a cursor concurrency level.

The transaction locks acquired by any SELECT statement are controlled by:

The transaction isolation level setting for the connection.

Any locking hints specified in the FROM clause.

These locks are held until the end of the current transaction for the SELECT statements. When SQL Server is in autocommit mode, each individual SQL statement is a transaction and the locks are freed when the statement finishes. If SQL Server is in explicit or implicit transaction mode, then the locks are held until the transaction is either committed or rolled back.

The locks generated by an independent SELECT or a cursor are always acquired when a row is retrieved.

Cursors retrieve the rows at different times depending on the type of cursor:

Static cursors and Keyset-driven cursors retrieve the entire result set at the time the cursor is opened. This locks each row of the result set at open time.

Dynamic cursors (including forward-only cursors) do not retrieve rows until they are fetched. Locks are not acquired on the rows until they have been fetched.

Fast forward-only cursors vary in when they acquire their locks depending on the execution plan chosen by the query optimizer. If a dynamic plan is chosen, no locks are taken until the rows are fetched. If worktables are generated, then the rows are read into the worktable and locked at open time.

SQL Datatypes Interview Questions And Answers

Q. Between Cast and Convert which function would you prefer and why?

A. Both the functions are used to convert one data type to another. CAST is based on the SQL-92 standard and is preferred over CONVERT. You may prefer to use convert, in situations where you want to convert a datetime value to a specific style.

Q. What are the new data types are introduced in SQL 2000?

A. SQL Server 2000 introduces three new data types. bigint is an 8-byte integer type. sql_variant is a type that allows the storage of data values of different data types. table is a type that allows applications to store results temporarily for later use. It is supported for variables, and as the return type for user-defined functions.

Q. Why it is recommended to avoid referencing a floating point column in the WHERE clause?

A. Approximate numeric data consists of numeric data preserved as accurately as the binary numbering system allows. Many floating-point values cannot be accurately represented in binary notation. For these numbers, it is necessary to store an approximation of the decimal number. Examples of these numbers are floating-point values ending in .3, .6, and .7. The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round toward zero. SQL Server uses round up. It is best to avoid referencing to floating-point columns in WHERE clauses. I.e. Float and Real.

Q. You have to store user responses of ‘Yes’ and ‘No’. What kind of data type is best suited for this task?

A. The bit datatype is best suited to store Yes/No, True/False, and 1/0 values.

Q. What is the Exact Numeric data type in SQL?

A. Exact numeric data consists of numeric data with accuracy preserved to the least-significant digit. Exact numeric datatypes are different from the approximate numeric datatypes float and real, which cannot store all decimal numbers with complete accuracy. The decimal and numeric datatypes are identical in the SQL Server. Both are provided for ANSI compatibility.

These are the exact numeric datatypes:

decimal[(p[, s])] and numeric[(p[, s])]

Q. Explain timestamp datatype?

A. The timestamp is a datatype that is automatically updated every time a row containing a timestamp column is inserted or updated. Values in timestamp columns are not datetime data, but binary(8) varbinary(8) data, indicating the sequence of SQL Server activity on the row. A table can have only one timestamp column.

The timestamp datatype has no relation to the system time it is simply a monotonically increasing counter whose values will always be unique within a database.

Q. How can a user-defined datatype be created?

A. Sp_addtype creates a user-defined datatype. Executing sp_addtype creates a user-defined datatype and adds it to the systypes system table. Once a user datatype is created, you can use it in the CREATE TABLE and ALTER TABLE statements, as well as bind defaults and rules to it.

sp_addtype typename, phystype [, nulltype]

Define each user datatype in terms of one of the physical (SQL Server − supplied) datatypes, preferably specifying NULL (allow null entries) or NOT NULL (disallow them). A user-defined datatype name must be unique in the database, but user-defined datatypes with different names can have the same definition.

Examples

A. Datatype That Does Not Allow Null Values

This example creates a user-defined datatype named ssn to be used for columns that hold social security numbers.

Notice that varchar(11) is enclosed in single quotation marks because it contains punctuation (parentheses).

sp_addtype ssn, ‘VARCHAR(11)’, ‘NOT NULL’

Q. What are the approximate numeric data types?

A. Float and Real are approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

Q. You are creating an application where Users are asked their gender. In the gender combo box you have three options: ‘Male’ , ‘Female’ and ‘I choose not to disclose’. These options are stored in the table as 1, 0 or NULL. Which datatype should you use?

A. Specify the bit data type for the column.

Q. Which data types generate inaccurate results if used with an = or <> comparison in a WHERE clause of a SQL statement?
A. Float, Real. The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.

Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of this approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators.

It is best to limit float and real columns to > or < comparisons.

Q. Beginning with SQL Server Version 7.0, a new enhanced data type nchar was added. What type of data is supported with this data type?

A. Unicode Data. Unicode data is stored using the nchar, nvarchar, and ntext data types in SQL Server.

Use these data types for columns that store characters from more than one character set.

Q. What will happen if a column containing char type data is changed to the nchar data type?

A. The nchar data type takes double the amount of bytes to save the same amount of information. Unicode data can be saved in this column. Unicode data is stored using the nchar, nvarchar, and ntext data types in SQL Server. Use these data types for columns that store characters from more than one character set. Unicode characters store the character set and the character; as a result it requires twice the space as compared to char datatype.

Q. To automatically record the time on which the data was modified in a table, which data type should you choose for the column?

A. Use the datetime datatype. Date and time data types are used for representing date and time of day. Date and time data from January 1, 1753, to December 31, 9999, to an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds, as shown in the table.

timestamp – A database-wide unique number. The storage size is 8 bytes.

The value of a timestamp column is unique within a database.

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid.

XML in SQL Interview Questions And Answers

Q. What is XDR?

A. In Microsoft® SQL Server™ 2000, the XML-Data Reduced (XDR) language is used to create the schemas. The XDR is flexible and overcomes some of the limitations of the Document Type Definition (DTD), which also describes the document structure. Unlike DTDs, XDR schemas describe the structure of the document using the same syntax as the XML document. Additionally, in a DTD, all the data contents are character data. XDR language schemas allow you to specify the data type of an element or an attribute.

Q. What is the difference between FOR AUTO and FOR NESTED?

A. The NESTED mode of the client-side FOR XML is similar to the AUTO mode of the server-side FOR

Q. What is the difference between FOR XML RAW and FOR XML AUTO?
A. RAW: Takes the query result and transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag.

For e.g.

SELECT AppID, CommonName FROM Application for XML RAW

AUTO: Returns query results in a simple, nested XML tree. Each table in the FROM clause is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes.

<ApplicationAppID=”6″ CommonName=”Information services Intranet systems”/>

Q.Explain FOR XML EXPLICIT Mode?

A. In an EXPLICIT mode, the query writer controls shape of the XML document returned by the

execution of the query. The query must be written in a specific way so that the additional information about the expected nesting is explicitly specified as part of the query.

See SQL Server Books On-Line for a detail. You may be asked to write a query which uses XML EXPLICIT mode.

Q. Using the Customer, and Order table in Northwind database, Please write a query to produce following XML?

SELECT ‘<Results><Data>’

SELECT 1 AS Tag,
NULL AS Parent,
dbo.Customers.CustomerID AS [row!1!id],
dbo.Customers.ContactName AS [row!1!name],
dbo.Orders.OrderID AS [row!1!orderid]
FROM dbo.Customers, dbo.Orders
WHERE dbo.Customers.CustomerID = dbo.Orders.CustomerID
FOR XML EXPLICIT

SELECT ‘</Data></Results>’

Advanced SQL Server Interivew Questions And Answers

Q. What is XML Schema?

A. An XML schema describes the structure of an XML document and also describes the various constraints on the data in the document. When you specify XPath queries against the schema, the structure of the XML document returned is determined by the schema against which the XPath query is executed.

In an XSD schema, the <xsd:schema> element encloses the entire schema; all element declarations must be contained within the <xsd:schema> element. You can describe attributes that define the namespace in which the schema resides and the namespaces that are used in the schema as properties of the <xsd:schema> element.

The minimum XSD schema is:

<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:sql=”urn:schemas-microsoft-com:mapping-
schema”>
…
</xsd:schema>

Q. What is mapping schema?

A. A mapping schema is, in effect, an XML view of the relational data. These mappings can be used to retrieve relational data as an XML document. In the context of the relational database, it is useful to map the arbitrary XSD schema to a relational store. One way to achieve this is to annotate the XSD schema. An XSD schema with the annotations is referred to as a mapping schema, which provides information pertaining to how XML data is to be mapped to the relational store.

Q. What is XPath?

A. XPath (XML Path Language) is a graph navigation language. XPath is used to select a set of nodes from an XML document. Each XPath operator selects a node-set based on a node-set selected by a previous XPath operator.

XPath language is defined by the W3C as a standard navigation language. The XPath language specification, XML Path Language (XPath) version 1.0 W3C Proposed Recommendation 8 October 1999, can be found at the W3C Web site at http://www.w3.org/TR/1999/PR-xpath-19991008.html. A subset of this specification is implemented in SQL Server 2000.

Q. What keyword you will use to get schema appended to the result set of a ‘for XML’ query?

SELECT TOP 2 Customers.CustomerID
FROM Customers
FOR XML RAW

Query will produce following result

To get the schema appended to the result set, write the query as follows:

SELECT TOP 2 Customers.CustomerID
FROM Customers
FOR XML RAW, XMLDATA

Q. If I execute this query

SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO

Following result set is returned:

How can I rewrite this query to get all attributes as an element?

A. If ELEMENT key word is used, the query will return an element centric result set.

SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO, ELEMENTS

<CustomerID>ALFKI</CustomerID> <ContactName>Maria Anders</ContactName>
<Orders><OrderID>10643</OrderID></Orders>
<Orders><OrderID>10692</OrderID></Orders>
<Orders><OrderID>10702</OrderID></Orders>
<Orders><OrderID>10835</OrderID></Orders>
<Orders><OrderID>10952</OrderID></Orders>
<Orders><OrderID>11011</OrderID></Orders>
</Customers>

SQL Clustering Interview Questions

Q. Explain Active/Passive and Active/Active cluster configurations?

A. Active/Passive cluster configuration is a combination of two nodes, one called a primary node and another called the secondary node. The primary node suporst the clients and the secondary node acts are a failover backup node. An Active/Active cluster configuration is two simultaneous mirrored active/passive configurations.

Advanced SSRS Interview Questions For Experience

SSRS Interview Questions And Answers. If you are looking for SQL Server Reporting Services Interview Questions, here is the comprehensive list from basic to most advanced SSRS interview questions for 2+, 3+, 4+, 5+, 6+ years of SSRS experienced professionals.

When attending interviews for experienced roles, you should know practical SSRS interview questions, and real-time SSRS scenario based interview questions. These SSRS interview questions for experienced will help you to crack your SSRS job interview.

SSRS Interview Questions

These are the basic interview questions on SSRS – SQL Server Reporting Services. Go through this below list of beginner SSRS interview questions.

SSRS Interview Questions

Are there issues when exporting SSRS reports into Microsoft Excel? When my users are trying to export a SSRS report into Microsoft Excel, one or two columns in the report appear to merge together. Why might this be?
Can we deploy SSRS reports on our personal website?
Can we run Reporting Services with SQL Server express edition, which is a free version of SQL Server?
Can We Use Data-grids For Our Report In Ssrs?
Can we use datagrids for our report in SSRS?
Can you always create a cache of a report?
Can you edit the .rdl code associated with a linked report?
Can you import Microsoft Excel data to SSRS?
Can You Use A Stored Procedure To Provide Data To An Ssrs Report?
Can you use a stored procedure to provide data to an SSRS report?
Can You Use Data Mining Models In Ssrs?
Describe different Processing Modes offered by SSRS?
Difference between Logical Page and Physical Page in SSRS.
Do I need a report server to run reports in my application?
Explain can SSRS reports Cache results?
Explain how would you store your query in an SSRS report or a Database server?
Explain how you can configure a running aggregate in SSRS?
Explain Reporting Life Cycle?
Explain SSRS Architecture?

SQL Server Reporting Services Interview Questions

How can I add Reporting Services reports to my application?
How do I get Report Builder to generate a parameter that can be set by users viewing the report?
How do users use Report Builder with SQL Server data sources?
How does Report Builder support Analysis Services cubes?
How does the report manager work in SSRS?
How to add the custom code in Report?
How to backup encryption key ?
How to backup SQL Server Reporting Services ?
How to deploy the Report?
How to fine-tune Reports?
How to send a SSRS report from SSIS?
How Would You Go About Developing A Ssrs Report?
In case you have filters in your report, when filters will be applied in Cached Report instance?
In which SQL Server version report builder introduced?
Is Ssrs Support Other Database Except Ms Sql Server?
Is SSRS support other database except MS SQL Server?
List out what other servers you can use with SSRS?
Mention what are the core components of SSRS?
Mention what are the different types of SSRS reports?
Name and Describe few console utilities for SSRS?
Name few Endpoints exposed by SSRS 2012?
SQL Server Reporting Services vs. Crystal Reports.
User wants only to display only PDF as export option in report Manager. How to achieve this?
What are Advantages of SSRS or why we should use SSRS?
What Are Advantages Of Ssrs Or Why We Should Use Ssrs?

Advanced SSRS Interview Questions

What are Click through Reports?
What are Data Driven Subscriptions?
What are data regions?
What are different types of roles provided by SSRS?
What are Drilldown Reports?
What are Drillthrough Reports?
What Are Parameterized Reports? What Are Cascading Parameters In Ssrs Reports?
What are sub reports and how to create them?
What Are The Different Kinds Of Ssrs Reports?
What are the different types of data sources in SSRS?
What are the drawbacks of reporting in SSRS?
What are the Export Options of SSRS?
What are the key configuration files for SQL Server Reporting Services ?
What are the limitations in SSRS on SQL Server express edition?
What are the limitations/drawbacks or SSRS 2008 R2?
What are the new features are introduced in SQL Server 2012 reporting services?
What are the new features of SQL Server 2008 R2 reporting service?
What are the new features of SQL Server 2012 reporting service?
What are the Reporting Services components?
What are the three different part of RDL file explain them?
What are the tools available in market as an Alternative to SQL Server Reporting Services?
What are the Types of SSRS?
What is a cache in SSRS?

SSRS Developer Interview Questions

What Is A Dataset And What Are The Different Types Of Datasets?
What is a matrix in SSRS?
What is Cached Report?
What is Data Set in report?
What is encryption key?
What is Linked Report?
What is Parameterized Reports in SSRS ?
What is Query parameter in SSRS?
What is Report Builder?
What is report rendering ?
What is report snapshot?
What is report subscription?
What is ReportServer and ReportServerTempDB ?
What is Snapshot Report?
What is SSRS?
What is Subreport?
What Is Tablix?
What is the chart in report?

SSRS Real Time Interview Questions

What is the name of reporting services config file and what’s it’s used for?
What is the web service used for reporting services?
What new data source types were added in SSRS 2014?
What Programming Language Would You Use To Create Embedded Functions In Ssrs?
When to Use Null Data driven Subscription?
Which language rdl files made of?
Which Rendering Formats Are Affected By The Pagesize Properties?
Would You Store Your Query In A Ssrs Report Or A Database Server? State The Reason Why?
You want to generate a report that is formatted as a chart. Can you use the Report Wizard to create such a report?
You Want To Use A Perspective In An Mdx Query. How Do You Select The Perspective?
You want to use BIDS to deploy a report to a different server than the one you chose in the Report Wizard. How can you change the server URL?
You Want Your Report To Display A Hyperlink That Will Take Users To Your Intranet. How Do You Configure Such A Hyperlink?

61 SQL Interview Questions For Experienced

61 SQL Interview Questions For Experienced. If you are preparing for SQL Server Interviews, this article will help you to find real-time scenario based SQL interview questions. Here Coding compiler collected a list of SQL job interview questions for freshers to professionals. Go through this below list of advanced SQL interview questions and crack your next job.

SQL Interview Questions

Can You Modify The Rows In A Table Based On Values From Another Table? Explain.
Can You Remove Rows From A Table Based On Values From Another Table? Explain.
Can You Sort A Column Using A Column Alias?
Can you write down Syntax for STUFF function in SQL?
Did you work on Dynamic SQL?
Difference between truncate and delete? Explain how can you use them?
Explain what is the purpose of STUFF function in SQL?
For what purpose Explicit Cursor Used in SQL?
How can you execure Dynamic SQL commands?
How Do You Copy Rows From One Table To Another?
How to connect to remote database?
How to create the scheduled job in SQL?

Advanced SQL Interview Questions

If you are in a situation to delete duplicate records in a table, that doesn’t have primary key? How can you delete?
What Are All The Different Normalizations?
What Are All The Different Types Of Indexes?
What Are All Types Of User Defined Functions?
What Are Local And Global Variables And Their Differences?
What Are Tables And Fields?
What are the advantages and disadvantages of Stored Procedure?
What Are The Case Manipulation Functions Of Sql?
What Are The Specific Uses Of Sql Functions?
What Are The Types Of Join And Explain Each?
What Are The Types Of Sub Query?
What Happens If You Omit The Where Clause In The Update Statement?
What Is A Constraint?
What Is A Cursor?
What Is A Database?
What Is A Query?
What Is A Relationship And What Are They?
What Is A Stored Procedure?
What Is A Trigger?
What Is A View?
What Is An Index?
What Is Auto Increment?
What is BCP in SQL? When is it used?
What Is Collation?
What is correlated query work in SQL?
What Is Cross-join?
What Is Data Integrity?
What Is Datawarehouse?
What Is De Normalization?.
What Is Normalization?
What Is Self-join?
What Is Sub Query?

Scenario Based SQL Interview Questions

What Is The Difference Between Cross Joins And Natural Joins?
What Is The Difference Between Delete And Truncate Commands?
What Is The Difference Between Sql And Mysql Or Sql Server?
What Is The Difference Between Sql And Pl/sql?
What Is The Difference Between Varchar2 And Char Datatypes?
What is the port number SQL server runs?
What is the procedure to store files like PDF and Doc. txt files?
What Is The Purpose Of Dml Statements In Sql?
What Is The Purpose Of The Merge Statement In Sql?
What Is The Purpose Of The Nvl Function?
What is the use of SQL CASE statement?
What Is The Use Of The Nullif Function?
What is the use of UPDATE_STATISTICS command in SQL server?
What is the use of UPDATE_STATISTICS command in SQL?
What Is User Defined Functions?
Which Sql Statement Is Used To Add, Modify Or Drop Columns In A Database Table?
Which Statement Is Used To Add A New Row In A Database Table?

SSIS Interview Questions And Answers For Experienced

SSIS Interview Questions And Answers. If you are looking for SQL Server Integration Services Interview Questions, here is the comprehensive list from basic to most advanced SSIS interview questions for 2+, 3+, 4+, 5+, 6+ years of SSIS experienced professionals.

When attending interviews for experienced roles, you should know practical SSIS interview questions, and real-time SSIS scenario based interview questions. These SSIS interview questions for experienced will help you to crack your SSIS job interview.

SSIS Interview Questions

These are the basic interview questions on SSIS – SQL Server Integration Services. Go through this below list of beginner SSIS interview questions.

Explain what is SSIS?
Mention what are the important components of SSIS package?
Explain what is Solution Explorer in SSIS?
Explain what does it mean by data flow in SSIS?
Define what is “task” in SSIS?

SSIS Package Interview Questions

Explain what is SSIS package?
List out different types of connection or files that support SSIS?
Explain what is a container? How many types of containers are there in SSIS?
Explain what is Precedence Constraint in SSIS?
Explain what variables in SSIS and what are the types of variables in SSIS?

SSIS Checkpoint Interview Questions

Explain what is a checkpoint in SSIS?
Explain what is connection managers in SSIS?
Explain what is SSIS breakpoint?
Explain what is event logging in SSIS?
Explain what is logging mode property?
Explain what is a data flow buffer?

SSIS Split Interview Questions

For what data checkpoint data is not saved?
Explain what is conditional split transactions in SSIS?
List out the different types of Data viewers in SSIS?
Mention what are the possible locations to save SSIS package?
What will be your first approach if the package that runs fine in Business Intelligence Development Studio (BIDS) but fails when running from an SQL agent job?

SSIS Event Handler Interview Questions

Explain what is the role of Event Handlers tab in SSIS?
Explain how you can notify the staff members about package failure?
Explain how would you do logging in SSIS?
Mention how would you deploy a SSIS package on production?
Explain how to handle Early Arriving Facts or Late Arriving Dimension?
Explain how can you do an incremental load?

SSIS Interview Questions For Experienced

How do I sort packages in Solution Explorer?
How do I check to see if a record exists in my destination and update it, and if it doesn’t exist, insert it?
Why are my mapped drives not working?
Why is my SQL Server Destination component not working?
Why is my Excel interop failing intermittently in my Script Task?
How do I set a flat file name for flat file connection manager dynamically?
How do I use dynamic T-SQL in Lookup?
How do I use lookup transformation to load data incrementally?
How do I perform data type conversions in SSIS?
How do I create a folder for SSIS package in MSDB with T-SQL?

SSIS Practical Interview Questions

How do I resolve the error “Connection is busy with results for another command”?
How do I programmatically create a Transfer SQL Server Objects task?
How do I execute a stored procedure with a return value and an output parameter in an Execute SQL Task?
How do I use the Fuzzy Grouping transformation?
How do I use Integration Services to achieve Push-mode processing for Analysis Services?
How do read data from Excel 2007 with Integration Services 2005?
How do I upgrade my DTS packages to SSIS 2008 packages?
How do I troubleshoot SSIS packages failed execution in a SQL Agent job?
How do I track the error message when my SSIS package failed execution?
How do I troubleshoot a connection error regarding DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER ?

SSIS Scenario Based Interview Questions

How do I troubleshoot SSIS package hanged for running?
How do I use SSIS to completely transfer my database including logins, users, and permissions from one server to another server?
How do I record log information in Script Component?
How do I create and deploy a SSIS custom task?
How do I improve package loading performance in BIDS when I open a package which is imported from another place?

Happy Learning..!!!

References:

https://social.msdn.microsoft.com/

SQL Server Interview Questions – Basic To Advanced

SQL SERVER INTERVIEW QUESTIONS – If you are looking for SQL Interview Questions, here is the comprehensive list from basic to most advanced SQL server interview questions for 2+, 3+, 4+, 5+ years of experienced professionals.

SQL Server Interview Questions

This list also includes concept based SQL interview questions on different topics in SQL.

General Relational DBMS Interview Questions

Q) What is a candidate key?

Q) What are alternate keys?

Q) What is a primary key?

Q) What is the difference between a primary key and a unique key? Are they the same?

Q) What is referential integrity?

Q) What is an Entity-Relationship Diagram (ERD)?

Q) What is a subquery?

Q) What is a correlated subquery?

Q) How do you use a subquery to find records that exist in one table and do not exist in another?

Q) What does it mean to normalize a database and why would you do it?

Q) What is denormalization?

Design and Programming Interview Questions

Q) How can I detect whether a given connection is blocked?

Q) How would you design a database for an online site, which would average a million hits a day?

Q) You are testing the performance of a query The first time you run the query, the performance is slow The second time you run the query, the performance is fast Why is this?

Q) What you can do to remove data from the cache and query plans from memory for testing the performance of a query repeatedly?

Q) What is wrong in the following SQL statement?

Q) Write the fastest query to find out how many rows exist in a table

Q) The COUNT() function always returns a int value type What should you do if you need to count rows from a query which you know will return a value that is too large for an int value type?

Q) What is a derived table?

Q) You have a table with three columns: Amount1, Amount2, and Amount3 In a single row only one of these three columns will have a value and the other two will be null Write a SQL query to retrieve the values from these columns

Q) How can you fix a poorly performing query?

Q) What is the physical representation for a many-to-many relationship?

Q) What is the maximum length of an extended property?

Q) In which database can extended stored procedures be added?

Q) Why does this query return 0?

Q) If a column is an image value type, how you can compare column values? How can you use this column in join clause?

Q) Which data type cannot be used as a parameter in a UDF?

Q) Which four data types cannot be used as a return type from a user-defined function?

Q) Can an extended stored procedure be called from inside a user-defined function?

Q) How you can make a parameterized view?

Q) How long can an Error message be in RAISEERROR function?

Q) What are the constraints on severity level in RAISEERROR?

Q) How can you log an error to the server event log from a stored procedure?

Q) Explain the Rollup operator

Q) Explain the Cube operator

Q) What is an application role and explain a scenario when you would use one?

Q) On Friday, you issued several INSERT statements using Query Analyzer. You then verified the data had been correctly entered with a SELECT statement On Monday, your users report that the data is not there What happened?

Q) You have two tables with a one to many join based on a field named ID. You need to find records in the parent table that are not represented in the child table How would you accomplish this?

SQL Interview Questions For Experienced

Q) Give an example of why you would want to denormalize a database

Q) Explain extended properties

Q) You have couple of stored procedures that depend on a table You dropped the table and recreated it What do you have to do to reestablish those stored procedure dependencies?

Q) How can you append an identity column to a temporary table?

Q) You schedule a job to run every minute What will happen if the first job runs more than 1 min? Will the second instance of the job start?

Q) Why should you use or avoid SELECT * … statements?

Q) What is wrong with this statement?

Q) You have several tables, and they are joined together for querying. The tables contain both clustered indexes and non clustered indexes To optimize performance, how should you distribute the tables and their indexes onto different file groups?

Q) Which event (Check constraints, Foreign Key, Rule, Trigger, Primary key check) will be performed last for an integrity check?

Q) What is the fastest way to permanently delete a 1 million row table named customers?

Q) After removing a table from database, what other related objects have to be dropped explicitly?

Q) You want to check the syntax of a complicated Update SQL statement without executing it. What command should you use?

Q) You are the database developer for a leasing company. Your leasing database includes a lease table that has a column which keeps Social security numbers Each SSN must be unique You want the data to be physically stored in order by SSN What constraint should you add to the SSN column on the lease table?Q) You are designing a database for your company Your Company has 10 departments and each

Q) You are designing a database for your company Your Company has 10 departments and each department has between 25 to 100 employees Each employee may work for one or more departments How can you represent this relationship in your ERD (entity relationship diagram)?

Q) Mary’s company is a research company that does experiments Mary’s company database includes an Experiment table The Experiments table stores all the Experiments and their ID There is also a Project table, which keeps all the Projects with their ID How should the database be designed, so that an experiment cannot be repeated in a Project, but a single experiment can belong to more than one Project?

Q) John exports information periodically from a Microsoft SQL Server database to an Oracle Database What is the best way to do this?

Q) You are designing a database for your human resources department In the employee table, there is a field for Social Security Number, which cannot contain NULL values If no value is given, you want a value of UNKNOWN to be inserted in this field What is the best approach?

Q) Is it true that rules do not apply to data already existing in a database at the time the rule is created?

Q) Is it true, that there is no difference between a rule and a check constraint?

Q) Can a rule be bound to any column of any data type?

Q) You are responsible for designing the physical architecture of an OLTP system. You have two tables TableA and TableB that will be joined together by several queries You need good reliability and high performance for these queries What should you do?Q) What is RAID, and how it can influence database performance?

Q) What is RAID, and how it can influence database performance?

Q) You work at the corporate office of a Fortune 500 company that has regional offices in 100 countries Each regional office maintains its own customer table This information needs to be brought to the corporate office and merged, to do some analysis for the marketing department The marketing department also needs to uniquely identify each customer How should you design the customer table?

Q) Explain the DBCC PINTABLE command When would you use it?

Q) You have to design a database for a manufacturing plant Among other things, this database maintains the attendance of the workshop employees who work in 3 shifts The Attendance table should have a field named ‘Shift’, which stores the shift that the employee worked The Workshop supervisor should be able to input only 1, 2 or 3 in this field What is the best design, to implement this requirement?

SQL Interview Questions For 5+ Years Experienced

Q) What is a transaction and why is it important?

Q) What is deadlock?

Q) What is a livelock?

Q) How you can minimize the deadlock situation?

Q) What is the importance of concurrency control?

Q) Define Joins

Q) What is an outer join?

Q) Define a cross join?

Q) How you can change a cross join into an inner join?

Q) Define constraints and give an example of their use?

Q) Write a SQL Query to sort on different column name according to the parameters passed in the Function?

Q) What is the downside of using UDF?

Q) You have a stored procedure, which execute a lengthy batch job. This stored procedure is called from a trigger You do not want to slow the data entry process You do not want trigger to wait for this batch job to finish before it completes itself What you can do to speed up the process?

Q) Write a SQL Query to delete duplicate records from a table called TABLE1

Q) What is an index?

Q) What is the preferred way to create a clustered and non-clustered index? Which index should you create first the clustered or non-clustered?

Q) Can a unique index be created on a column, which contains NULL?

Q) How would you choose between a clustered and a non-clustered index?

Q) Your table has a large character field There are queries that use this field in their search clause What should you do?

Q) What is a fill factor?

Q) When you should use a low fill factor?

Q) What are statistics?

Q) What is clustering?

Q) What is the difference between DBCC INDEXDEFRAG and DBCC REINDEX?

Q) How you can find out if an index is useful to the optimizer?

Q) Where are full-text indexes stored?

Q) How many full-text indexes can a table have?

Q) Indexes are updated automatically Is the full-text index also updated automatically?

Q) How is a full-text index updated?

Q) Can you force a query to use a specific Index?

Q) Which data type columns are the best candidates for full-text indexing?

Q) When would you prefer to have a minimum number of indexes?

Q) You created a table as follows

How you would rewrite the SQL Query to return the CUSTOMERID sorted numerically?

Q) Why is there a performance difference between two similar queries where one uses UNION and the other uses UNION ALL?

Q) You have a table ‘test’ which is a copy of northwind employee table You have written a trigger to update the field ‘HireDate’ with the current date

Q) What is the difference between OPENROWSET and OPENQUERY?

Q) How you can add messages to the NT event log from within a stored procedure?

Q) What are three ways you can use an identity value inside a trigger? Why would you prefer one way over another?

SQL Server View Interview Questions

Q) List some of the rules that apply to creating and using a ‘view’

Q) You added a row to a view, but the row is not shown on the view. Explain how this can happen, and how you can remedy the situation

Q) Can an ORDER BY clause be used in a creation of a view?

Q) ‘Order by’ is not allowed in a view How can you sort information from a view?

Q) What is a derived Table?

Q) What are Information Schema Views?

Q) What is a partitioned view?

Q) What is an Indexed View?

SQL Server Administration Interview Questions

Q) How you can get a list of all the table constraints in a database?

Q) How you can get the list of largest tables in a database?

Q) How you can move data or databases between servers and databases in SQL Server?

Q) If no size is defined while creating the database, what size will the database have?

Q) Can a database be shrunk with users active?

As a general practice, it is recommended to have dbo be the owner of all database objects However, in your database you find number of tables owned by a user other than dbo, how could you fix this?

Q) Why does a SQL statement work correctly outside of a user-defined function, but incorrectly inside it?

Q) Can a table be moved to different Filegroup?

Q) Can a database be shrunk to 0 Bytes, if not, why?

Q) What does the Automatic recovery do?

Q) Can an automatic recovery be initiated by a user?

Q) What is the primary use of the model database?

Q) What information is maintained within the msdb database?

Q) What stored procedure can you use to display the current processes?

Q) What stored procedure would you use to view lock information?

Q) How can a database be repaired?

Q) How can you find out if the current user is a member of the specified Microsoft® Windows NT® group or Microsoft SQL Server™ role?

Q) Your SQL Server is running out of disk space. You notice that there are several large files with LDF extensions What are these files?

Q) You notice that the transaction log on one of your databases is over 4GB The size of the data file is 2MB What could cause this situation, and how can you fix it?

Q) You accidentally delete the MSDB database What effect does this have on your existing SQL databases, and how do you recover?

Q) Where can you add custom error messages to SQL Server?

Q) Is it important for a Database Administrator to understand the operating system and file access?

Q) What is the difference between writing data to mirrored drives versus RAID5 drives

Q) In the physical file layout, where should the transaction log be stored in relation to the data file?

Q) You have separate development and production systems You want to move a copy of a development database into production To do this, you do a backup on the development system and restore to the production system After a few minutes, you begin getting calls from several customers saying that they are denied access to the system Why?

Q) What is a mixed extent?

Q) You have a table with close to 100 million records Recently, a huge amount of this data was updated Now, various queries against this table have slowed down considerably What is the quickest option to remedy the situation?

Q) How can you check the level of fragmentation on a table?

Q) You have developed an application which uses many stored procedures and triggers to update various tables Users ocassionally get locking problems Which tool is best suited to help you diagnose the problem?

Q) Which table keeps the locking information?

Q) You want to be sure that queries in a database always execute at the maximum possible speed. To achieve this goal you have created various indexes on tables Which other statement will keep the database in good condition?

Advanced SQL Server Interview Questions

Q) During a recent migration project, John inserted 10,000 records in a table that has an Identity Column called ticketID, which automatically increases by 1 each time a record is inserted in the table A month after the database went live; John noticed that record with ticketID 5123 has some incorrect information So John deletes this record and decides to re-insert this record in the table He wants to re-use the ticketID 5123 He needs to achieve this while the database is in production What should he do?

Q) Jenny wants to export data to Pivot table in Excel spreadsheet from a table in SQL Server This data changes frequently She wants to automate the process of updating the Excel spreadsheet using the SQL Job Scheduler What tool is the best choice for the task?

Q) You have written an application in VB which uses SQL 7 for its database You have used many stored procedure and triggers to make your application fast However, users complain that saving records take too much time To rectify the problem, you start the profiler and create a trace using the trace wizard How would you go about identifying the cause of the problem?

Q) You have a table with employee information that rarely changes However this table is used from many applications in the organization to validate the data and to produce reports What would be the optimal fill factor to choose for indexes created on this table?

Q) What is the difference between a fill factor of 100 and 0?

Q) How will you know when statistics on a table are obsolete?

Q) Explain different backup plans

Q) What is a full backup?

Q) Explain a differential backup

Q) Explain an incremental backup

Q) What is Log shipping?

Q) Every night you run a full backup After every 3 three hours you make a differential backup Every hour you make an incremental backup In a worst-case scenario, how much work you can lose?

Q) Explain a Checkpoint?

Q) Explain an Automatic Checkpoint

Q) How you can list all the tables in a database?

Q) How can you list all the columns in a database?

Q) How can you list all the table constraints in a database?

Q) What are the advantages DTS has over bcp?

Q) How do you rebuild an identity column?

Q) Create a DTS package to produce a text file using the ‘UPDATE STATISTICS’ command for the tables in a database with obsolete statistics.

Q) How can you transfer data from a text file to a database table? Or how can you export data from a table to a comma delimited (CSV) file? Or how can you import data from MS Access to a table in a database? Or how can you export data from a table to an Excel file?

Q) When does the auto update index statistics feature in SQL server turn itself on?Q) What specific conditions database should meet, before you can Bulk copy data into it using BCP

Q) What specific conditions database should meet, before you can Bulk copy data into it using BCP

SQL Server Replication Interview Questions

Q) What is database replication? What are the different types of replication you can set up in SQL Server?

Q) Which database stores information about replication?

Q) Your company has 50 branches all over the country All the branches, including the Head Office have SQL Server as the database Every night all 50 branches upload certain information to the Head Office Which Replication topology is best suited for the above scenario?

Q) Which of the following option(s) is(are) an inappropriate usage of merge replication: a company time sheet database, a static look up table, a high transaction based application that requires real time replication to subscribers or a company information price list that is updated at remote sites and replicated to a central databaseQ) What are the restraints imposed on the table design by a Merge Replication?

Q) What are the restraints imposed on the table design by a Merge Replication?

SQL Server Security Interview Questions

Q) A user is a member of the Public role and the Sales role The Public role has select permission on all the tables The Sales role does not have select permission on some of the tables Will the user be able to select from all tables?

Q) If a user does not have permission to a table, but has permission to a view created on it, will he be able to view the data in table?

Q) Tony works in the Sales Department and has created a table named OrderDetail for the Sales department You write a stored procedure which will help Mark, a sales representative, update the OrderDetail table However, when Mark uses the stored procedure he gets an error You realize that this is a security issue What is required to enable Mark to use your stored procedure?

SQL Transactions Interview Questions

Q) Define Concurrency Control

Q) What is Pessimistic Concurrency Control?

Q) What is Optimistic Concurrency Control?

Q) Explain Isolation levels

Q) What is the difference between the REPEATABLE READ and SERIALIZABLE isolation levels?

Q) What is the default isolation level in SQL server?

Q) What is the most restrictive isolation level?

Q) What is the least restrictive isolation level?

Q) How do you determine the current isolation level?

Q) What are the conditions an underlying table must satisfy before a cursor can be used by a positioned UPDATE or DELETE statement?

Q) Explain Locks

Q) Explain Lock escalation

Q) Under what condition it is possible to have a page level lock and row lock at the same time for a query?

Q) Explain how to use transactions efficiently

SQL Triggers Interview Questions

Q) What you can do to delete a table without the delete trigger firing?

Q) Other than Truncate statement, which other command can by-pass the trigger on the tables?

Q) What are the differences between Triggers and Stored Procedures?

Q) Is this statement true: A trigger can reference objects outside the current database?

Q) Can a trigger be created on a view?

Q) Will the WRITETEXT statement activate a trigger?

Q) Can a table be created inside a Trigger?

Q) When should you use an INSTEAD OF trigger?

Q) Can the “If Update (colName)” statement be used in a delete trigger?

Stored Procedures Interview Questions

Q) Why should one not prefix user stored procedures with ‘sp_’?

Q) What are the results of running this script?

Q) Which table keeps information about Stored Procedures?

Q) What will be the value of @@FETCH_STATUS if a row that was a part of the cursor resultset has been deleted from the database after the time the stored procedure that opened the cursor was executed?Q) Why and when do stored procedure recompile?

Q) Why and when do stored procedure recompile?

Q) How can you find out which stored procedures are recompiling?

Q) How can you stop stored procedures from recompiling?

SQL Cursors Interview Questions

Q) How many types of cursor type are there?

Q) What is the difference between insensitive and scroll cursor?

Q) If a table does not have a unique index, can a cursor be opened on it?

Q) Can a cursor be updated? If yes, how you can protect which columns are updated?

Q) While using a cursor, how can you differentiate between a deleted row and a row that has been inserted with NULL data values?

Concept-Based SQL Interview Questions

Q) How can you know if the row fetched from cursor is still valid in underlying table?

Q) How can you find out how many rows returned in a cursor?

Q) What does it mean if @@CURSOR_ROW returns a negative number?

Q) How can you set the threshold at which SQL Server will generate keysets asynchronously?

Q) What is the difference between Deallocate cursor and Close cursor?

Q) Define Cursor Locking

SQL Server Datatypes Interview Questions

Q) Between Cast and Convert which function would you prefer and why?

Q) What are the new data types are introduced in SQL 2000?

Q) Why it is recommended to avoid referencing a floating point column in the WHERE clause?

Q) You have to store user responses of ‘Yes’ and ‘No’ What kind of data type is best suited for this task?

Q) What is the Exact Numeric data type in SQL?

Q) Explain timestamp datatype?

Q) How can a user-defined datatype be created?

Q) What are the approximate numeric data types?

Q) You are creating an application where Users are asked their gender In the gender combo box you have three options: ‘Male’ , ‘Female’ and ‘I choose not to disclose’ These options are stored in the table as 1, 0 or NULL Which datatype should you use?

Q) Which data types generate inaccurate results if used with an = or <> comparison in a WHERE clause of a SQL statement?

Q) Beginning with SQL Server Version 7 0, a new enhanced data type nchar was added What type of data is supported with this data type?

Q) What will happen if a column containing char type data is changed to the nchar data type?

Q) To automatically record the time on which the data was modified in a table, which data type should you choose for the column?

XML in SQL Server Interview Questions

Q) What is XDR?

Q) What is the difference between FOR AUTO and FOR NESTED?

Q) What is the difference between FOR XML RAW and FOR XML AUTO?

Q)Explain FOR XML EXPLICIT Mode?

Q) Using the Customer, and Order table in Northwind database, Please write a query to produce XML?

Q) What is XML Schema?

Q) What is mapping schema?

Q) You have a table ‘customers’ , which has three fields, Address, CustomerID and ContactName You would like to retrieve rows as follows:

Q) What is XPath?

Q) What keyword you will use to get schema appended to the result set of a ‘for XML’ query?

Q) Explain Active/Passive and Active/Active cluster configurations?

Topics Covered in this SQL Server DBA Tutorial

SQL Server Interview Questions

SQL Server Interview Questions And Answers

General RDBMS Interview Questions And Answers

Basic SQL Server Interview Questions And Answers

SQL Designing And Programming Interview Questions And Answers

Most Asked SQL Server Interview Questions And Answers

Common SQL Server Interview Questions And Answers

Top SQL Server Interview Questions And Answers

Essential SQL Server Interview Questions And Answers

SQL Server Developer Interview Questions And Answers

Best SQL Server Interview Questions And Answers

MS SQL Server Interview Questions And Answers

SQL Server Interview Questions For Freshers

SQL Joins Interview Questions And Answers

SQL Server Query Interview Questions

SQL Server Interview Questions PDF

SQL Server Interview Questions By Shivprasad Koirala

SQL Views Interview Questions And Answers

SQL Query Interview Questions And Answers

SQL Server DBA Interview Questions

SQL Server DBA Interview Questions And Answers For Experienced

SQL Server Administration Interview Questions And Answers

Important SQL Queries Asked in Interviews

Complex SQL Server Interview Questions And Answers

Tricky SQL Queries For Interview

SQL Server Interivew Questions For Experienced

SQL Server Interview Questions And Answers For Experienced

SQL Interview Questions For Experienced Professionals

SQL Server Triggers Interview Questions

SQL Server Stored Procedures Interview Questions And Answers

SQL Server Cursors Interview Questions And Answers

SQL Datatypes Interview Questions And Answers

XML in SQL Interview Questions And Answers

Advanced SQL Server Interivew Questions And Answers

SQL Clustering Interview Questions

OTHER INTERVIEW QUESTIONS

SSRS Interview Questions

SQL Server Reporting Services Interview Questions

Advanced SSRS Interview Questions

SSRS Developer Interview Questions

SSRS Real Time Interview Questions

RELATED INTERVIEW QUESTIONS

SQL Interview Questions

Advanced SQL Interview Questions

Scenario Based SQL Interview Questions

RELATED INTERVIEW QUESTIONS

SSIS Interview Questions

SSIS Package Interview Questions

SSIS Checkpoint Interview Questions

SSIS Split Interview Questions

SSIS Event Handler Interview Questions

SSIS Interview Questions For Experienced

SSIS Practical Interview Questions

SSIS Scenario Based Interview Questions

RELATED INTERVIEW QUESTIONS

SQL Server Interview Questions

General Relational DBMS Interview Questions

Design and Programming Interview Questions

SQL Interview Questions For Experienced

SQL Interview Questions For 5+ Years Experienced

SQL Server View Interview Questions

SQL Server Administration Interview Questions

Advanced SQL Server Interview Questions

SQL Server Replication Interview Questions

SQL Server Security Interview Questions

SQL Transactions Interview Questions

SQL Triggers Interview Questions

Stored Procedures Interview Questions

SQL Cursors Interview Questions

Concept-Based SQL Interview Questions

SQL Server Datatypes Interview Questions

XML in SQL Server Interview Questions

RELATED INTERVIEW QUESTIONS