Create Stored Procedure to create Indexes?

Question

Post reply

Create Stored Procedure to create Indexes?

jbalbo

SSCertifiable

Points: 7583
More actions
April 17, 2020 at 3:45 pm

#3743777

Hi
Having an issue creating a SP to create indexes for an overnight job on a replicated DB that does not supply non-clustered Indexes
or any other ideas would be appreciated....
The Create index command work fine by themselves I just wanted to add them all in an SP

Thanks

tried
USE [DBname];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Non-Clustered_Indexes...]
AS
CREATE NONCLUSTERED INDEX.......
CREATE NONCLUSTERED INDEX.......

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16157 More actions · Answer 1

and what is your issue?

what you said does not give us any detail other than what you intend on doing.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

I agree with frederico_fonseca, this should work. I see no reason why that stored procedure wouldn't work unless you need it to create all of the indexes dynamically in which case that is going to be incredibly difficult.

Also, depending on how many indexes and how many rows you may end up creating a lot of blocking.

I am a little confused about the process though - shouldn't those index creation tasks be a one-time thing or are you dropping all of the indexes nightly?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

jbalbo SSCertifiable Points: 7583 More actions · Answer 3

Hi

Yes the overnight refresh drop nonclustered idexes, thats why I would run the job after the refresh

I have multiple

CREATE NONCLUSTERED INDEX [ix....

GO

CREATE NONCLUSTERED INDEX [ix....

GO

when I add the header

USE [dbname];

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spname]

AS

it just run the query(creates the indexes) and doesn't create the stored procedure

frederico_fonseca SSCoach Points: 16157 More actions · Answer 4

if I understand what you are trying to say it seems that you don't know how to create a stored procedure

so instead of putting a few snippets from your code looks like why don't you add the full create proc as a text file here so we can see what you are doing wrong.

jbalbo SSCertifiable Points: 7583 More actions · Answer 5

USE [Database];
GO

/****** Object:  StoredProcedure  [dbo].[Non-Clustered_Indexes]    Script Date: 4/16/2020 4:34:21 PM ******/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[Non-Clustered_IndexesB]
AS
     BEGIN
         CREATE NONCLUSTERED INDEX [ix_Service Note 2010 Selections_Parent] ON [database].[dbo].[CBFS Service Note 2010 Selections]([Parent] ASC) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
         GO;
         CREATE NONCLUSTERED INDEX [ix_Progress Note Selections_Parent] ON [database].[dbo].[CBHI Progress Note Selections]([Parent] ASC) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
         GO;
         CREATE NONCLUSTERED INDEX [IX_Appointment_BillingInformation_Client Payer Coverage] ON [database].[dbo].[eHana_Appointment_BillingInformation]([Client Payer Coverage] ASC) INCLUDE([ObjectID]) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
         GO;
         CREATE NONCLUSTERED INDEX [IX_Appointment_BillingInformation_ObjectID] ON [database].[dbo].[eHana_Appointment_BillingInformation]([ObjectID] ASC) INCLUDE([Procedure Code]) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
         GO;
         CREATE NONCLUSTERED INDEX [IX_Appointment_BillingInformation_ProviderID] ON [database].[dbo].[eHana_Appointment_BillingInformation]([Billing Provider] ASC) INCLUDE([ObjectID]) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
         GO;
     END;

frederico_fonseca SSCoach Points: 16157 More actions · Answer 6

yup.. you do need to go and learn the basics of T-SQL.

remove all "GO" statements that are after the create procedure.

But as Brian mentioned why this? once created the indexes remain created unless you are dropping them.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

What does this 'overnight refresh' process actually do?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbalbo SSCertifiable Points: 7583 More actions · Answer 8

To be honest the vendor won't tell use. It is transaction replication for reporting

All I know is they won't turn on nonclustered inxedes in replication

thus the recreation each night

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 9

Is the vendor tool dropping the indexes OR are you dropping them?

If it is you, why not disable them and then rebuild rather than dropping and recreating?

Do you need the indexes? If the vendor tool doesn't support nonclustered indexes, maybe they have a reason for that (apart from slower inserts/updates/deletes).

My advice would be to reach out to the vendor to see what other clients do rather than trying to "fix" a vendor process. One vendor we purchased software from decided that turning OFF page locks on an index was a good idea and it broke the reorganize I was doing on their indexes. The response from the vendor was that turning off page locks was for performance and that I should stop doing any maintenance on their system apart from backups. Everything else was "handled" by them and to stop doing index maintenance.

TheTL;DR version of the above is - Check with the vendor what other clients do. You may be able to disable the indexes and rebuild them instead of dropping and recreating which is likely to be a much more simple process AND can be dynamic so if the vendor tool pushes across another table due to an update, you can automagically handle it. The drop and recreate method will require more maintenance on your part.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

jbalbo wrote:

To be honest the vendor won't tell use. It is transaction replication for reporting
All I know is they won't turn on nonclustered inxedes in replication
thus the recreation each night

I'd be looking for a new vendor. I damned sure wouldn't let them work on my databases if they're going to cop such an attitude while demonstrating such ignorance. Like the robot used to say, "DANGER! DANGER!"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)