Microsoft SQL Server FAQ's Part-III

Q1. Topic- Log File Page Size
In SQL Server 2005, what is the size of log file page?


A. 8KB
B. 16KB
C. Log files do not contain pages.


Ans: C


Explanation: Log files do not contain pages; they contain a series of log records.


Ref: Pages and Extents - http://msdn.microsoft.com/en-us/library/ms190969(SQL.90).aspx




Q2. Topic- Data Collector
In SQL Server 2008, can data collection items within the same collection set have different upload schedules?

A. Yes
B. No


Ans: B


Explanation: The correct answer is no. All collection items within a collector set, regardless of target, must be on the same upload schedule.


Ref: Data Collection Terminology - http://msdn.microsoft.com/en-us/library/bb677279.aspx




Q3. Topic- Cursor optimization
Of course, we all know that 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 - which cursor option must be specified in order to assure maximum performance, assuming the cursor fits in cache?

A. none, the default options work best
B. FAST_FORWARD
C. FORWARD_ONLY
D. READ_ONLY
E. STATIC

Ans: E

Explanation: The default options are actually the slowest, since they create a dynamic cursor. In spite of its name, FAST_FORWARD does not result in the fastest performance, but can be faster with large tables that do not fit in memory. STATIC has been shown to be faster in situations where the cursor fits in cache. Specifying FORWARD_ONLY or READ_ONLY on a static cursor will not affect performance.

Ref: DECLARE CURSOR - http://msdn.microsoft.com/en-us/library/ms180169.aspx Curious Cursor Optimization - http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx (actual performance comparisons)


Q4. Topic- Identity Columns
Consider the following table.

CREATE TABLE Test
(
Col1 int IDENTITY(1,1)
)

Write the INSERT statement to populate the above table with sample values. Note: You are not allowed to use 'set identity_insert'

A. INSERT dbo.Test VALUES (1)
B. INSERT dbo.Test DEFAULT VALUES
C. INSERT dbo.Test DEFAULT VALUE

Ans: B

Explanation: The table contains only one column and it is an IDENTITY column. You cannot explicitly insert values to an IDENTITY column. Either you have to SET IDENTITY_INSERT ON before inserting the value or use the default values clause. DEFAULT VALUE is not allowed.

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


Q5. Topic- Independence Day
Since SQL Server is mostly developed in the US, it's a holiday, and the editor lives in Denver, who were the main characters in the movie Independence Day?

A. Will Smith
B. Denzel Washington
C. Dennis Quaid
D. Jeff Goldblum

Ans: A, D

Explanation: Independence Day is a great science fiction, adventure movie about aliens invading the Earth and us fighting back on July 4th, Independence Day in the US. Will Smith and Jeff Goldblum play a fighter pilot and TV engineer respectively. Recommended for this weekend if you haven't seen it (or even if you have).

Ref: Independence Day - http://www.imdb.com/title/tt0116629/


Q6. Topic-Maximum Capacity
The maximum capacity of RAM that is supported by SQL Server 2005 Workgroup edition is how much?

A. 1GB
B. 2GB
C. 3GB
D. Operating system maximum

Ans: C

Explanation: The Workgroup edition of SQL Server 2005 can support up to 3GB of RAM.

Ref: Compare Editions - http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx


Q7. Topic- Moving Files
How can you move data files in SQL Server 2005 from one drive to another? (Check all that apply)

A. Use sp_detach_db and sp_attach_db
B. ALTER DATABASE
C. Shut down the instance, move the files to a new drive, restart the instance with the -D parameter that specifices the database name and new path deparated by a colon.
D. Backup the database, drop it, and restore it using the WITH MOVE option.

Ans: A, B, D

Explanation: There are two basic ways of moving data files in SQL Server 2005. You can detach the files, copy them to the new location, and then attach them back. You can also use the ALTER DATABASE command with the MODIFY FILE options. You can also backup the database, drop it, and restore using the new location.

Ref: Moving User Databases - http://msdn.microsoft.com/en-us/library/ms345483.aspx


Q8. Topic- TABLESAMPLE
You have a default standard SQL 2005 SP2 server.
There is a table BigTable (col1 varchar(50)) with 10,000 rows.

How many rows does the following statement return?select col1 from bigtable tablesample system (10 percent)

A. 10
B. 1000
C. 10000
D. impossible to determine

Ans: D

Explanation: TABLESAMPLE returns an approximate percentage of rows, even if a number of rows is specified. This is used to get a sample of data from large rows and does not guarentee a number of rows or a random sample.

Ref: Limiting Result Sets Using TABLESAMPLE - http://technet.microsoft.com/en-us/library/ms189108.aspx


Q9. Topic- ALL
Assuming that I have the following values in the TimeGroup table, not case sensitive, (value1, value2, value3, value4), what does this query return?

if 'value1' < all ( select column1 from TimeGroup )
select 1
else
select 0

A. 1
B. 0

Ans: A

Explanation: This query checks the value given against the subquery by applying the "ALL" keyword to the operation. If all of the values returned meet these criteria, then TRUE is returned to the IF statement and it is false in this case since "VALUE1" was given in the question and that is not less than "value1". If A >= would return true.


Q10. Topic- Windowed Aggregate functions
Given the following table:

drop table #windows
create table #windows(id int identity(1,1), grp char(5), subgrp int, val int)
insert #windows (grp, subgrp, val)
select 'aaaaa',1,1 union all
select 'aaaaa',2,2 union all
select 'aaaaa',2,2 union all
select 'aaaaa',3,3 union all
select 'aaaaa',3,3 union all
select 'aaaaa',3,3 union all
select 'bbbbb',1,1

What does the following query return:

select grp,subgrp,count(*)
OVER (partition by grp,subgrp) grpcount,
count(*) OVER (partition by grp) subcount,
count(*) OVER (partition by NULL) grandcount
from #windows
group by grp, subgrp

A. aaaa 1 1 6 7, aaaa 2 2 6 7, aaaa 3 3 6 7, bbbb 1117
B. aaaaa 1 1 3 4, aaaaa 2 1 3 4, aaaaa 3 1 3 4, bbbbb 1 1 1 4
C. aaaaa 1 1 14 15, aaaaa 2 4 14 15, aaaaa 3 9 14 15, bbbbb 1 1 1 15
D.Msg 5309, Level 16, State 1, Line 1Windowed functions do not support constants as PARTITION BY clause expressions.

Ans: B

Explanation: Per ISO SQL - Windowed Aggregate function are calculated based on the relevant window in the RETURN SET. According to the expanded definition - that means that the Window is applied to the rows remaining, based on the following SQL Processing Steps (in ORDER):
1. JOIN/FROM clause
2. WHERE clause
3. GROUPING
4. AGGREGATION
5. AGGREGATION SELECTION (HAVING clause)
6. Window functions
7. SELECT clause
8. ORDER BY clause

In this example - the Windowed functions are firing against the result set AFTER the group by has been applied (so it's windowing over distinct values).

Ref:- For ISO SQL syntax information on Windowed Aggregate functions, see - http://savage.net.au/SQL/sql-2003-2.bnf.html


Q11. Topic- Database SnapShot
Database SnapShots are not allowed on which databases? (Check all that apply)

A. SalesDB
B. Master
C. Tempdb
D. AdventureWorksDW
E. Model
F. Msdb

Ans: B, C, E

Explanation: Database snapshots cannot be created on master, tempdb and model databases.

Ref: Limitations on Database Snapshots - http://technet.microsoft.com/en-us/library/ms189940.aspx


Q12. Topic- Cloning
Ever want or need a very much smaller version of your massive SQL 2005 production database where you could examine the execution plans for slow running queries, modify them and check for improvements, create new SPs and check execution plans all without tying up or bogging down your production environment? You do? Then create a clone. Which of these statements about cloning is true in SQL Server?

A. Creating a clone is not legal
B. Create a clone for testing
C. Clone is not efficient

Ans: B
Explanation: From a public blog - so owners permission to use is assumed - http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/21/cloning-in-sql-server-2005.aspx


Q13. Topic- CHECKDB
Does DBCC CHECKDB require space in tempdb?

A. Yes
B. No, all activity is in the current database being checked.

Ans: A

Explanation: A number of structures are created in memory while running tempdb and these can spill into tempdb if space is needed. It is not guarenteed that tempdb will be used, but it is used by the CHECKDB process if required.

Ref: CHECKDB From Every Angle: Why would CHECKDB run out of space? - http://www.sqlskills.com/blogs/paul/2007/11/06/CHECKDBFromEveryAngleWhyWouldCHECKDBRunOutOfSpace.aspx (near the bottom)


Q14. Topic- Sort Order - Include Null al last
You have a table with EmpName and DateOfLeaving columns. The EmpName column is mandatory, but DateOfleaving will have null value for those employees who have not left the company. The question is to have list off all employees with the employees with DateOfLeaving coming last and all others sorted ascending order of date of leaving, Name sorted in ascending order for each group. Consider the Example:

Table: Employees
EmpName DateOfLeaving
Abc 10 Oct 1999
Bcd 11 Nov 1998
Ccd null
Dcd 10 Aug 2000
Eed null

The solution should be:
EmpName DateOfLeaving
Bcd 11 Nov 1998
Abc 10 Oct 1999
Dcd 10 Aug 2000
Ccd null
Eed null

Which query will produce this output? (select all that apply)

A. Select EmpName, DateOfLeaving from Employees order by DateOfLeaving, EmpName asc
B. Select EmpName, DateOfLeaving from Employees order by isnull(DateOfLeaving,'10/10/9999'),EmpName asc
C. Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc

Ans: B

Explanation: In SQL Server T-SQL, by default, a query takes null as first when the order is ascending (the default). So we just give some value of greater date in order by so that it will come at last.

Ref: ORDER BY - http://msdn.microsoft.com/en-us/library/ms188385.aspx


Q15. Topic- ANY
Assuming that I have the following values in the TimeGroup table (value1, value2, value3, value4), what does this query return?

if 'Value1' < any ( select column1 from TimeGROUP )
select 1
else
select 0

A. 1
B. 0

Ans: A

Explanation: This query checks the value given against the subquery by applying the "ANY" keyword to the operation. If any value meets these criteria, then TRUE is returned to the IF statement and it is true in this case since "VALUE1" was given in the question.

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


Q16. Topic- CLR Assemblies
If you ALTER ASSEMBLY for one of your CLR objects and set the visibility to OFF, what does this imply?

A. No new objects can be created against the assembly
B. This assembly is only intended to be called by other assemblies
C. The assembly is disabled and all objects calling it return an error.
D. Reflection will not work with this assembly.

Ans: B

Explanation: The visibility property determines if the assembly can be called by other objects (stored procedures, functions, etc.) or only from other assemblies. OFF implies only other assemblies will call it. If you have objects referencing this assembly, the visibility cannot be changed.

Ref: ALTER ASSEMBLY - http://msdn.microsoft.com/en-us/library/ms186711.aspx


Q17. Topic- Cursors
If you open a cursor, which of these will allow you to get a count of the number of rows in the cursor? (select all that apply)

A. sp_cursor_list
B. sp_describe_cursor
C. sp_describe_cursor_columns
D. @@cursor_rows

Ans: A, B, D

Explanation: All of these items except sp_describe cursor columns will allow you to determine the number of rows in some cursors, subject to restrictions. The @@Cursor_rows requires a non-dynamic cursor.

Ref: sp_cursor_list - http://msdn.microsoft.com/en-us/library/ms186256.aspx sp_describe_cursor - http://msdn.microsoft.com/en-us/library/ms173806.aspx sp_describe_cursor_columns - http://msdn.microsoft.com/en-us/library/ms182755.aspx @@cursor_rows - http://msdn.microsoft.com/en-us/library/ms176044.aspx


Q18. Topic- A Hex on Your Database
Assuming you've taken these steps to prevent SQL Injection:
1. Replace single-quote ' with double-single-quote ''
2. Check for Select, Update, Delete
What happens if your web page runs into this at the end of the

URL:/YourPage.asp?account=1;declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a)

And runs that in a dynamic SQL command in SQL Server 2005?

A. Error Message "Syntax error near '0x736...'"
B. Nothing
C. A list of all the databases on your server

Ans: C

Explanation: This is a currently common SQL injection attack. If the web page does not use stored procedures, but instead uses dynamic SQL, this is a valid SQL 2005 command (there are versions for SQL 2000), and might execute.

cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000)) resolves to "select name from sys.databases;".

With Varchar(max) and Varbinary(max), much more complex commands can be issued.


Q19. Topic- BETWEEN
Given the following script -

create table #fun(id int identity(1,1) primary key clustered, crit int)
insert #fun(crit)
select top 250000 rand(checksum(newid()))* 10
from syscolumns sc1, syscolumns sc2

--select statement #1
select count(*) from #fun
where crit between 3 and 5

--select statement #2
select count(*) from #fun
where crit between 5 and 3

Would statements #1 and #2 consistently return the same result set?

A. Yes
B. No
C. Can't be determined

Ans: B

Explanation: In plain language, BETWEEN is a commutative operation, so the order in which you specify the criteria is irrelevant.
In TransactSQL, however, BETWEEN is not communtative. Books Online defines BETWEEN as:
"BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression."
By that definition, there is no attempt to reorder the parameters, so Statement #2 will always return 0.
Ref:- For more info on BETWEEN - http://msdn.microsoft.com/en-us/library/ms187922.aspx


Q20. Topic- Know your UNION(s), NULL(s), COUNT(s) ?
What do you expect the result of the following query to be? No cheating, don't run until you've answered!

WITH DATA (Numbers) AS
(SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION
SELECT 3)
SELECT COUNT(ALL Numbers) AS NULLNumberCount FROM DATA
WHERE Numbers IS NULL

A. Null
B. 0
C. 1
D. 3
E. 6

Ans: B

Explanation: The final UNION negates the duplicates selected with the previous UNION ALL statements. The COUNT(ALL expression) evaluates the expression for each row in a group and returns the number of nonnull values. ALL Applies the aggregate function to all values. ALL is the default.
For investigation, change the final UNION to UNION ALL, and the SELECT COUNT (ALL Numbers) to COUNT (*)

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


Q21. Topic- Database Mirroring
In SQL Server 2005 Database Mirroring, which form is used to specify the network address of the servers? (items inside brackets are replaced with your specifics)

A. tcp://:
B. http://:
C. dbm://:
D. udp://:

Ans: A

Explanation: The server address in Database Mirroring is specified with the TCP start, followed by colon and two slashes before the server address and port are specified, separated by a colon. The port must correspond to the endpoint on the other server that has been setup for database mirroring.

Ref: Specifying a Server Network Address (Database Mirroring) - http://msdn2.microsoft.com/en-us/library/ms189921.aspx


Q22. Topic- DMVs
What information is stored in the sys.dm_clr_tasks DMV?

A. A row for each assembly that has been added to the database
B. A row for each function in the database that has a CLR reference in its code.
C. A row for each task that is running a batch containin a CLR task
D. This DMV does not exist in SQL Server 2005

Ans: C

Explanation: The sys.dm_clr_tasks contains a row for each CLR task that is executing. Each batch containing a reference to CLR code creates a task and all CLR items in that batch execute under that task.

Ref: sys.dm_clr_rasks - http://msdn2.microsoft.com/en-us/library/ms177528.aspx


Q23. Topic- SQL Server 2008 FILESTREAM
How can you enable FILESTREAM in SQL Server 2008?

A. sp_filestream_configure
B. xp_cmdshell
C. xp_filestream_configure
D. sp_cmdshell

Ans: A

Explanation: For enabling FILESTREAM storage in SQL Server 2008 one needs to execute sp_filestream_configure with parameter @enable_level set to 1, 2, or 3.

Ref: sp_filestream_configure - http://msdn2.microsoft.com/en-us/library/bb934198(SQL.100).aspx


Q24. Topic- CASTing
Second question of day: what is the len of @c?

declare @c varchar(800)
set @c = N'hello' + replicate('-',800)
print len(@c)print @c

A. 800
B. 400
C. 4000

Ans: A
Explanation: The CAST to nvarchar(800) has a maximum 4000 character len. The CAST then to varchar(800) fits in that space, so the len is 800

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


Q25. Topic- Physical Database Architecture
How many pages are allocated to a log file in a database with a 10MB log?

A. 1280
B. 160
C. 0

Ans: C

Explanation: Zero. Log files contain log records, not pages.

Reference: Pages and Extents - http://msdn2.microsoft.com/en-us/library/ms190969.aspx