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:

• SQL Server questions for interview
• Overview of Sql
• Sql server while loop
• Continue Statement in sql
• SQL Injection, prevent asp.net web application
• Overview of Sql
• interview Part2
• interview Part3
• interview Part4