|
|
SQL Server Resources Normalization and levels of Normalization. When creating the logical design, normalization is the mathematical method of evaluating the relational quality of the data model. Normalization helps to simplify the structure of tables. Normalization results in the formation of tables that satisfy certain constraints, and represent certain normal forms. The most important and widely used of these are:
What's the difference between a primary key and a unique key? The first difference lies in the way the indexes are created. Primary key creates a clustered index on the column, where as unique key creates a nonclustered index by default. The difference between two indexes is a clustered index and keeps the data in the same physical order as the index. The Non-Clusterd Index is sorted and each index node points to an unsorted data row. The second difference is that, primary key doesn't allow NULLs, but unique key allows one NULL. Both primary key and unique key enforce uniqueness of the column on which they are defined. When to consider denormalization? Denormalization can be thought of to be the reverse process of normalization. It's the deliberate introduction of redundancy in to the database. However, the introduction of redundancy should be controlled with the objective of acheiving optimum query performance. The underlying technical issue is that the number of joins are reduced which results in better query perfomance.
Hints on implementing realtionships in the database.
As we know there are three types of relationships. There are one-to-one, one-to-many and many-to-many relationships.
One-to-One relationship : Implement as a single table and sometimes two tables with primary and foreign key relationships.
One-to-Many relationships : Implement as two tables with primary key and foreign key relationships.
Many-to-Many relationships : Implement a junction table with the keys from more than two tables forming the composite primary key of the junction table.
What are user defined datatypes and when should one go for them?
User defined datatypes are use to extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for an example, a column called Account_Num which appears in many tables. In all these tables it should be varchar(8). In this case we can create a user defined datatype called Account_num_type of varchar(8) and use it across all the tables.
What is bit datatype and what's the information that can be stored inside a bit column? Bit datatype is used to store boolean information like 1 or 0 (true or false) or NULL. Define candidate key, alternate key, composite key?
A candidate key is one that can identify each row of a table uniquely. 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. A key formed by combining at least two or more columns is called composite key.
What are defaults?
While inserting data, if no value is supplied to a particular column then a default is used.
Is there a column to which a default can't be bound?
IDENTITY, timestamp columns can't have defaults bound to them.
Explain different isolation levels?
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable.
When this "CREATE INDEX IxORDER ON Torder(orderno)" statement is executed, what type of Index will be created?
A Non-clustered index is created on #orderno of the Torder table.
What's the maximum size of a row?
8060 bytes.
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE command deletes all the rows in a table and each row gets logged in the transaction log. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row. But, it logs the deallocation of the data pages of the table. What are constraints? Explain different types of constraints Constraints enforce the integrity of the database automatically, without creating triggers, rules or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY What is RAID and what are different types of RAID configurations? RAID is a additopnal harddisks provided to offer fault tolerance to servers. RAID stands for Redundant Array of Inexpensive Disks. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. How do we improve performance of a query? Check whether there are correct indexes, Avoid table scans, Check for missing or out of date statistics, Check for blocking, Check for excess recompilations of stored procedures, Check for procedures or triggers without SET NOCOUNT ON, Check for complicated joins, Check excess usage of cursors and temporary tables. Tools for troubleshooting the performance problems SET SHOWPLAN_ALL ON Transact-SQL statements will not be executed. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.
SET SHOWPLAN_TEXT ON Transact-SQL statements.will not be executed. Instead, SQL Server returns detailed information about how the statements are executed.
SET STATISTICS IO ON Microsoft SQL Server will display information regarding the amount of disk activity generated by Transact-SQL statements. SQL Server Profiler SQL Server profiler watches SQL Server's traffic and events, recording the selected information to the screen, table, or file. Graphical execution plan in Query Analyzer SQL Query Analyzer is a tool that enables a database administrator or developer to write queries, execute multiple queries simultaneously, view results, analyze the query plan, and receive assistance to improve the query performance. How do we secure an SQL Server?
What is blocking ? Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode. Some situations under which you should update statistics: If there is significant change in the key values in the index If a large amount of data in an indexed column has been added, changed, or removed or the table has been truncated using the TRUNCATE TABLE statement and then repopulated Database is upgraded from a previous version Explian different types of BACKUPs avaialabe in SQL Server? Types of backups we can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. What is database replication? What are the different types of replication you can set up in SQL Server? 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:
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors? Cursors allow row-by-row prcessing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors. Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. Write down the general syntax for a SELECT statements covering all the options
What is a join and what are the 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 JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand? Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster. What is a self join? Self join is just like any other join, except that two instances of the same table will be joined in the query.
|