Home
MS SQL
Interview FAQs

FAQs of level

No, we can’t see definition of encrypted stored procedure in Activity Monitor.
False - Depending on the situation the OUTER JOIN may or may not be faster than a NOT EXISTS statement.
No - The SQL Server Express Edition does not have a SQL Server Agent Service.

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, following conditions had to be met:

  1. No two rows can have the same primary key value.
  2. Every row must have a primary key value
  3. The primary key field cannot be null
  4. Values in primary key columns can never be modified or updated


Properties of the transaction can be summarized as ACID Properties.
Atomicity
A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
Consistency
The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.
Isolation
Every transaction should operate as if it is the only transaction in the system.
Durability
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.


@@identity (): function returns the last identity created in the same session.

 scope_identity (): function returns the last identity created in the same session and the same scope.

ident_current (name): returns the last identity created for a specific table or view in any session.

identity (): function is not used to get an identity; it is used to create an identity in a select...into query.

For more http://www.aspdotnet-suresh.com/2015/07/sql-server-difference-between-identity-scope-identity-ident-current-with-examples.html#more


SQL statements are broadly classified into three. They are-
DDL – Data Definition Language
DDL is used to define the structure that holds the data. For example, Create, Alter, Drop and Truncate table.
DML– Data Manipulation Language
DML is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table. Select statement is considered as a limited version of DML, since it can not change data in the database. But it can perform operations on data retrieved from DBMS, before the results are returned to the calling function.
DCL– Data Control Language 
DCL is used to control the visibility of data like granting database access and set privileges to create tables etc. Example - Grant, Revoke access permission to the user to access data in database.

INTERSECT - gives you the final result set where values in both of the tables match
EXCEPT - gives you the final result set where data exists in the first dataset and not in the second dataset
The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.  


  • Stored procedure can reduce network traffic and latency, boosting application performance.
  • Stored procedure execution plans can be reused, staying cached in SQL Server memory, reducing server overhead.
  • Stored procedures help promote code reuse.
  • Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
  • Stored procedures provide better security to your data.

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Scheduled Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

PreviousDisplaying 10 of 15Next
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*