If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
How many MEMO fields allowed in a table?
On Thu, 13 Sep 2007 19:29:58 -0500, Michael Gramelspacher wrote:
If so, then Memo fields must be excluded as I have records with two Memo fields each in excess of 3,000 characters. On Thu, 13 Sep 2007 13:26:52 -0700, "Jeff Boyce" wrote: Access has a limit of (?)2,000 characters per record ... that would be why multiple text fields at 255 characters each would exceed the limit. If you'll provide a bit more specific description of the domain you are working in, the newsgroup readers may be able to offer alternate approaches. Regards Jeff Boyce Microsoft Office/Access MVP From Access Help + Specifications + Access Specifications + Tables Number of characters in a record (excluding Memo and OLE Object fields) 2,000 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#12
|
|||
|
|||
How many MEMO fields allowed in a table?
On Thu, 13 Sep 2007 19:55:32 -0500, Michael Gramelspacher
wrote: Why would I want memo fields to be indexed? I can get by perfectly well without indexes on them... but the question comes up surprisingly often in the newsgroups. John W. Vinson [MVP] |
#13
|
|||
|
|||
How many MEMO fields allowed in a table?
On 14 Sep, 01:55, Michael Gramelspacher wrote:
Why would I want memo fields to be indexed? I have almost no practical experience, and I really am wondering because I have read this many times before. One reason is the 255 chracter limit on NVARCHAR AND NCHAR columns. One government agency schema I must work with has a wide key; let's say it's up to 1000 characters. In Jet, this column would be best IMO as a MEMO column (yes I know it's blob data but surely that's better than scaling my own NCHAR/NVARCHR columns?) Values are unique in the domain so naturally I'd want a UNIQUE constraint in my db. The SQL DBMSs I work with all use indexes to implement such constraints, as does Jet. Also, I have to search, join, etc on this column so why _wouldn't_ I want to index it if possible? Hint: they agency chose such a wide key because the number of entities in the domain is vast (they have since realized they didn't need such a wide key but the seed has been sown) and users could have literally millions of values in the db... ...SQL Server Express... I'm glad *you* mentioned that g. In SQL Server I can create a calculated column to store a hash of the key value with a constraint to ensure the hash is a match for the value, and put an index (dupes allowed, obviously) on the hash column. I can then use both the value column and the hash column in a trigger to ensure uniqueness and the index on the hash column yields acceptable performance. Obviously, the wise may use both columns in regular SQL JOINs, WHERE clauses etc to ensure the index on the hash gets used. This is partially achievable in Jet (e.g. the hash column and index), more so in Access (e.g. validation rule to ensure the hash is a match) but not everything (e.g. the trigger to ensure uniqueness). Jamie. -- |
#14
|
|||
|
|||
How many MEMO fields allowed in a table?
On 13 Sep, 23:30, John W. Vinson
wrote: memos have disadvantages... sorting will truncate them to 255 bytes Picky, I know, but yours is a misstatement. To test: CREATE TABLE Test ( memo_col MEMO NOT NULL ) ; INSERT INTO Test (memo_col) VALUES ('123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90_JohnV') ; INSERT INTO Test (memo_col) VALUES ('123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90_Jamie') ; SELECT memo_col AS result1, LEN(result1) AS result1_bytes FROM Test ORDER BY memo_col ; result1_bytes returns 306 for both rows. Conclusion: sorting has not caused truncation. Jamie. -- |
#15
|
|||
|
|||
How many MEMO fields allowed in a table?
Jamie, a single-column key that is 1,000 characters wide seems absurd
in my mind. NVARCHAR and NCHAR are not recognized by most people as Access datatypes. And in SQL Server their length can be 4,000 characters according to Books on Line. An Access Text datatype is limited to 255 characters. I guess my vision does not extend much beyond the Access world. And I am still left wondering why I might want to index or sort a memo column. On Fri, 14 Sep 2007 08:29:20 -0000, Jamie Collins wrote: One reason is the 255 chracter limit on NVARCHAR AND NCHAR columns. One government agency schema I must work with has a wide key; let's say it's up to 1000 characters. In Jet, this column would be best IMO as a MEMO column (yes I know it's blob data but surely that's better than scaling my own NCHAR/NVARCHR columns?) |
#16
|
|||
|
|||
How many MEMO fields allowed in a table?
One thing I have run into a few times is when you do a UNION join between 2
tables that contain a memo column, the memo column gets truncated. There are ways around this but it can trip you up and may not be detected until the bug has existed for a long time when someone notices the truncation. "Michael Gramelspacher" wrote: Jamie, a single-column key that is 1,000 characters wide seems absurd in my mind. NVARCHAR and NCHAR are not recognized by most people as Access datatypes. And in SQL Server their length can be 4,000 characters according to Books on Line. An Access Text datatype is limited to 255 characters. I guess my vision does not extend much beyond the Access world. And I am still left wondering why I might want to index or sort a memo column. On Fri, 14 Sep 2007 08:29:20 -0000, Jamie Collins wrote: One reason is the 255 chracter limit on NVARCHAR AND NCHAR columns. One government agency schema I must work with has a wide key; let's say it's up to 1000 characters. In Jet, this column would be best IMO as a MEMO column (yes I know it's blob data but surely that's better than scaling my own NCHAR/NVARCHR columns?) |
#17
|
|||
|
|||
How many MEMO fields allowed in a table?
On Sep 14, 1:38 pm, Michael Gramelspacher wrote:
Jamie, a single-column key that is 1,000 characters wide seems absurd in my mind. I repeat: the example I gave is not my invention g! I am still left wondering why I might want to index or sort a memo column. I would hope it doesn't need too much thought/imagination to find an example within your own domain of something in excess of 255 characters that may need to be unique e.g. URL, SQL statements' text (removing white space, comments, etc). NVARCHAR and NCHAR are not recognized by most people as Access datatypes. I really don't know what to make of that sentence Do you mean 'Jet data types' or do you really mean something pertaining to Access that has no direct meaning in Jet such as the 'hyperlink data type' (if that is indeed the correct phrase)? You corrected my misspelling of 'NVARCHAR' so are you saying that some people consider that fixed width nature of NCHAR and/or WITH COMPRESSION should be disregarded because they are not exposed in the Access user interface? Whatever, I think there is an issue of awareness he distinct varying- and fixed- width text data types exist in Jet, NVARCHAR and NCHAR are the commonly encountered keywords in the wider SQL world, these keywords are supported in Jet's ANSI-92 Query Mode, and the more we talk about them the more familiar they will become in the Access world. Jamie. -- |
|
Thread Tools | |
Display Modes | |
|
|