Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

SQL Champ - A Quiz to Prove Your SQL Knowlege

Rate me:
Please Sign up or sign in to vote.
4.42/5 (10 votes)
30 Mar 2008CPOL7 min read 45K   25   3
software architects invites 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.

SQLChampLogo.png

Introduction

software architects 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 will get the background information about the correct answers.

You haven't done the test by now? Try it first and come back to read about the answers later!

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

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

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.

sqlQuizQuestion1.png

Port settings in SQL Server Configuration Manager

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-in functions.

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:

SQL
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 is created. The column's content would not change in the case of an UPDATE statement. The fourth answer is also wrong simply because there are no such built-in functions.

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

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 the date part of @MyDateTime.
  3. Only the 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 1st January 1900:

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

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

(1 row(s) affected)

The fractional digits represent the time:

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

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

(1 row(s) affected)

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. In contrast to SQL Server 2005, version 2008 offers separate data types for date and time. Read more about these new data types in MSDN.

Question 4 - Aggregation

Does the following statement work in SQL Server 2005?

SQL
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 Server 2008 supports this syntax

This statement works in SQL Server 2005! You may 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.

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

Question 5 - SQLCLR

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

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

The correct answer is Some of them are tested specifically for 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. A list of assemblies that are loaded from GAC by SQL Server is available on MSDN.

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 the disallowed types and members in MSDN.

Question 6 - Database Snapshots

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 an hour but it is running in the background.

The correct answer 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.

CopyOnWrite.gif

Copy-on-write (Source: MSDN)

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 the INSERT...SELECT syntax)
  3. SELECT, INSERT, UPDATE, DELETE
  4. None. TOP is new in SQL Server 2008

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:

SQL
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., the disk space for the 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.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
software architects
Austria Austria
Hi, my name is Rainer Stropek. I am living a small city named Traun in Austria. Since 1993 I have worked as a developer and IT consultant focusing on building database oriented solutions. After being a freelancer for more than six years I founded a small IT consulting company together with some partners in 1999. In 2007 my friend Karin and I decided that we wanted to build a business based on COTS (component off-the-shelf) software. As a result we founded "software architects" and developed the time tracking software "time cockpit" (http://www.timecockpit.com). If you want to know more about our companies check out my blogs at http://www.software-architects.com and http://www.timecockpit.com or take a look at my profile in XING (http://www.openbc.com/hp/Rainer_Stropek2/).

I graduated the Higher Technical School for MIS at Leonding (A) in 1993. After that I started to study MIS at the Johannes Kepler University Linz (A). Unfortunately I had to stop my study because at that time it was incompatible with my work. In 2005 I finally finished my BSc (Hons) in Computing at the University of Derby (UK). Currently I focus on IT consulting, development, training and giving speeches in the area of .NET and WPF, SQL Server and Data Warehousing.

Comments and Discussions

 
GeneralNice Pin
jomet1-Apr-08 2:29
jomet1-Apr-08 2:29 
GeneralFun and Informative Pin
homerbush31-Mar-08 7:48
homerbush31-Mar-08 7:48 
Generalgood Pin
canozurdo31-Mar-08 4:53
canozurdo31-Mar-08 4:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.