1 / 31

Module 2 Working with Data Types

Module 2 Working with Data Types. Module Overview. Using Data Types Working with Character Data Converting Data Types Working with Specialized Data Types. Lesson 1: Using Data Types. Introducing Data Types Exact Numeric Data Types Working with IDENTITY Approximate Numeric Data Types

hank
Download Presentation

Module 2 Working with Data Types

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 2 Working with Data Types

  2. Module Overview • Using Data Types • Working with Character Data • Converting Data Types • Working with Specialized Data Types

  3. Lesson 1: Using Data Types • Introducing Data Types • Exact Numeric Data Types • Working with IDENTITY • Approximate Numeric Data Types • Date and Time Data Types • Unique Identifiers • NULL or NOT NULL Columns • Demonstration 1A: Working with Numeric Data Types

  4. Introducing Data Types • Data types determine what can be stored • Constrains the type of data that an object can hold • Provides limits on the range of values • Data types apply to database columns, variables, expressions, and parameters • Critical to choose appropriate data type • Assists with query optimization • Provides a level of self-documentation • Three basic sets of data types • System data types • Alias data types • User-defined data types

  5. Exact Numeric Data Types • Wide variety of numeric types are supported • Vary in range, precision, and accuracy

  6. Working with IDENTITY • Property of a column • Specify a seed and an increment • Default seed and increment are both 1 • SCOPE_IDENTITY(), @@IDENTITY CREATETABLESales.Opportunity ( OpportunityIDintNOTNULL IDENTITY(1,1), Requirements nvarchar(50)NOTNULL, ReceivedDatedateNOTNULL, LikelyClosingDatedateNULL, SalespersonIDintNULL, Rating intNOTNULL );

  7. Approximate Numeric Data Types • Two approximate numeric types are supported • float is from float(1) to float(53) • float defaults to float(53) • real is fixed 4 byte storage • float and real not regularly used in business applications as they are not precise

  8. Date and Time Data Types • Rich set of options is available for storing date and time data • Need to be very careful of string literal formats of each • Large set of functions available for processing these data types

  9. Unique Identifiers • uniqueidentifier data type is typically used for storing GUID values • GUID is globally unique identifier • Storage is essentially a 128-bit integer but standard integer arithmetic is not supported • =, <>, <, >, <=, >= are supported along with NULL and NOT NULL checking • IDENTITY cannot be used • New values from NEWID() function • Common error is to store these as strings

  10. NULL or NOT NULL Columns • Can determine whether a value must be provided • Can be defined on columnsand parameters • Cannot be defined on variables • Often inappropriately defined CREATETABLESales.Opportunity ( OpportunityIDintNOTNULL, Requirements nvarchar(50)NOTNULL, ReceivedDatedateNOTNULL, LikelyClosingDatedateNULL, SalespersonIDintNULL, Rating intNOTNULL );

  11. Demonstration 1A: Working with Numeric Data Types In this demonstration you will see: • Work with IDENTITY values • Work with NULL • Insert GUIDs into a table

  12. Lesson 2: Working with Character Data • Understanding Unicode • Character Data Types • Understanding Collations • Demonstration 2A: Working with Character Data

  13. Understanding Unicode • Is a worldwide character-encoding standard • Simplifies software localization • Improves multilingual character processing • Is implemented in SQL Server as double-byte for Unicode types • Requires N prefix on constants • Uses LEN() to return number of characters, DATALENGTH() to return the number of bytes DECLARE @Hello nvarchar(10); SET @Hello =N'Hello'; SET @Hello =N'你好'; SET @Hello =N'こんにちは';

  14. Character Data Types • Fixed length, variable length, and large character data types • Single byte and double byte (Unicode) data types

  15. Understanding Collations • Collations in SQL Server control: • Code page that is used to store non-Unicode data • Rules that govern how SQL Server sorts and compares values for non-Unicode types • SQL Server supports a large number of collations, including case-sensitivity options • Collation settings can be determined at the instance, database, and column levels • Comparisons between data stored in different collations require specifying the collation to use for the comparison SELECT* FROMProduction.Product WHERE Name LIKEN'%ball%'COLLATE SQL_Latin1_General_Cp1_CS_AS;

  16. Demonstration 2A: Working with Character Data In this demonstration you will see: • How to work with Unicode and non Unicode data • How to work with collations

  17. Lesson 3: Converting Data Types • Using CAST • Using CONVERT • Implicit Data Conversion • Common Conversion Issues • Demonstration 3A: Common Conversion Issues

  18. Using CAST • Converts an expression of one data type to another in SQL Server • CAST is based on SQL standards SELECT'The list price is ' +CAST(ListPriceASvarchar(12)) ASListPriceMessage FROMProduction.Product WHEREListPriceBETWEEN 350.00 AND 400.00; SELECTCAST(SYSDATETIME()ASnvarchar(30));

  19. Using CONVERT • Converts an expression of one data type to another in SQL Server • Optionally allows providing a style • SQL Server specific extension to the SQL language SELECT'The list price is ' +CONVERT(varchar(12),ListPrice) ASListPriceMessage FROMProduction.Product WHEREListPriceBETWEEN 350.00 AND 400.00; SELECTCONVERT(varchar(8),SYSDATETIME(),112); SELECTCONVERT(char(8), 0x4E616d65, 0) AS'Style 0, binary to character';

  20. Implicit Data Conversion • When data isn't explicitly converted between types, implicit data conversion is attempted and is based on data type precedence. • Not all data types can be implicitly converted to all other data types DECLARE @Salary decimal(18,2)= 78000.00; DECLARE @Annual int= 50000; DECLARE @XmlDataxml; SET @Salary = @Annual; SET @Salary += @Annual; SET @XmlData='<Customers> <Customer CustomerID="10"/> </Customers>';

  21. Common Conversion Issues • Many common issues arise during data type conversions • Inappropriate values for the target data type • Value is out of range for the target data type • Value is truncated while being converted (sometimes silently) • Value is rounded while being converted (sometimes silently) • Value is changed while being converted (sometimes silently) • Assumptions are made about internal storage formats for data types • Some datetime conversions are non-deterministic and depend on language settings • Some parsing issues are hard to understand

  22. Demonstration 3A: Common Conversion Issues In the following demonstration you will see: • How to convert date data types explicitly • How language settings can affect date conversions • How data can be truncated during data type conversion • Issues that can arise with implicit conversion

  23. Lesson 4: Working with Specialized Data Types • timestamp and rowversion • Alias Data Types • Other Data Types • Demonstration 4A: rowversion Data Type

  24. timestamp and rowversion • rowversion assists in creating systems based on optimistic concurrency • Automatically changes value whenever a row is modified • Replaces timestamp data type • New value is always larger than the previous value and is unique within the database

  25. Alias Data Types • CREATE TYPE can be used to create alias types • Alias types are subtypes of existing system data types • Alias types can include the details of nullability • Often used to maintain consistency across data type usage in an application CREATETYPEProductNumber FROMnvarchar(20)NOTNULL; GO CREATETABLEProduction.ProductConversion ( ProductConversionIDintIDENTITY(1,1), FromProductProductNumber, ToProductProductNumber );

  26. Other Data Types

  27. Demonstration 4A: rowversion Data Type In this demonstration you will see: • How to use the rowversion data type

  28. Lab 2: Working with Data Types • Exercise 1: Choosing Appropriate Data Types • Exercise 2: Writing Queries With Data Type Conversions • Challenge Exercise 3: Designing and Creating Alias Data Types (Only if time permits) Logon information Estimated time: 45minutes

  29. Lab Scenario A new developer has sought your assistance in deciding which data types to use for three new tables she is designing. She presents you with a list of organizational data requirements for each table. You need to decide on appropriate data types for each item. You need to export some data from your existing system but while being exported, some of the columns need to be converted to alternate data types. If you have time, there is another issue that your manager would like you to address. She is concerned about a lack of consistency in the use of data types across the organization. At present, she is concerned about email addresses and phone numbers. You need to review the existing data types being used in the MarketDev database for this and create new data types that can be used in applications, to avoid this inconsistency.

  30. Lab Review • What data type should I use to store the number of seconds since midnight? • Which of the following columns are likely to be nullable: YTD_Sales, DateOfBirth?

  31. Module Review and Takeaways • Review Questions • Best Practices

More Related