SQL Database Creation

[tyrant1969]

First, I apologize if this is not the proper forum, please point me in the correct direction if it isn't.

One of my co-workers has converted a database from Access 2003 to SQL 2000, the Access DB was used in conjunction with Coldfusion for a web based application. One of the fields in the Access DB was a memo field, it was used for verbose descriptions and details of issues/resolutions.

Since the conversion to SQL, there is a max limit of something like 4000 characters in that field.

Is there a field type in SQL DBs that effectively mimics the memo field of Access?

 

[SQLDenis]

text will store up to 2 billion characters

but it comes with a price..no len, order by etc
updates are a pain in the neck


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

On a side note: I suggest you check the fields in the database. The last time I upsizes an Access Database, it made a bunch of char fields. Instead of char fields, I suggest you change them to varchar fields instead. char fields will append spaces to the end of your string values where varchar won't.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

>>where varchar won't.

<smart alec>depending on ANSI_PADDING settings of course</smart alec>

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

I've always had problems with Access datatypes upgrading to nvarchar if I don't pay attention to my transformations, even integer/double types.

Varchar can go up to 8000 characters and still be ordered by and use the Len function, etc.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Have you ever used ANSI_PADDING? I haven't. In fact, I didn't even realize it existed.

So, after a couple minutes of research, I'd like to ask you to predict the results of this query.

SET ANSI_PADDING OFF

Create Table #Temp(Data Char(20))

Declare @Temp Table(Data Char(20))

Insert Into #Temp Values('Temp Table')
Insert Into @Temp Values('Table Variable')

Select '--' + Data + '--' 
From   #Temp

Union All

Select '--' + Data + '--'
From   @Temp

Drop Table #Temp

SET ANSI_PADDING ON

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

We don't use ANSI Padding here. We have enough issues finding our data without added the "pad" into things. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

George

yes i used it while creating indexed views (it's required to be on)
btw what about this

SET ANSI_PADDING OFF

Create Table #Temp(Data varChar(20))

Declare @Temp Table(Data varChar(20))

Insert Into #Temp Values('Temp Table        ')
Insert Into @Temp Values('Table Variable    ')

Select '--' + Data + '--' 
From   #Temp

Union All

Select '--' + Data + '--'
From   @Temp

Drop Table #Temp

SET ANSI_PADDING ON

When set to ON, trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column. When set to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are trimmed. This setting affects only the definition of new columns.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

I guessed that one correctly, but only because I was playing around with the ansi_padding a couple minutes ago. I just read that quote, too. I suppose whats bugging me is that the results are inconsistent between table variables and temp tables.

>> This setting affects only the definition of new columns.

Does that imply that columns in table variables are not actually columns because the ANSI_PADDING does not affect their behavior?

Important It is recommended that ANSI_PADDING always be set to ON. SET ANSI_PADDING must be ON when creating or manipulating indexes on computed columns or indexed views.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

I guess table variables are not really tables ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/