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.
What are the types of Triggers?
There are four types of triggers and they are:
Insert
Delete
Update
Instead of
What is Collation?
Collation is defined to specify the sort order in a table. There are three types of sort order –
Case sensitive
Case Insensitive
Binary
What is the difference between UNION and UNION ALL?
• UNION: To select related information from two tables UNION command is used. It is similar to JOIN command.
• UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.
How Global temporary tables are represented and its scope?
Global temporary tables are represented with ## before the table name. Scope will be the outside the session whereas local temporary tables are inside the session. Session ID can be found using @@SPID.
What are the differences between Stored Procedure and the dynamic SQL?
Stored Procedure is a set of statements which is stored in a compiled form. Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply execute during run time.
What is the command used to get the version of SQL Server?
Select SERVERPROPERTY(‘productversion’)
Select SERVERPROPERTY(‘productversion’)
is used to get the version of SQL Server.
What is the use of =,==,=== operators?
= is used to assign one value or variable to another variable. == is used for comparing two strings or numbers. === is used to compare only string with the string and number with numbers.
What is ISNULL() operator?
ISNULL function is used to check whether value given is NULL or not NULL in sql server. This function also provides to replace a value with the NULL.
What is the use of FOR Clause?
FOR clause is mainly used for XML and browser options. This clause is mainly used to display the query results in XML format or in browser.
What is UPDATE_STATISTICS command?
UPDATE_STATISTICS command is used to update the indexes on the tables when there is a large amount of deletions or modifications or bulk copy occurred in indexes.
What are the methods used to protect against SQL injection attack?
Following are the methods used to protect against SQL injection attack:
Use Parameters for Stored Procedures
Filtering input parameters
Use Parameter collection with Dynamic SQL
In like clause, user escape characters
What is Filtered Index?
Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with WHERE clause, then it is called Filtered Index.
What is the use of SET NOCOUNT ON/OFF statement?
By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).
Which SQL server table is used to hold the stored procedure scripts?
Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure. Name of the stored procedure is saved in the table called Sys.Procedures.
What are Magic Tables in SQL Server?
During DML operations like Insert, Delete, and Update SQL Server create magic tables to hold the values during the DML operations. These magic tables are used inside the triggers for data transaction.
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.
 |
| Appian Corporation Most Frequently Asked Latest SQL Server Interview Questions Answers |
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.
Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, delete and update operations.
What are the types of Triggers?
There are four types of triggers and they are:
Insert
Delete
Update
Instead of
What is Collation?
Collation is defined to specify the sort order in a table. There are three types of sort order –
Case sensitive
Case Insensitive
Binary
What is the difference between UNION and UNION ALL?
• UNION: To select related information from two tables UNION command is used. It is similar to JOIN command.
• UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.
How Global temporary tables are represented and its scope?
Global temporary tables are represented with ## before the table name. Scope will be the outside the session whereas local temporary tables are inside the session. Session ID can be found using @@SPID.
What are the differences between Stored Procedure and the dynamic SQL?
Stored Procedure is a set of statements which is stored in a compiled form. Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply execute during run time.
What is the command used to get the version of SQL Server?
Select SERVERPROPERTY(‘productversion’)
Select SERVERPROPERTY(‘productversion’)
is used to get the version of SQL Server.
What is the use of =,==,=== operators?
= is used to assign one value or variable to another variable. == is used for comparing two strings or numbers. === is used to compare only string with the string and number with numbers.
What is ISNULL() operator?
ISNULL function is used to check whether value given is NULL or not NULL in sql server. This function also provides to replace a value with the NULL.
What is the use of FOR Clause?
FOR clause is mainly used for XML and browser options. This clause is mainly used to display the query results in XML format or in browser.
What is UPDATE_STATISTICS command?
UPDATE_STATISTICS command is used to update the indexes on the tables when there is a large amount of deletions or modifications or bulk copy occurred in indexes.
What are the methods used to protect against SQL injection attack?
Following are the methods used to protect against SQL injection attack:
Use Parameters for Stored Procedures
Filtering input parameters
Use Parameter collection with Dynamic SQL
In like clause, user escape characters
What is Filtered Index?
Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with WHERE clause, then it is called Filtered Index.
By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).
Which SQL server table is used to hold the stored procedure scripts?
Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure. Name of the stored procedure is saved in the table called Sys.Procedures.
What are Magic Tables in SQL Server?
During DML operations like Insert, Delete, and Update SQL Server create magic tables to hold the values during the DML operations. These magic tables are used inside the triggers for data transaction.