Post reply

Scripting CREATE INDEX Statements Automatically From Your Tables

  • November 27, 2012 at 12:39 am

    #285210

    Comments posted to this topic are about the item Scripting CREATE INDEX Statements Automatically From Your Tables

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • November 27, 2012 at 6:50 am

    #1562655

    Is there a script to move CLUSTERED INDEXES to another filegroup

  • November 27, 2012 at 6:56 am

    #1562660

    No - clustered indexes deliberately omitted as often these will be wrapped up with your CREATE TABLE scripts (i.e. when using CREATE TABLE myTable ( this INT PRIMARY KEY ) or ALTER TABLE myTable ADD CONSTRAINT pk_this PRIMARY KEY ( this ) )

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • December 10, 2012 at 12:37 pm

    #1567150

    I had to modify the example code very slightly to get the code to run:

    INSERT INTO test.TableWithIndexes

    VALUES(1, 'Defrabuliser', 'Blue', '2009-04-30', 19.99, 0),

    (15, 'Unbobulator', 'Green', '2012-01-13', 85.00, 0),

    (22, 'Disbibulator', NULL, '2012-11-18', 12.50, 0),

    (89, 'Bishbosher', 'Orange', '2008-05-23', 109.99, 1),

    (101, 'Jambasher', 'Yellow', '2001-03-03', 3.99, 0)

    -- create PK/clustered index with padding/fill factor

    GO

    /****** Object: Index [ix_pk_productId] Script Date: 12/10/2012 11:51:43 AM ******/

    ALTER TABLE [test].[TableWithIndexes] ADD CONSTRAINT [ix_pk_productId] PRIMARY KEY CLUSTERED

    (

    [productId] ASC

    )WITH (PAD_INDEX = ON, FILLFACTOR=90, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

  • June 4, 2015 at 2:23 am

    #1802909

    Very handy script.

    However, I found a little bug, and here is a recommendation for the little change in the script.

    Replace ip.[column_name] with '['+ ip.[column_name]+']' in two places.

    Having column names in square brackets makes omitting failures when reserved words (e.g. create, delete,...) are used for them.

    Igor Micev,My blog: www.igormicev.com

  • May 5, 2016 at 7:26 am

    #1876839

    Igor Micev (6/4/2015)

    Very handy script.

    However, I found a little bug, and here is a recommendation for the little change in the script.

    Replace ip.[column_name] with '['+ ip.[column_name]+']' in two places.

    Having column names in square brackets makes omitting failures when reserved words (e.g. create, delete,...) are used for them.

    Thanks for the tip.

  • May 5, 2016 at 7:26 am

    #1876840

    Thanks for the script.

Viewing 7 posts - 1 through 7 (of 7 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy