Would anyone know about how to go about adding one's own SQL to the SQL that is already supported by SQL Server?
I guess what I am trying to ask is if I can have my own query language but use the other parts of SQL Server? I would even keep some parts of SQL Server's SQL, but I would want to add support for a User Defined Type (UDT)I have created.
The following is what I would like to try to do, say you're in Query Analyzer:
select * from Person.Contact
select (insert custom SQL code here)
I would want to inform query analyzer to peel off and use my custom SQL code interpreter. It would have to recognize my special % (I've seen this technique with embedded SQL in C)
The reason I want to do this is because I have created an new data type, and want to implement operators on that new datatype. This is not available to me within the currect SS2k8 environment.
Last edited by gurzynski; 12-21-11 at 15:38.
Reason: More detail
While using SPs and UDFs may give me answers to particular queries involving my interval type, I am out to PROVE the proper way to handle my new datatype is with operators within a "new" SQL that properly handles handle the datatype.
I am interested in creating something quite revolutionary, and not just getting a query to work.
I have created a new interval datatype:
Using the type allows me to solve some time overlap issues, via stored procedures, that are only solvable uisng ancillary calendar tables with dates in them in SQL Server's version of SQL.
I was thinking about using something like MySQL. I would assume that would allow me to create my "own" SQL, but then I would have to throw away alll my C# work to create the datatype.
What I am looking for is to create something like TimeDB, from Wikipedia:
TimeDB TimeDB is a free temporal relational DBMS by TimeConsult. It runs as a frontend to Oracle that accepts TSQL2 statements and generates SQL92 statements.
I am not looking to create TSQL2, but my own Time Supportive SQL, interpret it, convert it to SQL Server supported SQL. In this SS2k8 compliant SQL there very well may be SPs and UDTs I use to implement my new type.
In short, time is handled very badly within the current supported SS2k8 SQL. I want to PROVE, that with the proper SQL support for an interval type, a type described very well in Temporal Data and the Relational Model by C. J. Date, that queries that are very difficult or even impossible in the current SS2k8 implementation are easy and possible with the relationally correct support for an interval type.
I guess what I am talking about is an interpreter for "New" version of SQL that translates my version into a SS2k8 compliant SQL. I have no idea of how to go about this.
... time is handled very badly within the current supported SS2k8 SQL. I want to PROVE, ..., that queries that are very difficult or even impossible in the current SS2k8 implementation are easy and possible with the relationally correct support for an interval type.
I guess what I am talking about is an interpreter for "New" version of SQL that translates my version into a SS2k8 compliant SQL.
You found that there are queries that are impossible to express in T-SQL. And you want to create an interpreter that translates your New Custom SQL commands to T-SQL commands to do the job. But first you said T-SQL commands can't handle those. Isn't that a contradiction?
What functionality (methods) does your C# class offer?
What methods have you classified as A) impossible B) hard C) easy to implement in T-SQL?
If you are interested in expanding the SQL syntax of a DBMS, you may find the source code of open source databases, like MySQL or PostrgreSQL, very helpful. I know there are extensions available for geospatial data (GIS). I guess what you're after is something similar.
I vaguely remember "Datablades", from when I worked with an Informix database. One could write his/her own Datablade if memory serves.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
I see no contradiction whatsoever, my quote is below:
"I want to PROVE, that with the proper SQL support for an interval type, ... that queries that are very difficult or even impossible in the current SS2k8 implementation are easy and possible with the relationally correct support for an interval type. "
So, the important caveat I add is the interval type and relationally correct support for that type vs. an implementation that does not have the type and relational support for it. A new type is essentially adding a new noun to the relational database's vocacubulary, there really aren't that many. (varchar, int, small int, ...) . That's huge. Also, relationally correct is also a huge statement. Current implementations of SQL, to say the least, are relationally lacking.
Also, while some temporal queries are "solvable", they involve ancilliary calendar tables that essentially are rows of dates. I also state that in my post. There is no need for these ancillary tables if things are done correctly.
Last edited by gurzynski; 12-22-11 at 12:53.
Reason: Added to my original quote/caveat