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 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.
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
Also read
Answer - The Bulk Copy is a command utility that transfer SQL
data to or from a data file.
This utility mostly used to transfer huge data to SQL server from other
database....
Answer - Temporary Stored Procedure is stored in TempDB
database. It is volatile and is deleted once connection gets terminated or
server is restarted......
Answer - Index can be thought as index of the book that is used
for fast retrieval of information. Index uses one or more column index keys and
pointers to the record to locate record.........
Answer - SQL Server supports searches on character string
columns using Full-Text Query......
Answer - The several phases a transaction has to go through are
listed here. Database.....
Answer - XPath is a language defined by the W3C, used to select
nodes from XML documents. ....
|
