Creating a well structured large SQL database -

i had single table contained of our server's windows event logs in (pulled log server).

at first ok, put more data it, slower became...it millions of rows.

so thought better break down each server it's own table, making specific querying faster.

the issue is, if want search event across servers?

so i'm coming here advice on how correctly set tables.

below create to.. copy , paste of how each table structured, replacing 'server1' actual name.

         use [events_data]     go      /****** object:  table [dbo].[servername1]    script date: 8/18/2017 8:48:59 ******/     set ansi_nulls on     go      set quoted_identifier on     go      create table [dbo].[servername1](         [message] [nvarchar](max) null,         [id] [nvarchar](max) null,         [version] [nvarchar](max) null,         [qualifiers] [nvarchar](max) null,         [level] [nvarchar](max) null,         [task] [nvarchar](max) null,         [opcode] [nvarchar](max) null,         [keywords] [nvarchar](max) null,         [recordid] [nvarchar](max) null,         [providername] [nvarchar](max) null,         [providerid] [nvarchar](max) null,         [logname] [nvarchar](max) null,         [processid] [nvarchar](max) null,         [threadid] [nvarchar](max) null,         [machinename] [nvarchar](max) null,         [userid] [nvarchar](max) null,         [timecreated] [datetime] null,         [activityid] [nvarchar](max) null,         [relatedactivityid] [nvarchar](max) null,         [containerlog] [nvarchar](max) null,         [matchedqueryids] [nvarchar](max) null,         [bookmark] [nvarchar](max) null,         [leveldisplayname] [nvarchar](max) null,         [opcodedisplayname] [nvarchar](max) null,         [taskdisplayname] [nvarchar](max) null,         [keywordsdisplaynames] [nvarchar](max) null,         [properties] [nvarchar](max) null     ) on [primary] textimage_on [primary]      go 

i've attached row of data server...each table similar except 'machinename' column specific server table holds events for.

 message,id,version,qualifiers,level,task,opcode,keywords,recordid,providername,providerid,logname,processid,threadid,machinename,userid,timecreated,activityid,relatedactivityid,containerlog,matchedqueryids,bookmark,leveldisplayname,opcodedisplayname,taskdisplayname,keywordsdisplaynames,properties error message whatever,1000,,0,2,100,,3.60288e+16,302366,application error,,application,,,servernameapp1.domain.com,,00:03.0,,,\\servernamelogs\d$\windows-logs\archive-forwardedevents-2017-04-01-05-03-29-167.evtx,,system.diagnostics.eventing.reader.eventbookmark,error,info,application crashing events,classic,system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty system.diagnostics.eventing.reader.eventproperty 

any on creating structured database can query against tables appreciated!

wow. many things wrong this, start:

1. create primary key , clustered index on commonly used key column(s) or artificial key (looks id in case?)
2. change datatypes... varchar(max) string of unlimited length... id columns should ints , event string columns limited reasonable max length.
3. make required columns non-nullable
4. create non-clustered indexes on join / sort / columns

do not create table each server. create index on serverid column...

p.s. sure need columns? how inserting them? few inputting type of object, eg. system.diagnostics.eventing.reader.eventproperty in last column. need .tostring() or actual value. question if need these columns.