quote:

---

Originally posted by pdrg

a) not worth changing an nvarchar(20) to (14) - varchar (and the unicode equivelent nvarchar) has a byte to indicate length as a part of the datatype, so an nvarchar(20) takes no more disk space than an nvarchar(14) unless you're truncating your data.

b) Nulls - if there are no nulls in the source data, just turn it on again after the impoirt, and all should be well. If it's an identity field (like an autonumber), you may run into problems, but it sounds like it's a varchar, so should be no problem. If there ARE nulls in that field already, you need to do a manual data scrub toi make sure it's all good

hth

---

quote:

---

Source: ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/udb9/html/f308784b-564e-4aa3-924f-4e3bc2fcee6b.htm

The value NULL means the data value for the column is unknown or not available. NULL is not synonymous with zero (numeric or binary value), a zero-length string, or blank (character value). Rather, null values allow you to distinguish between an entry of zero (numeric columns) or blank (character columns) and a nonentry (NULL for both numeric and character columns).

NULL can be entered in a column for which null values are permitted (as specified in the CREATE TABLE statement) in two ways:

Microsoft SQL Server 2005 automatically enters the value NULL if no data is entered and there is no default or DEFAULT constraint on the column or data type.


The user can explicitly enter the value NULL by typing NULL without quotation marks. If the word NULL is typed into a character column with quotation marks, it is treated as the letters N, U, L, and L, not as a null value.


When null values are retrieved, an application typically displays a string such as NULL, or (NULL), or (null) in the appropriate position. For example, the Color column of the Product table allows null values:

Example:

SELECT ProductID, Name, Color
FROM AdventureWorks.Production.Product
WHERE Color IS NULL


Comparing Null Values

Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:

Example:

ytd_sales > NULL


The following comparison also yields UNKNOWN any time the variable contains the value NULL:

Example:

ytd_sales > @MyVariable


Use the IS NULL or IS NOT NULL clauses to test for a NULL value. This can add complexity to the WHERE clause. For example, the TerritoryID column in the AdventureWorks Customer table allows null values. If a SELECT statement is to test for null values in addition to others, it must include an IS NULL clause:

Example:

SELECT CustomerID, AccountNumber, TerritoryID
FROM AdventureWorks.Sales.Customer
WHERE TerritoryID IN (1, 2, 3)
OR TerritoryID IS NULL


Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL. Also, a comparison of two expressions that have both evaluated to null values yields TRUE. With ANSI_NULLS set OFF, the following SELECT statement returns all the rows in the Customer table for which Region is a null value:

Example:

SELECT CustomerID, AccountNumber, TerritoryID
FROM AdventureWorks.Sales.Customer
WHERE TerritoryID = NULL


Regardless of the ANSI_NULLS setting, Null values are always considered equal for the purposes of the ORDER BY, GROUP BY, and DISTINCT keywords. Also, a unique index or UNIQUE constraint that allows NULL can contain only one row with a NULL key value. A subsequent row with NULL is rejected. A primary key cannot have NULL in any column that is part of the key.

Computations involving NULL evaluate to NULL because the result must be UNKNOWN if any of the factors is unknown. For example, column1 + 1 evaluates to NULL if column1 is NULL.

When the columns being searched include those defined as allowing null values, you can find null or nonnull values in the database with the following pattern:

Example:

WHERE column_name IS [NOT] NULL

---