Microsoft SQL Server FAQ's Part-II

Q1. Topic- Exponent Engima
Executing the following script on SQL Server 2005 :



declare @a bigint, @b bigint
set @a = 16
select sqrt(@a^@a - 17)



Returns "A domain error occurred." Why?


A. 16^16 = 2^64 which is out of bigint's range
B. @b-17 is negative, which is invalid for an exponent
C. Sqrt() only allows non-negative inputs
D. This is a known bug in Sql Server 2005


Ans: C

Explanation: @a^@a is a bitwise XOR, not an exponent operation. Any value XOR'd with itself is always 0. Subtracting 17 makes it -17, which is a negative number, and an invlaid input for the SQRT() function.



Ref: ^ (Bitwise XOR) - http://msdn.microsoft.com/en-us/library/ms190277.aspx


Q2. Topic- Error Logs
Which of the following is written in the SQL Server error log? (select all that apply)


A. Current Build Number
B. Server Process ID
C. Startup Parameters
D. Licensing Mode


Ans: A, B, C



Explanation: All of these except the licensing mode are written to the SQL Server error log.


Q3. Topic- Partition Functions
What would this code return?


CREATE PARTITION FUNCTION PartitionFunction1 ( int )
AS
RANGE FOR VALUES (10, 100, 1000) ;
GO
SELECT $PARTITION.PartitionFunction1 (10) ;
GO

A. 0
B. 1
C. Sntax error enar $Partition

Ans: B


Explanation: The Partition$ function returns the partition number from a partition function setup and the range value passed in.


Q4. Topic- Data Compression
When compressing indexes with Page compression, how are non-leaf level pages compressed?

A. They are not compressed
B. They are row compressed
C. They are page compressed


Ans: B


Explanation: Non leaf level pages in indexes are only compressed with row compression, even when page compression is enabled for the index.

Ref: Page Compression Implementation - http://msdn.microsoft.com/en-us/library/cc280464(SQL.100).aspx


Q5. Topic- Count
What will be output of below query?


DECLARE @t table
( id int identity(1,1),
value varchar(50)
)



INSERT INTO @t values(NULL)
INSERT INTO @t values(NULL)
INSERT INTO @t values(NULL)
INSERT INTO @t values('1')
INSERT INTO @t values('2')
INSERT INTO @t values('3')
SELECT count(id), count(value) FROM @t

A. 3 3
B. 3 6
C. 6 3
D. 6 6


Ans: C



Explanation: The Count aggregate does not include NULL values, so the count will be 3 rows for the 2nd column.

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



Q6.Topic- GROUP BY ALL
NOTE. This question is about what the RTM version of SQL Server 2008 will do, based on currently publicly available documentation and other information as of July 1st, 2008.


Suppose that you have just installed the RTM version of SQL Server 2008, with all options and settings at the default values. You now create and populate a table as follows:


CREATE TABLE DailySales
(Region tinyint NOT NULL,
SalesDate date NOT NULL,
SalesTotal decimal(9,2) NOT NULL,
PRIMARY KEY (Region, SalesDate));
INSERT INTO DailySales (Region, SalesDate, SalesTotal)
VALUES (1, '20080601', 153), (1, '20080602', 208),
(1, '20080603', 905), (1, '20080604', 12),
(2, '20080603', 19), (2, '20080605', 195),
(3, '20080605', 75), (3, '20080606', 230),
(3, '20080607', 900), (3, '20080608', 405);

What result should be returned when you run this query:


SELECT Region, COUNT(*) AS NumSales, AVG(SalesTotal) AS AvgValue
FROM DailySales
WHERE SalesDate < '20080605' GROUP BY ALL Region HAVING COUNT(*) <2;


A. Msg 156, Level 15, State 1, Line 17 - Incorrect syntax near the keyword 'ALL'.


B. Msg 4147, Level 15, State 1, Line 17. The query uses GROUP BY ALL. To run this query without modification, please set the compatibility level for current database


C. to 90, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query. In future versions of SQL Server, GROUP BY ALL will not be supported even in backward-compatibility modes.


D. Results of Region = 1, 2 - NumSales = 4, 1 - AvgValue = 319.5, 19


E. Results of Region = 2, 3 - NumSales = 1, 0 - AvgValue = 19, NULL


F. Results of Region = 2 - NumSales = 1 - AvgValue = 19


Ans: D


Explanation: The ALL option of the GROUP BY causes the result set to include a group for each Region in the input of the query, even those for which all rows are filtered out by the WHERE clause. The effect of the ALL keyword is applied before the HAVING clause is, so HAVING COUNT(*) > 0 would effectively undo the results of the ALL keyword.
Even though GROUP BY ALL will be deprecated in SQL Server 2008, it is still supported and does not require the database to be set to a lower compatibility level.

Ref: Group by - http://msdn.microsoft.com/en-us/library/ms177673(SQL.100).aspx





Q7.Topic- Power
What will this SQL statement return?


SELECT power(2,31)

A. 0
B. 2147483648
C. Arithmetic overflow error

Ans: C


Explanation: Return of Power will be of int type as base value is int (2 is considered an int). But power(2,31) exceeds the limit of int value, so this will throw arithmetic overflow error.


Q8.Topic- Resource Governer
You have created a new workload pool in SQL Server 2008's Resource Governer and your application's logins are assigned to this pool. You notice that they are submitting many batches which are slowing your ETL process. You adjust their maximum CPU down to 50%. When does this take effect?

A. Immediately
B. When the database is closed and reopened
C. When the instance is restarted
D. When the host is rebooted.

Ans: A


Explanation: If you change the CPU percentages in the resource governer, the effect takes place immediately, though currently executing threads will complete their execution.

Ref: Resource Governor States - http://msdn.microsoft.com/en-us/library/bb895389(SQL.100).aspx

Q9. Topic- TOP and TABLESAMPLE
You have a default standard SQL 2005 SP2 server. There is a table BigTable (col1 varchar(50)) with 10,000 rows. Which of the following statements are guaranteed to return 1000 rows? (select all that apply)

A. select top 1000 col1 from bigtable
B. select col1 from bigtable tablesample system (1000 rows)
C. select top 10 percent col1 from bigtable
D. select col1 from bigtable tablesample system (10 percent)


Ans: A, C


Explanation: TABLESAMPLE returns an approximate percentage of rows, even if a number of rows is specified. TOP will return the number of rows specified if that number of rows exist.

Ref: Limiting Result Sets by Using TABLESAMPLE - http://technet.microsoft.com/en-us/library/ms189108.aspx Limiting Result Sets by Using TOP and PERCENT - http://technet.microsoft.com/en-us/library/ms187043.aspx


Q10. Topic- SQL Trace
In SQL Server 2008, if you have a server side trace running, and you run out of disk space, what happens?

A. The server instance stops.
B. The current database in which the trace is set closes.
C. Tracing stops, but the server continues to run.
D. The server pauses, allowing read-only queries, but no updates until more disk space is added.

Ans: C

Explanation: If you have a normal SQL Trace setup, and you run out of disk space, the server continues to run, but tracing stops.

Ref: Using SQL Trace - http://msdn.microsoft.com/en-us/library/ms191443.aspx


Q11. Topic- Deterministic Functions
The RAND function is deterministic only when a seed parameter is specified.

A. Yes
B. No
C. depends on definition

Ans: A

Explanation: RAND is one of those functions that is deterministic in some situations. If a SEED parameter is used, this is a deterministic function.

Ref: Deterministic and Nondeterministic Functions - http://technet.microsoft.com/en-us/library/ms178091.aspx

Q12.Topic- Page Compression
Page compression consists of which types of compression? (Select all that apply)

A. Row compression
B. Prefix Compression
C. Postfix Compression
D. Dictionary Compression

Ans: A, B, D

Explanation: Page compression is a superset of Row compression and includes Row compression. It also does prefix compression and dictionary compression to fit more rows on a page.

Ref: Page Compression Implementation - http://msdn.microsoft.com/en-us/library/cc280464(SQL.100).aspx

Q13. Topic- Updating through cursors
Of course, we all know that server-side cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can't be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.

So, suppose you find yourself in a situation where you do need a cursor, and you also need to change the data retrieved by the cursor - how can you assure maximum performance?



A. Use FOR UPDATE OF columnname, columnname in the cursor declaration, and use WHERE CURRENT OF cursorname in the update statement
B. Use FOR UPDATE (without column list) in the cursor declaration, and use WHERE CURRENT OF cursorname in the update statement
C. Do not use FOR UPDATE in the cursor declaration, and use WHERE keycolumn = @keyvalue in the update statement



Ans: C



Explanation: Though not documented by Microosoft, extensive testing has shown that reading a STATIC cursor with no FOR UPDATE option and using the primary key to update the row just read is faster than specifying a FOR UPDATE option (either with or without a column list) and using the WHERE CURRENT OF clause in the UPDATE statement.

Ref: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx



Q14. Topic- References Permission
What is the purpose of the REFERENCES permission?

A. Allows altering the size, but not the data type of the table.
B. Allows the owner of another table to use columns in the table to which they've been granted that permission as part of a foreign key.
C. Allows the user to send a data reference directly from the database to others without SELECT permissions.
D. Allows users (other than the table owner) to read encrypted values.

Ans: B



Explanation: Assigning REFERENCES permission allows the owner of another table to use columns in the table to which they've been granted that permission as the target of a REFERENCES FOREIGN KEY constraint with his or her table. However, that person won't be allowed to change the structure of the table they've been granted the permission for.

Ref: GRANT Object Permissions- http://msdn.microsoft.com/en-us/library/ms188371.aspx




Q15.Topic- SQL Server Profiler
In SQL Server 2008's Profiler, which is a faster way to capture trace data?

A. Capturing to a table
B. Capturing to a file
C. Capturing to an SSIS stream.

Ans: B



Explanation: SQL Server 2008's Profiler allows you to capture data to a SQL Server table or a file. Capturing to a file is much faster.


Ref: Saving Traces and Trace Templates - http://msdn.microsoft.com/en-us/library/ms189616.aspx


Q16.Topic- ANSI_NULLS
Assume the following table

CREATE TABLE testnull(ggg INT NULL)
INSERT testnull(ggg)SELECT NULL UNION ALLSELECT 1 UNION ALLSELECT 0
What counts do you get from the followingSELECT Count(*)
FROM testnull GOSET ANSI_NULLS OFFGOSELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1;

SELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1
OR ggg=NULL;GOSET ANSI_NULLS ONGOSELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1;

SELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1
OR ggg=NULLGODROP TABLE testnull

A. 3,3,3,2,2
B. 3,3,2,2,2
C. 3,2,3,2,2
D. 3,2,3,2,3
Ans: C

Explanation: Although Setting the ANSI_NULLS setting to OFF allows for using the =NULL notation, it does NOT affect how NULL operates with regards to comparisons with non-null values.

the SET ANSI_NULLS OFF has been marked as deprecated in SQL 2005 and will be removed in the future. For more info on ANSI_NULLS, read the following Books Online document:

Ref: SET ANSI_NULLS- http://msdn2.microsoft.com/en-us/library/ms188048.aspx


Q17.Topic- Resource Governer
In the SQL Server 2008 resource governer, if you alter your classification function, when does this take effect?
A. For all current connections
B. For new session connections
C. When a new database is accessed
D. When the server restarts

Ans: B

Explanation: If you alter the classification function, this affects all new session connections. Existing connections are not altered with this change.

Ref: Resource Governor States - http://msdn.microsoft.com/en-us/library/bb895389(SQL.100).aspx


Q18.Topic- Resource Governor
Which components of SQL Server 2008 can the Resource Governor operate on?

A. The database engine
B. Reporting Services
C. Integration Services
D. Analysis Services

Ans: A

Explanation: The Resource Governor can control resources in the database engine only. It cannot work on Reporting Services, Integration Services, or Analysis Services.

Ref: Introducing Resource Governor - http://msdn.microsoft.com/en-us/library/bb895232(SQL.100).aspx">


Q19. Topic- Sparse Columns
If you add sparse columns to a table in SQL Server 2008, what is the maximum row size for in-row storage?
A. 8,060
B. 8,128
C. 8,018
D. 2 GB

Ans: C

Explanation: In SQL Server 2008, there is a new type of column, a sparse column, for columns that are mostly NULL values. You can have many sparse columns, up to 30,000, in a table, but if you add sparse columns, the maximum row size is reduced to 8,018.

Ref: Using Sparse Columns - http://msdn.microsoft.com/en-us/library/cc280604(SQL.100).aspx


Q20. Topic- Transaction
In SQL Server 2005, which isolation levels do not acquire shared locks while performing read transactions?

A. READ UNCOMMITTED
B. SNAPSHOT
C. REPEATABLE READ
D. ALL

Ans: A, B

Explanation: READ UNCOMMITTED and SNAPSHOT levels do not acquire shared locks.

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


Q21. Topic- Name Spaces
After running the following code in the AdventureWorks sample database on SQL Server 2008 (or SQL Server 2005): (URL broken for formatting purposes.

WITH XMLNAMESPACES( 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT Applicants.LastName.value('fn:string(.)[1]', 'nvarchar(100)')
FROM HumanResources.JobCandidate jc
CROSS APPLY jc.Resume.nodes('//ns:Name.Last') Applicants(LastName);

What do you get?

A. An error message since WITH XMLNAMESPACES can only be used with the OPENXML function and not with the xml data type .nodes() method.
B. An error message since SQL Server’s XQuery implementation does not support the “//” (descendant-or-self) axis specifier.
C. No error message and the last name of every AdventureWorks job candidate is returned in a result set.

Ans: C

Explanation: The answer is "No error message is returned and the last name of every AdventureWorks job candidate is returned in a result set.

WITH XMLNAMESPACES can be used with the xml data type methods, including .nodes(), so (a) is wrong. SQL Server’s XQuery implementation does support the “//” (descendant-or-self) axis specifier so (b) is wrong.

This question brought to you by Michael Coles, author of Pro SQL Server 2005 Programmer's Guide

Q22. Topic- Data Compression
Which of the following is true?

A. Enabling Row compression enables Page compression
B. Enabling Page compression enables Row compression
C. Enabling Backup compression enables Page compression
D. Enabling Backup compression enables Row compression

Ans: B

Explanation: If you enable Page compression on an object, it enables Row compression by default. Page compression is a superset of Row compression. Neither is affected by Backup Compression.

Ref: Page Compression Implementation - http://msdn.microsoft.com/en-us/library/cc280464(SQL.100).aspx


Q23. Topic- Reformat via query?
A user at your site shows you the contents of his SSMS window, connected to a SQL Server 2005 server with Service Pack 2. In the query pane, he has:

Drive C:\ is being reformatted, please wait...
COMPLETE
Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when sending the request to the server. (error: Cannot open file for database 'Master' at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf)

A. Accidental omission of the WHERE clause
B. A trojan sqlservr.exe acting just like Microsoft's sqlservr.exe except that executing the query above will reformat the main hard drive
C. A coworker prank where the error text was directly typed into the Result Messages tab
D. A SQL 2005 SP2 bug with disastrous consequences

Ans: C

Explanation: SSMS allows the user to type input text directly into the message tab. Running the indicated query would replace the error text shown with the number of records returned or "Command(s) completed successfully." as expected.



Q24.Topic- Resource Governor
At which level of the server does the Resource Governor control resource limits?

A. The object level
B. The schema level
C. The database level
D. The instance level
E. The Windows host level

Ans: D

Explanation: The Resource Governor in SQL Server 2008 operates at an instance level. Different connections can be separated into different pools, and they might have limits on which sections of the server they can use, but the limits are set a the instance level.

Ref: Introducing Resource Governor - http://msdn.microsoft.com/en-us/library/bb895232(SQL.100).aspx


Q25. Topic- SQL Server XML
After running the following code in SQL Server 2008 (or SQL Server 2005), what is the result?

DECLARE @x xml;SET @x = CONVERT(xml, N' ]> Steak Green Beans Cornbread ©right; ', 2);SELECT @x;

What is the result?

A. An error message that SQL Server cannot parse XML with internal subset DTDs.
B. An error message that the XML is not well formed check because of an undeclared entity.
C. No error message and some XML returned.
D. An error message that the XML does not conform to the structure defined in the DTD.

Ans: C

Explanation: The answer is c) No error message is returned, but the entity defined in the DTD (©right;) is expanded in the XML data. This data is returned:

Steak Green Beans
Cornbread
© 2008 by SQL Server Chefs, Inc.

Using CONVERT with the style option 2 (or 3) allows SQL Server to parse XML with internal subset DTDs, so (a) is wrong. The ©right; entity is defined in the DTD so SQL Server does not throw an undeclared entity error, making (b) wrong. The SQL Server xml data type does not use DTDs to constrain structure (instead it uses XML schemas), so the fact that the XML data does not conform to the DTD does not throw an error. This makes (d) wrong.

This question is provided by Michael Coles, author of Pro T-SQL 2005 Programmer's Guide (Expert's Voice)