1. posterid:210455
  2. "Included Columns" in index definition? (MSSQL)

Re: "Included Columns" in index definition? (MSSQL)

Avgerinos
 

Hi Andy & Sherman

I prefer to define all tables (fields, indices and related attributes) with the Magic Data Dictionary.
Having all the app design and logic defined and primarily stored in one place (Magic), helped me keep a good control of my numerous apps during the last 35 years.
If I need an extra feature which is not directly supported by Magic, I always try to find a way to define within the Studio, instead of using e.g. the Management Studio (in the case of MS-SQL).
And this happens very rarely, since I always avoid "acrobatics" when designing a database.

My current need is to add an "Included Column" in addition to the "Indexed Columns of an MSSQL table.
"Included Columns" are the ones that are stored in the index (for speed optimization purposes only) without participating in it.
This can be easily done in Management studio, but still I would prefer this to be done within Magic.

My question to the group came because I was hoping that there is some "Additional DB Information" solution or something similar.

Thank you both for your replies
Best Regards
Avgerinos


On 21/12/2020 6:13 π.μ., Andy Jerison wrote:
It's okay to define indexes for SQL databases. If you let Magic create the tables, you should set them all as virtual indexes. I'd say the best practice is to make all indexes virtual. Magic will include ORDER BY clauses on selected indexes and the database will use its optimizer to process the queries efficiently. 

Maintaining a lot of indexes imposes serious overhead on the database. Most indexes we had to create in Pervasive aren't necessary in a decent SQL database. 

You should instead use the database's tuning tools to create indexes once your db is populated.

Andy 

On Sun, Dec 20, 2020, 13:42 sherman levine <sherman.levine@...> wrote:
Andy,

My own answer would be that I define an index whenever I want to see a subset of records displayed in a specific sequence and I don't want to do a runtime sort.

For example, show all the records created by user XXXX in datetime sequence.

Similarly, I define an index when I want to return a specific record in a link. (show me the most recent record created by user XXXX)

Is that inappropriate?

Thanks

Sherm

On 12/20/2020 1:30 PM, Andy Jerison wrote:
Why are you defining an index? 

On Sun, Dec 20, 2020, 12:46 Avgerinos <mento@...> wrote:
Hi magicians :-)

When defining an Index In the Data-Repository of XPA (v.3 or 4) and
besides the "Index Key Columns",
is there any way to specify the "Included Columns" when defining an
index for an MSSQL table?

Thanks in advance
Avgerinos
Join main@magicu-l.groups.io to automatically receive all group messages.