Go and get SQL Server How to Tips & Tricks - a complete reference eBook | For more SQL Server Interview Questions click here.

Sql Server Interview Questions and Answers (1774) - Page 1

What is trigger?

Triggers allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table.

Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the insert, delete and update operation.
What is Clustered & Non-Clustered Index?

Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains.


Non-Clustered Index: It Non-Clustered Index contains pointers to the data that is stored in the data page. It is a kind of index backside of the book where you see only the reference of a kind of data.
How many types of triggers are there?

There are four types of triggers.

1. Insert
2. Delete
3. Update
4. Instead of
What is constraints?

SQL Server users constraints to enforce limitations on the data that can be entered into a particular column in table. There are following types of constraints.

Unique, Default, Check, Primary Key, Foreign Key, Not Null.
What is the difference between Truncate and Delete?

Delete statement removes rows of a table one by one & delete triggers on that table fires. But Truncate removes all rows by deallocating the data pages assigned to the table & only these deallocation are recorded in the transaction log.
What Primary key and Unique key?

Primary key are used with Foreign key to enforce referential integrity. Unique constraints allows nulls to be inserted into the field. But there can't be null in Primary key.
How to join two tables in Sql Server?

you can write following sql statement

select category.*, categoryparent.categoryparent from category, categoryparent where category.categoryparentid = categoryparent.autoid

I am assuming here that category.categoryparentid (foreign key) is the value of categoryparent.autoid (primary key).
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
What's the difference between a primary key and a unique key?

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.
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course.
What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
What's the maximum size of a row?

8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".
Difference Between Implict Transaction And Explict Transaction

Implicit Transaction is the auto commit. There is no beginning or ending of the transaction.

Explicit Transaction has the beginning, ending and rollback of transactions with the command
Begin Transaction
Commit Transaction and
Rollback Transation
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.
what is the diff between a HAVING CLAUSE and a WHERE CLAUSE?

You can use Having Clause with the GROUP BY function in query and WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
How to change Database name in SQL Server?

Use following code

Supported in SQL Server 2000 and 2005
exec sp_renamedb "test", "test1"

Supported in SQL Server 2005 and later version
ALTER Database "test1" Modify Name="test"
Difference between Primary key Constraint and Unique key Constraint in SQL Server.

Unique Key Constraint:
The column values should retain uniqueness.
It allows null values in the column.
It will create non-clustered index by default.
Any number of unique constraints can be added to a table.

Primary Key Constraint:
Primary key will create column data uniqueness in the table.
It Wont allow Null values.
By default Primary key will create clustered index.
Only one Primary key can be created for a table.
Multiple columns can be consolidated to form a single primary key.

Source: [b]http://interviews.dotnetthread.com/ [/b]
What is cursor in SQL Server?

A cursor is a set of rows together with a pointer that identifies a current row.

In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.

Typical syntax of cursor is

DECLARE @fName varchar(50), @lName varchar(50)

DECLARE cursorName CURSOR -- Declare cursor
LOCAL SCROLL STATIC
FOR
Select firstName, lastName FROM myTable
OPEN cursorName -- open the cursor
FETCH NEXT FROM cursorName
INTO @fName, @lName
PRINT @fName + ' ' + @lName -- print the name
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursorName
INTO @fName, @lName
PRINT @fName + ' ' + @lName -- print the name
END

CLOSE cursorName -- close the cursor
DEALLOCATE cursorName -- Deallocate the cursor


To know more about cursor, see
http://www.mssqlcity.com/Articles/General/UseCursor.htm
What is #temp table and @table variable in SQL Server?

#temp Table (Temporary Table)

temp table is a temporary table that is generally created to store session specific data. Its kind of normal table but it is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.

The data in this #temp table (in fact, the table itself) is visible only to the current scope. Generally, the table gets cleared up automatically when the current procedure goes out of scope, however, we should manually clean up the data when we are done with it.

Syntax:

-- create temporary table

CREATE TABLE #myTempTable (
AutoID int,
MyName char(50) )

-- populate temporary table
INSERT INTO #myTempTable (AutoID, MyName )
SELECT AutoID, MyName
FROM myOriginalTable
WHERE AutoID <= 50000


-- Drop temporary table
drop table #myTempTable


@table variable
table variable is similar to temporary table except with more flexibility. It is not physically stored in the hard disk, it is stored in the memory. We should choose this when we need to store less 100 records.

Syntax:

DECLARE @myTable TABLE (

AutoID int,
myName char(50) )

INSERT INTO @myTable (AutoID, myName )
SELECT YakID, YakName
FROM myOriginalTable
WHERE AutoID <= 50

-- to select the data from Temp variable

SELECT * FROM @myTable


We don't need to drop the @temp variable as this is created inside the memory and automatically disposed when scope finishes.
How to return XML in SQL Server?

We can use FOR XML statement at the end of the query to return xml data from the SQL Server.


select * from mytable for xml auto


There are three mode of returning XML and they are auto, raw and explicit

For more details see http://www.sqljunkies.ddj.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk
What is OPENXML in SQL Server?

OPENXML can parse the xml data in SQL server very efficiently in SQL Server. OpenXML primarily gives the ability to insert XML data to the relational database, however we can query the data too using OpenXML. We need to specify the path of the xml element using xpath.

Syntax:

DECLARE @index int

DECLARE @xmlString varchar(8000)
SET @xmlString ='<Persons>
<Person id="1">
<Name>Mohan</Name>
<PhoneNo>34343</PhoneNo>
</Person>
<Person id="2">
<Name>Sita</Name>
<PhoneNo>23432</PhoneNo>
</Person>
</Persons>'

EXEC sp_xml_preparedocument @index OUTPUT, @xmlString

SELECT *
FROM OPENXML (@index, 'Persons/Person')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')

EXEC sp_xml_removedocument @index


The above code snippet will give following result.
---------------------------------
1 Mohan 34343
2 Sita 23432
---------------------------------
Found this useful, bookmark this page to the blog or social networking websites. Page copy protected against web site content infringement by Copyscape

 Interview Questions and Answers Categories