SQL Server Developer/DBA Interview Question

1.

What happens with this code in SQL Server 2005?

Create Table Test(col varchar(10))
GO
Insert into Test
Select 1
union Select 2
union Select 3
union Select 4
union Select 5
union Select 6
union Select 7
UNION Select 'A'
union Select 'B'
union Select 'C'
union Select 'D'

Select Col
From (Select Col
From Test
Where Isnumeric(Col)=1) X
Where Col Between 1 and 6

Ans.

Conversion failed when converting the varchar value 'A' to data type int.

Explanation:

When the SELECT statement is compiled for the INSERT, the first value is implicitly taken as an INT. In the UNIONs, as each SELECT is added, they must match this data type. Even though the table holds characters, the SELECT statement fails in converting the 'A' to an int. So no data is inserted into the table.

The implicit conversions are being handled according to data type precedence.

2.

In which editions of SQL Server 2008 is C2 Audit Mode supported

Ans.

Correct answer:

Express, Workgroup, Standard, and Enterprise

Explanation:

C2 auditing is supported in all editions of SQL Server (except Compact).

Ref: Features Supported by the Editions of SQL Server 2008 - http://msdn.microsoft.com/en-us/library/cc645993.aspx

3.

Can you mirror a SQL Server 2008 database in compatability mode 90?

Ans. Correct answer:

Yes

Explanation:

Database Mirroring doesn't concern itself with checking if the compatibility mode. You can mirror a database in any mode if you are on the SQL Server 2008 platform.

Ref: Database Mirroring Overview - http://msdn.microsoft.com/en-us/library/ms189852.aspx

4.

In SQL Server 2008 Reporting Services, what is the best practice recommendation for the service account?

Ans. Correct answer:

Domain Account

Explanation:

You always want to use the least privileged account that you can for a service account, or any reason. In Reporting Services 2008, the ASP.NET account for IIS is no longer used. Instead, you want to pick and account and a domain account, with minimum privileges is preferred as it is not a shared account in case it is attacked.

Ref: Configuring the Report Server Service Account - http://msdn.microsoft.com/en-us/library/ms160340.aspx

5.

In SQL Server 2008, which editions can act as publishers in snapshot replication?

Ans. orrect answer:

Standard, and Enterprise

Explanation:

Only the Enterprise and Standard Editions of SQL Server 2008 can act as publishers.

Ref: Features Supported by the Editions of SQL Server 2008 - http://msdn.microsoft.com/en-us/library/cc645993.aspx

6.

In SQL Server 2008 Data Mining, when you create a mining model, you create a structure first. Can you use nested tables in your structures

Ans. Correct answer:

Yes

Explanation:

A data mining structure can use nested tables from any source.

Ref: Mining Structures - http://msdn.microsoft.com/en-us/library/ms174757.aspx

7.

Given this code,

DECLARE @val int; 

SET @val = -1

CREATE TABLE #empty (val int)

which statement(s) will result in @val being NULL? (select all that apply)

Ans.

Correct answer:

SET @val = NULL, SELECT @val = (SELECT val FROM #empty)

Explanation:

There are two statements above that will result in @val being NULL. From MSDN: "If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL."

Ref: SELECT @local_variable - http://msdn.microsoft.com/en-us/library/ms187330.aspx

8.

What happens when you delete the MDX script CALCULATE in a cube of a project of BIDS?

Ans.

Correct answer:

Nothing at BIDS, but the cube will lose the aggregations if processed.

Explanation:

The CALCULATE statement is automatically included as the first statement in a cube's MDX script when you create a cube by using Business Intelligence. The CALCULATE statement tells each cell in the cube to aggregate from lower granularity cells. So, if you delete this MDX script, the cube will never be processed.

Ref: Calculate - http://msdn.microsoft.com/en-us/library/ms144939.aspx

9.

In which editions does automatic page repair work with Database Mirroring?

Ans.

Correct answer:

Enterprise edition only

Explanation:

Automatic page repair is an Enterprise only feature. Database mirroring works in both standard and enterprise editions.

Ref: Automatic Page Repair During a Database Mirroring Session - http://msdn.microsoft.com/en-us/library/bb677167.aspx
Features Supported by Editions of SQL Server 2008 - http://msdn.microsoft.com/en-us/library/cc645993.aspx

10.

The output of the below will be:

select newid()
go
select newsequentialid()
go

Ans.

Correct answer:

a unique GUID for the first statement, an error for the second statement.

Explanation:

NEWSEQUENTIALID() cannot be referenced in queries, therefore you get a GUID from the first statement, and an error from the second.

Ref: NEWSEQUENTIALID() - http://m.podshow.com/media/16930/episodes/145626/sqlservercentralmp3-145626-03-03-2009.mp3

11.

If you set Master database as read only in SQL Server 2000 by executing

ALTER DATABASE [Master]

SET READ_ONLY

Who can write on or modify the Master database?

Ans.

Correct answer:

Option 'READ_ONLY' cannot be set in database 'master'

Explanation:

The master database is the exception to read-only, and this cannot be set. The documentation says it can, but this has not been shown in testing.

For reference see ALTER DATABASE - http://msdn.microsoft.com/en-us/library/aa275464(SQL.80).aspx

12.

Can we use RAND() inside a scalar User Defined Function ?

Ans.

Correct answer:

No

Explanation:

No, Because it's a inbuilt function and SQL SERVR does not allows to use that function in side any UDF. Try this example:

CREATE FUNCTION [dbo].[test]()

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @A VARCHAR(100)

SET @A = CONVERT(VARCHAR(100),RAND())

RETURN @A

END;

The error message shown below appears when you try to use an non-deterministic operator or an operator with side-effects within a user-defined function.

Msg 443, Level 16, State 1, Procedure udfMyFunc, Line 7

Invalid use of side-effecting or time-dependent operator in 'rand' within a function.

Ref: User-Defined Functions - http://msdn.microsoft.com/en-us/library/aa175085(SQL.80).aspx

13.

What does '-' indicate in SQL Server?

Ans.

Correct answer:

subtract, negative and except operators.

Explanation:

'-' is used for subtract, negative and except operations. The first two are T-SQL operations and the last is an MDX operator.

Ref: Negative - http://msdn.microsoft.com/en-us/library/aa276853(SQL.80).aspx
Subtract - http://msdn.microsoft.com/en-us/library/aa276853(SQL.80).aspx
Except - http://msdn.microsoft.com/en-us/library/ms145497(sql.90).aspx

14.

In SQL Server 2008, which editions of SQL Server can act as subscribers for transactional replication?

Ans.

Correct answer:

Express, Workgroup, Standard, and Enterprise

Explanation:

All editions of SQL Server can act as subscribers for transactional replication. Only Standard and Enterprise can act as publishers.

Ref: Features Supported by the Editions of SQL Server 2008 - http://msdn.microsoft.com/en-us/library/cc645993.aspx

15.

The quest is very simple, and please, "do not refer to BOL".

In SQL Server 2008 what is the range of severity levels?

Ans.

Correct answer:

0 to 25

Explanation:

Answer is very simple and state forward; the correct answer is 0 to 24. 0-10 return informational messages, 11-15 return user-correctable errors, 17-19 are not user-correctable and should be reported to the sysadmin. 20-25 are fatal errors.

Please refer the following link for further detail. http://msdn.microsoft.com/en-us/library/ms164086.aspx.

16.

select @@CPU_BUSY

returns what?

Ans,

Correct answer:

an integer value

Explanation:

Returns the time that SQL Server has spent working since it was last started, which is an integer value. The value is the number of ticks since SQL Server started.

17.

In SQL Server 2008, if you have a Reporting Services report server using the Express edition, what edition of database should you have?

Ans.

Correct answer:

Express only

Explanation:

The editions matter with Reporting Services. The database for an Express Edition reporting server should be an Express edition database.

Ref: Creating a Report Server Database - http://msdn.microsoft.com/en-us/library/ms157285.aspx

18.

declare @a int, @b int
select @a = 568788
select @b = right(left(@a,3),2)%10
select @b

The output of the above is:

Ans.

Correct answer:

8

Explanation:

This short piece of code shows how right, left and % operators work. The LEFT operator will implicitly convert the value to a character and then return 568. Next the RIGHT operator will take that and return 68. Finally the MODULO operator will return the 8 (68/10 is 6 R8).

Ref: % - http://msdn.microsoft.com/en-us/library/ms190279.aspx
LEFT - http://msdn.microsoft.com/en-us/library/ms177601.aspx
RIGHT - http://msdn.microsoft.com/en-us/library/ms177601.aspx

19.

What can be the output for this code? (select all that apply)

select NULL,'manoj'

union

select Null,'1'

union

select NULL,'manoj'

Ans.

Correct answer:

NULL, 1 and NULL,'manoj' (2 rows), NULL,'manoj' and NULL, 1 (2 rows)

Explanation:

The result is 2 rows, with UNION removing the duplicate rows being removed. Ordering can be either way and is not guaranteed without an Order by.

Ref: Union - http://msdn.microsoft.com/en-us/library/ms180026.aspx

20.

create table #t (id int,ch char)

insert into #t values (2,'A')

insert into #t values (5,'b')

insert into #t values (1,'$')

insert into #t values (3,'*')

insert into #t values (7,'@')

insert into #t values (4,'&')

insert into #t values (6,'!')

create nonclustered index nc_t on #t(id)

select top 1 ch from #t

drop table #t

The output of the above is:

Ans.

Correct answer:

A

Explanation:

Since the index created is a nonclustered one, when "top 1" is queried for; we get the value 'A' and not '$'. The data remains in the natural order it was inserted in. This is not guaranteed, but on a small table that fits on one page, this will be the case.

Note that this works in the current version of the optimizer, but it is subject to change. DO NOT use TOP WITHOUT an Order By clause.

Ref: Top - http://msdn.microsoft.com/en-us/library/ms189463.aspx

21.

In SQL Server 2008, which editions support Plan freezing for plan guides?

Ans.

Correct answer:

Standard, and Enterprise

Explanation:

Both Standard and Enterprise support plan freezing.

Ref: - http://msdn.microsoft.com/en-us/library/cc645993.aspx

22.

Cryptography in SQL Server is essentially made of: (Check all that apply)

Ans.

Correct answer:

Symmetric key processing, Asymmetric key processing, One-way hashing, A hybrid approach of two or more of the above.

Explanation:

Cryptography in SQL Server is essentially made of:Symmetric key processing, Asymmetric key processing, One-way hashing or a hybrid approach of one or more of the above.

Ref: Encryption Hierarchy - http://msdn.microsoft.com/en-us/library/ms189586.aspx

23.

What would be the out put of the below script?

CREATE TABLE #myTable (column1 text);

GO

INSERT INTO #myTable VALUES ('test');

GO

SELECT BINARY_CHECKSUM(*) from #myTable;

GO

DROP TABLE #myTable

GO

Ans.

Correct answer:

Error in binarychecksum. There are no comparable columns in the binarychecksum input.

Explanation:

BINARY_CHECKSUM ignores columns of noncomparable data types in its computation. Noncomparable data types include text, ntext, image, cursor, xml, and noncomparable common language runtime (CLR) user-defined types.

Ref: Binary_checksum - http://msdn.microsoft.com/en-us/library/ms173784.aspx

24.

declare @p table(ID int,lastname varchar(20),firstname varchar(20))

insert into @p values (4,'Mouse','Mickey')

insert into @p values (3,'Smith','Jay')

insert into @p values (5,'Smith','Anna')

insert into @p values (1,'Pratt','Sue')

insert into @p values (2,'Mouse','Minnie')

select ID from @p where ((lastname like 'Smith%') and (firstname like '%a%') and ID in ())

The output of the above is:

Ans.

Correct answer:

Error: Incorrect syntax near ')'.

Explanation:

The above errors out with "Incorrect syntax near ')'." as there is nothing specified in the condition 'ID in'. You must include some values in the IN clause for the query to be valid.

Ref: IN - http://msdn.microsoft.com/en-us/library/ms177682.aspx

30.

DECLARE @abc TABLE

(

A numeric(13,8),

B numeric(13,8),

C numeric(13,8)

)

INSERT INTO @abc VALUES(1000,100,0)

UPDATE @abc

SET C= (A * B)

Question : What happened Now?

Ans.

Correct answer:

Error: Arithmetic overflow error converting numeric to data type numeric.

Explanation:

The total length of the data is 13 and decimal is 8. The balance of space allows up to 5 digits before the decimal point. When we update the field we exceed that, so an error occurred. In this case 1000 * 100 goes above 5 characters before the decimal.

Ref: Precision, Scale, and Length - http://msdn.microsoft.com/en-us/library/ms190476.aspx

31.

Which of the following dynamic queries will run successfully, meaning display the result as TEST):

DECLARE @query AS VARCHAR(20)
DECLARE @variable AS VARCHAR(5)

SELECT @variable = 'TEST'
--1
SELECT @query = 'SELECT ''TEST'' '
EXEC (@query)
--2
SELECT @query = 'SELECT ' + '''TEST'''
EXEC (@query)
--3
SELECT @query = 'SELECT ' + @variable
EXEC (@query)
--4
SELECT @query = 'SELECT ' + '''' + @variable + ''''
EXEC (@query)
--5
SELECT @query = 'SELECT ' + ''' + @variable + '''
EXEC (@query)

Ans.

Correct answer:

1,2,4

Explanation:

1,2 and 4 will run successfully. 3 and 5 will fail as the dynamic queries built for 3 and 5 will have syntax error.

32.

Are the following statements equivalent?

Statement 1

SELECT *

FROM (

  SELECT 1 col1 UNION

  SELECT 2 UNION

  SELECT 3

) T1

LEFT JOIN (

  SELECT 1 col1 UNION

  SELECT 3

) T2

 ON t1.col1 = t2.col1

WHERE t2.col1 IS NULL

Statement 2

SELECT *

FROM (

  SELECT 1 col1 UNION

  SELECT 2 UNION

  SELECT 3

) T1

LEFT JOIN (

  SELECT 1 col1 UNION

  SELECT 3

) T2

 ON t1.col1 = t2.col1

 AND t2.col1 IS NULL

Ans.

Correct answer:

The statements are not equivalent.

Explanation:

From Books Online: When SQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join. Although the physical execution of various joins uses many different optimizations, the logical sequence is:

The join conditions in the FROM clause are applied.
The join conditions and search conditions from the WHERE clause are applied.
The search conditions from the HAVING clause are applied.

This sequence can sometimes influence the results of the query if conditions are moved between the FROM and WHERE clauses.

Ref: Join fundamentals - http://msdn.microsoft.com/en-us/library/aa213233(SQL.80).aspx Statement 2 is equivalent to the following query which more clearly shows the empty set that is T2. SELECT * FROM ( SELECT 1 col1 UNION SELECT 2 UNION SELECT 3 ) T1 LEFT JOIN ( SELECT * FROM ( SELECT 1 col1 UNION SELECT 3 ) S WHERE S.col1 IS NULL ) T2 ON t1.col1 = t2.col1

33.

create table t1 (i int)

insert into t1 values (NULL)

insert into t1 values (0)

insert into t1 values (null)

select max(i) from t1

drop table t1

The output of the above is:

Ans.

Correct answer:

The output 0 along with the message - Warning: Null value is eliminated by an aggregate or other SET operation.

Explanation:

Null values are eliminated as there is use of aggregate 'max' in the above query.

Ref: Aggregate Functions - http://msdn.microsoft.com/en-us/library/aa258901(SQL.80).aspx

34.

select the correct output of the print statament below...

declare @x varchar(50)
declare @y decimal(34,4)
declare @z varchar(50)

set @x=50
set @y='10.00'
set @z=@x+@y

print @z

Ans.

Correct answer:

60.0000

Explanation:

The variable @y has 4 decimals, so when the addition occurs, the result will be converted to the same level of precision before implicitly cast as a varchar.

Ref: Data Type Precedence - http://msdn.microsoft.com/en-us/library/ms190309.aspx
) CAST and CONVERT - http://msdn.microsoft.com/en-us/library/ms187928.aspx

35.

In SQL Server 2005, the following code is executed

SELECT CONVERT(datetime,-1)

UNION

SELECT CONVERT(datetime,0)+CONVERT(datetime,-1)

UNION

SELECT CONVERT(datetime,-1)-CONVERT(datetime,0)

UNION

SELECT CONVERT(datetime,0)-CONVERT(datetime,1)

UNION

SELECT CONVERT(datetime,0)-1

What happens?

Ans.

Correct answer:

(1 row(s) affected)

Explanation:

The integer value "0" corresponds to datetime value "1900-01-01 00:00:00" and integer value "-1" to "1900-01-01 00:00:00" minus one day ("1899-12-31 00:00:00.000"). The adding/subtracting of the datetime values is the same as adding their difference to the base value "1900-01-01 00:00:00".
"1900-01-01 00:00:00" minus "1899-12-31 00:00:00.000" becomes
"1900-01-01"+0(days) minus "1900-01-01"-1(days)
"1900-01-01"+(0(days)-1(days))
resulting in "1899-12-31".
All the select statements are variants of subtracting one day from "1900-01-01" so all select's return the same result. Because of the DISTINCT behaviour of the UNION only one row is returned.

36.

Which system view in SQL Server 2008 that replaces the sys.sql_dependencies view in the older versions?

Ans.

Correct answer:

sys.sql_expression_dependencies

Explanation:

sys.sql_expression_dependencies is the view in SQL Server 2008 that replaces the sys.sql_dependencies view in the older versions. Sys.sql_dependencies is still available, but has been deprecated.

Ref: sys.sql_expression_dependencies - http://msdn.microsoft.com/en-us/library/bb677315.aspx
sys.sql_dependencies - http://msdn.microsoft.com/en-us/library/ms174402.aspx

37.

Can this code create a database...?

Create Database Mytestdb

on

(

 name ='Mytest_1',

 Filename= 'C:\MyTEST.mp3',

 size = 5000 KB

)

log on

(

name ='MyTest_2',

 Filename= 'C:\mssql\MYTEST.doc',

 size= 512 kb

)

Ans.

Correct answer:

Yes

Explanation:

Yes, this code will create a database. The file extensions are mdf, ldf, and ndf by ercommendation. This is not enforced by SQL Server. However it is a poor idea to change them as you will confuse others working on your system.

Ref: Files and Filegroups Architecture - http://msdn.microsoft.com/en-us/library/ms179316.aspx

38.

The size of the transaction log files are physically reduced when: (Check all that apply)

Ans.

Correct answer:

A DBCC SHRINKDATABASE statement is executed., An autoshrink operation occurs. , A DBCC SHRINKFILE statement referencing a log file is executed.

Explanation:

Truncating the log or performing a backup will not physically shrink the log. Only a shrinking operation will do that.

Ref: Shrinking the Transaction Log - http://msdn.microsoft.com/en-us/library/aa174524(SQL.80).aspx
Truncating the Transaction Log - http://msdn.microsoft.com/en-us/library/aa174538(SQL.80).aspx

39.

Which of the following statements would you use to allow read-only access to the data and objects held within the mirrored copy of a mirrored database, without breaking the mirroring session?

Ans.

Correct answer:

CREATE DATABASE... AS SNAPSHOT OF...

Explanation:

It is possible to create a database snapshot on the mirrored copy of a mirrored database - the snapshot behaves as a read-only copy of the mirrored database as of the time that the snapshot was created.

The snapshot provides an ideal place to run resource-intensive reports where a certain amount of data latency is acceptable. If desired, a SQL Agent job can be created to drop any existing snapshot and create a new one at the required frequency, snapshots generally take seconds to drop and create.

There is no way to directly access the mirrored copy of the database.

Ref: Database Snapshots - http://msdn.microsoft.com/en-us/library/ms175158.aspx

40.

SQL Server 2008 has which new features/keys for database encryption (check all that apply)

Ans.

Correct answer:

Transparent data encryption (TDE) , database encryption key (DEK)

Explanation:

TDE and the DEK are new in SQL Server 2008. SDE does not exist and the DML was introduced in SQL Server 2005.

Reference: SQL Server TDE Encryption - http://msdn.microsoft.com/en-us/library/cc278098.aspx#_Toc189384672

41.

Which all are valid System Databases in SQL Server 2005/2008? Select all that apply

Ans,

Correct answer:

master, msdb, model, tempdb, resource

Explanation:

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata http://technet.microsoft.com/en-us/library/ms178028.aspx

42.

What is the maximum number of instances supported by SQL Server 2008 Enterprise edition and Standard edition respectively? (32bit)

Ans.

Correct answer:

50 and 50

Explanation:

SQL Server 2008 Enterprise Edition and Standard Edition support 50 instances. SQL Server 2008 Workgroup Edition supports 16 instances.

Ref: Maximum Capacity Specifications for SQL Server - http://msdn.microsoft.com/en-us/library/ms143432.aspx

43.

Which of the following system views give information on whether the stored procedures/ functions have the ansi_nulls and quoted_identifier set to ON or OFF? (Choose all that apply)

Ans.

Correct answer:

sys.sql_modules, sys.system_sql_modules

Explanation:

sys.sql_modules and sys.system_sql_modules system views have columns of bit type named uses_ansi_nulls and uses_quoted_identifier which indicate whether ansi nulls and quoted identifier is set to ON or OFF for that object.

Ref: sys.sql_modules - http://msdn.microsoft.com/en-us/library/ms175081.aspx
sys.system_sql_modules - http://msdn.microsoft.com/en-us/library/ms188034.aspx

44.

What is the output of the following query ?

DECLARE @str VARCHAR(50)

SELECT @str = '**************************************************'

SELECT LEN(CAST(@str AS VARCHAR));

Ans.

Correct answer:

30

Explanation:

While casting a varchar or nvarchar value without specifying the size it takes the default size of 30 characters.

Ref: CAST - http://msdn.microsoft.com/en-us/library/ms187928.aspx

45.

Which of the following queries will run on AdventureWorks database without any error:

--Query 1

SELECT MAX(NationalIDNumber),SUM(VacationHours) 

FROM HumanResources.Employee

--Query 2

SELECT MAX(NationalIDNumber),SUM(VacationHours) 

FROM HumanResources.Employee

GROUP BY ManagerID

--Query 3

SELECT EmployeeId,MAX(NationalIDNumber),SUM(VacationHours) 

FROM HumanResources.Employee

GROUP BY ManagerID

--Query 4

SELECT MAX(NationalIDNumber),SUM(VacationHours) 

FROM HumanResources.Employee

ORDER BY ManagerID

Ans.

Correct answer:

1 and 2 only

Explanation:

Fields included in a select list of a query with aggregate functions shall either be included in aggregate function or should appear in 'GROUP BY' clause. For a query having agregate function in its select list,a field cannot appear in an 'ORDER BY' clause unless it is contained in either an aggregate function or the GROUP BY clause.

Ref: GROUP BY - http://msdn.microsoft.com/en-us/library/ms177673(SQL.90).aspx

46.

What is MD2?

Ans.

Correct answer:

The CryptoAPI algorithm name for the MD2 hash algorithm.

Explanation:

MD2 refers to the MD2 algorithm: (MD2) A hashing algorithm that creates a 128-bit hash value. MD2 was optimized for use with 8-bit computers. CryptoAPI references this algorithm by its type (CALG_MD2), name (MAC), and class (ALG_CLASS_HASH). MD2 was developed by RSA Data Security, Inc.

Ref: The MD2 Message-Digest Algorithm - http://www.rfc-archive.org/getrfc.php?rfc=1319

47.

Can an object be renamed in SQL server?

Ans.

Correct answer:

yes, any object can be renamed

Explanation:

With the help of sp_rename, we can rename an object in the current database. A word of caution: it is better not to use this function since the existing name may be referenced by various scripts or stored procedures or even hard-coded at certain locations. Dropping the object and creating it with a different name is an option.

Ref: sp_rename - http://msdn.microsoft.com/en-us/library/ms188351.aspx

48.

Which cryptographic function is characterized by 'encryption with a public key' and 'signing with a private key'?

Ans.

Correct answer:

Asymmetric key processing

Explanation:

Asymmetric key processing is characterized by 'encryption with a public key' and 'signing with a private key' i.e.a key pair is used here, one of which is used for encryption (public key) and the other, for decryption (private key).

Ref: Encryption Hierarchy - http://msdn.microsoft.com/en-us/library/ms189586.aspx

49.

What is the output for the query below?

CREATE TABLE E (eid int, mid int, en char(10) )

INSERT E SELECT 1,2,'Vyas'
INSERT E SELECT 2,3,'Mohan'
INSERT E SELECT 3,NULL,'Shobha'
INSERT E SELECT 4,2,'Shridhar'
INSERT E SELECT 5,2,'Sourabh'

SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid

order by t1.en

Ans.

Correct answer:

Row 1 - Mohan Shobha | Row 2 - Shridhar Mohan | Row 3 - Sourabh Mohan | Row 4 - Vyas Mohan

Explanation:

A self join is just like any other join, except that two instances of the same table will be joined in the query. In this case, the old style join is used and produces an inner join. Note that this is not the recommended type of join. It is recommended that you use the specific keywords INNER JOIN between the two instances of the table and include an ON clause.

Ref: Using Self-Joins - http://msdn.microsoft.com/en-us/library/ms177490.aspx

50.

What is the name of the data type we call nvarchar?

Ans.

Correct answer:

national character varying

Explanation:

'national character varying' is the name of the data type we call nvarchar.

Ref: nchar and nvarchar - http://msdn.microsoft.com/en-us/library/ms186939.aspx

51.

What are the different ways in which we can execute a SSIS Package, Please select all applicable answers!

Ans.

Correct answer:

The dtexec command prompt utility (dtexec.exe), The Execute Package Utility (dtexecui.exe), BIDS, A SQL Server Agent job

Explanation:

You can use all the mentioned methods to execute a SSIS Package, 1. BIDS , 2.The dtexec command prompt utility (dtexec.exe), 3. The Execute Package Utility (dtexecui.exe), 4. A SQL Server Agent job.

Ref: Running Packages - http://msdn.microsoft.com/en-us/library/ms141708.aspx

52.

declare @xml xml,@hdoc int

set @xml = '





 2

 Y





 3

 N



'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

SELECT distinct class FROM OPENXML (@hdoc, '/test/value',1)

 WITH (id int,

 name varchar(10),

 class int,

 flag char(1))

exec sp_xml_removedocument @hdoc

The output of the above code is:

Ans.

Correct answer:

NULL

Explanation:

The value of 1 in (@hdoc, '/test/value',1) helps retrieve only the id and name data for /test/value and not the class and flag data. Therefore, we get a NULL as the answer. To get the class values as expected, change 1 to 2.

Ref: OPENXML - http://msdn.microsoft.com/en-us/library/ms186918.aspx

52.

Which of the below view(s) is materialized i.e. computed and stored?

Ans.

Correct answer:

Indexed Views

Explanation:

An indexed view is materialized, meaning it is physically stored in the database.

Reference: Types of Views - http://msdn.microsoft.com/en-us/library/ms190426.aspx

53.

Which is the data type that provides a storage structure for spatial data that is defined by coordinates on an arbitrary plane?

Ans.

Correct answer:

geometry

Explanation:

The geometry data type provides a storage structure for spatial data that is defined by coordinates on an arbitrary plane.

Ref: Getting Started with the geometry Data Type - http://msdn.microsoft.com/en-us/library/bb895270.aspx

54.

I have a variable that will hold one character of data and will never be a null or an empty space. Which of the following is the best data type to declare that variable?

Ans.

Correct answer:

Either char(1) or varchar(1) may be used.

Explanation:

Either char(1) or varchar(1) may be used. Both would allocate 1 byte of space which would be used. [If there's no data in the variable, varchar would be a better option to use, since char would have allocated and 'used' (padding with space), the space allocated].

This wouldn't actually matter here, because the space in consideration is just 1 byte. Just thought of this question considering larger scenarios where data types need to be chosen with care.

55.

True or False: the FILESTREAM feature must be separately configured, both at the Windows and the SQL Server level.

Ans.

Correct answer:

true

Explanation:

Since Filestream uses the file system in conjunction with SQL Server, both Windows and SQL Server must be configured to allow this.

Reference: http://msdn.microsoft.com/en-us/library/cc949109.aspx

56.

Which of the following is true about indexed views? (select all that apply)

Ans.

Correct answer:

The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement., The user that executes the CREATE INDEX statement must be the view owner.

Explanation:

The view cannot contain text or image data and the person executing the statement must own the view. The other statements are reversed. If you are in 80 or earlier compatibility mode, you need to set ARITHABORT to ON, and the IGNORE_DUP_KEY setting must be OFF.

Reference: Indexed Views -

57.

What will this return?

SELECT SUBSTRING('123456', 0, 3)

Ans.

Correct answer:

12

Explanation:

The string returned by SUBSTRING is truncated if Start + Length -1 is greater than the length of the Expression string.

Ref: SUBSTRING - http://msdn.microsoft.com/en-us/library/ms187748.aspx

58.

On your SQL Server 2005 you have used the system function sys.dm_db_index_physical_stats and note a value of avg_fragmentation_in_percent for a particular index is >5% and <=30%. You have determined that for this index the ALLOW_PAGE_LOCKS is not set to OFF. Using Microsoft's BOL recomended guidlines to remedy this fragmentation you would use which method?

Ans.

Correct answer:

Use Alter Index with the Reorganize clause

Explanation:

The recommended way to handle this is with the reorganize option.

Ref: - Reorganizing and Rebuilding Indexes - http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).asp

59.

There is database server. After you tried to logon to the server you have this error:

"Logon Failed for login DOMAINNAME\Administrator' due to trigger execution

You have changed the database context to 'master' and changed the language setting to 'us_english' How should you correct this problem so that all valid users could connect again to SQL Server?

Ans.

Correct answer:

Login using DAC. Disable specific or all trigger on all server

Explanation:

You cannot login due to trigger execution, so the only way you can login is DAC. (i.e. sqlcmd -A -E). To restore login connectivity you should disable the trigger that prevents you from logging in. However if you do not know which trigger is it, disable all server triggers also a solution.

Ref: Disable Trigger - http://msdn.microsoft.com/en-us/library/ms189748.aspx Restoring database server or master database is not right solution, where simple disable could help. Resource database is critical to SQL Server, if you delete it your database server will not work at all.

60.

Which of the following standard Microsoft data mining algorithms uses these two algorithms?

1) ARTXP algorithm, introduced in SQL Server 2005.

2) ARIMA algorithm, introduced in SQL Server 2008.

Ans.

Correct answer:

Time Series

Explanation:

Time Series is the standard Microsoft data mining algorithm that includes the algorithms ARTXP and ARIMA.

Reference: Microsoft Time Series Algorithm Technical Reference - http://msdn.microsoft.com/en-us/library/bb677216.aspx

61.

In SQL Server 2005/2008 32bit what is the Microsoft recommended limit for databases that can be mirrored per instance?

Ans.

Correct answer:

10

Explanation:

On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.

Ref: http://msdn.microsoft.com/en-us/library/ms366349.aspx

62.

What is the result of the following query

create table employe

(

id_num int identity(1,1),

fname varchar(20),

lname varchar(20),

)

insert into employe(id_num,fname,lname) values(12,'george','kelly')

Ans.

Correct answer:

An error: Cannot insert explicit value for identity column in table 'employe ' when IDENTITY_INSERT is not set ON

Explanation:

As when we have defined identity column on any column it provide the automatic incremental number for that column,but even after that we want to insert the explicit value we have to set SET_IDENTITY_INSERT on for the table.

Ref: Identity - http://msdn.microsoft.com/en-us/library/ms186775.aspx

63.

The Meta Data Services component of SQL Server 2000 has which system database for default repository storage?

Ans.

Correct answer:

msdb

Explanation:

The msdb system database is the default storage location for meta data services.

Ref: Meta Data Services Repository - http://msdn.microsoft.com/en-us/library/aa972283(SQL.80).aspx

64.

What is a geocentric latitude?

Ans.

Correct answer:

The angle between a ray from the center of the ellipsoid to a point and the plane of the equator.

Explanation:

The geocentric latitude measures an angle that corresponds to a vertical angle. The measurement is between the plane of the equator and a ray drawn to the point from the center of the ellipsoid modeling the Earth. This angle can be from -90 degrees (South pole) to +90 degrees (North pole).

Ref: Latitude - http://en.wikipedia.org/wiki/Latitude
Introduction to Spatial Coordinate Systems - http://msdn.microsoft.com/en-us/library/cc749633.aspx

65.

Which of the following has to be set ON while creating an index on a view? (Check all that apply)

Ans.

Correct answer:

ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER

Explanation:

All the 5 of the above must be set ON while creating index on a view.

Ref: Creating Index Views - http://msdn.microsoft.com/en-us/library/ms191432.aspx

66.

In SQL Server, It is possible to change the data type of a column in a table after it has been created.

Ans.

Correct answer:

True, the data type can be changed for an existing column.

Explanation:

I have a table zzztest with a column 'k' of data type int. This can be changed to say, char(2) by running in the below code:

alter table zzztest

alter column k char(2)

Ref: ALTER TABLE- http://msdn.microsoft.com/en-us/library/ms190273.aspx

67

What is the code name used for SQL Server 2005 by Microsoft?

Ans.

Correct answer:

Yukon

Explanation:

The code name used for SQL Server 2005 by Microsoft is "Yukon".

Ref: A variety of references refer to both. Here's one blog on MSDN

68.

The CIO wants you to create a database snapshot of the Sales database following the upgrade from SQL 2005 to 2008 and you say "I can't, unless the company runs the database on my laptop." What SQL version do you have on your laptop that the company lacks?

Ans.

Correct answer:

You have either Developer or Enterprise

Explanation:

The only versions that support snapshots in SQL Server 2008 are the Enterprise and Developer versions. Your boss has you working on a Standard or a Workgroup edition of SQL Server, but your laptop has the Developer version.

Ref: Database Snapshots - http://msdn.microsoft.com/en-us/library/ms175158.aspx#

69.

A SQL Server database has this table:

CREATE TABLE MyTable
(Column1 int NOT NULL,
Column2 char(200) NOT NULL,
Column3 varchar(40) NOT NULL,
PRIMARY KEY NONCLUSTERED(Column1));

No constraints or indexes other then those indicated by the CREATE TABLE statement exist for this table.

The table holds a million rows. Since Column2 is no longer used, you decide to free up some space by dropping it:

ALTER TABLE MyTable
DROP COLUMN Column2;

However, running sp_spaceused shows that no space is freed at all. Which of the following strategies can you use to reclaim the space freed up by removing Column2?

(Check all that apply)

Ans.

Correct answer:

Copy the existing data to a new table. Drop MyTable, create it again (without Column2), than copy the data back in., Copy the existing data to a new table. Drop MyTable, than rename the new table to MyTable., Create a clustered index on Column1 of MyTable, then drop this index again.

Explanation:

Dropping a column is a metadata-only operation. That means that the pages are not rebuilt. This makes the operation very fast, but also means the space is not reclaimed. And since metadata is the same for all pages, the same amount of space will also be lost on pages that are later allocated to the table.

SQL Server will only free up this space if it has to rebuild all the pages of the table. For most tables, that is the case when you rebuild indexes (using ALTER INDEX .. REBUILD, or using the deprecated DBCC DBREINDEX option). But not on this table, since it is a heap. DBCC DBREINDEX will rebuild the nonclustered index on Column1, but not the actual data stored in the heap.

Creating a clustered index does involve a rebuild of all the data pages, so this will also change the metadata and free up the space previously taken by Column2.

Even though TRUNCATE TABLE does remove all the data pages and could theoretically be a great time to change the metadata, it doesn't work this way (as can easily be verified by testing).

Creating a new table does of course free up the space that was required to store Column2 (though it temporarily uses extra space to store the duplicated copy).

Ref: ALTER TABLE - http://msdn.microsoft.com/en-us/library/ms190273.aspx

69.

What happens when executing this code?

Create table sales

(

COL1 varchar(20) not null,

COL2 varchar(600) not null,

COL3 varchar(400) not null,

date datetime not null,

Unique clustered (COL2, COL3)

)

Ans.

Correct answer:

Table and index created with warning message

Explanation:

A unique index in SQL Server enforces a unique constraint, and, as the warning message says, index keys are restricted to a maximum of 900 bytes. During the insertion of data, the index rolls back the insert when sum of the length of COL2 and length of Col3 exceeds the 900.

Ref: CREATE INDEX - http://msdn.microsoft.com/en-us/library/ms188783.aspx

70.

Which of these trace flags, when ON; return the deadlock information from the error log?

Check all that apply.

Ans.

Correct answer:

1204, 1222

Explanation:

1204 and 1222 are the trace flags that help in tracing deadlock information.

Ref: Trace Flags - http://msdn.microsoft.com/en-us/library/ms188396.aspx

71.

Which executable file runs behind Management Studio SQL Server 2005?

Ans.

Correct answer:

SqlWb.exe

Explanation:

The executables for the client management applications are:

• mmc.exe for SQL Server 2000
• SqlWb.exe for 2005
• Ssms.exe in 2008

72.

Which minimal authority does the SQLaccount need have to be able to use the SSMS (SQL2008) registered servers of a Central Management Server ? ( given you connect to the Central Management Server using SQLAuthentication)

This user needs to be granted:

Connect to the server + :

Ans.

Correct answer:

member of msdb - ServerGroupReaderRole

Explanation:

You can connect to a Central Management Server using SQL authentication. Connecting by using a registered server of a Central Management Server is only supported for Windows Authentication. (BOL "Administering Multiple Servers Using Central Management Servers ")

New msdb db roles have been implemented to be able to manage (ServerGroupAdministratorRole) or use (ServerGroupReaderRole) this new feature of SQL Server Management Studio 2008.

73.

What will happen when the following script is executed?

use master

go

create database testdb

go

create proc sp_test as

select db_name()

go

sp_test

go

use testdb

go

sp_test

go

use master

go

drop database testdb

drop proc sp_test

go

Ans.

Correct answer:

Two resultsets containing "master" and "testdb".

Explanation:

When a procedure beginning with "sp_" is created in the master database it becomes accessible from all other databases. It will execute within the context of the current database.

Note: It is not recommended that you create user stored procedures that begin with "sp_"

Ref: Executing Stored Procedures - http://msdn.microsoft.com/en-us/library/ms189915.aspx

74.

On SQL Server 2000, I have a "Test" database with simple recovery model. What will be output of this code? (No BOL/testing please)

dump transaction Test with no_log

Ans.

Correct answer:

The command(s) completed successfully.

Explanation:

You can truncate the log on simple recovery model, but you cant take a backup of the log.

Ref: http://msdn.microsoft.com/en-us/library/aa174538(SQL.80).aspx

75.

Introduced in SQL Server 2005, the suspect_pages table records pages considered to be suspect. Select all statements which are true as applied to this table.

Ans.

Correct answer:

A 823 error such as a disk error., A 824 error - other than bad check sum or a torn page (logical error.), A Torn page, A page with a bad check sum, During a DBCC CHECKDB operation, During a DBCC CHECKDB, REPAIR_ALLOW_DATA_LOSS operation

Explanation:

All of these are recorded.

Ref: Understanding and Managing the suspect_pages Table (BOL January 2009) - http://msdn.microsoft.com/en-us/library/ms191301.aspx
Suspect_pages (Transact-SQL) (BOL January 2009) - http://msdn.microsoft.com/en-us/library/ms174425.aspx

76.

Take the time to think about this one - don't just run it in Query Analyzer. If the following yields true:

select isnumeric('0,.0')

What is the output of:

select convert(int,'0,.0')

Ans.

Correct answer:

Error converting data type varchar to numeric.

Explanation:

The correct answer is Error converting data type varchar to numeric. Even though IsNumeric reports true, the value cannot be converted to a numeric value.

Ref: IsNumeric - http://msdn.microsoft.com/en-us/library/ms186272.aspx

77.

What is returned?

create procedure dbo.testProc

@testVar varchar(4)

as

select @testVar

GO

exec testProc 'This is a test string'

GO

Ans.

Correct answer:

This

Explanation:

Stored procedure calls will silently truncate strings.

78.

In SQL Server 2005, what tool should you use in rebuilding the master database?

Ans.

Correct answer:

Setup.exe

Explanation:

Rebuild.exe is no longer available in SQL Server 2005. To rebuild master database, you may use setup.exe.

Ref: How to: Rebuild the Master Database for SQL Server 2005 - http://msdn.microsoft.com/en-us/library/ms143269.aspx

79.

Your SQL Backup folder is C:\SQLToBeBackedUpToTape\AdventureWorks on a SQL Server 2008 instance and backup compression is set to '1', not the default '0'. You run:

BACKUP DATABASE [AdventureWorks] 

  TO DISK = N'C:\SQLTOBEBACKEDUPTOTAPE\AdventureWorks\AdventureWorks_compressed.bak' 

  WITH NOFORMAT, INIT, 

  NAME = N'AdventureWorks-Full Database Backup', 

  SKIP, NOREWIND, NOUNLOAD, STATS = 10

The statement to restore this databse is:

Ans.

Correct answer:

RESTORE DATABASE [AdventureWorks] FROM DISK = N'C:\SQLTOBEBACKEDUPTOTAPE\AdventureWorks\AdventureWorks_compressed.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10;

Explanation:

The second statement works because there is no assumption regarding the backup of the tail of the log which is inherent in the first [BACKUP LOG WITH NORECOVERY]. The last statement is actually a backup statement with the word BACKUP replacing the word RESTORE.

Ref: Restore - http://msdn.microsoft.com/en-us/library/ms186858.aspx

80.

Top of Form

Unknown NULLs What is the result of the following? Declare @i int --Test #1 If @i is null Print 'i is null' Else Print 'i is not null' --Test #2 if @i = 0 Print 'i = 0' Else Print 'i <> 0' --Test #3 If not @i = 0 Print 'i <> 0' Else Print 'i = 0' ANSWERS: A) i is null i <> 0 i = 0 B) i is null i <> 0 i <> 0 C) i is null i = 0 i = 0 D) i is null i = 0 i <> 0

Bottom of Form

Ans.

Correct answer:

A when SET ANSI_NULLS is ON, B when SET ANSI_NULLS is OFF

Explanation:

Microsoft says, "When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN." When SET ANSI_NULLS is ON, Test #3 evaluates as NOT(UNKNOWN), thereby failing to evaluate to TRUE.

81.

The vardecimal storage format was introduced in which version?

Ans.

Correct answer:

SQL Server 2005 SP2

Explanation:

In Service Pack 2, SQL Server 2005 adds a new storage format for numeric and decimal datatypes called vardecimal. Vardecimal is a variable-length representation for decimal types that can save unused bytes in every instance of the row. The biggest amount of savings come from cases where the decimal definition is large (like decimal(38,6)) but the values stored are small (like a value of 0.0) or there is a large number of repeated values or data is sparsely populated. http://technet.microsoft.com/en-us/library/cc917696.aspx

82.

Check all that apply that are true about DBCC CHECKDB.

Ans.

Correct answer:

It uses tempdb space., It has a flag not to run against nonclustered indexes.

Explanation:

DBCC CHECKDB against tempdb doesn't do allocation or catalog checks as snapshot creation is not available against tempdb. DBCC CHECKDB against master fails only when an internal snapshot cannot be created. DBCC CHECKDB ignores disabled indexes.

Ref: DBCC CHECKDB - http://msdn.microsoft.com/en-us/library/ms176064.aspx

83.

Which RAID levels provide protection from a single disk failure?

Ans.

Correct answer:

RAID1, RAID 5, RAID 10

Explanation:

Raid levels 1 (mirroring), 5 (striping with parity) and 10 (striping and mirroring) provide protected from a disk failure. RAID level 0, striping by itself, provides no protections from disk failures.

Ref: RAID Levels and SQL Server - http://msdn.microsoft.com/en-us/library/ms190764(SQL.90).aspx

84.

What is the data type conversion from char to datetime?

Ans.

Correct answer:

implicit

Explanation:

The data type conversion from a char to a datetime is implicit and doesn't require the use of explicit CAST or CONVERT functions. The char must be in a valid date format or it will cause an error, but an implicit conversion takes place.

Ref: Data Type Conversion - http://msdn.microsoft.com/en-us/library/ms191530.aspx#_datetime

85.

declare @c int,@d int

select @c = 211

select @c = cast(convert(varchar(2),convert(varchar(3),@c)) as float) * 0.5

select @c

The output of this code is:

Ans.

Correct answer:

10

Explanation:

The value 211 is 'convert'ed to that of two digits i.e. 21; which, multiplied by 0.5 gives 10 (and not 10.5) as @c is of int datatype.

Ref: CAST and CONVERT - http://msdn.microsoft.com/en-us/library/ms187928.aspx

86.

SQL 2008 We have 2 tables with the following data:

Table Depts

DeptID  DeptName         Manager

1       Human Resources  Margheim

2       Sales            Byham

5       Manufacturing    Brewer

-------

Table Delta

DeptID  Deptname         Manager

1       Human Resources  Margheim

2       Sales            Erickson

6       Production       Jones

We then execute the following T-SQL statements

MERGE dbo.Depts AS d

USING dbo.Delta AS dd

ON (d.DeptID = dd.DeptID)

WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName

    THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName

WHEN NOT MATCHED THEN

    INSERT (DeptID, DeptName, Manager)

        VALUES (dd.DeptID, dd.DeptName, dd.Manager)

WHEN NOT MATCHED BY SOURCE THEN

    DELETE

OUTPUT $action,

       inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,

       inserted.Manager AS SourceManager,

       deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,

       deleted.Manager AS TargetManager; 

What actions were executed in/on the rows in the Depts table?

Ans.

Correct answer:

Deptid 1 no changes,DeptId 2 data was updated,Deptid 5 was deleted,A new Deptid of 6 was added

Explanation:

This statement will no make any changes to rows with a Deptid = 1, update those rows with Deptid = 2, and delete those rows with DeptID = 5. Deptid = 6 was added. Inserting, Updating, and Deleting Data by Using MERGE [url]ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/952595a6-cf1d-4ff5-8927-66f9090cf79d.htm[/url]

87.

In SQL Server 2005, which of these data types have the highest and lowest precedence respectively?

Ans.

Correct answer:

user-defined data types, binary

Explanation:

"user-defined data types" have the highest precedence whereas "binary" is the data type with lowest precedence.

Ref: Data Type Precedence - http://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx

88.

In SQL Server 2008 what response would you recieve if you executed the following block of statements?

DECLARE @i AS INT = 100,@msg AS VARCHAR(5)= '11',@Date AS DATE=GETDATE()

SET @i +=25; SET @i *=10; SET @i /= 1000; SET @msg *=3;

SELECT @i AS '@i', @msg AS '@Msg', @Date AS 'Date'

Ans.,

Correct answer:

@i=1 @msg=33 the date of the day the code is executed

Explanation:

@i has been declared as INTEGER, if declared as DECIMAL(10,3) the answer for @i would have been 1.250. In previous versions, you cannot assign a default to a variable when you declare it.

Introduction to New T-SQL Programmability Features in SQL Server 2008 http://technet.microsoft.com/en-us/library/cc721270.aspx

89.

Given the following code in SQL 2005:

DECLARE @Temp TABLE (TempValue varchar(10))

INSERT INTO @Temp (TempValue) VALUES ('[]')

SELECT 1, TempValue FROM @Temp WHERE TempValue LIKE '![!]' ESCAPE '!'

UNION

SELECT 2, TempValue FROM @Temp WHERE TempValue LIKE '[[][]]'

UNION

SELECT 3, TempValue FROM @Temp WHERE TempValue LIKE '[[]]'

which output(s) will appear? (select all that apply)

Ans.,

Correct answer:

1, [], 3, []

Explanation:

The SQL LIKE operator allows very complex expressions, but when searching for a wildcard literal, it seems best to use the ESCAPE clause for clarification and readability. When I was researching this, I was quite surprised to see #3 appear in the output.

Ref: LIKE - http://msdn.microsoft.com/en-us/library/ms179859.aspx

90.

Can you create a snapshot of the msdb database in SQL Server 2005?

Ans.

Correct answer:

Yes

Explanation:

You can create a snapshot of msdb. The other system databases (master, model, tempdb) cannot have snapshots created on them, but msdb allows snapshots.

Ref: Limitations on Database Snapshots - http://msdn.microsoft.com/en-us/library/ms189940(SQL.90).aspx

91.

In SQL Server 2005, sysobjects is a:

Ans.

Correct answer:

system view

Explanation:

Actually it's sys.sysobjects and in V2005 this is a system view.

Ref: sysobjects - http://msdn.microsoft.com/en-us/library/ms177596(SQL.90).aspx

92.

declare @b table (num int,i char(1))

insert into @b values(5,'N')

insert into @b values(4,'N')

insert into @b values(6,'N')

insert into @b values(1,'Y')

insert into @b values(2,'N')

insert into @b values(3,'Y')

select i from @b group by i

The output of the above code would be:

Ans.

Correct answer:

Two rows: N, Y

Explanation:

The group by clause would cause the 'i's to be grouped under 'N's and 'Y's.

Ref: GROUP BY - http://msdn.microsoft.com/en-us/library/ms177673.aspx

93.

The following are examples of __________________ with database scope:

• User
• Role
• Application role
• Assembly
• Message Type
• Route
• Service
• Remote Service Binding
• Fulltext Catalog
• Certificate
• Asymmetric Key
• Symmetric Key
• Contract
• Schema

Ans.

Correct answer:

Securables

Explanation:

Not to be confused with Principals, this is a list of Securables with database scope. While a 'user' is also a Principal, once it is created in the database it is then considered to be a Securable just like a table is a Securable.

Ref: Securables - http://msdn.microsoft.com/en-us/library/ms190401.aspx

94.

True/False: The following statements are equivalent

CREATE TABLE TestTable (LName VARCHAR(20));

CREATE TABLE TestTable (LName character varying(20));

Ans.

Correct answer:

True

Explanation:

True. The use of data type synonyms is supported for DDL statements.

Reference: Data Type Synonyms - http://msdn.microsoft.com/en-us/library/ms177566(SQL.90).aspx

95.

What is the value of @@Error after executing this statement:

EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT' 

Ans.

Correct answer:

0

Explanation:

sp_rename returns a value indicating success or not. That is what must be used for error detection, not @@error or XACT_ABORT (which doesn't abort on error for sp_rename). A TRY/CATCH block CAN also be used where available.

Ref: sp_rename - http://msdn.microsoft.com/en-us/library/ms188351(SQL.90).aspx

96.

Suppose you have this table, holding millions of rows of data:

CREATE TABLE Table1

 (PKCol int NOT NULL PRIMARY KEY,

 CharCol nchar(100) NOT NULL);

Since most data inserted into CharCol is less than 100 characters (average 30 characters), you decide to change it to a varying length column:

ALTER TABLE Table1

ALTER COLUMN CharCol nvarchar(100) NOT NULL;

What effect will this have on the space taken by the table?

Ans.

Correct answer:

The space used will increase by approximately 100%

Explanation:

The storage area for row data is divided into two main areas: one for fixed-length data, and one for varying-length data. Changing a column from fixed length to varying length will add it to the varying length area, but it will not make the space previously reserved for it in the fixed length area available for reuse. That means that the space previously taken by the fixed length column is still taken, but the space requireed for the varying length column is added. The only way to reclaim this space is to rebuild the clustered index or to copy the data to a new table and drop the old one.

Fixed length columns are padded with spaces to their declared length. The ALTER COLUMN will not remove those trailing spaces, so all values will remain 100 characters long. That's why the table grows by 100% and not by just 30%. To get rid of the trailing spaces, you have to run an update statement like

UPDATE Table1 SET CharCol = RTRIM(CharCol);

97.

create table t (id int,ch char,na varchar(20),flag bool)

insert into t values (2,'A','jack','Y')

insert into t values (5,'b','amy','N')

insert into t values (1,'$','adams','N')

insert into t values (3,'*','anna','Y')

insert into t values (7,'@','rose','N')

insert into t values (4,'&','smith','Y')

insert into t values (6,'!','sue','Y')

create nonclustered index nc_t on t(id,ch,na)

-- query 1

select na from t where ch = '!' 

-- query 2

select na from t where id = 6 and ch = '!'

-- query 3

select na from t where ch = '!' and id = 6

-- query 4

select na from t where flag = 'Y' and id = 6 and ch = '!'

drop table t

Which of the above 'select' statements performs a table scan?

Ans.

Correct answer:

4

Explanation:

The column 'flag' is not a part of the defined non clustered index nc_t and so, the select statement 4 performs a table scan to get the data.

98.

The sp_browsereplcmds procedure is used in ...

Ans.

Correct answer:

Transactional replication

Explanation:

Returns a result set in a readable version of the replicated commands stored in the distribution database, and is used as a diagnostic tool. This stored procedure is executed at the Distributor on the distribution database. sp_browsereplcmds is used in transactional replication.

Ref: sp_browserreplcmds - http://msdn.microsoft.com/en-us/library/ms176109.aspx http://msdn.microsoft.com/en-us/library/ms176109.aspx

99.

The SWITCH operator of the ALTER TABLE TSQL command is used to:

Ans.

Correct answer:

To assign a table as a partition to an already existing partitioned table., To switch a partition from one partitioned table to another., For reassigning a partition to form a single table.

Explanation:

The SWITCH operator is an extremely fast, scalable and non-table-blocking way to add or remove large numbers of identically structured table data in partitioned sets. There are several restrictions to SWITCHing partitions, such as identical fields, data types, foreign keys, and indexes. See the referenced URL for more detail.

Ref: Transferring Data Efficiently by Using Partition Switching - http://msdn.microsoft.com/en-us/library/ms191160.aspx

100.

What will be the output of this query?

DECLARE @str  NVARCHAR(10)

SET @str = '1, 2, 3, '

SET @str = LEFT(@str, LEN(@str)- 2)

SELECT @str

Ans.

Correct answer:

'1, 2, ' (space after comma)

Explanation:

The LEN function in SQL removes the trailing spaces when calculating the length of the string. Therefore the LEN returns a 8 and -2 is 6, so only the first 6 characters are returned.

Ref: LEN - http://msdn.microsoft.com/en-us/library/ms190329.aspx

101.

When was SQLServerCentral.com site founded?

Ans,

Correct answer:

March 2001

Explanation:

I love this site... I learned a lot from it... special thanx to Steve Jones.

From the About Us History: This site was founded in March of 2001 by six DBAs that were all interested in writing about their work and helping others work with SQL Server. After a year and a half, three founders were left trying to build the fantastic site you see today. This site was the brainchild of SQL Server MVP Brian Knight who brought the rest of us together. Andy Warren and Steve Jones have worked for nearly six years growing the site and giving back to the community. Jon Winder, Leon Platt, and Sean Burke are the other three founders. In November 2006, Andy and Brian made the decision to move on to other SQL Server ventures and the site was sold to Red Gate software http://www.sqlservercentral.com/About/AboutUs

102.

Which of the following queries generates an error when executed?

DECLARE @x VARCHAR(10), @y VARCHAR(10)

SET @y = ''



SELECT @x/0 -- query 1

SELECT @x/2 -- query 2

SELECT @y/0 -- query 3

SELECT @y/2 -- query 4

Ans.

Correct answer:

3

Explanation:

Queries 1 and 2 return NULL since @x is undefined. Query 4 returns 0 since the string '' is implicitly converted to 0. Query 3 returns a divide by 0 error.

103.

What type of language is the SELECT command in SQL Server?

Ans.

Correct answer:

DML

Explanation:

The SELECT statement is a Data Manipulation Language commend, not a Data Definition Language command.

Ref: Data Manipulation Language - http://msdn.microsoft.com/en-us/library/ms177591(SQL.90).aspx

104.

declare @var char, @c char

set @c = 'I'

set @var = 'E'

if (@c <> 'I') OR (@var <> 'E') 

 begin

   select 'not OK'

 end

else

 select 'OK'

The output of the above will be:

Ans.

Correct answer:

OK

Explanation:

The value returned here is 'OK'. This can be confusing to follow the logic of negative comparisons and this is one of the reasons to rely on checking 'equality' rather than 'inequality'.

105

It is possible to turn off the auto creation of statistics.

Ans.

Correct answer:

true

Explanation:

True, it is possible to turn off the auto creation of statistics in a database. One of the database properties is "Auto Create Statistics" and this can be set to on or off. If off, you can manually update statistics as needed.

ReF: Setting Database Options - http://msdn.microsoft.com/en-us/library/ms190249.aspx

106.

Are newid() and newsequentialid() the same?

Ans.

Correct answer:

No

Explanation:

NEWID() returns a unique GUID value each time whereas NEWSEQUENTIALID() has a predictable output. Also, NEWSEQUENTIALID() cannot be referenced in queries.

Ref: NEWID() - http://msdn.microsoft.com/en-us/library/ms190348(SQL.90).aspx
NEWSEQUENTIALID() - http://msdn.microsoft.com/en-us/library/ms189786(SQL.90).aspx

107.

What do the entries with the prefix of _WA_Sys in the system views indicate?

Ans.

Correct answer:

Auto created Statistics

Explanation:

Auto created Statistics have the prefix of _WA_Sys in the system views.

Ref: Statistics Properties - http://msdn.microsoft.com/en-us/library/ms181293(SQL.90).aspx

108.

What results from this query?

Select 1.8 E,1.8E,1.Eight,3+8E8Eight,

 3+8 E8E,'Six'+'Seven' 'Eight','Six'+'Seven'+'Eight',

 8.8Eight,'Six''Seven' 'Eight'

Ans.

Correct answer:

Result set with nine columns, five of which have names starting with "E".

Explanation:

The syntax parser is forgiving of missed spaces between a literal value and it's column alias. Thus, "1.X" is interpreted as a floating point value of 1.0 with column alias "X", "1Y" is interpreted as an integer value of 1 with column alias of "Y", "1E3Z" is interpreted as floating point value of 1 times ten to the third power with a column alias of "Z". Some of the other column values and/or aliases in this QotD were constructed using string concatenation, where two strings are joined by use of a plus sign (+), and one literal was built with an embedded single-quote (') which was intended to fool the unwary into thinking there were two strings where there's actually just the one (Six'Seven).

109.

SELECT @@DBTS

returns: (select all that apply)

Ans.

Correct answer:

the value of the current timestamp data type for the current database., the last-used timestamp value of the current database.

Explanation:

@@DBTS is a system function that returns the current/ last-used timestamp of the current database. The return type of this is varbinary.

Ref: @@DBTS - http://msdn.microsoft.com/en-us/library/ms187366(SQL.90).aspx

110.

Can you include a From clause in an update statement in TSQL?

i.e

update table

 set a1.coulmn=a2.column<

 from table1 as a1, table2 as a2

 where a1.colum1 = a2.column2

Ans.

Correct answer:

YES

Explanation:

You can include a FROM clause in an UPDATE statement.

Ref: UPDATE - http://msdn.microsoft.com/en-us/library/ms177523.aspx

111.

The default size in bytes for @@TEXTSIZE is:

Ans.

Correct answer:

4096

Explanation:

The default size in bytes for @@TEXTSIZE is: 4096.

Ref: @@TEXTSIZE - http://msdn.microsoft.com/en-us/library/ms177687(SQL.90).aspx

112.

True or FalsE: when your database is in Bulk-Logged recovery mode, you cannot participate in 'BEGIN TRANSACTION....COMMIT TRANSACTION' operations

Ans.

Correct answer:

False

Explanation:

"Contrary to the SQL Server myths, a minimally logged operation can participate in a transaction. Because all changes in allocation structures are tracked, it is possible to roll back minimally logged operations."

Ref: The Data Loading Performance Guide - http://msdn.microsoft.com/en-us/library/dd425070.aspx

113.

Which of the below standard Microsoft data mining algorithms is characterized by input, hidden and output layers?

Ans.

Correct answer:

Neural Network

Explanation:

The standard Microsoft data mining algorithm that is characterized by input, hidden and output layers is the "Microsoft Neural Network Algorithm".

Ref: Microsoft Neural Network Algorithm - http://msdn.microsoft.com/en-us/library/ms174941.aspx

114.

SQL Server indexes are organized in a B-tree structure. The "B" in B-tree stands for what?

Ans.

Correct answer:

Balanced

Explanation:

B-tree structures are balanced so that the depth from the root to any leaf in the index is the same.

Ref: Clustered Index Structures - http://msdn.microsoft.com/en-us/library/ms177443.aspx
Tables - http://technet.microsoft.com/en-us/library/cc917720.aspx

115.

SQL Server 2008 introduces several extensions (subclauses), to the GROUP BY clause. Select all those that apply.

Ans.

Correct answer:

GROUPING SETS, CUBE, ROLLUP, GROUPING_ID

Explanation:

http://msdn.microsoft.com/en-us/library/cc721270.aspx Introduction to New T-SQL Programmability Features in SQL Server 2008 SQL Server Technical Article Writer: Itzik Ben-Gan Specifically this paragraph "SQL Server 2008 introduces several extensions to the GROUP BY clause that enable you to define multiple groupings in the same query. These extensions are: the GROUPING SETS, CUBE, and ROLLUP subclauses of the GROUP BY clause and the GROUPING_ID function. The new extensions are standard and should not be confused with the older, nonstandard CUBE and ROLLUP options."

116.

create table test2(id int,entrycode varchar(10),entryname varchar(30))

insert into test2 values (1,'BE','BENGALOORU')

insert into test2 values (2,' CH','CHENNAI')

insert into test2 values (3,' DE','DELHI')

insert into test2 values (4,'MU ','MUMBAI')

select entryname from test2 where entrycode in ('BE','CH','DE','MU')

drop table test2

The output of the above is:

Ans.

Correct answer:

BENGALOORU, MUMBAI

Explanation:

The entrycodes having 'space(s)' on their left are not brought in by the above select query, but the one with a space on the right (i.e.e MUMBAI) is. This is because the space is counted as a character that needs to be matched.

Ref: IN - http://msdn.microsoft.com/en-us/library/ms177682(SQL.90).aspx

117.

What will be the output of below script? (Please don't run the query in Query Analyzer).

CREATE TABLE T

(

 ID int NOT NULL,

 Code varchar(10) NULL

)

INSERT INTO T

SELECT 1,'AAAA'

GO

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

BEGIN Tran t1

 DROP TABLE T

ROLLBACK Tran

GO

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

SELECT * FROM T

Ans.

Correct answer:

1, 'AAAA'

Explanation:

The batch will rollback the DDL statement, so the table is not dropped. For more information please go through: SQL Server 2005 Books Online, Transaction Log Logical Architecture - http://msdn.microsoft.com/en-us/library/aa174536(SQL.80).aspx

118.

What is the output of the statement below:

UPDATE a

SET Name = 'Assis Marques new'

OUTPUT Inserted.Name, Deleted.Name

FROM tb_User a

WHERE Name = 'Assis Marques'

Assume you have a SQLServer2005 database and that the table "tb_User" contains the user 'Assis Marques'

Ans.

Correct answer:

A RecordSet with 2 columns: 'Assis Marques new', 'Assis Marques'.

Explanation:

The OUTPUT clause returns data from an insert/update/delete statement that you can use to audit activity and access the inserted and deleted tables.

Ref: OUTPUT - http://msdn.microsoft.com/en-us/library/ms177564(SQL.90).aspx

119.

declare @str varchar(max)

select @str = replicate('#',10000) + replicate(cast('#' as varchar(max)),8000)+ '#' + '#' + '#'

select len(@str)

The output of the above code in SQL Server 2005 is:

Ans.

Correct answer:

16003

Explanation:

The first 'replicate' gives 8000 #s, the second one gives 8000 and then, there are three more, so the len comes to 16003.

Ref: Replicate - http://msdn.microsoft.com/en-us/library/ms174383.aspx

120.

Consider the following statements:

create table #t (i int identity(1,1) not null, c varchar(5) not null)

insert into #t (c) select 'one'

truncate table #t

insert into #t (c) select 'two'

delete #t

insert into #t (c) select 'three'

select * from #t

What will be the result of the select query?

Ans.

Correct answer:

2 , three

Explanation:

TRUNCATE TABLE resets the counter for an identity column to its seed value, while DELETE retains the counter.

Ref: Truncate table - http://msdn.microsoft.com/en-us/library/ms177570.aspx

121.

True of False: Common language runtime (CLR) execution is supported under lightweight pooling.

Ans.

Correct answer:

False

Explanation:

False. These options are mutually exclusive. You must disable lightweight pooling in order for CLR execution to function.

Ref: Enabling CLR Integration - http://msdn.microsoft.com/en-us/library/ms131048(SQL.90).aspx

122.

The Entity-Attribute-Value (EAV) model is also known as

Ans.

Correct answer:

Open Schema

Explanation:

As opposed to Row Modeling, where an object has a fixed set of attributes (columns), the Entity-Attribute-Value model provides more flexibility in defining objects. EAV is also known as object-attribute-value model or soft-coded values.

Ref: http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

123.

declare @a int,@b int,@c bit,@d int

select @a = 120

select @b = 30

select @d = 40

select @c = (@a & @b)| @d

select @c

The output of the above is:

Ans,.

Correct answer:

1

Explanation:

Bit is a datatype that can only take values 1, 0 or NULL.

Ref: Datatypes - http://msdn.microsoft.com/en-us/library/ms187752(SQL.90).aspx
Bitwise AND - http://msdn.microsoft.com/en-us/library/ms174965(SQL.90).aspx
Bitwise OR - http://msdn.microsoft.com/en-us/library/ms186714(SQL.90).aspx

124.

To display a report containing several SQL Server statistics, including connection attempts, run:

Ans.

Correct answer:

sp_monitor

Explanation:

From SQL Server Books online: SQL Server keeps track, through a series of functions, of how much work it has done. Executing sp_monitor displays the current values returned by these functions and shows how much they have changed since the last time the procedure was run.

For each column, the statistic is printed in the form number(number)-number% or number(number). The first number refers to the number of seconds (for cpu_busy, io_busy, and idle) or the total number (for the other variables) since SQL Server was restarted. The number in parentheses refers to the number of seconds or total number since the last time sp_monitor was run. The percentage is the percentage of time since sp_monitor was last run. For example, if the report shows cpu_busy as 4250(215)-68%, the CPU has been busy 4250 seconds since SQL Server was last started up, 215 seconds since sp_monitor was last run, and 68 percent of the total time since sp_monitor was last run.

Ref: sp_monitor - http://msdn.microsoft.com/en-us/library/ms188912(SQL.90).aspx

125.

We need to define a column that will contain externally provided identifiers. Their length will vary from seven to ten characters, all English letters, both upper and lower case mixed with numeric digits, with an even distribution of lengths.

Which of these data types will be more efficient?

Ans.

Correct answer:

char(10)

Explanation:

A varchar (or nvarchar) column carries two bytes of overhead to hold the actual length of the data. Since the average size of the data is 8.5 characters -- (7+8+9+10) / 4 --, a varchar data type would take an average of 10.5 bytes for each entry. A column defined as char(10) needs just 10 bytes. As the column will hold only English language letters and digits, we don't need the additional bytes required by the national character data types. They use two bytes for each character, so would take 20 bytes for nchar or 19 bytes for nvarchar (average lenghth 8.5 doubled for national characters, then add two bytes to hold the length).

126.

In database ABC, you have two file groups - FG1 and FG2 each with one data file. FG1 is the default file group for the database. What will be the result on executing the below SQL?

CREATE TABLE [dbo].[tblA](
[intID] [int] IDENTITY(1,69) NOT NULL,
[strChar] [char](100) NULL,
[dteCreate] [datetime] NOT NULL,
CONSTRAINT [PK_tblA] PRIMARY KEY CLUSTERED
(
[intID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
) ON [FG2]

GO

Ans.

Correct answer:

Table and index created in created on FG1

Explanation:

Since the constraint is a clustered primary key, the table will be created on the same file group (FG1) as that of the constraint.

Ref: CREATE TABLE - http://msdn.microsoft.com/en-us/library/aa258255(SQL.80).aspx

127.

Which system database is set to the full recovery model by default?

Ans.

Correct answer:

model

Explanation:

The Model database is created with the full recovery model by default. By having the model database set to full recovery, this causes newly created databases by default to be set to full recovery.

MCTS SQL Server 2005 Implementation & Maintenance Study Guide (Exam 70-431) Ch. 8 page 341 by Tom Carpenter

128.

What is the ANSI SQL equivalent to GETDATE()?

Ans.

Correct answer:

CURRENT_TIMESTAMP

Explanation:

The ANSI SQL equivalent to GETDATE() is CURRENT_TIMESTAMP. GETDATE() is proprietary to the SQL Server system. If you would like to ensure to write most standards compatible and portable code, you should use CURRENT_TIMESTAMP.

Ref: CURRENT_TIMESTAMP - http://msdn.microsoft.com/en-us/library/ms188751.aspx

129.

What are the valid return values for the following code: (select all that apply)

SELECT IS_MEMBER('DOMAIN\SuperUsers')

Ans.

Correct answer:

1, 0, NULL

Explanation:

The IS_MEMBER() function returns an INT datatype and a NULL if either the group or role is invalid.

Reference: IS_MEMBER -

130.

SELECT @@DBTS

returns: (select all that apply)

Ans.

Correct answer:

the value of the current timestamp data type for the current database., the last-used timestamp value of the current database.

Explanation:

@@DBTS is a system function that returns the current/ last-used timestamp of the current database. The return type of this is varbinary.

Ref: @@DBTS - http://msdn.microsoft.com/en-us/library/ms187366(SQL.90).aspx

132.

Can you include a From clause in an update statement in TSQL?

i.e

update table

 set a1.coulmn=a2.column<

 from table1 as a1, table2 as a2

 where a1.colum1 = a2.column2

Ans.

Correct answer:

YES

Explanation:

You can include a FROM clause in an UPDATE statement.

Ref: UPDATE - http://msdn.microsoft.com/en-us/library/ms177523.aspx

133.