Home » Create an index without redundant code [closed]

Create an index without redundant code [closed]

Solutons:


In SQL Server 2016, this is very easy, you just need to make a choice between having simple scripts or enjoying whatever performance you’ve actually observed from DROP_EXISTING (is this quantifiable? Have you tested it?).

CREATE TABLE dbo.what(i int, INDEX x(i));
GO

DROP INDEX IF EXISTS dbo.what.x;
GO

CREATE INDEX x ON dbo.what(i DESC);
GO

So, your requirements are:

  • Create all indexes on the list, dropping and replacing existing indexes.
  • If the index does exist, use WITH (DROP_EXISTING = ON).
  • No repetition of the code to create the index.

I only see two options that would do this:

Option 1: Dynamic SQL

Build the basic CREATE INDEX command; then, if the index exists, tack on the DROP_EXISTING clause.

DECLARE @stmt NVARCHAR(MAX);

SET @stmt = N'
CREATE NONCLUSTERED INDEX [IX_a]
       ON [dbo].[animals]([BioNr] ASC)
       INCLUDE([ID], [Currency])'
+ CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
    THEN N' WITH (DROP_EXISTING = ON)'
    ELSE N''
  END
+ N';'
;

EXECUTE sp_executesql @stmt;

Obviously, this solution has some issues of its own. In general, you have to maintain the statements as strings. The biggest issues here are that you have to remember to double any single quotes that should be in the statement. As the sample statement has none, this may not turn out to be an issue, but it must be noted. In particular, the statement must be altered if need other WITH options set. Again, in this example, that’s not an issue. If all statements did have existing WITH options to include, it would be a huge issue either (though the statement would need tweaked:

SET @stmt = N'
CREATE NONCLUSTERED INDEX [IX_a]
       ON [dbo].[animals]([BioNr] ASC)
       INCLUDE([ID], [Currency]) WITH (DATA_COMPRESSION = PAGE'
+ CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
    THEN N', DROP_EXISTING = ON'
    ELSE N''
  END
+ N');'
;

Option 2: create a “dummy” index

Note that I wouldn’t recommend this; it’s actually at least as hard to maintain as simply having two copies of the CREATE INDEX statement, and causes extra work on the system. However, if you would expect that the index would almost always be there, it might be useful.

First, you create an index with the name you want (the columns don’t matter); then, replace it with the index you actually want.

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
    CREATE NONCLUSTERED INDEX [IX_a]
           ON [dbo].[animals] ([BioNr] ASC);

CREATE NONCLUSTERED INDEX [IX_a]
       ON [dbo].[animals]([BioNr] ASC)
       INCLUDE([ID], [Currency]) WITH (DROP_EXISTING = ON);

As I said, I wouldn’t recommend it, but it would (technically, at least) meet your requirements, so I thought I should mention it.

Final Note

It’s probably worth posting a CONNECT item, requesting that DROP_EXISTING = ON be allowed whether the index exists or not (if you can’t find an existing one, at least). It seems entirely reasonable for the option to simply be ignored if the index doesn’t exist.

I didn’t have a simple approach to this; instead I wrote a “master” program [BuildIndexes] which takes a table name and an index/statistic name term (default ‘%’), then for the table calls procedure [BuildIndexes_NameOfThisTable] which contains all the CREATE statements for indexes and statistics on t able [NameOfThisTable]. Well, likely to be table [B1_001].[dbo].[NameOfThisTable_01_2016], which is more worthwhile.

My program (which is currently private) finds and drops any matching-name indexes on the table first; an alternative for you would be to include a comment-type token /wde/ inside the index statement, which of course is contained in a string called using [sp_executesql], and if the index exists then – optionally – replace /wde/ with ‘WITH (DROP_EXISTING = ON)’. In my case, a reason to not do that (maybe?) was that I might have decided to change the index definition, then run the procedure to apply the change.

Related Solutions

Explaining computational complexity theory

Hoooo, doctoral comp flashback. Okay, here goes. We start with the idea of a decision problem, a problem for which an algorithm can always answer "yes" or "no." We also need the idea of two models of computer (Turing machine, really): deterministic and...

Building a multi-level menu for umbraco

First off, no need pass the a parent parameter around. The context will transport this information. Here is the XSL stylesheet that should solve your problem: <!-- update this variable on how deep your menu should be --> <xsl:variable...

How to generate a random string?

My favorite way to do it is by using /dev/urandom together with tr to delete unwanted characters. For instance, to get only digits and letters: tr -dc A-Za-z0-9 </dev/urandom | head -c 13 ; echo '' Alternatively, to include more characters from the OWASP...

How to copy a file from a remote server to a local machine?

The syntax for scp is: If you are on the computer from which you want to send file to a remote computer: scp /file/to/send username@remote:/where/to/put Here the remote can be a FQDN or an IP address. On the other hand if you are on the computer wanting to...

What is the difference between curl and wget?

The main differences are: wget's major strong side compared to curl is its ability to download recursively. wget is command line only. There's no lib or anything, but curl's features are powered by libcurl. curl supports FTP, FTPS, HTTP, HTTPS, SCP, SFTP, TFTP,...

Using ‘sed’ to find and replace [duplicate]

sed is the stream editor, in that you can use | (pipe) to send standard streams (STDIN and STDOUT specifically) through sed and alter them programmatically on the fly, making it a handy tool in the Unix philosophy tradition; but can edit files directly, too,...

How do I loop through only directories in bash?

You can specify a slash at the end to match only directories: for d in */ ; do echo "$d" done If you want to exclude symlinks, use a test to continue the loop if the current entry is a link. You need to remove the trailing slash from the name in order for -L to...

How to clear journalctl

The self maintenance method is to vacuum the logs by size or time. Retain only the past two days: journalctl --vacuum-time=2d Retain only the past 500 MB: journalctl --vacuum-size=500M man journalctl for more information. You don't typically clear the journal...

How can I run a command which will survive terminal close?

One of the following 2 should work: $ nohup redshift & or $ redshift & $ disown See the following for a bit more information on how this works: man nohup help disown Difference between nohup, disown and & (be sure to read the comments too) If your...

Get exit status of process that’s piped to another

bash and zsh have an array variable that holds the exit status of each element (command) of the last pipeline executed by the shell. If you are using bash, the array is called PIPESTATUS (case matters!) and the array indicies start at zero: $ false | true $...

Execute vs Read bit. How do directory permissions in Linux work?

When applying permissions to directories on Linux, the permission bits have different meanings than on regular files. The read bit (r) allows the affected user to list the files within the directory The write bit (w) allows the affected user to create, rename,...

What are the pros and cons of Vim and Emacs? [closed]

I use both, although if I had to choose one, I know which one I would pick. Still, I'll try to make an objective comparison on a few issues. Available everywhere? If you're a professional system administrator who works with Unix systems, or a power user on...

How do I use pushd and popd commands?

pushd, popd, and dirs are shell builtins which allow you manipulate the directory stack. This can be used to change directories but return to the directory from which you came. For example start up with the following directories: $ pwd /home/saml/somedir $ ls...

How to forward X over SSH to run graphics applications remotely?

X11 forwarding needs to be enabled on both the client side and the server side. On the client side, the -X (capital X) option to ssh enables X11 forwarding, and you can make this the default (for all connections or for a specific connection) with ForwardX11 yes...

What does “LC_ALL=C” do?

LC_ALL is the environment variable that overrides all the other localisation settings (except $LANGUAGE under some circumstances). Different aspects of localisations (like the thousand separator or decimal point character, character set, sorting order, month,...

What is a bind mount?

What is a bind mount? A bind mount is an alternate view of a directory tree. Classically, mounting creates a view of a storage device as a directory tree. A bind mount instead takes an existing directory tree and replicates it under a different point. The...

Turn off buffering in pipe

Another way to skin this cat is to use the stdbuf program, which is part of the GNU Coreutils (FreeBSD also has its own one). stdbuf -i0 -o0 -e0 command This turns off buffering completely for input, output and error. For some applications, line buffering may...