What is the use of SET NOCOUNT ON/OFF statement?
By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).
What is UPDATE_STATISTICS command?
UPDATE_STATISTICS command is used to update the indexes on the tables when there is a large amount of deletions or modifications or bulk copy occurred in indexes.
What are the differences between Stored Procedure and the dynamic SQL?
Stored Procedure is a set of statements which is stored in a compiled form. Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply execute during run time.
SQL Server Interview Questions
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 the meaning of Not Null in SQL?
“Constraints are rules that decide what kind of data can enter into the database tables. SQL server has six types of constraints and we will explore all these constraints here with suitable examples. The constraints that we are going to explore are listed below:
Primary Key Constraint
Foreign Key Constraint
Not Null Constraint
Unique constraint
Default Constraint
Check Constraint
This constraint is useful to stop storing the null entries in the specified columns. We will mark the student name column as the not null column. This allows us to always have some entries in the student name column of the student table without having NULL. Here are the steps:
Like you did previously, bring up the table design view by clicking the modify context menu for the table.
Remove the checkmark as shown in the picture below. This action will enable the Not Null constraint for the Stud Name column.”
What are the reporting services in SQL Server?
” SQL Server Reporting Services is a comprehensive reporting platform that includes processing components. Processing components are the basis for the multilayered architecture of SQL Server Reporting Services. Processing components interact with each other to retrieve data and deliver a report.
SQL Server Reporting Services has the following two basic components.
Processors
Extensions
Architecture of SQL Server Reporting Services
The following diagram shows the architecture of SQL Server Reporting Services.
Tools and Components of SQL Server Reporting Services architecture
This architecture consists mainly of the following types of components and tools.
Report Builder
Report Designer
Report Manager
Report Server
Report server database
Data sources”
Advance SQL Server Interview Questions
What are the integration services in SQL Server?
“Integration Services is a platform for building high-performance data integration and workflow solutions, including extraction, transformation, and loading (ETL) operations for data warehousing.
This includes graphical tools and wizards for building and debugging packages.
Uses of Integration Services:
One use of Integration Services is to merge data from multiple data stores and update the data to data warehouses and/or data marts. Create the Data Transformation process logic and automate the data loading process.
Architecture of Integration Services
Some important components of using Integration Services:
SSIS Designer
Runtime engine
Tasks and other executables
Data Flow engine and Data Flow components
API or object model
Integration Services Service
SQL Server Import and Export Wizard
Other tools, wizards and command prompt utilities”
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:
Create table Table Name (ID INT, NAME VARCHAR(30) )
Drop syntax: drop table Table Name
Select Syntax: Select * from Table Name “
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.”
SQL Server Interview Questions
What is the difference between SQL and PL/SQL?
“SQL is the standard query language for adding, accessing, and manipulating data in RDBMSs. With SQL, you have:
Only simple IF / Else statements.
Through SQL you can interact with the database through ADO.NET
In SQL you can execute a line of code
It can run only on windows
PL/SQL: It is referred to as Procedure Language/Structure Query Language:
In PL/SQL you can execute a block of code not a single line of code.
Deep control statements
It can run in UNIX also.
PL/SQL language includes object-oriented programming techniques such as encapsulation, function overloading, and information hiding (all but inheritance).”
What are the encryption mechanisms in SQL server?
“We can use encryption for security of data in the database in SQL Server. Following are the encryption mechanism used in SQL server:
Transact-SQL functions
Asymmetric keys
Symmetric keys
Certificates
Transparent Data Encryption”
How can we delete a table in SQL Server?
“A user can delete a TABLE from the database by using SQL Server Management Studio or by Transact-SQL in SQL Server
Following are the steps for deleting a table using SQL Server Management
Select a Table(wanted to remove) in object explorer
Choose DELETE from the shortcut menu by right- click on the table
Click on the ‘yes’ to confirm the deletion of the table”
Advance SQL Server Interview Questions
What is sub-query in SQL server? Explain its properties.
“In SQL Server, a query within the main query like Select, Update, Insert or Delete, is termed as sub-query. It is also called as INNER Query.
A subquery can be Added to WHERE clause, the FROM clause, or the SELECT clause.
Some properties of the subqueries are given below:
A subquery must be enclosed in parenthesis
A sub-query can add WHERE, GROUP BY, and HAVING CLAUSE but it’s optional.
SELECT clause and a FROM clause must be included a subquery.
A User can include more than one query”
What is the usage of SIGN function?
“SIGN function is used to define whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0. SIGN function returns the value with its sign.
SYNTAX:
SIGN (number)
If the number>0, then it will return +1
If the number=0, then it will return 0
If the number<0, then it will return -1 “
What is the use of FLOOR function in SQL Server?
“FLOOR function is used to round up a non-integer value to the previous least integer. Floor expression returns a unique value after rounding down the expression.
SYNTAX:
FLOOR (expression)
For example:
FLOOR (7.3)”
SQL Server Interview Questions
How will you decide the active and passive nodes?
Open Cluster Administrator checks the SQL Server group where you can see the current owner. So the current owner is the active node, and other nodes are passive. Because at one time only one node can be active and must be in the passive mode in a two node.
What is the most common trace flags used with SQL Server?
“Trace flag in SQL server sets the specific characteristic of the server. It works as an “”IF”” condition for the SQL Server. The most common trace flags used with SQL Server are:
Deadlock Information: 1204, 1205, 1222
Network Database files: 1807
Log Record for Connections: 4013
Skip Startup Stored Procedures: 4022
Disable Locking Hints: 8755
Do Force uniform extent allocations instead of mixed page allocations 1118 (SQL 2005 and 2008).”
What are the Hotfixes and Patches in SQL Server?
“Hotfixes are small software patches that are applied to live systems. A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product.
For example – a software bug
A patch is a program installed in the machine to rectify the problem occurred in the system and ensured the security of that system. The hotfix is a Kind of Patches provided by the Microsoft.
In Microsoft SQL Server, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. Hotfix response proactively against any bug”
Advance SQL Server Interview Questions
What are the different types of collation sensitivity in SQL Server?
“There are four types of collation sensitivity in SQL Server:
Case sensitivity
Accent sensitivity
Kana Sensitivity
Width sensitivity
Explained in detail in Question number 5″
What are the advantages of using third-party tools?
“A list of advantages of using third-party tools:
Third party tools provide faster backups and restore.
They provide flexible backup and recovery options.
They provide secure backups with encryption.
They provide the enterprise view of your backup and recovery environment.
Easily identify optimal backup settings.
Visibility into the transaction log and transaction log backups.
Timeline view of backup history and schedules.
Recover individual database objects.
Encapsulate a complete database restore into a single file to speed up restore time.
When we need to improve upon the functionality that SQL Server offers natively.
Save time, better information or notification.
Third party tools can put the backups in a single compressed file to reduce the space and time.”
Which is the main third-party tool used in SQL Server?
“A list of third-party tools used in SQL Server:
SQL CHECK – Idera: It is used to monitor server activities and memory levels.
SQL DOC 2 – Red Gate: It is used to document the databases.
SQL Backup 5 – Red Gate: It is used to automate the Backup Process.
SQL Prompt – Red Gate: It provides IntelliSense for SQL SERVER 2005/2000.
Lite Speed 5.0 – Quest Soft: It is used for Backup and Restore.”
SQL Server Interview Questions
What is a query?
A DB query is a code written in order to get the information back from the database. Query can be designed in such a way that it matched with our expectation of the result set. Simply, a question to the Database.
What is subquery?
A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. Sub Query is always executed first, and the result of subquery is passed on to the main query.
What are the types of subquery?
“There are two types of subquery – Correlated and Non-Correlated.
A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.
A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.”
Advance SQL Server Interview Questions
What is DBMS?
A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.
What is the Cartesian product of the table?
The output of Cross Join is called a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 rows.
What is an alternative for TOP clause in SQL?
“1. ROWCOUNT function
- Set rowcount 3
- Select * from employee order by empid desc Set rowcount 0 “
SQL Server Interview Questions
How to delete a row in SQL?
“We will be using the DELETE query to delete existing rows from the table:
DELETE FROM table_name
WHERE [condition];
We will start off by giving the keywords DELETE FROM, then we will give the name of the table, after that we will give the WHERE clause and give the condition on the basis of which we would want to delete a row.
For example, from the employee table, if we would like to delete all the rows, where the age of the employee is equal to 25, then this will the command:
DELETE FROM employee”
How to delete a table in SQL?
“There are two ways to delete a table from sql: DROP and TRUNCATE. The DROP TABLE command is used to completely delete the table from the database. This is the command:
DROP TABLE table_name;
The above command will completely delete all the data present in the table along with the table itself.
But if we want to delete only the data present in the table but not the table itself, then we will use the truncate command:”
What are system functions and give one example
System functions are operations performed on the database server, and values are returned accordingly. Example @@ERROR – Returns 0 if the previous Transact-SQL statement encountered no errors. Otherwise returns an error number.
Advance SQL Server Interview Questions
What is T-SQL?
It is an extension of SQL(Structured Query Language) developed by Sybase and used by Microsoft.
What is SAVEPOINT in transaction control?
“A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
SQL> SAVEPOINT A
SQL> INSERT INTO TEST VALUES (1,’Savepoint A’);
1 row inserted.
SQL> SAVEPOINT B
SQL> INSERT INTO TEST VALUES (2,’Savepoint B’);
1 row inserted.
SQL> ROLLBACK TO B;
Rollback complete.
SQL> SELECT * FROM TEST;
ID MSG”
List and explain the different types of JOIN clauses supported in ANSI-standard SQL.
“ANSI-standard SQL specifies five types of JOIN clauses as follows:
INNER JOIN (a.k.a. “simple join”): Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table. This means that if the ON clause doesn’t match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn’t find any matching records in the left table. This means that if the ON clause doesn’t match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.
FULL JOIN (or FULL OUTER JOIN): Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.
CROSS JOIN: Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria (“implicit join notation”).”
SQL Server Interview Questions
What is an execution plan? When would you use it? How would you view the execution plan?
“An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.
In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” (located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is r
What are the NVL and the NVL2 functions in SQL? How do they differ?
“Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null.
With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1.
With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.”
What is the difference between the WHERE and HAVING clauses?
“When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.
However, when GROUP BY is used:
The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).”
Advance SQL Server Interview Questions
Can we insert a row for identity column implicitly?
“SET IDENTITY_INSERT TABLE1 ON
INSERT INTO TABLE1 (ID,NAME)
SELECT ID,NAME FROM TEMPTB1
SET IDENTITY_INSERT OFF”
How do you get the last id without the max function?
“In MySQL:
select id from table order by id desc limit 1
In SQL Server:
select top 1 id from table order by id desc”
What is the difference between IN and EXISTS?
“IN:
Works on List result set
Doesn’t work on subqueries resulting in Virtual tables with multiple columns
Compares every value in the result list
Performance is comparatively SLOW for larger resultset of subquery
EXISTS:
Works on Virtual tables
Is used with co-related queries
Exits comparison when match is found
Performance is comparatively FAST for larger resultset of subquery”
SQL Server Interview Questions
How can you use a CTE to return the fifth highest (or Nth highest) salary from a table?
“Declare @N int
set @N = 5;
WITH CTE AS
(
SELECT Name, Salary, EmpID, RN = ROW_NUMBER()
OVER (ORDER BY Salary DESC)
FROM Employee
)
SELECT Name, Salary, EmpID
FROM CTE
WHERE RN = @N”
How do you copy data from one table to another table ?
“INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;”
Write a single SQL statement to concatenate all the customer names into the following single semicolon-separated string:
“SELECT CustomerName+ ‘; ‘
From Customer
For XML PATH(”)
This is close, but will have an undesired trailing ;. One way of fixing that could be:
SELECT top 1
LTRIM(STUFF((SELECT ‘; ‘ + c1.CustomerName FROM Customer c1 FOR XML PATH (”)), 1, 1,”)) as SSV
from Customer c2;
In PostgreSQL one can also use this syntax to achieve the fully correct result:
SELECT array_to_string(array_agg(CustomerName), ‘; ‘::text)
FROM Customer”
Advance SQL Server Interview Questions
How do you get the Nth-highest salary from the Employee table without a subquery or CTE?
“SELECT salary from Employee order by salary DESC LIMIT 2,1
This will give the third-highest salary from the Employee table. Accordingly we can find out Nth salary using LIMIT (N-1),1.
But MS SQL Server doesn’t support that syntax, so in that case:
SELECT salary from Employee order by salary DESC
OFFSET 2 ROWS
FETCH NEXT 1 ROW ONLY
OFFSET’s parameter corresponds to the (N-1) above.”
What do you mean by data manipulation language
“Data manipulation Language or DML is used to access or manipulate data in the database.
It allows us to perform below listed functions:
Insert data or rows in database
Delete data from database
Retrieve or fetch data
Update data in database.”
What is ACID property in a database?
“ACID property is used to ensure that the data transactions are processed reliably in a database system.
A single logical operation of a data is called transaction.
ACID is an acronym for Atomicity, Consistency, Isolation, Durability.
Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.
Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.”
SQL Server Interview Questions
What do you understand by case manipulation functions?
“Case manipulation functions are the functions which convert the data from the state in which it is already stored in the table to upper, lower or mixed case.
Case manipulation function can be used in almost every part of the SQL statement.
Case manipulation functions are mostly used when you need to search for data, and you don?t have any idea that the data you are looking for is in lower case or upper case.”
Which are the different case manipulation functions in SQL?
“There are three case manipulation functions in SQL:
LOWER: converts character into Lowercase.
UPPER: converts character into uppercase.
INITCAP: converts character values to uppercase for the initials of each word.”
