SQL Champ - A Quiz to Prove Your SQL Knowlege
SQL Champ - A Quiz to Prove Your SQL Knowlege
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
- Question 2 - Data Type
- Question 3 - Date and Time Handling
- Question 4 - Aggregation
- Question 5 - SQLCLR
- Question 6 - Database Snapshots
- Question 7 - TOP Clause
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?
- SQL Server uses a random port
- 1433 and 1434
- 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; seehttp://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.
You want to track date and time of the last write access (
UPDATE) per row. How can you achieve that?
- Add a
TIMESTAMPcolumn to the table; SQL Server will automatically track date and time of every change.
- Add a
DATETIMEcolumn to the table and assign
getdate()as the default value.
- Add a
DATETIMEcolumn to the table and write a trigger that sets its value.
- Add a
UNIQUEIDENTIFIERcolumn 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
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 ChangeTrackingTestset 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)yId MyDesc MyTimestampM---------------------------1 Test 0x00000000000007D4) affected) MyId(1 row(s) affected) (1 row(
sMyDesc 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.
What is the result of the following statement:
select cast(floor(cast(@MyDateTime as float)) asdatetime).
- Next Sunday relative to
- Only the date part of
- Only the time part of
- Doesn't work because you cannot cast
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:
select cast(cast('1900-01-01 00:00:00' as datetime) as float)----------------------01 row(s) affected)(
The fractional digits represent the time:
select cast(cast('1900-01-01 12:00:00' as datetime) as float)----------------------0,51 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.
Does the following statement work in SQL Server 2005?
select CustomerKey,ProductKey,sum(SalesAmount) as SumSalesAmount,sum(SalesAmount) /sum(sum(SalesAmount)) over ( partition by CustomerKey )* 100 as SumSumSalesAmountfrom dbo.FactInternetSalesgroup by CustomerKey,ProductKeyorder by CustomerKey,ProductKey
- No, syntax error
- 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.
Which classes from the .NET Framework can be used in SQLCLR assemblies with permission set to SAFE?
- All of them. Permission set to SAFE just restricts the use of unmanaged code.
- None. To use classes from the .NET Framework in SQL Server, you will at least need the permission set to EXTERNAL.
- Some of them are tested specifically for use within SQL Server, they can be used.
- 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.
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?
- A few seconds.
- Approximately 5 minutes.
- Usually approximately one hour; depends on the load on the server.
- 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.
In which statements can you use the
TOP clause in SQL Server 2005?
INSERT(if used with the
TOPis new in SQL Server 2008
The correct answer is SELECT, INSERT, UPDATE, DELETE. In SQL Server 2000,
TOP was only supported in
SELECTstatements. Since SQL Server 2005, you can use
TOP also with
DELETE. Here is an example for the use of
TOP in a
use tempdb;create table Orders ( OrderId int, CustId int, Revenue money );insert into Orders ( OrderId, CustId, Revenue )select 1, 1, 5000 union allselect 2, 1, 1000 union allselect 3, 2, 500 union allselect 4, 2, 100 union allselect 5, 3, 50 union allselect 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
deletetop(n), you can split the large transaction into multiple smaller ones.
Note: Parentheses that delimit expressions in
TOP is required in
DELETE statements. For backward compatibility, a
TOP expression without parentheses in
SELECT statements is supported, but Microsoft does not recommend this.