Using new data types in 2008
1 / 35

Using New Data Types in 2008 - PowerPoint PPT Presentation

  • Updated On :

Using New Data Types in 2008. Andrew Couch UK Access User Group ASC associates Flow of Presentation. Data types and products Share Point : Access & SQL Server SQL Server : Access & Visual Studio SQL Server New Data Types

Related searches for Using New Data Types in 2008

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Using New Data Types in 2008' - medwin

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Using new data types in 2008 l.jpg

Using New Data Types in 2008

Andrew Couch

UK Access User Group

ASC associates

Flow of presentation l.jpg
Flow of Presentation

  • Data types and products

  • Share Point : Access & SQL Server

  • SQL Server : Access & Visual Studio

  • SQL Server New Data Types

  • Updating Data & Multi-user Issues

  • Summary

Why are we interested in data types l.jpg
Why are we interested in Data Types?

  • What we do with data

    • Exchange

      • Converting and exchanging data between products

    • Applications that present data

      • Linking applications to back-end data sources

    • Applications that maintain data

      • updating data with applications linked to back-end data source

Which products will we consider l.jpg
Which Products will we consider?

  • Products

    • Access

      • Application and data source

    • SQL Server

      • data source and application components, also report services

    • Share Point

      • Application and data source (SQL Server)

    • Visual Studio

      • Application

What data types do the products support l.jpg
What Data Types do the Products support?

SQL Server (2008)

Access (2007)

Visual Studio (2005/8)


Multi-value fields

Share Point (2003)see next slide

n prefix for Unicode

Share point under the hood l.jpg
Share Point under the hood

Data is held in the AllUserData table which has a generic set of fields,


Access to share point data types moss l.jpg
Access to Share PointData Types; MOSS

Access 2007 share point l.jpg
Access (2007) & Share Point

  • Attachments (share point lists)

  • Multi-value fields (share point lists)

Share point a data object model wss l.jpg
Share Point a Data/Object Model; WSS


Access to sql server data type mappings l.jpg
Access to SQL Server Data Type Mappings

  • Yes/No (boolean) : bit

    • Access True -1, SQL Server True 1; also SQL Server boolean is tri-state 0, 1 and NULL

  • Text : varchar or nvarchar or char or nchar

  • Date/Time : datetime

  • Currency : money

  • Autonumber : int (identity property)

  • OLE Object : image, varbinary, filestream

  • Memo : text or varchar(max)

  • Number : various mappings

Yes no without a default translated to sql server l.jpg
Yes/No (without a default)Translated To SQL Server

Visual studio images and ole still a problem with varbinary max l.jpg
Visual Studio, Images and OLE still a problem with Varbinary(max)

some people would suggest that image data should not directly held in the

database and instead a text link is used to point to an external image file

which works around this issue

Mini summary l.jpg
Mini-summary Varbinary(max)

  • Access to SQL Server

    • MEMO to VARCHAR(max) or TEXT, probably TEXT safer bet but VARCHAR(max) more flexible


  • Visual Studio

    • VARCHAR(max) a better choice

    • OLE/IMAGE data doesn’t work for bound controls

Sql server 2005 l.jpg
SQL Server (2005) Varbinary(max)

  • XML data type

    • office requires program code using new SQL Native Client drivers to manipulate this

  • varchar(max), nvarchar(max)

    • advantages for programmers as can use as a variable (not possible with older text data type)

  • varbinary(max)

Date time data l.jpg
Date & Time Data Varbinary(max)

  • IBM DB2

    • DATE, TIME and TIMESTAMP date range 0001-01-01 to 9999-12-31

  • Oracle

    • DATE (includes time), TIMESTAMP, date range January 1 4713 BC to 9999-12-31

  • SQL Server (2000/2005)

    • DATETIME, SMALLDATETIME, date range 1753-01-01 to 9999-12-31 for DATETIME, when no date given defaults to 1st January 1900

  • Access – DATE/TIME

    • date range 1-1-100 to 9999-12-31,when no date given defaults to 30th December 1899 and hides date

  • SQL Server (2008)

    • DATE, TIME,DATETIME2, DATETIMEOFFSET range 0001-01-01 00:00:00:0000000 to 9999-12-31 23:59:59:9999999

Searching l.jpg
Searching Varbinary(max)

  • some limitations where fields are strings

Sql server 2008 l.jpg
SQL Server (2008) Varbinary(max)

  • Filestream

    • Unstructured large object data

  • DATE and TIME

    • Dates from 01/01/0001 to 31/12/9999

    • Times accurate to 00:00:00:0000000


    • New combined date and time data types with higher accuracy, Combines above, offset type includes time-zone data


    • Optimised for data hierarchies (special functions)


    • .NET-based spatial data

    • First deals with round-earth co-ordinates, second uses a flat-earth style of co-ordinates

    • Special functions

Geography l.jpg
Geography Varbinary(max)

  • It comes as no surprise that this renders in a binary type format and you can’t edit it

  • Stored procedures and pass through queries to the rescue!

With a little magic l.jpg
With a little Magic Varbinary(max)

Geography methods l.jpg
Geography Methods Varbinary(max)

Timestamps multiple users l.jpg
Timestamps & Multiple Users Varbinary(max)


data when





row versioning



Visual Studio does not utilise timestamps on bound controls the new LINQ will support timestamp concurrency

Access row versioning timestamps l.jpg
Access Row Versioning & Timestamps Varbinary(max)

Row Versioning


UPDATE "dbo"."authors"

SET "au_fname"=?

WHERE "au_id" = ? AND "au_lname" = ? AND "au_fname" = ?

AND "phone" = ? AND "address" = ? AND "city" = ? AND "state" = ? AND "zip" = ? AND "contract" = ?



UPDATE "dbo"."authors"

SET "au_fname"=?

WHERE "au_id" = ? AND "ts" = ?

Timestamps improve efficiency and remove potential problems

with rounding errors in numerical data

Example visual studio update handling null values l.jpg
Example Visual Studio Update handling Null Values Varbinary(max)

UpdateCommand = "UPDATE [Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE [CustomerID] = @original_CustomerID AND [CompanyName] = @original_CompanyName AND COALESCE([ContactName],'') = COALESCE(@original_ContactName,'') AND COALESCE([ContactTitle],'') = COALESCE(@original_ContactTitle,'') AND COALESCE([Address],'') = COALESCE(@original_Address,'') AND COALESCE([City],'') = COALESCE(@original_City,'') AND COALESCE([Region],'') = COALESCE(@original_Region,'') AND COALESCE([PostalCode],'') = COALESCE(@original_PostalCode,'') AND COALESCE([Country],'') = COALESCE(@original_Country,'') AND COALESCE([Phone],'') = COALESCE(@original_Phone,'') AND COALESCE([Fax],'') = COALESCE(@original_Fax,'')"

Summary l.jpg
Summary Varbinary(max)

  • We have a diverse set of data types in the different products

  • Need to test data types for product compatibility, don’t forget different versions and drivers can have an impact

  • Access and Share Point very good compatibility

  • Access and SQL Server good compatibility

  • Share Point data easily exported and linked MOSS but WSS more difficult

  • Please, Please can I have a nice tree presentation for XML data, then I may be tempted to use it!!!!!!

  • SQL Server 2008 new data types hold great promise, and it will possible to do a lot with then in Office 2007

Asc associates l.jpg
ASC Associates Varbinary(max)

  • Software consultancy specialising in Access, SQL Server and .net applications

  • Migration Upsizing SQL Tool

    • MUST

      • upsizes Access databases to SQL Server

    • MUST+SQL

      • converts Access queries to views and stored procedures

    • MUST+Web

      • converts Access forms to web forms in Visual Studio projects


  • [email protected]