Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm looking for a very exotic thing and have no idea if it exists at all in SQL Server 2012. (Please forgive me - my background is in T-SQL, but not yet the fine details of SQL 'programming'.) So, whether this 'this' is a UDF or SP or something else - I don't know.

The challenge is to have something so flexible that it could return a table with one or more records and a list of fields that is determined at runtime. I guess something like a virtual table, but 'lighter' and faster. Is that doable (and how)?

As an example, let's say I have two records (IDs being 1 and 2) and their values for field 'name' are 'R1' and 'R2'. Some other logic makes me want to combine these into a record with ID 'virtual1' and two fields names , 'F1' and 'F2' with value 'R1' and 'R2' - very much boiled down, but this what I'd like to achieve....

share|improve this question

closed as unclear what you're asking by amon, MichaelT, Bart van Ingen Schenau, gnat, Martijn Pieters Apr 20 at 7:13

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
Because when I hear the words "Microsoft SQL Server 2012", I think flexibility. –  Neil Apr 18 at 15:27
    
Well, what should I say...;-) –  MBaas Apr 18 at 15:29
    
SELECT Field1, Field3, Field99 FROM TableWithLotsOfFields? –  poke Apr 18 at 18:00
    
No, that exceeds SELECT, because I want to make different "artificial" fields from the same field and combine two independent records into one. –  MBaas Apr 18 at 18:36
    
Failing all else, there's always dynamic SQL. It's got terrible performance, but if you can query it, dynamic SQL can do it. –  mgw854 Apr 18 at 18:39

3 Answers 3

Not sure if this meets your needs, but views can be very dynamic if you know how to use them properly. The tables should be well-structured and contain the data exactly once, however the views can organize these tables with whatever combinations of columns you can imagine, given you know how to define them in a query. I think this is a very powerful way to organize a database without having to sacrifice good database structure as well. However we talking about creating a table from columns from existing tables.

If you wanted to create a table from scratch, then you should probably look into temporary tables (see in section Temporary Tables below). These tables disappear once you disconnect, making them ideal for holding information for performing various tasks. Like a normal table, you can make them to hold any type of information you want. Of course, you still have to take the time to define each field individually, however assuming you were writing a dynamic script, this should be a trivial task.

The alternative being that you stay away from SQL Server altogether and you just use NoSQL type databases. These are truly dynamic and are meant to be changed on the fly. If having a dynamic database were important enough, I would consider using NoSQL such as mongodb rather than SQL Server.

share|improve this answer

SQL Server has the ability to host .NET assemblies so, in theory, you could write code to make it do whatever you want.

Failing that, there's always EAV, but I wouldn't wish that on my worst enemy.

What you really need to do is figure out how you can confine your scope. Instead of building an inner platform, try to figure out the minimum possible additional flexibility that will meet your software requirements, and implement that. My guess is that you can do it with a custom table and some DDL.

share|improve this answer

It can be done (and indeed has been done), but it's not a feature of SQL Server and it's not particularly 'light'.

A relational database management system stores all its data in tables (that's item 0 in Codd's 12 rules). The flexibility you describe comes when you define tables that define 4 things: tables, rows, columns and values.

  • The 'tables' table lists all your tables (table id, attributes).
  • The 'columns' table lists columns in all your tables (table id, column id, attributes).
  • The 'rows' table lists rows in all your tables (table id, row id).
  • The 'values' table lists values in all your tables (table id, row id, column id, value). if you don't want all strings, you can have multiple 'values' tables.
  • There might be some other lookup tables for attributes and types, depending on how fancy you want to get.

You access the data by generating some rather fancy SQL that joins the 4 tables into the virtual table you mentioned.

Possibly not what you had in mind, but it can do what you asked.


And yes, this is an inner platform and has some features in common with a God table. As mentioned in these references, some very well known platforms use this technique to a greater or lesser extent, especially when tables, fields and relationships have to be defined on the fly. Microsoft CRM and Oracle Financials come to mind. Just don't do it unless you really need to. Then have no fear.


Obviously a system built like this would be next to impossible to query directly in SQL. As a rule end-users of such systems do not write SQL, but instead use the query and reporting tools provided as part of the system. It is also possible for the system to generate views on the fly to present the internal tables in a standard relational format, on which you can write standard SELECT queries and use SQL-oriented reporting tools.

share|improve this answer
    
Thanks, that does not sound particularly light, like you said. I was hoping it might be doable with UDFs - perhaps when definining at runtime, specifically for that purpose. –  MBaas Apr 18 at 14:52
1  
This describes an inner platform or God table, neither of which are desirable solutions. –  Snowman Apr 18 at 17:56
1  
@JohnGaughan: It does indeed, and why not? Absolutely the right answer for some questions. See edit. –  david.pfx Apr 19 at 0:24
    
@david.pfx these idioms remove the "R" from "RDBMS" making it far more difficult to query data, which is one of the primary actions performed by an RDBMS. –  Snowman Apr 21 at 13:29
    
@JohnGaughan: see edit. –  david.pfx Apr 21 at 14:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.