Question 241 – What is Normalization in SQL Server?

• In relational database design, the process of organizing data to minimize redundancy is called normalization.
• It usually involves dividing a database into 2 or more tables and defining relationships between tables.
• Objective is to isolate data so that additions, deletions, and modifications can be made in just one table.

Question 242 – What is a De-Normalization in SQL Server?

It is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Question 243 – What are the different Normalization Forms in SQL Server?

• 1NF: Eliminate Repeating Groups
• 2NF: Eliminate Redundant Data
• 3NF: Eliminate Columns Not Dependent On Key
• 4NF: Isolate Independent Multiple Relationships
• 5NF: Isolate Semantically Related Multiple Relationships

Question 244 – What is a Stored Procedure in SQL Server?

• It is a named group of SQL statements created and stored in the database.
• Accept input parameters so that single procedure can be used by several clients using different inputs.
• When the procedure is modified, all clients automatically get the new version.
• It reduces network traffic and improve performance.
• It can be used to help ensure the integrity of the database.

Question 245 – What are the Advantages of Stored Procedure in SQL Server?

• Execution plan retention and reuse
• Encapsulation of business rules and policies
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Improved security
• Reduced development cost and increased reliability
• Centralized security, administration, and maintenance for common routines.

Question 246 – What are the DisAdvantages of Stored Procedure in SQL Server?

• Source control can be a pain.
• Debugging is hard.
• Developing stored procedures can be a fairly specialized task, especially as they get more complex.

Question 247 – What is a User Defined Functions in SQL Server?

A user-defined function (UDF) is a prepared code segment that can accept parameters, process some logic, and then return some data. UDFs in SQL Server 2000 can accept anywhere from 0 to 1024 parameters

Question 248 – What is a Scalar value-returning User Defined Function in SQL Server?

• Similar to functions in other languages.
• Returns a single value of a scalar data type.
• Can return user-defined data types.
• Useful when the same segment of T-SQL code is used in several places
• Can be used in several stored procedures and batch SQL statements.
• Used to make the code more maintainable, reusable, and less complex.

Question 249 – What is an In Line Table User Defined Function in SQL Server?

• Returns a row set of the SQL Server table data type.
• Takes the structure of the row set from the SELECT statement itself.
• It is an exceptional alternative to a view
• Can pass parameters and in essence provide us with a parameterized, non-updateable view

Question 250 – What is a Multistatement Table User Defined Function in SQL Server?

• Returns a table and is also an exceptional alternative to a view
• Defines column names and data types in the RETURNS clause.
• Able to house more complicated and numerous T-SQL logic blocks.
• It can be used it in the FROM clause of a T-SQL command

Disclaimer – F5debug Interview Questions & Answers Series:

You may recopy extracts from these pages (“the material”) to individual third party websites or to any intranet websites, but only if:

You acknowledge www.f5debug.net as the source of the material. Such acknowledgment should include reference to www.f5debug.net in the copy of the material and should also include “© Karthikeyan Anbarasan, www.f5debug.net “. You inform the third party that these conditions apply to him/her and that he/she must comply with them.