- 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.
It is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
- 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
- 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.
- 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.
- Source control can be a pain.
- Debugging is hard.
- Developing stored procedures can be a fairly specialized task, especially as they get more complex.
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
- 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.
- 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
- 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.