sqldemo

Sql Server Interview Question: Miscellaneous

2009-06-27T06:16:00.000-07:00

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

2009-06-27T06:12:00.000-07:00

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

2009-06-27T06:04:00.000-07:00

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

2009-06-27T06:03:00.000-07:00

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

2009-06-27T05:56:00.000-07:00

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

2009-06-27T05:43:00.000-07:00

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.

Sql Server Interview Question:Create View

2009-06-27T05:39:00.000-07:00

What is view?
CREATE VIEW
Creates a virtual table that represents the data in one or more tables in an alternative way. CREATE VIEW must be the first statement in a query batch.

What are the restrictions when creating view?
There are a few restrictions on the SELECT clauses in a view definition. A CREATE VIEW statement cannot:

Include COMPUTE or COMPUTE BY clauses.
Include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.
Include the INTO keyword.
Reference a temporary table or a table variable.

Because select_statement uses the SELECT statement, it is valid to use and hints as specified in the FROM clause. For more information, see FROM and SELECT.
Functions can be used in the select_statement.
select_statement can use multiple SELECT statements separated by UNION or UNION ALL.

What is WITH CHECK OPTION on views?
Forces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION ensures the data remains visible through the view after the modification is committed.

What is WITH ENCRYPTION on views?
Indicates that SQL Server encrypts the system table columns containing the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

What do you know SCHEMABINDING?
Binds the view to the schema. When SCHEMABINDING is specified, the select_statement must include the two-part names (owner.object) of tables, views, or user-defined functions referenced.

Views or tables participating in a view created with the schema binding clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, SQL Server raises an error. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.

What is VIEW_METADATA?
Specifies that SQL Server will return to the DBLIB, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata returned by SQL Server to the client-side DB-LIB, ODBC, and OLE DB APIs, which allow the client-side APIs to implement updatable client-side cursors. Browse-mode meta data includes information about the base table that the columns in the result set belong to.

For views created with VIEW_METADATA option, the browse-mode meta data returns the view name as opposed to the base table names when describing columns from the view in the result set.

When a view is created WITH VIEW_METADATA, all its columns (except for timestamp) are updatable if the view has INSERT or UPDATE INSTEAD OF triggers

How many columns a view can reference?
A view can reference a maximum of 1,024 columns.

Notes about views:
When querying through a view, Microsoft® SQL Server™ checks to make sure that all the database objects referenced anywhere in the statement exist, that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules.

A check that fails returns an error message. A successful check translates the action into an action against the underlying table(s).

If a view depends on a table (or view) that was dropped, SQL Server produces an error message if anyone tries to use the view. If a new table (or view) is created, and the table structure does not change from the previous base table, to replace the one dropped, the view again becomes usable. If the new table (or view) structure changes, then the view must be dropped and recreated.

When a view is created, the name of the view is stored in the sysobjects table. Information about the columns defined in a view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table.

In addition, the text of the CREATE VIEW statement is added to the syscomments table. This is similar to a stored procedure; when a view is executed for the first time, only its query tree is stored in the procedure cache. Each time a view is accessed, its execution plan is recompiled.

The result of a query using an index on a view defined with numeric or float expressions may be different from a similar query that does not use the index on the view. This difference may be the result of rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.

What is Updatable Views
Microsoft SQL Server 2000 enhances the class of updatable views in two ways:

INSTEAD OF Triggers: INSTEAD OF triggers can be created on a view in order to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger allows the user to specify the set of actions that need to take place in order to process the data modification statement. Thus, if an INSTEAD OF trigger exists for a view on a given data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement. For more information about INSTEAD OF triggers, see Designing INSTEAD OF triggers.

Partitioned Views: If the view is of a specified form called 'partitioned view,' the view is updatable, subject to certain restrictions. Partitioned views and their updatability are discussed later in this topic.

When needed, SQL Server will distinguish Local Partitioned Views as the views in which all participating tables and the view are on the same SQL Server, and Distributed Partitioned Views as the views in which at least one of the tables in the view resides on a different (remote) server.

If a view does not have INSTEAD OF triggers, or if it is not a partitioned view, then it is updatable only if the following conditions are satisfied:

The select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION (unless the view is a partitioned view as described later in this topic), or DISTINCT clauses. Aggregate functions can be used in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.

select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.

What is Partitioned Views?
A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same SQL Server or in a group of autonomous SQL Server 2000 servers, called Federated SQL Server 2000 Servers.

What are then Conditions for Creating Partitioned Views?
1. SELECT list
All columns in the member tables should be selected in the column list of the view definition.

The columns in the same ordinal position of each select_list should be of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.

The same column cannot be used multiple times in the SELECT list.
1. Partitioning column
The partitioning column is a part of the PRIMARY KEY of the table.
It cannot be a computed column.

If there is more than one constraint on the same column in a member table, SQL Server ignores all the constraints and will not consider them when determining whether or not the view is a partitioned view. To meet the conditions of the partitioned view, there should be only one partitioning constraint on the partitioning column.

2. Member tables (or underlying tables T1, ..., Tn)
The tables can be either local tables or tables from other SQL Servers referenced either through a four-part name or an OPENDATASOURCE- or OPENROWSET-based name. (The OPENDATASOURCE and OPENROWSET syntax can specify a table name, but not a pass-through query.) For more information, see OPENDATASOURCE and OPENROWSET .
If one or more of the member tables are remote, the view is called distributed partitioned view, and additional conditions apply. They are discussed later in this section.

The same table cannot appear twice in the set of tables that are being combined with the UNION ALL statement.

The member tables cannot have indexes created on computed columns in the table.
The member tables should have all PRIMARY KEY constraints on an identical number of columns.

All member tables in the view should have the same ANSI padding setting (which is set using the user options option in sp_configure or the SET option).

What are the Conditions for Modifying Partitioned Views?
Only the Developer and Enterprise Editions of SQL Server 2000 allow INSERT, UPDATE, and DELETE operations on partitioned views. To modify partitioned views, the statements must meet these conditions:

The INSERT statement must supply values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow NULLs. For those member table columns that have DEFAULT definitions, the statements cannot use the keyword DEFAULT explicitly.

The value being inserted into the partitioning column should satisfy at least one of the underlying constraints; otherwise, the INSERT action will fail with a constraint violation.

UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause even if the column has a DEFAULT value defined in the corresponding member table.

PRIMARY KEY columns cannot be modified through an UPDATE statement if the member tables have text, ntext, or image columns.

Columns in the view that are an IDENTITY column in one or more of the member tables cannot be modified through an INSERT or UPDATE statement.

If one of the member tables contains a timestamp column, the view cannot be modified through an INSERT or UPDATE statement.

INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.

How do you alter a view?
ALTER VIEW
Alters a previously created view (created by executing CREATE VIEW), including indexed views, without affecting dependent stored procedures or triggers and without changing permissions

How do you DROP a VIEW?
Removes one or more views from the current database. DROP VIEW can be executed against indexed views.

Sql Server Interview Question:Create Database

2009-06-27T05:35:00.000-07:00

What does the Create Database statement do?

Creates a new database and the files used to store the database, or attaches a database from the files of a previously created database.

Database names must be unique within a server and conform to the rules for identifiers. database_name can be a maximum of 128 characters, unless no logical name is specified for the log.

If no logical log file name is specified, Microsoft® SQL Server™ generates a logical name by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical log file name is less than 128 characters.

What is LOG ON on create database definition?
Specifies that the disk files used to store the database log (log files) are explicitly defined. The keyword is followed by a comma-separated list of items defining the log files.
If LOG ON is not specified, a single log file is automatically created with a system-generated name and a size that is 25 percent of the sum of the sizes of all the data files for the database.

What is FOR ATTACH?
Specifies that a database is attached from an existing set of operating system files. There must be a entry specifying the first primary file.

The only other entries needed are those for any files that have a different path from when the database was first created or last attached. A entry must be specified for these files.

The database attached must have been created using the same code page and sort order as SQL Server. Use the sp_attach_db system stored procedure instead of using CREATE DATABASE FOR ATTACH directly. Use CREATE DATABASE FOR ATTACH only when you must specify more than 16 items.

If you attach a database to a server other than the server from which the database was detached, and the detached database was enabled for replication, you should run sp_removedbreplication to remove replication from the database.

What is primary when creating create database definition?

PRIMARY
Specifies that the associated list defines the primary file. The primary filegroup contains all of the database system tables. It also contains all objects not assigned to user filegroups.

The first entry in the primary filegroup becomes the primary file, which is the file containing the logical start of the database and its system tables. A database can have only one primary file. If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.

What is the default size when we create database?
SIZE
Specifies the size of the file defined in the . When a SIZE parameter is not supplied in the for a primary file, SQL Server uses the size of the primary file in the model database. When a SIZE parameter is not specified in the for a secondary or log file, SQL Server makes the file 1 MB.

How do you define maxsize on create database?
MAXSIZE
Specifies the maximum size to which the file defined in the can grow.
max_size

Is the maximum size to which the file defined in the can grow. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file grows until the disk is full.

What is UNLIMITED?
Specifies that the file defined in the grows until the disk is full.

What is FILEGROWTH
Specifies the growth increment of the file defined in the . The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.

What is growth_increment
Is the amount of space added to the file each time new space is needed. Specify a whole number; do not include a decimal. A value of 0 indicates no growth. The value can be specified in MB, KB, GB, TB, or percent (%).
If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. If FILEGROWTH is not specified, the default value is 10 percent and the minimum value is 64 KB. The size specified is rounded to the nearest 64 KB.

How SQL Server implements the CREATE DATABASE statement?
SQL Server implements the CREATE DATABASE statement in two steps:
1. SQL Server uses a copy of the model database to initialize the database and its meta data.
2. SQL Server then fills the rest of the database with empty pages, except for pages that have internal data recording how the space is used in the database.
Any user-defined objects in the model database are therefore copied to all newly created databases. You can add to the model database any objects, such as tables, views, stored procedures, data types, and so on, to be included in all databases

How many number of database can be specified per sql server?
A maximum of 32,767 databases can be specified on a server.

How many of files used to store database?
There are three types of files used to store a database:

The primary file contains the startup information for the database. The primary file is also used to store data. Every database has one primary file.

Secondary files hold all of the data that does not fit in the primary data file. Databases need not have any secondary data files if the primary file is large enough to hold all of the data in the database. Other databases may be large enough to need multiple secondary data files, or they may use secondary files on separate disk drives to spread the data across multiple disks.

Transaction log files hold the log information used to recover the database. There must be at least one transaction log file for each database, although there may be more than one. The minimum size for a transaction log file is 512 KB.

Every database has at least two files, a primary file and a transaction log file.
Although 'os_file_name' can be any valid operating system file name, the name more clearly reflects the purpose of the file if you use the following recommended extensions.

File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
Note The master database should be backed up when a user database is created.

What is filegroup on create database definition?

All databases have at least a primary filegroup. All system tables are allocated in the primary filegroup.

A database can also have user-defined filegroups. If an object is created with an ON filegroup clause specifying a user-defined filegroup, then all the pages for the object are allocated from the specified filegroup.

The pages for all user objects created without an ON filegroup clause, or with an ON DEFAULT clause, are allocated from the default filegroup.

When a database is first created the primary filegroup is the default filegroup. You can specify a user-defined filegroup as the default filegroup using ALTER DATABASE:

How do you report a database?
To display a report on a database, or on all the databases for an instance of SQL Server, execute sp_helpdb.

How do you report database space used?
For a report on the space used in a database, use sp_spaceused.

How do you report filegroup, files on database?
For a report on the filegroups in a database use sp_helpfilegroup, and use sp_helpfile for a report of the files in a database.

How do you rename a database?
sp_renamedb Changes the name of a database.

How do you alter the database?
ALTER DATABASE
Adds or removes files and filegroups from a database. Can also be used to modify the attributes of files and filegroups, such as changing the name or size of a file. ALTER DATABASE provides the ability to change the database name, filegroup names, and the logical names of data files and log files.

How do you drop a database?
DROP DATABASE
Removes one or more databases from Microsoft® SQL Server™. Removing a database deletes the database and the disk files used by the database.
Syntax
DROP DATABASE database_name [ ,...n ]
To use DROP DATABASE, the database context of the connection must be in the master database.

Notes about drop database:
DROP DATABASE removes damaged databases marked as suspect and removes the specified database. Before dropping a database used in replication, first remove replication.

Any database published for transactional replication, or published or subscribed to merge replication cannot be dropped. If a database is damaged and replication cannot first be removed, in most cases you still can drop the database by marking it as an offline database.

A dropped database can be re-created only by restoring a backup. You cannot drop a database currently in use (open for reading or writing by any user).

When database is created the master database should be backed up
When a database is dropped, the master database should be backed up.
System databases (msdb, master, model, tempdb) cannot be dropped.

Sql Server Interview Question:Table and Index Architecture

2009-06-27T05:31:00.000-07:00

Table and Index Architecture
Objects in a Microsoft® SQL Server™ 2000 database are stored as a collection of 8-KB pages

Notes about Table and Index:
SQL Server 2000 supports indexes on views. The first index allowed on a view is a clustered index.
At the time a CREATE INDEX statement is executed on a view, the result set for the view is materialized and stored in the database with the same structure as a table that has a clustered index

The data rows for each table or indexed view are stored in a collection of 8-KB data pages. Each data page has a 96-byte header containing system information such as the identifier (ID) of the table that owns the page.

The page header also includes pointers to the next and previous pages that are used if the pages are linked in a list. A row offset table is at the end of the page. Data rows fill the rest of the page.

Organization of Data Pages

SQL Server 2000 tables use one of two methods to organize their data pages:

Clustered tables are tables that have a clustered index.
The data rows are stored in order based on the clustered index key.
The index is implemented as a B-tree index structure that supports fast retrieval of the rows based on their clustered index key values.
The pages in each level of the index, including the data pages in the leaf level, are linked in a doubly-linked list, but navigation from one level to another is done using key values.

Heaps are tables that have no clustered index.
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.

Indexed views have the same storage structure as clustered tables.
SQL Server also supports up to 249 nonclustered indexes on each table or indexed view. The nonclustered indexes have a B-tree index structure similar to the one in clustered indexes.

The difference is that nonclustered indexes have no effect on the order of the data rows. Clustered tables and indexed views keep their data rows in order based on the clustered index key.

The collection of data pages for a heap is not affected if nonclustered indexes are defined for the table.

The data pages remain in a heap unless a clustered index is defined.
The pages holding text, ntext, and image data are managed as a single unit for each table. All of the text, ntext, and image data for a table is stored in one collection of pages.

All of the page collections for tables, indexes and indexed views are anchored by page pointers in the sysindexes table.

Every table and indexed view has one collection of data pages, plus additional collections of pages to implement each index defined for the table or view.
Each table, index and indexed view has a row in sysindexes uniquely identified by the combination of the object identifier (id) column and the index identifier (indid) column.

The allocation of pages to tables, indexes, and indexed views is managed by a chain of IAM pages.

The column sysindexes.FirstIAM points to first IAM page in the chain of IAM pages managing the space allocated to the table, index or indexed view.
Each table has a set of rows in sysindexes:

A heap has a row in sysindexes with indid = 0.
The FirstIAM column points to the IAM chain for the collection of data pages for the table. The server uses the IAM pages to find the pages in the data page collection because they are not linked together.

A clustered index on a table or view has a row in sysindexes with indid = 1.
The root column points to the top of the clustered index B-tree. The server uses the index B-tree to find the data pages.

Each nonclustered index created for a table or view has a row in sysindexes.
The values for indid in the rows for each nonclustered index range from 2 through 250. The root column points to the top of the nonclustered index B-tree.
Each table that has at least one text, ntext, or image column also has a row in sysindexes with indid = 255.

The column FirstIAM points to the chain of IAM pages that manage the text, ntext, and image pages.

Sql Server Interview Question:Create Index

2009-06-27T05:26:00.000-07:00

How do you create a index?
Creates an index on a given table or view.
Only the table or view owner can create indexes on that table. The owner of a table or view can create an index at any time, whether or not there is data in the table. Indexes can be created on tables or views in another database by specifying a qualified database name.

What is unique index?
Creates a unique index (one in which no two rows are permitted to have the same index value) on a table or view. A clustered index on a view must be UNIQUE.

Can we create index on table with existing data?
Microsoft® SQL Server™ checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created.

Which situation create unique index fail?
SQL Server does not allow the creation of a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set. If attempted, SQL Server displays an error message; duplicates must be eliminated before a unique index can be created on the column(s).

What is clustered index?

How many clustered index can we have per table?
CLUSTERED
Creates an object where the physical order of rows is the same as the indexed order of the rows, and the bottom (leaf) level of the clustered index contains the actual data rows. A table or view is allowed one clustered index at a time.

Why do we need to create clustered index first before creating nonclustered index?
Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created.
If CLUSTERED is not specified, a nonclustered index is created.

Note Because the leaf level of a clustered index and its data pages are the same by definition, creating a clustered index and using the ON filegroup clause effectively moves a table from the file on which the table was created to the new filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. It is important that the filegroup have at least 1.2 times the space required for the entire table.

What is NONCLUSTERED index?
Creates an object that specifies the logical ordering of a table. With a nonclustered index, the physical order of the rows is independent of their indexed order. The leaf level of a nonclustered index contains index rows. Each index row contains the nonclustered key value and one or more row locators that point to the row that contains the value. If the table does not have a clustered index, the row locator is the row's disk address. If the table does have a clustered index, the row locator is the clustered index key for the row.

How many nonclustered index we can have per table?
Each table can have as many as 249 nonclustered indexes (regardless of how they are created: implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX). Each index can provide access to the data in a different sort order.

Is it possible to create nonclustered index on views?
For indexed views, nonclustered indexes can be created only on a view with a clustered index already defined. Thus, the row locator of a nonclustered index on an indexed view is always the clustered key of the row.

Can we create nonclustered indexes on views?
A unique clustered index must be created on a view before any nonclustered index is created.

Why do we need to create indexes on view?
Indexed views may be used by the query optimizer in SQL Server Enterprise or Developer edition to speed up the query execution. The view does not need to be referenced in the query for the optimizer to consider that view for a substitution.
When creating indexed views or manipulating rows in tables participating in an indexed view, seven SET options must be assigned specific values.

The SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING must be ON. The SET option NUMERIC_ROUNDABORT must be OFF.
If any of these settings is different, data modification statements (INSERT, UPDATE, DELETE) on any table referenced by an indexed view fail and SQL Server raises an error listing all SET options that violate setting requirements.

In addition, for a SELECT statement that involves an indexed view, if the values of any of the SET options are not the required values, SQL Server processes the SELECT without considering the indexed view substitution. This ensures correctness of query result in cases where it can be affected by the above SET options.

Which are the datatypes not supported by index?
Columns consisting of the ntext, text, or image data types cannot be specified as columns for an index. In addition, a view cannot include any text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.

What is a composite index?
Composite indexes are used when two or more columns are best searched as a unit or if many queries reference only the columns specified in the index
What is constraint when defining composite indexes?
As many as 16 columns can be combined into a single composite index. All the columns in a composite index must be in the same table.
The maximum allowable size of the combined index values is 900 bytes. That is, the sum of the lengths of the fixed-size columns that make up the composite index cannot exceed 900 bytes

What is fillfactor?
Specifies a percentage that indicates how full SQL Server should make the leaf level of each index page during index creation.
When an index page fills up, SQL Server must take time to split the index page to make room for new rows, which is quite expensive. For update-intensive tables, a properly chosen FILLFACTOR value yields better update performance than an improper FILLFACTOR value. The value of the original FILLFACTOR is stored with the index in sysindexes.

When FILLFACTOR is specified, SQL Server rounds up the number of rows to be placed on each page.
For example, issuing CREATE CLUSTERED INDEX ... FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33 percent. Assume that SQL Server calculates that 5.2 rows is 33 percent of the space on a page. SQL Server rounds so that six rows are placed on each page.

Notes about fillfactor
An explicit FILLFACTOR setting applies only when the index is first created. SQL Server does not dynamically keep the specified percentage of empty space in the pages.
User-specified FILLFACTOR values can be from 1 through 100. If no value is specified, the default is 0. When FILLFACTOR is set to 0, only the leaf pages are filled. You can change the default FILLFACTOR setting by executing sp_configure.
Use a FILLFACTOR of 100 only if no INSERT or UPDATE statements will occur, such as with a read-only table. If FILLFACTOR is 100, SQL Server creates indexes with leaf pages 100 percent full. An INSERT or UPDATE made after the creation of an index with a 100 percent FILLFACTOR causes page splits for each INSERT and possibly each UPDATE.
Smaller FILLFACTOR values, except 0, cause SQL Server to create new indexes with leaf pages that are not completely full. For example, a FILLFACTOR of 10 can be a reasonable choice when creating an index on a table known to contain a small portion of the data that it will eventually hold. Smaller FILLFACTOR values also cause each index to take more storage space.
The following table illustrates how the pages of an index are filled up if FILLFACTOR is specified.
FILLFACTOR Intermediate page Leaf page
0 percent One free entry 100 percent full
1 - 99 percent One free entry <= FILLFACTOR percent full
100 percent One free entry 100 percent full

One free entry is the space on the page that can accommodate another index entry.

Important Creating a clustered index with a FILLFACTOR affects the amount of storage space the data occupies because SQL Server redistributes the data when it creates the clustered index

What is IGNORE_DUP_KEY?
Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.
If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.
The table shows when IGNORE_DUP_KEY can be used.
Index type Options
Clustered Not allowed
Unique clustered IGNORE_DUP_KEY allowed
Nonclustered Not allowed
Unique nonclustered IGNORE_DUP_KEY allowed

When dropping clustered indexes what happen to the clustering keys?
What is DROP_EXISTING?
Specifies that the named, preexisting clustered or nonclustered index should be dropped and rebuilt. The index name specified must be the same as a currently existing index. Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account.

What is the advantange of drop_existing?
The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes.
The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.
If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted.
A clustered index cannot be converted to a nonclustered index using the DROP_EXISTING clause;
however, a unique clustered index can be changed to a non-unique index, and vice versa.

Notes about index space:
Space is allocated to tables and indexes in increments of one extent (eight 8-kilobyte pages) at a time. Each time an extent is filled, another is allocated.

Indexes on very small or empty tables will use single page allocations until eight pages have been added to the index and then will switch to extent allocations. For a report on the amount of space allocated and used by an index, use sp_spaceused.
Creating a clustered index requires space available in your database equal to approximately 1.2 times the size of the data.

This is space in addition to the space used by the existing table; the data is duplicated in order to create the clustered index, and the old, nonindexed data is deleted when the index is complete.
When using the DROP_EXISTING clause, the space needed for the clustered index is the amount of space equal to the space requirements of the existing index.
The amount of additional space required also may be affected by the FILLFACTOR specified.
The CREATE INDEX statement is optimized like any other query. The SQL Server query processor may choose to scan another index instead of performing a table scan to save on I/O operations

How do you report index?
To display a report on an object's indexes, execute sp_helpindex.

Can we create indexes and triggers on temporary table?
Indexes can be created on a temporary table. When the table is dropped or the session ends, all indexes and triggers are dropped.

What is Variable type columns in indexes?
The maximum size allowed for an index key is 900 bytes, but SQL Server 2000 allows indexes to be created on columns that may have large variable type columns with a maximum size greater than 900 bytes.
During index creation, SQL Server checks the following conditions:
The sum of all fixed data columns that participate in the index definition must be less or equal to 900 bytes.

When the index to be created is composed of fixed data columns only, the total size of the fixed data columns must be less or equal to 900 bytes. Otherwise, the index will not be created and SQL Server will return an error.

If the index definition is composed of fixed- and variable-type columns, and the fixed-data columns meet the previous condition (less or equal to 900 bytes), SQL Server still checks the total size of the variable type columns.

If the maximum size of the variable-type columns plus the size of the fixed-data columns is greater than 900 bytes, SQL Server creates the index, but returns a warning to the user.

The warning alerts the user that if subsequent insert or update actions on the variable-type columns result in a total size greater than 900 bytes, the action will fail and the user will get a run-time error.

Likewise, if the index definition is composed of variable-type columns only, and the maximum total size of these columns is greater than 900 bytes, SQL Server will create the index, but return a warning.

What needs to be Considerations when indexing computed columns and views?
In SQL Server 2000, indexes also can be created on computed columns and views. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.
Creation of an index on a computed column or view may cause the failure of an INSERT or UPDATE operation that previously worked. Such a failure may take place when the computed column results in arithmetic error

What are the Restrictions on indexed views?
The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords. It cannot have a subquery.
The SELECT list may not include asterisks (*), 'table.*' wildcard lists, DISTINCT, COUNT(*), COUNT(), computed columns from the base tables, and scalar aggregates.

Nonaggregate SELECT lists cannot have expressions. Aggregate SELECT list (queries that contain GROUP BY) may include SUM and COUNT_BIG(); it must contain COUNT_BIG(*). Other aggregate functions (MIN, MAX, STDEV,...) are not allowed.

Complex aggregation using AVG cannot participate in the SELECT list of the indexed view.
However, if a query uses such aggregation, the optimizer is capable of using this indexed view to substitute AVG with a combination of simple aggregates SUM and COUNT_BIG.

A column resulting from an expression that either evaluates to a float data type or uses float expressions for its evaluation cannot be a key of an index in an indexed view or on a computed column in a table. Such columns are called nonprecise. Use the COLUMNPROPERTY function to determine if a particular computed column or a column in a view is precise.

Indexed views are subject to these additional restrictions:

The creator of the index must own the tables. All tables, the view, and the index, must be created in the same database.

The SELECT statement defining the indexed view may not contain views, rowset functions, inline functions, or derived tables. The same physical table may occur only once in the statement.

In any joined tables, no OUTER JOIN operations are allowed.

No subqueries or CONTAINS or FREETEXT predicates are allowed in the search condition.

If the view definition contains a GROUP BY clause, all grouping columns as well as the COUNT_BIG(*) expression must appear in the view's SELECT list. Also, these columns must be the only columns in the CREATE UNIQUE CLUSTERED INDEX clause.

Is DROP INDEX statement drop indexes created by primary key or unique key?
Removes one or more indexes from the current database.
The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints (created by using the PRIMARY KEY or UNIQUE options of either the CREATE TABLE or ALTER TABLE statements, respectively).

Notes about Drop index statement:
After DROP INDEX is executed, all the space previously occupied by the index is regained. This space can then be used for any database object.
DROP INDEX cannot be specified on an index on a system table.

To drop the indexes created to implement PRIMARY KEY or UNIQUE constraints, the constraint must be dropped. For more information about dropping constraints, see "ALTER TABLE" in this volume.

Nonclustered indexes have different pointers to data rows depending on whether or not a clustered index is defined for the table.

If there is a clustered index the leaf rows of the nonclustered indexes use the clustered index keys to point to the data rows.

If the table is a heap, the leaf rows of nonclustered indexes use row pointers. If you drop a clustered index on a table with nonclustered indexes, all the nonclustered indexes are rebuilt to replace the clustered index keys with row pointers
.
Similarly, when the clustered index of an indexed view is dropped, all nonclustered indexes on the same view are dropped automatically.

Sometimes indexes are dropped and re-created to reorganize the index, for example to apply a new fillfactor or to reorganize data after a bulk load. It is more efficient to use CREATE INDEX and the WITH DROP_EXISTING clause for this, especially for clustered indexes. Dropping a clustered index causes all the nonclustered indexes to be rebuilt.

If the clustered index is then re-created, the nonclustered indexes are rebuilt once again to replace the row pointers with clustered index keys.

The WITH DROP_EXISTING clause of CREATE INDEX has optimizations to prevent this overhead of rebuilding the nonclustered indexes twice. DBCC DBREINDEX can also be used and has the advantage that it does not require that the structure of the index be known.

What is DBCC DBREINDEX
Rebuilds one or more indexes for a table in the specified database.

Notes about DBCC DBREINDEX:
DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints.

This means an index can be rebuilt without knowing the table's structure or constraints, which could occur after a bulk copy of data into the table.

If either index_name or fillfactor is specified, all preceding parameters must also be specified.

DBCC DBREINDEX can rebuild all of the indexes for a table in one statement, which is easier than coding multiple DROP INDEX and CREATE INDEX statements.
Because the work is done by one statement, DBCC DBREINDEX is automatically atomic, while individual DROP INDEX and CREATE INDEX statements would have to be put in a transaction to be atomic.

Also, DBCC DBREINDEX can take advantage of more optimizations with DBCC DBREINDEX than it can with individual DROP INDEX and CREATE INDEX statements.
DBCC DBREINDEX is not supported for use on system tables.

Sql Server Interview Question: Default

2009-06-27T05:23:00.000-07:00

What is the CREATE DEFAULT definition?
Creates an object called a default. When bound to a column or a user-defined data type, a default specifies a value to be inserted into the column to which the object is bound (or into all columns, in the case of a user-defined data type) when no value is explicitly supplied during an insert.

How do you attach a default definition to column?
When a default is created, use sp_bindefault to bind it to a column or to a user-defined data type.

Notes about binding the rule?
If the default is not compatible with the column to which it is bound, Microsoft® SQL Server™ generates an error message when trying to insert the default value. For example, N/A cannot be used as a default for a numeric column.
If the default value is too long for the column to which it is bound, the value is truncated.
CREATE DEFAULT statements cannot be combined with other Transact-SQL statements in a single batch.

What you need to do before dropping the default?
A default must be dropped before creating a new one of the same name, and the default must be unbound by executing sp_unbindefault before it is dropped.

What will happen when you have both rule and default in a column?
If a column has both a default and a rule associated with it, the default value must not violate the rule. A default that conflicts with a rule is never inserted, and SQL Server generates an error message each time it attempts to insert the default.

When are the time default inserted to the table?
A value is not explicitly inserted.
Either the DEFAULT VALUES or DEFAULT keywords are used with INSERT to insert default values.
If NOT NULL is specified when creating a column and a default is not created for it, an error message is generated when a user fails to make an entry in that column. This table illustrates the relationship between the existence of a default and the definition of a column as NULL or NOT NULL. The entries in the table show the result.
Column definition No entry, no default No entry, default Enter NULL, no default Enter NULL, default
NULL NULL default NULL NULL
NOT NULL Error default error error

How do you rename a default?
To rename a default, use sp_rename

How do get report on default?
For a report on a default, use sp_help.

How do you drop default?
DROP DEFAULT
Removes one or more user-defined defaults from the current database.
The DROP DEFAULT statement does not apply to DEFAULT constraints. For more information about dropping DEFAULT constraints (created by using the DEFAULT option of either the CREATE TABLE or ALTER TABLE statements), see "ALTER TABLE" in this volume.
Syntax
DROP DEFAULT { default } [ ,...n ]

Sql Server Interview Qustion:Rule

2009-06-27T04:23:00.000-07:00

What is the create rule statement?
Creates an object called a rule. When bound to a column or a user-defined data type, a rule specifies the acceptable values that can be inserted into that column.

How many constraint a single column can have?
However, a column can have both a rule and one or more check constraints associated with it. When this is true, all restrictions are evaluated
Check constraint, default constraint, rule

What conditional expression you can use in rule?
A rule can be any expression valid in a WHERE clause and can include such elements as arithmetic operators, relational operators, and predicates (for example, IN, LIKE, BETWEEN). A rule cannot reference columns or other database objects. Built-in functions that do not reference database objects can be included.

What variable you should use when you create Rule?
condition_expression includes one variable. The at sign (@) precedes each local variable. The expression refers to the value entered with the UPDATE or INSERT statement. Any name or symbol can be used to represent the value when creating the rule, but the first character must be the at sign (@).

When you create the rule, whether the rule may apply for existing data?
Rules do not apply to data already existing in the database at the time the rules are created, and rules cannot be bound to system data types. A rule can be created only in the current database.

How do you bind the rule to the column?
After creating a rule, execute sp_bindrule to bind the rule to a column or to a user-defined data type.

Which are the datatypes not supported for rule?
The rule must be compatible with the column data type. A rule cannot be bound to a text, image, or timestamp column. Be sure to enclose character and date constants with single quotation marks (') and to precede binary constants with 0x. For example, "@value LIKE A%" cannot be used as a rule for a numeric column

How do you get report on your Rule?
To get a report on a rule, use sp_help.

How do you display your rule text?
To display the text of a rule, execute sp_helptext with the rule name as the parameter.

How do you rename a rule?
To rename a rule, use sp_rename.

How do you drop a rule?
A rule must be dropped (using DROP RULE)

When dropping rule what you must do?
the rule must be unbound (using sp_unbindrule) before it is dropped. Use sp_unbindrule to unbind a rule from a column.

What will happen when binding new rule to the old rule?
You can bind a new rule to a column or data type without unbinding the previous one; the new rule overrides the previous one. Rules bound to columns always take precedence over rules bound to user-defined data types. Binding a rule to a column replaces a rule already bound to the user-defined data type of that column. But binding a rule to a data type does not replace a rule bound to a column of that user-defined data type. The table shows the precedence in effect when binding rules to columns and to user-defined data types where rules already exist.
Old rule bound to
New rule bound to user-defined data type Column
User-defined data type Old rule replaced No change
Column Old rule replaced Old rule replaced

If a column has both a default and a rule associated with it, the default must fall within the domain defined by the rule. A default that conflicts with a rule is never inserted. SQL Server generates an error message each time it attempts to insert such a default.

How do drop rule?
DROP RULE
Removes one or more user-defined rules from the current database.
Syntax
DROP RULE { rule } [ ,...n ]

Sql Server Interview Question:Alter Table

2009-06-27T04:03:00.000-07:00

What do you do with Alter table statement?
Modifies a table definition by altering, adding, or dropping columns and constraints, or by disabling or enabling constraints and triggers.

Which are the data type column we cannot alter?

The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
A replicated column.
Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.

Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.

Some data type changes may result in a change in the data. For example, changing an nchar or nvarchar column to char or varchar can result in the conversion of extended characters. For more information, see CAST and CONVERT. Reducing the precision and scale of a column may result in data truncation.

When altering column what you have to taken care of?

The previous data type must be implicitly convertible to the new data type.
new_data_type cannot be timestamp.
ANSI null defaults are always on for ALTER COLUMN; if not specified, the column is nullable.
ANSI padding is always on for ALTER COLUMN.
If the altered column is an identity column, new_data_type must be a data type that supports the identity property.
The current setting for SET ARITHABORT is ignored. ALTER TABLE operates as if the ARITHABORT option is ON.

What is WITH CHECK, WITH NOCHECKin the alter table definition?

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY and UNIQUE constraints.

If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. This is not recommended except in rare cases. The new constraint will be evaluated in all future updates. Any constraint violations suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.
Constraints defined WITH NOCHECK are not considered by the query optimizer. These constraints are ignored until all such constraints are re-enabled using ALTER TABLE table CHECK CONSTRAINT ALL.

Which are the constraint use CHECK NOCHECK?

Specifies that constraint_name is enabled or disabled. When disabled, future inserts or updates to the column are not validated against the constraint conditions. This option can only be used with FOREIGN KEY and CHECK constraints.
ALL
Specifies that all constraints are disabled with the NOCHECK option, or enabled with the CHECK option.

How do your enable or disable a trigger?
ENABLE | DISABLE} TRIGGER
Specifies that trigger_name is enabled or disabled. When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled.
ALL
Specifies that all triggers in the table are enabled or disabled.
trigger_name
Specifies the name of the trigger to disable or enable.

What is ON DELETE CASCADE NO ACTION definition in the alter table statement?

Specifies what action occurs to a row in the table altered, if that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.
If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back.
The CASCADE action ON DELETE cannot be defined if an INSTEAD OF trigger ON DELETE already exists on the table in question.

What is ON UPDATE CASCADE NO ACTION in the alter table definition?
Specifies what action occurs to a row in the table altered, if that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.
If CASCADE is specified, the row is updated in the referencing table if that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.
The CASCADE action ON UPDATE cannot be defined if an INSTEAD OF trigger ON UPDATE already exists on the table in question.

What is WITH VALUES in the alter table definition?

Specifies that the value given in DEFAULT constant_expression is stored in a new column added to existing rows. WITH VALUES can be specified only when DEFAULT is specified in an ADD column clause. If the added column allows null values and WITH VALUES is specified, the default value is stored in the new column added to existing rows. If WITH VALUES is not specified for columns that allow nulls, the value NULL is stored in the new column in existing rows. If the new column does not allow nulls, the default value is stored in new rows regardless of whether WITH VALUES is specified.

Which schema used by the alter table statement?
To add new rows of data, use the INSERT statement. To remove rows of data, use the DELETE or TRUNCATE TABLE statements. To change the values in existing rows, use UPDATE.
The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows.

ALTER TABLE acquires a schema modify lock on the table to ensure no other connections reference even the meta data for the table during the change.
The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects a large number of rows.

Notes on Alter table:
Indexes created as part of a constraint are dropped when the constraint is dropped.
Indexes that were created with CREATE INDEX must be dropped with the DROP INDEX statement.

The DBCC DBREINDEX statement can be used to rebuild an index part of a constraint definition; the constraint does not need to be dropped and added again with ALTER TABLE.
All indexes and constraints based on a column must be removed before the column can be removed.

When constraints are added, all existing data is verified for constraint violations. If any violations occur, the ALTER TABLE statement fails and an error is returned.
When a new PRIMARY KEY or UNIQUE constraint is added to an existing column, the data in the column(s) must be unique.

If duplicate values are found, the ALTER TABLE statement fails. The WITH NOCHECK option has no effect when adding PRIMARY KEY or UNIQUE constraints.
Each PRIMARY KEY and UNIQUE constraint generates an index. The number of UNIQUE and PRIMARY KEY constraints cannot cause the number of indexes on the table to exceed 249 no clustered indexes and 1 clustered index.

If a column is added having a uniqueidentifier data type, it can be defined with a default that uses the NEWID() function to supply the unique identifier values in the new column for each existing row in the table.

SQL Server does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.
The ALTER COLUMN clause of ALTER TABLE does not bind or unbind any rules on a column. Rules must be bound or unbound separately using sp_bindrule or sp_unbindrule.

Rules can be bound to a user-defined data type.
CREATE TABLE then automatically binds the rule to any column defined having the user-defined data type.
ALTER COLUMN does not unbind the rule when changing the column data type. The rule from the original user-defined data type remains bound to the column.

After ALTER COLUMN has changed the data type of the column, any subsequent sp_unbindrule execution that unbinds the rule from the user-defined data type does not unbind it from the column for which data type was changed.

If ALTER COLUMN changes the data type of a column to a user-defined data type bound to a rule, the rule bound to the new data type is not bound to the column.

SQL Server Interview Question:Table

2009-06-27T03:21:00.000-07:00

1.) How many character sql server table can have?

table_name can contain a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

2. What is the distinction between sql server tablename and column name?

The combination of owner.table_name must be unique within the database.

Column names must conform to the rules for identifiers and must be unique in the table.

3. Which occurrences you can omit column name in sql server table?

column_name can be omitted for columns created with a timestamp data type. The name of a timestamp column defaults to timestamp if column_name is not specified.

4. What is computed column in sqlserver?

Is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table

5. What is the restriction when defining computed column expression?

a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

6 Where can we use computed columns?

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used

7. Can we create a foreign key or default, not null constraint on computed column?

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition.

8. Can we create an index on computed columns?

a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.

For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

9. can computed column be the target of insert and update?

A computed column cannot be the target of an INSERT or UPDATE statement.

Note Each row in a table can have different values for columns involved in a computed column; therefore the computed column may not have the same value for each row.

The nullability of computed columns is determined automatically by SQL Server based on the expressions used. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will produce NULL results as well. Use the COLUMNPROPERTY function (AllowsNull property) to investigate the nullability of any computed column in a table. An expression expr that is nullable can be turned into a non-nullable one by specifying ISNULL(check_expression, constant) where the constant is a non-NULL value substituted for any NULL result.

10.) Why do we specify filegroup when creating database?

Where is your table stored in sql server?

Specifies the filegroup on which the table is stored. If filegroup is specified, the table is stored in the named filegroup. The filegroup must exist within the database. If DEFAULT is specified, or if ON is not specified at all, the table is stored on the default filegroup.

ON {filegroup | DEFAULT} can also be specified in a PRIMARY KEY or UNIQUE constraint. These constraints create indexes. If filegroup is specified, the index is stored in the named filegroup. If DEFAULT is specified, the index is stored in the default filegroup. If no filegroup is specified in a constraint, the index is stored on the same filegroup as the table. If the PRIMARY KEY or UNIQUE constraint creates a clustered index, the data pages for the table are stored in the same filegroup as the index.

11.) Can we specify length to a user-defined datatype when creating table?

You cannot specify a length for a user-defined data type in a CREATE TABLE statement.

12. Which are the columns we cannot have default definition?

DEFAULT

Specifies the value provided for the column when a value is not explicitly supplied during an insert. DEFAULT definitions can be applied to any columns except those defined as timestamp, or those with the IDENTITY property

13.) If we drop the table default definition will remain?

DEFAULT definitions are removed when the table is dropped

14.) What are the value we can use for default definition?

Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

constant_expression

Is a constant, NULL, or a system function used as the default value for the column.

What is identity column in sqlserver?

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table.

15) Which are the datatype suitable for identity column?

The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns.

16) How many identity column can we have per table in sqlserver?

Only one identity column can be created per table

17) Can we specify default definition on identity column?

Bound defaults and DEFAULT constraints cannot be used with an identity column

18) What is the seed and increment in identity column?

You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Seed:Is the value used for the very first row loaded into the table.

Increment:Is the incremental value added to the identity value of the previous row loaded

19) What is ROWGUIDCOL?

Indicates that the new column is a row global unique identifier column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column

20) How many unique identifier can we have per table?

Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column.

21) Why do we use NEWID function on unique identifier column?

It also does not automatically generate values for new rows inserted into the table. To generate unique values for each column, either use the NEWID function on INSERT statements or use the NEWID function as the default for the column.

22) Can u guarantee uniqueness of row value on unique identifier column?

The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column

23) What are constraints in sql server?

Is an optional keyword indicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint definition. Constraints are special properties that enforce data integrity and they may create indexes for the table and its columns. Constraint names must be unique within a database.

24) What is NULL | NOT NULL in sql server

Are keywords that determine if null values are allowed in the column. NULL is not strictly a constraint but can be specified in the same manner as NOT NULL.

25) What is PRIMARY KEY in sql server?

Is a constraint that enforces entity integrity for a given column or columns through a unique index. Only one PRIMARY KEY constraint can be created per table.

26) What is UNIQUE constraint?

Is a constraint that provides entity integrity for a given column or columns through a unique index. A table can have multiple UNIQUE constraints.

27) What is FOREIGN KEY constraint?

Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table.

28) What is CHECK constraint?

Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

29) What is CLUSTERED | NONCLUSTERED ?

Are keywords to indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED and UNIQUE constraints default to NONCLUSTERED.

You can specify CLUSTERED for only one constraint in a CREATE TABLE statement. If you specify CLUSTERED for a UNIQUE constraint and also specify a PRIMARY KEY constraint, the PRIMARY KEY defaults to NONCLUSTERED

30) What is fillfactor in sqlserver?

Specifies how full SQL Server should make each index page used to store the index data. User-specified fillfactor values can be from 1 through 100, with a default of 0. A lower fill factor creates the index with more space available for new index entries without having to allocate new space.

31) What is cascade?

If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table

32) How many tables sql server can have?

SQL Server can have as many as two billion tables per database

33) How many columns per table sql server can have?

1,024 columns per table

34) What is size of the table in sqlserver?

The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. If you create tables with varchar, nvarchar, or varbinary columns in which the total defined width exceeds 8,060 bytes, the table is created, but a warning message appears. Trying to insert more than 8,060 bytes into such a row or to update a row so that its total row size exceeds 8,060 produces an error message and the statement fails.

35) Why sqlvariant column generate warning?

CREATE TABLE statements that include a sql_variant column can generate the following warning:

The total row size (xx) for table 'yy' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.

This warning occurs because sql_variant can have a maximum length of 8016 bytes. When a sql_variant column contains values close to the maximum length, it can overshoot the row's maximum size limit.

36) How many indexes can we have per table?

Each table can contain a maximum of 249 nonclustered indexes and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.

37) What is the order when default, identity, rowguidcol are specified?
SQL Server does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.

What is Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

When you create local or global temporary tables, the CREATE TABLE syntax supports constraint definitions with the exception of FOREIGN KEY constraints.

If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message indicating that the constraint was skipped, and the table is still created without the FOREIGN KEY constraints.

Temporary tables cannot be referenced in FOREIGN KEY constraints.
Consider using table variables instead of temporary tables.
Temporary tables are useful in cases when indexes need to be created explicitly on them, or when the table values need to be visible across multiple stored procedures or functions. In general,
table variables contribute to more efficient query processing

Notes PRIMARY KEY Constraints Features
A table can contain only one PRIMARY KEY constraint.

The index generated by a PRIMARY KEY constraint cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.

If CLUSTERED or NONCLUSTERED is not specified for a PRIMARY KEY constraint, CLUSTERED is used if there are no clustered indexes specified for UNIQUE constraints.

All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

Notes UNIQUE Constraints Features
If CLUSTERED or NONCLUSTERED is not specified for a UNIQUE constraint, NONCLUSTERED is used by default.
Each UNIQUE constraint generates an index. The number of UNIQUE constraints cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.

Notes FOREIGN KEY Constraints Features
When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.

FOREIGN KEY constraints are applied to the preceding column unless source columns are specified.

FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers.

FOREIGN KEY constraints can reference another column in the same table (a self-reference).
The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.

The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

CASCADE may not be specified if a column of type timestamp is part of either the foreign key or the referenced key.

It is possible to combine CASCADE and NO ACTION on tables that have referential relationships with each other. If SQL Server encounters NO ACTION, it terminates and rolls back related CASCADE actions. When a DELETE statement causes a combination of CASCADE and NO ACTION actions, all the CASCADE actions are applied before SQL Server checks for any NO ACTION.

A table can contain a maximum of 253 FOREIGN KEY constraints.
FOREIGN KEY constraints are not enforced on temporary tables.

A table can reference a maximum of 253 different tables in its FOREIGN KEY constraints.

FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.

Notes DEFAULT Definitions Features
A column can have only one DEFAULT definition.

A DEFAULT definition can contain constant values, functions, SQL-92 niladic functions, or NULL. The table shows the niladic functions and the values they return for the default during an INSERT statement.

constant_expression in a DEFAULT definition cannot refer to another column in the table, or to other tables, views, or stored procedures.

DEFAULT definitions cannot be created on columns with a timestamp data type or columns with an IDENTITY property.

DEFAULT definitions cannot be created for columns with user-defined data types if the user-defined data type is bound to a default object.

Notes CHECK Constraints Features
A column can have any number of CHECK constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple CHECK constraints for a column are validated in the order created.

The search condition must evaluate to a Boolean expression and cannot reference another table.

A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.

CHECK CONSTRAINTS and rules serve the same function of validating the data during INSERT and DELETE statements.
When a rule and one or more CHECK constraints exist for a column or columns, all restrictions are evaluated.

Notes on Additional Constraint Information
An index created for a constraint cannot be dropped with the DROP INDEX statement; the constraint must be dropped with the ALTER TABLE statement. An index created for and used by a constraint can be rebuilt with the DBCC DBREINDEX statement.

Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint. The constraint name appears in any error message about constraint violations.

When a constraint is violated in an INSERT, UPDATE, or DELETE statement, the statement is terminated. However, the transaction (if the statement is part of an explicit transaction) continues to be processed. You can use the ROLLBACK TRANSACTION statement with the transaction definition by checking the @@ERROR system function.

If a table has FOREIGN KEY or CHECK CONSTRAINTS and triggers, the constraint conditions are evaluated before the trigger is executed.

38) To Report a table which command do you use?
For a report on a table and its columns, use sp_help or sp_helpconstraint.

39) To rename a table which command do you use?
To rename a table, use sp_rename.

40) What is the use of sp_depends
For a report on the views and stored procedures that depend on a table, use sp_depends.

41) What is the use of sp_spaceused?
Space is generally allocated to tables and indexes in increments of one extent at a time. When the table or index is created, it is allocated pages from mixed extents until it has enough pages to fill a uniform extent. After it has enough pages to fill a uniform extent, another extent is allocated each time the currently allocated extents become full. For a report about the amount of space allocated and used by a table, execute sp_spaceused.

How do create table in sql server

2009-06-20T04:22:00.000-07:00

Create table emp
(
empid int not null,
empname char(20) not null,
city char(20) not null
)

--Insert values

insert into emp(empid,empname,city)values(1,thangadurai,chennai)