SQL Server Performance Tuning

SQL Server Performance Tuning

You can improve your SQL Server application performance by optimizing the queries you use. The following sections outline techniques you can use to optimize query performance.

• SQL Architecture and Memory
• Table and Index Structure
• Performance Tools and Monitoring
• Locking and Concurrency
• Query Optimization
• Programming Efficiency
• Resource Governor

1. Improve Indexes
   
   Creating useful indexes is one of the most important ways to achieve better query performance. Useful indexes help you find data with fewer disk I/O operations and less system resource usage.
   
   To create useful indexes, you much understand how the data is used, the types of queries and the frequencies they are run, and how the query processor can use indexes to find your data quickly.As with most performance optimization techniques, there are tradeoffs.
   
   For example, with more indexes, SELECT queries will potentially run faster. However, DML (INSERT, UPDATE, and DELETE) operations will slow down significantly because more indexes must be maintained with each operation. Therefore, if your queries are mostly SELECT statements, more indexes can be helpful. If your application performs many DML operations, you should be conservative with the number of indexes you create.
   • Create Highly-Selective Indexes
   • Create Multiple-Column Indexes
   • Avoid Indexing Small Tables
2. Choose What to Index
   
   We recommend that you always create indexes on primary keys. It is frequently useful to also create indexes on foreign keys. This is because primary keys and foreign keys are frequently used to join tables. Indexes on these keys lets the optimizer consider more efficient index join algorithms. If your query joins tables by using other columns, it is frequently helpful to create indexes on those columns for the same reason.
   
   When primary key and foreign key constraints are created, SQL Server Compact 3.5 automatically creates indexes for them and takes advantage of them when optimizing queries. Remember to keep primary keys and foreign keys small. Joins run faster this way.
   • Use Indexes with Filter Clauses
   
   Indexes can be used to speed up the evaluation of certain types of filter clauses. Although all filter clauses reduce the final result set of a query, some can also help reduce the amount of data that must be scanned.
3. Use the Query Optimizer
   
   When determining the access methods for base tables, the SQL Server Compact 3.5 optimizer determines whether an index exists for a SARG clause. If an index exists, the optimizer evaluates the index by calculating how many rows are returned. It then estimates the cost of finding the qualifying rows by using the index. It will choose indexed access if it has lower cost than table scan. An index is potentially useful if its first column or prefix set of columns are used in the SARG, and the SARG establishes a lower bound, upper bound, or both, to limit the search.
4. Understand Response Time vs. Total Time
   
   Response time is the time it takes for a query to return the first record. Total time is the time it takes for the query to return all records. For an interactive application, response time is important because it is the perceived time for the user to receive visual affirmation that a query is being processed. For a batch application, total time reflects the overall throughput. You have to determine what the performance criteria are for your application and queries, and then design accordingly.
   
   For example, suppose the query returns 100 records and is used to populate a list with the first five records. In this case, you are not concerned with how long it takes to return all 100 records. Instead, you want the query to return the first few records quickly, so that you can populate the list.
   • Index the ORDER-BY / GROUP-BY / DISTINCT Columns for Better Response Time
   The ORDER-BY, GROUP-BY, and DISTINCT operations are all types of sorting. The SQL Server Compact 3.5 query processor implements sorting in two ways. If records are already sorted by an index, the processor needs to use only the index. Otherwise, the processor has to use a temporary work table to sort the records first. Such preliminary sorting can cause significant initial delays on devices with lower power CPUs and limited memory, and should be avoided if response time is important.
5. Rewrite Subqueries to Use JOIN
   
   Sometimes you can rewrite a subquery to use JOIN and achieve better performance. The advantage of creating a JOIN is that you can evaluate tables in a different order from that defined by the query. The advantage of using a subquery is that it is frequently not necessary to scan all rows from the subquery to evaluate the subquery expression. For example, an EXISTS subquery can return TRUE upon seeing the first qualifying row.
   • Limit Using Outer JOINs
   OUTER JOINs are treated differently from INNER JOINs in that the optimizer does not try to rearrange the join order of OUTER JOIN tables as it does to INNER JOIN tables. The outer table (the left table in LEFT OUTER JOIN and the right table in RIGHT OUTER JOIN) is accessed first, followed by the inner table. This fixed join order could lead to execution plans that are less than optimal.
6. Use Parameterized Queries
   
   If your application runs a series of queries that are only different in some constants, you can improve performance by using a parameterized query. For example, to return orders by different customers, you can run the following query:
   
   SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?
   
   Parameterized queries yield better performance by compiling the query only once and executing the compiled plan multiple times.
7. Query Only When You Must
   
   The SQL Server Compact 3.5 query processor is a powerful tool for querying data stored in your relational database. However, there is an intrinsic cost associated with any query processor. It must compile, optimize, and generate an execution plan before it starts doing the real work of performing the plan. This is particularly true with simple queries that finish quickly. Therefore, implementing the query yourself can sometimes provide vast performance improvement. If every millisecond counts in your critical component, we recommend that you consider the alternative of implementing the simple queries yourself. For large and complex queries, the job is still best left to the query processor.
   
   For example, suppose you want to look up the customer ID for a series of orders arranged by their order IDs. There are two ways to accomplish this. First, you could follow these steps for each lookup:

    Open the Orders base table
    Find the row, using the specific "Order ID"
    Retrieve the "Customer ID"

Or you could issue the following query for each lookup:

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

The query-based solution is simpler but slower than the manual solution, because the SQL Server Compact 3.5 query processor translates the declarative SQL statement into the same three operations that you could implement manually. Those three steps are then performed in sequence. Your choice of which method to use will depend on whether simplicity or performance is more important in your application.

Logical Design & Physical Design of Database

Logical Design & Physical Design of Database

Logical design 
Defining business entities, attributes for each entity, and relationships among entities. 

The latter are sometimes referred to as cardinality and optionality rules. 

For example, a inventory store application might include entities for store, employee, customer, item and sales transaction. Each store can have multiple employees, but each employee might only be allowed to be a full-time employee in a single store. Similarly each sales transaction must have a single customer, and can have one or more items sold.

Logical data models (LDMs) are often produced in the form of an Entity Relationship Diagram (ERD), which uses notation for expressing entities as boxes and relationships as lines. Attributes either appear in the boxes or are listed for each entity. Relationships are lines between the boxes, with the cardinality and optionality expressed as a circle for zero (or optional), a vertical line for "one" and a crow's foot for "many". 

Store -||---employs---0|< Employee 

(a Store employs zero one or more Employees and an employee is employed by exactly one Store). 

Customer -||-- participates in -|< Sales Transaction 

(a Customer participates in one or more Sales Transactions and a Sales Transaction is participated in by exactly one Customer) 

Logical modeling is not specific to the database engine or other technical platform. It is organizationally independent as well. Logical models are generally highly normalized. Logical models are often used to derive or generate physical models via a physical design process. 

Note: Some methodologies (such as ORM) refer to a technology-independent data models as Conceptual Data Models (CDM) and a Logical Data Model is constrained by technology constraints.

Physical design 

Generating the schema for tables, indexes, default and check constraints, and views for a specific physical structure such as a database, file, or XML document. 

A physical model is generally specific to the database engine and version and is often optimized for a specific application usage of the data. Physical models include data types for each attribute and can be normalized or de-normalized. Physical models can change after the application is deployed in the production environment. 

For example, indexes might be altered to tune the performance. New tables, constraints, defaults and rules might be added to enhance the application's feature set and enforce new set of business rules.

Magic Tables in SQL Server

What are Magic Tables in SQL Server?

Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the inserted and the deleted tables.They are also referred to as the magic tables.These are the conceptual tables and are similar in structure to the table on which trigger is defined(the trigger table).

While using triggers these Inserted & Deleted tables (called as magic tables) will be created automatically.

The inserted table contains a copy of all records that are inserted in the trigger table.

The deleted table contains all records that have been deleted from deleted from the trigger table.

Whenever any updation takes place,the trigger uses both the inserted and deleted tables.

When we insert any record then that record will be added into this Inserted table initially, similarly while updating a record a new entry will be inserted into Inserted table & old value will be inserted into Deleted table. In the case of deletion of a record then it will insert that record into the Deleted table.

Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.

increase SQL performance

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.

User-Defined Data Types (UDDT)

What is a User-Defined Data Type?

A user-defined data type provides a convenient use of underlying native datatypes for columns known to have the same domain of possible values.

Example:
EXEC sp_addtype phone_number, 'VARCHAR(20)','NOT NULL'
CREATE TABLE Customer(cust_id smallint, cust_phone phone_number)

How to create User-Defined Data Types?

User-defined data types are based on the system data types in Microsoft SQL Server. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and NULLability. For example, a user-defined data type called postal_code could be created based on the char data type. User-defined data types are not supported in TABLE variables.

When a user-defined data type is created, you must supply these parameters:

1. Name
2. System data type upon which the new data type is based
3. NULLability (whether the data type allows NULL values)
   
   When NULLability is not explicitly defined, it will be assigned based on the ANSI NULL default setting for the database or connection.

Note: If a user-defined data type is created in the model database, it exists in all new user-defined databases. However, if the data type is created in a user-defined database, the data type exists only in that user-defined database.

---

Examples:

The SQL Server user defined data types can be created both with SQL Server Management Studio and T-SQL commands. Let's walk through samples of each option to serve as an example of SQL Server user defined data types can be used with defaults and rules.

1.Creating SQL Server User Defined Data Types in SQL Server Management Studio (SSMS)
	> Open SQL Server Management Studio. > Navigate to the Databases | AdventureWorks | Programmability | Types folder. > Right click on the Types folder and select New | User-Defined Data Type
2.Syntax for creating SQL Server user defined data type
	sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @NULLtype = ] 'NULL_type' ] [ , [ @owner = ] 'owner_name' ]

Here is a basic explanation of the four parameters from the system stored procedure:

Parameter Explanation @typename Name of new user defined data type that is being created @phystype Base system data type of SQL Server @NULLtype To specify that NULL values are allowed for this data type or not @owner Owner of this being created user defined data type

Below is the example default and rule code used in SSMS above: Example

Example Default and Rule Code 1.CREATE a default value for phone number to be used in example USE AdventureWorks GO CREATE DEFAULT Default_PhNo AS 'UnknownNumber' GO 2.CREATE a rule for phone number to be used in example Number will be in format +92-3335409953 or UnknownNumber by default USE AdventureWorks GO CREATE RULE rule_PhNo AS (@phone='UnknownNumber') OR (LEN(@phone)=14 AND SUBSTRING(@phone,1,1)= '+' AND SUBSTRING(@phone,4,1)= '-') GO

In the final code snippet, we will bind the rules and defaults to the user defined data types:

Commands to bind the defaults and rules to user defined data types

To bind a default 'Default_PhNo' to user defined data type 'PhoneNumb'

EXEC sp_bindefault 'Default_PhNo', 'PhoneNumb'

To bind a rule 'rule_PhNo' to user defined data type 'PhoneNumb'

EXEC sp_bindrule 'rule_PhNo', 'PhoneNumb'

Pre-defined Data Types in SQL Server Database

Data Types in SQL Server Database

In a Database, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, data and time data, binary strings, and so on.

Integer Types: To hold the Integer values it provides with tinyint, smallint, int and bigint data types with sizes 1, 2, 4 and 8 bytes respectively.

Boolean Type: To hold the Boolean values it provides with bit data type that can take a value of 1, 0, or NULL.

Note: The string values TRUE and FALSE can be converted to bitvalues. TRUE is converted to 1 and FALSE is converted to 0.

Decimal Types: To hold the decimal values it provides with the following types:

decimal[ (p[ , s] )] and numeric[ (p[ , s] )] p (precision) The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18. s (scale) The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 throughp. Scale can be specified only if precision is specified. The default scale is 0. Storage sizes of Decimal and Numeric types vary, based on the precision. Precision Storage bytes 1-9 5 10-19 9 20-28 13 29-38 17 Note: numeric is functionally equivalent to decimal. float [ ( n ) ] and real Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53. n value Precision Storage size 1-24 7 digits 4 bytes 25-53 15 digits 8 bytes

Monetary or Currency Types: To hold the Currency values it provides with the following types which takes a scale of 4 by default:

Data type Range Size money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes smallmoney - 214,748.3648 to 214,748.3647 4 bytes

Date and Time Values: To hold the Date and Time values of a day it provides with the following types:

Data type Range Accuracy datetime January 1, 1753, through December 31, 9999 3.33 milliseconds smalldatetime January 1, 1900, through June 6, 2079 1 minute Values with the datetime data type are stored internally by the Microsoft SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. The smalldatetime data type stores dates and times of day with less precision than datetime. The Database Engine storessmalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight.

String Values: To hold the string values it provides with the following types:

char [ ( n ) ] Fixed-length, non-Unicode character data with a length of nbytes. n must be a value from 1 through 8,000. The storage size is n bytes. varchar [ ( n | max ) ] Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. text It was equal to varchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varchar(max) instead. Unicode Data types for storing Multilingual Characters are nchar, nvarchar and ntext where n stands for national. nchar [ ( n ) ] Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times nbytes. nvarchar [ ( n | max ) ] Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. ntext It was equal to nvarchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use nvarchar(max) instead.

Binary Values: To hold the binary values likes images, audio clips and video clips we use the following types.

binary [ ( n ) ] Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes. varbinary [ ( n | max) ] Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. Image It was equal to varbinary(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varbinary(max) instead. Use char, nchar, binary when the sizes of the column data entries are consistent. Use varchar, nvarchar, varbinary when the sizes of the column data entries vary considerably. Use varchar(max), nvarchar(max), varbinary(max)when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

Other Types:Apart from the above it provides some additional types like -

timestamp: Is a data type that exposes automatically generated, unique binary numbers within a database. The storage size is 8 bytes. You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read. Uniqueidentifier: Is a 16-byte GUID which is initialized by using the newid() function or converting a string constant in the form of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx which is used to guarantee that rows are uniquely identified across multiple copies of the table. Xml: Is the data type that stores XML data. You can store xmlinstances in a column, or a variable of xml type. The stored representation of xml data type instances cannot exceed 2 gigabytes (GB) in size.

SQL SERVER Interview Questions-part8

1. What is BCP? When do we use it?
   BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.
2. What should we do to copy the tables, schema and views from one SQL Server to another?
   We have to write some DTS packages for it. 

SQLSERVER Interview Questions-part7

1. Can you link only other SQL Servers or any database servers such as Oracle?
   We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.
2. Which stored procedure will you be running to add a linked server?
   sp_addlinkedserver, sp_addlinkedsrvlogin
3. What are the OS services that the SQL Server installation adds?
   MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
4. Can you explain the role of each service?
   SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintanance, Backups DTC - Is for linking and connecting to other SQL Servers
5. How do you troubleshoot SQL Server if its running very slow?
   First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes
6. Due to N/W or Security issues client is not able to connect to server or vice versa. How do you troubleshoot?
   First I will look to ensure that port settings are proper on server and client Network utility for connections. ODBC is properly configured at client end for connection; Makepipe & readpipe are utilities to check for connection. Makepipe is run on Server and readpipe on client to check for any connection issues.
7. What are the authentication modes in SQL Server?
   Windows mode and mixed mode (SQL & Windows).
8. Where do you think the users names and passwords will be stored in sql server?
   They get stored in master db in the sysxlogins table.
9. What is log shipping? Can we do logshipping with SQL Server 7.0?
   Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.
10. SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow?
    For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER Ã?¢â‚¬â€œm which will basically bring it into the maintenance mode after which we can restore the master db. 

SQL SERVER Interview Questions-part6

1. Which TCP/IP port does SQL Server run on?
   SQL Server runs on port 1433 but we can also change it for better security.
2. From where can you change the default port?
   From the Network Utility TCP/IP properties �> Port number.both on client and the server.
3. Can we use Truncate command on a table which is referenced by FOREIGN KEY?
   No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
4. What is the use of DBCC commands?
   DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
5. What command do we use to rename a db?
   sp_renamedb â€Ë?oldname’ , â€Ë?newname’
6. Sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases?
   In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode. 
7. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
   Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
8. What do you mean by COLLATION?
   Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.
9. When do you use SQL Profiler?
   SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..
10. What is a Linked Server?
    Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. 

SQL SERVER Interview Questions-part5

1. What are triggers?
   Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
2. How many triggers you can have 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.
3. How to invoke a trigger on demand?
   In SQL Server 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. 
4. What are the uses of Triggers?
   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. Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.
5. What is a self join? Explain it with an example.
   Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join. 
6. What is normalization?
   The Process of organizing relational data into tables is actually referred to as normalization.
7. What is a Stored Procedure?
   Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.
8. Can you give an example of Stored Procedure?
   sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.
9. What is the basic difference between clustered and a non-clustered index?
   The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
10. When do we use the UPDATE_STATISTICS command?
    This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly. 

SQL SERVER Interview Questions-part4

1. What are cursors?
   Cursors allow row-by-row prcessing of the resultsets.
2. Explain different types of cursors.
   Types of cursors:
   Static, Dynamic, Forward-only, Keyset-driven.
3. What are the disadvantages of cursors?
   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.
4. How can you avoid 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.
5. What is a join?
   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.
6. Explain different types of joins.
   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.
7. Can you have a nested transaction?
   Yes, very much.
8. What is an extended stored procedure?
   An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.
9. Can you instantiate a COM object by using T-SQL?
   Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. 
10. What is the system function to get the current user's user id?
    USER_ID(). Some more system functions: USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME(). 

SQLSERVER Interview Questions-part3

1. What is blocking and how would you troubleshoot it?
   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.
2. How to restart SQL Server in single user mode?
   SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very 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.
3. what are the DBCC commands that you commonly use for database maintenance?
   DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.
4. What are statistics?
   Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.
5. Under what circumstances they go out of date, how do you update statistics?
   Some situations under which you should update statistics:
   1) If there is significant change in the key values in the index
   2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
   3) Database is upgraded from a previous version Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats
6. What are the different ways of moving data/databases between servers and databases in SQL Server?
   BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.
7. Explian different types of BACKUPs avaialabe in SQL Server?
   Full database backup, differential database backup, transaction log backup, filegroup backup.
8. What is database replicaion?                                                                 Replication is the process of copying/moving data between databases on the same or different servers.
9. What are the different types of replication you can set up in SQL Server?
   SQL Server supports the following types of replication scenarios:
   * Snapshot replication
   * Transactional replication (with immediate updating subscribers, with queued updating subscribers)
   * Merge replication \
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. 

SQLSERVER Interview Questions-part2

1. What's the maximum size of a row?
   8060 bytes.
2. What is lock escalation?
   Strong names are required to store shared assemblies in the global assembly cache (GAC). This is because the GAC allows multiple versions of the same assembly to reside on your system simultaneously, so that each application can find and use its own version of your assembly. This helps avoid DLL Hell, where applications that may be compiled to different versions of your assembly could potentially break because they are all forced to use the same version of your assembly. Another reason to use strong names is to make it difficult for hackers to spoof your assembly, in other words, replace or inject your assembly with a virus or malicious code.
3. 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 be rolled back.
4. What are constraints? Explain different types of constraints.
   Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY 
5. What is an index?
   Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.
6. What are the types of indexes?
   Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
7. I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
   If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
8. What is RAID and what are different types of RAID configurations?
   RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.
9. What are the steps you will take, if you are tasked with securing an SQL Server?
   Preferring NT authentication, using server, databse and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server etc.
10. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
    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. 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.

SQL SERVER Interview Questions-part1

1. What is denormalization and when would you go for it?
   As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
2. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
   One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
3. 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.
4. What are user defined datatypes and when you should go for them?
   User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called ZIP_Code which appears in many tables. In all these tables it should be varchar(6). In this case you could create a user defined datatype called ZIP_Code_Type of varchar(6) and use it across all your tables.
5. 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). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.
6. 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.
7. 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. See CREATE DEFUALT in books online.
8. What is a transaction and what are ACID properties?
   A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
9. 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.
10. How does .NET and SQL SERVER thread is work?
    There are two types of threading pre-emptive and Non-preemptive but Sql Server support Non-preemptive and .NET thread model is different. Because Sql have to handle thread in different way for SQLCLR this different thread are known as Tasking of Threads . In this thread there is a switch between SQLCLR and SQL SERVER threads .SQL SERVER uses blocking points for transition to happen between SQLCLR and SQL SERVER threads.