what is the recommended way to save a M by N matrix in sql table

If I have a M by N matrix filled with data, what is the best way to save it to a sql table for fast query? I can save a 3 column table (X, Y,Z) total MN ro开发者_如何学Cws, or simply M rows with N columns. N is in the order of hundreds to thousands, M can be much larger. I guess the second way is faster for query, since the query can be build on known column names. But would it recommended to have as many columns in a table? thanks,

Your second option (N columns) will make it harder to select values from column unknown in compile time (you'll have to dynamicaly generate selects). So I'll go with table (X,Y,value) and make (x,y) a primary key.

You are gonna have a limitation on the maximum number of columns that a table can have on any RDBMS. For example, SQL Server 2008 has a maximum of 1.024 columns for a nonwide table and 30.000 columns for a wide table, Oracle had 1.000 columns maximum (though i'm not sure if that limitation changed). So I recommend your first option, actually, I would've chosen that even with no column limitation.