SQL Server Interview Questions and Answers (Basics)

What is a view?


MS SQL Server 2008 View

A MS SQL Server 2008 view can be thought of as a stored query accessible as a virtual table. It can be used for retrieving data as well as updating or deleting rows. Views in MS SQL Server provide a preset way to view data from one or more tables. They may also include aggregate fields (e.g., COUNT, SUM). Views allow your users to query a single object which behaves like a table and contains the needed joins and fields you have specified. In this way, a simple query (SELECT * FROM ViewName) can produce a more refined result which can serve as a report and answer business questions.

Rows updated or deleted in the MS SQL Server 2008 view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does the data in the view-as views are the way to look at parts of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using a standard T-SQL select command and can come from one to many different base tables or even other views.





Two main purposes of creating a MS SQL Server 2008 view are


1.) provide a security mechanism which restricts users to a certain subset of data
2.)provide a mechanism for developers to customize how users can logically view the data.



What is an index? Clustered and non-clustered indexes in SQL Server






MS SQL Server 2008 Index

An MS SQL Server 2008 index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users can see the MS SQL Server 2008 index name but cannot see the indices themselves; they are just used to speed up queries. Effective indices are one of the best ways to improve performance of a database application.










An MS SQL Server 2008 Index can give you improved query performance because a seek action occurs for retrieving records from your table in a query. A seek means you were able to locate record(s) without having to examine every row to locate those record(s).










A table scan occurs when there is no index available or when a poorly created index exists on the table for a query running against that table. In a An MS SQL Server 2008 table scan, SQL Server examines every row in the table to satisfy the query results. An MS SQL Server 2008 table scans are sometimes unavoidable, but on large tables, scans have a significant impact on performance










SQL Server 2008 Interview Questions and Answers for Experienced






















Maximum number of Indices from Ms SQL Server 2008 is 1 clustered + 999 non-clustered indices



If a table is created with no indexes, the data rows are not stored in any particular order. This structure is called a heap.



The two types of Ms SQL Server 2008 indexes are:



Clustered Index






Clustered indexes sort and store the data rows in the table based on their key values. Because the data rows are stored in sorted order on the clustered index key, clustered indexes are efficient for finding rows. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. The data rows themselves form the lowest level of the clustered index.





The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. If a table has no clustered index, its data rows are stored in a heap.



Nonclustered indexes




Nonclustered indexes have a structure completely separate from the data rows. The lowest rows of a nonclustered index contain the nonclustered index key values and each key value entry has pointers to the data rows containing the key value. The data rows are not stored in order based on the nonclustered key.






The pointer from an index row in a non clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or are clustered. For a heap, a row locator is a pointer to the row. For a table with a clustered index, the row locator is the clustered index key.










The only time the rows in a table are stored in any specific sequence is when a clustered index is created on the table. The rows are then stored in sequence on the clustered index key. If a table only has nonclustered indexes, its data rows are stored in a unordered heap.










Indexes can be unique, which means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value.



There are two ways to define indexes in MS SQL Server 2008 The CREATE INDEX statement creates and names an index. The CREATE TABLE statement supports the following constraints that create indexes:



PRIMARY KEY creates a unique index to enforce the primary key.



UNIQUE creates a unique index.



CLUSTERED creates a clustered index.



NONCLUSTERED creates a nonclustered index.When you create an index on Ms SQL Server 2008, you can specify whether the keys are stored in ascending or descending order.






Ms SQL Server 2008 supports indexes defined on computed columns, as long as the expression defined for the column meets certain restrictions, such as only referencing columns from the table containing the computed column, and being deterministic.










A fill factor is a property of a SQL Server index that controls how densely the index is packed when created. The default fill factor usually delivers good performance, but in some cases it may be beneficial to change the fill factor. If the table is going to have many updates and inserts, create an index with a low fill factor to leave more room for future keys. If the table is a read-only table that will not change, create the index with a high fill factor to reduce the physical size of the index, which lowers the number of disk reads SQL Server uses to navigate through the index. Fill factors are only applied when the index is created. As keys are inserted and deleted, the index will eventually stabilize at a certain density.



Indexes not only speed up the retrieval of rows for selects, they also usually increase the speed of updates and deletes. This is because SQL Server must first find a row before it can update or delete the row. The increased efficiency of using the index to locate the row usually offsets the extra overhead needed to update the indexes, unless the table has a lot of indexes.



This example shows the Transact-SQL syntax for creating indexes on a table.



USE pubs

GO

CREATE TABLE emp_sample

(emp_id int PRIMARY KEY CLUSTERED,

emp_name char(50),

emp_address char(50),

emp_title char(25) UNIQUE NONCLUSTERED )

GO




CREATE NONCLUSTERED INDEX sample_nonclust ON emp_sample(emp_name)




GODeciding which particular set of indexes will optimize performance depends on the mix of queries in the system. Consider the clustered index on emp_sample.emp_id. This works well if most queries referencing emp_sample have equality or range comparisons on emp_id in their WHERE clauses. If the WHERE clauses of most queries reference emp_name instead of emp_id, performance could be improved by instead making the index on emp_name the clustered index.












Many applications have a complex mix of queries that is difficult to estimate by interviewing users and programmers. SQL Server 2000 provides an Index Tuning Wizard to help design indexes in a database. The easiest way to design indexes for large schemas with complex access patterns is to use the Index Tuning Wizard.










You provide the Index Tuning Wizard with a set of SQL statements. This could be a script of statements you build to reflect a typical mix of statements in the system, but it is usually a SQL Profiler trace of the actual SQL statements processed on the system during a period of time that reflects the typical load on the system. The Index Tuning Wizard analyzes the workload and the database, and then recommends an index configuration that will improve the performance of the workload. You can choose to either replace the existing index configuration, or to keep the existing index configuration and implement new indexes to improve the performance of a slow-running subset of the queries.









how to delete duplicate records in sql server 2008







Delete From TableName




Where ID Not IN




{




Select MAX(ID) From TableName




Group By Col1, Col2...




}




By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.



What are the different types of triggers?

There are three types of triggers.
1) DML trigger
There are two kinds of DML triggers
a. Instead of Trigger
Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.
b. After Trigger
After triggers execute following the triggering action, such as an insert, update, or delete.
2) DDL trigger
This type of trigger is fired against DDL statements like Drop Table, Create Table, or Alter Table. DDL Triggers are always after Triggers.

3) Logon trigger
This type of trigger is fired against a LOGON event before a user session is established to the SQL Server.




What is a Trigger?

A trigger is a SQL procedure or SQLCLR Code that initiates an action when an event (like INSERT, DELETE or UPDATE) occurs on an object. Based on events which take place in your database, you can have SQL Server “listen” for just the ones that should signal when it’s time for actions to run automatically.

Triggers are stored in and managed by the DBMS. Triggers can be used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed directly; DBMS automatically fires the trigger as a result of a data modification to the associated table or in the case of DDL triggers to a DDL event in the database. Triggers are similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-driven and are not attached to a specific table as most triggers are. Stored procedures are explicitly executed by invoking a call to the procedure while triggers are implicitly executed by events. In addition, triggers can also execute stored procedures.

Nested trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so when the trigger is fired because of data modification, it can also cause data modification, thereby firing another trigger. A trigger is called a nested trigger when it is fired off from another trigger.



What is a stored procedure?




1) What is a stored procedure?

A stored procedure (SP) is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures are objects that do the work they are designed to do when you call upon them. You need to make sure they have what they need (the right values and parameters), so they can perform their important tasks. Stored procedures can act like views and select data, but they can also make updates, create objects, or even be set up to backup a database or perform other maintenance tasks.



Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. When the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database logic. Typical system Stored Procedures are - sp_helpdb, sp_renamedb, sp_depends etc.



2) How to Create a Stored Procedure (SQL Server Management Studio) 2008?

Step For Creat Store Procedure




In Object Explorer, connect to an instance of Database Engine and then expand that instance.


Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.


Right-click Stored Procedures, and then click New Stored Procedure.


On the Query menu, click Specify Values for Template Parameters.


In the Specify Values for Template Parameters dialog box, the Value column contains suggested values for the parameters. Accept the values or replace them with new values, and then click OK.


In the query editor, replace the SELECT statement with the statements for your procedure.


To test the syntax, on the Query menu, click Parse.


To create the stored procedure, on the Query menu, click Execute.


To save the script, on the File menu, click Save. Accept the file name or replace it with a new name, and then click Save.




3)Advantages of Stored Procedure i) Modular programming - We can write a stored procedure once, then call it from multiple places in our application.




ii) Faster execution: Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimze your SQL code every time it runs.

iii) Reduced network traffic: If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.




iv) Security - Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.





Creating a Stored Procedure

To create a store procedure use create procedure command that has the following Syntax-




CREATE PROCEDURE <ProcedureName>
[Parameter]
AS
BEGIN
<Statement>
END
GO





What are the different normalization forms?



Database normalization is a data design and organizational process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing database data into different tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then retrieved through the rest of the database via the defined relationships.

What is normalization?


1NF(Normalization): Eliminate repeating groups

Make a separate table for each set of related attributes and give each table a primary key. Each field contains at most one value from its attribute domain.



2NF(Normalization): Eliminate redundant data

If an attribute depends on only part of a multi-valued key, then remove it to a separate table.



3NF(Normalization): Eliminate columns not dependent on the key

If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key.



BCNF(Normalization): Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.


4NF(Normalization): Isolate independent multiple relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.


5NF(Normalization): Isolate semantically related multiple relationships

There may be practical constraints on information that justifies separating logically related many-to-many relationships.



ONF(Normalization): Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.



DKNF(Normalization): Domain-Key Normal Form

A model free from all modification anomalies are said to be in DKNF. Remember, these normalization guidelines are cumulative.

For a database to be in the 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.





What is de-normalization?




De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.




What is normalization?

Database normalization is a data design and organizational process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing database data into different tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then retrieved through the rest of the database via the defined relationships.

What are the different normalization forms?


1NF(Normalization): Eliminate repeating groups

Make a separate table for each set of related attributes and give each table a primary key. Each field contains at most one value from its attribute domain.



2NF(Normalization): Eliminate redundant data

If an attribute depends on only part of a multi-valued key, then remove it to a separate table.



3NF(Normalization): Eliminate columns not dependent on the key

If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key.



BCNF(Normalization): Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.


4NF(Normalization): Isolate independent multiple relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.


5NF(Normalization): Isolate semantically related multiple relationships

There may be practical constraints on information that justifies separating logically related many-to-many relationships.



ONF(Normalization): Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.



DKNF(Normalization): Domain-Key Normal Form

A model free from all modification anomalies are said to be in DKNF. Remember, these normalization guidelines are cumulative.

For a database to be in the 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.






What are the properties of the relational tables?

Relational tables have the following five properties:




Values are atomic.


Column values are of the same kind.


The sequence of columns is insignificant.


The sequence of rows is insignificant.


Each column must have a unique name.



What is RDBMS?




Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.





RDBMS stands for Relational Database Management System. RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields.



RDBMS store the data into collection of tables, which might be related by common fields (database table columns). RDBMS also provide relational operators to manipulate the data stored into the database tables. Most RDBMS use SQL as database query language.



Edgar Codd introduced the relational database model. Many modern DBMS do not conform to the Codd’s definition of a RDBMS, but nonetheless they are still considered to be RDBMS.



The most popular RDBMS are MS SQL Server, DB2, Oracle and MySQL