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

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

Write query with which you can achieve paging from SQL side.

With below provided query you can get desired result. You need to pass parameter of startrowindex and maximum rows. Here in below example they are set to 0 and 200 respectively.


declare @StartRowIndex as int

set @StartRowIndex = 0

declare @MaximumRows as int

set @MaximumRows = 200

SELECT * FROM

(

      SELECT *, ROW_NUMBER() OVER (Order By Sort_Column) AS RowRank -- Add your desired column name which you want ordered here in place of "Sort_Column"

      FROM

      (

            SELECT distinct Sort_Column , column1 , column2 -- Desired list of the column names you want to retrieve

            FROM  [dbo].[table_name] WITH(NOLOCK)

			--* Optional section if you have some joins then place them here *--

            --inner join vTanks on vTanks.ClientId = Facilities.ClientId    

            --and vTanks.FacilityId = Facilities.Facilityid

      ) primarySelect

      WHERE Id = 294 AND Archive <> 1 -- specify your conditions here

) As joinTable

WHERE (RowRank > @StartRowIndex AND RowRank <= (@StartRowIndex + @MaximumRows))

ORDER BY table_name

How can you insert multiple rows together in a single insert statement for a table?

It is not possible to insert multiple rows with a single insert statement. If you want to insert multiple rows, one have to write multiple insert statements considering that you are working SQL server 2005 or it's below version.

But SQL Server 2008 allows to insert multiple rows with a single insert statement.

As for example,

In sql server 2005, if we need to insert multiple row, we used to do something like this.

CREATE TABLE [State] (

    [StateID]    int,

    [StateName]    VARCHAR(20)

)

GO



INSERT INTO State

VALUES (1, 'Gujarat')



INSERT INTO State

VALUES (2, 'Dehli')



INSERT INTO State

VALUES (3, 'Bihar')

But with SQL Server 2008 we can combine all the three insert statement in single insert statement. See below:

CREATE TABLE [State] (

    [StateID]    int,

    [StateName]    VARCHAR(20)

)

GO



INSERT INTO State

VALUES (1, 'Gujarat'),

	(2, 'Dehli'),

	(3, 'Bihar')

Hope this helps...

Tell name of the system procedure to create and drop linked server in SQL SERVER?

To create linked server
sp_addlinkedserver

To remove linked server
sp_dropserver

How will you delete all records of table other then recently added 10 records?

There can be many ways to get the desired results. Two of the simplest ways are described below assuming your table is having column(date1) containing value of data creation.
Way 1:
With the query below, we are deleting all the records except 10 records where id is not matching with the top most listed records based on time entry.

delete from table1

where id1 not in

(	

	select top 10 id1 from table1

	order by date1 desc

)

Way2:
With this query we are listing records in descending way with date as key and having index more then 10. All listed records will be deleted.

delete from table1

WHERE id1 in (

      SELECT id1

      FROM

        (

           SELECT id, ROW_NUMBER() OVER(ORDER BY date1 DESC) AS rownumber

           FROM table1

        ) AS a

      WHERE rownumber > 10

)

How can you declare and initialize variables in a single line with SQL Server ?

With SQL Server 2005, we need to declare and initialize variables individually.

declare @t1 int, 

@t2 varchar(10)

set @t1 = 4

set @t2 = 'Hello!'

print @t1

print @t2

But with SQL 2008, we can do it following way:

declare @t1 int = 4, 

@t2 varchar(10)= 'Hello!'

print @t1

print @t2

Which are the five types of SQL table objects?

1. Standard
2. Temporary
3. Table type
4. Derived
5. View

What is XQuery?

XQuery is a language that is designed to query an XML document in SQL Server.

What is sp_XML_Preparedocument procedure?

It is a system procedure which is used to read the XML document in the memory and returns a handle to this document. After that you can use OPEN_XML method to iterate through the xml result set.

Before OPEN_XML, one has to use sp_XML_Preparedocument.

What is sp_XML_RemoveDocument procedure?

This procedure is used to remove the XML document from memory created using sp_XML_Preparedocument procedure.

What is FOR XML clause in SQL?

FOR XML Clause is used with SQL Server’s Select statement to convert the relational data stored in your database to XML.

It has two options
1. Raw: To display XML element for each row.
2. Auto : It will automatically parse the structure used in query

In which table, SQL SERVER stores the user name and password used for connection?

SQL Server stores it in Syslogins table of Master database. See below query:

select * from syslogins

Does Views occupy memory?

No.

Clustered Index vs NonClustered Index.

Clustered Index :
A Clustered index determines the physical order of data in a table and is particularly efficient on columns that are often searched for ranges of values.
The leaf nodes of a clustered index contain the data pages.
There can be only one clustered index per table.
"primary key" is the ideal column for a clustered index
Clustered indexes are good for range searches.

Nonclustered Index :
Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
There can be multiple non-clustered indexes per table.
"Unique Key" is the ideal column for a nonclustered index
Non-clustered indexes are good for random searches.

What is Index?

A database index is a data structure which use to improve the speed of operations on a database table.

It's a physical structure containing pointers to the data.

Index can be created using one or more columns of a database table.
Index are created in an existing table to quickly and efficiently lookups.

It is possible to create an index on one or more columns of a table, and each index is given a name.

There are two type of index available.

Clustered index :
Clustered index are physical form of sorting. Therefore only one clustered index can be created on each table because the data rows themselves can only be sorted in one order.

Non Clustered index :
Non clustered indexes are logical form of sorting. Therefore more than one Non clustered indexes can be created on each table.

Difference between DELETE and TRUNCATE commands ?

TRUNCATE TABLE is faster that DELETE TABLE and uses fewer system and transaction log resources.
Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE ?

Both Clause are used to check the Condition at the time of Retrival of records in Database.
WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
Having Clause is basically used only with the GROUP BY function in a query.

What is COMMIT and ROLLBACK statement in SQL

Commit statement helps in termination of the current transaction and do all the changes that occur in transaction persistent and this also commits all the changes to the database.
ROLLBACK do the same thing just terminate the currenct transaction but one another thing is that the changes made to database are ROLLBACK to the database.

What are the different types of Locks

Mainly There are three types of locks in SQL Server :

(1)Shared locks are used for operations that does not allow to change or update data, such as a SELECT statement.
(2)Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
(3)Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

What is the difference between UNION ALL Statement and UNION

The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION.
Reason : Because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.