Geek Answers Handbook

Automatically Assigning SQL Server Indexes When Creating Tables

I am new to SQL Server (I learned SQL back in the late 1980s, if I recall DB2). Today I am integrating my database level in SQL, for starters, SQL Server.

To start. Like today, I will generate at runtime every database object, table objects, and indexes programmatically, as happens with almost every visual and data object in my projects. That is, I use visual design tools very limited.

Each column in my project has an external description file (each user has a profile that contains these files), as well as with a database key, and for visual objects, as for an effect, like positioning, length, image mask, size font i.e. dynamic forms. Almost all windows, grids, filters are created at runtime, like most of my database connections.

I created a small test “machine” for creating tables in this environment, and well done, it is very simple to create tables inside the program (I use delphi and ADO)

The problem I am facing is when I mark the column as "auto-increment" or as Identity in SQL Server, or if I describe the column as a primary key, then SQL Server Management Studio automatically creates the index or key.

This would be normal if I could control the name that it gives to this index or key.

An example of such situations:

  AdoCommand.CommandText := Str_SQL; TRY AdoCommand.Execute; FINALLY NotiFy_TimeOut ('Table was created', wait_for_one_sec); END;

My database engine creates this SQL script, which I pass to the Str_SQL line above:

  CREATE TABLE GENGITFL ( NR INT NOT NULL IDENTITY, GJM CHAR(3) NOT NULL, HEITI VARCHAR(25) NOT NULL, KAUPG REAL NULL, SOLUG REAL NULL, TOLLG REAL NULL, DUMMY VARCHAR(20) NULL, UNIQUE (GJM), PRIMARY KEY (GJM) )

SQL Server automatically creates these two indexes:

  • PK__GENGITFL__C51F17260A463F49
  • UQ__GENGITFL__C51F17277FA3E6E6

I do not want to use these names for these files, I would prefer names like:

  • IDX_GENGITFL_GJM
  • IDX_GENGITFL_NR

The reason should be obvious in the light of my introduction, the execution mechanism cannot automatically create these names, and I believe that this is not an option to search which index files are available in the system database. If my external description says that there should be an index, I would just like to create the names for the index automatically using the prefix, IDX_ next table name and the last name or field name with an underscore between them, like IDX_GENGITFL_GJM , etc.

Hope someone understands my poor english and presentation. I'm pretty rusty in english.

thanks

Edit: after using marc_s, my SQL "script" looks like this:

  CREATE TABLE GENGITFL ( NR INT NOT NULL IDENTITY, GJM CHAR(3) NOT NULL, HEITI VARCHAR(25) NOT NULL, KAUPG REAL NULL, SOLUG REAL NULL, TOLLG REAL NULL, DUMMY VARCHAR(20) NULL, CONSTRAINT IDX_GENGITFL_NR UNIQUE (NR), CONSTRAINT IDX_GENGITFL_GJM PRIMARY KEY (GJM), ) CREATE INDEX IDX_GENGITFL_HEITI ON GENGITFL (HEITI)

Thanks again.

+4
source share
1 answer

If you do not need the default names for the system - then just specify your own! :

  CREATE TABLE GENGITFL ( NR INT NOT NULL IDENTITY, GJM CHAR(3) NOT NULL, HEITI VARCHAR(25) NOT NULL, KAUPG REAL NULL, SOLUG REAL NULL, TOLLG REAL NULL, DUMMY VARCHAR(20) NULL, CONSTRAINT IDX_GENGITFL_NR UNIQUE (GJM), CONSTRAINT IDX_GENGITFL_GJM PRIMARY KEY (GJM) )

See those CONSTRAINT (yourownnamehere) before UNIQUE and PRIMARY KEY ?

Now your limitations are called you .

+11
source

More articles:


All Articles