contact@textnotes.in

SQL Server Interview Questions & Answers

What is Database?

A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches.

What is DBMS?

DBMS stands for Database Management System. DBMS is a system software responsible for the creation, retrieval, updation, and management of the database. It ensures that our data is consistent, organized, and is easily accessible by serving as an interface between the database and its end-users or application software.

What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

What is SQL?

SQL stands for Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data.

What is the difference between SQL and MySQL?

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

What are Tables and Fields?

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.

What are Constraints in SQL?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:

  • NOT NULL - Restricts NULL value from being inserted into a column.
  • CHECK - Verifies that all values in a field satisfy a condition.
  • DEFAULT - Automatically assigns a default value if no value has been specified for the field.
  • UNIQUE - Ensures unique values to be inserted into the field.
  • INDEX - Indexes a field providing faster retrieval of records.
  • PRIMARY KEY - Uniquely identifies each record in a table.
  • FOREIGN KEY - Ensures referential integrity for a record in another table.
What is a Primary Key?

The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.

What is a UNIQUE constraint?

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.

What is a Foreign Key?

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.

What is a Join? List its different types.

The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.

  • (INNER) JOIN: Retrieves records that have matching values in both tables involved in the join. This is the widely used join for queries.
  • LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.
  • RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.
  • FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or right table.
What is a Self-Join?

A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query.

What is the Windows Authentication Mode in SQL Server?

This mode connects the server via a Windows account. The server uses the username and password for authentication. In this mode, SQL server authentication is disabled.

Give an example of a function in an SQL server that returns the first non-null expression from more than one column in arguments.

Select COALESCE(sid, sname, marks) from the student;

Explain the one-to-many relationship in the SQL Server database.

When a single column value in one table has a minimum of one dependent column value in some other table, a one-to-many relationship exists.

What is the significance of CHECK in SQL Server?

CHECK constraint limits the values that can be placed inside a table’s column. This maintains integrity. The constraint is used column-wise to give specific values to that column. Example: CONSTRAINT CHK_Student CHECK (age<20)

How to find the 3rd highest marks from the Student table?

SELECT TOP 3 marks FROM (SELECT DISTINCT TOP 3 marks FROM student ORDER BY marks DESC) a ORDER BY marks

What is a trigger?

When a table event occurs, such as INSERT, DELETE, or UPDATE, triggers allow executing an SQL code batch. Triggers are managed by DBMS and can also execute stored procedures. [2] For example, when a record is inserted in a database table, a trigger can be set.

When can records be deleted from a view in SQL Server?

Records can be deleted in a ‘simple’ view as it contains data from one table only.

List down some of the features of MS SQL Server.
  • It provides an easy and straightforward Syntax.
  • MS SQL uses transact SQL.
  • Query optimization is not supported.
  • The transaction process does not allow rollbacks
  • Clustering is not supported
  • Statements are executed serially.
Which command can be used to get the version of SQL Server?

To get the version of SQL Server, use: 

            Select SERVERPROPERTY('productversion')

In SQL Server, what is a user defined function?

A user defined function allows users to write their logic as per need. The advantage is that it is not limited to pre-defined functions and writing functions, simply complex SQL code. The return type is a table or a scalar value.

Example: Create function sample(@sid int) returns table as return select * from s where Id = @sid

Explain types of replication in SQL Server.

There are three types of replication as follows:

  • Transactional replication- It is a process of data distribution from publisher to subscriber. Transactional replication can be used when data is changed frequently.
  • Merge replication- It groups the data to a single centralized database from various sources. Merge replication is used in cases where central and branch databases need to update information simultaneously.
  • Snapshot replication- This replication is the best way to replicate data that changes infrequently, and it is easiest to maintain. Example: Snapshot replication can be used for lists that are updated once per day and needs to be distributed from main server to branch servers.
Define referential integrity.

Every foreign key value must have a corresponding primary key value. The maintenance of this consistency between foreign and primary keys is known as referential integrity.

What are TCL Commands? and List down the TCL Commands available on SQL Server?
    TCL or Transactional Control Language commands are used to manage different transactions taking place in a database. The three TCL commands are as follows:
  • Rollback- This is used to restore the database to the last committed state
  • Save Tran- This saves the transaction, and the transaction can be rolled back to this point.
  • Commit- Saves the transaction permanently in the database
Write a SQL Server Query to get the letter ‘e’ in the name ‘Jenna’ from the student table.
Select CHARINDEX('e',NAME,0) from student where name='Jenna'
As a SQL developer, how will you ensure that SQL server-based applications and databases perform well?

The volume of data, type of information stored, and data to be accessed must be checked. When a system is being upgraded, the present data should be analyzed, and the methods of accessing data should be checked to help understand problem design. Keeping the information about data is necessary when using a new system.

When should Server-based cursors be used?

When you require to work on one record at any instance of time, instead of taking all the data from the table as bulk. Cursors’ performance is affected when large volumes of data are present.

Tell us about the working of the FLOOR function.

FLOOR function rounds the given non-integer value to the previous least integer—for example, FLOOR(5.6) returns 5

What do you know about scheduled tasks in SQL Server?

Scheduled jobs or tasks automate processes that can be run at a prescribed time at a regular interval. By scheduling tasks, human intervention is reduced, and tasks can be carried out at any time in the order that the user wants.

Mention a query that returns the list of triggers in a database.
Select * from sys.objects where type='tr'
Differentiate between rollback and commit.

When COMMIT is executed, all statements between BEGIN and COMMIT become persistent to the database. Whereas, when ROLLBACK is executed, all statements between ROLLBACK and BEGIN are reverted to the state.

Explain how to create a table in SQL.

The following query is used to create a SQL table: Create table name_of_table( column1 datatype, column2 datatype ) For Example : 

            create table Student (    
    Name varchar(20),     
    DOB date,     
    Marks nvarchar(5),     
    Subject varchar(20) )

What is the function of a foreign key in a database?

A foreign key is used to define a relationship between the parent and child table connected by columns. The foreign key is a constraint that ensures that the values of the child table appear in the parent table. The foreign key of one table is the primary key of the other, and a table can have several foreign keys. For example: 

            student {ID, Name, Age, Contact, Gender, Add}
    
    teacher{Teach_ID, Name, ID}
Here, ID is the foreign key for the teacher table.

What is the importance of views in a database?

There are scenarios where we need to look for a view to getting the solution, such as:

  • Aggregating data for performance
  • Customizing the schema and data for a set of users
  • Controlling access to columns and rows of data
Tell us the steps to hide SQL Server Instances.

To hide the SQL Server Instances, we need to make changes in SQL Server Configuration Manager, and to launch it, the following steps are needed:

  • Select instance of SQL server
  • Select properties after right-clicking
  • Set Hide Instances to Yes and click on APPLY
  • Post changes, restart the instance of SQL Server
Explain the DBCC command and its use.

Database Consistency Checker (DBCC) checks the consistency of the database; It helps in reviewing and monitoring the maintenance of database, tables, and operation validation. For example:

  • DBCC CHECKALLOC checks all pages in the database to ensure they are correctly allocated.
  • DBCC CHECKDB makes sure that indexes are correctly linked in the tables of the database.
  • DBCC CHECKFILEGROUP checks all file groups for damage.
Describe the SIGN function.

The SIGN function is used to specify a number as positive, zero, or negative. It returns the following: SIGN (number) 

        Returns – 1 if number <0, +1 if number>0 and 0 if number=0
Define alternate key.

When a table has more than one candidate key (i.e., candidate for primary keys), one becomes the primary key, and the rest are the alternate keys.

Define Join. What are the different types of joins?

Joins are used in SQL queries to describe how different tables are related. They also allow users to select data from one table depending on the data of the other table. The different types of joins are:

  • INNER Joins
  • OUTER Joins- LEFT OUTER, RIGHT OUTER, FULL OUTER
  • CROSS Joinss
Tell about the use of UPDATE STATISTICS.

UPDATE STATISTICS is used to update information about the distribution of the key values for one or more statistic groups/collections in the indexed view or specified table.

Define Full backup.

The most common type of backup in SQL server is the complete backup of the database. It also includes part of the transaction logs for recovery.

In SQL, what is meant by the identity column?

In SQL, an identity column generates numeric values automatically. These columns need not be indexed, and we can define the start and increment value of the identity column.

Explain the UNIQUE KEY constraint.

The UNIQUE constraint maintains the uniqueness of records in the set of columns to ensure there are no duplicate values. This constraint enforces entity integrity.

Define the process of de-normalization.

The process of de-normalization adds redundant data to a database in order to enhance the performance. This technique moved from higher to lower normal forms of the database. This speeds up the database access.

Show how table type constraint can be applied to a table.
        Alter Table Name_of_the_Constraint
            
            Alter Table Constraint_1
Differentiate between derived persistent attribute and derived attribute.

A derived attribute is obtained from values of other existing columns as its values do not exist on their own. A derived attribute that can be stored is a derived persistent attribute.

What do you mean by database?

A database is a structured form of data storage where data can be retrieved and managed efficiently. It is the collection of tables where the data is stored in an organized way. Tables consist of rows and columns in which rows are also known as records or tuples, and columns are known as attributes. Bank Management Database and University Management Database are a few examples of databases.

What is a Relational Database?

A relational database is the collection of data that have an established relationship with each other. It consists of rows and columns. Each row in a database is a record, and columns have the attributes of data. Each row in a table can be identified with a unique ID known as a primary key. And rows in a table can be related to other tables using foreign keys.

What do you mean by RDBMS?

RDBMS is nothing but – Relational DataBase Management System. It is the software that allows storing, managing, querying, and retrieving data from a relational database. And RDBMS interacts with users and the database; it can also carry out administrative tasks such as – managing data storage, accessing data, and assessing database performance.

Can you differentiate between SQL and MYSQL?
SQL MySQL
It is the licensed product of Microsoft It is an open-source platform managed by Oracle Corporation
Knowns as Structured Query Language – It is a query programming language It is the Relational Database Management System
It uses SQL queries to perform tasks in RDBMS It has no separate querying language. Uses SQL to perform tasks in RDBMS
Data is protected safely by not allowing third parties to intrude on the SQL servers. Unlike SQL, it is an open-source platform, so data security wouldn't be as expected.
SQL doesn’t support any connectors MySQL supports connectors such as the workbench tool to build databases.

What do you mean by Relationships between Tables and mention their Types?

Relationships between tables describe how a row in a table is related to a row or rows of another table in a database.

There are three types of relationships, as mentioned below:

  • One to one: When a row in a table has only one related row in another table.
  • One to many: When a row in a table has multiple related rows in another table.
  • Many to many: When a row has multiple related rows in another table and vice-versa.
What is an Entity in a Database?

Entities are nothing but objects that are stored in the master data services models. They are real-world objects that can be distinguishable from other objects. Rows in an entity represent members of the master data, and columns represent attributes of the master data. Generally, entities group the same kind of data. For example, a list of employees of a company.

What do you mean by Attributes and mention their Types?

Attributes are the objects that are included in master data service entities. Also, attribute values are used to describe the members of the entity.

There are three types of attributes, as mentioned below:

  • Free-form attributes
  • Domain-based attributes
  • File attributes
What is SQL Server, and mention its core components?

SQL Server is an RDBMS developed by Microsoft. It has two core components – database engine and SQLOS. The database engine has a relation engine that supports processing queries, and managing database files, pages, indexes, etc. And SQLOS is nothing but a SQL Operating system. SQLOS provides operating systems such as memory and I/O management. It also performs the operations like exception handling and synchronization services.

What is a transaction in SQL Server and mention its modes?

A transaction represents a single task. Once a transaction is over, modifications made in the data will be committed and stored in a database permanently. If an error occurs in the transaction, then the data changes will be canceled immediately.

The following are the modes of transactions:

  • Autocommit transactions
  • Explicit transactions
  • Implicit transactions
  • Batch-scoped transactions
What is a Transaction Log, and why is it important?

Transaction log records all transactions and related database modifications of every transaction. To be precise, it records the beginning of a transaction, the changes during the transaction, and the last COMMIT or ROLLBACK of the transaction. The transaction log is one of the vital components in database management since it is used to retrieve the database to a consistent state if there is a system failure.

What are ACID properties, and what do they mean?

The ACID properties are nothing but Atomicity, Consistency, Isolation, and Durability. Generally, all transactions must follow ACID properties.

  • Atomicity: It ensures that a complete transaction must take place in a single execution. Suppose there is only a subset of operations during a transaction, then there could be a compromise in the aim of the transaction. But, atomicity eliminates this possibility.
  • Consistency: It ensures that a transaction takes place with absolute data consistency before and after the transaction. Simply put, data consistency shouldn’t be compromised during transactions.
  • Isolation: It ensures that each transaction takes place in complete isolation from other transactions. It means that each transaction must run as if it is the only transaction that is happening in the system.
  • Durability: It makes sure that every transaction must be recoverable when required. Once a transaction is committed, it means that all the data updates have been made in the database then, and they can be retrieved at any time by users.
What is a Stored Procedure?

It is a function that consists of a group of statements, which can be stored and used repeatedly. Stored procedures can be called and executed as and when required. Stored procedures are stored as ‘Named object’ in the SQL server database. The result set of the stored procedure depends on the values of the parameters.

What is the use of an Extended Stored Procedure?

Generally, stored procedures are the blocks of codes that can be accessed by simple application calls. At the same time, extended stored procedures help expand the functionality of SQL servers through external functions written in C or C++ languages. As a result, it allows returning result sets and parameters to the server from different data sources.

What is a Recursive Stored Procedure?

A stored procedure turns into a recursive stored procedure, including a CALL statement in the procedure body. Here, the CALL statements can be nested. In fact, there is no limit for recursions, but stored procedures nesting is limited by 15 times. Moreover, there are two forms of recursive stored procedures – mutual recursion and chain recursion.

What are Database Normalisation and Denormalisation?

Database normalization is the process of restructuring a relational database to reduce data redundancy and improve data integrity. On the other hand, denormalization is a reverse engineering process that helps increase the read performance of a database. And it is achieved by either adding copies of data or grouping data. Hence, data can be read in a shorter time.

What is a JOIN, and mention its Types?

JOIN is a logical operation used to retrieve data from two or more two tables. It can only be accomplished when there is a logical relationship between two tables. Here, data from one table is used to select rows in another table.

There are five types of logical JOIN operations as mentioned below:

  • INNER JOIN
  • LEFT (OUTER) JOIN
  • RIGHT (OUTER) JOIN
  • FULL (OUTER) JOIN
  • CROSS JOIN
What is Subquery in SQL Server?

It is known as subquery when a query is nested inside the statements such as SELECT, INSERT, UPDATE, or DELETE. Besides, a nested query inside a subquery is also known as a sub-query. Further, a subquery is also known as an inner query or an inner select. Similarly, the statement that has a subquery is known as an outer query or an outer select.

What are the Types of Subquery in SQL Server?

The following are the subquery types:

  • Subqueries with table aliases
  • Subqueries with IN and NOTIN
  • Subqueries in UPDATE, DELETE and INSERT statements
  • Subqueries with comparison statements
  • Comparison operators modified by ANY, SOME or ALL
  • Subqueries with EXISTS and NOT EXISTS
How can you differentiate between a Primary Key and a Unique Key?

The primary key identifies each record in a table. It should have unique values but shouldn’t have NULL values. At the same time, the unique key ensures that all the values in a column are different. Simply put, the unique key avoids duplication of data in a column except for NULL Values. Moreover, a table will have only one primary key, but it may have many unique keys.

How can you relate a Foreign Key and a Primary Key?

A foreign key is a field (s) in a table that links the primary key with another table. A foreign key is mainly used to prevent the loss of a link between two tables. The table that has a primary key is known as the parent table, and a table that has a foreign key is known as the child table. A Foreign key can link with a unique key of another table as it links with a primary key.

What are Defaults in the SQL Server?

Default is the value specified for a column in a database table. This value will be duplicated in all the new records of the column unless there is no other value specified. In this regard, SQL server management studio is the tool used to specify a default value. Know that we can create defaults only for a current database, and the default value can be truncated when it exceeds the size of the column.

What are Cursors, and mention their types?

Cursors are known to be the extensions to result in sets that are the group of rows returned for a statement. They help retrieve one or more blocks of rows from the current position. Similarly, they can support data modifications for the rows in the current position in the result set.

There are four cursor types, as you can find below:

  • Forward only
  • Static
  • Keyset
  • Dynamic
What are Triggers, and mention their types in the SQL server?

Triggers are the special stored procedures. When there is an event in the SQL server, triggers will run automatically. There are three types of triggers: LOGON, DDL, and DML.

  • LOGON Triggers: They are fired when a user establishes a LOGON event.
  • DDL Triggers: They are fired when there is a Data Definition Language (DDL) event.
  • DML Triggers: They are fired when there is a modification in data due to Data Manipulation Language (DML).
When can you use Triggers in the SQL Server?
  • When there is a need for an audit trail of activity in a database
  • Deriving additional data that is not available within a database
  • When enforcing referential integrity
  • To implement a business rule
What is Referential Integrity?

Referential integrity aims at keeping SQL databases consistent. It is achieved using a group of rules that enforces relationships among data in tables. Generally, referential integrity is enforced with the support of foreign key constraints. Besides, it can be enforced with the help of check constraints with user-defined functions and triggers.

What do you mean by ER Diagram?

The ER diagram is known as the Entity-Relationship diagram. This diagram shows the visual representation of the structure of tables in databases and their logical relationships. ER Diagram displays table structures with column names and the associated data types, primary and foreign keys used, and relationships between the tables.

What do you mean by CTE?

CTE is represented as Common Table Expression. It specifies the temporary named result set. This result set is obtained by executing simple queries. CTE can be referred to in SELECT, INSERT, UPDATE, DELETE and MERGE statements. Moreover, CTE can also be used in VIEW statements. There are two types of CTE – recursive and non-recursive.

What is a Sparse Column?

Sparse columns are nothing but ordinary table columns that provide optimized storage for NULL values. They reduce space requirements for NULL values by about 20 to 40 percent. Sparse columns can be used with filtered indexes and column sets. Sparse columns are defined by CREATE TABLE and ALTER TABLE statements.

What do you mean by Shared, Exclusive, and Updated locks?
  • Shared locks: It allows a page or rows only for reading. It restricts modifications of data by concurrent transactions.
  • Exclusive locks: It allows exclusive transactions to modify a page or row using DML statements such as INSERT, UPDATE, and DELETE.
  • Updated locks: It is used to avoid deadlocks. You can place a shared lock over a resource that already has an updated lock.
What is SQL Server Profiler?

It is a graphical user interface used for monitoring an instance of the database engine. It creates and manages traces and analyses the trace results when there is a demand. Generally, trace files record events, which can be replayed while diagnosing issues such as slow-running queries, finding the root cause of problems, etc.

What do you mean by Check Constraints?

Check constraints help to limit values to ensure their domain integrity. For instance, you can use logical operators that will only return either TRUE or FALSE. They can be used to create a check constraint to limit upper and lower values within a range. Also, you can apply multiple check constraints to a single column; similarly, you can apply a single constraint to multiple columns.

What is an SQL Server Agent?

SQL server agent is a Microsoft Windows service that runs jobs on-demand, on schedule, or in response to an event. Generally, jobs are known as scheduled administrative tasks, and the SQL server agent executes the jobs in the SQL server. Jobs may contain one or more steps, and each step will have a task. For example, if there is a problem in a backup, the SQL server will record this event and notify the user.

What is COALESCE in SQL server and mention its few properties?

It is an expression that evaluates arguments in a list and returns only the first value that is not NULL.

For example, consider the following statement

SELECT COALESCE (NULL, 14, 15);

Now, the COALESCE expression returns the first value 14, which comes first after a NULL value.

Properties of COALESCE expression:

  • The data type must be the same
  • It can be a syntactic shortcut for the case expression
What is BULK COPY in SQL server?

BULK COPY allows a large amount of data transfer in and out of SQL tables or views. In addition, it allows data transfer between an SQL server and an Operating System data file such as ASCII.

BULK COPY in SQL server can be done in the following four modes:

  • Native mode data file: Bulk copy from a table or view into a table or view in the same format.
  • Character mode data file: Bulk copy from a table or view into another table in a different format
  • Bulk copying from a data file into a table or view
  • Loading data into program variables initially and then bulk copying into a table or view.
What do you mean by Collation?

Collation refers to the pre-defined rules to sort and compare data, and it also defines bit patterns that represent characters in a database. The rules help determine how the data should be stored, accessed, and compared. In addition, collation also provides case sensitivity and accent sensitivity for a dataset. SQL servers can store objects that have different collations in a single database.

What is the use of the UPDATE_STATISTICS command?

SQL Server updates query optimization statistics regularly in a database table or indexed view. In this regard, the SQL server's query optimizer performs this function by default. UPDATE_STATISTICS is the command that allows updating query statistics frequently in database tables. As a result, it improves query plans and query performance as well.

What is a Filtered Index?

A filtered index is nothing but a non-clustered index with an optimized disk-based restore. It uses a filter predicate to select a portion of rows in a table. A filtered index is created when a column has fewer relevant values for queries. Hence, it helps to improve query performance, reduce storage costs, and index maintenance.

How can you select data from an SQL table?

The below example shows the retrieval of all the data from a table using the SELECT statement.

SELECT *

    FROM DimEmployee

    ORDER BY LastName;

Suppose you need to select a portion of a table, then the WHERE clause must be used along with the SELECT statement to retrieve the required data. The below example shows the meaning of the WHERE clause in a SELECT statement. 

    SELECT FirstName, LastName, StartDate AS FirstDay
    FROM DimEmployee
    WHERE EndDate IS NOT NULL
    AND MaritalStatus='M',

    ORDER BY LastName;
A table variable or a temporary table: which one is faster?

A table variable functions faster than a temporary table because table variables are stored in memory, whereas temporary tables are stored in a disk. Moreover, if a table variable’s size exceeds the size of memory, then its speed decreases.

What do you mean by Scheduled Tasks in SQL server?

To update the backups and statistics, databases must be maintained regularly. It can be achieved using scheduled jobs. In this way, the SQL server agent supports scheduling tasks in the SQL server to perform maintenance at regular intervals. In addition, the SQL server agent helps to view and modify the scheduled tasks.

What is the use of the SIGN function?

The SIGN function returns the sign of a number. The syntax for this function is shown as follows: 

            SIGN (numeric_expression)

This function returns values based on the following conditions:
When numeric_expression > 0; returns positive (+1)
When numeric_expression = 0; returns zero (0)
When numeric_expression < 0; returns negative (-1)

What is a TABLESAMPLE?

This is the SQL statement that allows extracting random samples from a table using FROM statements. When users don’t require the entire dataset of a table, this statement can be applied to extract only the necessary samples. Note that the extracted rows won't be in any order, and sampling can be performed based on the percentage of rows.

What is SQL injection?

It is a malicious attack that would be sent targeting an SQL server instance. It is usually inserted through strings and will be passed into the SQL server for execution. TO OVERCOME THIS ATTACK, all SQL statements need to be verified for SQL injection vulnerabilities before their execution. Otherwise, the SQL server will execute the statements as usual, which will, in turn, cause harm to resources.

What is Database Mirroring, and mention its benefits?

Database mirroring allows keeping two copies of a single database in two different locations. The database is copied into different server instances of the SQL server database engine. It is applicable for the databases which adapt the full recovery model.

The benefits of database mirroring are as follows:

  • It increases the availability of the database through high-safety mode with automatic failover.
  • It increases data protection irrespective of the mode –whether high-performance mode or operating mode.
  • It increases the availability of the production database during upgrades, which will reduce downtime significantly.
What are the various SQL Database Functions?

Aggregate functions : They work on a group of values and return a single value. Analytic functions : They work with a set of groups and can return multiple rows for each group. Ranking functions : They return the ranking value for each row. Rowset functions : They return a value that can be used as the table reference. Scalar functions : They work on a single value and return a single value as well.

What is the difference between Stored Procedures and Functions?

In stored procedures, codes are usually compiled, and these compiled codes are executed when the program calls them. But in functions, on the contrary, codes are compiled and executed every time they are called. Also, there must be a return while executing functions, whereas it is optional while executing stored functions. Furthermore, functions can be called from stored procedures, but stored procedures cannot be called from functions.

Can you mention the different types of Queries in SQL Servers?

Select Query: This query creates the SQL SELECT statement. It retrieves data from tables or views. Insert results: This query creates the SQL INSERT INTO….SELECT statement. It allows copying rows from one table to another and copying rows within a table. Insert Values: This query creates the SQL INSERT INTO….VALUES statement. It creates new rows in tables and inserts values into them. Update Query: This query creates SQL UPDATE…SET statement. It allows updating values in multiple rows. Delete Query: This query creates the SQL delete statement. It helps to remove rows from a table. Make table query: This query creates the SQL SELECT…INTO statement. It creates a new table and rows in it.

What is the use of Database Engine in the SQL Server?

The database engine can store, process, and secure data. It creates database objects such as stored procedures, views, and triggers; and executes the objects. It processes queries, provides controlled access, and optimizes transactions in a database. A SQL server can support up to 50 database engine instances in a single computer.

How can you differentiate between UNION and UNION ALL?

UNION is the operator that combines two queries into a single result set using select statements. Note that it functions based on specified conditions. 

Syntax: query1 UNION query2

UNION ALL is the operator that combines two queries into a single result set, extracting all the rows from the tables without any conditions.

Syntax: query1 UNION ALL query2
Brief the different types of JOINS?

INNER JOIN: This command helps to return records that are common to both the tables
LEFT JOIN: This command helps to return values that are common to each other along with the complete records of the left table
RIGHT JOIN: This command helps to return values that are common to each other along with the complete records of the right table
FULL JOIN: This command helps return all the tables' records when there is a match between the two.
CROSS JOIN: It is known as Cartesian Join. This command returns all combinations of each row from the tables. There is no specific condition for joining two tables.

Search

Categories

Tags

© Copyright 2016 - 2025 TEXTNOTES. All Rights Reserved