MS-SQL interview question for fresher/MS-SQL Interview Questions and Answers for Freshers & Experienced

What are associative entities?

An associative entity is a conceptual concept. An associative entity can be thought of as both an entity and a relationship since it encapsulates properties from both. It is a relationship since it is serving to join two or more entities together, but it is also an entity since it may have its own properties.

What is "scheduled jobs" or "scheduled tasks"?

Scheduled tasks let you manage the tasks in an automated manner that runs on regular or predictable cycles. You can schedule administrative tasks and also determine the order in which tasks will run.

What is business intelligence?

Refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by-products and/or departments, or by associated costs and incomes.

What is Atomicity?

This means the transaction finish completely, or it will not occur at all.

What is a prime attribute?

A prime attribute is an attribute that is part of a candidate key.

What is an ER Diagram?

An ER diagram or Entity-Relationship diagram is a special picture used to represent the requirements and assumptions in a system from a top-down perspective. It shows the relations between entities (tables) in a database.

What are the steps you will take to improve the performance of a poor-performing query?

This is a very open-ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be:

1. No indexes
2. No Table scans
3. Missing or out of date statistics
4. Blocking
5. Excess recompilations of stored procedures

If you have no choice but to use a SQL Server-based cursor, what tips do you have to optimize them?

Perhaps the best performance gain is when you can create a cursor asynchronously rather than needing the whole population operation to be completed before further processing can continue. Then, by checking specific global variables settings, you can tell when there is no further processing to take place. However, even here, care has to be taken. The asynchronous population should only occur on large record sets rather than those that only deal with a small number of rows.

Use the smallest set of data possible. Break out of the cursor loop as soon as you can. If you find that a problem has occurred, or processing has ended before the full cursor has been processed, then exit. If you are using the same cursor more than once in a batch of work, and this could mean within more than one stored procedure, then define the cursor as a global cursor by using the GLOBAL keyword, and not closing or deallocating the cursor until the whole process is finished. A fair amount of time will be saved, as the cursor and the data contained will already be defined, ready for you to use.

What is a database table?

Database table: The table contains records in the form of rows and columns. A permanent table is created in the database you specify and remains in the database permanently until you delete it.

Syntax:
1. Create table TableName (ID INT, NAME VARCHAR(30) )
2. Drop syntax: drop table TableName
3. Select Syntax: Select * from TableName
Click on the following link to read more on temporary tables in SQL.

What are the authentication modes in SQL Server? How can it be changed?

Authentication mode is used for authentication of the user in SQL server, and it can be selected at the time of setup of the database engine.

SQL Server supports two authentication modes: Window authentication mode and mixed mode.

Window authentication mode: This authentication mode is used to connect through a Microsoft NT 4.0 or window 2000 user account. In Windows authentication server take computer's username and password for authentication purpose. SQL server authentication mode is disabled in this mode.

Mixed mode: It is used to connect with the instance of SQL Server using window authentication or SQL Server authentication. In SQL server authentication mode a unique username and password are required for a particular database, as it will not depend on windows account.

What do you consider are the best reasons to use stored procedures in your application instead of passing Transact-SQL code directly to SQL Server?

First and foremost, a stored procedure is a compiled set of code, where passing T-SQL through languages such as VB, Visual FoxPro, etc., means that the set of code needs to be compiled first. Although T-SQL within VB, etc., can be prepared before running, this is still slower than using a stored procedure. Then, of course, there is the security aspect, where, by building a stored procedure, you can place a great deal of security around it.

When dealing with sensitive data, you can use an encrypted stored procedure to hide sensitive columns, calculations, and so on. Finally, by using a stored procedure, I feel that transactional processing becomes a great deal easier and, in fact, using nested transactions becomes more insular and secure. Having to deal with transactions within code that may have front-end code, will slow up a transaction, and therefore a lock will be held for longer than necessary.

What is the use of the SCOPE_IDENTITY() function?

It returns the most recently created identity value for the tables in the current execution scope.

What is the purpose of UPDATE STATISTICS?

it updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

What is Referential Integrity?

Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

Define the one-to-one relationship while designing tables.

One-to-One relationship: It can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One to one relationship exists if an entity in one table has a link with only one entity on another table. Let?s take an example of the employee and their employee id so that a unique employee id will be there for a particular employee at another table.

What is a virtual table in SQL?

In SQL there is an object which is known as a virtual table, it is not stored directly in the file of the database. The virtual table allows SQLite for manipulating and accessing the resources by using a powerful language of SQL query. In other terms, the view in SQL is known as a virtual table. This view represents the columns and rows like any real table.

To create a view in SQL you can use the following syntax:

CREATE VIEW view_name AS
SELECT column1, column2, . . .
FROM table_name
WHERE condition;

So, a virtual table doesn't exist physically in the database. It helps in defining things that can be used as the SQL statements just similar to a real table.

Explain cross join or cartesian product in sql?

In SQL a cross join is also known as a cartesian product which is used to produce a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are special kinds of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE, and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder.

Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

What is the system function to get the current user’s user id?

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using the sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy.

What is difference between foreign key and unique key?

A foreign key is basically a primary key of another table used in the given table. It is used to create connections between two different tables. Whereas a unique key uniquely determines a row that isn’t the primary key and Creates a non-clustered index.

How Global temporary tables are represented and its scope?

Global temporary tables are represented with ## before the table name. Scope will be the outside the session whereas local temporary tables are inside the session. Session ID can be found using @@SPID.

What is a Join? Explain Different Types of Joins

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS.

Write down the general syntax for a SELECT statement covering all the options.

Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax).

SELECT select_list
[HDEV:INTO new_table_]
FROM table_source
[HDEV:WHERE search_condition]
[HDEV:GROUP BY group_by_expression]
[HDEV:HAVING search_condition]
[ORDER BY order_expression [ASC | HDEV:DESC] ]

What is RDBMS?

RDBMS: It is referred to as Relation Database Management Systems (RDBMS).

RDBMS possesses the below characteristics:

<> Write-intensive operations: The RDBMS is frequently written to and is often used in transaction-oriented applications.
<> Data in flux or historical data: The RDBMS is designed to handle frequently changing data. <> Alternatively, RDBMS can also store vast amounts of historical data, which can later be analyzed or "mined".
<> Application-specific schema: The RDBMS is configured on a per-application basis and a unique schema exists to support each application.
<> Complex data models. The relational nature of the RDBMS makes it suitable for handling sophisticated, complex data models that require many tables, foreign key values, complex join operations, and so on.
<> Data integrity: The RDBMS features many components designed to ensure data integrity. This includes rollback operations, referential integrity, and transaction-oriented operations.

What are the main steps in Data Modeling?

1.Logical – Planning, Analysis, and Design

2.Physical – Design, Implementation, and Maintenance

What can a developer do during the logical and physical design of a database to help ensure that their database and SQL Server-based application will perform well?

A developer must investigate volumes of data (capacity planning), what types of information will be stored, and how that data will be accessed. If you are dealing with an upgrade to an existing system, analyzing the present data and where existing data volumes occur, how that data is accessed and where the current response bottlenecks are occurring, can help you search for problem areas in the design.

A new system would require a thorough investigation of what data will be captured, and looking at volumes of data held in other formats also will aid design. Knowing your data is just as important as knowing the constituents of your data. Also, constantly revisit your design. As your system is built, check relationships, volumes of data, and indexes to ensure that the physical design is still at its optimum. Always be ready to check your system by using tools like the SQL Server Profiler.

What part does database design have to play in the performance of a SQL Server-based application?

It plays a very major part. When building a new system, or adding to an existing system, the design must be correct. Ensuring that the correct data is captured and is placed in the appropriate tables, that the right relationships exist between the tables, and that data redundancy is eliminated is an ultimate goal when considering performance. Planning a design should be an iterative process, and constantly reviewed as an application is developed.

It is rare, although it should be the point that everyone tries to achieve, when the initial design and system goals are not altered, no matter how slightly. Therefore, a designer has to be on top of this and ensure that the design of the database remains efficient.

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the properties of a transaction.

What is PL/SQL?

PL/SQL (Procedural Language for SQL) is a procedural language developed by Oracle to work with the Oracle database using procedures in SQL. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database. PL/SQL syntaxes include declarations for variables, constants, procedures, functions, conditions, and loops.

Control statements in PL/SQL:
* Control statements are very important in PL/SQL.
* Control Statements are elements in a program that controls the flow of program execution.
* The syntax of control statements is similar to regular English and is very similar to the choices that we make every day.
* Branching statements are as follows:
* If statement
* If - THEN - ELSE
* Nested IF
* Branching with logical connectivity
* While
* For Loop

What is the purpose of FLOOR function?

FLOOR function is used to round up a non-integer value to the previous least integer. Example is given

FLOOR(6.7)
Returns 6.

How exceptions can be handled in SQL Server Programming?

Exceptions are handled using TRY—-CATCH constructs and it is handles by writing scripts inside the TRY block and error handling in the CATCH block.

What are user-defined data types and when you should go for them?

Lets you extend the base SQL Server data types by providing a descriptive name and format to the database

E.g. Flight_num appears in many tables and all these tables have varchar(8)
Create a user-defined data-type

What are defaults? Is there a column to which a default can’t be bound?

It is a value that will be used by a column if no value is supplied to that column while inserting data.
I can’t be assigned for identity and timestamp values.

State the difference between UNION and UNION ALL?

UNION – It is used to select the related information.
It is similar to that of JOIN command.
UNION ALL – It is similar to that of UNION command, but it selects all the values.
It does not remove the values from the table but will retrieve the data.

Explain Magic tables in SQL Server?

When the triggers are fired for any DML command, insert and delete tables are created, these tables are called Magic tables in SQL server.These tables are used inside the triggers for transactions.

How exceptions can be handled in SQL Server Programming?

Exceptions are handled using TRY—-CATCH constructs and it is handles by writing scripts inside the TRY block and error handling in the CATCH block.

What are scheduled tasks in SQL Server?

Scheduled tasks or jobs are used to automate processes that can be run on a scheduled time at a regular interval. This scheduling of tasks helps to reduce human intervention during night time and feed can be done at a particular time. User can also order the tasks in which it has to be generated.

What is SQL server agent?

The SQL Server agent plays a vital role in day to day tasks of SQL server administrator(DBA). Server agent’s purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.

What methods do you follow to protect from SQL injection attack?

Following methods are used to protect from SQL injection attack:
<> Filtering input parameters
<> Use parameter collection with Dynamic SQL
<> Use Parameters for Stored procedures
<> In like clause, use escape characters

What is CHECK constraint?

A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.

What is CHECK constraint?

A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.

What is denormalization and when would you go for it?

It is the reverse process of normalization. It increases query performance by reducing the joins. It is used for OLAP applications.

Explain Trigger and its types?

Trigger – It is used to execute a batch of SQL code when the commands like INSERT, UPDATE or DELETE are executed against a table.
They are automatically executed or triggered when the data gets modified.
Types of Triggers:
* Insert
* Update
* Delete
* Instead of

What is normalization? Explain different levels of normalization?

It is the way to eliminate redundant data

1. Reduces null value
2. Enables efficient indexing
3. 1NF – Removes duplicated attributes, Attribute data should be atomic, and attribute should be the same kind.
4. 2NF – This should be in 1NF and each non-key is fully dependent on the primary key.
5. 3NF – This should be in 2NF and all the non-key attributes which are not dependent on the primary key should be removed. All the attributes which are dependent on the other non-key attributes should also be removed. Normalization is done in.

Explain Subquery and state its properties?

Subquery – It is a query which is used when expressions are allowed and can be nested inside the main query like SELECT, UPDATE, DELETE or INSERT statements.
Properties:
<> A subquery in the main query that has to be placed on the right-hand side of the comparison operator
<> A subquery has to be placed parenthesis, in order to get executed first before the main query.
<> It cannot have any order by clause.
<> More than one subquery can be included in the main query.

What is COALESCE and CHECK constraint in SQL server?

COALESCE – It is a function that is used to return the first non-null expression from more than one column within the arguments.

CHECK constraint – It is used to enforce the integrity. It is applied to a column in a table to limit the values that have to be placed in a column.

What is SQL Profiler?

It is a tool that allows system’s administrator to monitor the events in SQL Server.It is mainly used to capture and save the data of each event of a file or a table for analysis.

What is SQL server agent and what are the two modes of authentication in SQL Server?

SQL Server agent
The SQL Server agent plays an important part in the day to day tasks of the SQL Server Database Administrator (DBA).
Its purpose is to implement the tasks easily with the Scheduler engine that allows our tasks to run at a scheduled time and date.
Modes of Authentication in SQL Server
The two authentication modes in SQL Server are:

* Windows Mode
* Mixed Mode

From the tools menu of SQL Server configuration properties in the security page, the modes can be changed.

What is MSSQL?

MSSQL stands for Microsoft Server SQL that is a Microsoft’s relational database management system. It is a featured database which is designed to compete against Oracle Database and MySQL. MSSQL is also referred as SQL Server.

Search
R4R Team
R4R provides MS-SQL Freshers questions and answers (MS-SQL Interview Questions and Answers) .The questions on R4R.in website is done by expert team! Mock Tests and Practice Papers for prepare yourself.. Mock Tests, Practice Papers,MS-SQL interview question for fresher,MS-SQL Freshers & Experienced Interview Questions and Answers,MS-SQL Objetive choice questions and answers,MS-SQL Multiple choice questions and answers,MS-SQL objective, MS-SQL questions , MS-SQL answers,MS-SQL MCQs questions and answers Java, C ,C++, ASP, ASP.net C# ,Struts ,Questions & Answer, Struts2, Ajax, Hibernate, Swing ,JSP , Servlet, J2EE ,Core Java ,Stping, VC++, HTML, DHTML, JAVASCRIPT, VB ,CSS, interview ,questions, and answers, for,experienced, and fresher R4r provides Python,General knowledge(GK),Computer,PHP,SQL,Java,JSP,Android,CSS,Hibernate,Servlets,Spring etc Interview tips for Freshers and Experienced for MS-SQL fresher interview questions ,MS-SQL Experienced interview questions,MS-SQL fresher interview questions and answers ,MS-SQL Experienced interview questions and answers,tricky MS-SQL queries for interview pdf,complex MS-SQL for practice with answers,MS-SQL for practice with answers You can search job and get offer latters by studing r4r.in .learn in easy ways .