Rachit Gupta

Can we maintain multiple transactions inside a procedure?

Multiple/ nested transaction can be maintained by the transaction inside a transaction, below is the sample for the statement for the same, below is the diagram which explains, if we have nested transaction how are they executed: i.e. first transaction 3 will be committed, post that transaction 2, at the end…

SQL Server

Answered Jan 7, 2020 · 3.9K Views Read answer →

How to find (max, AVG, COUNT, SUM, and MIN) in the row/ columnFor calculating the operations on the column we will have to find max, AVG, COUNT, SUM, and MIN of salary then below will be operation:

For calculating the operations on the column we will have to find max, AVG, COUNT, SUM, and MIN of salary then below will be operation: Dataset: CREATE TABLE [dbo].[Employ] ( [EmployID] [int] NULL, [EmployName] [varchar](30) NULL, [EmploySal

SQL Server

Answered Dec 29, 2019 · 1.4K Views Read answer →

State some Uses of function.

Below are few uses: 1. Function is written only once and can be used/called multiple times at multiple places such as procedure/ views etc. this saves further efforts and time 2. It helps in making the program modular and easy to understand 3. Functions are compiled once and there are cached before they are used. 4.…

SQL Server

Answered Dec 16, 2019 · 1.4K Views Read answer →

Difference between nullif and isnull

Isnull is a function which will check if the first value is null then return the second value, if first value is not null then it will return the first value only declare @name varchar(30),@replace_name varchar(30) set @name=null set @replace_name='Replaced_ALEX' select isnull (@name, @replace_name)set @name='Alex'…

SQL Server

Answered Nov 21, 2019 · 2.2K Views Read answer →

How can you fetch the data from different types of server (oracle/ big data/Teradata) to SQL server, within a procedure?

In Server there is a system function named: OPENQUERY () which supports you to fetch the data from different servers. For using this we need to follow the below steps: 1. Create a Linked server connection in SQL server with all the details such as: EXEC [dbo].[CreateStdLinkedServer] @LinkedServerName =…

SQL Server

Answered Nov 5, 2019 · 1.4K Views Read answer →

How to remove duplicate rows from a given table, when there is a unique key based on more than one column.And you have to keep the latest record based on the modified date/ create date

Solution 1: Using Row_number and CTE: If there is a unique combination based on 3-4 columns so we should use a row_number function with the partition by clause and find the number for each row. Post that, we can use this statement in CTE (common table expression). Post that we can delete the data from CTE. CREATE…

SQL Server

Answered Nov 5, 2019 · 3.2K Views Read answer →

If you are given an xml (as a table) as input and you want to store that XML in the database. How can be work with update/ insert from the XML

In SQL you are given a functionality that you can convert xml inputs from the application/ or Json or any other input can be opened in the database and it can be used in the SQL statements to the database: ---Table create CREATE TABLE xmlEmploy (FirstName VARCHAR(50), ID nvarchar(20), Department VARCHAR(50) ) DECLARE…

SQL Server

Answered Nov 5, 2019 · 1.3K Views Read answer →

Difference and similarities between scope_identity, @@identity and ident_current

Below are the definitions of these functions: scope_identity 1. This returns the last identity row id which is inserted to any table in the current scope ( i.e. session/ function/ triggers / procedure /batch) 2.. If no insert statement is executed having identity column in the current session prior to this statement…

SQL Server

Answered Nov 1, 2019 · 3.0K Views Read answer →

Difference between ceiling, Floor, Abs, Round?

All these functions are used in the mathematical expression mainly in the decimal variables. Ceiling is used to round the decimal value to higher whole digit no. Floor is used to round the decimal value to lower whole digit no. Abs is used to return the positive value of any value send in parameter Round is used to…

SQL Server

Answered Nov 1, 2019 · 3.4K Views Read answer →

What all joins do functions and tables support?

We can’t use normal joins (inner/left/ right joins) directly on table and function, however we can use cross apply/ outer apply on table and function. Cross apply will work as the inner join operator with the functions and tables.it will return the matching row between the table valued function and table. Outer apply…

SQL Server

Answered Nov 1, 2019 · 1.4K Views Read answer →

How to use table variables and temporary tables in function?

Use of Temporary is not allowed in the function. We can use only the permanent table/or the table variables in the function. As the create table/ any DML/DDL operation is not allowed in the function. Also table variables are allowed in the multi-line function table valued only. CREATE FUNCTION…

SQL Server

Answered Nov 1, 2019 · 1.5K Views Read answer →

Which function you will use if you have to give one rank to one person no duplicate ranks, whether value is same or not.

For this question we will be using a common data set which will be: CREATE TABLE [dbo].[Employ] ( [EmployID] [int] NULL, [EmployName] [varchar](30) NULL, [EmploySalary] [int] NULL )

SQL Server

Answered Nov 1, 2019 · 1.9K Views Read answer →