Sql Server Interview Question: Miscellaneous

What is entity?
In English Query, an entity is a real-world object, referred to by a noun (person, place, thing, or idea), such as people, products, shipments, cities, and so on. Entities are semantic objects.


Define entity integrity?
A state in which all the rows in a database have a not-null primary key value, all tables have primary keys, and no table has any duplicate primary key values. This ensures that there are no duplicate entries for anything represented in the database.


What is domain integrity?
An integrity mechanism that enforces the validity of entries for a given column. The mechanism, such as the CHECK constraint, can restrict the possible data values by data type, format, or range of values allowed.

Define data integrity?
A state in which all the data values stored in the database are correct. If incorrect data values have been stored in a database, the database is said to have lost data integrity.

Define primary key (PK)?
A column or set of columns that uniquely identify all the rows in a table.
Primary keys do not allow null values. No two rows can have the same primary key value; therefore, a primary key value always uniquely identifies a single row.


What is Candidate key?

More than one key can uniquely identify rows in a table, each of these keys is called a candidate key. Only one candidate can be chosen as the primary key of a table;

What is Alternate keys?
all other candidate keys are known as alternate keys. Although tables are not required to have primary keys, it is good practice to define them.

In a normalized table, all of the data values in each row are fully dependent on the primary key. For example, in a normalized employee table that has EmployeeID as the primary key, all of the columns should contain data related to a specific employee. This table does not have the column DepartmentName because the name of the department is dependent on a department ID, not on an employee ID.


What is referenced key?
A primary key or unique key referenced by a foreign key.


Define referential integrity (RI)?
A state in which all foreign key values in a database are valid. For a foreign key to be valid, it must contain either the value NULL, or an existing key value from the primary or unique key columns referenced by the foreign key.

What is foreign key (FK)?
The column or combination of columns whose values match the primary key (PK) or unique key in the same or another table. Also called the referencing key.


Define foreign table?
A table that contains a foreign key.



UNIQUE constraints?
Constraints that enforce entity integrity on a nonprimary key. UNIQUE constraints ensure that no duplicate values are entered and that an index is created to enhance performance.

unique index?
An index in which no two rows are permitted to have the same index value, thus prohibiting duplicate index or key values. The system checks for duplicate key values when the index is created and checks each time data is added with an INSERT or UPDATE statement.

uniqueidentifier data type?
A data type containing a unique identification number stored as a 16-byte binary string used for storing a globally unique identifier (GUID).

What is candidate key?

A column or set of columns that have a unique value for each row in a table. Each candidate key value uniquely identifies a single row in the table. Tables can have multiple candidate keys. One candidate key in a table is specified by the database designer to be the primary key for the table, and any other candidate key is called an alternate key.


What is CHECK constraints?
Defines which data values are acceptable in a column. You can apply CHECK constraints to multiple columns, and you can apply multiple CHECK constraints to a single column. When a table is dropped, CHECK constraints are also dropped.

Define composite key?
A key composed of two or more columns.

Define table-level constraint?
Constraints that allow various forms of data integrity to be defined on one column (column-level constraint) or several columns (table-level constraints) when the table is defined or altered. Constraints support domain integrity, entity integrity, and referential integrity, as well as user-defined integrity.

What is declarative referential integrity (DRI)?
FOREIGN KEY constraints defined as part of a table definition that enforce proper relationships between tables. The constraints ensure that proper actions are taken when DELETE, INSERT, and UPDATE statements remove, add, or modify primary or foreign key values. The DRI actions enforced by FOREIGN KEY constraints can be supplemented with additional referential integrity logic defined in triggers on a table.

What is default?
A data value, option setting, collation, or name assigned automatically by the system if a user does not specify the value, setting, collation, or name. An action taken automatically at certain events if a user has not specified the action to take.

What is DEFAULT constraint?
A property defined for a table column that specifies a constant to be used as the default value for the column. If any subsequent INSERT or UPDATE statement specifies a value of NULL for the column, or does not specify a value for the column, the constant value defined in the DEFAULT constraint is placed in the column.

Define constraint?
A property assigned to a table column that prevents certain types of invalid data values from being placed in the column. For example, a UNIQUE or PRIMARY KEY constraint prevents you from inserting a value that is a duplicate of an existing value, a CHECK constraint prevents you from inserting a value that does not match a search condition, and NOT NULL prevents you from inserting a NULL value.


Define column-level constraint?
A constraint definition that is specified within a column definition when a table is created or altered. The constraint applies only to the associated column.]


integrity constraint?
A property defined on a table that prevents data modifications that would create invalid data.

What is key column?
A column referenced by a primary, foreign, or index key.


What is denormalize?
To introduce redundancy into a table in order to incorporate data from a related table. The related table can then be eliminated. Denormalization can improve efficiency and performance by reducing complexity in a data warehouse schema.

Define checkpoint?
An event in which the database engine writes dirty buffer pages to disk. Dirty pages are pages that have been modified, but the modifications have not yet been written to disk. Each checkpoint writes to disk all pages that were dirty at the last checkpoint and still have not been written to disk. Checkpoints occur periodically based on the number of log records generated by data modifications, or when requested by a user or a system shutdown.


Define Concurrency?
A process that allows multiple users to access and change shared data at the same time. SQL Server uses locking to allow multiple users to access and change shared data at the same time without conflicting with each other.


What is cascading delete?
An operation that deletes a row containing a primary key value that is referenced by foreign key columns in existing rows in other tables. On a cascade delete, all of the rows whose foreign key values reference the deleted primary key value are also deleted.

What is cascading update?
An operation that updates a primary key value that is referenced by foreign key columns in existing rows in other tables. On a cascade update, all of the foreign key values are updated to match the new primary key value.

Define clustered index?
An index in which the logical order of the key values determines the physical order of the corresponding rows in a table.

Define composite index?
An index that uses more than one column in a table to index data.

What is leaf level?
The bottom level of a clustered or nonclustered index. In a clustered index, the leaf level contains the actual data pages of the table. In a nonclustered index, the leaf level either points to data pages or points to the clustered index (if one exists), rather than containing the data itself.

What is key range lock?
A lock used to lock ranges between records in a table to prevent phantom insertions or deletions into a set of records. Ensures serializable transactions.

Define keyset-driven cursor?
A cursor that shows the effects of updates made to its member rows by other users while the cursor is open, but does not show the effects of inserts or deletes.

Define static cursor?
A cursor that shows the result set exactly as it was at the time the cursor was opened. Static cursors do not reflect updates, deletes, or inserts made to underlying data while the cursor is open. They are sometimes called snapshot cursors.

Define dynamic cursor?
A cursor that can reflect data modifications made to the underlying data while the cursor is open. Updates, deletes, and inserts made by users are reflected in the dynamic cursor.

What is forward-only cursor?
A cursor that cannot be scrolled; rows can be read only in sequence from the first row to the last row.

What is fragmentation?
Occurs when data modifications are made. You can reduce fragmentation and improve read-ahead performance by dropping and re-creating a clustered index.

What is full-text catalog?
Stores all of the full-text indexes for tables within a database.

What is full-text enabling?
The process of allowing full-text querying to occur on the current database.

What is full-text index?
The portion of a full-text catalog that stores all of the full-text words and their locations for a given table.

What is full-text query?
As a SELECT statement, a query that searches for words, phrases, or multiple forms of a word or phrase in the character-based columns (of char , varchar , text , ntext , nchar , or nvarchar data types). The SELECT statement returns those rows meeting the search criteria.

What is full-text service?
The SQL Server component that performs the full-text querying.

Define function?
A piece of code that operates as a single logical unit. A function is called by name, accepts optional input parameters, and returns a status and optional output parameters. Many programming languages support functions, including C, Visual Basic, and Transact-SQL. Transact-SQL supplies built-in functions, which cannot be modified, and supports user-defined functions, which can be created and modified by users.

Define Transaction?
A group of database operations combined into a logical unit of work that is either wholly committed or rolled back. A transaction is atomic, consistent, isolated, and durable.

transaction log
A database file in which all changes to the database are recorded. It is used by SQL Server during automatic recovery.

transaction rollback?
Rollback of a user-specified transaction to the last savepoint inside a transaction or to the beginning of a transaction.

write-ahead log?
A transaction logging method in which the log is always written prior to the data.

Define concurrency
A process that allows multiple users to access and change shared data at the same time. SQL Server uses locking to allow multiple users to access and change shared data at the same time without conflicting with each other.

What is dirty pages?
Buffer pages that contain modifications that have not been written to disk.

Define dirty read?
Reads that contain uncommitted data. For example, transaction1 changes a row. Transaction2 reads the changed row before transaction1 commits the change. If transaction1 rolls back the change, transaction2 has read a row that never logically existed.

isolation level?
The property of a transaction that controls the degree to which data is isolated for use by one process and guarded against interference from other processes. Setting the isolation level defines the default locking behavior for all SELECT statements in your SQL Server session.

Savepoint?
A marker that allows an application to roll back part of a transaction if a minor error is encountered. The application must still commit or roll back the full transaction when it is complete.

shared lock?
A lock created by nonupdate (read) operations. Other users can read the data concurrently, but no transaction can acquire an exclusive lock on the data until all the shared locks have been released.

What is exclusive lock?
A lock that prevents any other transaction from acquiring a lock on a resource until the original lock on the resource is released at the end of the transaction. An exclusive lock is always applied during an update operation (INSERT, UPDATE, or DELETE).

Define explicit transaction?
A group of SQL statements enclosed within transaction delimiters. The first delimiter must be either BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION, and the end delimiter must be one of the following:
COMMIT TRANSACTION
COMMIT WORK
ROLLBACK TRANSACTION
ROLLBACK WORK
SAVE TRANSACTION.

implicit transaction?
A connection option in which each SQL statement executed by the connection is considered a separate transaction.

incremental update?
The set of operations that either adds new members to an existing cube or dimension, or adds new data to a partition. One of three processing options for a cube or partition. One of two processing options for a dimension.


Define deadlock?
A situation when two users, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each user would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

Define encryption?
A method for keeping sensitive information confidential by changing data into an unreadable form.

Index?
In a relational database, a database object that provides fast access to data in the rows of a table, based on key values. Indexes can also enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. The primary key of a table is automatically indexed. In full-text search, a full-text index stores information about significant words and their location within a given column.

index page?
A database page containing index rows.

clustered index?
An index in which the logical order of the key values determines the physical order of the corresponding rows in a table

nonrepeatable read?
When a transaction reads the same row more than one time, and between the two (or more) reads, a separate transaction modifies that row. Because the row was modified between reads within the same transaction, each read produces different values, which introduces inconsistency.

normalization rules?
A set of database design rules that minimizes data redundancy and results in a database in which the database engine and application software can easily enforce integrity.

What is NULL?
An entry that has no explicitly assigned value. NULL is not equivalent to zero or blank. A value of NULL is not considered to be greater than, less than, or equivalent to any other value, including another value of NULL.


What is equijoin?
A join in which the values in the columns being joined are compared for equality, and all columns are included in the results.

Define inner join?
An operation that retrieves rows from multiple source tables by comparing the values from columns shared between the source tables. An inner join excludes rows from a source table that have no matching rows in the other source tables.

left outer join?
A type of outer join in which all rows from the left-most table in the JOIN clause are included. When rows in the left table are not matched by rows in the right table, all result set columns that come from the right table are assigned a value of NULL.

right outer join?
A type of outer join in which all rows in the right-most table in the JOIN clause are included. When rows in the right table are not matched in the left table, all result set columns that come from the left table are assigned a value of NULL.

What is full outer join?
A type of outer join in which all rows in all joined tables are included, whether they are matched or not.

join condition?
A comparison clause that specifies how tables are related by their join columns.

full-text index?
The portion of a full-text catalog that stores all of the full-text words and their locations for a given table.

heterogeneous data?
Data stored in multiple formats. For example, data stored in a SQL Server database, a text file, and an Excel spreadsheet.

What is error log?
A text file that records system information from SQL Server.

Define Data Control Language (DCL)?
The subset of SQL statements used to control permissions on database objects. Permissions are controlled using the GRANT and REVOKE statements.

What is data definition language (DDL)?
A language, usually part of a database management system, that is used to define all attributes and properties of a database, especially row layouts, column definitions, key columns (and sometimes keying methodology), file locations, and storage strategy.
Miscellaneous
A set of system tables, stored in a catalog, that includes definitions of database structures and related information, such as permissions.
What is data dictionary view?
A system table.

What is data explosion?
The exponential growth in size of a multidimensional structure, such as a cube, due to the storage of aggregated data.

master database?
The database that controls the operation of each instance of SQL Server. It is installed automatically with each instance of SQL Server and keeps track of user accounts, remote user accounts, and remote servers that each instance can interact with. It also tracks ongoing processes, configurable environment variables, system error messages, tapes and disks available on the system, and active locks.

model database?
A database installed with SQL Server that provides the template for new user databases. SQL Server 2000 creates a new database by copying in the contents of the model database and then expanding it to the size requested.

system databases?
A set of four databases present in all instances of SQL Server that are used to store system information:
The master database stores all instance-level meta data, and records the location of all other databases.
The tempdb database stores transient objects that only exist for the length of a single statement or connection, such as worktables and temporary tables or stored procedures.
The model database is used as a template for creating all user databases.
The msdb database is used by the SQL Server Agent to record information on jobs, alerts, and backup histories.

table lock?
A lock on a table including all data and indexes.

table scan?
A data retrieval operation where the database engine must read all the pages in a table to find the rows that qualify for a query.

many-to-many relationship?
A relationship between two tables in which rows in each table have multiple matching rows in the related table. Many-to-many relationships are maintained by using a third table called a junction table and adding the primary key columns from each of the other two tables to this table.

many-to-one relationship?
A relationship between two tables in which one row in one table can relate to many rows in another table.
one-to-many relationship?
In relational databases, a relationship between two tables in which a single row in the first table can be related to one or more rows in the second table, but a row in the second table can be related only to one row in the first table.

one-to-one relationship?
In relational database, a relationship between two tables in which a single row in the first table can be related only to one row in the second table, and a row in the second table can be related only to one row in the first table.

ODBC data source?
The location of a set of data that can be accessed using an ODBC driver. Also, a stored definition that contains all of the connection information an ODBC application requires to connect to the data source.

ODBC driver?
A dynamic-link library (DLL) that an ODBC-enabled application, such as Excel, can use to access an ODBC data source. Each ODBC driver is specific to a database management system (DBMS), such as SQL Server, Access, and so on.

online analytical processing (OLAP)?
A technology that uses multidimensional structures to provide rapid access to data for analysis. The source data for OLAP is commonly stored in data warehouses in a relational database.


online transaction processing (OLTP)?
A data processing system designed to record all of the business transactions of an organization as they occur. An OLTP system is characterized by many concurrent users actively adding and modifying data.

Open Database Connectivity (ODBC)?
A data access application programming interface (API) that supports access to any data source for which an ODBC driver is available. ODBC is aligned with the American National Standards Institute (ANSI) and International Organization for Standardization (ISO) standards for a database Call Level Interface (CLI).

SQL collation?
A set of SQL Server 2000 collations whose characteristics match those of commonly-used code page and sort order combinations from earlier versions of SQL Server. SQL collations are compatibility features that let sites choose collations that match the behavior of their earlier systems.

Subquery?
A SELECT statement nested inside another SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

Correlated subqueries?
the subquery depends on the outer query for its values
In the subqueries previously discussed, SQL evaluates the subquery once, substitutes the result of the subquery in the search condition, and evaluates the outer-level SELECT based on the value of the search condition. You can also write a subquery that SQL may need to re-evaluate as it examines each new row (WHERE clause) or group of rows (HAVING clause) in the outer-level SELECT. This is called a correlated subquery.

Correlated names and references
Example: Correlated subquery in a WHERE Clause
Example: Correlated subquery in a HAVING Clause
Example: Correlated subquery in select-list
Example: Correlated subqueries in an UPDATE statement
Example: Correlated subqueries in a DELETE statement
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafycorrs.htm

Sql Server Interview Question:Trigger

What is trigger?
Creates a trigger, which is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table. Microsoft® SQL Server™ allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement

Can we write trigger on view?
Table | view
Is the table or view on which the trigger is executed and is sometimes called the trigger table or trigger view. Specifying the owner name of the table or view is optional.

What is WITH ENCRYPTION in trigger?
Encrypts the syscomments entries that contain the text of CREATE TRIGGER. Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.

What is AFTER trigger?
Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger executes.
AFTER is the default, if FOR is the only keyword specified.

Can we write after trigger on views?
AFTER triggers cannot be defined on views.

What is INSTEAD OF trigger?
Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, it is possible to define views on views where each view has its own INSTEAD OF trigger.

INSTEAD OF triggers are not allowed on updateable views WITH CHECK OPTION. SQL Server will raise an error if an INSTEAD OF trigger is added to an updateable view WITH CHECK OPTION specified. The user must remove that option using ALTER VIEW before defining the INSTEAD OF trigger

What are the data modification statement execute the trigger?

DELETE] [,] [INSERT] [,] [UPDATE] }
Are keywords that specify which data modification statements, when attempted against this table or view, activate the trigger. At least one option must be specified. Any combination of these in any order is allowed in the trigger definition. If more than one option is specified, separate the options with commas.

What are the logical table trigger can have?
deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:
SELECT *
FROM deleted

What are the datatypes not supported for trigger?

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70.
The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table.
When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.

How many columns we can add on update trigger?
IF UPDATE (column)
Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, do not include the table name before the column name in an IF UPDATE clause.

To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

How do you enforce business rules by trigger?
Triggers are often used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the table creation statements (ALTER TABLE and CREATE TABLE);

however, DRI does not provide cross-database referential integrity. To enforce referential integrity (rules about the relationships between the primary and foreign keys of tables), use primary and foreign key constraints (the PRIMARY KEY and FOREIGN KEY
keywords of ALTER TABLE and CREATE TABLE).

If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed (fired).

The first and last AFTER triggers to be executed on a table may be specified by using sp_settriggerorder. Only one first and one last AFTER trigger for each of the INSERT, UPDATE, and DELETE operations may be specified on a table; if there are other AFTER triggers on the same table, they are executed randomly.
If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and the order value must be reset with

sp_settriggerorder.
An AFTER trigger is executed only after the triggering SQL statement, including all referential cascade actions and constraint checks associated with the object updated or deleted, has executed successfully.

The AFTER trigger sees the effects of the triggering statement as well as all referential cascade UPDATE and DELETE actions caused by the triggering statement.

What are Trigger Limitations?
CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

A trigger is created only in the current database; however, a trigger can reference objects outside the current database.
If the trigger owner name is specified (to qualify the trigger), qualify the table name in the same way.

The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.

INSTEAD OF DELETE/UPDATE triggers cannot be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.

Any SET statement can be specified inside a trigger. The SET option chosen remains in effect during the execution of the trigger and then reverts to its former setting.

When a trigger fires, results are returned to the calling application, just as with stored procedures. To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger.

A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the beginning of the trigger to eliminate the return of any result sets.

A TRUNCATE TABLE statement is not caught by a DELETE trigger. Although a TRUNCATE TABLE statement is, in effect, a DELETE without a WHERE clause (it removes all rows), it is not logged and thus cannot execute a trigger.

Because permission for the TRUNCATE TABLE statement defaults to the table owner and is not transferable, only the table owner should be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement.

The WRITETEXT statement, whether logged or unlogged, does not activate a trigger.
These Transact-SQL statements are not allowed in a trigger:

ALTER DATABASE CREATE DATABASE DISK INIT
DISK RESIZE DROP DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE RESTORE DATABASE
RESTORE LOG

What is Recursive Triggers?
SQL Server also allows recursive invocation of triggers when the recursive triggers setting is enabled in sp_dboption.

Recursive triggers allow two types of recursion to occur:
Indirect recursion
Direct recursion

With indirect recursion, an application updates table T1, which fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.

With direct recursion, the application updates table T1, which fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.
This example uses both indirect and direct trigger recursion. Assume that two update triggers, TR1 and TR2, are defined on table T1.

Trigger TR1 updates table T1 recursively. An UPDATE statement executes each TR1 and TR2 one time. In addition, the execution of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deleted tables for a given trigger contain rows corresponding only to the UPDATE statement that invoked the trigger.

How many levels we can have nested triggers?

Nested Triggers
Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on.

If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows nested triggers.

If nested triggers is off, recursive triggers is also disabled, regardless of the recursive triggers setting of sp_dboption.

How do you ALTER TRIGGER?

Alters the definition of a trigger created previously by the CREATE TRIGGER statement
ALTER TRIGGER supports manually updateable views through INSTEAD OF triggers on tables and views. Microsoft® SQL Server™ applies ALTER TRIGGER the same way for all types of triggers (AFTER, INSTEAD-OF).

The first and last AFTER triggers to be executed on a table may be specified by using sp_settriggerorder. Only one first and one last AFTER trigger may be specified on a table; if there are other AFTER triggers on the same table, they will be executed in an undefined sequence.

If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and the order value must be reset with sp_settriggerorder.

An AFTER trigger is executed only after the triggering SQL statement, including all referential cascade actions and constraint checks associated with the object updated or deleted, is executed successfully.

The AFTER trigger operation checks for the effects of the triggering statement as well as all referential cascade UPDATE and DELETE actions caused by the triggering statement.

When a DELETE action to a child or referencing table is the result of a CASCADE on a DELETE from the parent table, and an INSTEAD OF trigger on DELETE is defined on that child table, the trigger is ignored and the DELETE action is executed.

How do you DROP TRIGGER?

Removes one or more triggers from the current database.
You can remove a trigger by dropping it or by dropping the trigger table.

When a table is dropped, all associated triggers are also dropped. When a trigger is dropped, information about the trigger is removed from the sysobjects and syscomments system tables.

Use DROP TRIGGER and CREATE TRIGGER to rename a trigger. Use ALTER TRIGGER to change the definition of a trigger.

For more information about determining dependencies for a specific trigger, see "sp_depends" in this volume.

For more information about viewing the text of the trigger, see "sp_helptext" in this volume.

How do you see existing trigger list?
For more information about viewing a list of existing triggers, see "sp_helptrigger" in this volume.

Sql Server Interview Question:Transactions

What is Transactions?
A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:

What is ACID?

Atomicity
A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.

Consistency
When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.


Isolation
Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions.

A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state.

This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

Durability
After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

Optimizing Transaction Log Performance
General recommendations for creating transaction log files include:

Create the transaction log on a physically separate disk or RAID (redundant array of independent disks) device. The transaction log file is written serially; therefore, using a separate, dedicated disk allows the disk heads to stay in place for the next write operation.


Set the original size of the transaction log file to a reasonable size to prevent the file from automatically expanding as more transaction log space is needed. As the transaction log expands, a new virtual log file is created, and write operations to the transaction log wait while the transaction log is expanded. If the transaction log expands too frequently, performance can be affected.


Set the file growth increment percentage to a reasonable size to prevent the file from growing by too small a value. If the file growth is too small compared to the number of log records being written to the transaction log, then the transaction log may need to expand constantly, affecting performance.

Manually shrink the transaction log files rather than allowing Microsoft® SQL Server™ 2000 to shrink the files automatically. Shrinking the transaction log can affect performance on a busy system due to the movement and locking of data pages.


What is Transaction Logs?
A database in Microsoft® SQL Server™ 2000 has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database.

SQL Server uses the transaction log of each database to recover transactions. The transaction log is a serial record of all modifications that have occurred in the database as well as the transaction that performed each modification. The transaction log records the start of each transaction. It records the changes to the data and enough information to undo the modifications (if necessary later) made during each transaction. For some large operations, such as CREATE INDEX, the transaction log instead records the fact that the operation took place. The log grows continuously as logged operations occur in the database.

The transaction log records the allocation and deallocation of pages and the commit or rollback of each transaction. This allows SQL Server either to apply (roll forward) or back out (roll back) each transaction in the following ways:

A transaction is rolled forward when you apply a transaction log. SQL Server copies the after image of every modification to the database or reruns statements such as CREATE INDEX. These actions are applied in the same sequence in which they originally occurred. At the end of this process, the database is in the same state it was in at the time the transaction log was backed up.

A transaction is rolled back when you back out an incomplete transaction. SQL Server copies the before images of all modifications to the database since the BEGIN TRANSACTION. If it encounters transaction log records indicating that a CREATE INDEX was performed, it performs operations that logically reverse the statement. These before images and CREATE INDEX reversals are applied in the reverse of their original sequence.


At a checkpoint, SQL Server ensures that all transaction log records and database pages modified are written to disk.

During the recovery process of each database that occurs when SQL Server is restarted, a transaction must be rolled forward only when it is not known whether all the data modifications in the transaction were actually written from the SQL Server buffer cache to disk.

Because a checkpoint forces all modified pages to disk, it represents the point at which the startup recovery must start rolling forward transactions.

Because all pages modified before the checkpoint are guaranteed to be on disk, there is no need to roll forward anything done before the checkpoint.


What is Virtual Log Files?
Each transaction log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions.

The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files. The number and size of the virtual log files in a transaction log increase as the size of the log file increases. A small log file can have a small number of small virtual log files (for example, a 5-MB log file that comprises five 1-MB virtual log files).
A large log file can have larger virtual log files (for example, a 500-MB log file that comprises ten 50-MB virtual log files).

Microsoft® SQL Server™ 2000 tries to avoid having many small virtual log files. The number of virtual log files grows much more slowly than the size. If a log file grows in small increments, it tends to have many small virtual log files.
If the log file grows in larger increments, SQL Server creates a smaller number of larger virtual log files.


For example, if the transaction log is growing by 1-MB increments, the virtual log files are smaller and more numerous compared to a transaction log growing at 50-MB increments. A large number of virtual log files can increase the time taken to perform database recovery.

As records are written to the log, the end of the log grows from one virtual log file to the next. If there is more than one physical log file for a database, the end of the log grows through each virtual log file in each physical file before circling back to the first virtual log file in the first physical file. Only when all log files are full will the log begin to grow automatically.

Write-Ahead Transaction Log
Microsoft® SQL Server™ 2000, like many relational databases, uses a write-ahead log. A write-ahead log ensures that no data modifications are written to disk before the associated log record.

SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are instead made to the copy of the page in the buffer cache.

The modification is not written to disk until either the database is checkpointed, or the modifications must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache but not yet written to disk is called a dirty page.

At the time a modification is made to a page in the buffer, a log record is built in the log cache recording the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk.

If the dirty page were flushed before the log record, it would create a modification on disk that could not be rolled back if the server failed before the log record were written to disk.

SQL Server has logic that prevents a dirty page from being flushed before the associated log record. Because log records are always written ahead of the associated data pages, the log is called a write-ahead log.

Transactions Architecture
Microsoft® SQL Server™ 2000 maintains the consistency and integrity of each database despite errors that occur in the system. Every application that updates data in a SQL Server database does so using transactions.

A transaction is a logical unit of work made up of a series of statements (selects, inserts, updates, or deletes). If no errors are encountered during a transaction, all of the modifications in the transaction become a permanent part of the database. If errors are encountered, none of the modifications are made to the database.

A transaction goes through several phases:

Before the transaction starts, the database is in a consistent state.

The application signals the start of a transaction. This can be done explicitly with the BEGIN TRANSACTION statement. Alternatively, the application can set options to run in implicit transaction mode; the first Transact-SQL statement executed after the completion of a prior transaction starts a new transaction automatically.

No record is written to the log when the transaction starts; the first record is written to the log when the application generates the first log record for a data modification.

The application starts modifying data. These modifications are made one table at a time. As a series of modifications are made, they may leave the database in a temporarily inconsistent intermediate state.

When the application reaches a point where all the modifications have completed successfully and the database is once again consistent, the application commits the transaction.


This makes all the modifications a permanent part of the database.
If the application encounters some error that prevents it from completing the transaction, it undoes, or rolls back, all the data modifications. This returns the database to the point of consistency it was at before the transaction started.

SQL Server applications can also run in autocommit mode. In autocommit mode each individual Transact-SQL statement is committed automatically if it is successful and rolled back automatically if it generates an error.

There is no need for an application running in autocommit mode to issue statements that specifically start or end a transaction.
All Transact-SQL statements run in a transaction: an explicit transaction, an implicit transaction, or an autocommit transaction.

All SQL Server transactions that include data modifications either reach a new point of consistency and are committed, or are rolled back to the original point of consistency. Transactions are not left in an intermediate state where the database is not consistent.

How do you Shrinking the Transaction Log?

The size of the log files are physically reduced when:
A DBCC SHRINKDATABASE statement is executed.
A DBCC SHRINKFILE statement referencing a log file is executed.
An autoshrink operation occurs.

Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log.

A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.

The unit of size reduction is a virtual log. For example, if you have a 600 MB log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but it cannot be reduced to sizes such as 433 MB or 525 MB.

Virtual logs that hold part of the logical log cannot be freed. If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive.

When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user.

The target_size specified by the user is rounded to the next highest virtual log boundary. For example, if a user specifies a target_size of 325 MB for our sample 600 MB file with 100 MB virtual log files, the last two virtual log files are removed and the new file size is 400 MB.

In SQL Server 2000, a DBCC SHRINKDATABASE or DBCC SHRINKFILE operation attempts to shrink the physical log file to the requested size (subject to rounding) immediately:

If no part of the logical log is in the virtual logs beyond the target_size mark, the virtual logs after the target_size mark are freed and the successful DBCC statement completes with no messages.

If part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. The message tells you what actions you need to perform to get the logical log out of the virtual logs at the end of the file. After you perform this action, you can then reissue the DBCC statement to free the remaining space.

What is Truncating the Transaction Log?
If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files.

At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records. The process of deleting these log records to reduce the size of the logical log is called truncating the log.

What is Transaction Recovery?
Every Microsoft® SQL Server™ 2000 database has a transaction log that records data modifications made in the database.

The log records the start and end of every transaction and associates each modification with a transaction. An instance of SQL Server stores enough information in the log to either redo (roll forward) or undo (roll back) the data modifications that make up a transaction.

Each record in the log is identified by a unique log sequence number (LSN). All of the log records for a transaction are chained together.

An instance of SQL Server records many different types of information in the transaction log. Instances of SQL Server 2000 primarily log the logical operations performed. The operation is reapplied to roll forward a modification, and the opposite of the logical operation is performed to roll back a modification.

What is dirty pages?

Each instance of SQL Server controls when modifications are written from its data buffers to disk. An instance of SQL Server may cache modifications in buffers for a period of time to optimize disk writes.

A buffer page that contains modifications that have not yet written to disk is known as a dirty page. Writing a dirty buffer page to disk is called flushing the page. When modifications are cached, care must be taken to ensure that no data modification is flushed before the corresponding log image is written to the log file. This could create a modification that could not be rolled back if necessary.

To ensure that they can recover all modifications, instances of SQL Server use a write-ahead log, which means that all log images are written to disk before the corresponding data modification.
A commit operation forces all log records for a transaction to the log file so that the transaction is fully recoverable even if the server is shut down.

A commit operation does not have to force all the modified data pages to disk as long as all the log records are flushed to disk. A system recovery can roll the transaction forward or backward using only the log records.

What are the ways of Starting Transactions?
You can start transactions in Microsoft® SQL Server™ as explicit, autocommit, or

implicit transactions.
Explicit transactions

Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.

Autocommit transactions
This is the default mode for SQL Server. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.

Implicit transactions
Set implicit transaction mode on through either an API function or the Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. The next statement automatically starts a new transaction. When that transaction is completed, the next Transact-SQL statement starts a new transaction.

Connection modes are managed at the connection level. If one connection changes from one transaction mode to another it has no effect on the transaction modes of any other connection


How do you Ending Transactions?

You can end transactions with either a COMMIT or ROLLBACK statement.

COMMIT
If a transaction is successful, commit it. A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. A COMMIT also frees resources, such as locks, used by the transaction.

ROLLBACK
If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. A ROLLBACK also frees resources held by the transaction.

What does the BEGIN TRANSACTION do?
Marks the starting point of an explicit, local transaction. BEGIN TRANSACTION increments @@TRANCOUNT by 1.
BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent.

If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency.

Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK


TRANSACTION statement.
BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the

Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources.

Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement.

An application can perform actions such as acquiring locks to protect the transaction isolation level of SELECT statements, but nothing is recorded in the log until the application performs a modification action.
Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system.

A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback are in fact rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back.

BEGIN TRANSACTION starts a local transaction. The local transaction is escalated to a distributed transaction if the following actions are performed before it is committed or rolled back:

An INSERT, DELETE, or UPDATE statement is executed that references a remote table on a linked server. The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked server does not support the ITransactionJoin interface.

A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.


What does the COMMIT TRANSACTION do?

Marks the end of a successful implicit or user-defined transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the connection, and decrements @@TRANCOUNT to 0.

If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1.

It is the responsibility of the Transact-SQL programmer to issue COMMIT TRANSACTION only at a point when all data referenced by the transaction is logically correct.

If the transaction committed was a Transact-SQL distributed transaction, COMMIT TRANSACTION triggers MS DTC to use a two-phase commit protocol to commit all the servers involved in the transaction.

If a local transaction spans two or more databases on the same server, SQL Server uses an internal two-phase commit to commit all the databases involved in the transaction.

When used in nested transactions, commits of the inner transactions do not free resources or make their modifications permanent.

The data modifications are made permanent and resources freed only when the outer transaction is committed.

Each COMMIT TRANSACTION issued when @@TRANCOUNT is greater than 1 simply decrements @@TRANCOUNT by 1. When @@TRANCOUNT is finally decremented to 0, the entire outer transaction is committed.

Because transaction_name is ignored by SQL Server, issuing a COMMIT TRANSACTION referencing the name of an outer transaction when there are outstanding inner transactions only decrements @@TRANCOUNT by 1.

Issuing a COMMIT TRANSACTION when @@TRANCOUNT is 0 results in an error that there is no corresponding BEGIN TRANSACTION.

You cannot roll back a transaction after a COMMIT TRANSACTION statement is issued because the data modifications have been made a permanent part of the database.


What does the SAVE TRANSACTION do?

Sets a savepoint within a transaction.
A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled.

If a transaction is rolled back to a savepoint, it must proceed to completion (with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement), or it must be canceled altogether (by rolling the transaction back to its beginning).

To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone.
SAVE TRANSACTION is not supported in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.

What is Explicit Transactions?
An explicit transaction is one in which you explicitly define both the start and end of the transaction. Explicit transactions were also called user-defined or user-specified transactions in earlier versions of Microsoft® SQL Server™.

What does the @@TRANCOUNT do?
Returns the number of active transactions for the current connection.

Sql Server Interview Question:Control-of-Flow Language

What are Control-of-flow language?

The table shows the Transact-SQL control-of-flow keywords.

Keyword Description

BEGIN...END
Defines a statement block.

BREAK
Exits the innermost WHILE loop.

CONTINUE
Restarts a WHILE loop.

GOTO label
Continues processing at the statement following the label as defined by label.

IF...ELSE
Defines conditional, and optionally, alternate execution when a condition is FALSE.

RETURN
Exits unconditionally.

WAITFOR
Sets a delay for statement execution.

WHILE
Repeats statements while a specific condition is TRUE.

Sql Server Interview Question: Functions

What is Deterministic and Nondeterministic Functions?
All functions are deterministic or nondeterministic

Deterministic functions always return the same result any time they are called with a specific set of input values.

Nondeterministic functions may return different results each time they are called with a specific set of input values.

Whether a function is deterministic or nondeterministic is called the determinism of the function.

For example, the DATEADD built-in function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed.

Earlier versions of Microsoft® SQL Server™ have no functionality that is dependent on the determinism of functions. In Microsoft SQL Server 2000, nondeterministic functions cannot be specified in two types of Transact-SQL expressions:

An index cannot be created on a computed column if the computed_column_expression references any nondeterministic functions.

A clustered index cannot be created on a view if the view references any nondeterministic functions.

One of the properties SQL Server records for user-defined functions is whether the function is deterministic. A nondeterministic user-defined function cannot be invoked by either a view or computed column if you want to create an index on the view or computed column.

What is Built-in Function Determinism?
You cannot influence the determinism of any built-in function. Each built-in function is deterministic or nondeterministic based on how the function is implemented by Microsoft SQL Server.

All of the aggregate and string built-in functions are deterministic except the string functions CHARINDEX and PATINDEX. For a list of these functions, see Aggregate Functions and String Functions.

These built-in functions from categories of built-in functions other than aggregate and string functions are always deterministic:

ABS DATEDIFF PARSENAME
ACOS DAY POWER
ASIN DEGREES RADIANS
ATAN EXP ROUND
ATN2 FLOOR SIGN
CEILING ISNULL SIN
COALESCE ISNUMERIC SQUARE
COS LOG SQRT
COT LOG10 TAN
DATALENGTH MONTH YEAR
DATEADD NULLIF

These functions are not always deterministic but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

Function Comments
CAST Deterministic unless used with datetime, smalldatetime, or sql_variant.
CONVERT Deterministic unless used with datetime, smalldatetime, or sql_variant. The datetime and smalldatetime data types are deterministic if the style parameter is also specified.

CHECKSUM Deterministic, with the exception of CHECKSUM(*).
ISDATE Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and style is not equal to 0, 100, 9, or 109.
RAND RAND is deterministic only when a seed parameter is specified.

What is non deterministic function?
All of the configuration, cursor, meta data, security, and system statistical functions are nondeterministic. For a list of these functions, see Configuration Functions, Cursor Functions, Meta Data Functions, Security Functions, and System Statistical Functions.

These built-in functions from other categories are always nondeterministic:


@@ERROR FORMATMESSAGE NEWID
@@IDENTITY GETANSINULL PATINDEX
@@ROWCOUNT GETDATE PERMISSIONS
@@TRANCOUNT GetUTCDate SESSION_USER
APP_NAME HOST_ID STATS_DATE
CHARINDEX HOST_NAME SYSTEM_USER
CURRENT_TIMESTAMP IDENT_INCR TEXTPTR
CURRENT_USER IDENT_SEED TEXTVALID
DATENAME IDENTITY USER_NAME


What are the types of Functions ?
The Transact-SQL programming language provides three types of functions:

Rowset functions
Can be used like table references in an SQL statement. For more information about a list of these functions, see Rowset Functions.

Aggregate functions

Operate on a collection of values but return a single, summarizing value. For more information about a list of these functions, see Aggregate Functions.
Scalar functions

Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. This table categorizes the scalar functions.

Function category Explanation

Configuration Functions
Returns information about the current configuration.

Cursor Functions
Returns information about cursors.

Date and Time Functions
Performs an operation on a date and time input value and returns either a string, numeric, or date and time value.

Mathematical Functions
Performs a calculation based on input values provided as parameters to the function, and returns a numeric value.

Metadata Functions
Returns information about the database and database objects.
Security Functions

Returns information about users and roles.
String Functions
Performs an operation on a string (char or varchar) input value and returns a string or numeric value.

System Functions
Performs operations and returns information about values, objects, and settings in Microsoft® SQL Server™.

System Statistical Functions
Returns statistical information about the system.
Text and Image Functions
Performs an operation on a text or image input values or column, and returns information about the value.

What are the Configuration Functions?
These scalar functions return information about current configuration option settings.

@@DATEFIRST
@@OPTIONS

@@DBTS
@@REMSERVER

@@LANGID
@@SERVERNAME

@@LANGUAGE
@@SERVICENAME

@@LOCK_TIMEOUT
@@SPID

@@MAX_CONNECTIONS
@@TEXTSIZE

@@MAX_PRECISION
@@VERSION

@@NESTLEVEL


All configuration functions are nondeterministic; they do not always return the same results every time they are called with a specific set of input values.


What are the Cursor Functions?
These scalar functions return information about cursors.
@@CURSOR_ROWS
CURSOR_STATUS
@@FETCH_STATUS
All cursor functions are nondeterministic; they do not always return the same results every time they are called with a specific set of input values.

What are the Date and Time Functions?
These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value.
This table lists the date and time functions and their determinism property. For more information about function determinism, see Deterministic and Nondeterministic Functions.

Function Determinism
DATEADD
Deterministic
DATEDIFF
Deterministic
DATENAME
Nondeterministic
DATEPART
Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week.
DAY
Deterministic
GETDATE
Nondeterministic
GETUTCDATE
Nondeterministic
MONTH
Deterministic
YEAR
Deterministic



What are the Mathematical Functions?
These scalar functions perform a calculation, usually based on input values provided as arguments, and return a numeric value.
ABS
DEGREES
RAND

ACOS
EXP
ROUND

ASIN
FLOOR
SIGN

ATAN
LOG
SIN

ATN2
LOG10
SQUARE

CEILING
PI
SQRT

COS
POWER
TAN

COT
RADIANS


What are the Meta Data Functions?
These scalar functions return information about the database and database objects.
COL_LENGTH
fn_listextendedproperty

COL_NAME
FULLTEXTCATALOGPROPERTY

COLUMNPROPERTY
FULLTEXTSERVICEPROPERTY

DATABASEPROPERTY
INDEX_COL

DATABASEPROPERTYEX
INDEXKEY_PROPERTY

DB_ID
INDEXPROPERTY

DB_NAME
OBJECT_ID

FILE_ID
OBJECT_NAME

FILE_NAME
OBJECTPROPERTY

FILEGROUP_ID
@@PROCID

FILEGROUP_NAME
SQL_VARIANT_PROPERTY

FILEGROUPPROPERTY
TYPEPROPERTY

FILEPROPERTY


All meta data functions are nondeterministic. They do not always return the same results every time they are called with a specific set of input values.


What are the Security Functions?
These scalar functions return information about users and roles.
fn_trace_geteventinfo
IS_SRVROLEMEMBER

fn_trace_getfilterinfo
SUSER_SID

fn_trace_getinfo
SUSER_SNAME

fn_trace_gettable
USER_ID

HAS_DBACCESS
USER

IS_MEMBER


All security functions are nondeterministic. They do not always return the same results every time they are called with a specific set of input values.


What are the String Functions
These scalar functions perform an operation on a string input value and return a string or numeric value.
ASCII
NCHAR
SOUNDEX

CHAR
PATINDEX
SPACE

CHARINDEX
REPLACE
STR

DIFFERENCE
QUOTENAME
STUFF

LEFT
REPLICATE
SUBSTRING

LEN
REVERSE
UNICODE

LOWER
RIGHT
UPPER

LTRIM
RTRIM


What are the System Functions?
These scalar functions perform operations on and return information about values, objects, and settings in Microsoft® SQL Server™.


Function Determinism
APP_NAME
Nondeterministic
CASE expression
Deterministic
CAST and CONVERT
Deterministic unless used with datetime, smalldatetime, or sql_variant.
COALESCE
Deterministic
COLLATIONPROPERTY
Nondeterministic
CURRENT_TIMESTAMP
Nondeterministic
CURRENT_USER
Nondeterministic
DATALENGTH
Deterministic
@@ERROR
Nondeterministic
fn_helpcollations
Deterministic
fn_servershareddrives
Nondeterministic
fn_virtualfilestats
Nondeterministic
FORMATMESSAGE
Nondeterministic
GETANSINULL
Nondeterministic
HOST_ID
Nondeterministic
HOST_NAME
Nondeterministic
IDENT_CURRENT
Nondeterministic
IDENT_INCR
Nondeterministic
IDENT_SEED
Nondeterministic
@@IDENTITY
Nondeterministic
IDENTITY (Function)
Nondeterministic
ISDATE

Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and the style parameter is not equal to 0, 100, 9, or 109. Styles 0 and 100 use the default format mon dd yyyy hh:miAM (or PM). Styles 9 and 109 use the default format plus milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM).

ISNULL
Deterministic
ISNUMERIC
Deterministic
NEWID
Nondeterministic
NULLIF
Deterministic
PARSENAME
Deterministic
PERMISSIONS
Nondeterministic
@@ROWCOUNT
Nondeterministic
ROWCOUNT_BIG
Nondeterministic
SCOPE_IDENTITY
Nondeterministic
SERVERPROPERTY
Nondeterministic
SESSIONPROPERTY
Nondeterministic
SESSION_USER
Nondeterministic
STATS_DATE
Nondeterministic
SYSTEM_USER
Nondeterministic
@@TRANCOUNT
Nondeterministic
USER_NAME
Nondeterministic


What are the System Statistical Functions?
These scalar functions return statistical information about the system.
@@CONNECTIONS
@@PACK_RECEIVED

@@CPU_BUSY
@@PACK_SENT

fn_virtualfilestats
@@TIMETICKS

@@IDLE
@@TOTAL_ERRORS

@@IO_BUSY
@@TOTAL_READ

@@PACKET_ERRORS
@@TOTAL_WRITE


All system statistical functions are nondeterministic;

What are the Text and Image Functions?
These scalar functions perform an operation on a text or image input value or column and return information about the value.
PATINDEX
TEXTPTR
TEXTVALID
These text and image functions are nondeterministic functions and they may not return the same results each time they are called, even with the same set of input values

Sql Server Interview Question:Procedure

What is stored PROCEDURE?
Creates a stored procedure, which is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.

Procedures can be created for permanent use or for temporary use within a session (local temporary procedure) or for temporary use within all sessions (global temporary procedure).

Local or global temporary procedures can be created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 128 characters.
Stored procedures can also be created to run automatically when Microsoft® SQL Server™ starts.

What is group procedure?
;number
Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement.

For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.

What is parameter procedure?
@parameter
Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter is defined).

How many parameter a stored procedure can have?
A stored procedure can have a maximum of 2,100 parameters.

What are the datatype you can use for stored procedure parameter?
data_type
Is the parameter data type. All data types, including text, ntext and image, can be used as a parameter for a stored procedure.

Which procedure you can use cursor datatype?
However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified

What is the maximum number of output parameters a procedure can have?
There is no limit on the maximum number of output parameters that can be of cursor data type.

What is VARYING in procedure?
Specifies the result set supported as an output parameter (constructed dynamically by the stored procedure and whose contents can vary). Applies only to cursor parameters.

Can we have default parameter procedure?
Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.

What does the OUTPUT keyword in procedure?
Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. An output parameter using the OUTPUT keyword can be a cursor placeholder.

What is RECOMPILE and ENCRYPTION in stored procedure?
RECOMPILE indicates that SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Use the RECOMPILE option when using atypical or temporary values without overriding the execution plan cached in memory.

ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication

What is the maximum size of the stored procedure?
The maximum size of a stored procedure is 128 MB.

How do you get Information About Stored Procedures?
To display the text used to create the procedure, execute sp_helptext in the database in which the procedure exists with the procedure name as the parameter.

Can we get the text of encrypted procedure?
Stored procedures created with the ENCRYPTION option cannot be viewed with sp_helptext.

How do you know what are the objects referenced by procedure?
For a report on the objects referenced by a procedure, use sp_depends.

Where are the procedure name and its text stored in sql server?
When a CREATE PROCEDURE statement is executed successfully, the procedure name is stored in the sysobjects system table and the text of the CREATE PROCEDURE statement is stored in syscomments. When executed for the first time, the procedure is compiled to determine an optimal access plan to retrieve the data.

Rules for Cursor Output Parameters:
The following rules pertain to cursor output parameters when the procedure is executed:

For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the stored procedure executed, for example:

A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.

The procedure fetches the first 5 rows of result set RS.

The procedure returns to its caller.

The result set RS returned to the caller consists of rows from 6 through 100 of RS, and the cursor in the caller is positioned before the first row of RS.

For a forward-only cursor, if the cursor is positioned before the first row upon completion of the stored procedure, the entire result set is returned to the calling batch, stored procedure, or trigger. When returned, the cursor position is set before the first row.

For a forward-only cursor, if the cursor is positioned beyond the end of the last row upon completion of the stored procedure, an empty result set is returned to the calling batch, stored procedure, or trigger.

Note An empty result set is not the same as a null value.

For a scrollable cursor, all the rows in the result set are returned to the calling batch, stored procedure, or trigger at the conclusion of the execution of the stored procedure. When returned, the cursor position is left at the position of the last fetch executed in the procedure.

For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, stored procedure, or trigger. This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

Note The closed state matters only at return time. For example, it is valid to close a cursor part way through the procedure, to open it again later in the procedure, and return that cursor's result set to the calling batch, stored procedure, or trigger.

Notes about procedure:
A user-defined stored procedure can be created only in the current database (except for temporary procedures, which are always created in tempdb).

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not allow NULLs, SQL Server generates an error.

To prevent passing a NULL parameter value to a column that does not allow NULLs, add programming logic to the procedure or use a default value (with the DEFAULT keyword of CREATE or ALTER TABLE) for the column.

It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement in a stored procedure, such as when creating a temporary table.

The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way SQL Server assigns the NULL or NOT NULL attributes to columns if not specified in a CREATE TABLE or ALTER TABLE statement.

If a connection executes a stored procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behaviors.

If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created with the same nullability for all connections that execute the stored procedure.
SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed.

Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or altered.

If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting.

When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes.
The setting is then restored to the value it had when the stored procedure was called. This allows individual clients to set the options wanted without affecting the logic of the stored procedure.

How do you ALTER PROCEDURE
Alters a previously created procedure, created by executing the CREATE PROCEDURE statement, without changing permissions and without affecting any dependent stored procedures or triggers

What are System Stored Procedures
Many administrative and informational activities in Microsoft® SQL Server™ can be performed through system stored procedures. The system stored procedures are grouped into these categories.

Category Description
Active Directory Procedures Used to register instances of SQL Server and SQL Server databases in Microsoft Windows® 2000 Active Directory™.

Catalog Procedures Implements ODBC data dictionary functions and isolates ODBC applications from changes to underlying system tables.
Cursor Procedures Implements cursor variable functionality.
Database Maintenance Plan Procedures Used to set up core maintenance tasks necessary to ensure database performance.

Distributed Queries Procedures Used to implement and manage Distributed Queries.
Full-Text Search Procedures Used to implement and query full-text indexes.
Log Shipping Procedures Used to configure and manage log shipping.

OLE Automation Procedures Allows standard OLE automation objects to be used within a standard Transact-SQL batch.

Replication Procedures Used to manage replication.
Security Procedures Used to manage security.

SQL Mail Procedures Used to perform e-mail operations from within SQL Server.
SQL Profiler Procedures Used by SQL Profiler to monitor performance and activity.
SQL Server Agent Procedures Used by SQL Server Agent to manage scheduled and event-driven activities.

System Procedures Used for general maintenance of SQL Server.
Web Assistant Procedures Used by the Web Assistant.
XML Procedures Used for Extensible Markup Language (XML) text management.
General Extended Procedures Provides an interface from SQL Server to external programs for various maintenance activities.

What are catalog procedures?


Catalog Procedures
sp_column_privileges
sp_special_columns

sp_columns
sp_sproc_columns

sp_databases
sp_statistics

sp_fkeys
sp_stored_procedures

sp_pkeys
sp_table_privileges

sp_server_info
sp_tables

What are the cursor procedure?
Cursor Procedures
sp_cursor_list
sp_describe_cursor_columns

sp_describe_cursor
sp_describe_cursor_tables



What are the database maintenance procedure?
Database Maintenance Plan Procedures
sp_add_maintenance_plan
sp_delete_maintenance_plan_db

sp_add_maintenance_plan_db
sp_delete_maintenance_plan_job

sp_add_maintenance_plan_job
sp_help_maintenance_plan

sp_delete_maintenance_plan

What are the distributed queries procedure?

Distributed Queries Procedures
sp_addlinkedserver
sp_indexes

sp_addlinkedsrvlogin
sp_linkedservers

sp_catalogs
sp_primarykeys

sp_column_privileges_ex
sp_serveroption

sp_columns_ex
sp_table_privileges_ex

sp_droplinkedsrvlogin
sp_tables_ex

sp_foreignkeys

What are the full-text search procedure

Full-Text Search Procedures
sp_fulltext_catalog
sp_help_fulltext_catalogs_cursor

sp_fulltext_column
sp_help_fulltext_columns

sp_fulltext_database
sp_help_fulltext_columns_cursor

sp_fulltext_service
sp_help_fulltext_tables

sp_fulltext_table
sp_help_fulltext_tables_cursor

sp_help_fulltext_catalogs


What are the log-shipping procedures?

Log Shipping Procedures
sp_add_log_shipping_database
sp_delete_log_shipping_database

sp_add_log_shipping_plan
sp_delete_log_shipping_plan

sp_add_log_shipping_plan_database
sp_delete_log_shipping_plan_database

sp_add_log_shipping_primary
sp_delete_log_shipping_primary

sp_add_log_shipping_secondary
sp_delete_log_shipping_secondary

sp_can_tlog_be_applied
sp_get_log_shipping_monitor_info

sp_change_monitor_role
sp_remove_log_shipping_monitor

sp_change_primary_role
sp_resolve_logins

sp_change_secondary_role
sp_update_log_shipping_monitor_info

sp_create_log_shipping_monitor_account
sp_update_log_shipping_plan

sp_define_log_shipping_monitor
sp_update_log_shipping_plan_database


OLE Automation Extended Stored Procedures
sp_OACreate
sp_OAMethod

sp_OADestroy
sp_OASetProperty

sp_OAGetErrorInfo
sp_OAStop

sp_OAGetProperty
Object Hierarchy Syntax


Replication Procedures
sp_add_agent_parameter
sp_enableagentoffload

sp_add_agent_profile
sp_enumcustomresolvers

sp_addarticle
sp_enumdsn

sp_adddistpublisher
sp_enumfullsubscribers

sp_adddistributiondb
sp_expired_subscription_cleanup

sp_adddistributor
sp_generatefilters

sp_addmergealternatepublisher
sp_getagentoffloadinfo

sp_addmergearticle
sp_getmergedeletetype

sp_addmergefilter
sp_get_distributor

sp_addmergepublication
sp_getqueuedrows

sp_addmergepullsubscription
sp_getsubscriptiondtspackagename

sp_addmergepullsubscription_agent
sp_grant_publication_access

sp_addmergesubscription
sp_help_agent_default

sp_addpublication
sp_help_agent_parameter

sp_addpublication_snapshot
sp_help_agent_profile

sp_addpublisher70
sp_helparticle

sp_addpullsubscription
sp_helparticlecolumns

sp_addpullsubscription_agent
sp_helparticledts

sp_addscriptexec
sp_helpdistpublisher

sp_addsubscriber
sp_helpdistributiondb

sp_addsubscriber_schedule
sp_helpdistributor

sp_addsubscription
sp_helpmergealternatepublisher

sp_addsynctriggers
sp_helpmergearticle

sp_addtabletocontents
sp_helpmergearticlecolumn

sp_adjustpublisheridentityrange
sp_helpmergearticleconflicts

sp_article_validation
sp_helpmergeconflictrows

sp_articlecolumn
sp_helpmergedeleteconflictrows

sp_articlefilter
sp_helpmergefilter

sp_articlesynctranprocs
sp_helpmergepublication

sp_articleview
sp_helpmergepullsubscription

sp_attachsubscription
sp_helpmergesubscription

sp_browsesnapshotfolder
sp_helppublication

sp_browsemergesnapshotfolder
sp_help_publication_access

sp_browsereplcmds
sp_helppullsubscription

sp_change_agent_parameter
sp_helpreplfailovermode

sp_change_agent_profile
sp_helpreplicationdboption

sp_changearticle
sp_helpreplicationoption

sp_changedistpublisher
sp_helpsubscriberinfo

sp_changedistributiondb
sp_helpsubscription

sp_changedistributor_password
sp_ivindexhasnullcols

sp_changedistributor_property
sp_helpsubscription_properties

sp_changemergearticle
sp_link_publication

sp_changemergefilter
sp_marksubscriptionvalidation

sp_changemergepublication
sp_mergearticlecolumn

sp_changemergepullsubscription
sp_mergecleanupmetadata

sp_changemergesubscription
sp_mergedummyupdate

sp_changepublication
sp_mergesubscription_cleanup

sp_changesubscriber
sp_publication_validation

sp_changesubscriber_schedule
sp_refreshsubscriptions

sp_changesubscriptiondtsinfo
sp_reinitmergepullsubscription

sp_changesubstatus
sp_reinitmergesubscription

sp_change_subscription_properties
sp_reinitpullsubscription

sp_check_for_sync_trigger
sp_reinitsubscription

sp_copymergesnapshot
sp_removedbreplication

sp_copysnapshot
sp_repladdcolumn

sp_copysubscription
sp_replcmds

sp_deletemergeconflictrow
sp_replcounters

sp_disableagentoffload
sp_repldone

sp_drop_agent_parameter
sp_repldropcolumn

sp_drop_agent_profile
sp_replflush

sp_droparticle
sp_replicationdboption

sp_dropanonymouseagent
sp_replication_agent_checkup

sp_dropdistpublisher
sp_replqueuemonitor

sp_dropdistributiondb
sp_replsetoriginator

sp_dropmergealternatepublisher
sp_replshowcmds

sp_dropdistributor
sp_repltrans

sp_dropmergearticle
sp_restoredbreplication

sp_dropmergefilter
sp_revoke_publication_access

sp_scriptsubconflicttable

sp_dropmergepublication
sp_script_synctran_commands

sp_dropmergepullsubscription
sp_setreplfailovermode

sp_showrowreplicainfo

sp_dropmergesubscription
sp_subscription_cleanup

sp_droppublication
sp_table_validation

sp_droppullsubscription
sp_update_agent_profile

sp_dropsubscriber
sp_validatemergepublication

sp_dropsubscription
sp_validatemergesubscription

sp_dsninfo
sp_vupgrade_replication

sp_dumpparamcmd


Security Procedures
sp_addalias
sp_droprolemember

sp_addapprole
sp_dropserver

sp_addgroup
sp_dropsrvrolemember

sp_addlinkedsrvlogin
sp_dropuser

sp_addlogin
sp_grantdbaccess

sp_addremotelogin
sp_grantlogin

sp_addrole
sp_helpdbfixedrole

sp_addrolemember
sp_helpgroup

sp_addserver
sp_helplinkedsrvlogin

sp_addsrvrolemember
sp_helplogins

sp_adduser
sp_helpntgroup

sp_approlepassword
sp_helpremotelogin

sp_changedbowner
sp_helprole

sp_changegroup
sp_helprolemember

sp_changeobjectowner
sp_helprotect

sp_change_users_login
sp_helpsrvrole

sp_dbfixedrolepermission
sp_helpsrvrolemember

sp_defaultdb
sp_helpuser

sp_defaultlanguage
sp_MShasdbaccess

sp_denylogin
sp_password

sp_dropalias
sp_remoteoption

sp_dropapprole
sp_revokedbaccess

sp_dropgroup
sp_revokelogin

sp_droplinkedsrvlogin
sp_setapprole

sp_droplogin
sp_srvrolepermission

sp_dropremotelogin
sp_validatelogins

sp_droprole


SQL Mail Procedures
sp_processmail
xp_sendmail

xp_deletemail
xp_startmail

xp_findnextmsg
xp_stopmail

xp_readmail


SQL Profiler Procedures
sp_trace_create
sp_trace_setfilter

sp_trace_generateevent
sp_trace_setstatus

sp_trace_setevent


SQL Server Agent Procedures
sp_add_alert
sp_help_jobhistory

sp_add_category
sp_help_jobschedule

sp_add_job
sp_help_jobserver

sp_add_jobschedule
sp_help_jobstep

sp_add_jobserver
sp_help_notification

sp_add_jobstep
sp_help_operator

sp_add_notification
sp_help_targetserver

sp_add_operator
sp_help_targetservergroup

sp_add_targetservergroup
sp_helptask

sp_add_targetsvrgrp_member
sp_manage_jobs_by_login

sp_addtask
sp_msx_defect

sp_apply_job_to_targets
sp_msx_enlist

sp_delete_alert
sp_post_msx_operation

sp_delete_category
sp_purgehistory

sp_delete_job
sp_purge_jobhistory

sp_delete_jobschedule
sp_reassigntask

sp_delete_jobserver
sp_remove_job_from_targets

sp_delete_jobstep
sp_resync_targetserver

sp_delete_notification
sp_start_job

sp_delete_operator
sp_stop_job

sp_delete_targetserver
sp_update_alert

sp_delete_targetservergroup
sp_update_category

sp_delete_targetsvrgrp_member
sp_update_job

sp_droptask
sp_update_jobschedule

sp_help_alert
sp_update_jobstep

sp_help_category
sp_update_notification

sp_help_downloadlist
sp_update_operator

sp_helphistory
sp_update_targetservergroup

sp_help_job
sp_updatetask

xp_sqlagent_proxy_account


System Procedures
sp_add_data_file_recover_suspect_db
sp_helpconstraint

sp_addextendedproc
sp_helpdb

sp_addextendedproperty
sp_helpdevice

sp_add_log_file_recover_suspect_db
sp_helpextendedproc

sp_addmessage
sp_helpfile

sp_addtype
sp_helpfilegroup

sp_addumpdevice
sp_helpindex

sp_altermessage
sp_helplanguage

sp_autostats
sp_helpserver

sp_attach_db
sp_helpsort

sp_attach_single_file_db
sp_helpstats

sp_bindefault
sp_helptext

sp_bindrule
sp_helptrigger

sp_bindsession
sp_indexoption

sp_certify_removable
sp_invalidate_textptr

sp_configure
sp_lock

sp_create_removable
sp_monitor

sp_createstats
sp_procoption

sp_cycle_errorlog
sp_recompile

sp_datatype_info
sp_refreshview

sp_dbcmptlevel
sp_releaseapplock

sp_dboption
sp_rename

sp_dbremove
sp_renamedb

sp_delete_backuphistory
sp_resetstatus

sp_depends
sp_serveroption

sp_detach_db
sp_setnetname

sp_dropdevice
sp_settriggerorder

sp_dropextendedproc
sp_spaceused

sp_dropextendedproperty
sp_tableoption

sp_dropmessage
sp_unbindefault

sp_droptype
sp_unbindrule

sp_executesql
sp_updateextendedproperty

sp_getapplock
sp_updatestats

sp_getbindtoken
sp_validname

sp_help
sp_who


Web Assistant Procedures
sp_dropwebtask
sp_makewebtask

sp_enumcodepages
sp_runwebtask


XML Procedures
sp_xml_preparedocument
sp_xml_removedocument


General Extended Procedures
xp_cmdshell
xp_logininfo

xp_enumgroups
xp_msver

xp_findnextmsg
xp_revokelogin

xp_grantlogin
xp_sprintf

xp_logevent
xp_sqlmaint

xp_loginconfig
xp_sscanf


How do you DROP a PROCEDURE?
Removes one or more stored procedures or procedure groups from the current database.
Syntax
DROP PROCEDURE { procedure } [ ,...n ]

How do you recompile a procedure?
sp_recompile
Causes stored procedures and triggers to be recompiled the next time they are run.
Syntax
sp_recompile [ @objname = ] 'object'
sp_recompile looks for an object in the current database only.
The queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

Note Microsoft® SQL Server™ automatically recompiles stored procedures and triggers when it is advantageous to do so.

What are the Rules for Programming Stored Procedures ?
Rules for programming stored procedures include:

The CREATE PROCEDURE definition itself can include any number and type of SQL statements except for the following CREATE statements, which cannot be used anywhere within a stored procedure:

CREATE DEFAULT CREATE TRIGGER
CREATE PROCEDURE CREATE VIEW
CREATE RULE

Other database objects can be created within a stored procedure. You can reference an object created in the same stored procedure as long as it is created before it is referenced.

You can reference temporary tables within a stored procedure.
If you create a local temporary table inside a stored procedure, the temporary table exists only for the purposes of the stored procedure; it disappears when you exit the stored procedure.

If you execute a stored procedure that calls another stored procedure, the called stored procedure can access all objects created by the first stored procedure, including temporary tables.


If you execute a remote stored procedure that makes changes on a remote instance of Microsoft® SQL Server™ 2000, those changes cannot be rolled back. Remote stored procedures do not take part in transactions.

The maximum number of parameters in a stored procedure is 2100.
The maximum number of local variables in a stored procedure is limited only by available memory.

Depending on available memory, the maximum size of a stored procedure is 128 megabytes (MB).


How do you Encrypt Procedure?
If you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause.
The procedure definition is then stored in an unreadable form.
After a stored procedure is encrypted, its definition cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator.

What is EXECUTE in procedure?
Executes a scalar-valued, user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure. Also supports the execution of a character string within a Transact-SQL batch.