CREATE TABLE fails by default in MS SQL Server due to Index = True being the default

[ZacharyMcGuire]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional

from sqlmodel import Field, SQLModel, create_engine

# DATABASE CONFIG
DB_DRIVER = "ODBC Driver 17 for SQL Server"
DB_HOST = "some_sql_server"
DB_DATABASE = "a_database"

engine = create_engine(
    f"mssql+pyodbc://@{DB_HOST}/{DB_DATABASE}?&driver={DB_DRIVER}"
)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


SQLModel.metadata.create_all(engine)

Description

• Create a Hero model
• Attempt to create_all(engine), where the engine is configured against SQL Server.

(pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'secret_name' in table 'hero' is of a type that is invalid for use as a key column in an index. (1919) (SQLExecDirectW)")
[SQL: CREATE INDEX ix_hero_secret_name ON hero (secret_name)]
(Background on this error at: https://sqlalche.me/e/14/f405)

I believe this is due to the column being VARCHAR(MAX) which MS SQL Server does not see as a column that can be used for indexing.

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

Python 3.9.6

Additional Context

Happy to write up a PR, but I think the default for indexing either needs to be switched to False by default, or implement some form of dialect check.

P.S. I don't contribue much on GitHub, so please let me know if there's any further context/assistance I can provde. 😄

[ZacharyMcGuire]

My mistake, this duplicates #9 and is already raised by #11

[tiangolo]

Thanks @ZacharyMcGuire! 🔍

This should be solved in #205, the new docs explaining indexes are here: https://sqlmodel.tiangolo.com/tutorial/indexes/

Indexes are now opt-in and not created by default. You can read more about it all in that PR.

It is now released as part of SQLModel 0.0.6. 🎉