Use ONLINE and SORT_IN_TEMPDB Effectively

The ONLINE and SORT_IN_TEMPDB index options affect both the temporary space requirements and performance of the index create or rebuild operation. The advantages and disadvantages of each are covered in this section.
When considering the ONLINE option, you must weigh the need for a performant index operation versus the need for concurrent user access to the underlying data.
· To achieve the best performance, that is, the least time to create or rebuild an index, set ONLINE to OFF. However, this prevents all user access to the underlying table for the duration of the index create or rebuild operation.
· To achieve the best concurrency, that is, the least impact on other users accessing the table, set ONLINE to ON. However, the index operation will take more time.
You must also take into consideration the extra temporary space requirements of the online operation.
· To use the least amount temporary space while rebuilding a clustered index, set ONLINE to OFF.
· To use the least amount of temporary space while rebuilding a nonclustered index, set ONLINE to ON.
· If there are concurrent user transactions on the table during the online index operation, you must plan for additional space in tempdb for the version store.
For more information, see Determining the Amount of Temporary Space Used in this paper.
As we discussed earlier, when SORT_IN_TEMPDB is set to ON, sort runs and other intermediate tasks are stored in tempdb rather than the user database. Setting this option to ON can have two advantages:
· You can achieve the most contiguous space in the index. When the sort extents are held separately in tempdb, the sequence in which they are freed has no affect on the location of the index extents. Also, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup. This increases the chance that index extents will be contiguous.

When both SORT_IN_TEMPDB and ONLINE are set to ON, the index transactions are stored in the tempdb transaction log, and the concurrent user transactions are stored in the transaction log of the user database. This allows you to truncate the transaction log of the user database during the index operation if needed. Additionally, if the tempdb log is not on the same disk as the user database log, the two logs are not competing for the same disk space.

Measuring Temporary Disk Space Usage

When the temporary space is used from the tempdb database, you can measure the amount of temporary space used by an index operation by using the dynamic management views provided in SQL Server 2005. There are three views that report the temporary disk space used by any operation in tempdb:
· sys.dm_db_task_space_usage
· sys.dm_db_session_space_usage
· sys.dm_db_file_space_usage
While these views only pertain to the tempdb database, you can set the SORT_IN_TEMPDB option to ON when testing for disk space usage requirements and then plan for the same space allocation in your user database.
The sys.dm_db_task_space_usage dynamic management view provides tempdb usage information for each task. As a task (such as an index rebuild) progresses, you can monitor how much temporary space the task is using. However, as soon as the task completes, the counters in the view are reset to zero. So, unless you happen to query this view just at the moment before the task completes, you can’t get the total amount of tempdb space used by a given task. However, when the task is completed these values are aggregated at the session level and stored in the sys.dm_db_session_space_usage view.
The sys.dm_db_session_space_usage provides tempdb usage information for each session. The easiest way to measure the tempdb space used by a given operation is to query sys.dm_db_session_space_usage for your session before and after the operation. However, there is a catch. The data in sys.dm_db_session_space_usage is not updated until the completion of the batch; therefore, you must execute these statements as three separate batches. Essentially, all you really need is three GO statements, as shown in the following example:
SELECT * FROM sys.dm_db_session_space_usage WHERE session_id = @@spid;
GO

GO
SELECT * FROM sys.dm_db_session_space_usage WHERE session_id = @@spid;

GO
When you query the sys.dm_db_session_space_usage view, pay attention to the following two columns in the result set:
· internal_objects_alloc_page_count: This column represents the space used by the sort runs while creating or rebuilding an index.
· user_objects_alloc_page_count: This column represents the tempdb space used by the temporary mapping index. The temporary mapping index is created only when an online index operation creates, drops, or rebuilds a clustered index.
To measure the size of the version store, you can query the version_store_reserved_page_count column in the sys.dm_db_file_space_usage view. The version store size can also be monitored by using the System Monitor (perfmon) counter Version Store Size (KB) in the Transactions performance object. The amount of space required for the version store depends on the size and duration of the transactions that change the data in the underlying table.

Advanced Query Concepts - Sql or Pl-Sql

Advanced Query Concepts
After you have mastered query fundamentals, you can explore these advanced query concepts for query solutions:
· Using aggregate functions in the select list
· Grouping rows with GROUP BY
· Combining results with UNION
· Subquery fundamentals
· Conditional data processing using CASE
· Parallel queries
· Summarizing data


Using COUNT(*)
COUNT(*) does not require an expression parameter because it does not use information about any particular column. It counts the total number of rows that meet the qualifications of the query. COUNT(*) returns the number of rows that match the search conditions specified in the query without eliminating duplicates. It counts each row separately, including rows that contain null values. This query finds the total number of books in titles:USE pubsSELECT COUNT(*)FROM titles
Here is the result set:
18
(1 row(s) affected)

COUNT(*) can be combined with other aggregate functions. This query shows COUNT(*) combined with an AVG function in which both aggregate functions aggregate data only from the rows that satisfy the WHERE clause search condition:

USE pubs
SELECT COUNT(*), AVG(price)
FROM titles
WHERE advance > $1000

Here is the result set:
----------- ------
15 14.42
(1 row(s) affected)


Using DISTINCT
The DISTINCT keyword is optional with SUM, AVG, and COUNT. When DISTINCT is used, duplicate values are eliminated before the sum, average, or count is calculated.
If you use DISTINCT, the expression must consist of a column name only. It cannot include an arithmetic expression.
This query returns the average prices of business books (without duplicate values):
USE pubs
SELECT AVG(DISTINCT price)FROM titlesWHERE type = ‘business’

Here is the result set:
14.64
(1 row(s) affected)

Without DISTINCT, the AVG function finds the average price of all business titles:
USE pubs
SELECT AVG(price)FROM titlesWHERE type = ‘business


Here is the result set:
13.73
(1 row(s) affected)

Null Values
Null values in a column are ignored while an aggregate function is operating. For example, the count of advances in the titles table is not the same as the count of title names because null values in the advance column are not counted.
USE pubs
SELECT COUNT(advance)
FROM titles
Here is the result set:
16
(1 row(s) affected)

USE pubs
SELECT COUNT(title)FROM titles
Here is the result set:
18
(1 row(s) affected)

If no rows meet the condition(s) specified in the WHERE clause, COUNT returns a value of zero. The other functions all return NULL. COUNT(*), counts each row, even if all column values are NULL. Here are examples:
USE pubs
SELECT COUNT(DISTINCT title)FROM titlesWHERE type = ‘poetry’
Here is the result set:
0
(1 row(s) affected)

USE pubs
SELECT AVG(advance)FROM titlesWHERE type = ‘poetry’
Here is the result set:
(null)
(1 row(s) affected)


Grouping Rows with GROUP BY
The GROUP BY clause is used to produce aggregate values for each row in the result set. When used without a GROUP BY clause, aggregate functions report only one aggregate value for a SELECT statement.
This example returns the number of units sold for each product in category 2:
USE Northwind
SELECT OrdD.ProductID AS ProdID,SUM(OrdD.Quantity) AS AmountSold
FROM [Order Details] AS OrdD
JOIN Products as PrdON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2
GROUP BY OrdD.ProductID

The GROUP BY keywords are followed by a list of columns, known as the grouping columns. The GROUP BY clause restricts the rows of the result set; there is only one row for each distinct
value in the grouping column or columns. Each result set row contains summary data related to the specific value in its grouping columns.
There are restrictions on the items that can be specified in the select list when a SELECT statement contains a GROUP BY. Items allowed in the select list are:
· The grouping columns.
· Expressions that return only one value for each value in the grouping columns, such as aggregate functions that have a column name as one of their parameters. These are known as vector aggregates.

You cannot use GROUP BY or HAVING on ntext,text,image, or bit columns unless they are in a function that returns a value having another data type.
Examples of such functions are SUBSTRING and CAST.

MS SQL Interview Questions -1

1. What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

2. Write a SQL Query to find first Week Day of month?
SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

3. How to find 6th highest salary from Employee table
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee
ORDER BY salary DESC) a ORDER BY salary

4. What is a join and List different types of joins.
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS Joints. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

5. How can I enforce to use particular index?
You can use index hint (index=index_name) after the table name. SELECT au_lname FROM authors (index=aunmind)

6. What is sorting and what is the difference between sorting and clustered indexes?
The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. This will happen by the time when we retrieve data from database. Clustered indexes physically sorting data, while inserting/updating the table.

7. What are the differences between UNION and JOINS?
A join selects columns from 2 or more tables. A union selects rows.

8. What is the Referential Integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value

9. What is the row size in SQL Server 2000?
8060 bytes.

10. How to determine the service pack currently installed on SQL Server?
The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. eg: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

11. What is the purpose of UPDATE STATISTICS?
Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

12. What is the use of SCOPE_IDENTITY() function?
Returns the most recently created identity value for the tables in the current execution scope.

13. What are the different ways of moving data/databases between servers and databases in SQL Server?
There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, detaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

14. How do you transfer data from text file to database (other than DTS)?
Using the BCP (Bulk Copy Program) utility.

15. What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can't be rolled back.

16. What is a deadlock?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

17. What is a LiveLock?
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

18. How to restart SQL Server in single user mode?
From Startup Options :- Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager. Under the 'General' tab, click on 'Startup Parameters'. Enter a value of -m in the Parameter.

19. Does SQL Server 2000 clustering support load balancing?
SQL Server 2000 clustering does not provide load balancing; it provides failover support. To achieve load balancing, you need software that balances the load between clusters, not between servers within a cluster.

20. What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several different sources of data in one transaction. MS DTC coordinates committing the distributed transaction across all the servers enlisted in the transaction.

21. What is DTS?
Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations.

22. What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.

23. What are the constraints ?
Table Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. SQL Server 2000 supports five classes of constraints. NOT NULL , CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY.

24. What is Transaction?
A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction.

25. What is Isolation Level?
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. A lower isolation level increases concurrency, but at the expense of data correctness. Conversely, a higher isolation level ensures that data is correct, but can affect concurrency negatively. The isolation level required by an application determines the locking behavior SQL Server uses. SQL-92 defines the following isolation levels, all of which are supported by SQL Server:

Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read).
Read committed (SQL Server default level).
Repeatable read.
Serializable (the highest level, where transactions are completely isolated from one
another).