SQL Champ - Quiz

We invite you to prove your knowledge about certain subjects concerning Microsoft .NET technology by participating in a monthly quiz. This month the quiz is about SQL Server. In this article you can reread the questions. Additionally you get background information about the correct answers.

Here is a list of all questions included in the quiz. Use the hyperlinks to jump to the topic you are interested in:

• Question 1 - A Simple One To Start
• Question 2 - Data Type
• Question 3 - Date and Time Handling
• Question 4 - Aggregation
• Question 5 - SQLCLR
• Question 6 - Database Snapshots
• Question 7 - TOP Clause

Question 1 - A Simple One To Start

You want to access a SQL Server instance that is not in your domain. The only network protocol you can use is TCP/IP. Which ports do you need to open on the firewall that is between your computer and the server running SQL Server?

1. SQL Server uses a random port.
2. 1433
3. 1433 and 1434
4. It depends; the port can be chosen by an administrator

Port settings in SQL Server Configuration Manager

The correct answer is It depends; the port can be chosen by an administrator. 1433 is the default port for the default instance of SQL Server (has been assigned by IANA - Internet Assigned Numbers Authority; see http://www.iana.org/assignments/port-numbers). Before version 2000 one port was sufficient because on a single server you could only run a single instance of SQL Server. However, since version 2000 there can be multiple instances and therefore an administrator can choose which port is used by which instance.

The port 1434 (UDP) is used by the SQL Server Browser service. Clients can use this service to query connection information (TCP/IP port or named pipe) about all running SQL Server instances. If you know which port the instance you want to connect to is using you do not need SQL Server Browser at all. Therefore you do not need a connection to port 1434 in this case.

Question 2 - Data Type

You want to track date and time of the last write access (INSERT, UPDATE) per row. How can you achieve that?

1. Add a TIMESTAMP column to the table; SQL Server will automatically track date and time of every change.
2. Add a DATETIME column to the table and assign getdate() as the default value.
3. Add a DATETIME column to the table and write a trigger that sets its value.
4. Add a UNIQUEIDENTIFIER column to the table and use it with SQL Server's built functions.

If you just want to record date and time of the last change to identify those rows that have changed since a certain point in time (e.g. for delta load of a DWH) and you are able to use SQL 2008 check out change tracking and data capture. They provide a mechanism for that without having to change the table structure at all.

The correct answer is Add a DATETIME column to the table and write a trigger that sets its value. TIMESTAMP does not help in this case because it does not record any date or time values. It just offers a way to generate unique binary numbers that are changed whenever the content of a row changes. The following script shows how to use TIMESTAMP:

use tempdb;  

create table ChangeTrackingTest (  
  MyId int primary key,  
  MyDesc varchar(50),  
  MyTimestamp timestamp  
);  

insert into ChangeTrackingTest ( MyId, MyDesc )  
values ( 1, 'Test' );  

select * from ChangeTrackingTest;  

update ChangeTrackingTest  
set MyDesc = 'Test 2'  
where MyId = 1;  

select * from ChangeTrackingTest;

This is the result of this batch. Note how the value of the TIMESTAMP column changes:

(1 row(s) affected)  
MyId MyDesc MyTimestamp  
--------------------------- 
1 Test 0x00000000000007D4  

(1 row(s) affected)  

(1 row(s) affected)  

MyId MyDesc MyTimestamp  
--------------------------- 
1 Test 2 0x00000000000007D5  

(1 row(s) affected)

The second answer is wrong because this solution would only record date and time when each row was created. The column's content would not change in case of an UPDATE statement. The fourth answer is also wrong simply because there are no such built in functions.

Question 3 - Date and Time Handling

What is the result of the following statement:
select cast(floor(cast(@MyDateTime as float)) as datetime)

1. Next Sunday relative to @MyDateTime.
2. Only date part of @MyDateTime.
3. Only time part of @MyDateTime.
4. Doesn't work because you cannot cast float  to datetime.

The second answer Only date part of @MyDateTime is correct. If you cast a datetime value to float you get a floating point value representing the number of days since 1^st January 1900:

select cast(cast('1900-01-01 00:00:00' as datetime) as float) 

----------------------  
0  

(1 row(s) affected)

The fractional digits represent the time:

select cast(cast('1900-01-01 12:00:00' as datetime) as float) 

----------------------  
0,5  

(1 row(s) affected)

In contrast to SQL Server 2005 the version 2008 offers separate data types for date and time. Read more about these new data types in MSDN.

The statement shown above converts the datetime variable to float, removes the fractional digits using floor and converts the result back to datetime. Therefore it removes the time part of the datetime variable.

Question 4 - Aggregation

You can try this statement with Microsoft's sample database AdventureWorksDW.

Does the following statement work in SQL 2005?

select CustomerKey,  
       ProductKey,  
       sum(SalesAmount) as SumSalesAmount,  
       sum(SalesAmount) /  
         sum(sum(SalesAmount)) over ( partition by CustomerKey )  
         * 100 as SumSumSalesAmount  
from   dbo.FactInternetSales  
group by CustomerKey,  
       ProductKey  
order by CustomerKey,  
       ProductKey

1. Yes
2. No, syntax error
3. No, but SQL 2008 supports this syntax

This statement works in SQL 2005! You maybe know the keyword over from ranking functions. However, it is not quite commonly known that you can use over with traditional aggregation functions, too. Our sample shown above generates a sales statistics including the customer, the product and the revenue that each customer has generated with each product. The fourth column calculates the ratio of each product's revenue to the customer's total revenue.

Question 5 - SQLCLR

Which classes from the .NET Framework can be used in SQLCLR assemblies with permission set SAFE?

1. All of them. Permission set SAFE just restricts the use of unmanaged code.
2. None. To use classes from the .NET Framework in SQL Server you need at least permission set EXTERNAL.
3. Some of them are tested specifically for the use within SQL Server, they can be used.
4. Permission sets have nothing to do with SQLCLR. 

A list of assemblies that are loaded from GAC by SQL Server is available on MSDN.

The correct answer is Some of them are tested specifically for the use within SQL Server, they can be used. There is a hardcoded list of assemblies that SQL Server loads from the file system. These assemblies have undergone an extensive code review to ensure they don't destablize SQL Server.

However, some assemblies contain both supported and unsupported functions (e.g. mscorlib). For those cases Microsoft introduced the HostProtectionAttribute attribute. Types and members that are not allowed in SQL Server are decorated with this attribute. You can get a list of disallowed types and members in MSDN.

Question 6 - Database Snapshots

Copy-on-write (Source: MSDN)

How long does it approximately take to create a snapshot of a 100GB database on a state-of-the-art Quadcore server using RAID 5?

1. A few seconds.
2. Approximately 5 minutes.
3. Usually approximately one hour; depends on the load on the server.
4. More than one hour but it is running in the background.

The correct anser is A few seconds. Database snapshots use sparse files (read more about sparse files in Wikipedia). That means that the database is not copied when the snapshot is created. Data pages are copied immediately before they are modified in the source database. This mechanism is called copy-on-write. Therefore creating a database snapshot is very fast. You have to pay the performance price when you write to the source database.

Question 7 - TOP Clause

In which statements can you use the TOP clause in SQL Server 2005?

1. SELECT
2. SELECT, INSERT (if used with INSERT...SELECT syntax)
3. SELECT, INSERT, UPDATE, DELETE
4. None. TOP is new in SQL Server 2008

Parentheses that delimit expression in TOP is required in INSERT, UPDATE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but Microsoft does not recommend this.

The correct answer is SELECT, INSERT, UPDATE, DELETE. In SQL Server 2000 TOP was only supported in SELECT statements. Since SQL Server 2005 you can use TOP also with INSERT, UPDATE and DELETE. Here is an example for the use of TOP in a DELETE statement:

use tempdb;  

create table Orders ( OrderId int, CustId int, Revenue money );  

insert into Orders ( OrderId, CustId, Revenue )  
select 1, 1, 5000 union all  
select 2, 1, 1000 union all  
select 3, 2, 500 union all  
select 4, 2, 100 union all  
select 5, 3, 50 union all  
select 6, 3, 10;  

while exists ( select 1 from Orders where CustId = 1 )  
  delete top(1) from Orders where CustId = 1;  

select * from Orders;

You may ask yourself why someone would write such a strange script to delete all orders of a certain customer. Imagine the table Orders containing a very large amount of rows. If you would delete all orders of customer 1 in a single delete statement this would lead to a very large transaction. It could be hard to handle it (e.g. disk space for transaction log could be critical, stopping the transaction could take a long time, etc.). With delete top(n) you can split the large transaction into multiple smaller ones.