1) What are different normalization forms?
1NF: Eliminate Repeating GroupsMake 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: Eliminate Redundant DataIf an attribute depends on only part of a multi-valued key, remove it to a separate table.3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary keyBCNF: Boyce-Codd Normal FormIf there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.4NF: Isolate Independent Multiple RelationshipsNo table may contain two or more 1:n or n:m relationships that are not directly related.5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships.ONF: Optimal Normal FormA model limited to only simple (elemental) facts, as expressed in Object Role Model notation.DKNF: Domain-Key Normal FormA model free from all modification anomalies.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
2) What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And 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.e.g. sp_helpdb, sp_renamedb, sp_depends etc.
3)What is Trigger?A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. 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 another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
3)What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the 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 data in the view, as views are the way to look at part 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 standard T-SQL select command and can come from one to many different base tables or even other views.
4)What is Index?
An 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 cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.
5) You are creating a procedure that will update two tables within a transaction. The code looks similar to the following (line numbers are included for reference only):1 BEGIN TRANSACTION23 BEGIN TRY4 UPDATE . . .56 END TRY78 BEGIN CATCH9 IF . . .1011 END CATCH12
Answer :- After Line 10
For eg:- begin catch
if @@TRANCOUNT>0
rollback
end catch
6) You are building a scientific application that will store data in a SQL Server 2005 database. The application does not store data until a final process is complete. One of the procedures you are currently working with performs many processor-intensive calculations. Which of the following would accommodate the situation?
Answers:-
a) A standard T-SQL stored procedure
b) A T-SQL stored procedure that uses a cursor
c) A CLR-integrated stored procedure (Correct)
d) A CLR-integrated stored procedure that uses a cursor
7) You need to add a column to a table that will hold a unique value. Which of the following would accommodate the situation?
Answers:-
a) An identity column with an integer data type (Correct)
b) A timestamp data type (Correct)
c) A uniqueidentifier data type with a default value of NewID() (Correct)
d) A column set as the primary key (Correct)
8) You would like to add an additional column to a table. The new column will be used to hold a seven-character serial number for assets and will be a mandatory element of data in the future. How would you implement the process?
Answers:-
a) Create a new NOT NULL column. (Correct)
b) Set a default value of N/A. (Correct)
c) Create the new column as an identity column.
d) Enter serial numbers for all existing records.
9) For producing a report from a random selection of records within a SQL Server database. There are 20,000 records in the table, and you would like to include 4,000 of them in the process. How would you implement the process?
Answers:-
a) Use CURSOR and process every 10th record.
b) Use the TOP clause to get 4,000 records.
c) Set the ROWCOUNT to 4000.
d)Use TABLESAMPLE to get 20% of the records. (Correct)
Answer Description: Processing a random sample of records is exactly what TABLESAMPLE accomplishes. The answer for previous SQL Server versions may have been B, as using the TOP clause to get 4,000 records would sort of accomplish the goal. Answers A, B, and C would not give you truly random selection, although they would be able to limit the output to 4,000 records. RND is a random number generator and would not accomplish random selection of records
10) While producing a grouped report of sales, by city, you only want to include report groups that have sales within the year 2007. Which of the following query elements would you use to solve the problem?
Answers:-
a) GROUP BY (Correct)
b) WHERE (Correct)
c) HAVING
d) BETWEEN (Correct)
11) During the major system upgrade, multiple data changes are going to be made. You would like to implement various changes without disturbing any of the existing data. Which of the following operations do not affect any existing data values?
Answers: -
a) Insert (Correct)
b) Update
c) Changing column name (Correct)
d)Increasing column length (Correct)
12) In your development environment number of individuals creates databases regularly. You would like to log the database creation activity so that the username, time, date, and details of the creation are recorded. How would you achieve this?
Answers: -
a) Use a DDL trigger (Correct)
b) Use a DML Trigger
c) Use a constraint
d ) Use a UDF.
13) Your accounting application using SQL Server database that is accessed by 100 users on your organization. When a user inserts or updates a record, you want to make sure that all the required columns have appropriate values. Which of the following would be best for this situation?
Answers: -
a) A stored procedure and a trigger
b) A batch and a trigger
c) An UPDATE Trigger and an INSERT trigger
d) One trigger by itself (Correct)
14) Your database contains several FOREIGN KEY and CHECK constraints. Users are facing problems while data entry on the database because the data they are adding is constantly in violation of the CHECK constraints. You can not change the database design, so you decide to implement your changes via a trigger. Which types of triggers would be best suited for this task?
Answers: -
a) UPDATE, DELETE, and INSERT Triggers
b) Just UPDATE and INSERT triggers
c) INSTEAD OF Triggers (Correct)
d) Triggers cannot be used in this circumstance.
Answer Description: INSTEAD OF triggers are required for this task because you must check for constraint violations before the update occurs. You need to implement INSTEAD OF INSERT or INSTEAD OF INSERT and UPDATE triggers. When trigger actions are listed, such as an INSERT Trigger, you cannot know for sure whether it is an INSTEAD OF or AFTER trigger, but you should assume that it is a FOR or AFTER Trigger if not specifically mentioned. For more information about the order in which triggers and constraints are applied see the information on sp_settriggerorder in SQL Server Books Online.
15) You are creating a new index on a table that has 1,700 rows. 20 rows are added to this table almost every day. The table already has a primary key, and the new index does not represent the order in which data in the table is to be stored. Updates to the table occur periodically but are infrequent. Which type of index would you create?
Answers: -
a) Use a clustered index with a high FILLFACTOR setting
b) Use a clustered index with a low FILLFACTOR setting
c) Use a non-clustered index with a high FILLFACTOR setting
d) Use a non-clustered index with a low FILLFACTOR setting (Correct)
Answer Description: The primary key is usually creates the clustered index on the table. The clustered index indicates the physical order of the data. A low fill factor leaves more room for updates.
16) How to create Full text Indexing on a database?
Answer: -
As you can probably guess, there's a certain amount of overhead involved in passing data back and forth between SQL Server and the Search Service. To speed things up, SQL Server doesn't pass any data to the Search Service unless you explicitly tell it to do so. After all, you might never want to do any full-text searches, in which case it would be silly to spend time indexing your data for them.
To get started, you need to add a full-text catalog to your database. The easiest way to do this is to open SQL Server Enterprise Manager and expand the node for your database to find the Full-Text Catalogs node (if that node isn't present, check to make sure that the Microsoft Search Service is installed on the server). Right-click on the node and select New Full-Text Catalog. SQL Server will prompt you for a name and location for the catalog (and it will supply a default location). Name the catalog anything you like and click OK to create it.
Next you need to tell SQL Server what data to include in the catalog. Again, you can do this in Enterprise Manager. Right-click on a table and select Full-Text Index Table, Define Full-Text Indexing on a Table. This will launch the SQL Server Full-Text Indexing Wizard. You need to make these choices to complete the wizard:
Select a unique index on the table
Select the columns to index. You can optionally specify a language to use for word breaking.
Select the catalog to contain the index, or create a new catalog.
Create a schedule to repopulate the index on a regular basis (this is also optional).
When you finish the wizard, it will create the index for the table. But the index won't have any entries in it yet. Right-click on the table again anfd select Full-Text Index Table, Start Full Population to build the actual index
17) How to call nested stored procedure and save the output in Temporary Table?
Answer:-
Suppose you have stored procedure named sp_FirstProcedure and sp_SecondProcedure
Code for sp_ FirstProcedure
Create Procedure sp_FirstASBEGIN Create Table #tempTable ( City varchar(255), EmployeeName varchar(255) ) Insert into #tempTable Values (’Alabama’,'Rana’)
Insert into #tempTable Values (’Alabama’,'John’)
Insert into #tempTable Values (’Alabama’,'Richard’)
Insert into #tempTable Values (’Alabama’,'Kash’)
Select * from #TempTable
Drop Table #TempTableENDGO
Output is :
Here is the second stored procedure where I will call first procedureBy using these steps.
Create Procedure sp_SecondProcedureAS
BEGINCreate table #tempCityEmp( City varchar(255), EmployeeName varchar(255)
)Insert into #tempCityEmpExec sp_First
– here you can Manuplate your temporary table like I want to —– take only the record with Rana’s name
Select * from #tempCityEmp where EmployeeName like ‘Rana’
ENDGo
Output would be
This is how you call nested stored procedure and insert the out put in a temporary table.
18) You create a Web application to process XML documents. The Web application receives XML document files from several sources, reads them, and stores them in a Microsoft SQL Server database. The Web application parses all incoming data files to ensure that they conform to an XML schema. You need to find all validation errors in the XML document. What should you do?
Answers: -
a) Load the XML data by using an instance of the XmlDocument class and specify a location for the application schema.
b) Configure the ValidationEventHandler in the XmlReaderSettings of the XmlReader object. (Correct)
c) Read the XML file into a DataSet object and set the EnforceConstraints property to True.
d) Read the XML file into a DataSet object. Handle the DataSet.MergeFailed event to parse the data that does not conform to the XML schema.
19) You need to set up security on Microsoft SQL Server 2005 Enterprise Edition to allow access to the employees of the company. The Microsoft SQL Server 2005 will contain several databases, some of which contain sensitive information. How do you configure access to the server?
Answers: -
a) Allow access to the Domain Users group and assign users to appropriate applications roles.
b) Allow access to everyone group and assign appropriate permissions to associated SQL logins.
c) Allow access to Active Directory groups of company and assign permissions according to those groups. (Correct)
d) Disable guest access and assign minimal permissions to the Public role in the sensitive databases. (Correct)
Answer Description: Never deny permissions to the Public role because, in essence, you would be denying the ability for any user. Instead, assign a minimal, base level of permission to this role. Leverage Active Directory groups to ease administration, but you need to be cautious in using the Domain Users group, which in this example would be invalid. You should never use the Everyone group.
20) On an existing Microsoft SQL Server 2005 instance, you have to prevent non-management individuals from accessing the Microsoft SQL Server. How do you configure access to the server?
Answers: -
a) Disable guest access; grant access to a network group containing the management individuals; deny access to the Public group in each database.
b) Disable guest access and grant access to a network group containing the management individuals; ensure that no other access has been granted to other groups. (Correct)
c) Disable guest access and grant access to the Domain Users group; deny database access to anyone not in management.
d) Disable guest access and grant access to a network group containing the management individuals; grant access to all other network groups and assign them to the db_denydatareader role in all databases
Answer Description:
Never deny permissions to the Public role because, in essence, you would be denying the ability for any user. Instead, assign a minimal, base level of permission to this role. Leverage existing Active Directory groups to ease administration, as long as they contain the appropriate individuals.
21) While installing Microsoft SQL Server 2005, how you will check configuration is suitable for the installation. Which tool would you use?
Answers: -
a) System Configuration Checker (Correct)
b) SQL Server Analysis Services
c) SQL Server Migration Assistant
d) SQL Server Profiler
Answer Description: The System Configuration Checker (SCC) can notify issues that will prevent a successful installation. The Migration Assistant, on the other hand, is used to migrate from other systems such as Access or Oracle. SQL Server Analysis Services provides online analytical processing (OLAP) and data mining functionality. SQL Server Profiler is used for monitoring an existing instance of the database engine or Analysis Services.
22) SQL Server Full-Text Search Performance Tuning and Optimization
Answer: -
The SQL Server Full-Text Search service can affect the overall performance of SQL Server when it is indexing text in your tables. Indexing large amounts of text uses up server resources, reducing the CPU time and memory that could be used by your users running their SQL Server-based applications. If you find that the Full-Text Search service is negatively affecting your server's performance, and impacting your users, you can change a setting, telling the service to back off, and not use so much of your server's resources.
To change this setting, right-click on the Full-Text Search service in Enterprise Manager and click on "Properties". This displays the "Full-Text Search Service Properties" dialog box. Here, click on the "Performance" tab. On this tab is a "System Resource Usage" option that has a slider bar you can use to change the effect this service has on SQL Server.
The slider bar has five position, and the default setting is at the third setting. If you want to reduce the impact of this service on your SQL Server, move the slider bar to the left one or two steps. This will proportionally reduce the affect of the service on SQL Server. It will also cause the full-text service to run longer to perform the same task, but now with less impact on the server. Conversely, if you want to dedicate this SQL Server to this service, you can boost the resource usage by sliding the slider bar to the right. But don't do this unless your SQL Server is dedicated to the Full-Text Search Service.
The SQL Server Full-Text Search service can affect the overall performance of SQL Server when it is indexing text in your tables. Indexing large amounts of text uses up server resources, reducing the CPU time and memory that could be used by your users running their SQL Server-based applications. If you find that the Full-Text Search service is negatively affecting your server's performance, and impacting your users, you can change a setting, telling the service to back off, and not use so much of your server's resources.
To change this setting, right-click on the Full-Text Search service in Enterprise Manager and click on "Properties". This displays the "Full-Text Search Service Properties" dialog box. Here, click on the "Performance" tab. On this tab is a "System Resource Usage" option that has a slider bar you can use to change the effect this service has on SQL Server.
The slider bar has five position, and the default setting is at the third setting. If you want to reduce the impact of this service on your SQL Server, move the slider bar to the left one or two steps. This will proportionally reduce the affect of the service on SQL Server. It will also cause the full-text service to run longer to perform the same task, but now with less impact on the server. Conversely, if you want to dedicate this SQL Server to this service, you can boost the resource usage by sliding the slider bar to the right. But don't do this unless your SQL Server is dedicated to the Full-Text Search Service.
23) Write a regular expression for email id?
Answer: - \w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*
