What's SQL Server

SQL Server is a DBMS system provided by Microsoft. SQL Server is sometimes mistakenly referred to as SQL.

Can you use a CASE statement in a WHERE clause?

Answer: Yes

Explanation: You can. Try this code:
create table MyTable
( mydata int
, mycompare1 char(1)
, mycompare2 char(1)
)
go
insert MyTable select 1, 'A', 'B'
insert MyTable select 2, 'A', 'C'
insert MyTable select 3, 'D', 'G'
insert MyTable select 4, 'E', 'B'
GO
select *
from MyTable
where 1 = CASE
WHEN mycompare1 = 'A' then 1
ELSE 0
END
GO
drop table MyTable

You are considering using the SQL Server Everywhere (SQL Server Compact Edition) in your application that will run on Smartphones and PDAs. However you are concerned that it will not hold all the data that people will store in it over the next few years. How large a database can you have in this edition of SQL Server 2005?

Answer: 4GB

Explanation: The database limit is 4GB, which at the current time, greatly exceeds the capacity for most small devices.

What is one of the downsides of using RAID 5 as opposed to RAID 1?

Answer: Both write and read performance are worse.

Explanation: The downside of RAID 5 is that write performance can be degraded because it must calculate parity as well as write the data to multiple disks. In RAID 1, the information is written once to two disks.

What does this code return?
select 2 ^ 3
Figure it out without running it.

Answer: 1
Explanation: This returns a 1, which is the result of an Exclusive OR between these numbers. These numbers are:
2 - 0010
3 - 0011
If you OR these numbers, you get a 1, which is 0001, all bits with only a single 1 between them are returned.

To optimize the configuration of tempdb for performance, what setting should be used for the tempdb recovery model?

Answer: Simple

Explanation: Since tempdb is used for temporary storage and rebuilt every time the server is started, the Simple recovery mode makes the most sense.

Which query will return a list of the synonyms in your database?

Answer: select * from sys.synonyms

Explanation: The sys.synonyms catalog view will return the synonyms in a database.

You are building an application for a Smartphone and want to use SQL Server Compact Edition. However you do not want this to run as a service all the time because of power concerns. What can you do?

Answer: This edition does not run as a service, so there is nothing to worry about.

Explanation: SQL Server Compact Edition runs in process with an application, meaning it does not run as a service. Therefore it will only run when your application is running.

Which of the following classes provide the context of the caller to SQLCLR Assemblies? Submitted by Michael Coles

Answer: Microsoft.SqlServer.Server.SqlContext

Explanation: The answer is 1:Microsoft.SqlServer.Server.SqlContext
SQLCLR Assemblies can use the Microsoft.SqlServer.Server.SqlContext class - it is an abstraction of the caller's context. Objects exposed by SqlContext include SqlPipe, SqlTriggerContext, and WindowsIdentity.

How can you get SQL Server 2005 to simulate multiple processors for query testing?

Answer: Use the -P parameter in the startup parameter list.

Explanation: You can use the -Px trace flag in the startup parameters, where x is the number of processors to simulate. Beware of using numbers higher than 4 on a single processor machine. Also note that this is unsupported by Microsoft.

You restart your SQL Server on a Sunday morning after some maintenance and decide to do some other work before people come back Monday and begin using the server. Since you are the only person on the server, you decide to query the sys.dm_db_index_usage_stats DMV and get what?

Answer: You don't get any information back.

Explanation: While this DMV does indeed store information about index usage while the server is running, it is cleared on each server restart and does not maintain this information across time. Therefore you would not get any information.

Which of the following CREATE ASSEMBLY statement WITH PERMISSION_SET clauses sets the *minimum* required permission set for a SQLCLR Assembly to access Windows environment variables? Submitted by Michael Coles

Answer: WITH PERMISSION_SET EXTERNAL_ACCESS

Explanation: The correct answer is 3: WITH PERMISSION_SET EXTERNAL_ACCESS
The EXTERNAL_ACCESS permission set is the *minimum* permission set required for SQLCLR Assemblies to access Windows environment variables, files in the filesystem, Windows registry, and network resources.

(Continued on next part...)