Create new DB index

[ronnie_scott]

Does anyone know if we're allowed to add new db indexes for B1 fields (not a UDF) to a B1 table, like ORDR?

Also, by checking the "Create Index" flag in UDF Management, does it actually create a db index in SQL? It doesn't seem so...

[Former Member]

I don't know exactly what the 'index' flag does on a userfield, but apparently nothing in SQL.

If what you want is a real SQL index, than create them with the 'keys' window/button.

(See the SAP note mentioned above). It at first had me puzzled too, but then i read the note more carefully (the 'workaround') part. This also changes the type back to nvarchar(x), instead of max.

[Former Member]

Hello,

I also would like to know if the following is allowed on UserTables:

Our DB-Installer for each Add-on calls an SQL-script for each index.

For example a unique index on table @XXX_ISO_CAT with two columns U_CardCode and U_CatName which are of type text.

IF NOT EXISTS (SELECT [name] FROM sys.indexes WHERE [name]='XXX_I_ISO_CAT_00') BEGIN
	DECLARE @FieldLength AS INT

	SELECT @FieldLength=SizeID FROM [CUFD] WHERE TableID='@XXX_ISO_CAT' AND AliasID='CardCode'
	EXEC ('ALTER TABLE [@XXX_ISO_CAT] ALTER COLUMN [U_CardCode] NVARCHAR(' + @FieldLength + ')')

	SELECT @FieldLength=SizeID FROM [CUFD] WHERE TableID='@XXX_ISO_CAT' AND AliasID='CatName'
	EXEC ('ALTER TABLE [@XXX_ISO_CAT] ALTER COLUMN [U_CatName] NVARCHAR(' + @FieldLength + ')')

	CREATE UNIQUE NONCLUSTERED INDEX [XXX_I_ISO_CAT_00] ON [dbo].[@XXX_ISO_CAT]
	(
		[U_CardCode] ASC,
		[U_CatName] ASC
	)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

END

[former_member201110]

Hi Ronnie,

SAP's support terms don't allow you to add your own indexes to system tables. I believe in some cases it could cause issues with patches and upgrades and could cause unexpected errors in the application. Perhaps you could create a view that reads data from ORDR and index the view instead?

If I tick the Create Index for an existing UDF on a system table or UDT and then click on Update then SQL Profiler shows that the CUFD table is updated (so the UDF management screen shows that an index has been created for the field) but no command is sent to SQL to actually create an index so the SQL table structure remains the same. This is on SBO 2007A patch 46. Seems broken

One other thing to note is that in SBO 2007A all alphanumeric fields will be created as nvarchar(max) datatypes which cannot be indexed in SQL.

Kind Regards,

Owen

[Eneveux]

Owen,

The nvarchar(max) is true for all but those fields that are noted as text. See SAP Note# 1227965.

Eddy

[ronnie_scott]

Thanks for the answer, Owen. If I create a view and add an index there, the view does not get effected during an upgrade? Also, what is SAP's policy on stored procedures? Are we allowed to have our own?

As to the alphanumeric or nvarchar(max) UDF's, if it's not actually indexed in SQL, what does the B1 "indexed" field do to help with performance, if it helps at all?