I have this table of:
The first 20 columns are used by all rows, but the remaining 20 columns are just used by about 2% of the rows, and null for the rest. Queries are run mainly on the first columns.
My question is if it has any performance benefits (or are any practical up/downsides) to split these out as a separate table with a 1:1 relationship. The columns are a mix of dates, integers, and short strings. Its going to run on Azure SQL and im using an ORM.
Well, it depends. From storage engine perspactive, splitting the table will give you smaller rows in the main table, more rows on a single page, more data read in a single go, and probably a better perf as a result. But it largely depends on if the majority of your queries will join to the sub table or not. You should test both approaches, of course, and also take into consideration the sparse columns feature (https://msdn.microsoft.com/en-us/library/cc280604.aspx).