SQL Server mainly contains four System Databases (master, model, msdb, tempdb). Each of them is used by SQL Server for Separate purposes. From all the databases, the master database is the most important database.

Master Database:

Master Database contains information about SQL server configuration. Without a Master database, the server can’t be started. This will store the metadata information about all other objects (Databases, Stored Procedure, Tables, Views, etc.) which is created in the SQL Server.

It will contain the login information of users.

If the master database gets corrupted and is not recoverable from the backup, then a user has to again rebuild the master database. Therefore, it is always recommended to maintain a current backup of the master database. As everything crucial to the SQL server is stored in the master database, it cannot be deleted as it is the heart of SQL SERVER.

Model Database:

The model database sets a template for every database that was newly created. It serves as a template for the SQL server in order to create a new database. When we create a new database, the data present in the model database are moved to a new database to create its default objects which include tables, stored procedures, etc. Primarily, the requirement of the model database is not specific to the creation of a new database only. Whenever the SQL server starts, the Tempdb is created by using a model database in the form of a template. By default, it does not contain any data.

Msdb

The msdb database is used mainly by the SQL Server Management Studio, SQL Server Agent to store system activities like SQL server jobs, mail, service broker, maintenance plans, user and system database backup history, Replication information, log shipping. We need to take a backup of this database for the proper function of the SQL Server Agent Service.

TempDB

From the name of the database itself, we can identify the purpose of this database. It can be accessed by all the users in the SQL Server Instance. The tempdb is a temporary location for storing temporary tables(Global and Local) and a temporary stored procedure that holds intermediate results during the sorting or query processing and cursors. If more temporary objects are created and used storage of tempo DB then the performance of SQL Server will affect. So recommended moving the temdb to the location where a sufficient amount of space is there. This Database will be created by SQL Server instance when the SQL Server service starts. This database is created using the model database. We cannot take a backup of the temp Database.

~~-----------------------------------------------------------------------------------------------------------------------------~~

Mathematical Functions in SQL:

ABS(X)

This function returns the absolute value of X. For example −

Select abs(-6);

This returns 6.

MOD(X,Y)

The variable X is divided by Y and their remainder is returned. For example −

Select mod(9,5);

This returns 4.

SIGN(X)

This method returns 1 if X is positive, -1 if it is negative and 0 if the value of X is 0. For example −

Select sign(10);

This returns 1.

FLOOR(X)

This returns the largest integer value that is either less than X or equal to it. For example −

Select floor(5.7);

This returns 5.

CEIL(X)

This returns the smallest integer value that is either more than X or equal to it. For example −

Select ceil(5.7);

This returns 6.

POWER(X,Y)

This function returns the value of x raised to the power of Y For example −

Select power(2,5);

This returns 32.

ROUND(X)

This function returns the value of X rounded off to the whole integer that is nearest to it. For example −

Select round(5.7);

This returns 6.

SQRT(X)

This function returns the square root of X. For example −

Select sqrt(9);

This returns 3.

a~~-----------------------------------------------------------------------------------------------------------------------~~

Difference between functions and store procedures in SQL server?

User Defined Function	Stored Procedure
The function must return a value.	Stored Procedure may or not return values.
Will allow only Select statements, it will not allow us to use DML statements.	Can have select statements as well as DML statements such as insert, update, delete, and so on
It will allow only input parameters, doesn't support output parameters.	It can have both input and output parameters.
It will not allow us to use try-catch blocks.	For exception handling, we can use try-catch blocks.
Transactions are not allowed within functions.	Can use transactions within Stored Procedures.
We can use only table variables, it will not allow using temporary tables.	Can use both table variables as well as a temporary table in it.
Stored Procedures can't be called from a function.	Stored Procedures can call functions.
Functions can be called from a select statement.	Procedures can't be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.
A UDF can be used in the join clause as a result set.	Procedures can't be used in the Join clause
The function allows only SELECT statement in it.	The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement
A function can be.	Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT statements

The function can be used with select statement whereas sp is used with exec
The function can be used with simple statements whereas SP is used for performing business logics
Advantages of Stored Procedures:

Precompiled execution:
Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved, which executes (compiled code) whenever it is called.SQL Server compiles each Stored Procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when Stored Procedures are called repeatedly.

Reduced client/server traffic:
If network bandwidth is a concern in your environment then you'll be happy to learn that Stored Procedures can reduce long SQL queries to a single line that is transmitted over the wire.

Efficient reuse of code and programming abstraction:
Stored Procedures can be used by multiple users and client programs. If you utilize them in a planned manner then you'll find the development cycle requires less time.

Enhanced security controls:

You can grant users permission to execute a Stored Procedure independently of underlying table permissions.

Advantages of Functions:

A function is compiled and executed every time whenever it is called. A function must return a value and cannot modify the data received as parameters.

They allow modular programming:
You can create the function once, store it in the database, and call it any number of times in your program. User Defined Functions can be modified independently of the program source code.

They allow faster execution:
Similar to Stored Procedures, Transact-SQL User Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times. CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.

They can reduce network traffic:
An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.
~~----------------------------------------------------------------------------------------------------------------------------~~A function is a database object in SQL Server. Basically, it is a set of SQL statements that accept only input parameters, perform actions and return the result. The function can return only single value or a table. We can’t use a function to Insert, Update, Delete records in the database table(s).

Types of Function:

System Defined Function: These functions are defined by SQL Server for a different purpose. We have two types of system-defined functions in SQL Server

Scalar Function:

Scalar functions operate on a single value and return a single value. Below is the list of some useful SQL Server Scalar functions.

Scalar Function Description

abs(-10.67) This returns an absolute number of the given number means 10.67.

rand(10) This will generate a random number of 10 characters.

round(17.56719,3) This will round off the given number to 3 places of decimal means 17.567

upper('dot net) This will return the upper case of given string means 'DOTNET'

lower('DOTNET') This will returns the lower case of given string means 'dotnet'

ltrim(' dot net) This will remove the spaces from the left-hand side of 'dotnet' string.

convert(int, 15.56) This will convert the given float value to integer means 15.

Aggregate Function

Aggregate functions operate on a collection of values and return a single value. Below is the list of some useful SQL Server Aggregate functions.

Aggregate Function Description

max() This returns maximum value from a collection of values.

min() This returns the minimum value from a collection of values.

avg() This returns an average of all values in a collection.

count() This returns no of counts from a collection of values.

User-Defined Function

These functions are created by the user in the system database or in a user-defined database. We have three types of user-defined functions.

Scalar Function

The user-defined scalar function also returns a single value as a result of actions performed by the function. We return any datatype value from a function.

Inline Table-Valued Function

The user-defined inline table-valued function returns a table variable as a result of actions performed by the function. The value of the table variable should be derived from a single SELECT statement.

Multi-Statement Table-Valued Function

A user-defined multi-statement table-valued function returns a table variable as a result of actions performed by the function. In this, a table variable must be explicitly declared and defined whose value can be derived from multiple SQL statements.

Points to remember:

Unlike Stored Procedure, Function returns only a single value.

Unlike Stored Procedure, Function accepts only input parameters.

Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in a database table(s).

Like Stored Procedure, Function can be nested up to 32 levels.

A user-Defined Function can have up to 1023 input parameters while a Stored Procedure can have up to 2100 input parameters.

User-Defined Function can't return XML Data Type.

User-Defined Function doesn't support Exception handling.

User-Defined Function can call only Extended Stored Procedure.

User-Defined Function doesn't support set options like set ROWCOUNT etc.

~~------------------------------------~~ ~~------------------------------------~~ ~~-------------------------------------------------~~

Whether functions will return multiple values or not?

Generally, SQL Server functions will return only one parameter value if we want to return multiple values from function then we need to send multiple values in a table format by using table-valued functions.

CREATE FUNCTION test multiple values

(

@UserId INT

)

returns table as

return

(

Select * from UserInformation WHERE UserId=@userId

)

SELECT * FROM dbo.testmultiplevalues();

~~-----------------------------------------------------------------------------------------------------------------------------~~Return value or multiple values in SQL server SP.

CREATE PROCEDURE GetUserDetails

@UserName VARCHAR(50),

@Result INT OUTPUT

BEGIN

SELECT UserId FROM UserDetails WHERE UserName=@UserName

END
----------------------------------

DECLARE @UserId INT

EXEC GetUserDetails 'PrasanaK',@Result=@UserId OUTPUT

----------------------------------------

CREATE PROCEDURE GetMultipleUserDetails

@UserName VARCHAR(50),

@Id INT OUTPUT,

@lName VARCHAR(50) OUTPUT

BEGIN

SELECT UserId,LastName FROM UserDetails WHERE UserName=@UserName

END
--------------------------------

DECLARE @UserId INT,@LastName VARCHAR(50)

EXEC GetMultipleUserDetails 'PuthaK',@Id=@UserId OUTPUT, @lName= @LastName OUTPUT

~~-----------------------------------------------------------------------------------------------------------------------------~~
Indexes in SQL Server:

SQL Indexes are used in relational databases to quickly retrieve data. They are created on existing tables to retrieve rows quickly. They are created on a single column or group of columns when an index is created, it first sorts the data, and then it assigns rowid for each row.

SQL provides Create Index, Alter Index, and Drop Index commands that are used to create a new index, update an existing index, and delete an index in SQL Server.

By default an index is non-clustered.

Type of Indexes

SQL Server supports two types of indexes:

Clustered Index

Non-Clusterd Index.

1. Clustered Index

A clustered index sorts and stores the data rows of the table or view in order based on the index key. This type of index is implemented as a B-tree structure that supports fast retrieval of the rows, based on their key values. Since the data rows are stored in one direction, each table can only have a single clustered index. If a table doesn't have a clustered index, its rows are stored in a heap, which is an unordered structure.

Advantages:

The main benefit is speeding up query performance. Queries that contain the index key columns in the WHERE clause use the index structure to go straight to the table data.

Disadvantages:

There are a couple of disadvantages when it comes to clustered indexes. There is some overhead in maintaining the index structure with respect to any DML operation (INSERT, UPDATE, DELETE). This is especially true if you are updating the actual key values in the index as in this case all of the associated table data also has to be moved as it is stored in the leaf node of the index entry. In each case there will be some performance impact to your DML query.

Syntax : CREATE CLUSTERED INDEX CIX_Customers_CustomerID

ON dbo.Customers (CustomerID);

2. Non-clustered Index:

A non-clustered index is also maintained in a B-Tree data structure and the structure of non-clustered indexes is similar to the clustered index except that the actual data is not contained in the leaf nodes. A non-clustered index has the non-clustered index key values, and each key-value entry contains a reference to the actual data. Depending on how the table data is stored, it could point to a data value in the clustered index or a heap structure. If a row locator is a pointer to the row, it is a heap structure. If a row locator is the clustered index key, it is a clustered table.

You can create a maximum of 999 non-clustered indexes on a table, which is 254 up to SQL Server 2005.
Advantages:

The benefits of a non-clustered index are similar to that of the clustered index we mentioned above, the main benefit being speeding up query performance. There are however differences. The first is that you can have multiple non-clustered indexes defined on a single table. This allows you to index different columns which can help queries with different columns in the WHERE clause allowing you to fetch data faster and in the ORDER BY clause to eliminate a need for a sort
Disadvantages:

Similar to the clustered index the main disadvantage of a non-clustered index is the extra overhead required in maintaining the index during DML operations. It can sometimes be tricky to balance query performance as having too many non-clustered indexes on a table, while they will help all of your SELECT queries, can sometimes really slow down DML performance.
~~-----------------------------------------------------------------------------------------------------------------------------~~Simple index or Composite Index:

Based on the number of columns on which an index is created, indexes are classified into simple and composite indexes.
When indexes are created on single columns then it is called a simple index and when the index is created in combination with multiple columns then it's called a composite index.

~~-----------------------------------------------------------------------------------------------------------------------------~~

When should indexes be created?

When a column has a wide range of values

When the column does not have a large number of null values

When single or multiple columns used together in a where or join clause

When should indexes be avoided?

We can avoid an index in the following cases:

When a table is small

When the columns aren't used as a query condition

When the column is constantly updated

An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.
~~-----------------------------------------------------------------------------------------------------------------------------~~

Views in Sql server:

A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables. It does not hold any data and does not exist physically in the database. Similar to a SQL table, the view name should be unique in a database. It contains a set of predefined SQL queries to fetch data from the database.
Like a table, a view consists of a set of named columns and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database.
Views are virtual tables that are compiled at runtime. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view. A view can be made over one or more database tables. Generally, we put those columns in view that we need to retrieve/query again and again. Once you have created the view, you can query view like a table. We can make an index, trigger on the view.

Use of Views:

Views are used to implement the security mechanism in SQL Server. Views are generally used to restrict the user from viewing certain columns and rows. Views display only the data specified in the query, so it shows only the data that is returned by the query defined during the creation of the view.

Different types of views:

System Defined Views
System-defined Views are predefined Views that already exist in the Master database of SQL Server. These are also used as template Views for all newly created databases. These system Views will be automatically attached to any user-defined database.

Information Schema

In SQL Server we have twenty different schema views. These are used to display information of a database, like as tables and columns. This type of view starts with INFORMATION_SCHEMA and after this view name.

Catalog View.

Catalog Views were introduced with SQL Server 2005. These are used to show database self-describing information.

select * from sys. tables

Dynamic Management View.

Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information of the database about the current state of the SQL Server machine. These values help the administrator to analyze problems and tune the server for optimal performance. These are of two types

Server-scoped Dynamic Management View

These are stored only in the Master database.

Database-scoped Dynamic Management View

These are stored in each database.

--To see all SQL Server connections

SELECT connection_id,session_id,client_net_address,auth_scheme

FROM sys.dm_exec_connections

User-Defined Views.: These types of view are defined by users. We have two types of user-defined views.

Simple View. & Complex View.

~~-----------------------------------------------------------------------------------------------------------------------------~~

Simple View	Complex View
Contains only one single base table or is created from only one table.	Contains more than one base tables or is created from more than one tables.
We cannot use group functions like MAX(), COUNT(), etc.	We can use group functions.
Does not contain groups of data.	It can contain groups of data.
DML operations could be performed through a simple view.	DML operations could not always be performed through a complex view.
INSERT, DELETE and UPDATE are directly possible on a simple view.	We cannot apply INSERT, DELETE and UPDATE on complex view directly.
Simple view does not contain group by, distinct, pseudocolumn like rownum, columns defiend by expressions.	It can contain group by, distinct, pseudocolumn like rownum, columns defiend by expressions.
Does not include NOT NULL columns from base tables.	NOT NULL columns that are not selected by simple view can be included in complex view.

~~-----------------------------------------------------------------------------------------------------------------------------~~

View vs MaterializedViews:

A View is never stored it is only displayed whereas A Materialized View is stored on the disk.

View is the virtual table formed from one or more base tables or views whereasMaterialized view is a physical copy of the base table.

View is updated each time the virtual table (View) is used whereasMaterialized View has to be updated manually or using triggers.

Slow processing whereas Fast processing.

View do not require memory space whereasMaterialized View utilizes memory space.

Create View V As

Create Materialized View V Build [clause] Refresh [clause] On [Trigger] As
~~----------------------------------------------------------------------------------------------------------------------------~~

Stored Procedure:

It is a group of SQL statements that have been created and stored in DB

· It is a saved collection of transaction SQL statements

· It will accept input parameters so that a single procedure can be used over the network by several clients using different input data

· SP will reduce network traffic and increase performance

· If we modify the sp, all the clients will get the updated SP

It allows faster execution and can reduce network traffic.
~~------------------------------------------------------------------------------------------------------------------------~~Normalization:

It is a process of breaking a big table into 2 or more smaller tables. Through normalization, we can achieve consistency.
Normalization is the method used in a database to reduce the data redundancy and data inconsistency from the table. It is the technique in which Non-redundancy and consistency data are stored in the set schema. By using normalization the number of tables is increased instead of decreased.

De-Normalization:
It is the reverse process of Normalization.If we combine 2 or more tables into a single table known as de-normalization.It increases performance.Searching the data from one table is quite faster than searching data from multiple tables.

Denormalization is also the method which is used in a database. It is used to add the redundancy to execute the query quickly. It is a technique in which data are combined to execute the query quickly. By using denormalization the number of tables is decreased which oppose to the normalization.
~~----------------------------------------------------------------------------------------------------------------------------~~

Q)Normalization and its Types:

Normalization or database normalization is a process to organize the data into database tables. To make a good database design, you have to follow Normalization practices. Without normalization, a database system might be slow, inefficient and might not produce the expected result. Normalization reduces data redundancy and inconsistent data dependency.

To make a good database design, you must know the normal forms and their best uses.

1NF (First Normal Form) Rules:

Each table cell should contain a single value.
Each record needs to be unique.
There must be no duplicate records & repeated groups.
Data is stored in tables with rows that can be uniquely identified by a Primary Key.

2NF (Second Normal Form) Rules:
Remove the subset of data and place in a separate table. Single Column Primary Key that does not functionally dependent on any subset of candidate key relation. . Only those data that relates to a table’s primary key is stored in each table.

Third Normal Form (3NF):

Non-Primary key columns shouldn’t depend on the other non-Primary key columns
There is no transitive functional dependency
Remove columns that are not dependent on key

Boyce Code Normal Form (BCNF):

A database table is said to be in BCNF if it is in 3NF and contains each and every determinant as a candidate key. The process of converting the table into BCNF is as follows:

· Remove the nontrivial functional dependency.

· Make a separate table for the determinants.

Fourth Normal Form (4NF)

A database table is said to be in 4NF if it is in BCNF and primary key has a one-to-one relationship to all non-keys fields or We can also say a table to be in 4NF if it is in BCNF and contains no multi-valued dependencies. The process of converting the table into 4NF is as follows:

· Remove the multivalued dependency.

· Make a separate table for multivalued Fields.

· No table may contain 1 or more 1:n or n:m relationship that are not directly related.

Fifth Normal Form (5NF):

A database table is said to be in 5NF if it is in 4NF and contains no redundant values or we can also say a table to be in 5NF if it is in 4NF and contains no join dependencies. The process of converting the table into 5NF is as follows:

· Remove the join dependency.

· Break the database table into smaller and smaller tables to remove all data redundancy. ~~------------------------------------------------------------------------------------------------------------------------------~~

Constraints in sql server:

Constraints in SQL Server are rules and restrictions applied on a column or a table such that unwanted data can't be inserted into tables. This ensures the accuracy and reliability of the data in the database. We can create constraints on single or multiple columns of any table. Constraints maintain the data integrity and accuracy in the table.

Constraints can be classified into the following two types.

Column TypesConstraints
Table Types Constraints

SQL Server contains the following 6 types ofconstraints:

Not Null Constraint

Check Constraint

Default Constraint

Unique Constraint

Primary Constraint

Foreign Constraint

Not Null Constraint:

A Not null constraint restricts the insertion of null values into a column. If we are using a Not Null Constraint for a column then we cannot ignore the value of this column during an insert of data into the table.

Check Constraint:

A Check constraint checks for a specific condition before inserting data into a table. If the data passes all the Check constraints then the data will be inserted into the table otherwise the data for insertion will be discarded. The CHECK constraint ensures that all values in a column satisfies certain conditions.

Default Constraint:

Specifies a default value for when a value is not specified for this column. If in an insertion query any value is not specified for this column then the default value will be inserted into the column.
Unique Constraint:
It ensures that each row for a column must have a unique value. It is like a Primary key but it can accept only one null value. In a table one or more column can contain a Unique Constraint.

Primary Key Constraint:

A Primary key uniquely identifies each row in a table. It cannot accept null and duplicate data. One or more of the columns of a table can contain a Primary key.

Foreign Key Constraint:

A Foreign Key is a field in a database table that is a Primary key in another table. A Foreign key creates a relation between two tables. The first table contains a primary key and the second table contains a foreign key.
~~-----------------------------------------------------------------------------------------------------------------------------~~

Triggers in sql server with example:

It is a special kind of Stored Procedure that executes in response to certain action like DML.

A SQL Server trigger is a piece of procedural code, like a stored procedure which is only executed when a given event happens. There are different types of events that can fire a trigger. Just to name you a few, the insertion of rows in a table, a change in a table structure and even a user logging into a SQL Server instance.

There are three main characteristics that make triggers different than stored procedures:

Triggers cannot be manually executed by the user.
There is no chance for triggers to receive parameters.
You cannot commit or rollback a transaction inside a trigger.

There are two classes of triggers in SQL Server:

DDL (Data Definition Language) triggers. This class of triggers fires upon events that change the structure (like creating, modifying or dropping a table), or in certain server related events like security changes or statistics update events.
DML (Data Modification Language) triggers. This is the most used class of triggers. In this case the firing event is a data modification statement; it could be an insert, update or delete statement either on a table or a view.

After trigger (using FOR/AFTER CLAUSE)

The After trigger (using the FOR/AFTER CLAUSE) fires after SQL Server finishes the execution of the action successfully that fired it.

Instead of Trigger (using INSTEAD OF CLAUSE)

The Instead of Trigger (using the INSTEAD OF CLAUSE) fires before SQL Server starts the execution of the action that fired it. This is different from the AFTER trigger that fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.

Logon Triggers

Logon triggers are a special type of trigger that fires when a LOGON event of SQL Server is raised. This event is raised when a user session is being established with SQL Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger, such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.In short, the following are the various types of triggers.

DML:

Instead of Trigger: An Instead of trigger is fired instead of the triggering action such as an insert, update, or delete
After Trigger: An After trigger executes following the triggering action, such as an insert, update or delete

DDL Trigger

This type of trigger is fired against DDL statements like Drop Table, Create Table or Alter Table. DDL Triggers are always After Triggers.

Logon trigger

This type of trigger is fired against a LOGON event before a user session is established to the SQL Server.

Additionally, DML triggers have different types:

FOR or AFTER [INSERT, UPDATE, DELETE]: These types of triggers are executed after the firing statement ends (either an insert, update or delete).
INSTEAD OF [INSERT, UPDATE, DELETE]: Contrary to the FOR (AFTER) type, the INSTEAD OF triggers executes instead of the firing statement. In other words, this type of trigger replaces the firing statement. This is very useful in cases where you need to have cross-database referential integrity.

~~-----------------------------------------------------------------------------------------------------------------------------~~

Cursor in Sql server with example:

It is a set of rows together with a pointer that identifies a current row. Cursor is a database object to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use a cursor when we need to update records in a database table in singleton fashion means row by row.

Declare Cursor

A cursor is declared by defining the SQL statement that returns a result set.

Syntax:

DECLARE cursor_name CURSOR

[LOCAL | GLOBAL] --define cursor scope

[FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks

FOR select_statement --define SQL Select statement

FOR UPDATE [col1,col2,...coln] --define columns that need to be updated

Open

A Cursor is opened and populated by executing the SQL statement defined by the cursor.

Syntax:

OPEN [GLOBAL] cursor_name --by default it is local

Fetch

When the cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

Syntax:

FROM [GLOBAL] cursor_name

INTO @Variable_name[1,2,..n]

Close

After data manipulation, we should close the cursor explicitly.

Syntax:

CLOSE cursor_name --after closing it can be reopened

Deallocate

Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Syntax:

DEALLOCATE cursor_name --after deallocation it can't be reopened

~~-----------------------------------------------------------------------------------------------------------------------------~~

Why use a SQL Cursor?

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors. In programming, we use a loop like FOR or WHILE to iterate through one item at a time, the cursor follows the same approach and might be preferred because it follows the same logic.

Cursor Scope

Microsoft SQL Server supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to define the scope of the cursor name.

GLOBAL - specifies that the cursor name is global to the connection.

LOCAL - specifies that the cursor name is local to the Stored Procedure, trigger, or query that holds the cursor.

Data Fetch Option in Cursors

Microsoft SQL Server supports the following two fetch options for data:

FORWARD_ONLY - Specifies that the cursor can only be scrolled from the first to the last row.

SCROLL - It provides 6 options to fetch the data (FIRST, LAST, PRIOR, NEXT, RELATIVE, and ABSOLUTE).

Types of cursors

Microsoft SQL Server supports the following 4 types of cursors.

STATIC CURSOR

A static cursor populates the result set during cursor creation and the query result is cached for the lifetime of the cursor. A static cursor can move forward and backward.

FAST_FORWARD

This is the default type of cursor. It is identical to the static except that you can only scroll forward.

DYNAMIC

In a dynamic cursor, additions and deletions are visible for others in the data source while the cursor is open.

KEYSET

This is similar to a dynamic cursor except we can't see records others add. If another user deletes a record, it is inaccessible from our record set.

Microsoft SQL Server supports the following three types of Locks.

READ-ONLY

Specifies that the cursor cannot be updated.

SCROLL_LOCKS

Provides data integrity into the cursor. It specifies that the cursor will lock the rows as they are read into the cursor to ensure that updates or deletes made using the cursor will succeed.

OPTIMISTIC

Specifies that the cursor does not lock rows as they are read into the cursor. So, the updates or deletes made using the cursor will not succeed if the row has been updated outside the cursor.

Advantages

Cursors can be faster than a while loop but they do have more overhead.
It is we can do RowWise validation or in another way, you can perform an operation on each Row. It is a Data Type that is used to define a multi-value variables.
Cursors can be faster than a while loop but at the cost of more overhead.

Disadvantages

It consumes more resources because use-cursor-in-sql occupies memory from system that may be available for other processes.
Each time when a row is fetched from the use-cursor-in-sql and it may result in a network round trip. This uses much more network bandwidth than the execution of a single SQL statement like SELECT or DELETE, etc that makes only one round trip.

Cursor Alternative:

· Using Table Variable

· Using Temporary Table

· SQL While loop

We can also use temporary tables instead of SQL cursors to iterate the result set one row at a time. Temporary tables have been in use for a long time and provide an excellent way to replace cursors for large data sets

While SQL While loop is quicker than a cursor, reason found that cursor is defined by DECLARE CURSOR. Every emphasis of the loop will be executed inside system memory and consuming required server assets.

~~-----------------------------------------------------------------------------------------------------------------------------~~

Difference between primary key and unique key in SQL server

The primary key will not accept NULL values whereas the unique key can accept NULL values. A table can have only a primary key whereas there can be multiple unique keys on a table. A Clustered index is automatically created when a primary key is defined whereas a unique key generates the non-clustered index. The primary key does not allow null columns whereas unique allows null columns. In the primary key, duplicate keys are not allowed while in a unique key, if one or more key parts are null, then duplicate keys are allowed. The purpose of the primary key is to enforce entity integrity on the other hand the purpose of the unique key is to enforce unique data.
~~-----------------------------------------------------------------------------------------------------------------------------~~Difference between where and having and group by in sql server

Having	GroupBy
It is used for applying some extra conditions to the query.	The group by clause is used to group the data according to a particular column or row.
Having cannot be used without group by clause.	groupby can be used without having a clause with the select statement.
The having clause can contain aggregate functions.	It cannot contain aggregate functions
It restricts the query output by using some conditions	It groups the output on basis of some rows or columns.
SELECT COUNT (SALARIES) AS COUNT_SALARIES, EMPLOYEES FROM EMPLOYEES GROUP BY SALARIES HAVING COUNT(SALARIES) > 1;	SELECT COUNT (SALARIES) AS COUNT_SALARIES, EMPLOYEES FROM EMPLOYEES GROUP BY SALARIES;

Conclusion:

1. WHERE is used to filter records before any groupings take place that is on single rows.

2. GROUP BY aggregates/ groups the rows and returns the summary for each group.

3. HAVING is used to filter values after they have been groups.

~~-----------------------------------------------------------------------------------------------------------------------------~~

SQL Commands

· SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.

· SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.

Types of SQL Commands:

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

Data Definition Language (DDL)

DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.

All the command of DDL are auto-committed that means it permanently save all the changes in the database.Here are some commands that come under DDL:

CREATE
ALTER
DROP
TRUNCATE

CREATE: It is used to create a new table in the database.

DROP : It is used to delete both the structure and record stored in the table.

ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

2. Data Manipulation Language

DML commands are used to modify the database. It is responsible for all form of changes in the database.The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback. Here are some commands that come under DML:

INSERT
UPDATE
DELETE

3. Data Control Language:

DCL commands are used to grant and take back authority from any database user. Here are some commands that come under DCL:

Grant
Revoke

a. Grant: It is used to give user access privileges to a database.

b. Revoke: It is used to take back permissions from the user.

4. Transaction Control Language:

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

COMMIT
ROLLBACK
SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the database.

b. Rollback: Rollback command is used to undo transactions that have not already been saved to the database.

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

SELECT

a. SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by the WHERE clause.

~~----------------------------------------------------------------------------------------------------------------------------~~

Local temporary table	Global temporary table
Denoted by # symbol.	Denoted by ## symbol.
Tables are visible only in the current session	Tables are visible to all sessions.
Cannot be shared between multiple users.	Can be shared between multiple users.
Local temporary tables are deleted after the user disconnects from the instance of SQL Server.	Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
SELECT * FROM #temp	SELECT * FROM ##temp
CREATE PROCEDURE sp_create_tempt AS CREATE TABLE #temp1 (c1 INT); CREATE PROCEDURE sp_use_tempt AS BEGIN EXEC sp_create_tempt SELECT * FROM ##temp1 END EXEC sp_use_tempt	CREATE TABLE ##temp1 (c1 INT); INSERT INTO ##temp1 VALUES (1); SELECT * FROM ##temp1

~~----------------------------------------------------~~ ~~----------------------------------------------------~~ ~~-----------------~~

Difference between Union and Union All in sql server

1. Union :

Union means joining two or more data sets into a single set. In SQL Server, Union is used to combine two queries into a single result set using the select statements. Union extracts all the rows that are described in the query.

Syntax –

query1 UNION query2

2. Union All :

A union is used for extracting rows using the conditions specified in the query while Union All is used for extracting all the rows from a set of two tables.

Syntax –

query1 UNION ALL query2

The same conditions are applicable to Union All. The only difference between Union and Union All is that Union extracts the rows that are being specified in the query while Union All extracts all the rows including the duplicates (repeated values) from both the queries.
~~-----------------------------------------------------------------------------------------------------------------------------~~

Difference between temp table and table variable and cte in Sql server

CTE stands for Common Table Expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike the temporary table, its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with a semicolon.

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional

(

SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr

INNER JOIN EMP Emp ON Emp.EID = Addr.EID

)

SELECT * FROM CTE1 --Using CTE

WHERE CTE1.Age > 50

ORDER BY CTE1.NAME

When to use CTE?

This is used to store the result of a complex sub query for further use. This is also used to create a recursive query.

Temporary Tables

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside the Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

Local Temp Table

Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. The local temporary table name is stared with a single hash ("#") sign. The scope of Local temp table exists to the current session of the current user means to the current

query window. If you will close the current query window or open a new query window and will try to find above-created temp table, it will give you the error.

Global Temp Table

Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. The global temporary table name is stared with double hash ("##") sign. Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of the batch. This is also created in the tempdb database but not the memory. This also allows you to create a primary key, identity at the time of Table variable declaration but not non-clustered index.

Note:

Temp Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, an index like normal tables.

CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than the Tempdb database. You cannot create an index on CTE.

Table Variable acts like a variable and exists for a particular batch of query execution. It gets

dropped once it comes out of a batch. This is also created in the tempdb database but not the memory.
~~-----------------------------------------------------------------------------------------------------------------------------~~

Magic table in SQL server:

Magic Tables are invisible tables or virtual tables. You can see them only with the help of Triggers in SQL Server. Magic Tables are those tables that allow you to hold inserted, deleted and updated values during insert, delete and update DML operations on a table in SQL Server.

Magic Tables in SQL Server

These are the two Magic Tables:

Inserted
Deleted

Use with Triggers

If you have implemented a trigger for a table then:

Whenever you Insert a record on that table, that record will be shown in the INSERTED Magic Table.
Whenever you Delete the record on that table, that record will be shown in the DELETED Magic Table Only.
Whenever you Update the record on that table, that existing record will be shown in the DELETED Magic Table and Updated new data will be shown in the INSERTED Magic Table.

Inserted Virtual Table:

The Inserted table holds the recently inserted values. Hence that record will be shown in INSERTED Magic Table.

Create TRIGGER Trigger_ForInsertmagic

ON [UserDetails]

FOR INSERT

begin

SELECT * FROM INSERTED

end

Deleted Virtual Table:

The Deleted table holds the recently deleted values. Hence that record will be shown in the DELETED Magic Table.

Create TRIGGER Trigger_Fordeletemagic

ON [UserDetails]

FOR DELETE

begin

SELECT * FROM Deleted

End

~~----------------------------------------------------------------------------------------------------------------------------------------------------------------------~~

Different Types of SQL Joins

Types of SQL Joins

Inner Join:

Inner join returns only those records/rows that match/exists in both the tables. Syntax for Inner Join is as

Select * from table_1 as t1

inner join table_2 as t2

on t1.IDcol=t2.IDcol

Outer Join:

We have three types of Outer Join.

Left Outer Join:

Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values. Syntax for Left outer Join is as :

Select * from table_1 as t1

left outer join table_2 as t2

on t1.IDcol=t2.IDcol

Right Outer Join:

Right outer join returns all records/rows from right table and from left table returns only matched records. If there are no columns matching in the left table, it returns NULL values. Syntax for right outer Join is as :

Select * from table_1 as t1

right outer join table_2 as t2

on t1.IDcol=t2.IDcol

Full Outer Join:

Full outer join combines left outer join and right outer join. This join returns all records/rows from both the tables.If there are no columns matching in the both tables, it returns NULL values. Syntax for full outer Join is as :

Select * from table_1 as t1

full outer join table_2 as t2

on t1.IDcol=t2.IDcol

Cross Join:

Cross join is a cartesian join means the cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. Syntax for right outer Join is as :

Select * from table_1

cross join table_2

Self Join:

Self-join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically, we have only three types of joins: Inner join, Outer join, and Cross join. We use any of these three JOINS to join a table to itself. Hence Self join is not a type of Sql join.
~~----------------------------------------------------------------------------------------------------------------------------~~

Char, Nchar, Varchar and Nvarchar Data Types in SQL Server

CHAR data type:

It is a fixed-length data type

Used to store non-Unicode characters

Occupiers 1 byte of space for each character

VARCHAR data type:

It is a variable-length data type

Used to store non-Unicode characters

Occupies 1 byte of space for each character

NCHAR data type:

Is a fixed-length data type

Used to store Unicode characters (for example the languages Arabic, German and so on)

Occupies 2 bytes of space for each character

NVARCHAR data type:

It is a variable-length data type

Used to store Unicode characters

Occupies 2 bytes of space for each character

When to use what?

If your column will store a fixed-length Unicode character like French, Arabic and so on characters then go for NCHAR. If the data stored in a column is Unicode and can vary in length, then go for NVARCHAR.

Querying to NCHAR or NVARCHAR is a bit slower then CHAR or VARCHAR. So don't go for NCHAR or NVARCHAR to store non-Unicode characters even though this data type supports that.
~~-----------------------------------------------------------------------------------------------------------------------------~~

What is Lock in SQL Server?

As we all know, multiple users need to access databases concurrently. So locks come into the picture to prevent data from being corrupted or invalidated when multiple users try to do operations such as read, write and update on database.

“Lock is defined as a mechanism to ensure data integrity, consistency while allowing concurrent access to data. It is used to implement concurrency control when multiple users access Database to manipulate its data at the same time”

RID(ROW ID): Used to lock a single row within a table.

Table: Complete table, including all data and indexes.

Key: Row lock within an index. It means primary key, Candidate Key, Secondary key etc.

Page: 8-kilobyte (KB) data page or index page. Lock can be placed on Page Level also, it means if a particular page is locked so another user cannot update data on it.

Extent: Contiguous group of eight data pages which can include index pages also.

Database: Entire Database can be locked for some type of users who have read permission on database.

Different Models of SQL Server locks

Shared(S)

Used for select operations

Enable other sessions to perform select operations but prevent updates

read-only operations

Operation with SELECT statement generally use in Shared mode.

Exclusive(X)

Used for DML operations

Prevents other users from accessing the resource.

Operations, such as INSERT, UPDATE, or DELETE means DML query. Ensures that multiple updates cannot be made to the same resource at the same time.

Update(U)

The preliminary stage for an exclusive lock. Used by the server when filtering the records to be modified

Prevents other update locks

A solution to the cycle deadlock problem

Intent

Intent Locks are used for establishing a lock Hierarchy.

The types of intent locks are:

intent shared (IS),

intent exclusive (IX)

shared with intent exclusive (SIX).

Schema

Schema locks are used when an operation dependent on the schema of a table is executing.

The types of schema locks are:

Schema modification (Sch-M) and

Schema stability (Sch-S).

Bulk Update (BU)

Bulk Update is used when bulk-copying data into a table and the TABLOCK hint is specified. Generally, use when users want to insert huge data in database/

Examples of Locks in SQL Server

Shared lock: select balance from tbl_account where acct_number = 25

--shared lock

We can perform multiple select statements on the same table.

Exclusive lock

insert tbl_account values(34, 500)

When we perform insert query in the table then page lock in Exclusive mode. Until recorded it's not inserted in table n other operation perform here. Similarly, delete, Update operation occurs.

delete tbl_account where balance < 0

update tbl_account set balance = 0 where acct_number = 25

~~----------------------------------------------------------------------------------------------------------------------------~~What is a SQL Server deadlock?

SQL Server is a highly transactional database. For example, suppose you are supporting the database for an online shopping portal where you receive new orders from customers around the clock. Multiple users are likely performing the same activity at the same time. In this case, your database should follow the Atomicity, Consistency, Isolation, Durability (ACID) properties in order to be consistent, reliable, and protect data integrity.

To follow the ACID properties, SQL Server uses locking mechanisms, constraints, and write-ahead logging. Various lock types include: exclusive lock(X), shared lock(S), update lock (U), intent lock (I), schema lock (SCH), and bulk update lock (BU). These locks can be acquired on the key, table, row, page, and database level.

A-> Atomicity-All or no transactions are committed

C-> Consistency - It should save only valid data

I-> Isolation - Transactions must be isolated from each other

D-> Durability - Completed transactions are saved permanently

SET DEADLOCK_PRIORITY

It uses the following arguments:

By default, SQL Server marks the transaction with the least expensive rollback as a deadlock victim. Users can set the deadlock priority in a transaction using the DEADLOCK_PRIORITY statement.

Low: It is equivalent to deadlock priority -5

Normal: It is the default deadlock priority 0

High: It is the highest deadlock priority 5.

We can also set numeric values for the deadlock priority from -10 to 10 (total 21 values).

You can capture SQL Server deadlock information in the following ways:

SQL Server profiler

SQL Server extended events

SQL Server error logs

Default traces in SQL Server

5 Types of SQL Server deadlocks

1) Bookmark lookup deadlock

2) Range scan deadlock

3) Cascading constraint deadlock

4) Intra-query parallelism deadlock

5) Reverse object order deadlock

Useful ways to avoid and minimize SQL Server deadlocks:

· Try to keep transactions short; this will avoid holding locks in a transaction for a long period of time.

· Access objects in a similar logical manner in multiple transactions.

· Create a covering index to reduce the possibility of a deadlock.

· Create indexes to match the foreign key columns. This way, you can eliminate deadlocks due to cascading referential integrity.

· Set deadlock priorities using the SET DEADLOCK_PRIORITY session variable. If you set the deadlock priority, SQL Server kills the session with the lowest deadlock priority.

· Utilize the error handling using the try-catch blocks. You can trap the deadlock error and rerun the transaction in the event of a deadlock victim.

· Change the isolation level to the READ COMMITTED SNAPSHOT ISOLATION or SNAPSHOT ISOLATION. This changes the SQL Server locking mechanism. Although, you should be careful in changing the isolation level, as it might impact other queries negatively.

· Ensure the database design is properly normalized.

· Develop applications to access server objects in the same order each time.

· Do not allow any user input during transactions.

· Avoid cursors.

· Reduce the number of round trips between your application and SQL Server by using stored procedures or by keeping transactions within a single batch.

· Reduce the number of reads. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.

· Reduce lock time. Develop applications that obtain locks at the latest possible time, and release them at the earliest possible time.

· If appropriate, reduce lock escalation by using ROWLOCK or PAGLOCK.

· If the data being locked is not modified very frequently, consider using NOLOCK to prevent locking.
~~-----------------------------------------------------------------------------------------------------------------------------~~

Difference between delete/ drop/ truncate in sql server?

Delete	Drop
It removes some or all the tuples from a table.	It removes the entire schema, table, domain, or constraints from the database.
Data Manipulation Language command	Data Definition Language command.
WHERE clause is mainly used along with the DELETE command.	No clause is required along with the DROP command.
Actions performed by DELETE can be rolled back as it uses buffer.	Actions performed by DROP can’t be rolled back because it directly works on actual data.
space occupied by the table in the memory is not freed even if you delete all the tuples of the table using DELETE	It frees the tablespace from memory
Shortage of memory	Memory fragmentation
Fixed-size	Resizing is possible
DELETE FROM relation name WHERE condition;	DROP SCHEMA name RESTRICT; DROP Table table name CASCADE;
SQL is used to write queries, create and execute DDL and DML statements	PL/SQL is used to write program blocks, functions, procedures, triggers and packages.

Delete Statement:

DELETE is a DML (Data Manipulation Language) command and is used to delete one or more rows (records) from a table (relation). DELETE is used only to remove data from the table, not to remove a table from the database.

If you want to delete only a few rows and not the table structure, use the DELETE command. It can delete some or all rows (records) from the table (relation). After execution, the DELETE statement returns the total number of rows removed from the table.

Error Handling for DELETE Statement:

By defining a statement or query in a TRY and CATCH construct, the user can implement error handling for the DELETE statement.

If you try to delete a row that is referenced by data in another table with a FOREIGN KEY constraint, the DELETE statement may fail. The statement is canceled, an error has occurred, and no rows are deleted if DELETE removes multiple rows and any of the deleted rows violate the constraint.

Key Points:

DELETE can be executed by triggers.

DELETE doesn't reset auto-increment.

To use the DELETE statement, the user must have DELETE permission for that table.

During execution, the DELETE statement uses a row lock, which locks each row in the table with each row deletion.

The DELETE statement deletes rows one by one and records each removed row in the transaction log.

DELETE statement maintains the log, so it can be rolled back in SQL Server.

The DELETE statement returns the number of rows (records) that were deleted by its execution.

DELETE is slower than TRUNCATE.

TRUNCATE:

TRUNCATE is a DDL (Data Definition Language) command that is used to delete all data from a table (relation) without removing the table structure. TRUNCATE is used only to remove data from the table, not to remove a table from the database. This is almost similar to the DELETE statement because it does not have a WHERE clause. TRUNCATE is faster than the DELETE query.

TRUNCATE TABLE Customers;

Key points:

WHERE Clause cannot be used with TRUNCATE.

TRUNCATE resets auto-increment.

To use the TRUNCATE, you need permission to use the TRUNCATE TABLE.

TRUNCATE removes all records from a table by using a table lock.

TRUNCATE keeps the minimum log space in the transaction log, so it can also be rolled back in SQL Server.

In SQL Server, TRUNCATE operation can be rolled back.

TRUNCATE does not return the number of deleted rows (records) from the table as a result.

TRUNCATE is faster than DELETE.

Note

Users cannot use the TRUNCATE TABLE command on tables that are referenced by the FOREIGN KEY constraint. But, the user can truncate a table that has a foreign key that references itself.

DROP Statement:

DROP TABLE is also a DDL (Data Definition Language) command. It is used to remove data stored in a table as well as a table structure from a database. The DROP TABLE statement removes the table's structure, data, indexes, constraints, and triggers. When a table is dropped, any constraints or triggers associated with it, are also dropped.

A table that is referenced by a FOREIGN KEY constraint cannot be dropped using the DROP TABLE statement. The referencing FOREIGN KEY constraint or the referencing table must be dropped first. The referencing table must be specified first if both the referencing table and the primary key holding table are being dropped in the same DROP TABLE statement.

Key Points:

DROP Statement is also a DDL Command.

In SQL Server, DROP table operation can be rolled back.

The DROP statement frees up memory space (table space from the memory).

Syntax:

DROP TABLE <table_name>;

Note :

Multiple tables can be dropped in any database using a comma-separated list.

.No.	Key Points	DELETE	TRUNCATE	DROP
1	Classification	DML (Data Manipulation Language)	DDL (Data Definition Language)	DDL (Data Definition Language)
2	Use to delete	one or more rows	all the rows	all the data is stored in a table along with its structure
3	WHERE Condition?	Yes	No	No
4	Uses a lock	Row Lock	Table Lock	Table Lock
5	Write Transaction log	for each row	for the whole table	for the whole table
6	Should be Rollback?	Yes	Yes	Yes
7	Does it reset the auto-increment?	No	Yes	---
8	Speed	Slower than TRUNCATE	Faster than DROP	Quick to perform (faster)

~~---------------------------------------------------------------------------------------------------------------------------------~~What is a relationship and what are they?

Database Relationship is defined as the connection between the tables in a database. There are various databasing relationships, and they are as follows:.

One to One Relationship.
One to Many Relationship.
Many to One Relationship.
Self-Referencing Relationship.

~~----------------------------------------~~~~----------------------------------------~~~~------------------------------------------~~

What is subquery?
A subquery is a query within another query. The outer query is called the main query, and the inner query is called the subquery. Sub Query is always executed first, and the result of the subquery is passed on to the main query.

Types of SQL Subqueries

1. Single Row Subquery - Returns zero or one row in results.

2. Multiple Row Subquery - Returns one or more rows in results.

3. Multiple Column Sub queries - Returns one or more columns

4. Correlated Subqueries - Returns one or more columns according to the main or the outer query, thus called a correlated subquery.

5. Nested Subqueries - We have queries within a query (inner and outer query).

~~--------------------------------~~~~--------------------------------~~~~--------------------------------~~~~-----------------------------~~Collation in SQL server

Collation is a set of rules that tell the database engine how to compare and sort the character data in SQL Server. Collation can be set at different levels in SQL Server. Below are the three levels:

· SQL Server level

· Database level

· Column level

SQL Server level

Collation setting at SQL Server level can be specified while installing SQL Server

The collation of the SQL Server is set to SQL_Latin1_General_CP1_CI_AS.

Break down of the collation setting is as below:

SQL – All SQL Server collations will have the prefix SQL

Latin1_General – represents the sort rule, CI means case insensitive and AS means accent sensitive

Execute the following T-SQL script to know the collation of the SQL Server instance:

SELECT SERVERPROPERTY('collation')

Database level

By default, all system and user databases will inherit the collation setting that was specified at the SQL Server level during installation.

Here the collation of SQL Server instance is SQL_Latin1_General_CP1_CI_AS

Column level

By default, a new character type column inherits the collation of the database unless you specify the collation explicitly while creating the table.

Collation setting can be specified on the below character column types:

varchar

nvarchar

char

nchar

text

ntext

~~-------------------------------------~~~~-------------------------------------~~~~-------------------------------------~~~~--------------~~

SQL Injection

SQL injection is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It generally allows an attacker to view data that they are not normally able to retrieve. This might include data belonging to other users, or any other data that the application itself is able to access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application's content or behavior. In some situations, an attacker can escalate an SQL injection attack to compromise the underlying server or other back-end infrastructure or perform a denial-of-service attack.

SQL injection prevention techniques:

Input validation

Use regular expressions as whitelists for structured data (such as name, age, income, survey response, zip code) to ensure strong input validation.

In case of a fixed set of values (such as drop-down list, radio button), determine which value is returned. The input data should match one of the offered options exactly.

It has to be a number (the is_numeric() function).

You require that $number to be bigger than 0 and smaller than 6, which leaves you with a range of 1–5.

Parametrized queries

Parameterized queries are a means of pre-compiling an SQL statement so that you can then supply the parameters in order for the statement to be executed. This method makes it possible for the database to recognize the code and distinguish it from input data.

Stored procedures

Stored procedures (SP) require the developer to group one or more SQL statements into a logical unit to create an execution plan. Subsequent executions allow statements to be automatically parameterized. Simply put, it is a type of code that can be stored for later and used many times.

So, whenever you need to execute the query, instead of writing it over and over, you can just call the stored procedure.

Escaping

Always use character-escaping functions for user-supplied input provided by each database management system (DBMS). This is done to make sure the DBMS never confuses it with the SQL statement provided by the developer.

Avoiding administrative privileges

Don't connect your application to the database using an account with root access. This should be done only if absolutely needed since the attackers could gain access to the whole system. Even the non-administrative accounts server could place risk on an application, even more so if the database server is used by multiple applications and databases.

For that reason, it's better to enforce least privilege on the database to defend the application against SQL injection. Ensure that each application has its own database credentials and that those credentials have the minimum rights the application needs.

Instead of trying to determine which access rights you should take away, focus on identifying what access rights or elevated permissions your application needs. If a user only needs access to some parts, you could create a mode that strictly serves this function.

Web application firewall

One of the best practices to identify SQL injection attacks is having a web application firewall (WAF). A WAF operating in front of the web servers monitors the traffic which goes in and out of the web servers and identifies patterns that constitute a threat. Essentially, it is a barrier put between the web application and the Internet.

A WAF operates via defined customizable web security rules. These sets of policies inform the WAF what weaknesses and traffic behavior it should search for. So, based on that information, a WAF will keep monitoring the applications and the GET and POST requests it receives to find and block malicious traffic.

The value of a WAF comes in part from the ease with which policy modification can be enforced. New policies can be added in no time, enabling rapid rule implementation and fast incident response.

WAFs provide efficient protection from a number of malicious security attacks such as:

SQL injection

Cross-site scripting (XSS)

Session hijacking

Distributed denial of service (DDoS) attacks

Cookie poisoning

Parameter tampering

Along with these benefits, a WAF also offers:

Automatic protection from varying known and unknown threats, with not only strong default policies but also fixes for your specific WAF architecture

Real-time application security monitoring and robust HTTP traffic logging that lets you see what’s happening instantly

Considering the benefits, even beyond preventing SQL injection attacks, a WAF should always be considered a part of web security defense-in-depth strategy.

~~----------------------------------------------------------------------------------------------------------------------------~~Exception Handling in SQL Server:

Syntax:

BEGIN TRY

--T-SQL statements

--or T-SQL statement blocks

END TRY

BEGIN CATCH

--T-SQL statements

--or T-SQL statement blocks

END CATCH

--Exception handling syntax:

BEGIN TRY

DECLARE @num INT

---- Divide by zero to generate Error

SET @num = 5/0

PRINT 'This will not execute'

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

Error Functions used within CATCH block:

ERROR_NUMBER()

This returns the error number and its value is the same as for @@ERROR function.

ERROR_LINE()

This returns the line number of T-SQL statement that caused an error.

ERROR_SEVERITY()

This returns the severity level of the error.

ERROR_STATE()

This returns the state number of the error.

ERROR_PROCEDURE()

This returns the name of the stored procedure or trigger where the error occurred.

ERROR_MESSAGE()

This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.

The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.

Each TRY block is associated with only one CATCH block and vice versa

TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.

TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.

XACT_STATE function within the TRY..CATCH block can be used to check whether an open transaction is committed or not. It will return -1 if the transaction is not committed else returns 1.

------------------

Types of Errors

There are two types of errors in SQL Server: system errors and custom errors. System errors can be viewed in the sys.messages system view and are defined by SQL server. Therefore, when a system error occurs, SQL Server will log a system error and may take actions to fix the error.

Custom errors, on the other hand, are generated by T-SQL custom codes based on your code or business logic. To add a custom error message to sys.messages, the stored procedure sp_addmessage is used.

Below is an example of adding a custom error message:

EXEC sp_addmessage 50001, 16,

N'Unit price needs to be greater than 0'.

sql

In the example above, 50001 is the message id. This parameter can be an integer between 50,001 and 2,147,483,647.

16 is the severity, which is smallint and ranges from 1 through 25.

Raising Errors with RAISERROR

RAISERROR allows applications to generate an error that could then be caught by the calling process. This makes error handling in the application easier as it is sent like any other system error. RAISERROR can therefore be used to troubleshoot T-SQL codes, debug and check the value of variables and return meaningful error messages based on variables data.

Below is an example of using RAISERROR in SQL Server:

RAISERROR (N'This is message.', -- Message text.

10, -- Severity,

1); -- Second argument.

sql

The output would then be as follows:

This is message.

Raising Errors with THROW

The THROW statement is a simpler method of raising errors in T-SQL code.

Below is an example of how THROW is used in SQL Server:

THROW

51000, -- error number

'This is not a valid value for unit price.', -- message

1; --state

sql

The result set would then be as follows:

Msg 51000, Level 16, State 1, Line 1

This is not a valid value for unit price.

sql

In the example above, 51000 is the error number. The error number is an integer that must be a value between 50000 and 2147483647. The next parameter is the message, which is a string containing a description of the error. It's format is nvarchar(2048), and finally we have the state, which is a constant between 0 and 255. It shows the state to associate with the message. State is of type tinyint.

Understanding the @@Error variable

@@ERROR is a system variable that holds the error number of the last error that has occurred. One of the drawbacks of using @@ERROR is that the value it holds resets as each additional statement is executed. To get the last error number, the query below is used:

Select @@ERROR

sql

Example of Using @@Error

Consider the example below where a custom error is raised before selecting the value of @@ERROR.

RAISERROR(N'Message', 16, 1);

IF @@ERROR <> 0

PRINT 'Error=' + CAST(@@ERROR AS VARCHAR(8));

sql

The output is as follows:

Msg 50000, Level 16, State 1, Line 1

Message

Error=0

sql

Therefore, when working with @@ERROR, it is recommended to capture the error number into a variable as soon as it occurs and then continue processing with the variable. This is demonstrated in the example below.

DECLARE @Value int;

RAISERROR(N'Message', 16, 1);

SET @Value = @@ERROR;

IF @Value <> 0

PRINT 'Error=' + CAST(@Value AS VARCHAR(8));

sql

The output will then be as below, where the ID of the message is successfully captured in the variable @Value.

Msg 50000, Level 16, State 1, Line 2

Message

Error=50000

sql

Error Handling in T-SQL

The T-SQL Try Catch is used to handle errors in SQL Server programming. TRY-CATCH in SQL Server works in a similar manner as exception handling in popular programming languages such as Java and C#. For example, when a T-SQL code is written using TRY CATCH and a code in the TRY blocks fails, the execution flow will exit the TRY block and move to the CATCH block.

Below is an example of error handling in SQL Server:

BEGIN TRY

SELECT 50/0

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber

,ERROR_STATE() AS ErrorState

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

sql

If we executed only 50/0, the execution would fail with no way to handle the error. In the example above, when the error occurred, the flow moved to the catch block where the error is handled. For example, in the catch block, we could have logged the error in a log table to keep track of the error.

Also, note the use of the functions ERROR_NUMBER(), ERROR_STATE(), ERROR_LINE() and ERROR_MESSAGE(), which are very helpful in the catch block.

~~-----------------------------------------------------------------------------------------------------------------------------~~

What Is SQL Server Performance Tuning?

SQL performance tuning consists of making queries of a relation database run as fast as possible. SQL tuning involves several elements, including identifying which queries are experiencing slowdowns and optimizing them for maximum efficiency. Numerous relational databases—including MySQL and SQL Server—will benefit from SQL tuning.

Generic SQL Server performance tuning best practices include:

· Avoiding coding loops

· Avoiding correlating SQL sub queries

· Avoid Null value in the fixed-length field/Use Appropriate Naming Convention/Use Appropriate Data type

· Normalize tables in a database

· Store image path instead of the image itself

· USE Common Table Expressions (CTEs) instead of Temp table

· Use UNION ALL instead of UNION

· Use Stored Procedure

· Use Between instead of In

· Use If Exists to determine the record

· Avoid Cursors

· Use Try–Catch

· Remove Unused Index / Always create an index on the table/ Importance of Column Order in index /Use Index for required columns

· Use Foreign Key with the appropriate action

· Drop Index before Bulk Insertion of Data

· Use Unique Constraint and Check Constraint

· Avoid Use of Temp table

· Use View for complex queries

· Make Transaction short

The following SQL Server performance tuning best practices can be used with SolarWinds DPA to optimize your server and database performance:

1) Generate an Actual Execution Plan

In order to diagnose slow queries, it’s crucial to be able to generate graphical execution plans, which you can do by using SQL Server Management Studio. Actual execution plans are generated after the queries run. But how do you go about generating an execution plan?

Begin by clicking on “Database Engine Query”, on the SQL Server Management Studio toolbar. After that, enter the query and click “Include Actual Execution Plan” on the Query menu.Finally, it’s time to run your query. You do that by clicking on the “Execute” toolbar button or pressing F5. Then, SQL Server Management Studio will display the execution plan in the results pane, under the “Execution Pane” tab.

2)Monitor Resource Usage

Resource usage is an essential factor when it comes to a SQL database performance. Since you can’t improve what you don’t measure, you definitely should monitor resource usage.

So how can you do it?
If you’re using Windows, use the System Monitor tool to measure the performance of SQL Server. It enables you to view SQL Server objects, performance counters, and the behavior of other objects.

Using System Monitor allows you to monitor Windows and SQL Server counters simultaneously, so you can verify if there’s any correlation between the performance of the two.

3. Use the Database Engine Tuning Advisor

Another important technique for SQL performance tuning is to analyze the performance of Transact-SQL statements that are run against the database you intend to tune. You can use the Database Engine Tuning Advisor to analyze the performance implications.But the tool goes beyond that: it also recommends actions you should take based on its analysis. For instance, it might advise you to create or remove indexes.

4. Find Slow Queries With SQL DMVs

One of the great features of SQL Server is all of the dynamic management views (DMVs) that are built into it. There are dozens of them and they can provide a wealth of information about a wide range of topics.

There are several DMVs that provide data about query stats, execution plans, recent queries and much more. These can be used together to provide some amazing insights.

Required Permissions

To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission. This lets you selectively restrict access of a user or login to dynamic management views and functions. To do this, first create the user in master and then deny the user SELECT permission on the dynamic management views or functions that you do not want them to access. After this, the user cannot select from these dynamic management views or functions, regardless of database context of the user.

5)Query Reporting via APM Solutions

One of the great features of application performance management (APM) tools is the ability to track SQL queries. For example, Retrace tracks SQL queries across multiple database providers, including SQL Server. Retrace tells you how many times a query was executed, how long it takes on average, and what transactions called it.

This is valuable information for SQL performance tuning. APM solutions collect this data by doing lightweight performance profiling against your application code at runtime.

3 Types of APM monitoring tools

App Metrics based – Several tools use various server and app metrics and call it APM. At best they can tell you how many requests your app gets and potentially which URLs might be slow. Since they don’t do code-level profiling, they can’t tell you why.

Code-level performance – Stackify Retrace, New Relic, AppDynamics, and Dynatrace are the typical type of APM products you think of, based on code profiling and transaction tracing.

Network-based – Extrahop uses the term APM in regards to their ability to measure application performance based on network traffic. There is a whole product category called NPM that focuses on these types of solutions.APM solutions can help identify common application problems quickly:

Track overall application usage to understand spikes in traffic

Find slowness or connection problems with application dependencies including SQL, queues, caching, etc

Identify slow SQL queries

Find the highest volume and slowest web pages or transactions

Critical Application Performance Management Features for Developers

1. Performance of every web request and transaction

2. Code level performance profiling

3. Usage and performance of all application dependencies like databases, web services, caching, etc

4. Detailed traces of individual web requests or transactions

5. Basic server monitoring and metrics like CPU, memory, etc

6. Application framework metrics like performance counters, JMX MBeans, etc

7. Custom applications metrics created by the dev team or business

8. Application log data

9. Application errors

10. Real user monitoring (RUM)

6. SQL Server Extended Events

The SQL Server Profiler was around for a very long time. It was a very useful tool to see in real-time what SQL queries are being executed against your database, but it’s currently deprecated. Microsoft replaced it with SQL Server Extended Events.

Troubleshooting the cause of working set trimming.

Troubleshooting excessive CPU usage.

Troubleshooting deadlocks.

Correlating request activity with Windows ETW logs.

7. SQL Azure Query Performance Insights

~~-------------------------------------------~~~~-------------------------------------------~~~~---------------------------------------~~

What is B-Tree?

The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:

Root node: A root node contains node pointers to only one branch node.
Branch nodes: A branch node contains pointers to leaf nodes or other branch nodes, which can be two or more.
Leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes, which can be many.

~~-----------------------------------------------------------------------------------------------------------------------------~~

RANK () function

The RANK () function is a window function that assigns a rank to each row within a partition of a result set. The rows within a partition that have the same values will receive the same rank. The rank of the first row within a partition is one. The RANK () function adds the number of tied rows to the tied rank to calculate the rank of the next row, therefore, the ranks may not be consecutive.

The following shows the syntax of the RANK() function:
RANK() OVER ([PARTITION BY partition_expression, ... ]ORDER BY sort_expression [ASC | DESC], ...)

ROW_NUMBER()
RANK(),
DENSE_RANK()
NTILE()

ROW_NUMBER ()

It is used to return a unique sequential number for each row within the specified partition.

It starts from 1 for the first row in each partition and without repeating or skipping numbers in the ranking result of each partition. For duplicate values within the row set, the ranking ID numbers will be assigned arbitrarily

SELECT ROW_NUMBER() OVER( ORDER BY Name) AS SrNo, Name FROM Member

RANK()

It is used to return a unique rank number for each distinct row within the specified partition.

It starts from 1 for the first row in each partition, with the same rank for duplicate values and it leaves gaps between the ranks; this gap appears in the sequence after the duplicate values.

RANK() behaves like ROW_NUMBER() function except for the rows with equal values, where it will rank with the same rank ID and generate a gap after it.

SELECT RANK() OVER( ORDER BY Point desc) AS [Rank], Name, Point FROM Member

DENSE_RANK()

It is similar to RANK() but the only difference is DENSE_RANK() does not skip any rank, i.e. leaving no gap(s) between the gap(s).

SELECT DENSE_RANK() OVER( ORDER BY Point desc) AS [Rank], Name, Point FROM Member

NTILE()

It is used to distribute the rows into the rows set with a specific number of groups.

It provides each row in the result set with a unique group number. It starts from 1 that shows the group that belongs to this group. N is a positive number and it distributes the rows set into.

SELECT NTILE(3) OVER( ORDER BY Point desc) AS [Rank], Name, Point FROM Member

~~---------------------------------------------------------------------------------------------------------------------------~~What is Row_Number() in the SQL server

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.
~~-----------------------------------------------------------------------------------------------------------------------------~~Dirty Read in SQL server

A dirty read occurs when two operations say read and write occurs together giving the incorrect or unedited data. Suppose, A has changed a row but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read. To prevent dirty reads, the database engine must hide uncommitted changes from all other concurrent transactions. Each transaction is allowed to see its own changes because otherwise the read-your-own-writes consistency guarantee is compromised.

~~-----------------------------------------------------------------------------------------------------------------------------~~

How to get @@error and @@row count at the same time in sql server

@@error:

Returns 0 if the previous Transact-SQL statement encountered no errors. Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys. messages catalog view, then @@ERROR contains the value from the sys.

@@ROWCOUNT:

SQL Server @@ROWCOUNT is a system variable that is used to return the number of rows that are affected by the last executed statement in the batch. ... @@ROWCOUNT is used frequently in the loops to prevent the infinite loops and stop the current process when all the target rows are processed.

If @@ROWCOUNT is checked after the error-checking statement then it will have 0 as it would have been reset.

If @@ROWCOUNT is checked before the error-checking statement then @@ERROR would get reset.

To get @@ERROR and @@ROWCOUNT at the same time do both in the same statement and store them in a local variable as bellow.

DECLARE @RC INT,

@ER INT

SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

~~-----------------------------------------------------------------------------------------------------------------------------~~

What is the difference between @@ Identity and Scope_identity ()?

The @@identity function returns the last identity created in the same session. The scope_identity() function returns the last identity created in the same session and the same scope. The ident_current(name) returns the last identity created for a specific table or view in any session.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table. @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session.

The @@identity function returns the last identity created in the same session.

The identity() function is not used to get an identity, it's used to create an identity in a select...into query.

The session is the database connection. The scope is the current query or the current stored procedure.

~~-----------------------------------------------------------------------------------------------------------------------------~~
Aggregate () in Sql server

An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT (*), aggregate functions ignore null values.

Transact-SQL provides the following aggregate functions:

· APPROX_COUNT_DISTINCT

· AVG

· CHECKSUM_AGG

· COUNT

· GROUPING

· MAX

· MIN

· SUM
~~-----------------------------------------------------------------------------------------------------------------------------~~Pivot tables in sql server:

Pivot tables are a piece of summarized information that is generated from a large underlying dataset. It is generally used to report on specific dimensions from vast datasets. Essentially, the user can convert rows into columns. This gives the users the ability to transpose columns from a SQL Server table easily and create reports as per the requirements.

Some pivot tables are also created to help in data analysis, mainly for slicing and dicing with the data and generate analytical queries after all. If you see the figure below, you’ll have some idea of how a pivot table is created from a table. We use operators like most SUM, FOR, and IN

SELECT * FROM (

SELECT

[Student],

[Subject],

[Marks]

FROM Grades

) StudentResults

PIVOT (

SUM([Marks])

FOR [Subject]

IN (

[Mathematics],

[Science],

[Geography]

)

) AS PivotTable

Building a Dynamic Stored Procedure for PIVOT Tables:

CREATE PROCEDURE dbo.DynamicPivotTableInSql

@ColumnToPivot NVARCHAR(255),

@ListToPivot NVARCHAR(255)

BEGIN

DECLARE @SqlStatement NVARCHAR(MAX)

SET @SqlStatement = N'

SELECT * FROM (

SELECT

[Student],

[Subject],

[Marks]

FROM Grades

) StudentResults

PIVOT (

SUM([Marks])

FOR ['+@ColumnToPivot+']

IN (

'+@ListToPivot+'

)

) AS PivotTable

EXEC(@SqlStatement)

END

@ColumnToPivot – This parameter accepts the name of the column in the base table on which the pivot table is going to be applied. For the current scenario, it will be the “Subject” column because we would like to pivot the base table and display all the subjects in the columns

@ListToPivot – This parameter accepts the list of values that we want to visualize as a column in the pivot table in SQL
~~-----------------------------------------------------------------------------------------------------------------------------~~What is XML data type in SQL Server?

The XML data type is a built-in data type in SQL Server and is somewhat similar to other built-in types such as int and varchar. As with other built-in types, you can use the XML data type as a column type when you create a table as a variable type, a parameter type, a function-return type, or in CAST and CONVERT.

XML (eXtensible Markup Language) is one of the most common formats used to share information between different platforms. Owing to its simplicity and readability, it has become the de-facto standard for data sharing. In addition, XML is easily extendable.

What is for XPATH in SQL Server?

XPath (XML Path Language) is a query language that can be used to query data from XML documents. ... It is based on a tree representation of the XML document, and selects nodes by a variety of criteria. In popular use, an XPath expression is often referred to simply as an XPath.
~~-----------------------------------------------------------------------------------------------------------------------------~~

What is SQL Server Raiserror?
RAISERROR is a SQL Server error handling statement that generates an error message and initiates error processing. RAISERROR can either reference a user-defined message that is stored in the sys. messages catalog view or it can build a message dynamically.

~~-----------------------------------------------------------------------------------------------------------------------------~~NOLOCK in SQL server:

The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it.

WITH (NOLOCK) is the equivalent of using READ UNCOMMITTED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads from being deadlocked by other operations, it comes with a risk.

The NOLOCK and READUNCOMMITED hints are only allowed with SELECT statements. If we try to use this for an UPDATE, DELETE or INSERT we will get an error.
~~-----------------------------------------------------------------------------------------------------------------------------~~What is database mirroring and replication in SQL server
Mirroring and Replication are the terms somehow related to the copying of data in a DBMS. The prior difference between mirroring and replication is that mirroring refers to copy a database to another location whereas replication includes the copy of data and database objects from one database to another database.

Mirroring	Replication
Mirroring is the copying of data or databases to a different location. (or)Creating a DB copy at a different location(machine). Located on a different machine	While replication is the creation of data and database objects to increase the distribution actions. (or)Creation of DB and data objects to enhance the distributed operations. Located on different DB.
We perform mirroring on the database.	While we perform replication on the objects of data and database.
Mirroring operation on the database is costlier than replication.	While replication is less costlier than mirroring.
Mirroring does not support the distributed database.	While replication supports the distributed database.
Generally, a mirror database is implemented on a different machine or location from its main database.	While in replication, the database and data objects are kept in another database.

What is the use of Service Broker in SQL Server?

SQL Server Service Broker (SSBS) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008) that allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable, and secure queuing/message based applications within the database itself.

SQL Server Service Broker provides native support for messaging and queuing in the SQL Server Database Engine and Azure SQL managed instance Developers can easily create sophisticated applications that use the Database Engine components to communicate between disparate databases and build distributed and reliable applications.

1. To enable Service Broker run: ALTER DATABASE [Database_name] SET ENABLE_BROKER;

2. To disable Service Broker: ALTER DATABASE [Database_name] SET DISABLE_BROKER;
To check if Service Broker is enabled on a SQL Server database:
select name,is_broker_enabled from sys.databases where name= [YourDB]
~~-----------------------------------------------------------------------------------------------------------------------------~~What is the execution plan in SQL Server?
An execution plan in SQL Server is a simple graphical representation of the operations that the query optimizer generates to calculate the most efficient way to return a set of results. The execution plan for a query is your view into the SQL Server query optimizer and query engine. It will reveal which objects a query uses, object like: tables.

The first type is the Estimated Execution Plan. ...

The second type is the Actual Execution Plan that is generated by executing the submitted query, displaying the actual steps that followed while executing the query.
~~-----------------------------------------------------------------------------------------------------------------------------~~What is the log shipping in SQL Server?

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

To run the Transaction Log Shipping Status report, execute the following steps:

Connect to the primary, secondary, or monitor server using Object Explorer in SQL Server Management Studio.
Right-click the SQL Server instance; click Reports.
Click Standard Reports.
Click Transaction Log Shipping Status.

Disadvantages of Log Shipping

· Possible data loss when the primary server fails. ...

· Some manual DBA work is required to bring the standby server online, as discussed in this article.

· Log shipping setup cannot be scripted. ...

· The Enterprise edition of SQL Server 2000 is required on primary and standby servers.
~~-----------------------------------------------------------------------------------------------------------------------------~~SQL Server Agent service.

This is a windows service that enables database developers and database administrators to schedule jobs on the SQL Server machine. The jobs can be simple T-SQL scripts, stored procedures, SSIS packages or SSAS databases. This service is available on all editions of SQL Server except the Express edition.

There are a few components of the SQL Server Agent service that you must be aware of before proceeding forward.

Jobs – This is a program that defines the rules about the repetitive execution of one or more scripts or other utilities within the SQL Server environment
Steps – These can be considered as the building blocks of the jobs. Your job can contain one or multiple steps. Each step executes a specific set of instructions. The next step can be executed based on the success or failure of a previous step
Schedules – These are periodic rules set on the job to execute automatically based on a pre-defined time slot. These jobs can be scheduled hourly, daily, weekly, monthly, or even on specific days of the week
Alerts – SQL Server generates events that are stored in the Microsoft Windows Application Log. Whenever the agent finds a match, it fires an alert which is a response to the event
Notifications – You can set up email notifications to update about the result of the job execution. This is mostly done in case of job failures so that the person responsible for the jobs can take appropriate actions

Head over to Run and type the command services.msc. Click OK once done.

Once you hit OK, the Services window will show up. You can scroll below to find the service with the name “SQL Server Agent (<<INSTANCE NAME>>)”. As you can see in the figure below, the status of the service is not running. Let us go and start the service.

In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Expand SQL Server Agent. Right-click Job Activity Monitor and click View Job Activity. In the Job Activity Monitor, you can view details about each job that is defined for this server.
~~-----------------------------------------------------------------------------------------------------------------------------~~What is Profiler in SQL Server?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. ... Monitoring the performance of SQL Server to tune workloads.

Performance issue:
Yes, SQL Server Profiler does affect performance. When you trace using the GUI, all events are synchronously processed and filtered, so factors such as server load, network latency, which events you collect, and even where the collected events are stored, all contribute to overhead.

To open the SQL Profiler in SQL Server Management Studio:

1. Click on Tools.

2. Click on SQL Server Profiler.

3. Connect to the server on which we need to perform profiling.

4. On the Trace Properties window, under the General tab, select the blank template.

5. On the Events Selection tab, select the Deadlock graph under the Locks leaf.
~~-----------------------------------------------------------------------------------------------------------------------------~~
How to copy data from one table to another in an SQL server

· Insert into select

· Select into
~~-----------------------------------------------------------------------------------------------------------------------------~~What is MERGE used for in SQL?

The MERGE statement is used to make changes in one table based on values matched from another. It can be used to combine insert, update, and delete operations into one statement.
~~-----------------------------------------------------------------------------------------------------------------------------~~

What is the input/output parameter in SQL?

I/P: Pass something to SP
O/P: will return something back to you and hold somewhere(Variable)

When a parameter in a SQL statement or stored procedure is declared as out, the value of the parameter is returned back to the caller.

Input parameter is a parameter whose value is passed into a stored procedure/function module. The value of an IN parameter is a constant; it can't be changed or reassigned within the module.

Output parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module. You cannot assign a default value to an OUT parameter outside of the module's body. In other words, an OUT parameter behaves like an uninitialized variable.

Point to Remember:
The return value returns data of only an integer data type.

The following is when to use output parameter and return values in Stored Procedures:

When you want to return one or more items with a data type then it is better to use an output parameter.

Generally, use an output parameter for anything that needs to be returned.

When you want to return only one item with only an integer data type then it is better to use a return value.

Generally, the return value is only to inform the success or failure of the Stored Procedure.

A return value of 0 indicates success and any non-zero value indicates failure.
~~-----------------------------------------------------------------------------------------------------------------------------~~

Check duplicate remove duplicates in sql server?

My own query:

With EmployeeCTE As

(

Select *, row_number() over (partition by ID order by ID) As Rownumber from employee

)

delete from employeecte where rownumber>1

Note: Partition by divides the query reseultset set into parties.

------------------------------------------------------------------------------------------------------------------

The following statement uses a common table expression (CTE) to delete duplicate rows:

WITH cte AS (

SELECT

ROW_NUMBER() OVER (

PARTITION BY

ORDER BY

) as row_num

FROM

sales.contacts

)

DELETE FROM cte

WHERE row_num > 1;

---------------------------------------------------------------------

RANK function to SQL delete duplicate rows

SELECT E.ID,

E.firstname,

E.lastname,

E.country,

T.rank

FROM [SampleDB].[dbo].[Employee] E

INNER JOIN

(

SELECT *,

RANK() OVER(PARTITION BY firstname,

lastname,

country

ORDER BY id) rank

FROM [SampleDB].[dbo].[Employee]

) T ON E.ID = t.ID;

In the screenshot, you can note that we need to remove the row having a Rank greater than one. Let’s

remove those rows using the following query.

DELETE E

FROM [SampleDB].[dbo].[Employee] E

INNER JOIN

(

SELECT *,

RANK() OVER(PARTITION BY firstname,

lastname,

country

ORDER BY id) rank

FROM [SampleDB].[dbo].[Employee]

) T ON E.ID = t.ID

WHERE rank > 1;

----------------------------------------------------------------------------------------

Removing duplicates rows from a SQL Server table with a unique index

USE TestDB

--Initializing the table

TRUNCATE TABLE TableA

INSERT INTO TableA(Value)

VALUES(1),(2),(3),(4),(5),(5),(3),(5)

--Deleting duplicate values

DELETE t

FROM TableA t

WHERE ID IN ( SELECT a.ID FROM TableA a, (SELECT ID, (SELECT MAX(Value) FROM TableA i WHERE

o.Value=i.Value GROUP BY Value HAVING o.ID=MAX(i.ID)) AS MaxValue FROM TableA o) b

WHERE a.ID=b.ID AND b.MaxValue IS NULL)

--Initializing the table

TRUNCATE TABLE TableA

INSERT INTO TableA(Value)

VALUES(1),(2),(3),(4),(5),(5),(3),(5)

--Deleting duplicate values

DELETE a

FROM TableA a

INNER JOIN

(SELECT ID, RANK() OVER(PARTITION BY Value ORDER BY ID DESC) AS rnk FROM TableA ) b

ON a.ID=b.ID

WHERE b.rnk>1

--Initializing the table

TRUNCATE TABLE TableA

INSERT INTO TableA(Value)

VALUES(1),(2),(3),(4),(5),(5),(3),(5)

--Deleting duplicate values

DELETE FROM TableA

WHERE ID NOT IN (SELECT MAX(ID)

FROM TableA

GROUP BY Value)

---------------------------------------------------------------------

Removing duplicates from table without a unique index in ORACLE

DELETE TableB

WHERE rowid not in (

SELECT MAX(rowid)

FROM TableB

GROUP BY Value

);

DELETE from TableB o

WHERE rowid < (

SELECT MAX(rowid)

FROM TableB i

WHERE i.Value=o.Value

GROUP BY Value

);

----------------------------------------------------------------------

Removing duplicates from a SQL Server table without a unique index

USE TestDB

CREATE TABLE TableB (Value INT)

INSERT INTO TableB(Value)

VALUES(1),(2),(3),(4),(5),(5),(3),(5)

SELECT * FROM TableB

; WITH TableBWithRowID AS

(

SELECT ROW_NUMBER() OVER (ORDER BY Value) AS RowID, Value

FROM TableB

)

DELETE o

FROM TableBWithRowID o

WHERE RowID < (SELECT MAX(rowID) FROM TableBWithRowID i WHERE i.Value=o.Value GROUP BY Value)

SELECT * FROM TableB

(A) Delete duplicate rows with the DELETE JOIN statement

DELETE t1 FROM contacts t1

INNERJOIN contacts t2

WHERE

t1.id < t2.id AND

t1.email = t2.email;

-------------------------------------------------------------------------------------------------

Using GroupBy:

DELETE FROM STUDENT WHERE

Id NOT IN(SELECT MIN(Id) AS 'Id' FROM Student GROUP BY Name, Age, Standard)

~~-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------~~

2nd highest salary in sql server:

select distinct top 2 salary from tbl_Employees order by Salary desc

Select Max(Salary) as Salary from tbl_Employees where Salary <(select MAX(Salary) from tbl_Employees)

SELECT TOP 1 SALARY

FROM (

SELECT DISTINCT TOP 2 SALARY

FROM tbl_Employees

ORDER BY SALARY DESC

) RESULT

ORDER BY SALARY

WITH RESULT AS

(

SELECT SALARY,

DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK

FROM tbl_Employees

)

SELECT TOP 1 SALARY

FROM RESULT

WHERE DENSERANK = 2

select *from employee

group by salary

order by salary desc limit 1,1;

SELECT name, MAX(salary) AS salary

FROM employee

WHERE salary IN

(SELECT salary FROM employee MINUS SELECT MAX(salary)

FROM employee);

SELECT name, MAX(salary) AS salary

FROM employee

WHERE salary <> (SELECT MAX(salary)

FROM employee);

WITH T AS

(

SELECT *

DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk

FROM Employees

)

SELECT Name

FROM T

WHERE Rnk=2;

SELECT name, MAX(salary) AS salary

FROM employee

WHERE salary < (SELECT MAX(salary)

FROM employee

WHERE salary < (SELECT MAX(salary)

FROM employee)

);

SELECT salary

FROM employee

ORDER BY salary desc limit n-1,1

SELECT name, salary

FROM employee A

WHERE n-1 = (SELECT count(1)

FROM employee B

WHERE B.salary>A.salary)

SELECT * FROM employee

WHERE salary= (SELECT DISTINCT(salary)

FROM employee ORDER BY salary LIMIT n-1,1);

select min(salary) from

(select distinct salary from emp order by salary desc)

where rownum < 3;

~~-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------~~

Can i call a stored procedure from another stored procedure in Sql server?

Yes this is possible but first you need to store this id into a variable and then pass the same to second sp

//Procedure 1

Create proc stp_Test1

begin

select * from table

end

//Procedure 2

create proc stp_Test2

begin

// To call the Procedure stp_Test1 in this procedure

// Its return that Procedure output.

exec stp_Test1

end

You can also use execute to call stored procedures that return one or more result sets, but in that case any result sets are ignored.

SQL SERVER – Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter

-- First Stored Procedure

CREATE PROCEDURE SquareSP

@MyFirstParam INT

DECLARE @MyFirstParamSquare INT

SELECT @MyFirstParamSquare = @MyFirstParam*@MyFirstParam

-- Additional Code

RETURN (@MyFirstParamSquare)

-- Second Stored Procedure

CREATE PROCEDURE FindArea

@SquaredParam INT

DECLARE @AreaofCircle FLOAT

SELECT @AreaofCircle = @SquaredParam * PI()

RETURN (@AreaofCircle)

-- Pass One Stored Procedure's Result as Another Stored Procedure's Parameter

DECLARE @ParamtoPass INT, @CircleArea FLOAT

-- First SP

EXEC @ParamtoPass = SquareSP 5

-- Second SP

EXEC @CircleArea = FindArea @ParamtoPass

SELECT @CircleArea FinalArea

-- Clean up

DROP PROCEDURE SquareSP

DROP PROCEDURE FindArea

~~-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------~~

Search This Blog

PrasannaKumar Putha - An Author,Trainer, Software Architect and entrepreneur, founder of this Blog

Sql server interview questions for experienced

Q)Normalization and its Types:

2NF (Second Normal Form) Rules:
Remove the subset of data and place in a separate table. Single Column Primary Key that does not functionally dependent on any subset of candidate key relation. . Only those data that relates to a table’s primary key is stored in each table.

Third Normal Form (3NF):

Boyce Code Normal Form (BCNF):

Fourth Normal Form (4NF)

Fifth Normal Form (5NF):

Logon Triggers

SQL Commands

Types of SQL Commands:

Data Definition Language (DDL)

2. Data Manipulation Language

3. Data Control Language:

4. Transaction Control Language:

RANK () function

ROW_NUMBER ()

RANK()

DENSE_RANK()

NTILE()

Comments

Post a Comment

Popular posts from this blog

DOT NET CORE Basic Interview Question and Answers

Angular Basic concepts

Sql server interview questions for experienced

Q)Normalization and its Types:

2NF (Second Normal Form) Rules: Remove the subset of data and place in a separate table. Single Column Primary Key that does not functionally dependent on any subset of candidate key relation. . Only those data that relates to a table’s primary key is stored in each table.

Third Normal Form (3NF):

Boyce Code Normal Form (BCNF):

Fourth Normal Form (4NF)

Fifth Normal Form (5NF):

Logon Triggers

SQL Commands

Types of SQL Commands:

Data Definition Language (DDL)

2. Data Manipulation Language

3. Data Control Language:

4. Transaction Control Language:

RANK () function

ROW_NUMBER ()

RANK()

DENSE_RANK()

NTILE()

Comments

Post a Comment

Popular posts from this blog

DOT NET CORE Basic Interview Question and Answers

Angular Basic concepts

2NF (Second Normal Form) Rules:
Remove the subset of data and place in a separate table. Single Column Primary Key that does not functionally dependent on any subset of candidate key relation. . Only those data that relates to a table’s primary key is stored in each table.