SQL Server questions for interview Part4

-

What are the ways to code efficient transactions?
Some ways and guidelines to code efficient transactions:


  • ·         Do not ask for an input from a user during a transaction.
  • ·         Get all input needed for a transaction before starting the transaction.
  • ·         Transaction should be atomic
  • ·         Transactions should be as short and small as possible.
  • ·         Rollback a transaction if a user intervenes and re-starts the transaction.
  • ·         Transaction should involve a small amount of data as it needs to lock the number of rows involved.
  • ·         Avoid transactions while browsing through data.

What are the differences among batches, stored procedures, and triggers?
               
Batch
Stored Procedure
Triggers
Collection or group of SQL statements. All statements of a batch are compiled into one executional unit called execution plan. All statements are then executed statement by statement.
It’s a collection or group of SQL statements that’s compiled once but used many times.
It’s a type of Stored procedure that cannot be called directly. Instead it fires when a row is updated, deleted, or inserted.


What security features are available for stored procedures? 
Security features for stored procedures:

  • ·       Grants users permissions to execute a stored procedure irrespective of the related tables.
  • ·       Grant users users permission to work with a stored procedure to access a restricted set of data yet no give them permissions to update or select underlying data.
  • ·        Stored procedures can be granted execute permissions rather than setting permissions on data itself.
  • ·       Provide more granular security control through stored procedures rather than complete control on underlying data in tables.
What are the instances when triggers are appropriate? 
Scenarios for using triggers:

  • ·         To create a audit log of database activity.
  • ·         To apply business rules.
  • ·         To apply some calculation on data from tables which is not stored in them.
  • ·         To enforce referential integrity.
  • ·         Alter data in a third party application
  • ·         To execute SQL statements as a result of an event/condition automatically.
What are the restrictions applicable while creating views? 
Restrictions applicable while creating views:

  • ·         A view cannot be indexed.
  • ·         A view cannot be Altered or renamed. Its columns cannot be renamed.
  • ·         To alter a view, it must be dropped and re-created.
  • ·         ANSI_NULLS and QUOTED_IDENTIFIER options should be turned on to create a view.
  • ·         All tables referenced in a view must be part of the same database.
  • ·         Any user defined functions referenced in a view must be created with SCHEMABINDING option.
  • ·         Cannot use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, COMPUTE BY in views.
What are the events recorded in a transaction log? 
Events recorded in a transaction log:
  • Broker event category includes events produced by Service Broker.
  • Cursors event category includes cursor operations events.
  • CLR event category includes events fired by .Net CLR objects.
  • Database event category includes events of data.log files shrinking or growing on their own.
  • Errors and Warning event category includes SQL Server warnings and errors.
  • Full text event category include events occurred when text searches are started, interrupted, or stopped.
  • Locks event category includes events caused when a lock is acquired, released, or cancelled.
  • Object event category includes events of database objects being created, updated or deleted.
  • OLEDB event category includes events caused by OLEDB calls.
  • Performance event category includes events caused by DML operators.
  • Progress report event category includes Online index operation events.
  • Scans event category includes events notifying table/index scanning.
  • Security audit event category includes audit server activities.
  • Server event category includes server events.
  • Sessions event category includes connecting and disconnecting events of clients to SQL Server.
  • Stored procedures event category includes events of execution of Stored procedures.
  • Transactions event category includes events related to transactions.
  • TSQL event category includes events generated while executing TSQL statements.
  • User configurable event category includes user defined events.
Describe when checkpoints are created in a transaction log. 
Activities causing checkpoints are:

  • ·         When a checkpoint is explicitly executed.
  • ·         A logged operation is performed on the database.
  • ·         Database files have been altered using Alter Database command.
  • ·         SQL Server has been stopped explicitly or on its own.
  • ·         SQL Server periodically generates checkpoints.
  • ·         Backup of a database is taken. 


Define Truncate and Delete commands.

TRUNCATE
DELETE
This is also a logged operation but in terms of deallocation of data pages.
This is a logged operation for every row.
Cannot TRUNCATE a table that has foreign key constraints.
Any row not violating a constraint can be Deleted.
Resets identity column to the default starting value.
Does not reset the identity column. Starts where it left from last.
Removes all rows from a table.
Used delete all or selected rows from a table based on WHERE clause.
Cannot be Rolled back.
Need to Commit or Rollback
DDL command
DML command


Sql Server Interview Questions:


Comments

Post a Comment