I've just started with mssql
(after using sqlite, postgresql and mysql, I'm not very happy at the moment ;-)
I guess utf-16 would be the best way to go for the future.
(as my current database is quit large, I think at the moment I'll stick to
windows-1252 coding ;-)
further more in my humble opinion
NTEXT should be replaced by NVARCHAR(MAX)
IMAGE should be replaced by VARBINARY(MAX)
Stef Mientkie
On 02-03-2011 19:12, Ross Peoples wrote:
> Good question. If web2py is using unicode and going for internationalization,
> then NVARCHAR is the way to code. It stores all text as unicode, but it
> requires twice the storage space of VARCHAR because it uses two bytes to
> encode each character.
>
> And I have to ask: what is the motivation for using CHAR(1) over BIT for
> boolean fields?
>
> On Mar 2, 2011, at 12:49 PM, Massimo Di Pierro wrote:
>
>> Makes sense. The name is not the issue. The issue is what should the
>> mssql2005 types be:
>> types = {
>> 'boolean': 'BIT',
>> 'string': 'VARCHAR(%(length)s)',
>> 'text': 'TEXT',
>> 'password': 'VARCHAR(%(length)s)',
>> 'blob': 'IMAGE',
>> 'upload': 'VARCHAR(%(length)s)',
>> 'integer': 'INT',
>> 'double': 'FLOAT',
>> 'decimal': 'NUMERIC(%(precision)s,%(scale)s)',
>> 'date': 'DATETIME',
>> 'time': 'CHAR(8)',
>> 'datetime': 'DATETIME',
>> 'id': 'INT IDENTITY PRIMARY KEY',
>> 'reference': 'INT, CONSTRAINT %(constraint_name)s FOREIGN KEY
>> (%(field_name)s) REFERENCES %(foreign_key)s ON DELETE %
>> (on_delete_action)s',
>> 'reference FK': ', CONSTRAINT FK_%(constraint_name)s FOREIGN KEY (%
>> (field_name)s) REFERENCES %(foreign_key)s ON DELETE %
>> (on_delete_action)s',
>> 'reference TFK': ' CONSTRAINT FK_%(foreign_table)s_PK FOREIGN
>> KEY (%(field_name)s) REFERENCES %(foreign_table)s (%(foreign_key)s) ON
>> DELETE %(on_delete_action)s',
>> 'list:integer': 'TEXT',
>> 'list:string': 'TEXT',
>> 'list:reference': 'TEXT',
>> }
>>
>>
>> or
>>
>> types = {
>> 'boolean': 'CHAR(1)',
>> 'string': 'NVARCHAR(%(length)s)',
>> 'text': 'NTEXT',
>> 'password': 'NVARCHAR(%(length)s)',
>> 'blob': 'IMAGE',
>> 'upload': 'NVARCHAR(%(length)s)',
>> 'integer': 'INT',
>> 'double': 'FLOAT',
>> 'decimal': 'NUMERIC(%(precision)s,%(scale)s)',
>> 'date': 'DATETIME',
>> 'time': 'CHAR(8)',
>> 'datetime': 'DATETIME',
>> 'id': 'INT IDENTITY PRIMARY KEY',
>> 'reference': 'INT, CONSTRAINT %(constraint_name)s FOREIGN KEY
>> (%(field_name)s) REFERENCES %(foreign_key)s ON DELETE %
>> (on_delete_action)s',
>> 'reference FK': ', CONSTRAINT FK_%(constraint_name)s FOREIGN KEY (%
>> (field_name)s) REFERENCES %(foreign_key)s ON DELETE %
>> (on_delete_action)s',
>> 'reference TFK': ' CONSTRAINT FK_%(foreign_table)s_PK FOREIGN
>> KEY (%(field_name)s) REFERENCES %(foreign_table)s (%(foreign_key)s) ON
>> DELETE %(on_delete_action)s',
>> 'list:integer': 'NTEXT',
>> 'list:string': 'NTEXT',
>> 'list:reference': 'NTEXT',
>> }
>>
>> and should the string representation in SQL be 'value' or N'value' ?
>>
>>
>>
>> On Mar 2, 11:38 am, Ross Peoples <[email protected]> wrote:
>>> Actually thinking about this a bit more, mssql should be used for MSSQL
>>> 2000, and mssql2005 instead of mssql2, and mssql3. The mssql will be
>>> compatible with 2000, 2005, and 2008, while mssql2005 will be compatible
>>> with 2005, 2008, and up. What do you think about that?
>>>
>>> On Mar 2, 2011, at 10:52 AM, Massimo Di Pierro wrote:
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>> Let's create a mssql3 that does it by the book (or do you think MS
>>>> will keep changing the API?)
>>>> On Mar 2, 9:11 am, Ross Peoples <[email protected]> wrote:
>>>>> The only I problem I see is that MSSQL 2000 will not understand
>>>>> varchar(max). For reference, there is a StackOverflow question that
>>>>> relates
>>>>> to
>>>>> this:http://stackoverflow.com/questions/737755/varcharmax-ms-sql-server-20...
