Asp.net programming we consider all important topic for knowledge about controls,web Services, JSON, Ajax, database, and interview related Question. In this Asp.net and sql server tutorial blog we give many examples of jquery as like How Play YouTube Video in Your Asp.net Website by URL, How to Play YouTube Videos in Asp.net by using Jquery , Jquery Change div background on mouseover in asp.net , JQuery UI Datepicker (Calendar) etc.

Wednesday, October 8, 2014

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


No comments:

Post a Comment