Tag Archives: SQL Server 2005 Interview Questions and Answers

SQL Server 2005 Questions and Answers

1.    What does integration of .NET Framework mean for SQL Server 2005?

This feature enables us to execute C# or VB.NET code in the DBMS to take advantage of the .NET functionality. This feature gives more flexibility in writing complex stored procedures, functions, and triggers that can be written in .net compatible language.

2.    What is SSIS?

According to Microsoft SQL Server Integration Services, “(SSIS) is an effective set of tools for both the traditional demands of ETL operations, as well as for the evolving needs of general purpose data integration.” In short, it is the next version of DTS (Data Transformation Services). ETL stands for Extract, Transform and Loading. In short it is a data migration tool that is flexible, fast, and has scalable architecture that enables effective data integration in current business environments.

3.    What is MARS?

In previous versions of SQL Server, applications had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduces a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. Multiple Active Result Sets (MARS) is the ability to have more than one pending request under a given SQL Server connection. MARS is a programming model enhancement that allows multiple requests to interleave in the server. We need to note that it is not a parallel execution in the server. However, it may benefit us with some performance benefits if used correctly. By default, this feature is not set in SQL Server 2005.

4.    What are the Security Enhancements in SQL Server 2005?

SQL Server 2005 enables administrators to manage permissions at a granular level.

-> In the new SQL Server 2005, we can specify a context under which statements in a module can execute.

-> SQL Server 2005 clustering supports Kerberos authentication against a SQL Server 2005 virtual server.

-> Administrators can specify Microsoft Windows-style policies on standard logins so that a consistent policy is applied across all accounts in the domain.

-> SQL Server 2005 supports encryption capabilities within the database itself, fully integrated with a key management infrastructure. By default, client-server communications are encrypted.

5.    What is new with the Reporting services in SQL server 2005?

SQL Server 2005 Reporting Services is a key component of SQL Server 2005 that provides customers with an enterprise-capable reporting platform. This comprehensive environment is used for authoring, managing, and delivering reports to the entire organization. SQL Server 2005 reporting services have some major changes when compared with the previous version.

-> Changes to the core functionality of the Reporting services in the design of the report, processing, and interactivity

-> Better Integration with other components – Enhanced integration with other components within SQL Server 2005 like SSIS, SSAS and SQL Server Management studio

-> Report Builder – A new reporting tool that enables business users to create their own reports

6.    What is OLAP?

Online Analytical Processing (OLAP) allows us to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. The arrangement of data into cubes avoids a limitation of relational databases which are not well suited for near instantaneous analysis of large amounts of data. OLAP cubes can be thought of as extensions to the two-dimensional array of a spreadsheet.

7.    What is Data Mining?

According to MSDN Data, mining is “the process of extracting valid, authentic, and actionable information from large databases.” Microsoft data mining tools are different from traditional data mining applications in significant ways. Data Mining is a platform for developing intelligent applications, not a stand-alone application. You can build custom applications that are intelligent because the data mining models are easily accessible to the outside world. Further, the model is extensible so that third parties can add custom algorithms to support particular mining needs.

8.    What is new with the Analysis Services (SSAS) in SQL Server 2005?

SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality through a combination of server and client technologies, further reinforced through the use of a specialized development and management environment coupled with a well-defined object model for designing, creating, deploying, and maintaining business intelligence applications. The server component of Analysis Services is implemented as a Microsoft Windows service. Clients communicate with Analysis Services using the public standard XML for Analysis (XMLA), a SOAP-based protocol. Let us see the enhancements of made to SSAS.

·         Supports up to 16 instances of Analysis Services Service.

·         As discussed above, the Analysis Services service fully implements the XML for Analysis (XMLA) 1.1 specification. All communication with an instance of Analysis Services is handled through XMLA commands in SOAP messages.

·         Uses the Proactive caching.

9.    What is Information Schema in SQL Sever 2005?

Information Schema is the part of the SQL- 92 standard which exposes the metadata of the database. In SQL server, a set of views are created in each of the databases which exposes the metadata of the database. The information schema is kept in a separate schema – information schema – which exists in all databases, but which is not included in the search path by default. For more information regarding Information schema please read this article.

10. What is Full Text Search? How does it get implemented in SQL server 2005?

Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate which only works on character patterns, full-text queries perform linguistic searches against this data, by operating on words and phrases based on rules of a particular language.

11. What is integration of Microsoft Office System mean?

The integration with Microsoft Office system means the following.

· Table Analysis Tools for Excel: Provides an easy-to-use add-in that leverages SQL Server 2005 Data Mining behind the scenes to perform powerful end user analysis on spreadsheet data.

· Data Mining Client for Excel: Offers a full data mining model development lifecycle directly within Excel 2007.

· Data Mining Templates for Visio: Enables powerful rendering and sharing of mining models as annotatable Visio 2007 drawings.

12. What is the support of Web Services in SQL Server 2005?

With this feature the database engine can be directly exposed as a web service without a middle tier or even an IIS. This will enable the user to directly call a stored procedure by calling a web method. This feature is designed with well-known standards such as SOAP 1.2, WSDL 1.1, and HTTP. With this new feature we can now connect to SQL Server not only with TDS- Tabular data stream (a binary protocol for connecting to SQL Server 2005) but also over SOAP/ HTTP.

13. What is OLTP?

Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.

14. What is Snapshot in SQL Server 2005?

A database snapshot is a read-only, static view of a database, the source database. Each database snapshot is transaction-consistent with the source database as it existed at the time of the snapshot’s creation.

15. What is snapshot isolation in SQL Server 2005?

SQL Server 2005 introduces a new “snapshot” isolation level that is intended to enhance concurrency for online transaction processing (OLTP) applications. In prior versions of SQL Server, concurrency was based solely on locking, which can cause blocking and deadlocking problems for some applications. Snapshot isolation depends on enhancements to row versioning and is intended to improve performance by avoiding reader-writer blocking scenarios.

16. What is Database Partitioning in SQL Server 2005?

SQL Server 2005 provides a new capability for the partitioning of tables across file groups in a database. Partitioning a database improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries accessing only a fraction of the data can run faster because there is less data to scan.

17. What is SQL Server Agent?

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. We generally schedule the backups on the production databases using the SQL server agent. In SQL Server 2005 we have roles created for using SQL Server agents.

·         SQLAgentUserRole

·         SQLAgentReaderRole

·         SQLAgentOperatorRole

SQL Server Agent for SQL Server 2005 provides a more robust security design than earlier versions of SQL Server. This improved design gives system administrators the flexibility they need to manage their Agent service.

18. What is Replication? What is the need to have the replication? What are the enhancements made to SQL Server 2005 related to the replication?

“Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.” In short, replication is all about having multiple copies of the same database. We need replication when we need to distribute data to and from different locations. Generally we have a master copy of data. There will be multiple slaves (Clients) located at various locations which need to be replicated. We use replication for a variety of reasons. Load balancing is sharing the data among a number of servers and distributing the query load. Offline processing is one of the main reasons. In this scenario we need to modify the data on the database that is not connected to the network. The last reason may be to have a back-up to the database in case of failure to the existing database. Let us see the enhancements of SQL server 2005 database related to replication.

· Database Mirroring – Database Mirroring is moving the transactions of database from one SQL Server database to another SQL server database on a different SQL Server.

· Replication Management topology (RMO) – RMO is a new construct in SQL Server 2005. It is a .NET Framework library that provides a set of common language runtime classes for configuring, managing, and scripting replication, and for synchronizing Subscribers.

19. What are Business Logic Handlers?

Business logic handlers are written in managed code and allow us to execute custom business logic during the merge synchronization. We can invoke the business logic handler in case of non-conflicting data changes. Business logic handler can perform one of the following three actions.

· Reject Data

· Accept Data

· Apply Custom Data

20. What are different variants of SQL Server 2005?

There are different variants of SQL Server 2005 commercially available.

· Express – Free and only for one user

· Enterprise – 5 users apart from server

· Workgroup – 10 users apart from server

· Standard – 25 users apart from server

21. What are Various Service packs available for SQL Server 2005?

As of now there are two service packs available for the SQL Server 2005.

· Service Pack 1 – Has major changes or enhancements to SQL Server 2005 in Analysis Services, Data Programmability, SSIS, and reporting services.

· Service Pack 2 – Unlike Service Pack 2, this service pack enables SQL Server 2005 customers to take advantage of the enhancements within Windows Vista and the 2007 Office system.

22. What are the New Data types introduced in SQL Server 2005?

SQL Server 2005 has added some new data types to its existing data types.

. XML Data type

· VARCHAR (MAX)

· NVARCHAR (MAX)

· VARBINARY (MAX)

As we can see, the new term MAX has been introduced in SQL Server 2005. This new specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. Varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types.

23. Does SQL Server 2005 support SMTP?

SQL Server 2005 now supports sending E-mail from the database. It is called as database mail and it uses DatabaseMail90.exe. Gone are the days when we were using a third party component for this. Receiving an e-mail was not supported in the previous versions of SQL Server.

24. What is SQL Management Object is SQL Server 2005?

These are collection of objects that are made for programming all aspects of managing Microsoft SQL Server 2005. SMO is a .NET based object model. It comes with SQL Server 2005 as a .Net assembly named Microsoft.SqlServer.Smo.dll. We can use these objects for connecting to a database, calling methods of the database that returns a table, using transactions, transferring data, scheduling administrative tasks, etc. The best part about SMO is that most of it can also be used with SQL server 2000.

25. What is SQL Service Broker in SQL Server 2005?

SQL Service broker is a new technology introduced in SQL Server 2005 for building database-intensive distributed applications. Basically, service broker has been built for developing applications that consist of individual components which are loosely coupled. Service broker supports asynchronous yet reliable messages that are passed between the components. These messages are called conversations.

 

Advertisements

SQL Server 2005 Interview Questions

Download SQL Server Interview Question

• If I want to see what fields a table is made of, and what the sizes of the
fields are, what option do I have to look for?
Sp_Columns ‘TableName’

• What is a query?
A request for information from a database. There are three general methods for posing queries:
# Choosing parameters from a menu: In this method, the database system presents a list of parameters from which you can choose. This is perhaps the easiest way to pose a query because the menus guide you, but it is also the least flexible.
# Query by example (QBE): In this method, the system presents a blank record and lets you specify the fields and values that define the query.
# Query language: Many database systems require you to make requests for information in the form of a stylized query that must be written in a special query language. This is the most complex method because it forces you to learn a specialized language, but it is also the most powerful.

• What is the purpose of the model database?
It works as Template Database for the Create Database Syntax

• What is the purpose of the master database?
Master database keeps the information about sql server configuration, databases users etc

• What is the purpose of the tempdb database?
Tempdb database keeps the information about the temporary objects (#TableName, #Procedure). Also the sorting, DBCC operations are performed in the TempDB

• What is the purpose of the USE command?
Use command is used for to select the database. For i.e Use Database Name

• If you delete a table in the database, will the data in the table be deleted too?
Yes

• What is the Parse Query button used for? How does this help you?
Parse query button is used to check the SQL Query Syntax

• Tables are created in a ____________________ in SQL Server 2005.
resouce database(System Tables)

• What is usually the first word in a SQL query?
SELECT

• Does a SQL Server 2005 SELECT statement require a FROM?
NO

• Can a SELECT statement in SQL Server 2005 be used to make an assignment? Explain with examples.
Yes. Select @MyDate = GetDate()

• What is the ORDER BY used for?
Order By clause is used for sorting records in Ascending or Descending order

• Does ORDER BY actually change the order of the data in the tables or does it just
change the output?

Order By clause change only the output of the data

• What is the default order of an ORDER BY clause?
Ascending Order

• What kind of comparison operators can be used in a WHERE clause?

Operator Meaning
= (Equals) Equal to
> (Greater Than) Greater than
< (Less Than) Less than
>= (Greater Than or Equal To) Greater than or equal to
<= (Less Than or Equal To) Less than or equal to
<> (Not Equal To) Not equal to
!= (Not Equal To) Not equal to (not SQL-92 standard)
!< (Not Less Than) Not less than (not SQL-92 standard)
!> (Not Greater Than) Not greater than (not SQL-92 standard)

• What are four major operators that can be used to combine conditions on a WHERE
clause?

OR, AND, IN and BETWEEN

• What are the logical operators?

Operator Meaning
ALL TRUE if all of a set of comparisons are TRUE.
AND TRUE if both Boolean expressions are TRUE.
ANY TRUE if any one of a set of comparisons are TRUE.
BETWEEN TRUE if the operand is within a range.
EXISTS TRUE if a subquery contains any rows.
IN TRUE if the operand is equal to one of a list of expressions.
LIKE TRUE if the operand matches a pattern.
NOT Reverses the value of any other Boolean operator.
OR TRUE if either Boolean expression is TRUE.
SOME TRUE if some of a set of comparisons are TRUE.

•In a WHERE clause, do you need to enclose a text column in quotes? Do you need to enclose a numeric column in quotes?
Enclose Text in Quotes (Yes)
Enclose Number in Quotes (NO)

• Is a null value equal to anything? Can a space in a column be considered a null value? Why or why not?
No NULL value means nothing. We can’t consider space as NULL value.

• Will COUNT(column) include columns with null values in its count?
Yes, it will include the null column in count

• What are column aliases? Why would you want to use column aliases? How can you embed blanks in column aliases?
You can create aliases for column names to make it easier to work with column names, calculations, and summary values. For example, you can create a column alias to:
* Create a column name, such as “Total Amount,” for an expression such as (quantity * unit_price) or for an aggregate function.
* Create a shortened form of a column name, such as “d_id” for “discounts.stor_id.”
After you have defined a column alias, you can use the alias in a Select query to specify query output

• What are table aliases?
Aliases can make it easier to work with table names. Using aliases is helpful when:
* You want to make the statement in the SQL Pane shorter and easier to read.
* You refer to the table name often in your query — such as in qualifying column names — and want to be sure you stay within a specific character-length limit for your query. (Some databases impose a maximum

length for queries.)
* You are working with multiple instances of the same table (such as in a self-join) and need a way to refer to one instance or the other.

• What are table qualifiers? When should table qualifiers be used?
[@table_qualifier =] qualifier
Is the name of the table or view qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table’s database environment.

• Are semicolons required at the end of SQL statements in SQL Server 2005?
No it is not required

• Do comments need to go in a special place in SQL Server 2005?

No its not necessary

• When would you use the ROWCOUNT function versus using the WHERE clause?

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:
* Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
* Preserve @@ROWCOUNT from the previous statement execution.
* Reset @@ROWCOUNT to 0 but do not return the value to the client.
Statements that make a simple assignment always set the @@ROWCOUNT value to 1.

• Is SQL case-sensitive? Is SQL Server 2005 case-sensitive?

No both are not case-sensitive. Case sensitivity depends on the collation you choose.
If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable
(
mycolumn VARCHAR(10)
)
GO

SET NOCOUNT ON

INSERT mytable VALUES(‘Case’)
GO

SELECT mycolumn FROM mytable WHERE mycolumn=’Case’
SELECT mycolumn FROM mytable WHERE mycolumn=’caSE’
SELECT mycolumn FROM mytable WHERE mycolumn=’case’

You can alter your query by forcing collation at the column level:

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = ‘caSE’

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = ‘case’

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = ‘Case’

— if myColumn has an index, you will likely benefit by adding
— AND myColumn = ‘case’

• What is a synonym? Why would you want to create a synonym?

SYNONYM is a single-part name that can replace a two, three or four-part name in many SQL statements. Using SYNONYMS in RDBMS cuts down on typing.
SYNONYMs can be created for the following objects:

* Table
* View
* Assembly (CLR) Stored Procedure
* Assembly (CLR) Table-valued Function
* Assembly (CLR) Scalar Function
* Assembly Aggregate (CLR) Aggregate Functions
* Replication-filter-procedure
* Extended Stored Procedure
* SQL Scalar Function
* SQL Table-valued Function
* SQL Inline-table-valued Function
* SQL Stored Procedure

Syntax
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >

< object > :: =
{
[ server_name.[ database_name ] . [ schema_name_2 ].| database_name . [ schema_name_2 ].| schema_name_2. ] object_name
}

• Can a synonym name of a table be used instead of a table name in a SELECT statement?
Yes

• Can a synonym of a table be used when you are trying to alter the definition of a table?
Not Sure will try

• Can you type more than one query in the query editor screen at the same time?
Yes we can.

• While you are inserting values into a table with the INSERT INTO .. VALUES option, does the order of the columns in the INSERT statement have to be the same as the order of the columns in the table?
Not Necessary

• While you are inserting values into a table with the INSERT INTO .. SELECT option, does the order of the columns in the INSERT statement have to be the same as the order of the columns in the table?
Yes if you are not specifying the column names in the insert clause, you need to maintain the column order in SELECT statement

• When would you use an INSERT INTO .. SELECT option versus an INSERT INTO .. VALUES option? Give an example of each.
INSERT INTO .. SELECT is used insert data in to table from diffrent tables or condition based insert
INSERT INTO .. VALUES you have to specify the insert values

• What does the UPDATE command do?
Update command will modify the existing record

• Can you change the data type of a column in a table after the table has been created? If so,which command would you use?

Yes we can. Alter Table Modify Column

• Will SQL Server 2005 allow you to reduce the size of a column?
Yes it allows

• What integer data types are available in SQL Server 2005?

Exact-number data types that use integer data.

Data type Range Storage
bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes
int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes
smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes
tinyint 0 to 255 1 Byte


• What is the default value of an integer data type in SQL Server 2005?

NULL

• What is the difference between a CHAR and a VARCHAR datatype?

CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).

You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.

• Does Server SQL treat CHAR as a variable-length or fixed-length column?
SQL Server treats CHAR as fixed length column

• If you are going to have too many nulls in a column, what would be the best data type to use?
Variable length columns only use a very small amount of space to store a NULL so VARCHAR datatype is the good option for null values

• When columns are added to existing tables, what do they initially contain?

The column initially contains the NULL values

• What command would you use to add a column to a table in SQL Server?

ALTER TABLE tablename ADD column_name DATATYPE

• Does an index slow down updates on indexed columns?
Yes

• What is a constraint?

Constraints in Microsoft SQL Server 2000/2005 allow us to define the ways in which we can automatically enforce the integrity of a database. Constraints define rules regarding permissible values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, stored procedures, rules, and defaults, as a method of implementing data integrity rules. The query optimizer also uses constraint definitions to build high-performance query execution plans.

• How many indexes does SQL Server 2005 allow you to have on a table?
250 indices per table

• What command would you use to create an index?
CREAT INDEX INDEXNAME ON TABLE(COLUMN NAME)

• What is the default ordering that will be created by an index (ascending or descending)?
Clustered indexes can be created in SQL Server databases. In such cases the logical order of the index key values will be the same as the physical order of rows in the table.
By default it is ascending order, we can also specify the index order while index creation.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,…n ] )

• How do you delete an index?
DROP INDEX authors.au_id_ind

• What does the NOT NULL constraint do?
Constrain will not allow NULL values in the column

• What command must you use to include the NOT NULL constraint after a table has already been created?
DEFAULT, WITH CHECK or WITH NOCHECK

• When a PRIMARY KEY constraint is included in a table, what other constraints does this imply?
Unique + NOT NULL

• What is a concatenated primary key?

Each table has one and only one primary key, which can consist of one or many columns. A concatenated primary key comprises two or more columns. In a single table, you might find several columns, or groups of columns, that might serve as a primary key and are called candidate keys. A table can have more than one candidate key, but only one candidate key can become the primary key for that table

• How are the UNIQUE and PRIMARY KEY constraints different?
A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

• What is a referential integrity constraint? What two keys does the referential integrity constraint usually include?

Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key (alternate key) and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table’s primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. The relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used would be determined by the referential integrity constraint, as defined in the data dictionary.

• What is a foreign key?

FOREIGN KEY constraints identify the relationships between tables.
A foreign key in one table points to a candidate key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no candidate keys with that value. In the following sample, the order_part table establishes a foreign key referencing the part_sample table defined earlier. Usually, order_part would also have a foreign key against an order table, but this is a simple example.

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO

You cannot insert a row with a foreign key value (except NULL) if there is no candidate key with that value. The ON DELETE clause controls what actions are taken if you attempt to delete a row to which existing foreign keys point. The ON DELETE clause has two options:

NO ACTION specifies that the deletion fails with an error.

CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also deleted.
The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing foreign keys point. It also supports the NO ACTION and CASCADE options.


• What does the ON DELETE CASCADE option do?

ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.

ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.


• What does the ON UPDATE NO ACTION do?

ON DELETE NO ACTION
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE is rolled back.

ON UPDATE NO ACTION
Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE is rolled back.

• Can you use the ON DELETE and ON UPDATE in the same constraint?
Yes we can.
CREATE TABLE part_sample
(part_nmbr int PRIMARY KEY,
part_name char(30),
part_weight decimal(6,2),
part_color char(15) )

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION ON UPDATE NO ACTION,
qty_ordered int)
GO

Download SQL Server Interview Question