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.

ORACLE8 AND THE WINDOWS NT OPERATING SYSTEM

ORACLE8 AND THE WINDOWS NT OPERATING SYSTEM
The Oracle8 RDBMS for NT is written using Microsoft’s 32-bit API. By using the Microsoft 32-bit API the Oracle8 RDBMS has been tightly integrated with the underlying hardware. Oracle8’s architecture for Microsoft Windows NT has been implemented as a single multithreaded process to conform with the Windows NT memory model.Under the Windows NT operating system a process represents a logical unit of work or job that the operating system is to perform. A thread is one of many subtasks that are required to accomplish the job. The components of a thread include:
A unique identifier called a client ID.
The content of a set of registers that represent the state of the processor.
A stack for when the thread is running in user mode and a stack for when the thread is running in kernel mode.
The thread resides within the process’s virtual address space. When more then one thread exists in the same process, the threads share the address space of all the process’s resources. The NT kernel schedules processes thread(s) for execution. All processes running under Windows NT must have at least one thread before the process can be executed.Unlike Oracle8 for UNIX, Oracle8 for NT uses a single process with multiple threads, thereby sharing memory in a single address space. The database uses the operating system facility for preemptive scheduling and load balancing across multiple CPUs.The Oracle instance on Windows NT consists of a memory segment and a number of background threads. By default, the Oracle8 server and its associated background threads run in the Normal Priority class. In this class, the scheduler can dynamically vary the priority between 1 and 15, but it cannot raise the dynamic priority to the real-time priority class. The real-time priority class ranges from 16 to 31 and cannot vary in priority based on behavior.The Windows NT GUI and its associated utilities can be used to observe various portions of the Oracle8 RDBMS. The Windows menu is used to access the Windows NT control panel.By accessing the Windows NT CONTROL PANEL the administrator can perform various tasks One such task may be to observe, start, or stop any of the various services that are running on the machine. The various services associated with the Oracle RDBMS. The remainder of this chapter is used to investigate the various components of the Oracle8 RDBMS architecture. Where possible the GUI utilities provided by the Windows NT operating system are used to observe the various components of the RDBMS. The same utilities will also help develop our understanding of how the Oracle8 RDBMS is integrated with the Windows NT operating system.ORACLE8 RDBMS ARCHITECTUREThe architecture of the Oracle RDBMS is divided into two distinct parts. One part is called the Oracle database the other part is called the Oracle The Oracle database is defined as:
A logical collection of data to be treated as a unit (tables).
Operating system files called data files, redo log files, initialization files and control files.
The Oracle instance is defined as:
The software mechanism used for accessing and controlling the database.
Having at least four background threads called PMON, SMON, DBWR and LGWR.
Including memory structures called the SGA and the PGA.
Each Oracle instance is identified by a System Identifier (SID).
Instances and databases are independent of each other, but neither is of any use without the other. For the end user to access the database the Oracle instance must be started (the four background threads must be running) and the database must be mounted (by the instance) and opened. In the simple model a database can be mounted by only one instance. The exception to this is the Oracle parallel server, where a database can be mounted by more then one Oracle instance.ORACLE DATABASE STRUCTUREOur discussion of the Oracle RDBMS architecture will first focus on that part that makes up the Oracle database. The Oracle database has both a physical and a logical structure. The physical structure consists of the operating system files that make up the database. The logical structure is determined by the number of tablespaces and the database’s schema objects.TablespacesAll Oracle databases must consist of at least one logical entity called a tablespace. The characteristics of a tablespace are:
One or more per database. The database must have at least one tablespace called “SYSTEM.” The SYSTEM tablespace holds the Oracle Data Dictionary. The Data Dictionary holds the various system tables and views such as the Oracle performance tables, information about the users of the database, and how much space is left in the various tablespaces that make up the database. There are usually more tablespaces other than the SYSTEM tablespace. Most Oracle databases also include additional tablespaces. These tablespaces are used to hold user data for sorting, and indexes that are used to speed up data access. Additional tablespaces should be created to hold data that is being sorted and another tablespace to hold data that is required for read consistency.
The physical representation of the tablespace is called a data file (a tablespace may consist of more then one data file).
Can be taken off line (due to media failure or maintenance purposes) leaving the database running. The exception to this rule is that the SYSTEM tablespace cannot be taken off line if the database is to remain running.
Unit of space for object storage. Objects are tables, indexes synonyms, and clusters.
Contains default storage parameters for database objects.
When an end-user’s Oracle user ID is created the user is given access to a default tablespace and a temporary tablespace (where the sorting of data is performed).
Can be dropped (removed from the database).
As stated previously tablespaces are logical entities. Tablespaces are physically represented by files that are called data files. Data files have the following attributes:
Are operating system files.
There is one or more per tablespace.
The finest granularity of the data file is called the data block.
A collection of data blocks is called an extent.
A segment (by definition) consists of one or more extents (therefore to make a segment larger, extents are added to the segment).
A data file consists of segments.
Contain transaction System Change Numbers (SCNs).
Data File Contents and Types of SegmentsA data file can consist of several types of segments and a segment can consist of one of more extents. The four different types of segments are rollback segments, temporary segments, index segments and data segments.Rollback segments have the following attributes:
Records old data.
Provides for rollback of uncommitted transactions.
Provides information for read consistency.
Used during database recovery from media or processor failure.
Wrap-around/reuseable.
Can be dynamically created or dropped.
Rollback segments contain the following information:
Transaction ID.
File ID.
Block number
Row number
Column number
Row/column data.
Temporary segments have the following attributes:
Used by the Oracle RDBMS as a work area for sorting data.
The DBA defines which tablespace will contain temporary segments and therefore the tablespace where sorting will occur.
Index segments have the following attributes:
Allows for faster data retrieval by providing an index for the data in a table, thus eliminating a full table scan during the execution of a query (similar to how a reader would use the index in a book rather then scanning through the entire book to find a particular topic).
Data segments have the following attributes:
One per table/snapshot.
Contains all table data.
Data segments contain the following information:
Transaction ID.
File ID.
Block number
Row number
Column number
Row/column data.
Besides data files there are also files called redo log files. Redo log files record changes made to the database by various transactions. All changes made to the database will first be written to the redo log file. These files can also be written to an off-line log file (archived). Redo logs are used during database recovery to recover the database to the last physical backup or to the point in time of failure (for this type of recovery the database must be running in ARCHIVELOG mode). Redo log files have the following attributes:
Records new data.
Ensures permanence of data transactions.
Provides for roll forward recovery during database startup and after a media failure.
Redo log files contain:
Transaction IDs
Contents of redo log buffers.
Transaction SCN.
The Control FileEach database has one or more control files. The control file is used to store information about the database. The information in the control file includes:
Transaction System Change Number (SCN)
Location of all datafiles.
Names and locations of the redo log files.
Time stamp when database was created.
Database name.
Database size.
For database recovery purposes it is best to have multiple copies of the control file. Without the control file the Oracle RDBMS cannot find the pointers to the rest of the files that make up the database (data files and redo log files).The INIT.ORA FileThe init.ora file is the database initialization parameter file. It is only read at database start-up time. Every Oracle instance that is running will have its own init.ora file (the user should substitute with the Oracle System IDentifier for their instance). This file contains various initialization and tuning parameters that are needed by the RDBMS. Some of the parameters in the init.ora file are:
The maximum number of processes that the Oracle instance will use (PROCESSES=).
The name of the database (DB_NAME=).
Various parameters for tuning memory management (DB_BLOCK_BUFFERS, SORT_AREA_SIZE...)
The location of the control file(s).
How these parameters affect the starting and running of the database are covered in the chapters on Oracle RDBMS installation and performance analysis and tuning.

ASP.NET interview questions - 4

1. Does C# support multiple inheritance?
No, use interfaces instead

2. What’s the implicit name of the parameter that gets passed into the class’ set method?
Value, and its datatype depends on whatever variable we’re changing

3. What’s the top .NET class that everything is derived from?
System.Object.

4. How’s method overriding different from overloading?
When overriding, you change the method behavior for a derived class. Overloading simply involves having a method with the same name within the class.

5. What is CLR?
The .NET Framework provides a runtime environment called the Common Language Runtime or CLR (similar to the Java Virtual Machine or JVM in Java), which handles the execution of code and provides useful services for the implementation of the program. CLR takes care of code management at program execution and provides various beneficial services such as memory management, thread management, security management, code verification, compilation, and other system services. The managed code that targets CLR benefits from useful features such as cross-language integration, cross-language exception handling, versioning, enhanced security, deployment support, and debugging.

6. What is CTS?
Common Type System (CTS) describes how types are declared, used and managed in the runtime and facilitates cross-language integration, type safety, and high performance code execution.

7. What is CLS?
The CLS is simply a specification that defines the rules to support language integration in such a way that programs written in any language, yet can interoperate with one another, taking full advantage of inheritance, polymorphism, exceptions, and other features. These rules and the specification are documented in the ECMA proposed standard document, "Partition I Architecture", http://msdn.microsoft.com/net/ecma

8. What is strong name?
A name that consists of an assembly's identity—its simple text name, version number, and culture information (if provided)—strengthened by a public key and a digital signature generated over the assembly.

9. What is Application Domain?
The primary purpose of the AppDomain is to isolate an application from other applications. Win32 processes provide isolation by having distinct memory address spaces. This is effective, but it is expensive and doesn't scale well. The .NET runtime enforces AppDomain isolation by keeping control over the use of memory - all memory in the AppDomain is managed by the .NET runtime, so the runtime can ensure that AppDomains do not access each other's memory. Objects in different application domains communicate either by transporting copies of objects across application domain boundaries, or by using a proxy to exchange messages.

10. What is serialization in .NET? What are the ways to control serialization?
Serialization is the process of converting an object into a stream of bytes. Deserialization is the opposite process of creating an object from a stream of bytes. Serialization/Deserialization is mostly used to transport objects (e.g. during remoting), or to persist objects (e.g. to a file or database).Serialization can be defined as the process of storing the state of an object to a storage medium. During this process, the public and private fields of the object and the name of the class, including the assembly containing the class, are converted to a stream of bytes, which is then written to a data stream. When the object is subsequently deserialized, an exact clone of the original object is created. Binary serialization preserves type fidelity, which is useful for preserving the state of an object between different invocations of an application. For example, you can share an object between different applications by serializing it to the clipboard. You can serialize an object to a stream, disk, memory, over the network, and so forth. Remoting uses serialization to pass objects "by value" from one computer or application domain to another. XML serialization serializes only public properties and fields and does not preserve type fidelity. This is useful when you want to provide or consume data without restricting the application that uses the data. Because XML is an open standard, it is an attractive choice for sharing data across the Web. SOAP is an open standard, which makes it an attractive choice. There are two separate mechanisms provided by the .NET class library - XmlSerializer and SoapFormatter/BinaryFormatter. Microsoft uses XmlSerializer for Web Services, and uses SoapFormatter/BinaryFormatter for remoting. Both are available for use in your own code.

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).

SQL Query Fundamentals

The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:
SELECT select_list[INTO new_table_name]FROM table_list[WHERE search_conditions][GROUP BY group_by_list][HAVING search_conditions][ORDER BY order_list [ASC DESC] ]
select_list
Describes the columns of the result set. It is a comma-separated list of expressions. Each expression defines both the format (data type and size) and the source of the data for the result set column. Each select list expression is usually a reference to a column in the source table or view the data is coming from, but can be any other expression, such as a constant or a Transact-SQL function. Using the * expression in a select list specifies that all columns in the source table are returned.
INTO new_table_name
Specifies that the result set is used to create a new table. new_table_name specifies the name of the new table.
FROM table_list
Contains a list of the tables from which the result set data is retrieved. These sources can be:
· Base tables in the local server running Microsoft® SQL Server™.
· Views in the local SQL Server. SQL Server internally resolves a view reference to references against the base tables that make up the view.
· Linked tables, which are tables in OLE DB data sources made accessible to SQL Server. This is called a distributed query. OLE DB data sources can be accessed from SQL Server by linking them as a linked server, or referencing the data source in an OPENROWSET or OPENQUERY function.
The FROM clause can also contain join specifications, which define the specific path SQL Server is to use in navigating from one table to another.
The FROM clause is also used on the DELETE and UPDATE statements to define the tables that are modified.
WHERE search_conditions
The WHERE clause is a filter that defines the conditions each row in the source tables must meet to qualify for the SELECT. Only rows that meet the conditions contribute data to the result set. Data from rows that do not meet the conditions are not used.
The WHERE clause is also used on the DELETE and UPDATE statements to define the rows in the target tables that are modified.

GROUP BY group_by_list
The GROUP BY clause partitions the result set into groups based on the values in the columns of the group_by_list. For example, the Northwind Orders table has three values in ShipVia. A GROUP BY ShipVia clause partitions the result set into three groups, one for each value of ShipVia.

HAVING search_conditions

The HAVING clause is an additional filter that is applied to the result set. Logically, the HAVING clause filters rows from the intermediate result set built from the application of any FROM, WHERE, or GROUP BY clauses in the SELECT statement. HAVING clauses are most commonly used with a GROUP BY clause, although a GROUP BY clause is not required before a HAVING clause.
ORDER BY order_list [ ASC DESC ]

The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted in an ascending or descending sequence.

ORDER BY is important because relational theory specifies that the rows in a result set cannot be assumed to have any sequence unless ORDER BY is specified. ORDER BY must be used in any SELECT statement for which the order of the result set rows is important.
The clauses in a SELECT statement must be specified in the proper order.
Each reference to a database object must be unambiguous. Ambiguity can come from these sources:

· There may be multiple objects with the same name in a system. For example, both User1 and User2 may have defined a table named TableX. To resolve the ambiguity and specify the TableX owned by User1, qualify the table name with at least the user ID: ·

SELECT * FROM User1.TableX

· The database in which the object resides may not always be the current database when the SELECT statement is executed. To ensure that the proper object is always used, regardless of the current database setting, qualify the object name with the database and owner: ·

SELECT * FROM Northwind.dbo.Shippers