What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 134 Stack Exchange communities.

UPDATE:

New version released:

http://meta.stackexchange.com/questions/45333/fast-multi-platform-data-dump-import-sql-2000-05-08-sqlite-mysql


I just released a new import utility that uses SqlBulkCopy and an EnumerableDataReader fed by a streamed sequence from an XmlReader.

It is fast and light and has more options than a body has a right to, yet is dead simple to use.

Give it a shot and PLEASE let me know how it works for you. My email is at the bottom of the readme.

StackOverflow Data Dump Import v.09

Quickly and cleanly imports StackOverflow Data Dump XML files into MS Sql Server 2000/05/08 using very little memory (<9mb working set).

For example, all 4 sites in the March dump imported in <5 minutes using less than 10mb ram. This is with no indexes. Suitable as a staging import.

With all indexes, keys and full text on post body and title and all tags split is around 17 minutes.

All SO sites share a common schema and this utility imports all sites into common tables, using a SiteId field along with the row Id as a composite key.

This results in large datasets, but enables interesting aggregation and mining scenarios. You may use the SiteId field to migrate data out of the import location after import, if your usage requires.

USAGE:

SODDI SOURCE CONNECTION [SCHEMA:schema name] [BATCH:batch size] [SPLIT] [DROP] 
   [UNIQUE] [ROWID] [PK] [INDEXES] [FULLTEXT] [ALL] [[META] [SO] [SU] [SF]]

Example command lines.

soddi "F:\Export-030110" "{connection string}"

This is the minimum required information. This simply creates tables with no keys or indexes and bulk copies the data. This is the fastest option, typically taking less than 5 minutes to import the entire March 2010 dump. Use this option if you simply need to get the data in a table for further massaging.

soddi "F:\Export-030110" "{connection string}" split schema:sx drop pk indexes so

This example will import only the StackOverflow data, dropping and creating the object in schema 'sx'. The surrogate identity PK, 'RowId', will be included and typical indexes will be created. This will allow you to import each site into it's own schema in a single database, if so desired.

NOTE: this is a usage example only. If you are going to enable any import options it would be safe to assume that you plan to query the data from the import location. In this case, it is probably a good idea to just specify ALL. If keys and indexes are created before import, the typical import time is just over 15 minutes. If indexes and keys are applied to a populated database it could take hours.

Screen shot of a basic import run No Options

Screen shot of a fully loaded run with keys, indexes and full text indexes. All Options

The latest data dump can be found at http://blog.stackoverflow.com/category/cc-wiki-dump/

share|improve this question
    
Could you post a diagram of the database schema this tool will create? (I'm mainly interested in the relationships between the tables, not the data columns.) –  Jon Seigel Mar 28 '10 at 20:00
    
The nature of the data source contraindicates explicit, enforced relationships. You see, I am certain that it is not simply a direct representation of the database but an abstraction. In practice, I am finding that while the data in the March dump is consistent and some relationships can be enforced, there are several implicit relations that conflict. So, the conclusion is that, while I provide the option to thoroughly index the tables, I am not enforcing any relations. I will post a diagram of the tables. –  Sky Sanders Mar 28 '10 at 21:00
    
@Jon - there ya go. –  Sky Sanders Mar 28 '10 at 22:12
    
kudos! well done -- it looks like we're going to be building a formally supported and MSFT supported Azure dump site that supports odata and we might want to leverage this! –  Jeff Atwood May 3 '10 at 1:49
    
@Jeff - new version here meta.stackexchange.com/questions/45333/… - post shows gui, but is console based. I can lib it pretty quicklike if/when. –  Sky Sanders May 3 '10 at 17:00

1 Answer 1

up vote 5 down vote accepted

Here is the schema used by SODDI (full size)

I am totally open to suggestions for useful indexes to add.

alt text

And here is the script used to build the database.

The if 0 = 1--XXX and --XXX are the plugs I remove from the script as per options supplied.

IF 0 = 1--DROP 
  BEGIN 
    IF EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Badges]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
      DROP TABLE [dbo].[Badges] 

    IF EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Comments]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
      DROP TABLE [dbo].[Comments] 

    IF EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Posts]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
      DROP TABLE [dbo].[Posts] 

    IF EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[PostsTags]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
      DROP TABLE [dbo].[PostsTags] 

    IF EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[PostTypes]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
      DROP TABLE [dbo].[PostTypes] 

    IF EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Users]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
      DROP TABLE [dbo].[Users] 

    IF EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Votes]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
      DROP TABLE [dbo].[Votes] 

    IF EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[VoteTypes]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
      DROP TABLE [dbo].[VoteTypes] 

    IF EXISTS (SELECT * 
               FROM   sysfulltextcatalogs ftc 
               WHERE  ftc.name = N'PostFullText') 
      EXEC dbo.sp_fulltext_catalog 
        @ftcat = N'PostFullText' , 
        @action = N'drop' 

    IF NOT EXISTS (SELECT * 
                   FROM   sysfulltextcatalogs ftc 
                   WHERE  ftc.name = N'PostFullText') 
      EXEC dbo.sp_fulltext_catalog 
        @ftcat = N'PostFullText' , 
        @action = N'create' 
  END 

SET ANSI_NULLS  ON 

SET QUOTED_IDENTIFIER  ON 

SET ANSI_PADDING  ON 

IF NOT EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[VoteTypes]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
  BEGIN 
    CREATE TABLE [dbo].[VoteTypes] ( 
      [Id]   [INT]    NOT NULL, 
      [Name] [VARCHAR](40)    NOT NULL, 
      CONSTRAINT [PK__VoteType__3214EC073864608B] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY])
    ON [PRIMARY] 
    SET ANSI_PADDING  OFF 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(1, 
           CONVERT(TEXT,N'AcceptedByOriginator')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(2, 
           CONVERT(TEXT,N'UpMod')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(3, 
           CONVERT(TEXT,N'DownMod')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(4, 
           CONVERT(TEXT,N'Offensive')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(5, 
           CONVERT(TEXT,N'Favorite')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(6, 
           CONVERT(TEXT,N'Close')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(7, 
           CONVERT(TEXT,N'Reopen')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(8, 
           CONVERT(TEXT,N'BountyStart')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(9, 
           CONVERT(TEXT,N'BountyClose')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(10, 
           CONVERT(TEXT,N'Deletion')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(11, 
           CONVERT(TEXT,N'Undeletion')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(12, 
           CONVERT(TEXT,N'Spam')) 

    INSERT [dbo].[VoteTypes] 
          ([Id], 
           [Name]) 
    VALUES(13, 
           CONVERT(TEXT,N'InformModerator')) 

  END 


SET ANSI_NULLS  ON 

SET QUOTED_IDENTIFIER  ON 

SET ANSI_PADDING  ON 

IF NOT EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Votes]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
  BEGIN 
    CREATE TABLE [dbo].[Votes] ( 
      [Id]           [INT]    NOT NULL, 
      [SiteId]       [VARCHAR](10)    NOT NULL, 
      [PostId]       [INT]    NOT NULL, 
      [UserId]       [INT]    NULL, 
      [BountyAmount] [INT]    NULL, 
      [VoteTypeId]   [INT]    NOT NULL, 
      [CreationDate] [DATETIME]    NOT NULL
      --ROWID, [RowId]        [INT]    IDENTITY ( 1 , 1 )    NOT NULL
      --ROWID--PK, CONSTRAINT [PK_Votes] PRIMARY KEY CLUSTERED ( [RowId] ASC ) ON [PRIMARY]
      --UNIQUE, CONSTRAINT [IX_Votes_Id_SiteId] UNIQUE NONCLUSTERED ([Id] ASC,[SiteId] ASC ) ON [PRIMARY]
      )
    ON [PRIMARY] 
  END 
ELSE 
    TRUNCATE TABLE [dbo].[Votes]

IF 0 = 1--INDEXES 
  BEGIN 
    SET ANSI_PADDING  OFF 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[Votes]') 
                          AND name = N'IX_Votes_PostId_SiteId') 
      CREATE NONCLUSTERED INDEX [IX_Votes_PostId_SiteId] ON [dbo].[Votes] ( 
            [PostId] ASC, 
            [SiteId] ASC) 
      ON [PRIMARY] 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[Votes]') 
                          AND name = N'IX_Votes_SiteId_UserId') 
      CREATE NONCLUSTERED INDEX [IX_Votes_SiteId_UserId] ON [dbo].[Votes] ( 
            [SiteId] ASC, 
            [UserId] ASC) 
      ON [PRIMARY] 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[Votes]') 
                          AND name = N'IX_Votes_VoteTypeId') 
      CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId] ON [dbo].[Votes] ( 
            [VoteTypeId] ASC) 
      ON [PRIMARY] 
  END 

SET ANSI_NULLS  ON 

SET QUOTED_IDENTIFIER  ON 

SET ANSI_PADDING  ON 

IF NOT EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Users]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
  BEGIN 
    CREATE TABLE [dbo].[Users] ( 
      [Id]             [INT]    NOT NULL, 
      [SiteId]         [VARCHAR](10)    NOT NULL, 
      [AboutMe]        [NVARCHAR](2100)    NULL, 
      [Age]            [INT]    NULL, 
      [CreationDate]   [DATETIME]    NOT NULL, 
      [DisplayName]    [NVARCHAR](40)    NOT NULL, 
      [DownVotes]      [INT]    NOT NULL, 
      [EmailHash]      [NVARCHAR](40)    NULL, 
      [LastAccessDate] [DATETIME]    NOT NULL, 
      [Location]       [NVARCHAR](100)    NULL, 
      [Reputation]     [INT]    NOT NULL, 
      [UpVotes]        [INT]    NOT NULL, 
      [Views]          [INT]    NOT NULL, 
      [WebsiteUrl]     [NVARCHAR](200)    NULL
      --ROWID, [RowId]          [INT]    IDENTITY ( 1 , 1 )    NOT NULL
      --ROWID--PK, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [RowId] ASC ) ON [PRIMARY]
      --UNIQUE, CONSTRAINT [IX_Users_Id_SiteId] UNIQUE NONCLUSTERED ([Id] ASC,[SiteId] ASC ) ON [PRIMARY]
      )
    ON [PRIMARY] 
  END 
ELSE 
    TRUNCATE TABLE [dbo].[Users]

IF 0 = 1--INDEXES 
  BEGIN 
    SET ANSI_PADDING  OFF 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[Users]') 
                          AND name = N'IX_Users_DisplayName') 
      CREATE NONCLUSTERED INDEX [IX_Users_DisplayName] ON [dbo].[Users] ( 
            [DisplayName] ASC) 
      ON [PRIMARY] 
  END 

SET ANSI_NULLS  ON 

SET QUOTED_IDENTIFIER  ON 

IF NOT EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[PostTypes]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
  BEGIN 
    CREATE TABLE [dbo].[PostTypes] ( 
      [Id]   [INT]    NOT NULL, 
      [Type] [NVARCHAR](10)    NOT NULL, 
      CONSTRAINT [PK_PostTypes] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY]) 
    ON [PRIMARY] 

    INSERT [dbo].[PostTypes] 
          ([Id], 
           [Type]) 
    VALUES(1, 
           N'Question') 

    INSERT [dbo].[PostTypes] 
          ([Id], 
           [Type]) 
    VALUES(2, 
           N'Answer') 

  END 


SET ANSI_NULLS  ON 

SET QUOTED_IDENTIFIER  ON 

SET ANSI_PADDING  ON 

IF NOT EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[PostsTags]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
  BEGIN 
    CREATE TABLE [dbo].[PostsTags] ( 
      [PostId] [INT]    NOT NULL, 
      [Tag]    [NVARCHAR](50)    NOT NULL, 
      [SiteId] [VARCHAR](10)    NOT NULL
      --ROWID, [RowId]  [INT]    IDENTITY ( 1 , 1 )    NOT NULL
      --ROWID--PK, CONSTRAINT [PK_PostsTags] PRIMARY KEY CLUSTERED ( [RowId] ASC ) ON [PRIMARY]
      --UNIQUE, CONSTRAINT [IX_PostsTags_PostId_SiteId_Tag] UNIQUE NONCLUSTERED ([PostId] ASC,[SiteId] ASC,[Tag] ASC ) ON [PRIMARY]
      )
    ON [PRIMARY] 
  END 
ELSE 
    TRUNCATE TABLE [dbo].[PostsTags]

IF 0 = 1--INDEXES 
  BEGIN 
    SET ANSI_PADDING  OFF 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[PostsTags]') 
                          AND name = N'IX_PostsTags_SiteId_PostId') 
      CREATE NONCLUSTERED INDEX [IX_PostsTags_SiteId_PostId] ON [dbo].[PostsTags] ( 
            [SiteId] ASC, 
            [PostId] ASC) 
      ON [PRIMARY] 
  END 

SET ANSI_NULLS  ON 

SET QUOTED_IDENTIFIER  ON 

SET ANSI_PADDING  ON 

IF NOT EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Posts]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
  BEGIN 
    CREATE TABLE [dbo].[Posts] ( 
      [Id]                    [INT]    NOT NULL, 
      [SiteId]                [VARCHAR](10)    NOT NULL, 
      [AcceptedAnswerId]      [INT]    NULL, 
      [AnswerCount]           [INT]    NULL, 
      [Body]                  [NTEXT] NOT NULL, 
      [ClosedDate]            [DATETIME]    NULL, 
      [CommentCount]          [INT]    NULL, 
      [CommunityOwnedDate]    [DATETIME]    NULL, 
      [CreationDate]          [DATETIME]    NOT NULL, 
      [FavoriteCount]         [INT]    NULL, 
      [LastActivityDate]      [DATETIME]    NOT NULL, 
      [LastEditDate]          [DATETIME]    NULL, 
      [LastEditorDisplayName] [NVARCHAR](40)    NULL, 
      [LastEditorUserId]      [INT]    NULL, 
      [OwnerUserId]           [INT]    NULL, 
      [ParentId]              [INT]    NULL, 
      [PostTypeId]            [INT]    NOT NULL, 
      [Score]                 [INT]    NOT NULL, 
      [Tags]                  [NVARCHAR](150)    NULL, 
      [Title]                 [NVARCHAR](250)    NULL, 
      [ViewCount]             [INT]    NOT NULL
      --ROWID, [RowId]                 [INT]    IDENTITY ( 1 , 1 )    NOT NULL
      --ROWID--PK, CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED ( [RowId] ASC ) ON [PRIMARY]
      --UNIQUE, CONSTRAINT [IX_Posts_Id_SiteId] UNIQUE NONCLUSTERED ([Id] ASC,[SiteId] ASC ) ON [PRIMARY]
      )
    ON [PRIMARY] 
  END 
ELSE 
    TRUNCATE TABLE [dbo].[Posts]

IF 0 = 1--INDEXES 
  BEGIN 
    SET ANSI_PADDING  OFF 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[Posts]') 
                          AND name = N'IX_Posts_PostType') 
      CREATE NONCLUSTERED INDEX [IX_Posts_PostType] ON [dbo].[Posts] ( 
            [PostTypeId] ASC) 
      ON [PRIMARY] 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[Posts]') 
                          AND name = N'IX_SiteId_UserId') 
      CREATE NONCLUSTERED INDEX [IX_SiteId_UserId] ON [dbo].[Posts] ( 
            [SiteId] ASC, 
            [OwnerUserId] ASC) 
      ON [PRIMARY] 
  END 

IF 0 = 1--FULLTEXT 
  BEGIN 
    IF (OBJECTPROPERTY(OBJECT_ID(N'[dbo].[Posts]'),'TableFullTextCatalogId') = 0) 
      EXEC dbo.sp_fulltext_table 
        @tabname = N'[dbo].[Posts]' , 
        @action = N'create' , 
        @keyname = N'PK_Posts' , 
        @ftcat = N'PostFullText' 

    DECLARE  @lcid INT 

    SELECT @lcid = lcid 
    FROM   MASTER.dbo.syslanguages 
    WHERE  alias = N'English' 

    EXEC dbo.sp_fulltext_column 
      @tabname = N'[dbo].[Posts]' , 
      @colname = N'Body' , 
      @action = N'add' , 
      @language = @lcid 

    SELECT @lcid = lcid 
    FROM   MASTER.dbo.syslanguages 
    WHERE  alias = N'English' 

    EXEC dbo.sp_fulltext_column 
      @tabname = N'[dbo].[Posts]' , 
      @colname = N'Title' , 
      @action = N'add' , 
      @language = @lcid 

    EXEC dbo.sp_fulltext_table 
      @tabname = N'[dbo].[Posts]' , 
      @action = N'start_change_tracking' 

    EXEC dbo.sp_fulltext_table 
      @tabname = N'[dbo].[Posts]' , 
      @action = N'start_background_updateindex' 
  END 

SET ANSI_NULLS  ON 

SET QUOTED_IDENTIFIER  ON 

SET ANSI_PADDING  ON 

IF NOT EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Comments]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
  BEGIN 
    CREATE TABLE [dbo].[Comments] ( 
      [Id]           [INT]    NOT NULL, 
      [SiteId]       [VARCHAR](10)    NOT NULL, 
      [CreationDate] [DATETIME]    NOT NULL, 
      [PostId]       [INT]    NOT NULL, 
      [Score]        [INT]    NULL, 
      [Text]         [NVARCHAR](700)    NOT NULL, 
      [UserId]       [INT]    NULL
      --ROWID, [RowId]        [INT]    IDENTITY ( 1 , 1 )    NOT NULL
      --ROWID--PK, CONSTRAINT [PK_Comments] PRIMARY KEY CLUSTERED ( [RowId] ASC ) ON [PRIMARY]
      --UNIQUE, CONSTRAINT [IX_Comments_Id_SiteId] UNIQUE NONCLUSTERED ([Id] ASC,[SiteId] ASC ) ON [PRIMARY]
      )
    ON [PRIMARY] 
  END 
ELSE 
    TRUNCATE TABLE [dbo].[Comments]

IF 0 = 1--INDEXES 
  BEGIN 
    SET ANSI_PADDING  OFF 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[Comments]') 
                          AND name = N'IX_Comments_SiteId_PostId') 
      CREATE NONCLUSTERED INDEX [IX_Comments_SiteId_PostId] ON [dbo].[Comments] ( 
            [SiteId] ASC, 
            [PostId] ASC) 
      ON [PRIMARY] 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[Comments]') 
                          AND name = N'IX_Comments_SiteId_UserId') 
      CREATE NONCLUSTERED INDEX [IX_Comments_SiteId_UserId] ON [dbo].[Comments] ( 
            [SiteId] ASC, 
            [UserId] ASC) 
      ON [PRIMARY] 
  END 

SET ANSI_NULLS  ON 

SET QUOTED_IDENTIFIER  ON 

SET ANSI_PADDING  ON 

IF NOT EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = OBJECT_ID(N'[dbo].[Badges]') 
                      AND OBJECTPROPERTY(id,N'IsUserTable') = 1) 
  BEGIN 
    CREATE TABLE [dbo].[Badges] ( 
      [Id]     [INT]    NOT NULL, 
      [SiteId] [VARCHAR](10)    NOT NULL, 
      [Name]   [NVARCHAR](40)    NOT NULL, 
      [UserId] [INT]    NOT NULL, 
      [Date]   [DATETIME]    NOT NULL
      --ROWID, [RowId]  [INT]    IDENTITY ( 1 , 1 )    NOT NULL
      --ROWID--PK, CONSTRAINT [PK_Badges] PRIMARY KEY CLUSTERED ( [RowId] ASC ) ON [PRIMARY]
      --UNIQUE, CONSTRAINT [IX_Badges_Id_SiteId] UNIQUE NONCLUSTERED ([Id] ASC,[SiteId] ASC ) ON [PRIMARY]
      )
    ON [PRIMARY] 
  END 
ELSE 
    TRUNCATE TABLE [dbo].[Badges]

IF 0 = 1--INDEXES 
  BEGIN 
    SET ANSI_PADDING  OFF 

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.sysindexes 
                   WHERE  id = OBJECT_ID(N'[dbo].[Badges]') 
                          AND name = N'IX_Badges_SiteId_UserId') 
      CREATE NONCLUSTERED INDEX [IX_Badges_SiteId_UserId] ON [dbo].[Badges] ( 
            [SiteId] ASC, 
            [UserId] ASC) 
      ON [PRIMARY] 
  END
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .