Explore topic-wise InterviewSolutions in .

The COLUMN with a defined identity in turn means that there is an unique value that the system ASSIGNS to the SPECIFIC column. This is SIMILAR to the AUTONumber property of the ACCESS backend.

The column with a defined identity in turn means that there is an unique value that the system assigns to the specific column. This is similar to the AUTONumber property of the Access backend.

The check points are restoration points that indicate the specific state of the DATABASE. When there is some failure in the database that is occurring before the NEXT check point, the database can be REVERTED back to the previous check point and thus the database would STILL be CONSISTENT.

The check points are restoration points that indicate the specific state of the database. When there is some failure in the database that is occurring before the next check point, the database can be reverted back to the previous check point and thus the database would still be consistent.

The write-ahead log is the logging system that just UPDATES the buffer CACHE of the database for the transactions and updates the logs and only then the actual changes are incorporated in the actual database. This is the reason why it is called “write ahead”. This helps in maintaining the consistency in the database. This can also be USEFUL in getting the actual database values even in CASE of failures.

The write-ahead log is the logging system that just updates the buffer cache of the database for the transactions and updates the logs and only then the actual changes are incorporated in the actual database. This is the reason why it is called “write ahead”. This helps in maintaining the consistency in the database. This can also be useful in getting the actual database values even in case of failures.

The Schema refers to the overall structure of the database with all related INFORMATION like users, access privileges, interaction information between the different tables etc. The CREATE SCHEMA is the COMMAND that can be USED to create the schema in the SQL Server. This when done can be used to re DEPLOY the same database in another system for demonstrative or test purposes. This holds intact the underlying framework over which the database has been built.

The Schema refers to the overall structure of the database with all related information like users, access privileges, interaction information between the different tables etc. The CREATE SCHEMA is the command that can be used to create the schema in the SQL Server. This when done can be used to re deploy the same database in another system for demonstrative or test purposes. This holds intact the underlying framework over which the database has been built.

Yes we can USE the stored PROCEDURE sp_OACreate to INITIATE the external COM OBJECT from the T-SQL.

Yes we can use the stored procedure sp_OACreate to initiate the external COM object from the T-SQL.

Replication refers to the moving or copying of the DATABASE elements from one system to ANOTHER. This can be done in the SQL SERVER in one of the following methods:
● TRANSACTIONAL.
● Snapshot.
● Merge replication.

Replication refers to the moving or copying of the database elements from one system to another. This can be done in the SQL Server in one of the following methods:
● Transactional.
● Snapshot.
● Merge replication.

STATISTICS represent the uniqueness for the indexes that are being used for selecting the records. This can make the query execution pretty efficient. The tables that we are DEALING with if truncated and repopulated, there is a good chance that the indexes and statistics are out of sync and this is when we have to update the statistics. There are also other situations like when the table has been modified and lot of rows have been added recently or like when a server has been updated with different version of software. These also give us the reason to USE the UPDATE_STATISTICS, DBCC SHOW_STATISTICS etc to update it ACCORDINGLY.

Statistics represent the uniqueness for the indexes that are being used for selecting the records. This can make the query execution pretty efficient. The tables that we are dealing with if truncated and repopulated, there is a good chance that the indexes and statistics are out of sync and this is when we have to update the statistics. There are also other situations like when the table has been modified and lot of rows have been added recently or like when a server has been updated with different version of software. These also give us the reason to use the UPDATE_STATISTICS, DBCC SHOW_STATISTICS etc to update it accordingly.

The SQLServer.exe is the executable which can be CALLED in the COMMAND PROMPT with the PARAMETERS -m and -f. These are the options that will start the SQL server in the user MODE and minimal configuration mode respectively.

The SQLServer.exe is the executable which can be called in the command prompt with the parameters -m and -f. These are the options that will start the SQL server in the user mode and minimal configuration mode respectively.

The isolation represents the WAY of separating the database from the effects of NETWORK accesses, thereby maintaining the consistency. The different levels of isolation are:

● read committed: This level of isolation uses the shared locks and the reads to the database GIVE the constant and consistent VALUES.
● read UNCOMMITTED: No locks implemented. This is the least effective isolation level.
● repeatable read: There are locks over the rows and values but the updates are maintained as a separate phantom row which is the next set of values for the specific record. Values can change within a specific transaction of a SQL function.
● SERIALIZABLE reads: This is the implementation of pure lock mechanism where one specific transaction is not allowed access to specific record before another one completes.

The isolation represents the way of separating the database from the effects of network accesses, thereby maintaining the consistency. The different levels of isolation are:

● read committed: This level of isolation uses the shared locks and the reads to the database give the constant and consistent values.
● read uncommitted: No locks implemented. This is the least effective isolation level.
● repeatable read: There are locks over the rows and values but the updates are maintained as a separate phantom row which is the next set of values for the specific record. Values can change within a specific transaction of a SQL function.
● SERIALIZABLE reads: This is the implementation of pure lock mechanism where one specific transaction is not allowed access to specific record before another one completes.

The SQL server offers 4 TYPES of backups to suit the need of the administrator.

● Complete BACKUP- The complete back up is just zipping the content of the entire DATABASE in terms of the different tables and procedures etc. This back up can server as an independent entity that can be restored in different systems with just the base SQL server installed.
● Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server. This way the details of the database getting UPDATED is obtained. This cannot be a stand-alone back up mechanism. But can save a lot of time if we already have the file system related to the DB backed up on the new deployment server.
● Differential backup: This is a subset of the complete backup, where only the modified datasets are backed up. This can save the time when we are just trying to maintain a backup server to MAIN server.
● File backup: This is the quickest way to take the backup of entire database. Instead of taking in the data actually stored in DB, the files are backed up and the file system thus obtained when combined with the transaction logs of the original system will render the database that we are trying to back up.

The SQL server offers 4 types of backups to suit the need of the administrator.

● Complete backup- The complete back up is just zipping the content of the entire database in terms of the different tables and procedures etc. This back up can server as an independent entity that can be restored in different systems with just the base SQL server installed.
● Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server. This way the details of the database getting updated is obtained. This cannot be a stand-alone back up mechanism. But can save a lot of time if we already have the file system related to the DB backed up on the new deployment server.
● Differential backup: This is a subset of the complete backup, where only the modified datasets are backed up. This can save the time when we are just trying to maintain a backup server to main server.
● File backup: This is the quickest way to take the backup of entire database. Instead of taking in the data actually stored in DB, the files are backed up and the file system thus obtained when combined with the transaction logs of the original system will render the database that we are trying to back up.

Blocking is the deadlock situation when two SQL connections race to obtain the control over the same set of rows in conflicting terms. This can be tracked by the status of WAIT present in the SP_LOCK PROCEDURE’s OUTPUT. All the active LOCKS and the different rows that are being involved are shown in this output. The identification of the connections involved in the specific row contention lock can be identified with sp_who and sp_who2 procedures. This way the causal agents of the blocking is identifies. KILL command issued against the specific SQL CONNECTION CAUSING the BLOCK can resolve the issue. But the PERMANENT solution lies in the proper design of the application code to execute in concurrence across different connections.

Blocking is the deadlock situation when two SQL connections race to obtain the control over the same set of rows in conflicting terms. This can be tracked by the status of WAIT present in the SP_LOCK procedure’s output. All the active LOCKS and the different rows that are being involved are shown in this output. The identification of the connections involved in the specific row contention lock can be identified with sp_who and sp_who2 procedures. This way the causal agents of the blocking is identifies. KILL command issued against the specific SQL connection causing the BLOCK can resolve the issue. But the permanent solution lies in the proper design of the application code to execute in concurrence across different connections.

● Number of joins and use of complex views/cursors have to be reduced.
● The use of the stored procedures and INDEXES have to be maximized.
● The optimized use of the complex conditional CHECKS and computer columns have to be in place.
● TRACKING of performance analysis for the query helps us in identifying the right ASPECTS to optimize.

● Number of joins and use of complex views/cursors have to be reduced.
● The use of the stored procedures and indexes have to be maximized.
● The optimized use of the complex conditional checks and computer columns have to be in place.
● Tracking of performance analysis for the query helps us in identifying the right aspects to optimize.

SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in DATABASES which is ORGANIZED into logical components visible to the end USERS. It’s only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables.

Every SQL Server INSTANCE has primarily 4 system database i.e. master, model, tempdb and MSDB. All other databases are user created databases as per their needs and requirements.

A single SQL Server instance is capable of handling thousands of users working on multiple databases.

SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in databases which is organized into logical components visible to the end users. It’s only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables.

Every SQL Server instance has primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created databases as per their needs and requirements.

A single SQL Server instance is capable of handling thousands of users working on multiple databases.

Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files:

Primary: starting point of a database. It ALSO points to other files in database.
Extension: .mdf

SECONDARY: All data files except primary data file is a part of secondary files.
Extension: .NDF

Log files: All log information used to recover database.
Extension: .LDF

Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files:

Primary: starting point of a database. It also points to other files in database.
Extension: .mdf

Secondary: All data files except primary data file is a part of secondary files.
Extension: .ndf

Log files: All log information used to recover database.
Extension: .ldf

When a cursor is declared as DYNAMIC, the cursor reflects all CHANGES made to the base tables as the cursor is scrolled around.

The dynamic option does not SUPPORT ABSOLUTE FETCH.

When a cursor is declared as DYNAMIC, the cursor reflects all changes made to the base tables as the cursor is scrolled around.

The dynamic option does not support ABSOLUTE FETCH.

Using the SCROLL keyword while declaring a cursor allows fetching of ROWS in any sequence.

EXAMPLE:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee;

The active SET of the cursor is stored can be accessed in any order without the need of opening and closing the cursor. The Scroll cursors can be set for select and function cursors but not insert or UPDATE statements.

Using the SCROLL keyword while declaring a cursor allows fetching of rows in any sequence.

Example:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee;

The active set of the cursor is stored can be accessed in any order without the need of opening and closing the cursor. The Scroll cursors can be set for select and function cursors but not insert or update statements.

A schema is USED to create database objects. It can be CREATED USING CREATE SCHEMA statement. The objects created can be moved between schemas. Multiple database users can share a SINGLE default schema.

CREATE SCHEMA sample;

Table creation

A schema is used to create database objects. It can be created using CREATE SCHEMA statement. The objects created can be moved between schemas. Multiple database users can share a single default schema.

CREATE SCHEMA sample;

Table creation

Isolation in database DEFINES how and when changes made by ONE transaction can be VISIBLE to other transactions. Different isolation levels are:

•  Serializable
•  Repeatable read
•  Read COMMITTED
•  Read UNCOMMITTED

Isolation in database defines how and when changes made by one transaction can be visible to other transactions. Different isolation levels are:

Complete database backup: This type of backup will backup all the INFORMATION in the database. Used most commonly for disaster recovery and takes the longest TIME to backup.

Differential databse backup: The database is divided into partitions that have been modified since last complete backup. Most suitable for large databases. The most recent differential backup contains the changes from previous backups.

TRANSACTION log backups: Backups only the changes logged in the transaction log. The transaction log has all changes logged about a database. Once the changes are ACCOMMODATED on the database, the log is truncated or backed up.

File/File GROUP backups: used to recover individual files or file groups. Each filegroup can be individually backed up. This helps in recovery only the required file or filegroup for disaster recovery.

Complete database backup: This type of backup will backup all the information in the database. Used most commonly for disaster recovery and takes the longest time to backup.

Differential databse backup: The database is divided into partitions that have been modified since last complete backup. Most suitable for large databases. The most recent differential backup contains the changes from previous backups.

Transaction log backups: Backups only the changes logged in the transaction log. The transaction log has all changes logged about a database. Once the changes are accommodated on the database, the log is truncated or backed up.

File/File Group backups: used to recover individual files or file groups. Each filegroup can be individually backed up. This helps in recovery only the required file or filegroup for disaster recovery.

Blocking occurs when two or more rows are LOCKED by one SQL CONNECTION and a SECOND connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.

Troubleshooting blocking:

•  SQL scripts can be WRITTEN that constantly monitor the state of locking and blocking on SQL Server
•  The common blocking scenarios must be identified and RESOLVED.
•  The scripts output must be checked constantly.
•  The SQL profilers data must be examined regularly to detect blocking.

Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.

Troubleshooting blocking:

DEADLOCK OCCUR in INTERCONNECTION n/w when GROUP of process are unable to act because of waiting each other to release some resource.

live lock packets CONTINUE to move through n/w, but does not advance towards destination.

Deadlock occur in interconnection n/w when group of process are unable to act because of waiting each other to release some resource.

live lock packets continue to move through n/w, but does not advance towards destination.

•  Maximum use of INDEXES, STORED procures should be DONE.
•  Avoid excessive use of complicated joins and cursors.
•  Avoid using CONDITIONAL operators using COLUMNS of different tables.
•  Make use of computed columns and rewriting the query.