1) How many rows does your SQL Server table contain?

USE Broadcast75
SELECT o.name as [Name],i.rowcnt as

[Row Count]FROM sysindexes i
INNER JOIN sysobjects o ON i.id = o.id
WHERE indid < style="color: gray;">AND o.xtype = 'U'
ORDER BY Name

2) What is maximum size of temp db?

The syntax is: sp_helpdb 'tempdb'

db_size of a tempdb is 8.50 MB in sqlserver 2005.

3) How insert selected column only?

insert into Employees (EmpName,Designation)

values ('senthil','Software Engineer')

4) When we use function instead of procedure? With example?

·        Usually procedures are used when some action is to be performed
 without return value.

·        Functions are used when some value is to be returned to the
calling environment.

·        You can not use procedure in the select statement however functions
can be used.

5) How to create a table using Stroed procedure?

Create procedure Table_Proc as

declare @Country table(CountryCode Int primary key Identity,

CountryName varchar(100),NoCities int)

insert into @Country values('India',26)

insert into @Country values('Usa',20)

Select * from @country

Exec Table_Proc

6) What are cursors? Explain different types of cursors.

What are the disadvantages of cursors?

How can you avoid cursors?

Cursors allow row-by-row processing of the result sets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.

Disadvantages of cursors: Each time you fetch a row from the cursor,

it results in a network roundtrip, where as a normal SELECT query makes

only one roundtrip, however large the resultset is. Cursors are also costly

because they require more resources and temporary storage

(results in more IO operations). Further, there are restrictions on the

SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors.

7) What are triggers? How to invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed automatically

when an INSERT, UPDATE or DELETE operation takes place on a table.

Triggers can't be invoked on demand. They get triggered only when an

associated action (INSERT, UPDATE, and DELETE) happens on the table

on which they are defined.

Triggers are generally used to implement business rules, auditing.

Triggers can also be used to extend the referential integrity checks,

but wherever possible, use constraints for this purpose, instead of triggers,

as constraints are much faster.

8) Difference between a "where" clause and a "having" clause.

Having clause is used only with group functions whereas Where is not used with.

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

10) What is GROUP BY?

The GROUP BY keywords have been added to SQL because aggregate

functions (like SUM) return the aggregate of all column values every time

they are called. Without the GROUP BY functionality, finding the sum for

each individual group of column values was not possible.