Define Table.
SQL Server database stores information in a two dimensional objects of rows and columns called table.
Define Local temporary table and global temporary table.
Local temporary table is created by prefixing name with pound sign like (#table_name). Global temporary table is created by prefixing name with Double pound sign like (##table_name). Local temporary table is dropped when the stored procedure completes. Global temporary tables are dropped when session that created the table ends
SQL Server table -
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
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.
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", ... )
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));
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:
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
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;
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.
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.
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.
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.
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.
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.
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.
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.
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
0 Comments:
Post a Comment