Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How Can I Create My Own SQL

Răspuns How Can I Create My Own SQL

  • Friday, January 04, 2013 5:21 PM
     
     
    Sign In to Vote
    0

    I have created my own datatype (a UDT, not a type alias) and would like to create my own operators on that datatype.  SQL Server's version of SQL doesn't have the operators I need that would be able to take advantage of a rather complex new datatype that I have created. 

    The new datatype I have created is an interval, let's call it interval1:

    [1/1/2012:1/10/2012]

    The interval represents a period of time in the case above.  Let's say we have another interval, interval2:

    [1/5/2012:1/15:2012]

    I would like to create a superset of SQL that will support something like:

    select [1/1/2012:1/10/2012] overlaps [1/5/2012:1/15/2012]

    and get the result set:

    [1/5/2012:1/10/2012]

    which is a table with one (1) row and one (1) column of the type interval (my UDT)

    Of course I used a "literal" form of the interval datatype above.  If interval1 and interval2 were two columns in a table called intervaltable the SQL should look like:

    select interval1 overlaps interval2 as overlappedinterval

    from intervaltable

    and I would get a result set of a single column of the interval type with multiple rows.

    I am able to get the "answer" I am looking for, but it involves calling CLR Stored procedures and SQL involving temporary tables. I would like to hide that complexity from the user by expanding SQL to handle the new type. 

    Is the such a thing as an SQL Development Kit?  Or a SQL interpreter?  Surely, Microsoft has the capabilty, they must be able to accomodate changes to their version of SQL.  (I think it is accurate to say that all relational database vendors implement a superset of a subset of the SQL standard.)  My effort would involve expanding the superset of SQL that Microsoft supports, if you will.


     

All Replies

  • Friday, January 04, 2013 5:39 PM
     
     
    Sign In to Vote
    0

    Hi,

    Thanks for your initiative but how can we help you ?

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    • Edited by Ahsan Kabir Friday, January 04, 2013 5:41 PM
    •  
     
  • Friday, January 04, 2013 6:04 PM
     
      Has Code
    Sign In to Vote
    0

    you can create a scalar function that returns your data type:
    http://msdn.microsoft.com/en-us/library/ms186755.aspx

    here is an example for a SMALL subset of data

    CREATE FUNCTION OverLap
(
	@0Beg	DATE,
	@0End	DATE,
	@1Beg	DATE,
	@1End	DATE
)
RETURNS CHAR(21)
AS
BEGIN

	DECLARE @Beg DATE;
	DECLARE @End DATE;

	-- Include code to organize the sequence of the overlap dates
	-- This code only works when @0Beg < @1Beg < @0End < @1End
	--				0B----1B----0E----1E

	IF DATEDIFF(d, @0Beg, @1Beg) >= 0 AND DATEDIFF(d, @0End, @1Beg) =< 0
	BEGIN
		SET @Beg = @1Beg;
	END

	IF DATEDIFF(d, @1Beg, @0End) >= 0 AND DATEDIFF(d, @1End, @0End) =< 0
	BEGIN
		SET @End = @0End;
	END

	RETURN CONVERT(VARCHAR(10), @Beg) + ':' + CONVERT(VARCHAR(10), @End)

END;
GO

DECLARE @0Beg	DATE  = '1/1/2012';
DECLARE @0End	DATE  = '1/10/2012';
DECLARE @1Beg	DATE  = '1/5/2012';
DECLARE @1End	DATE  = '1/15/2012';

SELECT @0Beg [0Beg], @0End [0End], @1Beg [1Beg], @1End [1End], dbo.OverLap(@0Beg, @0End, @1Beg, @1End)


     
  • Friday, January 04, 2013 6:06 PM
     
     
    Sign In to Vote
    0
    INTERVAL is a temporal data type in ANSI/ISO Standard SQL. And, no, you do not get to change MS proprietary code. Look at other SQL products if you need this. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

     
  • Friday, January 04, 2013 6:42 PM
     
     
    Sign In to Vote
    0

    Hello,

    Thanks for taking the time to look at my post.

    I believe you question was:

    How can you help me?

    Let me elaborate.

    In my original post I am requesting how I might go about adding to SQL Server's version of SQL to support my new datatype?  I then ask if there is such a thing as an SQL Development Kit or an SQL Interpreter.  How would I go about doing that?  Certainly there has to be SOME way.  That may involve obtaining contacts at Microsoft who would allow me access, or, maybe creating my own SQL interpreter that would support my "new" SQL that supports my interval datatype. 

    This is not a simple question like "How do I use a temporary table" or "When is it advantageous to use an index?"  It is about expanding the capabilities of Microsoft's version of SQL to more effectively handle a new datatype.  Specifically, my interval datatype. 

    What would be nice is to have access to how Microsoft would actually do it, but that may be difficult.

    I was thinking about doing something like this.  I remember using a form of embedded SQL years ago that worked like this:

    select id1, column1 from table1 into resulttable

    %$

    // The %$ tells the interpreter that "What follows is code I want you to handle differently than the normal SQL Server SQL code

    select interval1 overlaps interval2 as overlappedinterval, id1 into table2

    from intervaltable

    %$

    // The second %$ tells the interpreter that the "special" non native SQL code is done. Go ahead now and use "regular" SQL that SQL Server should understand easily to combine the results of the "native" SQL and expanded SQL. 

    SELECT interval1
    FROM resulttable INNER JOIN table2
      ON resulttable.id1= table2.id2

    So, my question is, how could I go about expanding SQL Server SQL directly, OR, how would one go about creating the SQL interpreter described above?

    Ultimately, I am expecting my answer may be:  If you want to do this sort of thing, scrap your more than 20 years of experience with Microsoft tools and use an Open Source solution like MySQL.

    Certainly, someone out there in database development land has what they feel is a datatype, when, combined with the appropriate operators on that type, would exhibit theoretical and practical advantages and has gone to Microsoft with the idea, and they implement it.  How is that done? 


     
  • Friday, January 04, 2013 7:21 PM
     
     
    Sign In to Vote
    0

    Hello Rogge,

    Thanks for taking the time to look at my post.

    Unfortunately, I don't think your reply represents a solution because what you are returning in your function is the native SQL Server type varchar. 

    Here is your code that returns the varchar type:

    RETURN CONVERT(VARCHAR(10), @Beg) + ':' + CONVERT(VARCHAR(10), @End)

    I wish to return my own User Defined Type (UDT) called interval, not the native varchar type already available in SQL Server.

    I have numerous solutions that allow me to get an answer from the simple example I show using types native to SQL Server.  I want to get back the type I developed. 

    Again, thanks for trying.

     
  • Friday, January 04, 2013 8:01 PM
     
     
    Sign In to Vote
    0

    Hello Mr. Celko,

    I am quite familiar with the INTERVAL temporal data type in ANSI/ISO Standard SQL, and have found it lacking in a number of areas (of which I don't have the time to explain in this post, but criticisms are out there), that is why I have tried to create my own implementation of a temporal type that doesn't have the problems the one described in the standard has. 

    As far as changing MS proprietary code goes I know this may be difficult, but who might I be able to talk to about it at Microsoft?  Does anyone know?  I have already asked for implementations via various requests for feature enhancments during various beta releases.  Also, I have my "interpreter" suggestion.  How would I go about implementing that? 

    Please see my latest post for details. 

    Thank you. 


     
  • Friday, January 04, 2013 8:31 PM
     
     Proposed
    Sign In to Vote
    2

    You can file your suggestion here:  File bugs/suggestions

    I'll point out that your goal may be laudable but actually implementing it within sql server requires a great deal of thought, design, and coding.  TSQL is a stronly-typed language and does not lend itself to new "types".  There are equally daunting issues - such as how columns of this type would be indexed (or if that even makes any sense). 

    So things do not look promising unless you are extremely persuasive, have deep and intimate connections in MS or the industry, or a fat wallet. It would help if you had published your ideas in some official manner - perhaps presenting in a conference where it could be debated and more closely scrutinized.  Maybe someone from MS will respond to your thread - but don't hold your breath. 

     
  • Friday, January 04, 2013 9:48 PM
     
     
    Sign In to Vote
    0

    Hello Scott,

    Thanks for your reply.  So far, you have addressed my the question the best, and I have voted for it.  I have certainly brought up support for temporal datatype enhancements during various beta version of SS.  Particularly during a release called Yukon.  I have also brought up the issue with a number of user groups.

    Quite frankly, I think the case for proper temporal datatype support is made quite well in a book by C. J. Date, Temporal Data and the Relational Model.

    I have had one discussion with venture capital people and while they were receptive, I am convinced I need to show some type of working prototype at the very least. 

    Andy Gurzynski

     
  • Saturday, January 05, 2013 12:05 AM
     
     
    Sign In to Vote
    0

    I would like opinions on this thought:

    If a temporal interval datatype like [1/1/2012:1/10/2012] (or more generally [D1:D10] were supported along with operators like :

    equals, includes, before, after, meets, overlaps, merges

    all BI solutions would be obsolete.  The entire AdventureWorks Data Warehouse solution would be fundamentally simpler.  The only thing that would have to be added would be intervals for when things were valid, like prices and the time interval when the price was valid, or the time interval an employee was employed.

    Once this is done, there wouldn't be a need to separate the transaction database from the decision support database.  You may want to do this for strictly performance reasons, but you wouldn't have to.

    Again, most of the theoretical aspects are discussed in:

    C. J. Date, Temporal Data and the Relational Model

    Andy Gurzynski

     
  • Saturday, January 05, 2013 1:34 AM
     
     Answered Has Code
    Sign In to Vote
    0

    I am able to get the "answer" I am looking for, but it involves calling CLR Stored procedures and SQL involving temporary tables. I would like to hide that complexity from the user by expanding SQL to handle the new type. 

    It seems to me you could implement this with as an Overlaps method in a CLR UDT.  Usage would be something like:

    SELECT CAST('1/1/2012:1/10/2012' AS IntervalType).Overlaps('1/5/2012:1/15/2012').ToString() as overlappedinterval;

    or

    SELECT interval1.Overlaps(interval2).ToString() as overlappedinterval
FROM dbo.intervaltable;

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    • Edited by Dan GuzmanMVP Saturday, January 05, 2013 2:41 AM added ToString method
    • Marked As Answer by Iric WenModerator Monday, January 14, 2013 9:29 AM
    •  
     
  • Saturday, January 05, 2013 2:10 AM
     
     
    Sign In to Vote
    0

    >> As far as changing MS proprietary code goes I know this may be difficult, but who might I be able to talk to about it at Microsoft? <<

    Look for Conner Cummingham and his blgs. Then ask if they are hiring compiler writers. Otherwise, you can only post requests to "The Powers that Be" because this is not an easy add-on. Rick Snodgrass did a lot of early temporal stuff when he was on ANSI X3H2, and he might have some ideas. But as I remember it, DATETIME and INTERVAL had to be built into the engine at the same time for it to perform worth a damn. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL