SQL Server Basics

Hello Friends, While studying SQL SERVER, I found some good basics questions and i thought to share the same with you ...

As you all know i have collected these document for our reference.

SQL Server Basics

What is normalization? what are different types of normalization?

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as normalization. Benefits of normalizing your database will include : Avoiding repetitive entries Reducing required storage space Preventing the need to restructure existing tables to accommodate new data Increased speed and flexibility of queries, sorts, and summaries. Following are the three normal forms : First Normal Form For a table to be in first normal form, data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields. Second Normal Form The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. Or in other words, each non-key field should be a fact about all the fields in the primary key. Third Normal Form A non-key field should not depend on other Non-key field.

What is denormalization?

Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design - to enhance performance or if we are doing data warehousing and data mining. The sacrifice to performance is that you increase redundancy in database.

What is a candidate key?

A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key. During database design you can pick up one of the candidate keys to be the primary key.

What are the different types of joins? What is the difference between them?

Inner Join Inner join shows matches only when they exist in both tables. Example is given below SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID Left Outer Join Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Example is given below SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID Right Outer Join Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Example is given below SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

What are indexes? what is the difference between clustered and nonclustered indexes?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quickly. There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way in which records in the table are physically stored. Therefore, table can have only 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 index does not match the physical stored order of the rows on disk.The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

How can you increase SQL performance?

Following are tips which will increase your SQL performance : Every index increases the time takes to perform INSERTS, UPDATES, and DELETES, so the number of indexes should not be too much. Try to use maximum 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 index and reduces the number of reads required to read the index. Try to create indexes on columns that have integer values rather than character values. If you create a composite (multi-column) index, the orders of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key. If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns. Create surrogate integer primary key (identity for example) if your table will not have many insert operations. Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY. If your application will be performing the same query over and over on the same table, consider creating a covering index on the table. You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.

What is the use of OLAP?

OLAP is useful because it provides fast and interactive access to aggregated data and the ability to drill down to detail.

What is a measure in OLAP?

Measures are the key performance indicator that you want to evaluate. To determine which of the numbers in the data might be measures. A rule of thumb is: If a number makes sense when it is aggregated, then it is a measure.

What are dimensions in OLAP?

Dimensions are the categories of data analysis. For example, in a revenue report by month by sales region, the two dimensions needed are time and sales region. Typical dimensions include product, time, and region.

What are levels in dimensions?

Dimensions are arranged in hierarchical levels, with unique positions within each level. For example, a time dimension may have four levels, such as Year, Quarter, Month, and Day. Or the dimension might have only three levels, for example, Year, Week, and Day.The values within the levels are called members. For example, the years 2002 and 2003 are members of the level year in the Time dimension.

What are fact tables and dimension tables in OLAP?

The dimensions and measures are physically represented by a star schema. Dimension tables revolve around fact table. A fact table contains a column for each measure as well as a column for each dimension. Each dimension column has a foreign-key relationship to the related dimension table, and the dimension columns taken together are the key to the fact table.

What is DTS?

DTS is used to import data and while importing it helps us to transform and modify data. The name itself is self explanatory DTS ( Data transformation Services).

What is fill factor ?

The 'fill factor' option specifies how full SQL Server will make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time when the index is created. If the table contains read-only data (or data that very rarely changed), you can set the 'fill factor' option to 100. When the table's data is modified very often, you can decrease the fill factor to 70% or whatever you think is best.

What is the difference between DELETE TABLE and TRUNCATE TABLE commands?

Following are difference between them:- DELETE TABLE syntax logs the deletes thus make the delete operation slow.TRUNCATE table does not log any information but it logs information about deallocation of data page of the table so TRUNCATE table is faster as compared to delete table. DELETE table can have criteria while TRUNCATE cannot. TRUNCATE table does not invoke trigger.

What are different transaction levels in SQL SERVER?

Transaction Isolation level decides how is one process isolated from other process. Using transaction levels, you can implement locking in SQL SERVER. There are four transaction levels in SQL SERVER:- READ COMMITTED The shared lock is held for the duration of the transaction, meaning that no other transactions can change the data at the same time. Other transactions can insert and modify data in the same table,however, as long as it is not locked by the first transaction. READ UNCOMMITTED No shared locks and no exclusive locks are honored. This is the least restrictive isolation level resulting in the best concurrency but the least data integrity. REPEATABLE READ This setting disallows dirty and non-repeatable reads. However, even though the locks are held on read data, new rows can still be inserted in the table, and will subsequently be interpreted by the transaction. SERIALIZABLE This is the most restrictive setting holding shared locks on the range of data. This setting does not allow the insertion of new rows in the range that is locked; therefore, no phantoms are allowed. Following is the syntax for setting transaction level in SQL SERVER. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Can we suggest locking hints to SQL SERVER?

We can give locking hints that helps you over ride default decision made by SQL Server. For instance, you can specify the ROWLOCK hint with your UPDATE statement to convince SQL Server to lock each row affected by that data modification. Whether it is prudent to do so is another story; what will happen if your UPDATE affects 95% of rows in the affected table? If the table contains 1000 rows, then SQL Server will have to acquire 950 individual locks, which is likely to cost a lot more in terms of memory than acquiring a single table lock. So think twice before you bombard your code with ROWLOCKS.

What is LOCK escalation?

Lock escalation is the process of converting of low-level locks (like rowlocks, page locks) into higher-level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards SQL Server dynamically manages it.

What are the different ways of moving data between 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.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

You can use Having Clause with the GROUP BY function in a query and WHERE Clause is applied to each row before, they are part of the GROUP BY function in a query.

What is the difference between UNION and UNION ALL SQL syntax?

UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table, while UNION ALL selects all records from both the tables.

How can you raise custom errors from stored procedure?

The RAISERROR statement is used to produce an ad hoc error message or to retrieve a custom message that is stored in the sysmessages table.

what is ACID fundamental? What are transactions in SQL SERVER?

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: Atomicity A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them is performed. Consistency When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. Isolation Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either see data in the state it was before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed. Durability After a transaction has completed, its effects are permanently in place in the system.The modifications persist even in the event of a system failure.

What is DBCC?

DBCC (Database Consistency Checker Commands) is used to check logical and physical consistency of database structure.DBCC statements can fix and detect problems. These statements are grouped in to four categories:- Maintenance commands like DBCC DBREINDEX, DBCC DBREPAR etc, they are mainly used for maintenance tasks in SQL SERVER. Miscellaneous commands like DBCC ROWLOCK, DBCC TRACEO etc, they are mainly used for enabling row-level locking or removing DLL from memory. Status Commands like DBCC OPENTRAN, DBCC SHOWCONTIG etc , they are mainly used for checking status of the database. Validation Commands like DBCC CHECKALLOC, DBCCCHECKCATALOG etc , they perform validation operations on database.

What is the purpose of Replication?

Replication is way of keeping data synchronized in multiple databases. SQL server replication has two important aspects publisher and subscriber. Publisher Database server that makes data available for replication is called as Publisher. Subscriber Database Servers that get data from the publishers is called as Subscribers.

What are the different types of replication supported by SQL Server?

There are three types of replication supported by SQL SERVER:- Snapshot Replication Snapshot replication takes snapshot of one database and moves it to the other database. After initial load data can be refreshed periodically. The only disadvantage of this type of replication is that all data has to be copied each time the table is refreshed. Trnsactional Replication In transactional replication data is copied first time as in snapshot replication, but later only the transactions are synchronized rather than replicating the whole database. you can either specify to run continuosly or on periodic basis. Merge Replication Merge replication combines data from multiple sources into a single central database. Again as usual, the initial load is like snapshot but later it allows change of data both on subscriber and publisher, later when they come on-line it detects and combines them and updates accordingly.

What is BCP utility in SQL SERVER?

BCP (Bulk Copy Program) is a command line utility by which you can import and export large amounts of data in and out of SQL SERVER database.

What are the different types of triggers in SQL SERVER 2000?

There are two types of triggers : INSTEAD OF triggers INSTEAD OF triggers fire in place of the triggering action. For Example, if an INSTEAD OF UPDATE trigger exists on the Sales table and an UPDATE statement is executed against the Sales table, the UPDATE statement will not change a row in the sales table. Instead, the UPDATE statement causes the INSTEAD OF UPDATE trigger to be executed, which may or may not modify data in the Sales table. AFTER triggers AFTER triggers execute following the SQL action, such as an insert, update, or delete. This is the traditional trigger which existed in SQL SERVER. INSTEAD OF triggers gets executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER triggers gets executed after these constraints are checked. Unlike AFTER triggers, INSTEAD OF triggers can be created on views.

If we have multiple AFTER Triggers on table how can we define the sequence of the triggers?

If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder.

What is SQL injection?

It is a Form of attack on a database-driven Web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization’s host computers through the computer that is hosting the database. SQL injection attacks typically are easy to avoid by ensuring that a system has strong input validation.

Enjoy the day with Lots of smile and Happiness.....