Microsoft SQL Server FAQ's Part-I

Microsoft SQL Server FAQ's

Those who wants to test there knowledge towards Microsoft SQL Server here i have a some important Questions.

Q1. Topic: iFTS( integrated full- text)
In SQL Server 2008's integrated full-text search, if you wish to create a stoplist, what are your options? (Choose all that apply)

A. Base it on an empty list (create from scratch)
B. Create from a system default stoplist
C. Create it from another user defined stoplist.
D. Create it from a dictionary file

Ans: A, B, C.

Explanation: Custom stoplists in SQL Server 2008 contain noise words that are not relevant to most searches.

Ref: Stopwords and Stoplists - http://msdn.microsoft.com/en-us/library/ms142551.aspx

Q2. Topic- SQL Trace
If you are creating a server side trace with T-SQL stored procedures in SQL Server 2008, can events be lost if you have disk space?

A. Yes, if the server is busy
B. No, all events are guarenteed to be captured.

Ans: B

Explanation: The correct answer is no. All events are guaranteed to be captured if a server side trace is used. It is possible that you cannot read all events if a write error occurs, but the event would still be captured, just the logging missed.

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

Q3. What is the result ? (SQLServer 2005)
In SQL Server 2005, what would be the result of this code?

print 'Hey what''s going on ?';
GO 10

A. Incorrect syntax was encountered while parsing GO.
B. 'Hey what''s going on ?'; is printed in the result
C. The batch is executed 10 times

Ans: C

Explanation: The result is:

Hey what's going on ?

Hey what's going on ?

Hey what's going on ?

Hey what's going on ?

Hey what's going on ?

Hey what's going on ?

Hey what's going on ?

Hey what's going on ?

Hey what's going on ?

Hey what's going on ?

The batch execution is completed 10 times. BOL states:

GO [count]

where count is a positive integer. The batch preceding GO executes the specified number of times.

Ref: GO - http://www.exceptionaldba.com/images/exceptional-logo.gif

Q4. Topic- Type lengths
How many rows will be returned in each of the three resultsets below?

CREATE TABLE #TEST(A varchar,B varchar(30));
INSERT INTO #TEST(A) VALUES('A');
INSERT INTO #TEST(B) VALUES('Some text');
INSERT INTO #TEST(A) VALUES('B');
INSERT INTO #TEST(B) VALUES('Some more text');

SELECT COUNT(*)
FROM #TEST
GROUP BY LEN(ISNULL(B,A));

SELECT COUNT(*)
FROM #TEST
GROUP BY LEN(ISNULL(A,B));

SELECT COUNT(*)
FROM #TEST
GROUP BY LEN(COALESCE(A,B));

A. 1,1,1

B. 2,2,2

C. 3,3,3

D. 3,1,3

E. 3,1,1

Ans: D

Explanation: The output type of ISNULL is determined by its first argument. Hence, ISNULL(A,B) above will result in a varchar of length 1 (datatype of column A).

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

Q5. Topic- Data Collector
What type of data store is the Management Data Warehouse in SQL Server 2008?

A. Relational Database
B. Object Database
C. Cube
D. Flat File database

Ans: A

Explanation: The Management Data Warehouse in SQL Server 2008 is a relational database, just like any other relational database in the SQL Server database engine. It stored performance data from SQL Server instances and databases.

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

Q6.Topic- Spatial Indexing
What is the maximum number of spatial indexes on a table in SQL Server 2008?

A. 1
B. 8
C. 249
D. 1024

Ans: C

Explanation:
You can create up to 249 spatial indexes on columns in a table, including more than one spatial index on the same column.

Ref: Restrictions on Spatial Indexes - http://msdn.microsoft.com/en-us/library/bb964740(SQL.100).aspx

Q7. Topic- Best Practice
You receive some data from UI and need to populate table Customer if the name received from UI does not exist in database. Which of options below should be used for this purpose:

1.

IF NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName )
INSERT INTO [Customer]
( [Name])
VALUES
( @CustomerName)

2.

INSERT INTO [Customer]
( [Name])
SELECT @CustomerName
WHERE NOT EXISTS ( SELECT * FROM Customer
WHERE Name = @CustomerName )

A. Option 1 is the one to use
B. Option 2 is the one to use
C. Both options are equal, any one may be used according to personal preferences

Ans: B

Explanation: Option 1 has 2 separate statements. The first one applies locks relevant table resources in order to perform check for existence. As soon as it's completed SQL Server releases all applied locks. At the moment parallel SPID may insert a record into the table BEFORE following INSERT statement has applied its locks.

Therefore initial check may be irrelevant at the moment when following INSERT starts. IF there is a unique constraint on Customer.Name INSERT would fail despite you've done the check for existence.

The second option does everything in a single transaction. It applies the locks and holds it until INSERT transaction has finished its job. Another SPID cannot insert another row until all locks on the object applied by INSERT statement are released.

So, the first option is unacceptable, it relies on a user's luck not to have another user doing same thing at the same time. Of course, probability of the failure is quite low but it's a possible event. In active transactional systems with hundreds of transactions per second it does not look so improbable.

Script to run the test:

USE pubs

GO

CREATE TABLE Customer

(

ID int IDENTITY(1,1) NOT NULL,

Name nvarchar(100) NOT NULL,

UNIQUE (Name)

)

GO

DECLARE @CustomerName nvarchar(100)

SET @CustomerName = 'Customer2'

IF NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName )

BEGIN

WAITFOR DELAY '00:00:05'

/* this 5 sec pause lets you insert same row from another QA window:

DECLARE @CustomerName nvarchar(100)

SET @CustomerName = 'Customer2'

INSERT INTO [Customer]

( [Name])

VALUES

( @CustomerName)

*/

INSERT INTO [Customer]

( [Name])

VALUES

( @CustomerName)

END

SET @CustomerName = 'Customer3'

INSERT INTO [Customer]

( [Name])

SELECT @CustomerNameWHERE NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName )

GO

select Object_Id('Customer')

DROP TABLE Customer

Q8.Topic- Index covering
What is a "covered query" or "Index Covering?

A. A covered query is a query where all of the columns necessary are contained in the index.
B. There's no such thing, you made it up
C. A query that accesses more than one table
D. A query that accesses the same table more than once

Ans: A

Explanation: Since all of the columns are in the index, it's not necessary for the query to use the table - it will only use the index. As always with indexes, be sure to test, because while indexes will speed up queries, they will generally have the opposite effect on inserts and updates.
Reference:

Designing an Index - http://msdn.microsoft.com/en-us/library/aa933128.aspx

General Index Design Guidelines - http://msdn.microsoft.com/en-us/library/ms191195.aspx http://msdn.microsoft.com/en-us/library/ms979195.aspx - http://msdn.microsoft.com/en-us/library/ms979195.aspx

Q9. Topic- iFTS
In SQL Server 2008's integrated full-text search, what is the new terminology for noise words?

A. Noise Text
B. Discards
C. Stopwords
D. Ignore Words

Ans: C

Explanation:
SQL Server 2008 introduces the concept of stop words and stoplists (a collection of stop words) to replace the previously used noise words in SQL Server 2000 and SQL Server 2005.

Ref: Stopwords and Stoplists - http://msdn.microsoft.com/en-us/library/ms142551.aspx

Q10. Topic- Simulating memory pressure
You have been asked to optimize a stored procedure that runs against a terabyte-sized database. The stored procedure executes several steps consecutively. The performance problems appear to be mainly I/O related.

You install a severely trimmed down test version of the database (1 GB in size) on your desktop computer running SQL Server Developer Edition. Before you start optimizing, you want to establish a baseline by timing the stored procedure on your development machine, so that you can later compare performance after adding indexes and tweaking code.

However, your desktop has 2 GB of memory installed, and you are concerned that the performance test results may be skewed because the test version of the database fits entirely in cache. What is the best way to simulate the production circumstances as closely as possible?

A. Create a bigger test database, at least 5 GB in size.
B. Execute sp_configure and RECONFIGURE to limit server memory to 512 MB, then restart the SQL Server service.
C. Shut down your desktop, open the cover, remove some of the memory DIMMs and restart the desktop.
D. Execute DBCC DROPCLEANBUFFERS before starting the test.
E. Start some really memory hungry applications before starting the test.

Ans: B

Explanation: Executing "EXEC sys.sp_configure N'max server memory (MB)', N'512'" followed by "RECONFIGURE WITH OVERRIDE" will limit the memory used by your server to 512 MB. Since SQL Server might have already allocated more memory, which will not be released immediately, a reboot will force this memory setting to be respected.

Creating a bigger test database will also work, but involves a lot more work, will cause your tests to run longer, and might require you to clean up your collection of holiday pictures. Removing some RAM from your computer works as well, but will cause all other processes in your computer to slow down as well. Not recommended (especially if you are as clumsy in handling electronic equipment as I am)

Executing DBCC FREEPROCCACHE before starting the tests will cause the FIRST query to run with no data in cache, but the remaining steps of the stored procedure will once more be able to read data from cache. This is not a good simulation of the production circumstances. Starting memory hungry applcations might indeed cause available memory for SQL Server to be reduced. But it will also cause extra strain for the CPU, and you might get SQL Server and the other applications "fighting" for memory or Windows starting to page some applications in and out of memory. Again, not a good simulation of the production circumstances.

Q11. Topic- TSQL variable
What will be the output?

Declare @var

intSelect @var = isnull(@var,1) + Value1

From (Select 1 Value1 Union All Select 1 Union All Select 2) as a Select @var

A. 3
B. 5
C. Syntax Error

Ans: B

Explanation: The correct answer is 5. The query's behavior is the same as selecting value1 = value1 + 1.

Q12. Topic- DB File Extension
SQL Server 2005 does not enforce database filename extensions, but which of the following are the default file extensions for database files? (choose all that apply)
A. *.mdf
B. *.ldf
C. *.ndf

Ans: A, B, C

Explanation: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions help you identify the different kinds of files and their use. MDF is for primary database files, NDF is for secondary database files and LDF is for log files.

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

Q13. Topic- Single Page Restore
Can all page types be restored with a single page restore?

A. Yes
B. No

Ans: B

Explanation: While data pages can be restored with the single page restore feature, there are page types that cannot be restored. The following types cannot be single page restored:

. File header pages
. Boot page
. GAM, SGAM, DIFF map, and ML map pages

Ref: Search Engine Q&A #22: Can all page types be single-page restored? - http://www.sqlskills.com/blogs/paul/2008/08/11/SearchEngineQA22CanAllPageTypesBeSinglepageRestored.aspx

Q14. Topic- Counts Puzzle
What will be the count against each category? Each row in the result is shown as a pair in the answers.

CREATE TABLE #CATEGORY
(

CATID INT,

VAL1 INT

)

INSERT INTO #CATEGORY VALUES(1,NULL)
INSERT INTO #CATEGORY VALUES(2,1)
INSERT INTO #CATEGORY VALUES(3,2)

CREATE TABLE #DATA
(VAL1 INT)

INSERT INTO #DATA VALUES(1)
INSERT INTO #DATA VALUES(1)
INSERT INTO #DATA VALUES(1)
INSERT INTO #DATA VALUES(2)
INSERT INTO #DATA VALUES(2)
INSERT INTO #DATA VALUES(3)

SELECT C.CATID, COUNT(*)
FROM #DATA D
INNER JOIN #CATEGORY C ON C.VAL1 = D.VAL1 OR C.VAL1 IS NULL
GROUP BY C.CATID

A. 1 6,2 3,3 2
B. 1 5,2 3,3 2
C. 1 6,2 3,3 3
Ans: A

Explanation: For category 1, the join condition is true for all data records, that's why its count is 6. For category 2 and 3, due to the equi join, it returns the exact count for each one respectively.

Q15. Topic- Go go go?
Running this in SSMS gives what?
CREATE PROC GO @GO int=NULL AS SELECT @GO

GO

GO;

GO 3

GO

EXECUTE('GO 3')

GO 3

GO

DROP PROC GO

GO
A. Some resultset are returned along with the error: Incorrect syntax near the keyword 'GO'.
B. Error: Incorrect syntax near the keyword 'GO'.
C. Some resultsets are returned.
D. Error: Invalid object name 'GO'.
E. No results and no error.
F. Error: A fatal scripting error occurred. Incorrect syntax was encountered while parsing GO.

Ans: C

Explanation: "GO" is simply a batch delimiter used by SSMS and is not passed the query engine. "GO;" however, is interpreted as statement (because of the ";") and since "GO" is simply a SSMS thing creating and running the procedure is all ok. "G0 x" means run the above batch x times.

Q16. Topic- Spatial Indexing
If you wish to create a spatial index in SQL Server 2008, which of the following is required? (select all that apply)
A. A primary key must be defined on the table.
B. The index cannot be on an indexed views.
C. The table must have a clustered index
D. The table must be have a rowversion column defined.

Ans: A, B

Explanation: A spatial index is for columns that are of the geometry or geography types and requires that a primary key be defined, of which there are less than 15 key columns. There cannot be a spatial index created on indexed views, but the table does not need a rowversion column or a clustered index.

Ref: Restrictions on Spatial Indexes - http://msdn.microsoft.com/en-us/library/bb964740(SQL.100).aspx

Q17. Topic- Boot Page
In which Page does the BootPage of the Database reside?
A. Header Page
B. Master Database Header Page.
C. 1 in file 1
D. 9 in file 1

Ans: D

Explanation: The Boot Page of Any database is on the page 9 in File 1.

Ref: Boot pages and boot page corruption - http://www.sqlskills.com/blogs/paul/2008/07/11/SearchEngineQA20BootPagesAndBootPageCorruption.aspx

Q18. Topic- Hashing
An undocumented hash routine in SQL Server is pwdencrypt. What is the name of the function to check the hash?
A. pwdcontrast
B. pwdcompare
C. pwdconfirm

Ans: B

Explanation: The function that should be used is PWDCOMPARE as shown in this sample code:

Select (pwdcompare('mypassword',CONVERT(varbinary(255), pwdencrypt('mypassword')))) as checker

Ref: SQL Server undocumented password hashing builtins: pwdcompare and pwdencrypt - http://blogs.msdn.com/lcris/archive/2007/10/31/sql-server-undocumented-password-hashing-builtins-pwdcompare-and-pwdencrypt.aspx

Q19. Topic- SQL Server 2005
The Database Engine features that are disabled by default on SQL Server 2005 installations are:
A. CLR Integration
B. Database Mail
C. SQL Mail
D. Web Assistant

Ans: A, B, C, D.

Explanation: All of these features are disabled by default in SQL Server 2005.

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

Database Mail - http://msdn.microsoft.com/en-us/library/ms175887.aspx

SQL Mail - http://msdn.microsoft.com/en-us/library/ms175887.aspx

Web Assistance - http://msdn.microsoft.com/en-us/library/ms175576.aspx

Q20. Topic- FORMATMESSAGE
If you use the FORMATMESSAGE command to return an error message, is the text localized?
A. Yes
B. No

Ans: A

Explanation: The FORMATMESSAGE determines looks up localized versions of error messages and returns those. If one does not exist, the US English version is used.

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

Q21. Topic- Short cut key
What is the short cut key to execute sp_help procedure for any object in a Query Window in Management Studio? The object must be highlighted in the code window.

A. Ctrl+F1
B. Alt+F1
C. Shift+F1
D. Short cut is not available for sp_help

Ans: B

Explanation:
To execute sp_help procedure for any object, just select object name and press Alt+F1.

Ref: Query Window Shortcuts - http://www.kodyaz.com/articles/sql-query-window-short-cuts.aspx

Q22. Topic- Update Stats with Reindex
Does SQL Server update statistics with Reindex on a table in SQL Server 2005?

A. No
B. Yes, with partial sampling.
C. Yes, With full table scan details

Ans: C

Explanation: Whenever an index is rebuilt the stats on the table is updated with the details of a full table scan.

Ref: Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics - http://www.sqlskills.com/blogs/paul/2008/01/27/SearchEngineQA10RebuildingIndexesAndUpdatingStatistics.aspx

Q23. Topic- LIKE operator
Consider the T-SQL statements given below;

create table #Like_Test
( Col1 varchar(5) )
go

insert #Like_Test values('_Cus')

insert #Like_Test values('Cus_')

insert #Like_Test values('C_us')

insert #Like_Test values('Cus')

insert #Like_Test values('Cu_s')

select * from #Like_Test
where Col1 Like '%Cu_%'

How many records would you expect from the SELECT query?

A. 4
B. 1
C. 5

Ans: A

Explanation: The meaning of the ‘_’ (underscore) in LIKE string is, to represent any single character. This will not recognize the ‘_’ as a string of the Col1. You can use ECSAPE key word at the end of the statement to consider ‘_’ as a string.

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

Q24. Topic- Rowversion
What data type does this return in SQL Server 2005, SP2 and above?

SELECT MIN_ACTIVE_ROWVERSION()

A. int
B. Bigint
C. Datetime
D. Timestamp
E. Nothing as this is not a real SQL Server function.

Ans: D

Explanation: The MIN_ACTIVE_ROWVERSION function returns a timestamp data type that is the minimum value in the database. The rowversion value actually replaces the timestamp data type.
This was introduced in SP2 for SQL Server 2005.
Ref: MIN_ACTIVE_ROWVERSION - http://msdn.microsoft.com/en-us/library/bb839514.aspx

Q25. Topic- VarChar(max)?!?
What does the following SQL 2005 code snipet return?

Declare @Str VarChar(max);

Set @Str=Replicate('*',10000)+Replicate('*',10000);

Print Len(@Str);

A. 8000
B. 10000
C. 20000

Ans: A

Explanation: Apparenly VarChar(max) isn't intrinsically supported everywhere. You can fix it by CONVERT/CASTing the Replicate() string parameters to VarChar(max)!

Declare @Str VarChar(max);

Set @Str=Replicate(Cast('*' as VarChar(max)),10000)+Replicate(Cast('*' as VarChar(max)),10000);

Print Len(@Str);