Interview questions

SQL Server table


SQL Server table - Interview questions

<<Previous  Next>>

Define Table.
Define Local temporary table and global temporary table.
What is Cascade and restrict in Drop table SQL?
Define SQL Server Tables. Explain Create Table syntax with an example.
Explain how to determine column nullability of a table.
Explain how to reference a table from another database in the same server.
Explain how to change the owner of a table.
Explain autonumbering and identifier column of a table.
What is globally unique identifier?
How can we determine if a column is an identity column?
How can we determine if a column is a primary column?
What are the purposes of table variables?
What are some of the drawbacks of table variables?

SQL Server table - August 29, 2008 at 18:00 PM by Nishant Kumar

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 - 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


<<Previous  Next>>

Also read

Describe in brief exporting and importing utility?

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....

Define temporary and extended stored procedure.

Answer - Temporary Stored Procedure is stored in TempDB database. It is volatile and is deleted once connection gets terminated or server is restarted......

What is index? Define its types.

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.........

Question: Explain Full-Text Query in SQL Server.

Answer - SQL Server supports searches on character string columns using Full-Text Query......

Question: Explain the phases a transaction has to undergo.

Answer - The several phases a transaction has to go through are listed here. Database.....

Question: What is XPath?

Answer - XPath is a language defined by the W3C, used to select nodes from XML documents. ....



Write your comment - Share Knowledge and Experience


 
Latest placement tests
Latest links
 
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring