May

Mssql Interview Questions
- Monika Jacob
- 05th May, 2021
- 963 Followers
Mssql Interview Questions
There are a dozen of editions of Microsoft SQL server in the market, which targets different audiences and workloads of all interfaces ranging from the small single interface applications to large interface application with lots of concurrent users.
With lots of scope in the industry, the candidates aiming to cruise with the knowledge of MSSQL can get an extra pair of hands with these interview questions to crack the interview.
MSSQL Interview Questions and answers
Mssql Interview Questions
1) What is MSSQL?
2) What is SQL server agent and what are the two modes of authentication in SQL Server?
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.
3) What is SQL Profiler?
4) What is COALESCE and CHECK constraint in SQL server?
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.
5) Explain Subquery and state its properties?
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.
6) What are types of subqueries?
- Single Row – It returns only one row.
- Multiple Row – It returns multiple rows.
- Multiple Column – It returns multiple columns to the main query.
7) Explain Trigger and its types?
They are automatically executed or triggered when the data gets modified.
Types of Triggers:
- Insert
- Update
- Delete
- Instead of
8) What do you mean by Collation recursive stored procedure?
- Binary
- Case sensitive
- Case insensitive.
Recursive stored procedure – It is defined as a process of resolving a problem where the solution occurs repetitively.This can be nested up to 32 levels.
9) Explain Magic tables in SQL Server?
10) State the difference between Local and Global temporary tables?
Global temporary table – These tables are visible to all users and are deleted when the connection is closed.
11) Explain CDC and SQL injection?
SQL injection – SQL injection is an attack by malicious users in which the malicious code is inserted into the strings that are passed to an instance of SQL server for parsing and execution.
All the statements have to be checked for the vulnerabilities as they execute all syntactically valid queries received.The parameters can also be changed by the experienced and skilled attackers.
12) What methods do you follow 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
13) What is filter index?
14) Distinguish between COMMIT and ROLLBACK?
ROLLBACK – Every statement between BEGIN and ROLLBACK are revealed to the state when the ROLLBACK is executed.
15) Explain Logical operators in SQL Server?
- ALL – It returns true if all the set of operations are true.
- AND – It returns true if the Boolean expressions are true.
- IN – It returns true if the operand is equal to one of the lists of expressions.
- ANY – It returns true if any of the set of comparisons is true.
- BETWEEN – It returns true if the operand is within a range.
- EXISTS – It returns true if the subquery contains any rows.
- LIKE – It returns true if the operand matches a pattern
- NOT – It reverses the values of any Boolean operator.
- OR – It returns true if either of the Boolean expression is true.
- SOME – It returns true if some of the set of comparisons are true.
16) Explain the commands in SQL Server?
- Select
- Insert
- Update
- Delete
DDL (Data Definition Language)
- Create
- Alter
- Drop
- Truncate
DCL (Data Control Language)
- Grant
- Revoke
TCL (Transactional Control Language)
- Commit
- RollBack
- Save Transaction
17) State the difference between UNION and UNION ALL?
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.
18) Explain TABLESAMPLE?
19) What are the purposes of FLOOR and SIGN functions?
SIGN – It is used to determine whether the number provided is positive, Zero, negative and returns +1, 0, -1.
20) How are the exceptions handled in SQL Server Programming?
21) What is difference between clustered index and non clustered index?
Difference between clustered index and non clustered index
- Cluster index is an index type that is used to sort table data rows on the basis of their key values. In RDBMS primary key allows us to create a clustered index based on that specific column.
- A non-clustered index (or regular b-tree index) is an index where the order of the rows does not match the physical order of the actual data. It is instead ordered by the columns that make up the index.
22) 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.
23) 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.
24) 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.
25) Write query to return all rows sql?
The query to return all rows in SQL is: Select * From Table_name.
26) List the data types available in MySQL?
The data types available in MySQL are BIT, CHAR, DATE, DATETIME, DECIMAL, ENUM, INT, JSON, etc.
27) Truncate vs Delete in MSSQL .
Delete in MSSQL
- Deleting is one the most expensive operation, it's logged. Every row deleted is logged to the transaction log.
- Delete from ... allows to you delete specific rows.
- Delete from ... marks the row as a ghost row.
- Delete from ... does not release the space of the deleted rows, you will need to run DBCC SHRINKFILE to recover the space [single user mode].
Truncate in MSSQL
- Often people want to empty a temporary table to repopulate it with current data, then it's much faster to use the truncate statement instead of the delete statement.
- truncate table is the most efficient way of emptying a table.
- It's not possible to use the truncate table when you only want to delete some rows based on a condition.
- Truncate table does not delete rows, it deallocates the data pages and release the space.
- Truncate table cannot be used when the table is referenced by a foreign key or involved in data replication.
- Truncate table is recommended for temporary tables that need to be cleared for the next process.
Leave A Comment :
Valid name is required.
Valid name is required.
Valid email id is required.