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

Yes, we can reset the identity column

use the dbcc checkident statement

example:
create table sales
(sno int identity(100,1),
sname varchar(10))

insert sales values('usb')
insert sales values('kbd')

select * from sales

sno sname
100 usb will be the output.
101 kbd

Now run this command.

dbcc checkident('sales',reseed,50)

the seed value will be reset to 50

They are basically select statements in the from clause referred to by an
alias name.The alias name creates a table in the result set that can then be
referred to by some other Select statement

example:
2 tables emp and emp1 have got salary column. To calculate the maximun
salary from the salary columns of 2 tables

select max(salary) from (select salary from emp union select salary from emp1) a

a: is the derived table that will provide the input to the outer query

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

It is a statement that allows us to use double quotes when namimg the objects

for example:
1)create table "demo"
(ono int)

2)create table "select"
(sno int)

These 2 statements will work only when
set quoted_identifier on

if
set quoted_identifier off

Both the statements will fail

set quoted_identifier is on by default

Identity Column:
1.Identity column is auto incremented
2.Incremented numeric values only
3.Only one Identity column in table
4.All identity column is an primary Key
5.Values cannot be updated

Primary Key:
1.Primary Key value will be entered by the user.
2.Can be created more than one column (composite primary key).
3.All primary key is not an identity column.
4.Can be update the value
5.Can be refer by other table as a foreign key.

Sql server have 2 different authentication
1) Windows Authentication
2) SQL Authentication

Windows authentication uses the windows credentials to authenticate the user and takes it to the server

SQL authentication uses sql user name and password to authenticate the user and its secure as well

Microsoft suggests to use windows authentication inorder to maintain a single authentication by maintaining in active directory

Computed columns are the columns that can be used to store the calculated
results based upon some other columns of the table

create table emptable

(eno int primary key,

ename varchar(10),

basic float,

hra float,

pf float,

gross as basic+hra+pf)



--gross is the computed column.

insert emptable values(100,'king',5000.77,500,200)

---------------------

select * from emptable

--output--

eno ename basic hra pf gross

100 king 5000.77 500 200 5700.77


--we cannot insert or update in a computed column

count(*): gives the total number of rows in a table

count_big(*): also gives the total number of rows in a table

difference: count(*): returns int datatype value

count_big(*):returns bigint datatype value

count(<some columnname>: returns the total number of rows of a column

where the value of the column is not null

Consider this table emp

eno ename salary
100 dd 6000
200 ss 6500
400 ddd NULL

now run the query:

select count(*), count_big(*), count(salary) from emp

output will be

3 : below the count(*) column
3: below the count_big(*) column
2: below the count(salary)



(no column name will be displayed
for any of the columns since no alias has been used)

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

Where Clause:
1.Where Clause can be used other than Select statement also
2.Where applies to each and single row
3.In where clause the data that fetched from memory according
to condition
4.Where is used before GROUP BY clause
Ex:Using Condition for the data in the memory.

Having Clause:
1.Having is used only with the SELECT statement.
2.Having applies to summarized rows (summarized with GROUP BY)
3.In having the completed data firstly fetched and then separated according to condition.
4.HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query
Ex: when using the avg function and then filter the data like ava(Sales)>0

Summary:
Having works like Where clause with out Group By Clause

Union:
1.UNION only selects distinct values
2.Output is in sorted order

UnionAll:
1.UNION ALL selects all values (including duplicates).
2.Output is not in sorted order

EX:

SELECT Col 

FROM @Table1

UNION

SELECT Col

FROM @Table2

/* Result of Union All operation */

SELECT Col 

FROM @Table1

UNION ALL

SELECT Col

FROM @Table2

Output:
Union:
1
2
3
5

UnionAll:

1
2
3
2
5

There are four JOBs involved in Log Shipping

1. Backup the Log on Primary Server Job
2. Copy the Log backup file to Secondary Server Job
3. Restoring the Log on Secondary Server Job
4. Notifying alerts for errors (If any)

Yes, we can use order by clause when defining a view.
The condition is we have to include either TOP or FOR XML clauses also in the query

example:

create view gt as select * from emp order by ename 

--Wrong

But

create view gt as select top 5 * from emp order by ename

-- Correct

There are few differences between Stored Procedure and Function that are
• Stored Procedures are stored in a compiled format into the database where as Functions are compiled at run time.

• The Stored Procedures can perform certain tasks in the database by using insert, delete, update and create commands but in function you can't use these commands.

• Normally the Stored procedures are used to process certain task but the Functions are used to compute the values that is we can pass some value as input and then it perform some task on the value and return output.

• Stored procedures can change in the server directly but Functions cannot change in the server directly.

• To run a Stored Procedure we have to use the Execute or Exec command where as Functions can run as an executable file.

• Stored Procedure can return multiple values where as Functions can return only single value.

• The Stored Procedures can be used directly in the program by using its commandtype but Functions can be used by using SQL Query.

• The Stored Procedures are having both IN and OUT parameter where as Functions are always having IN parameter, no OUT parameter is possible.

• Stored Procedures cannot be used as an inline with a select statement while Functions can.

• The temparary variable is required to hold the return value of a Stored Procedure but in Functions, the temporary variable is optional.

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

using INSTEAD OF trigger, validates row counts of both original and INSERTED tables

CREATE TRIGGER trInsertEmployee ON Employee

INSTEAD OF INSERT

AS

BEGIN

      IF EXISTS

      (

            SELECT *

            FROM dbo.Employee

      )

      BEGIN

		PRINT 'Table contains one record.'

		RETURN

	  END

	  ELSE

	  BEGIN

	        IF ( SELECT COUNT(*) FROM INSERTED) > 1

	        BEGIN

				PRINT 'Can not insert more than one record.'

				RETURN

	        END

	        ELSE

	        BEGIN

	        	  INSERT INTO dbo.Employee(EmpID, EmpFullName, DeptID, GrossSalary)

				  SELECT TOP 1 EmpID, EmpFullName, DeptID, GrossSalary

					FROM INSERTED

	        END

	  END

END

local:
1) denoted by # symbol.
2) valid for the current connection only.
They are cleared as soon as the curent connection closes.
3)cannot be shared between multiple users.

global:
1)denoted by ## symbol.
2)Available to all the connections once created.
They are cleared when the last connection is closed.
3)can be shared betwen multiple users.

Both of then are stored in the tempdb database

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables. Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

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

1. Demonstrate or whiteboard how you would suggest using configuration files in packages. Would you consider it a best practice to create a configuration file for each connection manager or one for the entire package?

There should be a single configuration file for each connection manager in your packages that stores their connection string information. So if you have 6 connection managers then you have 6 config files. You can use the same config file across all your packages that use the same connections.
If you have a single config file that stores all your connection managers then all your packages must have contain the connection managers that are stored in that config file. This means you may have to put connection managers in your package that you don’t even need.

2. Demonstrate or whiteboard how checkpoints work in a package.
When checkpoints are enabled on a package if the package fails it will save the point at which the package fails. This way you can correct the problem then rerun from the point that it failed instead of rerunning the entire package. The obvious benefit to this is if you load a million record file just before the package fails you don’t have to load it again.

3. Demonstrate or whiteboard using a loop in a package so each file in a directory with the .txt extension is loaded into a table. Before demonstrating this tell which task/container accomplishes this and which enumerator will be used. (Big hint on which task/container to use is that it requires and enumerator)
This would require a Foreach Loop using the Foreach File Enumerator. Inside the Foreach Loop Editor you need to set a variable to store the directory of the files that will be looped through. Next select the connection manager used to load the files and add an expression to the connection string property that uses the variable created in the Foreach Loop.

4. Demonstrate or whiteboard how transactions work in a package.
If transactions are enabled on your package and tasks then when the package fails it will rollback everything that occurred during the package. First make sure MSDTC (Microsoft Distributed Transaction Coordinator) is enabled in the Control Panel -> Administrative Tools -> Component Services. Transactions must be enabled not only on the package level but also on each task you want included as part of the transaction. To have the entire package in a transaction set TransactionOption at the package level to Required and each task to Supported.