SQL Server FAQ's

SQL FAQS	NEXT >>
1. What is a constraint in SQL?
A constraint is a restriction. Placed at either column or table level, a constraint ensures that your data meets certain data integrity rules.
2. What is primary Key?
Primary keys are the unique identifiers for each row. They must contain unique values(and hence cannot be NULL). A table can have a maximum of one primary key.
3. How do you create a primary key?
We can create primary key using CREATE TABLE command or with an ALTER TABLE command Creating the primary key at table creation CREATE TABLE Customers { CustomerNo int IDENTITY NOTNULL PRIMARY KEY, CustomerName varchar(50) NOT NULL } Creating a primary key on an existing table ALTER TABLE Employees ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID)
4. What is a candidate key?
A table may have more than one combination of columns that could uniquely identify therows in a table; each combination is a candidate key
5. What are indexes and What is the difference betweenclustered and nonclustered indexes?
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrievethe data quicker There are clustered and nonclustered indexes. A clustered index is a special type of indexthat reorders the way records in the table are physically stored. Therefore table can haveonly one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the indexdoes not match the physical stored order of the rows on disk. The leaf node of anonclustered index does not consist of the data pages. Instead, the leaf nodes containindex rows.
6. How can you increase SQL performance ?
Following are tips which will increase your SQl performance :- Every index increases the time in takes to perform INSERTS, UPDATES andDELETES, so the number of indexes should not be very much. Try to usemaximum 4-5 indexes on one table, not more. If you have read-only table,then the number of indexes may be increased. Keep your indexes as narrow as possible. This reduces the size of the indexand reduces the number of reads required to read the index. Try to create indexes on columns that have integer values rather than charactervalues. If you create a composite (multi-column) index, the order of the columns inthe key are very important. Try to order the columns in the key as to enhanceselectivity, with the most selective columns to the leftmost of the key. v If you want to join several tables, try to create surrogate integer keys for thispurpose and create indexes on their columns. Create surrogate integer primary key (identity for example) if your table willnot have many insert operations. Clustered indexes are more preferable than nonclustered, if you need to selectby a range of values or you need to sort results set with GROUP BY or ORDERBY . If your application will be performing the same query over and over on thesame table, consider creating a covering index on the table. You can use the SQL Server Profiler Create Trace Wizard with "Identify Scansof Large Tables" trace to determine which tables in your database may needindexes. This trace will show which tables are being scanned by queries insteadof using an index.
7. What is DTS?
DTS is used to pull data from various sources into the star schema.
8. What is fillfactor?
The 'fill factor' option specifies how full SQL Server will make each index page. Whenthere is no free space to insert new row on the index page, SQL Server will create newindex page and transfer some rows from the previous page to the new one. This operationis called page splits. You can reduce the number of page splits by setting the appropriatefill factor option to reserve free space on each index page. The fill factor is a value from1 through 100 that specifies the percentage of the index page to be left empty. Thedefault value for fill factor is 0. It is treated similarly to a fill factor value of 100, thedifference in that SQL Server leaves some space within the upper level of the index treefor FILLFACTOR = 0. The fill factor percentage is used only at the time the index iscreated. If the table contains read-only data (or data that very rarely changed), you canset the 'fill factor' option to 100. When the table's data modified very often, you candecrease the fill factor to 70% or whatever you think is best.
9. What is RAID and how does it work ?
Redundant Array of Independent Disks (RAID) is a term used to describe the techniqueof improving data availability through the use of arrays of disks and various data-stripingmethodologies. Disk arrays are groups of disk drives that work together to achieve higherdata-transfer and I/O rates than those provided by single large drives. An array is a set ofmultiple disk drives plus a specialized controller (an array controller) that keeps track ofhow data is distributed across the drives. Data for a particular file is written in segmentsto the different drives in the array rather than being written to a single drive. For speed and reliability, it's better to have more disks. When these disks are arranged incertain patterns and use a specific controller, they are called a Redundant Array ofInexpensive Disks (RAID) set. There are several numbers associated with RAID, but themost common are 1, 5 and 10. RAID 1 works by duplicating the same writes on two hard drives. Let's assume you havetwo 20 Gigabyte drives. In RAID 1, data is written at the same time to both drives.RAID1 is optimized for fast writes. RAID 5 works by writing parts of data across all drives in the set (it requires at least threedrives). If a drive failed, the entire set would be worthless. To combat this problem, oneof the drives stores a "parity" bit. Think of a math problem, such as 3 + 7 = 10. You canthink of the drives as storing one of the numbers, and the 10 is the parity part. By removingany one of the numbers, you can get it back by referring to the other two, like this: 3 + X= 10. Of course, losing more than one could be evil. RAID 5 is optimized for reads. RAID 10 is a bit of a combination of both types. It doesn't store a parity bit, so it's fast,but it duplicates the data on two drives to be safe. You need at least four drives for RAID10. This type of RAID is probably the best compromise for a database server.
10. What's the difference between DELETE TABLE andTRUNCATE TABLE commands?
Following are difference between them :- DELETE TABLE syntax logs the deletes thus making the delete operationslow.TRUNCATE table does not log any information but it logs informationabout deallocation of data page of the table.So TRUNCATE table is faster ascompared to delete table.
SQL FAQS	NEXT >>