Metacube Interview – Judgment for gems

As Metacube is a great company, reason is they have diamonds in their work force and hire diamond in their company. Their interview process is very tough as I heard and faced too. People who have good programming skills and strong background can win. As I experienced in each round of interview process they checks logical, learning ability and programming skills of a candidate by data structures and other small programs totally brain grinder.    

If you are looking forward to join such a good company Metacube, this Book will help you a lot not only for Metacube but also other big companies too. Here is the Download Link.

Download

Register User Controls and Custom Controls in Web.config

One mistake most of developers (including me before writing this article) do. When we use user controls or custom controls, we use them by registering on the page where we are using that particular control (<%@ Register %> directives to the top of pages).

Instead of duplicating code all where we can do same with much easier and cleaner approach, by declaring them in web.config file.

<configuration>

  <system.web>
    <pages>
      <controls>
         <add tagPrefix="ucl" src="~/MyControls/uclMyControl.ascx" tagName="MyControl"/>
      </controls>
    </pages>
  </system.web>
</configuration>

Once you register the controls within the web.config file, you can then just use the controls on any page,

<ucl:MyControl id="mcLogin" runat="server"> </ucl:MyControl>

MERGE (Transact-SQL)

Question Suppose Table1 has following records:

id            emp        cellno
---------------------------------------------
1              abc         9999999995
2               xyz          9999999996

CREATE TABLE [dbo].[Table1](
      [id] [int] NULL,
      [employee] [nvarchar](50) NULL,
      [cellno] [nchar](10) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Table1] ([id], [employee], [cellno]) VALUES (1, N'abc', N'9868350985')

INSERT [dbo].[Table1] ([id], [employee], [cellno]) VALUES (1, N'xyz', N'9868350986')

And Table2 has following records:

id            emp        cellno
--------------------------------------------
1              abc         9319999995
3              tst            9868350987

CREATE TABLE [dbo].[Table2](
      [id] [int] NULL,
      [employee] [nvarchar](50) NULL,
      [cellno] [nchar](10) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Table2] ([id], [employee], [cellno]) VALUES (1, N'abc', N'9313164525')

INSERT [dbo].[Table2] ([id], [employee], [cellno]) VALUES (3, N'tst', N'9868350987')

Table2 contains the updated records for the employees.
Write the query that updates cellno for the employee in table1 if the record is already there else inserts the record in table1.i.e.
Running the query, records in table1 would be:

id            emp        cellno
---------------------------------------------
1              abc         9399999995
2              xyz           9999999996
3              tst            9868350987

Also I do not want to write 2 queries to achieve the desired output. There should be a single query.

Answer
MERGE Table1 AS Target
USING Table2 AS Source
ON (Target.id = Source.id)
--When records are matched, update the records if there is any change
WHEN MATCHED AND (Target.employee <> Source.employee OR Target.cellno <> Source.cellno) THEN
    UPDATE SET Target.employee = Source.employee, Target.cellno = Source.cellno
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, employee, cellno)
    VALUES (Source.id, Source.employee, Source.cellno)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action, Inserted.*, Deleted.*;

For more details about merge visit
http://technet.microsoft.com/en-us/library/bb510625.aspx

cursor

Some applications, such as interactive and online applications, cannot work effectively with the entire set as a unit. These applications need a mechanism to work with one row, or a small block of rows at a time.
For example, consider that you want to see a list of soaps available in a shop. In technical terms, the user wants selected records from the table. Once these records are available, you also would like to see all the records one by one.
A cursor is a database object that applications use to manipulate data by rows instead of recordsets. You can use cursors to perform multiple operations in a row-by-row manner, against the resultset. You can do this with or without returning to the original table. In other words, cursors conceptually return a resultset based on tables within a database.

You can use the cursors to do the following:
• Allow positioning at specific rows of the resultset.
• Retrieve a single row, or set of rows, from the current positions in the resultset.
• Support data modifications to the rows at the current position in the resultset.
• Support different levels of visibility to changes that others users make to the database data that is presented in the resultset.
• Provide T-SQL statements in scripts, stored procedures, and triggers, to access the data in the resultset.

Creating a Cursor
You can use the following two methods to create a cursor in SQL Server:

1. The T-SQL language, which supports the syntax for using cursors modeled after the Sql-92 cursor syntax.
2. Database application programming interface.

In this article, we will focus on T-SQl cursors. The syntax of T-SQL cursors and API cursors is different, but they follow a common sequence of steps.

Follow these steps to create a cursor:
1. Associate a cursor with a resultSet of a T-SQL statement, and define the characteristics of the cursor, such as how the rows are going to be retrieved, and so forth.
2. Execute the T-SQL statement to populate the cursor.
3. Retrieve the rows in the cursor. The operation to retrieve one row or a set of rows is called fetch. Scrolling is a series of fetch operations to retrieve the rows in a backward or forward direction.
4. You can also perform the modifications on a row at the cursor position.
5. Close the cursor.

Now We Shall See the Syntax
1. Use the DECLARE statement to create the cursor. It contains a SQL statement to include the records from the table.
2. DECLARE <Cursor_Name> CURSOR
3. FOR
4. <Select Statement>
5. After creating the cursor, you will open it. Use the OPEN statement to make the cursor accessible.
OPEN <Cursor_name>
6. Use the FETCH statement to obtain the records from the cursor for further processing.
FETCH <Cursor_name>
7. Use the CLOSE statement to temporarily close the cursor when it is not required. This statement releases the current resultset to close an open cursor. You have to re-open the cursor to fetch the rows.
CLOSE <Cursor_Name>
8. When you no longer require the cursor, you can use the DEALLOCATE statement to remove its reference.
DEALLOCATE <Cursor_Name>

Fetching and Scrolling Through a Cursor
When you open a cursor, the current row pointer in the cursor is logically before the first row. T-SQL cursors can fetch one row at a time. The options for FETCH operations are as follows:

Option                        Description

FETCH FIRST                   Fetches the first row in the cursor
FETCH NEXT                   Fetches the row after the previously fetched row
FETCH PRIOR                  Fetches the row before the previously fetched row
FETCH LAST                    Fetches the last row in the cursor
FETCH ABSOLUTE          n If n is a positive integer, it fetches the nth row in a cursor. If n is a negative integer, it fetches the nth row before the last row. If n is 0, no row is fetched.
FETCH RELATIVE            n If n is positive, it fetches the nth row from the previously fetched row. If n is negative, it fetches the nth row before the previously fetched row. If n is 0, the same row is fetched again.

By default, the FETCH NEXT option works. To use other options, you must include certain options in the DECLARE statement while creating the cursors.

DECLARE Statement Options
You can add the following attributes to the cursor to enhance its scrollability of a cursor. The attributes also are explained in the following list.

DECLARE <Cursor_Name> CURSOR
[LOCAL | GLOBAL]
[FORWARD ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR <Selecting Statements>
[FOR UPDATE [OF Column_name[,....N]]]

• LOCAL: Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is valid only within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates.
The cursor is implicitly de allocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is de allocated when the last variable referencing it is deallocated or goes out of scope.
• GLOBAL: Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.
• FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor.

When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified, the other cannot be specified.

• STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

• KEYSET: Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor).

If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

• DYNAMIC: Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.

• FAST_FORWARD: Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified, the other cannot be specified.

• READ_ONLY: Prevents updates from being made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

• SCROLL_LOCKS: Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.

• OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

• TYPE_WARNING: Specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another.

select_statement is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within a select_statement of a cursor declaration. SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type. For more information, see Implicit Cursor Conversions.

UPDATE [OF column_name [,...n]] defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

Example 1

Declare Flight_cursor CURSOR SCROLL
FOR SELECT * from flight Order By Aircraft_code
Open Flight_Cursor
Fetch First From Flight_Cursor
While @@Fetch_Status =0
BEGIN
Fetch NExt from Flight_Cursor
End

Explanation of Example 1
This simply creates a cursor that selects all records from a flight table, then it opens the cursor. Then, it fetches the first record from the cursor and it moves the cursor ahead until the last executed cursor FETCH statement returns 0.

@@FETCH_STATUS returns an integer for the last executed cursor FETCH statement. @@CURSOR_ROWS returns the number of qualifying rows that are present in the currently open cursor.

Example 2

Declare @@counter int
set @@counter=0
Declare @@ProductID int
Declare @@ProductName varchar(30)
Declare @@Qty int

Declare special cursor
local Scroll Keyset Optimistic
For
Select * from Products
Open special /* Opening the cursor */
fetch Absolute 1 from special
into @@ProductID,@@ProductName,@@Qty
while @@fetch_Status<>-1
begin
fetch next from special
into @@ProductID,@@ProductName,@@Qty
Print @@ProductiD
Print @@ProductName
print @@Qty
/*set @@ProductName=(Select Prod_Name from special)*/

Update Products set Prod_Name= @@counter

set @@counter=@@counter+1
end
close special
Deallocate special

Temporary Tables

Temporary tables are useful workspaces, like scratch pads, that you can use to try out intermediate data processing or to share work-in-progress with other connections. You can create temporary tables from within any database, but they exist in only the tempdb database, which is created every time the server is restarted. Don't assume that temporary tables aren't logged: temporary tables, and actions on those tables, are logged in tempdb so that transactions can be rolled back as necessary. However, the log isn't used for recovery of the database at system restart because the database is entirely re-created. Likewise, tempdb is never restored from a backup, so the log in tempdb is never needed for restoring the database. You can use temporary tables in three ways in SQL Server: privately, globally, and directly.

Private Temporary Tables (#)
By prefixing a table name with a single pound sign (#)—as in CREATE TABLE #my_table—you can create it (from within any database) as a private temporary table. Only the connection that created the table can access the table, making it truly private. Privileges can't be granted to another connection. As a temporary table, it exists for the life of that connection only; that connection can drop the table using DROP TABLE.

Because the scoping of a private temporary table is specific to the connection that created it, you won't encounter a name collision should you choose a table name that's used in another connection. Private temporary tables are analogous to local variables—each connection has its own private version, and private temporary tables that are held by other connections are irrelevant. (However, temporary tables do differ from local variables in one crucial way: temporary tables exist for the life of the session, while local variables exist only for a single batch.)

Global Temporary Tables (##)

By prefixing a table name with double pound signs (##)—as in CREATE TABLE ##our_table—you can create a global temporary table (from within any database and any connection). Any connection can subsequently access the table for retrieval or data modification, even without specific permission. Unlike with private temporary tables, all connections can use the single copy of a global temporary table. Therefore, you might encounter a name collision if another connection has created a global temporary table of the same name, and the CREATE TABLE statement will fail.

A global temporary table exists until the creating connection terminates and all current use of the table completes. After the creating connection terminates, however, only those connections already accessing it are allowed to finish, and no further use of the table is allowed. If you want a global temporary table to exist permanently, you can create the table in a stored procedure that's marked to autostart whenever SQL Server is started. That procedure can be put to sleep using WAITFOR and it will never terminate, so the table will never be dropped. Or you can choose to use tempdb directly, which I'll discuss next.

Direct Use of tempdb

Realizing that tempdb is re-created every time SQL Server is started, you can use tempdb to create a table or you can fully qualify the table name to include the database name tempdb in the CREATE TABLE statement issued from another database. To do this, you need to establish create table privileges in tempdb.

You can set up privileges in tempdb in one of two ways every time SQL Server starts: you can set the privileges in model (the template database) so that they are copied to tempdb when it's created at system restart, or you can have an autostart procedure set the tempdb privileges every time SQL Server is started. One reason to consider not setting the privileges for tempdb in the model database is because tempdb isn't the only database that will be affected. Any new database you create will inherit those permissions as well.

Tables created directly in tempdb can exist even after the creating connection is terminated, and the creator can specifically grant and revoke access permissions to specific users:

— Creating a table in tempdb from pubs. Another method would be

— to first do a 'use tempdb' instead of fully qualifying
— the name.

CREATE TABLE tempdb.dbo.testtemp

(col1 int)

Views and Derived Tables

Think of a view as a virtual table. Simply put, a view is a named SELECT statement that dynamically produces a result set that you can further operate on. A view doesn't actually store any data. It acts as a filter for underlying tables in which the data is stored. The SELECT statement that defines the view can be from one or more underlying tables or from other views. To relieve users of the complexity of having to know how to write an outer join properly, we can turn the previous outer-join query into a view:

CREATE VIEW outer_view AS
(
SELECT   'Author'=RTRIM(au_lname) + ', ' + au_fname, 'Title'=title
FROM (titleauthor AS TA
FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id))
RIGHT OUTER JOIN authors AS A ON (A.au_id=TA.au_id)
WHERE
A.state <> 'CA' OR A.state IS NULL
)

Now, instead of formulating the outer join, we can simply query the outer-join view, outer_view. Then we can do a search for author names starting with Ri.

SELECT * FROM outer_view WHERE Author LIKE 'Ri%' ORDER BY Author

A derived table is a fancy name for the result of using another SELECT statement in the FROM clause of a SELECT statement. This works because the result of a SELECT statement is a table, which is exactly what the FROM clause requires. You can think of a view as a named derived table. A view is named, and its definition is persistent and reusable; a derived table is a completely dynamic, temporal concept. To show the difference, here's an equivalent LIKE 'Ri%' query using a derived table instead of a view:

SELECT *  FROM
(SELECT  'Author'=RTRIM(au_lname) + ',' + au_fname, 'Title'=title
FROM (titleauthor AS TA
FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id))
RIGHT OUTER JOIN authors AS A ON (A.au_id=TA.au_id)
WHERE A.state <> 'CA' OR A.state IS NULL ) AS T
WHERE T.Author LIKE 'Ri%'

You can insert, update, and delete rows in a view but not in a derived table. Keep in mind that you're always modifying rows in the tables on which a view is based, because the view has no data of its own. The definition of the view is persistent, but the data is not. The data exists only in the view's base tables. Think of it as modifying data through a view rather than modifying data in a view. Some limitations to modifying data through a view exist; the SQL Server documentation explains them.

example:
CREATE VIEW CA_authors AS
(
SELECT * FROM authors WHERE state='CA'
)
GO

SELECT * FROM CA_authors
-- (returns 15 rows)

BEGIN TRAN

UPDATE CA_authors SET state='IL'
SELECT * FROM CA_authors
-- (returns 0 rows)

ROLLBACK TRAN

Altering Views
SQL Server allows you to alter the definition of a view. The syntax is almost identical to the syntax for creating the view initially:

ALTER VIEW view_name  [(column [,…n])]
[WITH ENCRYPTION] AS
select_statement [WITH CHECK OPTION]

Modifying Data Through View
You can specify INSERT, UPDATE, and DELETE statements on views as well as on tables, although you should be aware of some restrictions and other issues. Modifications through a view end up affecting the data in an underlying base table (and only one such table) because views don't store data. All three types of data modifications work easily for single-table views, especially in the simplest case, in which the view exposes all the columns of the base table. But a single-table view doesn't necessarily have to expose every column of the base table—it can restrict the view to a subset of columns only.

If your views or your desired data modification statements don't meet the requirements for actually updating through the view, you can create INSTEAD OF TRIGGERS on a view. The code in an INSTEAD OF TRIGGER is executed instead of a specified modification operation, and this is the only kind of trigger you can place on a view. (The other kind of trigger is an AFTER TRIGGER.) For example, suppose you have a view based on a join between two tables, and you want to update columns in both tables. An INSTEAD OF TRIGGER on UPDATE of your view can issue two separate UPDATE statements so that both tables can actually be updated. I'll discuss INSTEAD OF TRIGGERS along with AFTER TRIGGERS in. The remaining discussion in this section will assume there are no INSTEAD OF TRIGGERS on your views and that you want to modify the base table data directly through the view.
For any direct modification, all the underlying constraints of the base table must still be satisfied. For example, if a column in the base table is defined as NOT NULL and doesn't have a DEFAULT declared for it, the column must be visible to the view and the insert must supply a value for it. If the column weren't part of the view, an insert through that view could never work because the NOT NULL constraint could never be satisfied. And, of course, you can't modify or insert a value for a column that's derived by the view, such as an arithmetic calculation or concatenation of two strings of the base table. (You can still modify the nonderived columns in the view, however.)
Basing a view on multiple tables is far less straightforward. You can issue an UPDATE statement against a view that is a join of multiple tables, but only if all columns being modified (that is, the columns in the SET clause) are part of the same base table. An INSERT statement can also be performed against a view that does a join only if columns from a single table are specified in the INSERT statement's column list. Only the table whose columns are specified will have a row inserted: any other tables in the view will be unaffected. A DELETE statement can't be executed against a view that's a join because entire rows are deleted and modifications through a view can affect only one base table. Because no columns are specified in a delete, which table would the rows be deleted from?
In the real world, you would probably have little use for an INSERT statement against a view with a join because all but one table in the underlying query would be totally ignored. But the insert is possible. The following simple example illustrates this:

CREATE TABLE one
(   col11    int    NOT NULL,
    col12    int    NOT NULL
}
CREATE TABLE two
(
    col21    int    NOT NULL,
    col22    int    NOT NULL
)
GO

CREATE VIEW one_two

AS
(SELECT col11, col12, col21, col22
FROM one LEFT JOIN two ON (col11=col21))
GO

INSERT one_two (col11, col12)

VALUES (1, 2)
SELECT * FROM one_two

Here's the result:

col11   col12    col21    col22
-----       -----       -----        -----
1          2          NULL     NULL

Notice that this insert specifies values only for columns from table one, and only table one gets a new row. Selecting from the view produces the row only because LEFT OUTER JOIN is specified in the view. Because table two contains no actual rows, a simple equijoin would have found no matches. Although the row would still have been inserted into table one, it would have seemingly vanished from the view. You could specify the view as an equijoin and use WITH CHECK OPTION to prevent an insert that wouldn't find a match. But the insert must still affect only one of the tables, so matching rows would have to already exist in the other table. I'll come back to WITH CHECK OPTION in the next section; for now, here's how it would be specified:

CREATE VIEW one_two_equijoin

AS
(SELECT col11, col12, col21, col22
FROM one JOIN two ON (col11=col21))
WITH CHECK OPTION
GO

If you try to specify all columns with either view formulation, even if you simply try to insert NULL values into the columns of table two, an error results because the single INSERT operation can't be performed on both tables. Admittedly, the error message is slightly misleading.

INSERT one_two (col11, col12, col21, col22)

VALUES (1, 2, NULL, NULL)

Server: Msg 4405, Level 16, State 2, Line 1

View or function 'one_two' is not updatable because the modification

affects multiple base tables..

Similarly, a DELETE against this view with a join would be disallowed and results in the same message:

DELETE one_two

Server: Msg 4405, Level 16, State 1, Line 1

View or function 'one_two' is not updatable because the modification

affects multiple base tables referenced.

The UPDATE case isn't common, but it's somewhat more realistic. You'll probably want to avoid allowing updates through views that do joins (unless you have INSTEAD OF TRIGGERS), and the next example shows why.

Given the following view,

CREATE VIEW titles_and_authors

AS
( SELECT A.au_id, A.au_lname, T.title_id, T.title
FROM
authors AS A
FULL OUTER JOIN titleauthor AS TA ON (A.au_id=TA.au_id)
FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id)
)

selecting from the view

SELECT * FROM titles_and_authors
The following UPDATE statement works fine because only one underlying table, authors, is affected. This example changes the author's name from DeFrance to DeFrance-Macy.

UPDATE TITLES_AND_AUTHORS

SET au_lname='DeFrance-Macy'
WHERE au_id='722-51-5454'
(1 row(s) affected)

This UPDATE statement yields an error, however, because two tables from a view can't be updated in the same statement:

UPDATE TITLES_AND_AUTHORS
SET au_lname='DeFrance-Macy', title='The Gourmet Microwave Cookbook'
WHERE au_id='722-51-5454' and title_id='MC3021'
Server: Msg 4405, Level 16, State 2, Line 1

View or function 'TITLES_AND_AUTHORS' is not updatable because the modification affects multiple base tables referenced.

Download Artical

Identity Property

It is common to provide simple counter-type values for tables that don't have a natural or efficient primary key. Columns such as cust_id are usually simple counter fields. The IDENTITY property makes generating unique numeric values easy. IDENTITY isn't a datatype; it's a column property that you can declare on a whole-number datatype such as tinyint, smallint, int, or numeric/decimal (having a scale of zero). Each table can have only one column with the IDENTITY property. The table's creator can specify the starting number (seed) and the amount that this value increments or decrements. If not otherwise specified, the seed value starts at 1 and increments by 1, as shown in this example:


CREATE TABLE customer
(
cust_id smallint IDENTITY NOT NULL,
cust_name varchar(50) NOT NULL
)

To find out which seed and increment values were defined for a table, you can use the IDENT_SEED(tablename) and IDENT_INCR(tablename) functions. The statement

SELECT IDENT_SEED('customer'), IDENT_INCR('customer')

produces

1 1

for the customer table because values weren't explicitly declared and the default values were used.
This next example explicitly starts the numbering at 100 (seed) and increments the value by 20:

CREATE TABLE customer
(
cust_id smallint IDENTITY(100, 20) NOT NULL,
cust_name varchar(50) NOT NULL
)
Download

Altering a Table

SQL Server allows existing tables to be modified in several ways. You can use ALTER TABLE to add or drop constraints from a table. Using the ALTER table command, you can make the following types of changes to an existing table:

● Change the datatype or NULL property of a single column.
● Add one or more new columns, with or without defining constraints for those columns.
● Add one or more constraints.
● Drop one or more constraints.
● Drop one or more columns.
● Enable or disable one or more constraints (only applies to CHECK and FOREIGN KEY constraints).
● Enable or disable one or more triggers.

Changing a Datatype
By using the ALTER COLUMN clause of ALTER TABLE, you can modify the datatype or NULL property of an existing column. But be aware of the following restrictions:

● The modified column can't be a text, image, ntext, or rowversion (timestamp) column.
● If the modified column is the ROWGUIDCOL for the table, only DROP ROWGUIDCOL is allowed; no datatype changes are allowed.
● The modified column can't be a computed or replicated column.
● The modified column can't have a PRIMARY KEY or FOREIGN KEY constraint defined on it.
● The modified column can't be referenced in a computed column.
● The modified column can't have the type changed to timestamp.
● If the modified column participates in an index, the only type changes that are allowed are increasing the length of a variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing nullability of the column, or both.
● If the modified column has a UNIQUE OR CHECK constraint defined on it, the only change allowed is altering the length of a variable-length column. For a UNIQUE constraint, the new length must be greater than the old length.
● If the modified column has a default defined on it, the only changes that are allowed are increasing or decreasing the length of a variable-length type, changing nullability, or changing the precision or scale.
● The old type of the column should have an allowed implicit conversion to the new type.
● The new type always has ANSI_PADDING semantics if applicable, regardless of the current setting.
● If conversion of an old type to a new type causes an overflow (arithmetic or size), the ALTER TABLE statement is aborted.

Here's the syntax and an example of using the ALTER COLUMN clause of the ALTER TABLE statement:

SYNTAX

ALTER TABLE table-name ALTER COLUMN column-name
{ type_name [ ( prec [, scale] ) ] [COLLATE <collation name> ]
[ NULL NOT NULL ]
{ADD DROP} ROWGUIDCOL }

EXAMPLE

/* Change the length of the emp_name column in the employee table from varchar(30) to varchar(50) */

ALTER TABLE employee
ALTER COLUMN emp_name varchar(50)

Adding a New Column
You can add a new column, with or without specifying column-level constraints. You can add only one column for each ALTER TABLE statement. If the new column doesn't allow NULLs and isn't an identity column, the new column must have a default constraint defined. SQL Server populates the new column in every row with a NULL, the appropriate identity value, or the specified default. If the newly added column is nullable and has a default constraint, the existing rows of the table are not filled with the default value, but rather with NULL values. You can override this restriction by using the WITH VALUES clause so that the existing rows of the table are filled with the specified default value.
Dropping a Column
You can use ALTER TABLE to remove one or more columns from a table. However, you can't drop the following columns:

● A replicated column.
● A column used in an index.
● A column used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
● A column associated with a default defined with the DEFAULT keyword or bound to a default object.
● A column to which a rule is bound.

Dropping a column is accomplished using the following syntax:

ALTER TABLE table-name
DROP COLUMN column-name [, next-column-name]...

Downlosd