0

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....

3 Answers3

2

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.

Neil
  • 22,750
1

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.

Community
  • 1
Robert Harvey
  • 199,517
-1

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.

david.pfx
  • 8,125
  • 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. –  Apr 18 '14 at 14:52
  • 1
    This describes an inner platform or God table, neither of which are desirable solutions. –  Apr 18 '14 at 17:56
  • 1
    @JohnGaughan: It does indeed, and why not? Absolutely the right answer for some questions. See edit. – david.pfx Apr 19 '14 at 00: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. –  Apr 21 '14 at 13:29
  • @JohnGaughan: see edit. – david.pfx Apr 21 '14 at 14:11