Sql Server Interview Questions and Answers (1758) - Page 5

The process of converting many fine-grain locks into fewer coarse-grain locks.
ie:
fine-grain = ROW
coarse-grain = TABLE

Words that do not participate in a full-text query search, such as "a", "and", and "the".

The process of recovering only the transactions within a log backup that were committed before a specific point in time, instead of recovering the whole backup.

The binding of an object to a query.

To apply logged changes to the data in a roll forward set to bring the data forward in time.

A transaction logging method in which the log is always written prior to the data.

In SQL Server 2005, we can create a new database by restoring a database backup taken using SQL Server 7.0, 2000 or 2005.

SQL Server 6.5 or earlier are cannot be restored in SQL Server 2005.

But, backups of master, model and msdb that were created by using SQL Server 7.0 or 2000 cannot be restored to SQL Server 2005.

SQL Server 7.0 log backups that contain create-index operations cannot be restored to SQL Server 2000 or 2005.

SQL Server 2005 uses a different default path than earlier versions. To restore a database created in the default location of either SQL Server 7.0 or 2000 from backups, we must use the MOVE option.

When we install SQL Server 2005, any existing databases (SQL Server 7 or 2000) are automatically upgraded. If a database that was created by using SQL Server 6.5 or earlier has not been upgraded to SQL Server 2005, we have to convert it to SQL Server 2005 by using one of the following methods:

1. Use the Integration Services (SSIS) Import and Export Wizard.
2. Export the data into a data file using "BCP out" in Character mode and import the data into a SQL Server 2005 database using "BCP in" with the "-V".

NOTE: This is objective type question, Please click question title for correct answer.

A FOREIGN KEY constraint can reference columns within the same table (a table itself).

True

NOTE: This is objective type question, Please click question title for correct answer.

Untyped XML :
I have created one Table named : Table1

CREATE TABLE TABLE2

	(XMLSample	XML)

GO

INSERT TABLE2 VALUES('TEST')	

INSERT TABLE2 VALUES('123')	

- The table should not allow any TYPE of data other than XML format with specific element. But it allows. This is called 'UnTyped XML '.

Typed XML :
I create one Table with one column as XML datatype. It should allow only INTEGER type of data along with some specific XML Element. This is called 'Typed XML '

1. I have to define one XML SCHEMA ( Type of the XML Element and Name of the Element, Etc., )

CREATE XML SCHEMA COLLECTION PandianXMLSchema AS '

<schema xmlns="http://www.w3.org/2001/XMLSchema">

      <element name="Dotnetfunda" type="int"/>

</schema>'

GO

I have created one XML SCHEMA named 'PandianXMLSchema ' ( It will say what is the name of the 'Element ' - and Type of the Element ).

2. I create one Table with one column as XML datatype along with XML SCHEMA which we created above. It should allow only INTEGER type of data along with Dotnetfunda XML Element alone.

CREATE TABLE TABLE2

	(XMLSample	XML(PandianXMLSchema))

GO

3. Inserting data into the TABLE2

INSERT TABLE2 VALUES('TEST')

- It will throw an Err
Msg 6909, Level 16, State 1, Line 1
XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content. Location: /

Because, the data 'TEST' is not an INTEGER and Its not encloused with 'Dotnetfunda ' element.

INSERT TABLE2 VALUES('123')

- It will throw an Err
Msg 6909, Level 16, State 1, Line 1
XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content. Location: /

Because, the data '123' is an INTEGER. But Its not encloused with 'Dotnetfunda ' element.

INSERT TABLE2 VALUES('<Dotnetfunda>TEST</Dotnetfunda>')

- It will also throw an Err

Msg 6926, Level 16, State 1, Line 1

XML Validation: Invalid simple type value: 'TEST'. Location: /*:Dotnetfunda[1]

The data 'TEST' encloused with proper element, But Its not an INTEGER type.

INSERT TABLE2 VALUES('<Dotnetfunda>123</Dotnetfunda>')

(1 row(s) affected)

XMLSample
<Dotnetfunda>123</Dotnetfunda>

Cheers

NOTE: This is objective type question, Please click question title for correct answer.

NOTE: This is objective type question, Please click question title for correct answer.

@@VERSION

Returns the date, version, and processor type for the current installation of SQL Server.



The information returned by @@VERSION  is similar to the product name, version, platform, and file data returned by the xp_msver stored procedure, which provides more detailed information.

SELECT @@VERSION

Cursor requires a network roundtrip each time it fetches a record, thus consume network resources.
While data processing, it issues locks on part of the table, or on the whole table.

Bulk copying is used to transfer large amount of data.

NOTE: This is objective type question, Please click question title for correct answer.

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

NOTE: This is objective type question, Please click question title for correct answer.