SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.

What are the two authentication modes in SQL Server?

There are two authentication modes –

Windows Mode
Mixed Mode

Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page.

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 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 COALESCE in SQL Server?

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

Example –

Select COALESCE(empno, empname, salary) from employee;

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 is recursive stored procedure?

SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to levels.

CREATE PROCEDURE [dbo].[Fact]

(

@Number Integer,

@RetVal Integer OUTPUT

)

AS

DECLARE @In Integer

DECLARE @Out Integer

IF @Number != 1

BEGIN

SELECT @In = @Number – 1

EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively)

SELECT @RetVal = @Number * @Out

END

ELSE

BEGIN

SELECT @RetVal =

END

RETURN

GO

What are the differences between local and global temporary tables?

Local temporary tables are visible when there is a connection, and are deleted when the connection is closed.

CREATE TABLE #<tablename>

Global temporary tables are visible to all users, and are deleted when the connection that created it is closed.

CREATE TABLE ##<tablename>

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.

Can SQL servers have linked to other servers?

SQL server can be connected to any database which has OLE-DB provider to give a link. Example: Oracle has OLE-DB provider which has link to connect with the SQL server group.

What is sub query and its properties?

A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as

A sub query should not have order by clause
A sub query should be placed in the right-hand side of the comparison operator of the main query
A sub query should be enclosed in parenthesis because it needs to be executed first before the main query
More than one sub query can be included

Types of subquery
There are three types of sub query –

Single row sub query which returns only one row
Multiple row sub query which returns multiple rows
Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.

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(.)

Returns .

Can we check locks in database? If so, how can we do this lock check?

Yes, we can check locks in the database. It can be achieved by using in-built stored procedure called sp_lock.

What is the use of SIGN function?

SIGN function is used to determine whether the number specified is Positive, Negative and Zero. This will return +,- or .

Example –

SIGN(-) returns -

What is a Trigger?

Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, delete and update operations.

What are the types of Triggers?

There are four types of triggers and they are:

Insert
Delete
Update
Instead of

What is an IDENTITY column in insert statements?

IDENTITY column is used in table columns to make that column as Auto incremental number or a surrogate key.

What is Bulkcopy in SQL?

Bulkcopy is a tool used to copy large amount of data from Tables. This tool is used to load large amount of data in SQL Server.

What will be query used to get the list of triggers in a database?

Query to get the list of triggers in database-

Select * from sys.objects where type='tr'

What is the difference between UNION and UNION ALL?

UNION: To select related information from two tables UNION command is used. It is similar to JOIN command.
UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.

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 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.

What is Collation?

Collation is defined to specify the sort order in a table. There are three types of sort order –

Case sensitive
Case Insensitive
Binary

How can we get count of the number of records in a table?

Following are the queries can be used to get the count of records in a table –

Select * from <tablename> 
Select count(*) from <tablename> 
Select rows from sysindexes where id=OBJECT_ID(tablename) and indid<

What is the command used to get the version of SQL Server?

Select SERVERPROPERTY('productversion')

is used to get the version of SQL Server.

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 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).

Which SQL server table is used to hold the stored procedure scripts?

Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure. Name of the stored procedure is saved in the table called Sys.Procedures.

What are Magic Tables in SQL Server?

During DML operations like Insert, Delete, and Update, SQL Server creates magic tables to hold the values during the DML operations. These magic tables are used inside the triggers for data transaction.

What is the difference between SUBSTR and CHARINDEX in the SQL Server?

The SUBSTR function is used to return specific portion of string in a given string. But, CHARINDEX function gives character position in a given specified string.

SUBSTRING('Smiley',,)

Gives result as Smi

CHARINDEX('i', 'Smiley',)

Gives as result as I appears in ^rd position of the string

How can you create a login?

You can use the following command to create a login

CREATE LOGIN MyLogin WITH PASSWORD = '';

What is ISNULL() operator?

ISNULL function is used to check whether value given is NULL or not NULL in sql server. This function also provides to replace a value with the NULL.

What is the use of FOR Clause?

FOR clause is mainly used for XML and browser options. This clause is mainly used to display the query results in XML format or in browser.

What will be the maximum number of index per table?

For SQL Server Index can be used as maximum number per table. Clustered Index and Non-clustered indexes per table can be used in SQL Server.

Index can be used as maximum number per table. Clustered Index and Non-clustered indexes per table can be used in SQL Server.

Clustered Index and Non-clustered indexes per table can be used in SQL Server.

What is the difference between COMMIT and ROLLBACK?

Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.

What is the difference between varchar and nvarchar types?

Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.

What is the use of @@SPID?

A @@SPID returns the session ID of the current user process.

What is the command used to Recompile the stored procedure at run time?

Stored Procedure can be executed with the help of keyword called RECOMPILE.

Example

Exe <SPName> WITH RECOMPILE

Or we can include WITHRECOMPILE in the stored procedure itself.

How to delete duplicate rows in SQL Server?

Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.

Where are SQL Server user names and passwords stored in SQL Server?

User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.

What is the difference between GETDATE and SYSDATETIME?

Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.

How data can be copied from one table to another table?

INSERT INTO SELECT

This command is used to insert data into a table which is already created.

SELECT INTO

This command is used to create a new table and its structure and data can be copied from existing table.

What is TABLESAMPLE?

TABLESAMPLE is used to extract sample of rows randomly that are all necessary for the application. The sample rows taken are based on the percentage of rows.

Which command is used for user defined error messages?

RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.

What do mean by XML Datatype?

XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.

What is CDC?

CDC is abbreviated as Change Data Capture which is used to capture the data that has been changed recently. This feature is present in SQL Server .

What is SQL injection?

SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.

Even parameters can be manipulated by the skilled and experienced attackers.

What are the methods used to protect against SQL injection attack?

Following are the methods used to protect against SQL injection attack:

Use Parameters for Stored Procedures
Filtering input parameters
Use Parameter collection with Dynamic SQL
In like clause, user escape characters

How can we convert between Unix & MySQL timestamps?

UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp

FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

What are the differences between CHAR and VARCHAR data types?

Both CHAR and VARCHAR data types are used to store string data in the field of the table.

The differences between these data types are mentioned below:

CHAR data type is used to store fixed-length string data and the VARCHAR data type is used to store variable-length string data.

The storage size of the CHAR data type will always be the maximum length of this data type and the storage size of VARCHAR will be the length of the inserted string data. Hence, it is better to use the CHAR data type when the length of the string will be the same length for all the records.

CHAR is used to store small data whereas VARCHAR is used to store large data.

CHAR works faster and VARCHAR works slower.

What is Filtered Index?

Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with WHERE clause, then it is called Filtered Index.

What is the difference between MyISAM Static and MyISAM Dynamic?

In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.

What is the purpose of using the TIMESTAMP data type?

A TIMESTAMP data type is used to store the combination of date and time value which is characters long.

The format of TIMESTAMP is YYYY-MM-DD HH:MM: SS. It can store data from ‘– ::’ UTC to ‘– ::’ UTC. By default, the current date and time of the server get inserted in the field of this data type when a new record is inserted or updated.

What is the difference between mysql_fetch_array() and ysql_fetch_object() ?

Both mysql_fetch_array() and mysql_fetch_object() are built-in methods of PHP to retrieve records from MySQL database table.

The difference between these methods is that mysql_fetch_array() returns the result set as an array and mysql_fetch_object() returns the result set as an object.

How can you filter the duplicate data while retrieving records from the table?

A DISTINCT keyword is used to filter the duplicate data from the table while retrieving the records from a table.

What is the difference between NOW() and CURRENT_DATE()?

Both NOW()and CURRENT_DATE()are built-in MySQL methods. NOW() is used to show the current date and time of the server and CURRENT_DATE() is used to show only the date of the server.

Which statement is used in a select query for partial matching?

REGEXP and LIKE statements can be used in a SELECT query for partial matching. REGEXP is used to search records based on the pattern and LIKE is used to search any record by matching any string at the beginning or end or middle of a particular field value.

List the different index configurations possible for a table?

A table can have one of the following index configurations:

No indexes
A clustered index
A clustered index and many non-clustered indexes
A non-clustered index
Many non-clustered indexes

What is the recovery model? List the types of recovery models available in SQL Server?

The recovery model tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model. It also tells SQL server which backup is possible in a particular selected recovery model.

There are three types of recovery models:

Full
Simple
Bulk-Logged

What are the different backups available in SQL Server?

Different possible backups are:

Full backup
Differential Backup
Transactional Log Backup
Copy Only Backup
File and Filegroup backup

What is a Full Backup?

A full backup is the most common type of backup in SQL Server. This is the complete backup of the database. It also contains part of the transaction log so that it can be recovered.

What is OLTP?

OLTP means Online Transaction Processing which follows rules of data normalization to ensure data integrity. Using these rules, complex information is broken down into a most simple structure.

What is RDBMS?

RDBMS or Relational Database Management Systems are database management systems that maintain data in the form of tables. We can create relationships between the tables. An RDBMS can recombine the data items from different files, providing powerful tools for data usage.

What are the properties of the Relational tables?

Relational tables have six properties:

Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.

What’s the difference between a primary key and a unique key?

The differences between the primary key and a unique key are:

The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. They create a clustered index on the column and cannot be null.
A Unique key is a column whose values also uniquely identify every row in a table but they create a non-clustered index by default and it allows one NULL only.

When is the UPDATE_STATISTICS command used?

As the name implies UPDATE_STATISTICS command updates the statistics used by the index to make the search easier.

What is the function of mysqldump?

mysqldump is a useful utility tool of MySQL that is used to dump one or more or all databases from the server for backup or transfer to another database server.

Syntax:

For a single database,

mysqldump [OPTIONS] db_name [TABLES]

For multiple databases,

mysqldump [OPTIONS] –databases DB [DB DB…]

For all databases,

mysqldump [OPTIONS] –all-databases

Which TCP/IP port does SQL Server run on?

By default SQL Server runs on port .

What is the difference between clustered and non-clustered index?

A clustered index is an index that rearranges the table in the order of the index itself. Its leaf nodes contain data pages. A table can have only one clustered index.

A non-clustered index is an index that does not re-arrange the table in the order of the index itself. Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered indexes.

What is the difference between UNIX TIMESTAMP and MySQL TIMESTAMP?

Both UNIX TIMESTAMP and MySQL TIMESTAMP are used to represent the date and time value. The main difference between these values is that UNIX TIMESTAMP represents the value by using -bits integers and MySQL TIMESTAMP represents the value in the human-readable format.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

The differences between HAVING CLAUSE and WHERE CLAUSE is:

Both specify a search condition but the HAVING clause is used only with the SELECT statement and typically used with GROUP BY clause.
If the GROUP BY clause is not used, then the HAVING clause behaves like a WHERE clause only.

What is Mirroring?

Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from the principal server to a secondary server which keeps a secondary server up to date with the principal server.

What are the advantages of the Mirroring?

Advantages of Mirroring are:

It is more robust and efficient than Log shipping.
It has an automatic failover mechanism.
The secondary server is synced with the primary in near real-time.

What is Log Shipping?

Log shipping is nothing but the automation of backup and restores the database from one server to another standalone standby server. This is one of the disaster recovery solutions. If one server fails for some reason we will have the same data available on the standby server.

What are the advantages of Log shipping?

Advantages of Log Shipping includes:

Easy to set up.
The secondary database can be used as a read-only purpose.
Multiple secondary standby servers are possible
Low maintenance.

Can we take the full database backup in Log shipping?

Yes, we can take the full database backup. It won’t affect the log shipping.

What is an execution plan?

An execution plan is a graphical or textual way of showing how the SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.

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 a query execution plan in a separate window when the query is run again.

What is the Stored Procedure?

A stored procedure is a set of SQL queries that can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

List the advantages of using Stored Procedures?

Advantages of using Stored procedures are:

Stored procedure boosts application performance.
Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
They can be reused.
It can encapsulate logic. You can change the stored procedure code without affecting clients.
They provide better security for your data.

What is identity in SQL?

An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.

What are the common performance issues in SQL Server?

Following are the common performance issues:

Deadlocks
Blocking
Missing and unused indexes.
I/O bottlenecks
Poor Query plans
Fragmentation

List the various tools available for performance tuning?

Various tools available for performance tuning are:

Dynamic Management Views
SQL Server Profiler
Server Side Traces
Windows Performance monitor.
Query Plans
Tuning advisor

What is a performance monitor?

Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.
Some useful counters are – Disks, Memory, Processors, Network, etc.

What are ways to get a count of the number of records in a table?

SELECT * FROM table_Name;
SELECT COUNT(*) FROM table_Name;
SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid&amp;amp;lt; ;

Can we rename a column in the output of the SQL query?

Yes, by using the following syntax we can do this.

SELECT column_name AS new_name FROM table_name;

What is the difference between a Local and a Global temporary table?

If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.

What is the SQL Profiler?

SQL Profiler provides a graphical representation of events in an instance of SQL Server for monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.

What do you mean by authentication modes in SQL Server?

There are two authentication modes in SQL Server.

Windows mode
Mixed Mode – SQL and Windows.

How can we check the SQL Server version?

By running the following command:

SELECT @@Version

Is it possible to call a stored procedure within a stored procedure?

Yes, we can call a stored procedure within a stored procedure. It is called the recursion property of the SQL server and these types of stored procedures are called nested stored procedures.

What is the SQL Server Agent?

SQL Server agent allows us to schedule the jobs and scripts. It helps in implementing the day to day DBA tasks by automatically executing them on a scheduled basis.

What is the PRIMARY KEY?

The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.

What is a UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

What is FOREIGN KEY

When a one table’s primary key field is added to related tables to create the common field which relates the two tables, it called a foreign key in other tables.

Foreign Key constraints enforce referential integrity.

What is a CHECK Constraint?

A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

What are a Scheduled Jobs?

The scheduled job allows a user to run the scripts or SQL commands automatically on a scheduled basis. The user can determine the order in which command executes and the best time to run the job to avoid the load on the system.

What is a heap?

A heap is a table that does not contain any clustered index or non-clustered index.

What is BCP?

BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and views. BCP does not copy the structures the same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

What is Normalization?

The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more tables and define relationships between them. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.

List the different normalization forms?

Different normalization forms are:

NF (Eliminate Repeating Groups): Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
NF (Eliminate Redundant Data): If an attribute depends on only part of a multi-valued key, remove it to a separate table.
NF (Eliminate Columns Not Dependent On Key): If attributes do not contribute to the description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
BCNF (Boyce-Codd Normal Form): If there are non-trivial dependencies between candidate key attributes, separate them into distinct tables.
NF (Isolate Independent Multiple Relationships): No table may contain two or more :n or n:m relationships that are not directly related.
NF (Isolate Semantically Related Multiple Relationships): There may be practical constraints on information that justifies separating logically related many-to-many relationships.
ONF (Optimal Normal Form): A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF (Domain-Key Normal Form): A model free from all modification is said to be in DKNF.

What is De-normalization?

De-normalization is the process of adding redundant data to a database to enhance the performance of it. It is a technique to move from higher to lower normal forms of database modeling to speed up database access.

What is a Trigger and types of a trigger?

The trigger allows us to execute a batch of SQL code when table event occurs (INSERT, UPDATE or DELETE command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute a stored procedure.

Types of triggers that are available in the SQL Server are as follows:

DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or the view.
DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development of database environments.
Logon Triggers: These are very special triggers that fire in case of the logon event of the SQL Server. This is fired before the setup of a user session in the SQL Server.

What is the Subquery?

A Subquery is a subset of SELECT statements, whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

Types of Sub-query:

Single-row sub-query: The subquery returns only one row
Multiple-row sub-query: The subquery returns multiple rows
Multiple column sub-query: The subquery returns multiple columns

What is a Linked Server?

Linked Server is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements sp_addlinkedsrvloginisssed to add link server.

What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Where SQL server usernames and passwords are stored in a SQL server?

They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

What are the properties of a transaction?

Generally, these properties are referred to as ACID properties.
They are:

Atomicity
Consistency
Isolation
Durability

Define UNION, UNION ALL, MINUS, INTERSECT?

UNION – returns all distinct rows selected by either query.
UNION ALL – returns all rows selected by either query, including all duplicates.
MINUS – returns all distinct rows selected by the first query but not by the second.
INTERSECT – returns all distinct rows selected by both queries.

What is SQL Server used for?

SQL Server is one of the very popular Relational Database Management Systems. This is a product from Microsoft to store and manage the information in the database.

Which language is supported by SQL Server?

SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.

Which is the latest version of SQL Server and when it is released?

SQL Server is the latest version of SQL Server that is available in the market and Microsoft launched this on November th, with the support of the Linux O/S.

What are the various editions of SQL Server that are available in the market?

SQL Server is available in editions. These are as follows:

Enterprise: This delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence for mission-critical workloads and end-user access to data insights.
Standard: This delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premises and cloud-enabling effective database management.
Web: This edition is a low total-cost-of-ownership option for Web hosters and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
Express: Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications.
Developer: This edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.

What are functions in the SQL Server?

Functions are the sequence of the statements which accept inputs, process the inputs to perform some specific task and then provide the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.

What is a User-Defined function in the SQL Server and what is its advantage?

User-Defined Function is a function that can be written as per the needs of the user by implementing your logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the requirement.

This returns Scalar value or a table.

Explain the creation and execution of a user-defined function in the SQL Server?

A User-Defined function can be created in the following way:
CREATE Function fun(@num int)
returns table
as
return SELECT * from employee WHERE empid=@num;

This function can be executed as follows:

SELECT * from fun();

So, in the above case, a function with the name of ‘fun’ is created to fetch employee details of an employee having empid=.

What are the Pre-Defined functions in the SQL Server?

These are built-in functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.

Why are Views required in the SQL Server or any other database?

Views are very beneficial because of the following reasons:

Views are required to hide the complexity that is involved in the database schema and also to customize the data for a particular set of users.
Views provide a mechanism to control access to particular rows and columns.
These help in aggregating the data to improve the performance of the database.

What is TCL in SQL Server?

TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.

Which TCL Commands are available on the SQL Server?

There are TCL Commands in the SQL Server. These are as follows:

Commit: This command is used to save the transaction permanently in the database.
Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
Save Tran: This is used for saving the transaction to provide the convenience that the transaction can be rolled back to the point wherever required.

What are the types of classifications of constraints in the SQL Server?

Constraints are classified into the following types in the SQL Server:

Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. The definition of these can be given at the time of the creation of a table in the database.
Table Types Constraints: These constraints are applied on a table and these are defined after the creation of a table is complete. Alter command is used to apply the table type constraint.

How is table type constraint applied to a table?

Table Type Constraint is applied in the following way:

Alter Table Name of the Constraint

Alter Table Constraint_

What are the different types of Columns Types Constraints in the SQL Server?

SQL Server provides types of Constraints. These are as follows:

Not Null Constraint: This puts a constraint that the value of a column cannot be null.
Check Constraint: This puts a constraint by checking some particular condition before inserting data in the table.
Default Constraint: This constraint provides some default value that can be inserted in the column if no value is specified for that column.
Unique Constraint: This puts a constraint that each row of a particular column must have a unique value. More than one unique constraint can be applied to a single table.
Primary Key Constraint: This puts a constraint to have a primary key in the table to identify each row of a table uniquely. This cannot be null or duplicate data.
Foreign Key Constraint: This puts a constraint that the foreign key should be there. A Primary key in one table is the foreign key of another table. Foreign Key is used to create a relation between or more tables.

What command is used to delete a table from the database in the SQL Server and how?

DELETE Command is used to delete any table from the database in the SQL Server.

Syntax: DELETE Name of the table
Example: If the name of a table is “employee” then DELETE command to delete this table can be written as

DELETE employee;

Why is replication required on the SQL Server?

Replication is the mechanism that is used to synchronize the data among the multiple servers with the help of a replica set.

This is mainly used to increase the capacity of reading and to provide an option to its users to select among various servers to perform the read/write operations.

What command is used to create a database in the SQL Server and how?

CREATEDATABASE Command is used to create any database in the SQL Server.
Syntax: CREATEDATABASE Name of the Database
Example: If the name of a database is “employee” then create command to create this database that can be written as CREATEDATABASE employee.

What function does a database engine serve in the SQL Server?

Database Engine is a type of service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.

What are the advantages of having an index on the SQL Server?

The index has the following advantages:

Index supports the mechanism of having faster data retrieval from the database.
This forms a data structure in a way that helps in minimizing data comparisons.
This improves the performance of the retrieval of the data from the database.

What is View?

A view is a virtual table that contains data from one or more tables. Views restrict data access of the table by selecting only required values and make complex queries easy.
Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database

Mention the differences between SQL Server and MySQL.

SQL Server	MySQL
Developed by Microsoft	Developed by Oracle
Licensed software	Open-source software
Supports C#, Java C++, PHP, Visual Basic, Perl, Python, Ruby, etc	Supports PHP, Perl, Python, Ruby, etc
Doesn’t allow any kind of database file manipulation while running	Allows database file manipulation while running.
Allows query cancellation mid-way in the process	Doesn’t allow query cancellation mid-way in the process.
While backing up the data, it doesn’t block the database	While backing up the data, it blocks the database
Takes a large amount of operational storage space.	Takes less amount of operational storage space.
Available in Express and Custom mode.	Available in MySQL Community Edition, and MySQL Enterprise Edition

What do you understand by SQL Server Agent?

SQL Server Agent is a Windows service which is used to schedule and execute jobs. Here, each job contains one or more step, and each step contains a task. So, the Server Agent uses the SQL Server to store job information and run a job on a schedule.

The main components of the SQL Server Agent are Jobs, Schedules, Operators, and Alerts.
Example:
If an enterprise wishes to take a backup of the company servers at : pm on every Friday, then you can very well automate this task to let the schedule happen on its own. In a scenario, the backup encounters an error, the SQL Server Agent records the event and notifies the corresponding team.

Mention the different authentication modes in SQL Server.

The different authentication modes offered by SQL SERVER are as follows:

Windows Authentication Mode: This mode is used to connect the server through a Windows account. Here, the server takes computers’ username and password for authentication purposes. Also, the SQL server authentication mode is disabled in this mode.
Mixed mode: The Mixed mode is used to connect with an instance of SQL Server using either the SQL Server authentication or Windows authentication. In this mode, a username and password is set by the user for the database.

Mention the differences between local and global temporary tables.

Local Temporary Table	Global Temporary Table
These tables only exist for the duration of connection or duration of that statement.	These tables exist permanently in the database and only the rows get deleted when the connection gets closed.
Syntax:CREATE TABLE #<tablename>	Syntax:CREATE TABLE ##<tablename>

How can you check the version of SQL Server?

To check the version of SQL Server, you can use the following command:

SELECT @@version

The @@VERSION gives output as one nvarchar string.

What is single-user mode and what are the steps you should follow to start SQL Server in single-user mode?

It may often happen that you would want to start an instance of SQL Server in the single-user mode. You could do this, either when you want to recover data from other database systems or may want to change server configurations.

When you start the SQL Server in the single-user mode then, any member of the computer’s local Administrators group gets connected to the instance of SQL Server as sysadmin.

The following events occur on starting the database in single-user mode:

A single user connects to the server.
The CHECKPOINT process is not executed since it is executed by default at the startup.

Also, note that you have to stop the SQL Server Agent service before connecting to an instance of SQL Server in the single-user mode.

To start SQL Server in single-user mode use the command: sqlcmd –m
To connect through the Query Editor in Management Studio use: -m”Microsoft SQL Server Management Studio – Query”.

What is SQL Server Profiler?

The Microsoft SQL Server Profiler is an interface used to create and manage traces. It also analyzes and replays the trace results. Here, events are saved in a trace file which are later analyzed or used to replay a specific series of steps while debugging an issue.

You can use SQL Server Profiler for activities such as:

Finding the root cause of the problem
Monitoring the performance of SQL Server to handle the workloads.
Diagnosing the slow queries
Capturing a series of SQL statements causing problem, to further replicate the problem on the test server, while debugging the issue.
It also helps in correlating performance counters to debug issues easily.

What is the TCP/IP port on which SQL Server runs?

The TCP/IP port on which SQL Server runs is.

What are subqueries in SQL server? Explain its properties.

A subquery is a query inside another query where a query is defined to retrieve data or information back from the database. In a subquery, the outer query is called as the main query whereas the inner query is called subquery. Subqueries are always executed first and the result of the subquery is passed on to the main query. It can be nested inside a SELECT, UPDATE or any other query. A subquery can also use any comparison operators such as >,< or =.

The properties of subqueries are as follows:

Must be enclosed in parentheses as it must be executed first before the main query
More than one query can be included.
A sub query should not have ORDER BY clause but can include WHERE, GROUP BY, and HAVING CLAUSE
The subquery must be on the right-hand side of the comparison operator of the main query
A subquery must include the SELECT clause and a FROM clause.

How do you start single user mode in clustered installations?

In clustered installation, the SQL Server uses DLL available connection, and thus blocks any other connections to the server.

In this state, if you try to bring SQL Server Agent resources online, then it may fail over SQL resources to a different node as it could be configured to a group. So, to start a single user-mode in clustered installation, you can follow the below steps:

Go to advanced properties and remove -m startup parameter.
Now, put the SQL Server resource offline.
Issue the following command from the command prompt, and make sure you are at the current owner node of the group:
net start MSSQLSERVER /m.
Next, you have to verify from the cluster administrator or fail over cluster management console that SQL Server resource is still offline or not.
Then, connect to the SQL Server by using the following command and perform the required operation: SQLCMD -E -S<servername>.
Once an operation is complete, you have to close the command prompt and then bring back the SQL and other resources online through cluster administrator.

What do you understand by replication in SQL Server? Mention the different types of replication in SQL Server.

Replication in Microsoft SQL Server is a process to synchronize the data across multiple servers. This is generally done by a replica set, and these sets provide multiple copies of data with redundancy and high availability on different servers.

Not only this, but replication provides a mechanism to recover from failures. It also removes dependencies from single server to protect the loss of data from a single server.

Following are three types of replications in SQL Server:

Merge Replication: This replication groups the data from various sources to a single centralized database and is used in the server to the client environment.
Transactional Replication:This replication is a process of distributing data from publisher to subscriber and is used in server to server environment.
Snapshot Replication: This replication distributes data exactly as it appears at a specific moment and is used for replicating data, which changes infrequently.

What are the differences between MS SQL Server & Oracle?

MS SQL Server	Oracle
Provides simple and easy syntax.	Consists of complex and comparatively more efficient syntax.
Uses transact SQL or T-SQL.	Uses PL/SQL
Does not support query optimization.	Uses star query optimization.
Rollbacks are not allowed in the transaction process.	Rollbacks are allowed during the transaction process.
Allows incremental, partial and full backups	Allows incremental, full, file level, and differential backups.
Does not support clustering.	Offers support for clustered configuration.
Statements such as INSERT, UPDATE, DELETE are executed serially.	Statements such as INSERT, UPDATE, DELETE, MERGE are executed parallel.
Job is scheduled via the SQL Server Agent	Job is scheduled via Oracle scheduler or OEM

What do you understand by INTENT locks?

Microsoft SQL Server uses a lock hierarchy whenever the data is read or something is changed in the data. Whenever a row is read, SQL Server acquires a shared lock. Similarly, as soon as we change a row, SQL Server acquires an Exclusive lock. These locks are incompatible with each other. So, the INTENT locks are used to indicate at a higher level which locks are applied within a lock hierarchy. There are mainly three kinds of INTENT locks:

Intent Shared Lock(IS):This lock is used when you have a shared lock at the row level.
Intent Update Lock(IU):The Intent update lock is used when you have an update lock at the row level.
Intext Exclusive Lock(IX): This lock is used when you have an exclusive lock at the row level.

What are the steps you must follow to hide SQL Server instances?

The steps you must follow to hide SQL Server instances are as follows:

Open the SQL Server Configuration Manager and expand the SQL Server Network Configuration.
Then go to Protocols and choose the instance of SQL Server.
Later on, right-click on the instance and select the Properties

Next, in the Hide Instance box, go to the on the Flags tab, and select Yes.
Finally, click on OK, and close the dialog box.

What do you understand by the data quality services in SQL Server?

The data quality services in SQL Server is a knowledge-driven data quality product. SQL Server Data Quality Services (DQS) enable the user to build a knowledge base and thereafter use it to perform tasks such as correction, deduplication, enrichment, standardization of data.

Apart from this, DQS also provides profiling and enables you to perform data cleaning with the help of cloud-based data services.

DQS consists of two components:

Data Quality Server: It is an SQL Server instance feature consisting of three SQL Server catalogs with data-quality functionality and storage
Data Quality Client: It is an SQL Server feature which users can use to perform computer-assisted data quality analysis and manage their data quality interactively.

Explain Magic Tables in SQL server

Magic Tables are tables automatically created tables in SQL Server used to internally store the inserted, updated values for DML operations such as (SELECT, DELETE, INSERT, UPDATE, etc).

What do you understand by Change Data Capture?

Change Data Capture or most commonly known as CDC is used to record INSERT, UPDATE, DELETE activities applied on the tables. So, as the name suggests, Change Data Capture is used to capture the data that has been changed recently. The column information and metadata required to apply the changes to the target environment are captured for the modified rows and are eventually stored in the change tables. These change tables are the mirror image of the original column structure.

What do you understand by triggers and mention the different types of it?

Triggers are used to execute batches of SQL code whenever INSERT, DELETE, or UPDATE commands are executed against a table. So, basically triggers are automatically executed whenever the data is modified based on the data manipulation operations.

The different types of triggers are as follows:

INSERT
UPDATE
DELETE
INSTEAD OF

What do you understand by recursive stored procedure?

A recursive stored procedure is a problem-solving method through which you can arrive at the solution again and again.

Explain Log Shipping and mention its advantages.

The process of automation of backup to restore databases from one standalone server to another standalone standby server is known as Log Shipping. You can also understand Log shipping as one of the disaster recovery solutions, as it makes sure that even if one server fails, the standby server will have the same data as that of the server itself.

The advantages of Log Shipping are as follows:

Needs low maintenance and is easy to set up
The secondary database created is used for read-only purpose.
You can create multiple secondary standby servers
Allows a delay specified by the user for the time between the primary server backs up the log of the primary database when the secondary servers restore (apply) the log backup.

What are trace flags and mention a few common trace flags used with SQL Server?

These flags are used to alter server behavior or set server characteristics. Few common trace flags used with SQL Server are as follows

, , – These flags are used for deadlock Information.
– This trace flag increases the SQL Server Database Engine plan cache bucket count from , to , on -bit systems.
– Forces uniform extent allocations instead of mixed page allocations – (SQL and ) To reduces TempDB contention.
– This trace flag disables page prefetching scans.
– Used to run the DBCC CHECKDB command without data purity check, unless the DATA_PURITY option is specified.

Mention the differences between SUBSTR and CHARINDEX in SQL Server.

SUBSTR

CHARINDEX

Used to return a specific portion of the string in a given string

Used to return a character position in a given specified string

Example:

SUBSTRING(‘Edureka’,,)

Output:

Edur

Example:

CHARINDEX(‘r’,’Edureka’,)

Output:

What do you understand by the Analysis Services in SQL Server?

Analysis Services in Microsoft SQL Server is an analytical data engine used in business analytics and decision support. This service provides enterprise-grade semantic models for client applications and reports such as Power BI, Microsoft Excel, and other visualization tools.

The Analysis Services is available in platforms such as :

Azure Analysis Services
Power BI Premium
SQL Server Analysis Services

What do you understand by Mirroring and mention the advantages of the Mirroring?

Mirroring in SQL Server is designed to maintain a hot standby server, that is consistent with the primary server in terms of a transaction. Also, the transaction log records are sent from the principal server to the secondary server.

Following are the advantages of Mirroring:

Consists of an automatic fail over mechanism.
More efficient than Log Shipping and is more robust.
The primary server is synced with the secondary server in real-time

When do you think a developer should use SQL Server-based cursors?

SQL Server-based cursors are used when you wish to work on a record at any instance of time, rather than taking all the data from a table as a bulk. However, cursors are not preferred to be used when large volumes of data is present as it affects performance. In a scenario, where it is not possible to avoid cursors, then try to reduce the number of records to process by using a temporary table and then eventually build the cursor from this.

What part does database design plays a role in performance of an SQL Server-based application?

The physical and logical design plays an important part in the performance of SQL Server-based applications. We need to ensure that correct data is captured in proper tables, the data items have proper relationships between them and data redundancy is reduced. I would also suggest that while you are designing a database, make sure that it is an iterative process to achieve all the required system goals and is under constant observation. Once the database design is set, it is very tough to change the design according to requirement. You can only add new relationships and data items.

What do you understand by User-Defined function in the SQL Server and explain the steps to create and execute a user-defined function in the SQL Server?

A user-defined function is a function written as per the needs of the user by implementing logic. In these kinds of functions, the user is not limited to pre-defined functions and simplify the complex code of predefined function by writing simple code. This function returns a scalar value or a table.

To create a user-defined function, refer to the following example:
CREATE FUNCTION samplefunc(@num INT)

RETURNS TABLE

AS

RETURN SELECT * FROM customers WHERE CustId=@num

To execute the above-created function, refer to the following command:

SELECT * FROM samplefunc()

How can you ensure that the database and SQL Server based application perform well?

A developer must check type of information stored, volume of data and the data that will be accessed.

In a scenario, where you are upgrading an existing system, you should analyze the present data, existing data volumes occur, and check the method through which data is accessed, to help you understand the problem areas for design.

In a scenario, where you are using a new system, you have to keep the information about what data will be captured, and what are the constituents of data, and the relationship between the data items.

What are relationships and mention different types of relationships in the DBMS

A relationship in DBMS is the scenario where two entities are related to each other. In such a scenario, the table consisting of foreign key references to that of a primary key of the other table.

The different types of relationships in DBMS are as follows:

One-to-One Relationship – Used when a single row in Table A is related to a single row in Table B.
One-to-Many Relationship – Used when a single row in Table A is related to many rows in table B.
Many-to-Many Relationship – Used when many rows in table A can be related to many rows in table B.
Self -Referencing Relationship – Used when a record in table A is related to record in same table.

What are joins in SQL and what are the different types of joins?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are joins in SQL namely:

Inner Join
Right Join
Left Join
Full Join

What is the command DBCC CHECKDB used for?

The command DBCC CHECKDB is used to check the physical and logical integrity of all the objects in the mentioned database. To do that, it performs the following operations:

Runs DBCC CHECKALLOC on the mentioned database.
On every table and view in the database, the DBCC CHECKTABLE command is executed.
Runs DBCC CHECKCATALOG on the database.
Then it validated the contents of every indexed view in the mentioned database.
It also validates link-level consistency between file system directories and table metadata while storing varbinary(max) data in the file system using FILESTREAM.
Finally, it validates the Service Broker data in the database.

So, you just have to execute the DBCC CHECKDB command, and automatically, DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands get executed.

Also, note that DBCC is supported on databases that contain memory-optimized tables but does not provide repair options. This implies that you must regularly back up databases and test those backups.

What do you understand by CHECK constraint in SQL Server?

The CHECK constraint in SQL Server is used to limit the values or type of data stored in a column. Once you apply the CHECK constraint on a single column, you can go forward and apply specific values for that particular column.

Example:

CREATE TABLE Customer (

Cust_ID int NOT NULL,

FirstName varchar(),

Age int,

City varchar(),

CONSTRAINT CHK_Customer CHECK (Age> AND City= 'Hyderabad')

);

What do you understand by COALESCE in SQL Server?

This function is used to return the first non-null expression within arguments. The COALESCE command is used to return a non-null value from more than a single column in arguments.

Example:

SELECT COALESCE(CustID, CustName, Amount) from Customers;

Explain the usage of FLOOR function in SQL Server.

The FLOOR function is used to round up a non-integer value to the previous least integer value. This function returns a unique value after rounding the digits.

Syntax:

FLOOR(expression)

Example:

FLOOR(.)

What is the command used to check locks in Microsoft SQL Server?

To check locks in the database, you can use the in-built stored procedure sp_lock.

Syntax

sp_lock [ [ @spid = ] 'session ID' ] [ , [@spid = ] 'session ID' ]

[ ; ]

Example:

To list all the locks currently held in an instance of the Database Engine, use the following command:

USE SampleDB;
GO
EXEC sp_lock;
GO

Mention the ways to get a count of the number of records in a table.

The following are three ways to count the number of records in the table:

SELECT * FROM TableName;
SELECT COUNT(*) FROM TableName;
SELECT rows FROM indexes WHERE id = OBJECT_ID(TableName) AND indexid< ;

What is the usage of the SIGN function?

This function is used to determine whether the mentioned number is zero, positive, and negative. So, it will either return , +, -.

Syntax:

SIGN(number)

Example:

SIGN ()  returns

SIGN ()  returns

SIGN (-)  returns -

Write an SQL Query to find first weekday of the month?

To find the first week day of the month, you can write a query as follows:

SELECT DATENAME(dw, DATEADD(dd, – DATEPART(dd, GETDATE()) + , GETDATE())) AS 
FirstDay;

Mention the command used to rename the database.

To rename a database, you have to use the sp_renamedb command in the following manner:

sp_renamedb 'OldDatabaseName', 'NewDatabaseName';

Write a query to find th highest amount paid from the Customer table.

To find the the highest amount paid from the customers’ table, you can write a query as below:

SELECT TOP amount FROM (SELECT DISTINCT TOP amount FROM customers ORDER BY amount 
DESC) ORDER BY amount;

How can we delete a table in SQL Server?

To delete a table in SQL Server, use the Delete command.

Syntax:

DELETE TableName

Example:

DELETE Customers;

What is the purpose of UPDATE STATISTICS and SCOPE_IDENTITY() function?

UPDATE _STATISTICS is used to update the information used by indexes such as the distribution of key values for one or more statistic groups in the mentioned indexed view or table.
SCOPE_IDENTITY is used to create identity value for tables in the current execution scope.

What do you understand by PHYSICAL_ONLY option in DBCC CHECKDB?

The PHYSICAL_ONLY option is used to limit check to the integrity of the physical structure of the record headers, page, and also the allocation consistency of the database.
The PHYSICAL_ONLY check is used to provide a small overhead check of the physical consistency of the database.
Also, the PHYSICAL_ONLY option causes a shorter run-time for DBCC CHECKDB on large databases. So, it is generally recommended for frequent use on production systems.

Can you explain how long are locks retained within the REPEATABLE_READ and SERIALIZABLE isolation levels, during a read operation with row-level locking?

With REPEATABLE_READ and SERIALIZABLE isolation levels, locks are held during the transaction. But, if you consider READ_COMMITTED, then locks are held for isolation level.

Mention the differences between HAVING and WHERE clause.

HAVING	WHERE
Used only with SELECT statement	Used in a GROUP BY clause
Used with the GROUP BY function in a query	Applied to each row before they are a part of the GROUP BY function in a query

Note: Whenever GROUP BY is not used, HAVING behaves like a WHERE clause.

What do you understand by integration services in SQL Server?

Integration services is a platform offered by Microsoft to build enterprise-level data transformation solutions and integration. These services solve complex business problems by loading data warehouses, perform data wrangling, copy or download files, and manage SQL Server objects.

Also, integration services can extract and transform data from a wide variety of sources such as relational data sources, XML data files, load the data into more than a single database. So, basically, you can use the integration services to create solutions without coding, code complex tasks, program the extensive integration object model to create packages.

The integration services include good set of built-in tasks and transformations, graphical tools used for building packages and also contain the Catalog database to store, run and manage packages.

What do you understand by Hotfixes and Patches in SQL Server?

Hotfixes are single, cumulative software packages applied to live systems. This includes one or more files used to address a problem in a software product. Patches are a programs installed on the machines to rectify the problem occurred in the system and ensured the security of the system. So, basically hotfixes are a kind of patch offered by Microsoft SQL Server to address specific issues.

Can you name a few encryption mechanisms in SQL server?

These are few encryption mechanisms in SQL Server to encrypt data in the database:

Transparent Data Encryption
Symmetric Keys
Asymmetric Keys
Transact SQL functions
Certificates

What are the options which must be set to allow the usage of optimistic models?

The READ_COMMITED_SNAPSHOT option and the ALLOW_SNAPSHOT_ISOLATION option must be set to allow the usage of optimistic models.

The READ_COMMITTED_SNAPSHOT option is used for the read committed optimistic model.
ALLOW_SNAPSHOT_ISOLATION option is used for the snapshot isolation level.

What are the common performance issues in SQL server?

The common performance issues in SQL Server are as follows:

Fragmentation
Input/Output bottlenecks
Blocking Queues
Deadlocks
Missing and unused indexes

So, this brings us to the end of the MS-SQL Server Interview Questions blog. This Tecklearn ‘Top MS-SQL Server Interview Questions and Answers’ helps you with commonly asked questions if you are looking out for a job in MS-SQL Server or Database Domain. If you wish to learn MS-SQL Server and build a career in Database domain, then check out our interactive, MS-SQL Server Training, that comes with 24*7 support to guide you throughout your learning period.

https://www.tecklearn.com/course/ms-sql-server-training-and-certification-course/

MS-SQL Server Training

About the Course

Microsoft SQL Server training course is an online classroom training meant for developers to master the descriptive language to work for relational databases. Important topics included in this SQL course are SQL introduction, relational databases, queries, subqueries, joins, and unions. After completing this course, you will also become proficient in the theory of optimization, transactions, indexing, installing SQL Server, database administration, clustering, implementing programmability objects, designing and implementing database objects, managing database concurrency, and optimizing SQL infrastructure and database objects.

Why Should you take MS-SQL Server Training?

The average salary for Sql server developer ranges from approximately $88,666 per year for Database Developer to $115,811 per year for Senior SQL Developer. – Indeed.com
Wells Fargo, UPS, US Bank, Perspecta, Silicon Valley Bank, Mindtree Wipro, Infosys & many other MNC’s worldwide use MS SQL Server for their Database deployments.
According to Gartner, MS SQL Server is the market leader in the Relational Database with 18.5% market share globally.

What you will Learn in this Course?

Introduction to SQL

Various types of databases
Introduction to SQL
Installation Steps of MS-SQL Server
Overview of Microsoft SQL Server Management Studio
SQL architecture, client/server relation
Database types

Introduction to relational databases, basic concepts of relational tables

Working with rows and columns
Various operators used like logical and relational
Constraints, Primary Key and Foreign Key
Insert, Update, Delete, Alter

Working with SQL: Join, Tables, and Variables

SQL Operators and Queries
SQL functions
Creation of Table
Retrieval of Data from tables
Combining rows from tables using joins
Operators such as intersect, except, union, temporary table creation, set operator rules, table variables etc

Deep Dive into SQL Functions

Data Retrieval Language
Functions
Operators
Clauses
Sub-Queries
Correlated Sub-Queries

SQL Views, Functions

Sub-queries
Views
Indexes

Managing Data with Transact-SQL

Transact-SQL queries
Implementing functions and aggregating data
Determining the results of DDL statements on supplied tables and data

Cursor Management and Triggers

Cursors
Triggers
Types of Triggers

Stored Procedures and User Defined functions

Understanding of Stored Procedures
Key benefits of Stored Procedures
Studying user-defined functions

Deep Dive into User-defined Functions

Detailed study of user-defined functions
Various types of UDFs like Scalar, Inline Table Value, multi-statement Table
What is Rank Function? Triggers, when to execute Triggers?

SQL Optimization and Performance

Concepts of Concurrency and Locking behaviour
Usage of memory-optimized tables to solve issues
Examining and Troubleshooting of query plans
Performance management of Database instances
SQL server Performance Monitoring

Got a question for us? Please mention it in the comments section and we will get back to you.

758