SQL Server Functions Interview Preparation Guide
Download PDF

SQL Server Functions frequently Asked Questions by expert members with experience in MS SQL Server Functions. These interview questions and answers on SQL Server Functions will help you strengthen your technical skills, prepare for the interviews and quickly revise the concepts. So get preparation for the SQL Server Functions job interview

12 MS SQL Server Functions Questions and Answers:

1 :: MS SQL Server Functions Job Interview Questions!

Explain Microsoft SQL Server functions?
What are the benefits of User-Defined Functions?
Explain the difference between Functions and Stored procedures in SQL Server?

2 :: Explain the benefits of User-Defined Functions?

a. Can be used in a number of places without restrictions as compared to stored procedures.

b. Code can be made less complex and easier to write.

c. Parameters can be passed to the function.

d. They can be used to create joins and also be sued in a select, where or case statement.

e. Simpler to invoke.

3 :: What is user-defined function? Explain its types i.e. scalar and Inline table value user-defined function?

User defined functions are created and defined by the user. They are created as per users needs. They may or may not accept parameters. They can be used to create joins and simple to invoke as compared to stored procedures

Scalar user defined: returns values as one of the scalar data types. Text, timestamp, image data types are not supported. It may or may not accept parameters.

Inline table value user defined : it returns a table data type. These functions can pass parameters to the sql’s SELECT command. They are similar to views except, they can accept parameters.

4 :: What is built-in function?

A built in function of sql is used for performing calculations. These are standard functions provided by sql.

5 :: What are aggregate functions?

Aggregate functions: these functions perform calculation on a column and return a single value. Example: AVG(), SUM(), MIN(), MAX()

6 :: What are scalar functions?

Scalar functions : these functions perform calculation on an input value and return a single value. Example: ROUND(), MID(), LCASE(), UCASE()

7 :: Explain database Normalization?

It is the process of organizing data into related table.
To normalize database, we divide database into tables and establish relationships between the tables.
It reduces redundancy. It is done to improve performance of query.

8 :: Explain Steps of normalization?

First Normal form
Entities of the table must have unique identifier or entity key.
Second Normal Form
All the attributes of the table must depend on the entity key for that entity.
Third Normal Form
All attributes that are not part of the key must not depend on any other non-key attributes.

9 :: What is De-normalization?

The process of adding redundant data to get rid of complex join, in order to optimize database performance. This is done to speed up database access by moving from higher to lower form of normalization.

10 :: What do you know about Normalization and De- Normalization?

Normalization is the process of reducing data redundancy and maintains data integrity. This is performed by creating relationships among tables through primary and foreign keys. Normalization procedure includes 1NF, 2NF, 3NF, BCNF, and then the data is normalized.

Denomalization on the contrary is the process of adding redundant data to speed up complex queries involving multiple table JOINS. One might just go to a lower form of Normalization to achieve Denormalization and better performance. Data is included in one table from another in order to eliminate the second table which reduces the number of JOINS in a query and thus achieves performance.

11 :: Do you know exporting and importing utility?

BCP Utility

The Bulk Copy is a command utility that transfer SQL data to or from a data file.
This utility mostly used to transfer huge data to SQL server from other database.
With this, data is first exported from source file to a data file and then imported from the data file to SQL server table.
It is also used to transfer data from SQL server table to a data file.
You can use ‘Bulk Insert’ statement to transfer data from data file to SQL server table.
DTS Packages

It is a set of tools that allows you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You can create custom data movement solution using DTS object model.

DTS packages can provide following services:

It can import or export data to or from text file or OLE DB data source.
It supports data transformation using DTS transformation which means that data can be operated using one or more functions before hitting the destination.
You can transfer database objects along with data using DTS package.
DTS package also notifies if package steps succeed or failed by sending mails to source and destination.

12 :: Explain how DTS is used to extract, transform and consolidate data?

Data Transformation Services is a set of tools available in SQL server that helps to extract, transform and consolidate data. This data can be from different sources into a single or multiple destinations depending on DTS connectivity. To perform such operations DTS offers a set of tools. Depending on the business needs, a DTS package is created. This package contains a list of tasks that define the work to be performed on, transformations to be done on the data objects.

Import or Export data: DTS can import data from a text file or an OLE DB data source into a SQL server or vice versa.

Transform data: DTS designer interface also allows to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. For parameterized queries and mapping purposes, Data driven query task can be used from the DTS designer.

Consolidate data: the DTS designer can also be used to transfer indexes, views, logins, triggers and user defined data. Scripts can also be generated for the sane For performing these tasks, a valid connection(s) to its source and destination data and to any additional data sources, such as lookup tables must be established.