Sqlserver 2005 questions continued

Please refer to the following link as well.

click here


I did not took backup for a database.My colleague had mad some changes to some Stored procedures and I want to roll back to the original codr.Since he did not used the begin transaction i am not able to rollback the changes.>How can I get back to my original code?

Go to your last good full backup. Restore to another db using the MOVE command.

1)What is the maximum value that can be stored for a datetime field?

a. Dec 31, 9999
b. Jun 6, 2079
c. Jan 1, 2753
d. Jan 1, 2100
2)What is the correct SQL syntax for returning all the columns from a table named "Persons" sorted REVERSE alphabetically by "FirstName"?

a. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
b. SELECT * FROM Persons SORT REVERSE 'FirstName'
c. SELECT * FROM Persons ORDER BY -'FirstName'
d. SELECT * FROM Persons ORDER BY FirstName DESC
SELECT * FROM Persons ORDER BY DESC FirstName
3)What is the correct SQL syntax for selecting all the columns where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

a. SELECT * FROM Persons WHERE LastName > 'Hansen', LastName < 'Pettersen' b. SELECT LastName > 'Hansen' AND LastName < 'Pettersen' FROM Persons c. SELECT * FROM customers WHERE LastName > 'Hansen' AND LastName > 'Pettersen'
d. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
4)Consider the following queries:
1. select * from employee where department LIKE "[^F-M]%";
2. select * from employee where department = "[^F-M]%";
Select the correct option:

a. Query 2 will return an error
b. Both the queries will return the same set of records
c. Query 2 is perfectly correct
d.Query 2 would return one record less than Query 1


5)Which of the following is not a global variable?

a. @@colcount
b. @@error
c. @@rowcount
d. @@version
e. All are valid global variables
Consider the following two tables:
1. customers( customer_id, customer_name)
2. branch ( branch_id, branch_name )
6)What will be the output if the following query is executed:
Select * branch_name from customers,branch

a. It will return the fields customer_id, customer_name, branch_name
b. It will return the fields customer_id, customer_name, branch_id, branch_name
c. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
d. It will return an empty set since the two tables do not have any common field name
e. It will return an error since * is used alone for one table only
7)Which of the following is not a valid Numeric datatypes in SQL Server?

a. INT
b. SMALLINT
c. TINYINT
d. BIGINT
e. MONEY
8)Which of the following datatypes is not supported by SQL-Server?

a. Character
b. Binary
c. Logical
d. Date
e. Numeric
f. All are supported
9)Which of the following are false for batches (batch commands)?

a. Statements in a batch are parsed, compiled and executed as a group
b. None of the statements in the batch is executed if there are any syntax errors in the batch
c. None of the statements in the batch is executed if there are any parsing errors in the batch
d. None of the statements in the batch is executed if there are any fatal errors
in the batch

10) Select the correct option:

a. Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer
b. Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer
11) How can you view the structure of a table named "myTable" in SQL Server?
a. desc myTable

b. desc table myTable

c. sp_columns myTable

d. None of the above

e. Using either option a or c
12) Which of the following is not a valid binary datatype in SQL Server?

a. BINARY

b. VARBINARY

c. BIT

d. IMAGE
e. TESTAMP
13) Which of the following is false with regards to sp_help?

a. When a procedure name is passed to sp_help, it shows the parameters

b. When a table name is passed to sp_help, it shows the structure of the table
c. When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database

d. All of the above are true
14)Which of the following are false for batches (batch commands)?

a. Statements in a batch are parsed, compiled and executed as a group

b. None of the statements in the batch is executed if there are any syntax errors in the batch

c. None of the statements in the batch is executed if there are any parsing errors in the batch
d. None of the statements in the batch is executed if there are any fatal errors
in the batch

How to find out duplicate records in sql server?

Select columnName From Table_name Group By columnName Having count (*)> 1
Select max(empid),max(empname),salary from emp group by salary having count(salary) >1
By using following query we wil get the duplicate records

SELECT columnName FROM TableName
Group By columnName
Having Count(*)>1

How many column maximum we can add in one table and how many foreign key maximum we can use in one table?

Foreign key table references per table- 253

Columns per foreign key -16

table having a column named DATE, which takes date in the format dd/mm/yyyy, in SQL Server2000? Or any format of date.?

SELECTÂ CONVERT(varchar, GETDATE(), 103)

In this example the GETDATE() will return your sql server system date.

and the CONVERT is used to convert the date into the desired fomat.

Basically the CONVERT requires three arguments

1. Datatype

2. Date to Convert

3. Format style

Here is the list of style .....

Style ID
Style Type

0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM


Enjoy the Code...

Hope this will help u


using this query you can take as you like,

select convert(varchar,DATE,107) from tablename

DATE->one of the column is availabe in table which is created by you


CREATE TABLE mytable ([Date] SMALLDATETIME)

can we generate a trigger for two tables? if so what is the query to generate a trigger for two tables employee table and department table with employee having department no?

We can't

In a trigger we can't specified two table name

that means

CREATE TRIGGER < >
ON <{ table | view }>

{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]


expecting justification on my answer


Are there any Commands, can be used to calculate the performance of a Querry?


set statistics io on
set statistics time on

set showplan on

set showplan_text on


Write sql query for retrieving employee names from employee table who are having salary greater than 5000 without using where clause?


Hi,

If you don't want to use where clause to get the employee names who are getting salary higher than 5000, you can use having clause. The query will be as follows.

SELECT ENAME,SALARY FROM EMPLOYEES GROUP BY ENAME,SALARY HAVING SALARY>5000;

SELECT ENAME,SAL FROM Emp
having sal>2000
group by empno,ename,SAL;

What is fill factor, where it can be used?

When you create a clustered index, the data in the table is stored in the data pages of the database according to the order of the values in the indexed is known as fill factor

Fill factor is the term associated with indexes actually with clustured indexes

Whenever a clustured index is created sql server physically orders data in basis of the clustured column. As you must be aware that the data is sql server is stored in 8K data page. Fill factor value setting advice SQL Server to leave specified amount of space free on each page inorder to accommodate new data if no fill factor is set SQL Server data page will be filled completely and incase a new record comes it will have to make space in the data page by reorganizing rest of the pages which is an over head. And hence it is suggested to keep reasonable value for fill factor considering future requirements.


What is the difference between Inner join and full outer join in terms of the Number of rows returned?

Inner Join will return rows that have matching records in both table, but in case of full outer join it will return all records from left table and from right table.

In Left-Outer join all the record from the left-most table will be fetched and if records found in the 2nd table then field values will be displayed else it will display Null in the 2nd table fields.

Ex.
Create table EMPNAME (ID int, EMPNAME varchar(20))
CREATE table EMPADDRESS (ID int, ADDRESS varchar(20))

INSERT INTO EMPNAME VALUES(1, 'DEEPAN')
INSERT INTO EMPNAME VALUES(2, 'RAMESH')
INSERT INTO EMPNAME VALUES(3, 'PRADEEP')
INSERT INTO EMPADDRESS VALUES(1, 'BANGALORE')
INSERT INTO EMPADDRESS VALUES(2, 'DELHI')
INSERT INTO EMPADDRESS VALUES(4, 'DELHI')

SELECT * FROM EMPNAME
ID EMPNAME
----------- --------------------
1 DEEPAN
2 RAMESH
3 PRADEEP


SELECT * FROM EMPADDRESS
ID ADDRESS
----------- --------------------
1 BANGALORE
2 DELHI
4 DELHI

Example : INNER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above JOIN Query (i.e Inner Query)
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI

Example : FULL OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
FULL OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above FULL OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
3 PRADEEP NULL
NULL NULL DELHI

Example : LEFT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
LEFT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above LEFT OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
3 PRADEEP NULL

Example : RIGHT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
RIGHT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above RIGHT OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
NULL NULL DELHI


What is table in SQL server? What is ##table in SQL server?


#table and ##table are called as temporary tables in SQL Server. One with single # is called as local temp table and one with double # is called as global temp tables.Both are created are run time in tempdb (One of the SQL Server's system database).

When you create any table with single # the scope is limited to the session it cannot be reffered by any other session.

However when you want to share data across the sessions you should go for ## ie., global temperoary table data can be shared across multiple session

Note: As far as possible try avoiding temp tables as it kills your SQL quering techniques.

#table in SQL Server :

This # symbol prefixed with the tablename indicates that it is a local temporary table. This table will be existing till the session exists. Once the session expires the table will be dropped automatically. The table that is created with # symbol prefixed is temporary, so we can't give foreign key constraints to that table. Rest all the features are similar to that of the table that is permanent.

create table #Employee(EmpId int,EmpName Varchar(20))

##table in SQL Server:

This ## symbol prefixed with the tablename indicates that it is a Global Temporary table.This particular temporary table can be used by all the connections of SQL server and the temporary table is made available for all the users to access it.

The Temporary table can be mostly used for 'Select into' statements

Suppose if u want to get the structure of table categories

use the following command in sql server

sp_columns categories

This will work.. Like wise if u want to see the list of tables in ur database, use

sp_tables


Which is the best option used to move database from one server to another serve on same network?
a) Import - Export
b)Backup-Restore
c)Detach-Attach

Why?



Import-Export is the best option used to move database from one server to another serve on same network.

It reduces the network traffic.

Its very simple.

Take the latest backup of the database in any shared directory on the same server.

Copy the backup to the new server where you want to restore the database. Restore it using Enterprise Manager or SQL Query Analyser.

If you are restoring transaction Log backup be sure that you are attaching all the logfiles in the same sequence they were backed up followed by completed database backup. Also keep the database non-operational while restoring transaction log backup.

Adavnce features of sqlserver 2005 over sql server 2000?


SQL Server 2005 is having much more functionality than SQL Server 2000

SQL Server 2005 is having Integrated SQL Server Management Studio which combines Enterprise Manager, Query Analyser and lot More

SQL 2005 integrated very well with Visual Studio and dot net.

The DTS of SQL 2000 has been replaced with SSIS (Sql Server Integrated
Services). It is completely new and ready to compete with Other ETL Vendors.

BI Studio is having SSIS - SQL Server Intergraion Services, SSAS - SQL Server Analysis Services, SSRS - SQL Server Reporting Services and Notification Services.

It is having complete suit to build DWH. It is having end to end solution for DW/BI

There are lot of independent and powerful features have been implemented in SQL Server 2005,Some of these features are avaiable in other databases but some are specific for SQL Server for example

Database Partitions (all ready available in other Db's)

Service Broker: Enables developers to transmit messages using this at database level. This will help in deveoping applications which are similar use like MSMQ

CLR : Another new feature enables SQL developers to write code in C# or VB.NET instead of PL/SQL. Advantage of this is we can avoid using cursors

DB Mirroring: Failover is now not a problem

XML datatype & much more new features which gives alltogether a powerful weapon to deal with db's

Give example for binary datatype non binary dataty...?

What is the Disadvantage of StoredProcedure?
--------------------------------------------------------------------------------

When a procedure is created a execution plan is created, proc is executed according to that execution plan. When we modify the proc it also follow this execution plan.
When index is rebuild, new index structure is created, but the proc follows the old execution plan because it was compiled, so there is a chance of unexpected result.
For this there is need to recompile the proc.

Explain about Dead-locks in Sql Server database?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.


The binary datatype is used for storing the images in the database.the non binary datatypes are integer,varchar etc

Dirty Pages: Data that has been modified and Stored in the buffer cache and has not yet been written in to the hard disk. (Transaction Logs)

Dirty reads: Reading the data that is actually read by Transaction2 which was modified by Transaction 1 and Transaction1 was not yet committed and above that, if Transaction 1 is rolled back then transaction2 has read data that never was modified or exists... This is dirty read.

Explain real time situation where you would use clustered and Non-clustered Indexes?
clustered index is used and applied only and primary keys

For example if a table student is created consider here in this table student id is the primary key so clustered index is applied on the student id column.

Coming to non-clustered index consider other table employee table here employee id is the primary

Key and it is foreign key for student table and there may be more than one foreign key so non-clusterd index is applied on foreign key and candidate keys.
What is XMLA in SQL Server 2005
I would like to know whether it uses SOAP or XML.

XML for Analysis (XMLA) is a Simple Object Access Protocol (SOAP)-based XML protocol, designed specifically for universal data access to any standard multidimensional data source residing on the Web. XMLA also eliminates the need to deploy a client component that exposes Component Object Model (COM) or Microsoft .NET Framework interfaces. XMLA is optimized for the Internet, when round trips to the server are expensive in terms of time and resources, and when stateful connections to a data source can limit user connections on the server.

XMLA is the native protocol for Microsoft SQL Server 2005 Analysis Services (SSAS), used for all interaction between a client application and an instance of Analysis Services. Analysis Services fully supports XML for Analysis 1.1, and also provides extensions to support metadata management, session management, and locking capabilities. Both Analysis Management Objects (AMO) and ADOMD.NET use the XMLA protocol when communicating with an instance of Analysis Services.

SQL Server data in XML Format
How to display the table data in XML format using SQL Server.




There are Many Differences like

1.Replication Management: Replication monitor is way usless when compared to the 2000 interface (lots and lots of screens and can not even handle pull subscriptions entirely )

2. Debugging stored Procedures. From Query Analyzer we could do it in 2000. 2005 requires visual studio ...


3.Script Generation: Although on SP2 they fixed some issues there are *still* role membership problems ... In 2000 it worked "beautifully" since day one.


Let me Compare Afew Features of SQL 2000 Vs 2005

SQL Server 2000

1.Security:Owner = Schema, hard to remove old users at times

2.Encryption:No options built in, expensive third party options with proprietary skills required to implement properly.

3.High Availability:Clustering or Log Shipping require Enterprise Edition. Expensive hardware

4.Scalability:Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.



SQL Server 2005

1.Security:Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.

2.Encryption:Encryption and key management build in.

3.High Availability:Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.

4.Scalability:4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.

How is the error handling in stored proc of T-SQL ?

In SQL Server 2000
We can check by @@Error<>0 i.e. error.
ex:
IF @@Error<>0
BEGIN

ROLLBACK TRANSACTIOn

RETURN

END

ELSE

BEGIN

COMMIT TRANSACTION

RETURN

END

Note: You need to check @@Error at every DML statement and since it is Global Variable, So it reset every time.

In SQL Server 2005
We can check by using try catch block
ex:
BEGIN TRY

BEGIN TRANSACTION
/*statement*/

COMMIT TRANSACTION

END TRY
BEGIN CATCH

IF @@TRANCOUNT>0
ROLLBACK TRAN

END CATCH

Note: You can check the following functions in catch block
a)ERROR_NUMBER() returns no of the error
b)ERROR_SEVERITY() returns severity of the error
c)ERROR_STATE() returns error state no
d)ERROR_PROCEDURE() returns name of stored procedure
e)ERROR_LINE() returns line no.
f)ERROR_Message() returns the complete text of the message

Abt Clustered indexes:
V can hav only one clustered index in a table, by default primary key is termed as clustered index.
V can have 249 non-clustered indexes in a table.

What is the difference between windows authentication and sql server authentication?

Windows authentication will uses the credentials of the currently logged user to login in to the sql server database. Your application need not to supply the user name and password.
for SQL server authentication we have to supply the sql database user name and password. That will be used to get the privileges and rights of the user.

How do you find which procedures are being used in database?
--------------------------------------------------------------------------------

select object_name from user_objects where object_type = 'PROCEDURE'

select proc_name from user_procedures;

What is the difference between "stored procedure" and "dynamic SQL"

Stored Procedures are compiled and runs on the server. dynamic SQL are not compiled and runs on client.

Stored procedures are stored in data base in complied form. In dynamic sql the statements are dynamically constructed at run time.

Can you call procedure in functions?

CREATE OR REPLACE PROCEDURE TEST_PROC(V_NUM OUT NUMBER) AS
BEGIN
V_NUM := 10;
END;
/



CREATE OR REPLACE FUNCTION TEST_FUNC RETURN NUMBER AS
V_NUM NUMBER;
BEGIN
TEST_PROC(V_NUM);
RETURN V_NUM;
END;
/


SELECT TEST_FUNC() FROM DUAL;