SQL Server table - August 29, 2008 at 18:00 PM by Amit Satpute
What is Cascade and restrict in Drop table SQL?
RESTRICT indicates that the table should not be dropped if any dependencies
exist. If dependencies are found, an error is returned and the table isn't
dropped.
CASCADE specifies that the dependencies be removed before the drop is performed
SQL Server table - Nov 18, 2008 at 18:00 PM by Rajmeet Ghai
Define SQL Server Tables. Explain Create Table syntax with an example.
Answer
SQL server tables are used to store data. Each table is divided into
rows and columns. Each row has some data against the specified column. Each
column has a data type thereby restricting the type of data it can accept.
Syntax:
CREATE TABLE "table_name" ("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2", ... )
Example:
CREATE TABLE Customer (ID Integer (10), First_name Varchar(20));
Explain how to determine column nullability of a table.
Answer
The column nullability can be determined if:
a. source table column is nullable: If any of the source columns are nullable,
the result column is nullable
b. Source table column is not nullable; the column in the new table is defined
as not null: If all the source columns in the expression are not nullable, the
result column is not nullable.
Explain how to reference a table from another database in the same server.
Answer
A table can be referred from another database on the same server the
following way:
Dbname.tablename
Example:
Select * from Dbtest.customer
Explain how to rename a table.
Answer
The ALTER table statement can be used to rename a table.
ALTER TABLE table_name
RENAME TO new_table_name
Example:
ALTER TABLE customer
RENAME TO cust_details;
Explain how to change the owner of a table.
Answer
Owner of a table can be changed by using:
Sp_changeobjectowner [obj_name] ‘object’, [ @newowner = ] 'owner'
Example:
Sp_changeobjectowner ‘customer’, ‘Mark’
Here, customer table is the obj_name while Mark is owner.
Explain autonumbering and identifier column of a table.
Answer
Each table can have a unique identifier that uniquely identifies the
row. This identifier can be an auto incremented value generated by system. For
e.g. Customer_id Identifier columns can contain values unique within the table
on which they are defined. This means that other tables with an identifier
column can have the same values. A single, unique identifier column can be
created for every table. Such columns are typically used for billing system to
avoid duplications.
Explain how to determine if a table has a primary key?
Answer
A primary key is usually identified by the keyword PRIMARY KEY. Any
column that has the keyword (while creating the table) as PRIMARY KEY means the
table has a primary key. The values of this column are always unique.
What is globally unique identifier?
Answer
A globally unique identifier is sued to provide a unique reference
number. This GUID is unique across tables, servers and locations. The GUID
values are unique and hence may be large in number. NEWID() can be used to
create such unique values.
How can we determine if a column is an identity column?
Answer
Using OBJECTPROPERTY function, one can find if the table has an
identity column.
Syntax:
OBJECTPROPERTY(object_id,proeprty)
Here the property will be TableHasIdentity. SQL Server returns 1 if the table
has an identity column, 0 if it doesn't.
How can we determine if a column is a primary column?
Answer
Using OBJECTPROPERTY function, one can find if the table has an
identity column.
Syntax:
OBJECTPROPERTY(object_id,proeprty)
Here the property will be TableHasPrimaryKey. SQL Server returns 1 if the table
has a primary key, 0 if it doesn't.
What are the purposes of table variables?
Answer
a. Table variables can be used to store result set in a SQL serer.
This result set can be used for processing later.
b. Table variables can be used in stored procedures, user defined functions.
c. Table variables give a better performance
d. Table variables can be used in a SELECT, INSERT, UPDATE statements.
e. Unlike temporary variables, they don’t require a declaration or cleaning up
What are some of the drawbacks of table variables?
Answer
a. SQL server does not maintain a statistics of table variables. This
means that how and where these variables were used cannot be determined because
they are washed out as soon as the stored procedure or function is finished
execution.
b. Table definition of a table variable cannot be changed after a DECLARE
statement. Hence, NO ALTER statement will work.
c. Table variables cannot be used In ROLLBACK of transactions
|