What is Log Shipping ?
Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover.
Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.
What are the advantages of database normalization ?
Benefits of normalizing the database are
No need to restructure existing tables for new data.
Reducing repetitive entries.
Reducing required storage space
Increased speed and flexibility of queries.
What is Denormalization ?
For optimizing the performance of a database by adding redundant data or by grouping data is called de-normalization.
It is sometimes necessary because current DBMSs implement the relational model poorly.
In some cases, de-normalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.
A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De‐normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
Query to Pull ONLY duplicate records from table ?
There are many ways of doing the same and let me explain one here. We can acheive this by using the keywords GROUP and HAVING. The following query will extract duplicate records from a specific column of a particular table.
Select specificColumn
FROM particluarTable
GROUP BY specificColumn
HAVING COUNT(*) > 1
This will list all the records that are repeated in the column specified by “specificColumn” of a “particlarTable”.
Types of Joins in SQL SERVER ?
There are 3 types of joins in Sql server.
Inner Join
Outer Join
Cross Join
Outer join again classified into 3 types.
Right Outer Join
Left Outer Join
Full Outer Join.
Whar are the differences between Temp table and Table variable ?
This is very routine question in interviews. Let’s see the major differences between these two.
Table variables are Transaction neutral where as Temp tables are Transaction bound. For example if we declare and load data into a temp table and table variable in a transaction and if the transaction is ROLLEDBACK, still the table variable will have the data loaded where as Temp table will not be available as the transaction is rolled back.
Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
Table variables don’t participate in transactions, logging or locking. This means they’re faster as they don’t require the overhead.
You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don’t need to define your temp table structure upfront.
What is the difference between Char,Varchar and nVarchar datatypes ?
char[(n)] – Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
varchar[(n)] – Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
nvarchar(n) – Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
What is the difference between STUFF and REPLACE functions in Sql server ?
The Stuff function is used to replace characters in a string. This function can be used to delete a certain length of the string and replace it with a new string.
Syntax – STUFF (string_expression, start, length, replacement_characters)
Ex – SELECT STUFF(‘I am a bad boy’,8,3,’good’)
Output – “I am a good boy”
REPLACE function replaces all occurrences of the second given string expression in the first string expression with a third expression.
Syntax – REPLACE (String, StringToReplace, StringTobeReplaced)
Ex – REPLACE(“Roopesh”,”pe”,”ep”)
Output – “Rooepsh” – You can see PE is replaced with EP in the output.
What are Magic Tables ?
Sometimes we need to know about the data which is being inserted/deleted by triggers in database. Whenever a trigger fires in response to the INSERT, DELETE, or UPDATE statement, two special tables are created. These are the inserted and the deleted tables. They are also referred to as the magic tables. These are the conceptual tables and are similar in structure to the table on which trigger is defined (the trigger table).
The inserted table contains a copy of all records that are inserted in the trigger table.
The deleted table contains all records that have been deleted from deleted from the trigger table.
Whenever any updation takes place, the trigger uses both the inserted and deleted tables.
What is Table Expressions in Sql Server ?
Table Expressions are subqueries that are used where a TABLE is Expected. There are TWO types of table Expressions.
Derived tables
Common Table Expressions.
What is Derived Table ?
Derived tables are table expression which appears in FROM Clause of a Query. PFB an example of the same.
select * from (Select Month(date) as Month,Year(Date) as Year from table1) AS Table2
In the above query the subquery in FROM Clause “(Select Month(date) as Month,Year(Date) as Year from table1) ” is called Derived Table.
What is CTE or Common Table Expression ?
Common table expression (CTE) is a temporary named result set that you can reference within a
SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement. There are TWO types of CTEs in Sql Server –
Recursive
Non Recursive.
Difference between SmallDateTime and DateTime datatypes in Sql server ?
Both the data types are meant to specify date and time but these two has slight differences and pfb the same.
DateTime occupies 4 Bytes of data where as SmallDateTime occupies only 2 Bytes.
DateTime ranges from 01/01/1753 to 12/31/9999 where as SmallDateTime ranges from 01/01/1900 to 06/06/2079.
What is SQL_VARIANT Datatype ?
The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values. (The only types of values that cannot be stored are TIMESTAMP values.) Each value of an SQL_VARIANT column has two parts: the data value and the information that describes the value. (This information contains all properties of the actual data type of the value, such as length, scale, and precision.)
What are Linked Servers ?
Linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:
The ability to access data from outside of SQL Server.
The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
The ability to address diverse data sources similarly.
Can connect to MOLAP databases too.
What is the Difference between the functions COUNT and COUNT_BIG ?
Both Count and Count_Big functions are used to count the number of rows in a table and the only difference is what it returns.
Count returns INT datatype value where as Count_Big returns BIGINT datatype value.
Count is used if the rows in a table are less where as Count_Big will be used when the numbenr of records are in millions or above.
Syntax –
Count – Select count(*) from tablename
Count_Big – Select Count_Big(*) from tablename
How to insert values EXPLICITLY to an Identity Column ?
This has become a common question these days in interviews. Actually we cannot Pass values to Identity column and you will get the following error message when you try to pass value.
Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'tablename' when IDENTITY_INSERT is set to OFF.
To pass an external value we can use the property IDENTITY_INSERT. PFB the sysntax of the same.
SET IDENTITY_INSERT <tablename> ON;
Write your Insert statement here by passing external values to the IDENTITY column.
Once the data is inserted then remember to SET the property to OFF.
How to RENAME a table and column in SQL ?
We can rename a table or a column in SQL using the System stored procedure SP_RENAME. PFB the sample queries.
Table – EXEC sp_rename @objname = department, @newname = subdivision
Column – EXEC sp_rename @objname = ‘sales.order_no’ , @newname = ordernumber
How to rename a database ?
To rename a database please use the below syntax.
USE master;
GO
ALTER DATABASE databasename
Modify Name = newname ;
GO
What is the use the UPDATE_STATISTICS command ?
UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account.
How to read the last record from a table with Identity Column ?
We can get the same using couple of ways and PFB the same.
First –
SELECT *
FROM TABLE
WHERE ID = IDENT_CURRENT(‘TABLE’)
Second –
SELECT *
FROM TABLE
WHERE ID = (SELECT MAX(ID) FROM TABLE)
Third –
select top 1 * from TABLE_NAME order by ID desc
What is Worktable ?
A worktable is a temporary table used internally by SQL Server to process the intermediate results of a query. Worktables are created in the tempdb database and are dropped automatically after query execution. Thease table cannot be seen as these are created while a query executing and dropped immediately after the execution of the query.
What is recursive stored procedure?
SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels.
CREATE PROCEDURE [dbo].[Fact]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively)
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN
GO
CREATE PROCEDURE [dbo].[Fact]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively)
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN
GO
What are the differences between local and global temporary tables?
Local temporary tables are visible when there is a connection, and are deleted when the connection is closed.
CREATE TABLE #<tablename>
CREATE TABLE #<tablename>
Global temporary tables are visible to all users, and are deleted when the connection that created it is closed.
CREATE TABLE ##<tablename>
CREATE TABLE ##<tablename>
What are the two authentication modes in SQL Server?
There are two authentication modes –
Windows Mode
Mixed Mode
Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page.
What Is SQL Profiler?
SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.
What is CHECK constraint?
A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.
Can SQL servers linked to other servers?
SQL server can be connected to any database which has OLE-DB provider to give a link. Example: Oracle has OLE-DB provider which has link to connect with the SQL server group.
What is COALESCE in SQL Server?
COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.
Example –
Select COALESCE(empno, empname, salary) from employee;
Select COALESCE(empno, empname, salary) from employee;
How exceptions can be handled in SQL Server Programming?
Exceptions are handled using TRY—-CATCH constructs and it is handles by writing scripts inside the TRY block and error handling in the CATCH block.
What is the purpose of FLOOR function?
FLOOR function is used to round up a non-integer value to the previous least integer. Example is given
FLOOR(6.7)
FLOOR(6.7)
Returns 6.
Can we check locks in database? If so, how can we do this lock check?
Yes, we can check locks in the database. It can be achieved by using in-built stored procedure called sp_lock.
What is the use of SIGN function?
SIGN function is used to determine whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0.
Example –
SIGN(-35) returns -1
1
SIGN(-35) returns -1
What is sub query and its properties?
A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as
A sub query should not have order by clause
A sub query should be placed in the right hand side of the comparison operator of the main query
A sub query should be enclosed in parenthesis because it needs to be executed first before the main query
More than one sub query can be included
What are the types of sub query?
There are three types of sub query –
Single row sub query which returns only one row
Multiple row sub query which returns multiple rows
Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.
What is SQL server agent?
The SQL Server agent plays a vital role in day to day tasks of SQL server administrator(DBA). Server agent’s purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.
What are scheduled tasks in SQL Server?
Scheduled tasks or jobs are used to automate processes that can be run on a scheduled time at a regular interval. This scheduling of tasks helps to reduce human intervention during night time and feed can be done at a particular time. User can also order the tasks in which it has to be generated.
What is a Trigger?
Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, delete and update operations.
Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover.
Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.
What are the advantages of database normalization ?
Benefits of normalizing the database are
No need to restructure existing tables for new data.
Reducing repetitive entries.
Reducing required storage space
Increased speed and flexibility of queries.
 |
| Oracle Frequently Asked SQL Server Interview Questions Answers |
What is Denormalization ?
For optimizing the performance of a database by adding redundant data or by grouping data is called de-normalization.
It is sometimes necessary because current DBMSs implement the relational model poorly.
In some cases, de-normalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.
A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De‐normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
Query to Pull ONLY duplicate records from table ?
There are many ways of doing the same and let me explain one here. We can acheive this by using the keywords GROUP and HAVING. The following query will extract duplicate records from a specific column of a particular table.
Select specificColumn
FROM particluarTable
GROUP BY specificColumn
HAVING COUNT(*) > 1
This will list all the records that are repeated in the column specified by “specificColumn” of a “particlarTable”.
Types of Joins in SQL SERVER ?
There are 3 types of joins in Sql server.
Inner Join
Outer Join
Cross Join
Outer join again classified into 3 types.
Right Outer Join
Left Outer Join
Full Outer Join.
Whar are the differences between Temp table and Table variable ?
This is very routine question in interviews. Let’s see the major differences between these two.
Table variables are Transaction neutral where as Temp tables are Transaction bound. For example if we declare and load data into a temp table and table variable in a transaction and if the transaction is ROLLEDBACK, still the table variable will have the data loaded where as Temp table will not be available as the transaction is rolled back.
Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
Table variables don’t participate in transactions, logging or locking. This means they’re faster as they don’t require the overhead.
You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don’t need to define your temp table structure upfront.
What is the difference between Char,Varchar and nVarchar datatypes ?
char[(n)] – Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
varchar[(n)] – Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
nvarchar(n) – Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
What is the difference between STUFF and REPLACE functions in Sql server ?
The Stuff function is used to replace characters in a string. This function can be used to delete a certain length of the string and replace it with a new string.
Syntax – STUFF (string_expression, start, length, replacement_characters)
Ex – SELECT STUFF(‘I am a bad boy’,8,3,’good’)
Output – “I am a good boy”
REPLACE function replaces all occurrences of the second given string expression in the first string expression with a third expression.
Syntax – REPLACE (String, StringToReplace, StringTobeReplaced)
Ex – REPLACE(“Roopesh”,”pe”,”ep”)
Output – “Rooepsh” – You can see PE is replaced with EP in the output.
What are Magic Tables ?
Sometimes we need to know about the data which is being inserted/deleted by triggers in database. Whenever a trigger fires in response to the INSERT, DELETE, or UPDATE statement, two special tables are created. These are the inserted and the deleted tables. They are also referred to as the magic tables. These are the conceptual tables and are similar in structure to the table on which trigger is defined (the trigger table).
The inserted table contains a copy of all records that are inserted in the trigger table.
The deleted table contains all records that have been deleted from deleted from the trigger table.
Whenever any updation takes place, the trigger uses both the inserted and deleted tables.
What is Table Expressions in Sql Server ?
Table Expressions are subqueries that are used where a TABLE is Expected. There are TWO types of table Expressions.
Derived tables
Common Table Expressions.
What is Derived Table ?
Derived tables are table expression which appears in FROM Clause of a Query. PFB an example of the same.
select * from (Select Month(date) as Month,Year(Date) as Year from table1) AS Table2
In the above query the subquery in FROM Clause “(Select Month(date) as Month,Year(Date) as Year from table1) ” is called Derived Table.
What is CTE or Common Table Expression ?
Common table expression (CTE) is a temporary named result set that you can reference within a
SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement. There are TWO types of CTEs in Sql Server –
Recursive
Non Recursive.
Difference between SmallDateTime and DateTime datatypes in Sql server ?
Both the data types are meant to specify date and time but these two has slight differences and pfb the same.
DateTime occupies 4 Bytes of data where as SmallDateTime occupies only 2 Bytes.
DateTime ranges from 01/01/1753 to 12/31/9999 where as SmallDateTime ranges from 01/01/1900 to 06/06/2079.
What is SQL_VARIANT Datatype ?
The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values. (The only types of values that cannot be stored are TIMESTAMP values.) Each value of an SQL_VARIANT column has two parts: the data value and the information that describes the value. (This information contains all properties of the actual data type of the value, such as length, scale, and precision.)
What are Linked Servers ?
Linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:
The ability to access data from outside of SQL Server.
The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
The ability to address diverse data sources similarly.
Can connect to MOLAP databases too.
What is the Difference between the functions COUNT and COUNT_BIG ?
Both Count and Count_Big functions are used to count the number of rows in a table and the only difference is what it returns.
Count returns INT datatype value where as Count_Big returns BIGINT datatype value.
Count is used if the rows in a table are less where as Count_Big will be used when the numbenr of records are in millions or above.
Syntax –
Count – Select count(*) from tablename
Count_Big – Select Count_Big(*) from tablename
How to insert values EXPLICITLY to an Identity Column ?
This has become a common question these days in interviews. Actually we cannot Pass values to Identity column and you will get the following error message when you try to pass value.
Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'tablename' when IDENTITY_INSERT is set to OFF.
To pass an external value we can use the property IDENTITY_INSERT. PFB the sysntax of the same.
SET IDENTITY_INSERT <tablename> ON;
Write your Insert statement here by passing external values to the IDENTITY column.
Once the data is inserted then remember to SET the property to OFF.
How to RENAME a table and column in SQL ?
We can rename a table or a column in SQL using the System stored procedure SP_RENAME. PFB the sample queries.
Table – EXEC sp_rename @objname = department, @newname = subdivision
Column – EXEC sp_rename @objname = ‘sales.order_no’ , @newname = ordernumber
How to rename a database ?
To rename a database please use the below syntax.
USE master;
GO
ALTER DATABASE databasename
Modify Name = newname ;
GO
What is the use the UPDATE_STATISTICS command ?
UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account.
How to read the last record from a table with Identity Column ?
We can get the same using couple of ways and PFB the same.
First –
SELECT *
FROM TABLE
WHERE ID = IDENT_CURRENT(‘TABLE’)
Second –
SELECT *
FROM TABLE
WHERE ID = (SELECT MAX(ID) FROM TABLE)
Third –
select top 1 * from TABLE_NAME order by ID desc
What is Worktable ?
A worktable is a temporary table used internally by SQL Server to process the intermediate results of a query. Worktables are created in the tempdb database and are dropped automatically after query execution. Thease table cannot be seen as these are created while a query executing and dropped immediately after the execution of the query.
What is recursive stored procedure?
SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels.
CREATE PROCEDURE [dbo].[Fact]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively)
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN
GO
CREATE PROCEDURE [dbo].[Fact]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively)
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN
GO
What are the differences between local and global temporary tables?
Local temporary tables are visible when there is a connection, and are deleted when the connection is closed.
CREATE TABLE #<tablename>
CREATE TABLE #<tablename>
Global temporary tables are visible to all users, and are deleted when the connection that created it is closed.
CREATE TABLE ##<tablename>
CREATE TABLE ##<tablename>
What are the two authentication modes in SQL Server?
There are two authentication modes –
Windows Mode
Mixed Mode
Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page.
What Is SQL Profiler?
SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.
What is CHECK constraint?
A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.
Can SQL servers linked to other servers?
SQL server can be connected to any database which has OLE-DB provider to give a link. Example: Oracle has OLE-DB provider which has link to connect with the SQL server group.
What is COALESCE in SQL Server?
COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.
Example –
Select COALESCE(empno, empname, salary) from employee;
Select COALESCE(empno, empname, salary) from employee;
How exceptions can be handled in SQL Server Programming?
Exceptions are handled using TRY—-CATCH constructs and it is handles by writing scripts inside the TRY block and error handling in the CATCH block.
What is the purpose of FLOOR function?
FLOOR function is used to round up a non-integer value to the previous least integer. Example is given
FLOOR(6.7)
FLOOR(6.7)
Returns 6.
Can we check locks in database? If so, how can we do this lock check?
Yes, we can check locks in the database. It can be achieved by using in-built stored procedure called sp_lock.
What is the use of SIGN function?
SIGN function is used to determine whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0.
Example –
SIGN(-35) returns -1
1
SIGN(-35) returns -1
What is sub query and its properties?
A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as
A sub query should not have order by clause
A sub query should be placed in the right hand side of the comparison operator of the main query
A sub query should be enclosed in parenthesis because it needs to be executed first before the main query
More than one sub query can be included
What are the types of sub query?
There are three types of sub query –
Single row sub query which returns only one row
Multiple row sub query which returns multiple rows
Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.
What is SQL server agent?
The SQL Server agent plays a vital role in day to day tasks of SQL server administrator(DBA). Server agent’s purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.
What are scheduled tasks in SQL Server?
Scheduled tasks or jobs are used to automate processes that can be run on a scheduled time at a regular interval. This scheduling of tasks helps to reduce human intervention during night time and feed can be done at a particular time. User can also order the tasks in which it has to be generated.
What is a Trigger?
Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, delete and update operations.
https://mytecbooks.blogspot.com/2019/01/oracle-frequently-asked-sql-server.html