Demo

Document Sample
Demo Powered By Docstoc
					Demo Script
Date and Time Support in SQL Server 2008
Lab version:    1.0.0

Last updated:   10/13/2011
CONTENTS

OVERVIEW ................................................................................................................................................... 3
 Key Messages ........................................................................................................................................... 3
   Key Technologies ...................................................................................................................................... 3
   Time Estimates ......................................................................................................................................... 3

SETUP AND CONFIGURATION .................................................................................................................. 4
       Task 1 – Running the Configuration Wizard ......................................................................................... 4

OPENING STATEMENT............................................................................................................................... 6

STEP-BY-STEP WALKTHROUGH .............................................................................................................. 7
  Segment #1 – Using the New Date and Time Data Types ....................................................................... 8
   Segment #2 – Programming Global Applications with DateTimeOffset ................................................. 15

SUMMARY .................................................................................................................................................. 21
Overview
This document provides setup documentation, step-by-step instructions, and a written script for demonstrating the use of new date and time
data types in SQL Server 2008.

Key Messages
   1. The new date and time data types in SQL Server 2008 offer increased range and precision and are ANSI SQL compatible.

    2. Separate date and time data types minimize storage space requirements for applications that need only date or time information.
       Moreover, the variable precision of the new time data type increases storage savings in exchange for reduced accuracy.

    3. The new data types are mostly compatible with the original date and time data types and use the same Transact-SQL functions.

    4. The datetimeoffset data type allows you to handle date and time information in global applications that use data that originates from
       different time zones.


Key Technologies
This demo uses the following technologies:
    1. Microsoft SQL Server 2008 R2 Express (or higher)


Time Estimates
    Estimated time for setting up and configuring the demo: 2 min

       Estimated time to complete the demo: 15 min
Setup and Configuration
System Requirements
       Microsoft SQL Server 2008 R2 Express (or higher)

       Microsoft SQL Server Management Studio

       Microsoft Visual Web Developer 2008 (Express Edition) or Microsoft Visual Studio 2008

The setup and configuration for this demo involves running the Configuration Wizard tool included with the training kit. After going through the
demo, you can run the Cleanup.cmd script to revert any changes made to your system. If you intend to present other demos in the training kit,
avoid running this script until you have completed them (the script will drop the SQLTrainingKitDB and Mondial databases used elsewhere in the
training kit).


Task 1 – Running the Configuration Wizard
The following steps describe how to run the Configuration Wizard tool included with the demo to verify that all the prerequisites are properly
installed.
    1. Browse to the setup folder in the Source folder of this demo, and run the Setup.cmd script. This script will launch the Configuration
       Wizard for the demo. The Configuration Wizard is designed to check your computer to ensure that it is properly configured with all of
       the dependencies needed to run the demo.
    2. Click through the steps in the Configuration Wizard to get to the Detecting Required Software step; a scan of prerequisites will be
       performed on your computer. If you do not have the necessary dependencies, install them using the links provided by the tool and
       rescan your computer.
   Figure 1
   Dependencies check completed successfully


3. Once the required software is properly installed click Next to configure your computer for this demo. A script will be executed to create
   the Mondial database (dropping it previously if necessary) and populate it with data and another to create the server alias
   ('SQLServerTrainingKitAlias') used in this demo to establish a connection with the database.
       Figure 2
       Configuration Wizard tasks




Opening Statement
SQL Server 2008 introduces four new date and time data types. These types enable applications to have separate types for date or time,
increased year range, increased fractional second precision and time zone offset support.

In addition to the date and time data types, which are date only and time only types respectively, there are two new data types that can hold
both date and time information: datetime2 and datetimeoffset. The datetime2 data type is the ANSI standard version of SQL Server’s datetime
and has a larger date range and variable precision. The datetimeoffset data type is similar, but it also has a time zone offset from Coordinated
Universal Time (UTC) time.
The current range of the datetime data type spans the years 1753 to 9999, and the accuracy in seconds of its time part is 0.003 seconds. In the
new data types, the date part has been expanded to range from the year 1 to the year 9999, and the time part has an improved accuracy of 100
nanoseconds.

During the demo, I will show you the value of the new date data type in a real world scenario using the Mondial sample database. Mondial is a
sample database often referenced in books, lectures and courses and based on information from a variety of sources including the CIA World
Factbook. It contains geo-political facts about countries, including their dates of independence. In the past, we could not represent this
information using SQL Server’s datetime data type because many countries have dates of independence that precede 1753.

In addition, we will explore the date and time functions in Transact-SQL and you will learn how to use these functions with the new date and
time data types. We will also examine the higher precision system date and time functions introduced in SQL Server 2008.

Finally, we will see the new datetimeoffset data type using ADO.NET. I will present an application that operates across different time zones,
which is a common scenario in global applications, were users are geographically distributed. Using a sample application, we will see how users
can enter date and time information in any time zone that can be viewed by others in a different time zone using their local time.




Step-by-Step Walkthrough
This demo is composed of the following segments:
    1. Using the New Date and Time Data Types

    2. Programming Global Applications with DateTimeOffset


 Note: The SQL statements shown in the Action column provide the necessary context for a demo step. You may find that the SQL demo script
 file in the code folder contains additional statements, where appropriate, to verify the existence and conditionally create required database
 objects. These statements have been omitted in the document for simplicity.
Segment #1 – Using the New Date and Time Data Types
Action                                        Script                                        Screenshot


 1. In SQL Server Management                     Now, I will show you some of the new
    Studio, open the                              date and time data types and how
    CreateMondialDatabase.sql file                they are used. To do this, I am going
    located inside the                            to use the Mondial database with
    setup\scripts\tasks\sql folder at the         SQL Server.
    Source folder of this demo.
                                                 I have a script here that creates the
 2. Scroll down the script file to show           Mondial database and populates it
    the Transact-SQL statement that               with data. If we examine the script,
    creates the politics table and point to       we see that a couple of tables have
    the Independence column.                      columns with date information. One of
                                                  the drawbacks of using this database
                                                  with SQL Server in the past was the
                                                  fact that it contains information about
                                                  the date of independence of each
                                                  country. This can be a problem for
                                                  countries whose date of
                                                  independence precedes 1753. These
                                                  dates fall outside the range of the
                                                  original datetime data type.
                                                  Consequently, not every country can
                                                  be represented using this data type
                                                  unless you stored the independence
                                                  date as a string.
3. In SQL Server Management                   If we examine the script that
   Studio, open the                            populates the database with data, we
   PopulateMondialDatabase.sql file            see that it contains dates in a format
   located inside the                          that SQL Server does not support,
   setup\scripts\tasks\sql folder at the       namely day-month-year separated by
   Source folder of this demo.                 spaces (dd mm yyyy).

4. Scroll down the script file to show        To load this data, the script uses a
   the Transact-SQL statements that            simple workaround that consists of
   load data into the politics table.          inserting all the rows into a temporary
                                               table. The script then retrieves the
                                               data in the temporary table and
                                               replaces the spaces in the date string
                                               with dashes before inserting each row
                                               into the target table. Notice that the
                                               script changes the language settings
                                               to British English before doing this, so
                                               that SQL Server can interpret the
                                               date strings correctly as day-month-
                                               year, and then restores the language
                                               setting back to its original value.
 5. In SQL Server Management                  We will now use this database and
    Studio, open the                           see how we can handle the date and
    MondialQueries.sql file located            time information.
    inside the code folder at the Source
    folder of this demo.
                                              One of the things that I can find out
                                               about the Mondial database is which
  6. Select the USE statement and click        countries have dates of
     Execute ( ) to use the Mondial            independence that are earlier than
     database.                                 1753. These are the countries that we
T-SQL                                          could not represent using the
                                               datetime data type with SQL Server
USE Mondial
                                               in the past.
GO

                                              Notice that there are quite a few of
  7. Highlight the SELECT statement and        them; in fact, at least nine countries
     click Execute ( ) to retrieve             would have caused problems had we
     countries whose independence date         used the original datetime data type
     is earlier than 1753.                     with SQL Server.
T-SQL
SELECT c.name, p.* FROM politics
p
JOIN country c
ON p.country = c.code
WHERE YEAR(Independence) < 1753
ORDER BY Independence
GO
  8. Highlight the SELECT statement and        Now, we will apply some date and
     click Execute ( ) to show the use of       time functions against the new date
     some of the date functions.                data type.
T-SQL
SELECT c.name AS [Country Name],               In this query, we convert the
       CONVERT(VARCHAR(12),                     Independence date column to a string
p.Independence, 107)                            using a specific format. We also have
AS [Independence Date],                         a column that uses the DATEDIFF
                                                function to calculate the number of
       DATEDIFF(YEAR,
                                                years since the independence of a
p.Independence, GETDATE())                      country. Notice that we are able to
AS [Years Independent (appox)],                 use the new DATE data type with the
       p.Government                             “old” Transact-SQL functions.
FROM politics p
JOIN country c
ON p.country = c.code
WHERE YEAR(Independence) < 1753
ORDER BY Independence
GO
  9. In SQL Server Management                   Next, we will examine some of the
     Studio, open the                            characteristics of the new date and
     DateTimeFormats.sql file located            time formats. I am going to set the
     inside the code folder at the Source        DATEFORMAT to day-month-year
     folder of this demo.                        (dmy), which is the format used in
                                                 England or France. In SQL Server
  10. Select the SET DATEFORMAT                  2008, there has been a subtle change
                                                 in one of the string formats used to
     statement and click Execute ( ) to          represent date and times literals.
     change the DATEFORMAT to day-
     month-year.
T-SQL                                           The string in this example is the only
SET DATEFORMAT dmy                               date literal format recognized by
                                                 ANSI but, does the string 2008-12-05
GO                                                                       th
                                                 represent December 5 or is it May
                                                    th
                                                 12 ? In fact, if you are using the day-
  11. Select the DECLARE and SELECT              month-year (dmy) date format, it
     statements and click Execute ( ) to         depends on whether you use the
     show how the datetime and                   datetime or datetime2 data types. In
     datetime2 data types interpret a date       the case of the datetime data type,
     literal.                                    this string is DATEFORMAT
T-SQL                                            dependent.
SET DATEFORMAT dmy
DECLARE @dt datetime = '2008-12-                After we execute this sequence of
                                                 statements, notice that datetime and
05'
                                                 datetime2 produce different results.
DECLARE @dt2 datetime2 = '2008-                  This is the only string assignment
12-05'                                           format that has been changed to
SELECT MONTH(@dt) AS [Month-                     correspond with the ANSI standard.
Datetime], DAY(@dt)
    AS [Day-Datetime]
SELECT MONTH(@dt2) AS [Month-
Datetime2], DAY(@dt2)
    AS [Day-Datetime2]
GO
  12. Highlight the DECLARE and              The original datetime data type
     SELECT statements and click              supports simple arithmetic, so you
     Execute ( ) to use integer               can add, for example, one day to a
     arithmetic on a datetime variable.       datetime variable using integer
T-SQL                                         addition.
DECLARE @dt datetime = '2008-12-
05'
SELECT @dt + 1
GO




  13. Highlight the DECLARE and              However, integer arithmetic is not
     SELECT statements and click              allowed for the new date and time
     Execute ( ) to show how integer          data types. If we try to do this with
     arithmetic is not allowed for            datetime2, it fails with the message
     datetime2 variables.                     "datetime2 is incompatible with int".
T-SQL
DECLARE @dt2 datetime = '2008-12-
05'
SELECT @dt2 + 1
GO
  14. Highlight the DECLARE and              If you need to arithmetic with
     SELECT statements and click              datetime2, you have to use the
     Execute ( ) to show how to use           DATEADD function.
     DATE functions to do simple
     arithmetic on datetime2 variables.
T-SQL
DECLARE @dt2 datetime2(7) =
'2008-12-05'
SELECT DATEADD(d, 1, @dt2)
GO




  15. Highlight the DECLARE and              Finally, we examine the system date
     SELECT statements and click              and time functions. You can use the
     Execute ( ) to show how the              original system date and time
     GETDATE function can be used with        functions with both datetime and
     both datetime and datetime2 data         datetime2 data types. Notice that
     types.                                   when using the old functions, you
T-SQL                                         always get the same date and time
                                              values, regardless of whether you are
DECLARE @dt datetime = GETDATE();
                                              using a datetime or a datetime2. Both
DECLARE @dt2 datetime2(7) =                   results have the same accuracy. We
GETDATE();                                    would expect datetime2 to exhibit
SELECT @dt AS [GetDate-DateTime],             better precision than datetime. The
@dt2 AS [GetDate-DateTime2]                   reason that both values are the same
GO                                            is because we are using the original
                                              GETDATE system function to
                                              initialize it, and its precision is only
  16. Draw attention to the values            0.003 seconds.
     returned for both columns and how
     they are equal.
  17. Highlight the DECLARE and            If we use the new SYSDATETIME
     SELECT statements and click            function instead, we can see that it is
     Execute ( ) to show how the            compatible with the old datetime data
     SYSDATETIME function can be            type, but it truncates its value to
     used with both datetime and            correspond to the time range of the
     datetime2 data types.                  old datetime data type. In the case of
T-SQL                                       the new datetime2 data type, no
                                            truncation occurs and we obtain the
DECLARE @dt datetime =
                                            full precision. Notice that the value of
SYSDATETIME();                              these two results differs in the least
DECLARE @dt2 datetime2(7) =                 significant digits.
SYSDATETIME();
SELECT @dt AS [Sysdatetime-
                                           To sum up, you can use the original
DateTime], @dt2                             function with the new data type or the
    AS [Sysdatetime-DateTime2]              new function with the old data type,
GO                                          but bear in mind that by using the
                                            new function, you will get different
                                            values than with the old function.
 18. Draw attention to the values
    returned for both columns and how
    they are different.




Segment #2 – Programming Global Applications with DateTimeOffset
Action                                  Script                                         Screenshot
  1. In SQL Server Management                    I am going to demonstrate a project
     Studio, open the DateTimeTest.sql            called EnterDates that illustrates how
     file located inside the code folder at       to use some of the new date and time
     the Source folder of this demo.              support in ADO.NET. This simple
                                                  Windows Forms application allows
  2. If you have not previously created           you to select the time zone that you
     the SQLTrainingKitDB database                are in and inserts rows into a
     while completing another demo in             database table using that time zone.
     this training kit, highlight the             Before I run the program, I need to
     CREATE DATABASE statement and                set up the database that we are going
                                                  to use.
     click Execute ( ) to do so now.
T-SQL
CREATE DATABASE SQLTrainingKitDB                 To use the program, we will set up a
                                                  table with a very simple structure to
GO
                                                  show how you can use the
                                                  datetimeoffset data type. This table
  3. Select the USE and CREATE                    has a datetimecol column, which has
     TABLE statements and click                   a datetimeoffset type. It also has an
     Execute ( ) to create table datetest         EnteredTZ column, which is a string
     in the SQLTrainingKitDB database.            column where we keep track of the
T-SQL                                             time zone used by the client. Note
                                                  that SQL Server provides no direct
USE SQLTrainingKitDB                              support for time zones, so it does not
GO                                                take into consideration daylight
                                                  savings time, for example.
CREATE TABLE datetest (
   id integer IDENTITY PRIMARY
KEY,
   datetimecol datetimeoffset,
   EnteredTZ varchar(40)
);
GO
4. In Microsoft Visual Web Developer           Next, I will give you a brief overview
   2008, open the EnterDates                    of the program code. The program
   solution. To do this, browse to the          uses a connection string named Test
   code\EnterDates folder in the                defined in the configuration file that
   Source folder of this demo and               connects to our SQL Server 2008
   double-click the EnterDates.sln file.        database.

5. Highlight the connection string in the
   code editor.


6. Highlight the code that retrieves           In the form's constructor, it retrieves a
   available time zones and initializes         collection of available time zones and
   the drop down list control.                  uses it to populate a drop down list.
                                                To do this, it uses the TimeZoneInfo
                                                class introduced in .NET Framework
                                                3.5. We will use this list to determine
                                                the time zone where the client is
                                                located.
7. Show the AddButton_Click event             The form has a button labeled Add a
   handler.                                    Row. The handler for this button
                                               inserts rows into the datetest table.
8. Show the conversion of the current
   date and time to the target time           The code uses the
   zone.                                       SqlDbType.DateTimeOffset class
                                               introduced to support the new
9. Discuss the use of                          datetimeoffset type in SQL Server
   SqlDbType.DateTimeOffset.                   2008. This type is fully aligned with
                                               the DateTimeOffset type introduced
                                               in .NET Framework 3.5.

                                              The code retrieves the current date
                                               and time and converts it to the
                                               selected time zone. It retrieves a
                                               localized display name for the time
                                               zone, taking into account daylight
                                               saving time, if appropriate, and writes
                                               both values to the database table.


10. Show the FetchButton_Click                The form also has a button labeled
   event handler.                              Fetch Rows, so that it can display
                                               the rows that we enter. The handler
11. Highlight and discuss the query that       for the button executes a query that
                                               retrieves rows from the datetest table.
   uses the SWITCHOFFSET function.
                                               Because the information is stored as
                                               a datetimeoffset, a calculation is
                                               needed to convert the dates from
                                               their original time zone to the local
                                               time zone.

                                              Fortunately, SQL Server 2008
                                               provides the SWITCHOFFSET
                                               Transact-SQL function that does this
                                               for us. The program uses this function
                                               in the query to change the stored
                                                date and time to the local time zone
                                                of the client. This is a common
                                                requirement for global applications
                                                that operate in different time zones.

12. In Solution Explorer, right-click the      We will now run the application to
   project node, point to Debug and             show how it works. The scenario
   select Start new instance. Repeat            depicted is that of three people
   the procedure to start three separate        working simultaneously with the
   instances.                                   application and located in different
                                                time zones. The application records
13. Select the following time zones for         the date and time of any rows that
   the three instances:                         users insert. To show this scenario, I
                                                am going to start three separate
   ◦   Dublin, Edinburgh, Lisbon,               instances of the EnterDates program.
       London
   ◦   Chennai, Kolkata, Mumbai, New           When you start the program, it allows
       Delhi                                    you to select the time zone where you
                                                pretend to be located. We will choose
   ◦   Pacific Time (US & Canada)               one of the instances to be in London
                                                and using Greenwich Mean Time, a
                                                second instance located in Redmond
                                                and using Pacific Time, which is GMT
                                                -08:00, and the last instance in
                                                Mumbai, India, which is GMT +05:30.
14. Click Add A Row in each of the      When you record the date and time,
   program instances in quick            you actually record the date and time
   succession to insert rows with        on the server. We want the
   different time zones and              application to display these dates and
   approximately equal time.             times using the time zone that
                                         corresponds to each user.

                                        Suppose the person in London enters
                                         a row, then the person in Redmond
                                         enters a second row, and finally, the
                                         person in India enters a third row.
                                         Notice that these rows were entered
                                         within seconds of each other.
  15. Click Fetch Rows for each of the           If we look at the information that we
     program instances and show how               entered in one of these instances, for
     the EnteredDateTime column                   example the London instance, we can
     reflects the time zone of each client.       see in the datetimecol column the
                                                  date and time that these rows were
                                                  entered and the offset of the time
                                                  zone where the user that entered this
                                                  information was located. However,
                                                  we can also use SWITCHOFFSET as
                                                  you can see in the EnteredDateTime
                                                  calculated column to display the
                                                  same information using our own time
                                                  zone.

                                                 Similarly, using the Redmond
                                                  instance, we can fetch all the rows. If
                                                  we examine the EnteredDateTime
                                                  column, it appears as if all these rows
                                                  were input at the same time in our
                                                  own time zone even though they
                                                  originated in completely different time
                                                  zones. The same is apparent in the
                                                  Mumbai instance.




Summary
In this demo, you reviewed the new date and time data types in SQL Server 2008 and their increased range and precision. You have seen how
these new data types are compatible with the system date and time functions available in previous releases. You learnt that SQL Server 2008
introduces new higher precision functions to retrieve the system date and time, and that these are compatible with the original date and time
data types, albeit with a loss of accuracy. Finally, you have explored the new datetimeoffset data type and seen how you can use this type in a
global application to display information entered in different time zones.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:12
posted:10/13/2011
language:English
pages:22